260 lines
9.0 KiB
Markdown
260 lines
9.0 KiB
Markdown
# 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](https://github.com/go-sql-driver/mysql)
|
|
|
|
PostgreSQL: [github.com/lib/pq](https://github.com/lib/pq)
|
|
|
|
SQLite: [github.com/mattn/go-sqlite3](https://github.com/mattn/go-sqlite3)
|
|
|
|
Mysql: [github.com/ziutek/mymysql/godrv](https://github.com/ziutek/mymysql)
|
|
|
|
MS ADODB: [github.com/mattn/go-adodb](https://github.com/mattn/go-adodb)
|
|
|
|
Oracle: [github.com/mattn/go-oci8](https://github.com/mattn/go-oci8)
|
|
|
|
ODBC: [bitbucket.org/miquella/mgodbc](https://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:
|
|
```Go
|
|
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):
|
|
```Go
|
|
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:
|
|
```Go
|
|
orm = beedb.New(db, "mssql")
|
|
```
|
|
PostgreSQL:
|
|
```Go
|
|
orm = beedb.New(db, "pg")
|
|
```
|
|
beedb supports debugging. Use the following code to enable it:
|
|
```Go
|
|
beedb.OnDebug=true
|
|
```
|
|
Next, we have a struct for the `Userinfo` database table that we used in previous sections.
|
|
```Go
|
|
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.
|
|
```Go
|
|
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.
|
|
```Go
|
|
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:
|
|
```Go
|
|
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.
|
|
```Go
|
|
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:
|
|
```Go
|
|
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:
|
|
```Go
|
|
var user Userinfo
|
|
// Where accepts two arguments, supports integers
|
|
orm.Where("uid=?", 27).Find(&user)
|
|
```
|
|
Example 2:
|
|
```Go
|
|
var user2 Userinfo
|
|
orm.Where(3).Find(&user2) // short form that omits primary key
|
|
```
|
|
Example 3, other query conditions:
|
|
```Go
|
|
var user3 Userinfo
|
|
// Where two arguments are accepted, with support for char type.
|
|
orm.Where("name = ?", "john").Find(&user3)
|
|
```
|
|
Example 4, more complex conditions:
|
|
```Go
|
|
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:
|
|
```Go
|
|
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:
|
|
```Go
|
|
var tenusers []Userinfo
|
|
err := orm.Where("id > ?", "3").Limit(10).FindAll(&tenusers)
|
|
```
|
|
Example 3, gets all records:
|
|
```Go
|
|
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:
|
|
```Go
|
|
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:
|
|
```Go
|
|
// saveone is the one in above example.
|
|
orm.Delete(&saveone)
|
|
```
|
|
Example 2, delete multiple records:
|
|
```Go
|
|
// alluser is the slice which gets multiple records.
|
|
orm.DeleteAll(&alluser)
|
|
```
|
|
Example 3, delete records by SQL:
|
|
```Go
|
|
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:
|
|
```Go
|
|
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`.
|
|
```Go
|
|
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:
|
|
```Go
|
|
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.
|
|
|
|
## Links
|
|
|
|
- [Directory](preface.md)
|
|
- Previous section: [PostgreSQL](05.4.md)
|
|
- Next section: [NoSQL database](05.6.md)
|