Qbs stands for Query By Struct. A Go ORM. ä¸æ–‡ç‰ˆ README
- 2013.03.14: index name has changed to
{table name}_{column name}
.- For existing application with existing database, update to this change may lead to creating redundant index, you may need to drop duplicated index manually.
- 2013.03.14: make all internal structures unexported.
- 2013.05.22: fixed memory leak issue.
- 2013.05.23: Breaking change, improved performance up to 100%, removed deprecated methods, make Db and Tx field unexported.
- 2013.05.25: Added
QueryStruct
method to do raw SQL query and fill the result into struct or slice of struct. Added support for defining custom table/struct and column/field name convertion function, so you are not forced to use snake case in database. - 2013.05.27: dropped go 1.0 support, only support go 1.1, changed to use build-in sql.DB connection pool.
- 2013.05.29: Added
Iterate
method for processing large data without loading all rows into memory. - 2013.08.03: Fixed a bug that exceeds max_prepared_stmt_count
- Define table schema in struct type, create table if not exists.
- Detect table columns in database and alter table add new column automatically.
- Define selection clause in struct type, fields in the struct type become the columns to be selected.
- Define join query in struct type by add pointer fields which point to the parent table's struct.
- Do CRUD query by struct value.
- After a query, all the data you need will be filled into the struct value.
- Compose where clause by condition, which can easily handle complex precedence of "AND/OR" sub conditions.
- If Id value in the struct is provided, it will be added to the where clause.
- "Created" column will be set to current time when insert, "Updated" column will be set to current time when insert and update.
- Struct type can implement Validator interface to do validation before insert or update.
- Support MySQL, PosgreSQL and SQLite3.
- Support connection pool.
Qbs.Find
is about 60% faster on mysql, 130% faster on postgreSQL than raw Db.Query
, about 20% slower than raw Stmt.Query
. (benchmarked on windows).
The reason why it is faster than Db.Query
is because all prepared Statements are cached in map.
Only support go 1.1+
Go get to get the most recent source code.
go get github.com/coocood/qbs
New version may break backwards compatibility, so for production project, it's better to download the tagged version. The most recent release is v0.2.
tags with same minor version would be backward compatible, e.g v0.1
and v0.1.1
.
tags with different minor version would break compatibility, e.g v0.1.1
and v0.2
.
See Gowalker for complete documentation.
-
The
qbs.Register
function has two more arguments thansql.Open
, they are database name and dilect instance. -
You only need to call it once at the start time..
func RegisterDb(){ qbs.Register("mysql","qbs_test@/qbs_test?charset=utf8&parseTime=true&loc=Local", "qbs_test", qbs.NewMysql()) }
-
If the field name is
Id
and field type isint64
, the field will be considered as the primary key of the table. if you want define a primary key with name other thanId
, you can set the tagqbs:"pk"
to explictly mark the field as primary key. -
The tag of
Name
fieldqbs:"size:32,index"
is used to define the column attributes when create the table, attributes are comma seperated, inside double quotes. -
The
size:32
tag on a string field will be translated to SQLvarchar(32)
, addindex
attribute to create a index on the column, addunique
attribute to create a unique index on the column -
Some DB (MySQL) can not create a index on string column without
size
defined.type User struct { Id int64 Name string `qbs:"size:32,index"` }
-
If you want to create multi column index, you should implement
Indexed
interface by define aIndexes
method like the following.func (*User) Indexes(indexes *qbs.Indexes){ //indexes.Add("column_a", "column_b") or indexes.AddUnique("column_a", "column_b") }
-
call
qbs.GetMigration
function to get a Migration instance, and then use it to create a table. -
When you create a table, if the table already exists, it will not recreate it, but looking for newly added columns or indexes in the model, and execute add column or add index operation.
-
It is better to do create table task at the start time, because the Migration only do incremental operation, it is safe to keep the table creation code in production enviroment.
-
CreateTableIfNotExists
expect a struct pointer parameter.func CreateUserTable() error{ migration, err := qbs.GetMigration() if err != nil { return err } defer migration.Close() return migration.CreateTableIfNotExists(new(User)) }
-
Suppose we are in a handle http function. call
qbs.GetQbs()
to get a instance. -
Be sure to close it by calling
defer q.Close()
after get it. -
qbs has connection pool, the default size is 100, you can call
qbs.ChangePoolSize()
to change the size.func GetUser(w http.ResponseWriter, r *http.Request){ q, err := qbs.GetQbs() if err != nil { fmt.Println(err) w.WriteHeader(500) return } defer q.Close() u, err := FindUserById(q, 6) data, _ := json.Marshal(u) w.Write(data) }
-
Call
Save
method to insert or update the row,if the primary key fieldId
has not been set,Save
would execute insert stamtment. -
If
Id
is set to a positive integer,Save
would query the count of the row to find out if the row already exists, if not then executeINSERT
statement. otherwise executeUPDATE
. -
Save
expects a struct pointer parameter.func CreateUser(q *qbs.Qbs) (*User,error){ user := new(User) user.Name = "Green" _, err := q.Save(user) return user,err }
-
If you want to get a row by
Id
, just assign theId
value to the model instance.func FindUserById(q *qbs.Qbs, id int64) (*User, error) { user := new(User) user.Id = id err := q.Find(user) return user, err }
-
Call
FindAll
to get multiple rows, it expects a pointer of slice, and the element of the slice must be a pointer of struct.func FindUsers(q *qbs.Qbs) ([]*User, error) { var users []*User err := q.Limit(10).Offset(10).FindAll(&users) return users, err }
-
If you want to add conditions other than
Id
, you should allWhere
method.WhereEqual("name", name)
is equivalent toWhere("name = ?", name)
, just a shorthand method. -
Only the last call to
Where
/WhereEqual
counts, so it is only applicable to define simple condition. -
Notice that the column name passed to
WhereEqual
method is lower case, by default, all the camel case field name and struct name will be converted to snake case in database storage, so whenever you pass a column name or table name parameter in string, it should be in snake case. -
You can change the convertion behavior by setting the 4 convertion function variable:
FieldNameToColumnName
,StructNameToTableName
,ColumnNameToFieldName
,TableNameToStructName
to your own function.func FindUserByName(q *qbs.Qbs, n string) (*User, error) { user := new(User) err := q.WhereEqual("name", n).Find(user) return user, err }
-
If you need to define more complex condition, you should call
Condition
method, it expects a*Condition
parameter. you can get a new condition instance by callingqbs.NewCondition
,qbs.NewEqualCondition
orqbs.NewInCondition
function. -
*Condition
instance hasAnd
,Or
... methods, can be called sequentially to construct a complex condition. -
Condition
method of Qbs instance should only be called once as well, it will replace previous condition defined byCondition
orWhere
methods.func FindUserByCondition(q *qbs.Qbs) (*User, error) { user := new(User) condition1 := qbs.NewCondition("id > ?", 100).Or("id < ?", 50).OrEqual("id", 75) condition2 := qbs.NewCondition("name != ?", "Red").And("name != ?", "Black") condition1.AndCondition(condition2) err := q.Condition(condition1).Find(user) return user, err }
-
To update a single row, you should call
Find
first, then update the model, andSave
it.func UpdateOneUser(q *qbs.Qbs, id int64, name string) (affected int64, error){ user, err := FindUserById(q, id) if err != nil { return 0, err } user.Name = name return q.Save(user) }
-
Call
Update
to update multiple rows at once, but you should call this method cautiously, if the the model struct contains all the columns, it will update every column, most of the time this is not what we want. -
The right way to do it is to define a temporary model struct in method or block, that only contains the column we want to update.
func UpdateMultipleUsers(q *qbs.Qbs)(affected int64, error) { type User struct { Name string } user := new(User) user.Name = "Blue" return q.WhereEqual("name", "Green").Update(user) }
-
call
Delete
method to delete a row, there must be at least one condition defined, either byId
value, or byWhere
/Condition
.func DeleteUser(q *qbs.Qbs, id int64)(affected int64, err error) { user := new(User) user.Id = id return q.Delete(user) }
-
For join query to work, you should has a pair of fields to define the join relationship in the model struct.
-
Here the model
Post
has aAuthorId
int64 field, and has aAuthor
field of type*User
. -
The rule to define join relationship is like
{xxx}Id int64
,{xxx} *{yyy}
. -
As the
Author
field is pointer type, it will be ignored when creating table. -
As
AuthorId
is a join column, a index of it will be created automatically when creating the table, so you don't have to addqbs:"index"
tag on it. -
You can also set the join column explicitly by add a tag
qbs:"join:Author"
to it for arbitrary field Name. hereAuthor
is the struct pointer field of the parent table model. -
To define a foreign key constraint, you have to explicitly add a tag
qbs:"fk:Author"
to the foreign key column, and an index will be created as well when creating table. -
Created time.Time
field will be set to the current time when insert a row,Updated time.Time
field will be set to current time when update the row. -
You can explicitly set tag
qbs:"created"
orqbs:"updated"
ontime.Time
field to get the functionality for arbitrary field name.type Post struct { Id int64 AuthorId int64 Author *User Content string Created time.Time Updated time.Time }
-
Sometimes we do not need to get every field of a model, especially for joined field (like
Author
field) or large field (likeContent
field). -
Omit them will get better performance.
func FindPostsOmitContentAndCreated(q *qbs.Qbs) ([]*Post, error) { var posts []*Post err := q.OmitFields("Content","Created").Find(&posts) return posts, err }
-
With
OmitJoin
, you can omit every join fields, return only the columns in a single table, and it can be used along withOmitFields
.func FindPostsOmitJoin(q *qbs.Qbs) ([]*Post, error) { var posts []*Post err := q.OmitJoin().OmitFields("Content").Find(&posts) return posts, err }
- a CMS system toropress
- Go documentation reference website Gowalker
- Nebri OS (https://nebrios.com/)
Erik Aigner Qbs was originally a fork from hood by Erik Aigner, but I changed more than 80% of the code, then it ended up become a totally different ORM.