( 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
You can use go get to install beedb locally.
go get github.com/astaxie/beedbFirst, 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=trueNext, 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.
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.
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) // updateLike 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 thatuidis the primary key records in theuserinfotable..Where()sets conditions and supports multiple arguments. If the first argument is an integer, it's a short form forWhere("<primary key>=?", <value>)..Update()method accepts a map and updates the database.
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 keyExample 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][]bytetype, so you need to convert to other types yourself.
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()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.
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.
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/driverin 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.
- Directory
- Previous section: PostgreSQL
- Next section: NoSQL database