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.
- go get github.com/buffalo-x/borm
- download zip file and unzip to directoty github.com/buffalo-x/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".
-
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")
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 -
Update more columns
bgen.Sql("id=?", 23).
UpdateColumns("test_table",
bsql.CV{"code": "33", "name": "cccs", "col_int":\ bsql.Expr("col_int+1")})
- prepare sql option data
You can organize sqls like this. id-》dbType+dbVersion-》sqlsqlMap := 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)This also works in .Where function.