05.5.md

#5.5 Develop ORM based on beedb beedb is a ORM, "Object/Relational Mapping", that developed in Go by me. It uses Go style to operate database, implemented mapping struct to database records. It's a lightweight Go ORM framework, the purpose of developing this ORM is to help people learn how to write ORM, and finds a good balance between functions and performance.

beedb is an open source project, and supports basic functions of ORM, but doesn't support associated query.

Because beedb support database/sql interface standards, so any driver that supports this interface can be used in beedb, I've tested following drivers:

Mysql: github.com/ziutek/mymysql/godrv

Mysql: code.google.com/p/go-mysql-driver

PostgreSQL: github.com/bmizerany/pq

SQLite: github.com/mattn/go-sqlite3

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

ODBC: bitbucket.org/miquella/mgodbc

##Installation You can use go get to install beedb in your computer.

go get github.com/astaxie/beedb

##Initialization First, you have to import all corresponding packages as follows:

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 one is for standard requirement, and the second one is for indicating database engine, but if you are using MySQL/SQLite, you can just skip the second one.

Otherwise, you have to initialize, like SQLServer:

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

PostgreSQL:

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

beedb supports debug, and use following code to enable:

beedb.OnDebug=true

Now we have a struct for the database table Userinfo 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-convert your camel style name to underline and lower case letter. For example, we have UserInfo as the struct name, and it will be user_info in database, same rule for field name. Camel

##Insert data The following example shows you how to use beedb to save a struct instead of SQL command, and use Save method to apply change.

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

And you can check saveone.Uid after inserted, its value is self-increase ID, Save method did this job for you.

beedb provides another way to insert data, which is using map.

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 way I showed you above is like chain query, you should be familiar if you know jquery. It returns original ORM object after calls, and continue to do other jobs.

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

##Update data Continue above example to show how to update data. Now we have primary key value of saveone(Uid), so beedb executes update operation instead of inserting new record.

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

You can use map for updating data also:

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

Let me explain some methods we used above:

• .SetPK() tells ORM uid is the primary key of table userinfo.
• .Where() sets conditions, supports multiple arguments, if the first argument is a integer, it's a short form of Where("<primary key>=?", <value>).
• .Update() method accepts map and update to database.

##Query data 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 accepts two arguments, supports char type.
orm.Where("name = ?", "john").Find(&user3)

Example 4, more complex conditions:

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

Examples to get multiple records:

Example 1, gets 10 records that id>3 and 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 number of results.

• .Limit() supports two arguments, which are number of results and start position. 0 is the default value of start position.
• .OrderBy() is for ordering results, the arguments is the order condition.

All examples that you see is mapping records to structs, and you can also just put data into 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 database table, returns all fields as default.
• .FindMap() returns type []map[string][]byte, so you need to convert to other types by 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()

##Associated query beedb doesn't support joining between structs. However since some applications need this feature, here is a 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(), it 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 a implementation of group by and having.

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

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

##Future I have received many feedback from many people from all around world, and I'm thinking about reconfiguration in following aspects:

• Implement interface design like database/sql/driver in order to implement corresponding CRUD operations.

• Implement relational database design, one to one, one to many, many to many, here are some samples:

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

• Auto-create table and index.

• Implement connection pool through goroutine.

##Links

• Directory
• Previous section: PostgreSQL
• Next section: NoSQL database