a small ORM for golang, simultaneously enhancing traditional access methods
We are trying to create a very simple orm framework using the go language that can support traditional database operations and orm patterns. The goal of borm is easy to use and modify in your project.
getting borm
go get github.com/buffalo-x/borm
download zip file and unzip to directoty github.com/buffalo-x/borm
starting to use borm
borm.Init() should be called in your project
connect to a database, taking MySQL as an example. dsn := "root:XXX@tcp(127.0.0.1:3307)/db"
sqlDb, err := sql.Open("mysql", dsn)
Add default database connection to datasource bds.SetDs("default", sqlDb, "root", "","127.0.0.1",3306, "db", "","mysql", "version info", "more info")
Here, "mysql" is dsType
More connections can be added to the datasource, and they can be accessed by function bds.GetDs(name string)(*Datasource)
DS with the name "default" should generally exist, otherwise it will increase code complexity. Because, if there is no explicit selection of ds, many functions default to using the ds with name of "default".
using bmod method
Define the go structure of a database table
type TestTable struct {
Id int64 `borm:"primaryKey;autoIncrement"`
Code string `borm:"column:code;"`
Name string
Pwd string `borm:"excluded"`
ColInt int `borm:"excluded"`
ColDecimal float64 `borm:"type:decimal(20,2)"`
ColDatetime string `borm:"column:col_datetime;autoCreateTime"`
ColDate time.Time `borm:"excluded"`
ColTs time.Time `borm:"autoUpdateTime;"`
}
The accepted datatype is among [string int int8 int16 int32 int64 float32 float64 bool time.Time]
borm tags :
primaryKey : this is a primary key column
autoIncrement : only primary key has this tag and field datatype must be int64
readonly : display data only
excluded : not handled
column : the real table column name in database
autoCreateTime : the time when record created, server's time
autoUpdateTime : the time when record updated, server's time
type : datatype constraints
Query table rows var rs []TestTable
modDb := bmod.Where("id>?",1).Query(&rs)
If everything is normal, you can see the data in rs. You can also use err := bmod.Query(&rs).Error() to find if error occured or not.
Query one table row
_var row TestTable modDb := bmod.Where("id=1").Query(&row)
About func Where func (modDb *ModDB) Where(sqlStr string, args ...interface{}) (retModDb *ModDB),this func will create a *ModDB and set its where condition
for example modDb := bmod.Where("code=? and name=?","z01","jim").Query(&row)
Specify a datasource func Db(dsName ...string) (retModDb *ModDB)
We can use it in this way. modDb := bmod.Db("ds1").Query(&rs)
This will choose a datasource with name of "ds1",then query.
Create a database record tb := &TestTable{Code: "aa01"}
modDb := bmod.Create(tb)
You can also choose a datasource modDb := bmod.Db("ds1").Create(tb)
Use Transaction func Tx(tx *sql.Tx, dsName ...string) (retModDb *ModDB)
We can use it in this way.
Firstly, you should create a *sql.Tx object. Then, modDb := bmod.Tx(tx,"ds1").Create(tb)
This will create record using tx
Save a database record tb := &TestTable{Code: "aa01",name:"mike"}\
modDb := bmod.Save(tb)
Delete a database record modDb := bmod.Delete(tb)
Use Count func var ct int
modDb := bmod.Model(tb).Where("1=1").Count(&ct)
Update one column bmod.Model(tb).Update("name","cat")
This will update the name column using table primary key bmod.Model(tb).Where("id=20").Update("name","cat")
This will update the name column where column id=20
Update more than one columns bmod.Model(tb).UpdateColumns(bsql.CV{"name": "cat", "code": "z002"})
bsql.CV is a map[string]interface{}
Use bsql.Expr bmod.Model(tb).Update("col_int",bsql.Expr("col_int+?", 1))
This means\ update test_table set col_int=col_int+1
Use sql.Rows as Output func (modDb *ModDB) Rows(columnList ...string) (*sql.Rows, error)
Youcan use
_bmod.Model(tb).Rows()
or
_bmod.Model(tb).Where("id>1").Rows("name,code")
Use sql.Row as Output func (modDb *ModDB) Row(columnList ...string) (*sql.Row, error)
Youcan use
_bmod.Model(tb).Where("id>1").Row()
or
_bmod.Model(tb).Where("id>?",2).Row("name,code")
using bgen method
We also have a traditional way to deal with database.
Query table rows var rs bsql.Rows
genMod := bgen.Sql("select * from test_table where id>?", 1).Query(&rs)
If everything is normal, you can see the data in rs. You can also use err := bgen.Sql("select * from test_table where id>?", 1).Query(&rs).Error()
to find if error occured or not.
bsql.Rows has the following definition,all data being stored as string.
type Rows struct {
Count int
Data [][]string
ColsMap map[string]int
ColsName []string
}
Query table row var rs bsql.Row
genMod := bgen.Sql("select * from test_table where id>?", 1).First(&rs)
bsql.Row has the following definition,all data being stored as string.
type Row struct {
Data []string
ColsMap map[string]int
ColsName []string
}
Specify a datasource genMod := bgen.Db("ds1").Sql("select * from test_table where id>?", 1).First(&rs)
This will choose a datasource with name of "ds1"
Use Transaction
func Tx(tx *sql.Tx, dsName ...string) (retModDb *ModDB)
We can use it in this way.
Firstly, you should create a *sql.Tx object. Then, genMod := bmod.Tx(tx,"ds1").Sql("select * from test_table where id>?", 1).First(&rs)
This will query using tx
Query a value var ct int
bgen.Sql("select count(*) from test_table").Value(&ct)
If your query return more then one rows, the first row will be used. bgen.Sql("select id from test_table").Value(&ct)
Use sql.Rows as Output rows,err:=bgen.Sql("select * from test_table").Rows()
Use sql.Row as Output row,err:=bgen.Sql("select * from test_table").Row()
Exec sql genMod:= bgen.Sql("insert into test_table(name,code) values(?,?)","mike","z11").Exec()
This will execute one insert sql. The following code has the same result. args := []interface{}{"mike", "z11"}
db1 := bgen.Sql("insert into test_table(name,code) values(?,?)",args).Exec()
We can create args like the following: args := [][]interface{}{{"susan", "z01"}, {"jose", "z02"}}
db1 := bgen.Sql("insert into test_table(name,code) values(?,?)",args).Exec()
This will execute two insert sqls.
Exec batch sqls func (genDb *GenDB) Batch(sqls []bsql.BatchSql) (retGenDb *GenDB)
This will execute sqls in []bsql.BatchSql.
bsql.BatchSql is a struct\
type BatchSql struct {
Sql string
Args []interface{}
}
Create record bgen.Create("test_table", bsql.CV{"code": "33", "name": nil, "col_int": 1})
Update one column bgen.Sql("id=?", 23).Update("test_table", "name", "mike")
this will update one column
prepare sql option data
You can organize sqls like this. id-》dbType+dbVersion-》sql
sqlMap := map[string]map[string]string{
"id1": {
"mysql": "select id,now() from test_table",
"mssql": "select id,getdate() from test_table",
"default": "select id,now() from test_table",
},
"id2":{
"mysql": "select id,left(name,5) from test_table",
"mssql": "select id,substring(name,0,5) from test_table",
},
}
"id1" and "id2" are ids
"mysql" and "mssql" are dbTypes
"default" is the option when no matchs
add the option sql data to global map\
_mdb.AddDsSqlOptionMap("test",sqlMap)
Here, "test" is called a category.
use the option data var rs bsql.Rows
bgen.Sql("opt^^test,id1^^demo sql").Query(&rs)
This will use the ds with name "default" to locate sql in group "id1" of category "test".
If no match found,
"default": "select id,now() from test_table"
will be returned.
You can use like this to specify a datasource: bgen.Db("ds1").Sql("opt^^test,id1^^demo sql").Query(&rs)