5.5 Develop ORM based on beedb

5.5 Develop ORM based on beedb

( Project beedb is no longer maintained, but the code s still there )

beedb is an ORM ( object-relational mapper ) developed in Go, by me. It uses idiomatic Go to operate on databases, implementing struct-to-database mapping and acts as a lightweight Go ORM framework. The purpose of developing this ORM is not only to help people learn how to write an ORM, but also to find a good balance between functionality and performance when it comes to data persistence.

beedb is an open source project that supports basic ORM functionality, but doesn’t support association queries.

Because beedb supports database/sql interface standards, any driver that implements this interface can be used with beedb. I’ve tested the following drivers:

Mysql: github/go-mysql-driver/mysql

PostgreSQL: github.com/lib/pq

SQLite: github.com/mattn/go-sqlite3

Mysql: github.com/ziutek/mymysql/godrv

MS ADODB: github.com/mattn/go-adodb

Oracle: github.com/mattn/go-oci8

ODBC: bitbucket.org/miquella/mgodbc

Installation

You can use go get to install beedb locally.

go get github.com/astaxie/beedb

Initialization

First, you have to import all the necessary packages:

    import (
        "database/sql"
        "github.com/astaxie/beedb"
        _ "github.com/ziutek/mymysql/godrv"
    )

Then you need to open a database connection and create a beedb object (MySQL in this example):

    db, err := sql.Open("mymysql", "test/xiemengjun/123456")
    if err != nil {
        panic(err)
    }
    orm := beedb.New(db)

beedb.New() actually has two arguments. The first is the database object, and the second is for indicating which database engine you’re using. If you’re using MySQL/SQLite, you can just skip the second argument.

Otherwise, this argument must be supplied. For instance, in the case of SQLServer:

    orm = beedb.New(db, "mssql")

PostgreSQL:

    orm = beedb.New(db, "pg")

beedb supports debugging. Use the following code to enable it:

    beedb.OnDebug=true

Next, we have a struct for the Userinfo database table that we used in previous sections.

    type Userinfo struct {
        Uid     int `PK` // if the primary key is not id, you need to add tag `PK` for your customized primary key.
        Username    string
        Departname  string
        Created     time.Time
    }

Be aware that beedb auto-converts camelcase names to lower snake case. For example, if we have UserInfo as the struct name, beedb will convert it to user_info in the database. The same rule applies to struct field names.

Insert data

The following example shows you how to use beedb to save a struct, instead of using raw SQL commands. We use the beedb Save method to apply the change.

    var saveone Userinfo
    saveone.Username = "Test Add User"
    saveone.Departname = "Test Add Departname"
    saveone.Created = time.Now()
    orm.Save(&saveone)

You can check saveone.Uid after the record is inserted; its value is a self-incremented ID, which the Save method takes care of for you.

beedb provides another way of inserting data; this is via Go’s map type.

    add := make(map[string]interface{})
    add["username"] = "astaxie"
    add["departname"] = "cloud develop"
    add["created"] = "2012-12-02"
    orm.SetTable("userinfo").Insert(add)

Insert multiple data:

    addslice := make([]map[string]interface{}, 10)
    add:=make(map[string]interface{})
    add2:=make(map[string]interface{})
    add["username"] = "astaxie"
    add["departname"] = "cloud develop"
    add["created"] = "2012-12-02"
    add2["username"] = "astaxie2"
    add2["departname"] = "cloud develop2"
    add2["created"] = "2012-12-02"
    addslice = append(addslice, add, add2)
    orm.SetTable("userinfo").InsertBatch(addslice)

The method shown above is similar to a chained query, which you should be familiar with if you’ve ever used jquery. It returns the original ORM object after calls, then continues doing other jobs.

The method SetTable tells the ORM we want to insert our data into the userinfo table.

Update data

Let’s continue working with the above example to see how to update data. Now that we have the primary key of saveone(Uid), beedb executes an update operation instead of inserting a new record.

    saveone.Username = "Update Username"
    saveone.Departname = "Update Departname"
    saveone.Created = time.Now()
    orm.Save(&saveone)  // update

Like before, you can also use map for updating data:

    t := make(map[string]interface{})
    t["username"] = "astaxie"
    orm.SetTable("userinfo").SetPK("uid").Where(2).Update(t)

Let me explain some of the methods used above:

.SetPK() tells the ORM that uid is the primary key records in the userinfo table.
.Where() sets conditions and supports multiple arguments. If the first argument is an integer, it’s a short form for Where("<primary key>=?", <value>).
.Update() method accepts a map and updates the database.

Query data

The beedb query interface is very flexible. Let’s see some examples:

Example 1, query by primary key:

    var user Userinfo
    // Where accepts two arguments, supports integers
    orm.Where("uid=?", 27).Find(&user)

Example 2:

    var user2 Userinfo
    orm.Where(3).Find(&user2) // short form that omits primary key

Example 3, other query conditions:

    var user3 Userinfo
    // Where two arguments are accepted, with support for char type.
    orm.Where("name = ?", "john").Find(&user3)

Example 4, more complex conditions:

    var user4 Userinfo
    // Where three arguments are accepted
    orm.Where("name = ? and age < ?", "john", 88).Find(&user4)

Examples to get multiple records:

Example 1, gets 10 records with id>3 that starts with position 20:

    var allusers []Userinfo
    err := orm.Where("id > ?", "3").Limit(10,20).FindAll(&allusers)

Example 2, omits the second argument of limit, so it starts with 0 and gets 10 records:

    var tenusers []Userinfo
    err := orm.Where("id > ?", "3").Limit(10).FindAll(&tenusers)

Example 3, gets all records:

    var everyone []Userinfo
    err := orm.OrderBy("uid desc,username asc").FindAll(&everyone)

As you can see, the Limit method is for limiting the number of results.

.Limit() supports two arguments: the number of results and the starting position. 0 is the default value of the starting position.
.OrderBy() is for ordering results. The argument is the order condition.

All the examples here are simply mapping records to structs. You can also just put the data into a map as follows:

    a, _ := orm.SetTable("userinfo").SetPK("uid").Where(2).Select("uid,username").FindMap()

.Select() tells beedb how many fields you want to get from the database table. If unspecified, all fields are returned by default.
.FindMap() returns the []map[string][]byte type, so you need to convert to other types yourself.

Delete data

beedb provides rich methods to delete data.

Example 1, delete a single record:

    // saveone is the one in above example.
    orm.Delete(&saveone)

Example 2, delete multiple records:

    // alluser is the slice which gets multiple records.
    orm.DeleteAll(&alluser)

Example 3, delete records by SQL:

    orm.SetTable("userinfo").Where("uid>?", 3).DeleteRow()

Association queries

beedb doesn’t support joining between structs. However, since some applications need this feature, here is an implementation:

    a, _ := orm.SetTable("userinfo").Join("LEFT", "userdetail", "userinfo.uid=userdetail.uid")
        .Where("userinfo.uid=?", 1).Select("userinfo.uid,userinfo.username,userdetail.profile").FindMap()

We see a new method called .Join() that has three arguments:

The first argument: Type of Join; INNER, LEFT, OUTER, CROSS, etc.
The second argument: the table you want to join with.
The third argument: join condition.

Group By and Having

beedb also has an implementation of group by and having.

    a, _ := orm.SetTable("userinfo").GroupBy("username").Having("username='astaxie'").FindMap()

.GroupBy() indicates the field that is for group by.
.Having() indicates conditions of having.

Future

I have received a lot of feedback on beedb from many people all around the world, and I’m thinking about reconfiguring the following aspects:

Implement an interface design similar to database/sql/driver in order to facilitate CRUD operations.

Implement relational database associations like one to one, one to many and many to many. Here’s a sample:

              type Profile struct {
                  Nickname string
                  Mobile   string
              }
              type Userinfo struct {
                  Uid         int
                  PK_Username string
                  Departname  string
                  Created     time.Time
                  Profile     HasOne
              }

Auto-create tables and indexes.
Implement a connection pool using goroutines.

5.5. Develop ORM based on beedb