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
QueryStructmethod 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
Iteratemethod 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.Registerfunction 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
Idand 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
Namefieldqbs:"size:32,index"is used to define the column attributes when create the table, attributes are comma seperated, inside double quotes. -
The
size:32tag on a string field will be translated to SQLvarchar(32), addindexattribute to create a index on the column, adduniqueattribute to create a unique index on the column -
Some DB (MySQL) can not create a index on string column without
sizedefined.type User struct { Id int64 Name string `qbs:"size:32,index"` } -
If you want to create multi column index, you should implement
Indexedinterface by define aIndexesmethod like the following.func (*User) Indexes(indexes *qbs.Indexes){ //indexes.Add("column_a", "column_b") or indexes.AddUnique("column_a", "column_b") }
-
call
qbs.GetMigrationfunction 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.
-
CreateTableIfNotExistsexpect 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
Savemethod to insert or update the row，if the primary key fieldIdhas not been set,Savewould execute insert stamtment. -
If
Idis set to a positive integer,Savewould query the count of the row to find out if the row already exists, if not then executeINSERTstatement. otherwise executeUPDATE. -
Saveexpects 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 theIdvalue 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
FindAllto get multiple rows, it expects a pointer 9EBD 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 allWheremethod.WhereEqual("name", name)is equivalent toWhere（"name = ?", name), just a shorthand method. -
Only the last call to
Where/WhereEqualcounts, so it is only applicable to define simple condition. -
Notice that the column name passed to
WhereEqualmethod 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,TableNameToStructNameto 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
Conditionmethod, it expects a*Conditionparameter. you can get a new condition instance by callingqbs.NewCondition,qbs.NewEqualConditionorqbs.NewInConditionfunction. -
*Conditioninstance hasAnd,Or... methods, can be called sequentially to construct a complex condition. -
Conditionmethod of Qbs instance should only be called once as well, it will replace previous condition defined byConditionorWheremethods.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
Findfirst, then update the model, andSaveit.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
Updateto 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
Deletemethod to delete a row, there must be at least one condition defined, either byIdvalue, 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
Posthas aAuthorIdint64 field, and has aAuthorfield of type*User. -
The rule to define join relationship is like
{xxx}Id int64,{xxx} *{yyy}. -
As the
Authorfield is pointer type, it will be ignored when creating table. -
As
AuthorIdis 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. hereAuthoris 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.Timefield will be set to the current time when insert a row,Updated time.Timefield will be set to current time when update the row. -
You can explicitly set tag
qbs:"created"orqbs:"updated"ontime.Timefield 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
Authorfield) or large field (likeContentfield). -
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.