GF Framework GDB-Elegant and powerful database orm

This is a creation in Article, where the information may have evolved or changed.

The GF Framework's database ORM operations are supported by the GDB package, which has been carefully designed to provide powerful configuration management, method operations, chained operations, transactional operations, and more. GDB package specific API documentation see: Godoc. This section provides a basic introduction to the use of the GDB package, including the basic features of the GDB package, configuration management function descriptions, common usage and common operating examples.

How to use:

import "gitee.com/johng/gf/g/database/gdb"

Database configuration

GDB Data structure:

type List        []Map                  // 数据记录列表 type Map         map[string]interface{} // 数据记录type Config      map[string]ConfigGroup // 数据库配置对象type ConfigGroup []ConfigNode           // 数据库分组配置// 数据库配置项(一个分组配置对应多个配置项)type ConfigNode  struct {    Host     string // 地址    Port     string // 端口    User     string // 账号    Pass     string // 密码    Name     string // 数据库名称    Type     string // 数据库类型：mysql, sqlite, mssql, pgsql, oracle(目前仅支持mysql,pgsql)    Role     string // (可选，默认为master)数据库的角色，用于主从操作分离，至少需要有一个master，参数值：master, slave    Charset  string // (可选，默认为 utf-8)编码，默认为 utf-8    Priority int    // (可选)用于负载均衡的权重计算，当集群中只有一个节点时，权重没有任何意义    Linkinfo string // (可选)自定义链接信息，当该字段被设置值时，以上链接字段(Host,Port,User,Pass,Name)将失效(该字段是一个扩展功能，参考sql.Open参数)}

The map and list are used for data table record operation, corresponding to one data table record and data table record list; config, Configgroup, and Confignode are used for database configuration management, Confignode is used to store a database node information, Configgroup is used to manage configuration groupings consisting of multiple database nodes (typically one grouping for a business database cluster), and config is used to manage multiple configgroup configuration groupings.

GDB Main Features:

1. Support multi-node database cluster management, using singleton mode to manage database instantiation object;
2. Support the group management of the database cluster, and obtain the instantiated database operation object according to the group name;
3. Supports multiple relational database management and can be configured via the Confignode.type attribute (currently supports only MySQL and pgsql databases);
4. Support Master-slave read/write separation, can be configured by the Confignode.role property;
5. Support client-side load balancing management, can be configured through the Confignode.priority property, the higher the value, the higher the priority;

In particular, the most important feature of GDB's configuration management is that all the database cluster information (in the same process) is uniformly maintained using the same configuration management module, and the different business database cluster configurations are configured and retrieved using different grouping names.

Configuration method

List of database configuration management methods:

// 添加一个数据库节点到指定的分组中func AddConfigNode(group string, node ConfigNode)// 添加一个配置分组到数据库配置管理中(同名覆盖)func AddConfigGroup(group string, nodes ConfigGroup)// 添加一个数据库节点到默认的分组中(默认为default，可修改)func AddDefaultConfigNode(node ConfigNode)// 添加一个配置分组到数据库配置管理中(默认分组为default，可修改)func AddDefaultConfigGroup(nodes ConfigGroup)// 设置数据库配置为定义的配置信息func SetConfig(c Config)// 设置默认的分组名称func SetDefaultGroup(groupName string)

The default grouping means that if you do not specify a configuration grouping name when you get a database object, GDB reads the configuration grouping by default. For example, gdb.Instance() you can get a default grouping of database Singleton objects.

As a simple practice, we can SetConfig customize the database global configuration through the configuration management method of the GDB package, for example:

gdb.SetConfig(gdb.Config {    "default" : gdb.ConfigGroup {        gdb.ConfigNode {            Host     : "127.0.0.1",            Port     : "3306",            User     : "root",            Pass     : "123456",            Name     : "test",            Type     : "mysql",            Role     : "master",            Priority : 100,        },        gdb.ConfigNode {            Host     : "127.0.0.2",            Port     : "3306",            User     : "root",            Pass     : "123456",            Name     : "test",            Type     : "mysql",            Role     : "master",            Priority : 100,        },    },})

Configuration file

Of course, GDB supports configuration files for configuration, which also facilitates configuration management of the project, as described in the ORM Advanced Usage section.

Database operations

GDB database operation method is more, detailed see Godoc, the following only some common methods are introduced.

Method action

SQL action method that returns the native standard library SQL object query (query string, args ... interface{}) (*sql. Rows, error) Exec (query string, args ... interface{}) (SQL. Result, error) Prepare (query string) (*sql. STMT, error)//data table record query://Query A single record, query multiple records, query individual field values (chained operation similarly) GetAll (query string, args ... interface{}) (List, error) GetOne ( Query string, args ... interface{}) (Map, error) GetValue (query string, args ... interface{}) (interface{}, error)//Turn on transaction operation B Egin () (*TX, error)//Data single operation insert (table string, Data Map) (SQL. Result, error) Replace (table string, Data Map) (SQL. Result, error) Save (table string, Data Map) (SQL. Result, error)//Data Bulk Operations Batchinsert (table string, list list, batch int) (SQL. Result, error) batchreplace (table string, list list, batch int) (SQL. Result, error) batchsave (table string, list list, batch int) (SQL. Result, error)//data modification/deletion update (table string, data interface{}, Condition interface{}, args ... interface{}) (SQL. Result, error) Delete (table string, Condition interface{}, args ... interface{}) (SQL. Result, error)//Create chain action pairLike (table is the alias of the From) table (tables String) (*dbop) from (Tables string) (*DBOP)//Shut down database close () error 

Need to explain the difference between the three insert/replace/save (Batchinsert/batchreplace/batchsave):

1. Insert: Writes the database using the INSERT INTO statement, and if there is a primary key or unique key in the written data, the return fails, otherwise a new data is written;
2. Replace: Use the REPLACE into statement for database writing, if there is primary key or unique key in the written data, delete the original record, write a new record according to the given data, or write a new data;
3. Save: Using the INSERT INTO statement for database writing, if there is primary key or unique key in the written data, update the original data, otherwise write a new data;

Chained operation

GDB provides a simple and flexible chain operation interface through DB of database objects. table/db. The tx.table/tx of the From method or transaction object. The From method returns a chained operand Dbop based on the specified data table, which can perform the following methods (refer to the API documentation for instructions).

func LeftJoin(joinTable string, on string) (*DbOp)func RightJoin(joinTable string, on string) (*DbOp)func InnerJoin(joinTable string, on string) (*DbOp)func Fields(fields string) (*DbOp)func Limit(start int, limit int) (*DbOp)func Data(data interface{}) (*DbOp)func Batch(batch int) *DbOpfunc Where(where string, args...interface{}) (*DbOp)func GroupBy(groupby string) (*DbOp)func OrderBy(orderby string) (*DbOp)func Insert() (sql.Result, error)func Replace() (sql.Result, error)func Save() (sql.Result, error)func Update() (sql.Result, error)func Delete() (sql.Result, error)func Select() (List, error)func All() (List, error)func One() (Map, error)func Value() (interface{}, error)

Database Example

Https://gitee.com/johng/gf/bl ...

Method action

2. Get an ORM Singleton object

   // 获取默认配置的数据库对象(配置名称为"default")db, err := gdb.Instance()// 获取配置分组名称为"user-center"的数据库对象db, err := gdb.Instance("user-center")

4. Data Write

   r, err := db.Insert("user", gdb.Map {    "name": "john",})

6. Data query (list)

   list, err := db.GetAll("select * from user limit 2")

8. Data Query (Single)

   one, err := db.GetOne("select * from user limit 2")// 或者one, err := db.GetOne("select * from user where uid=1000")

10. Data saving

    r, err := db.Save("user", gdb.Map {    "uid"  :  1,    "name" : "john",})

12. Bulk operations

    // BatchInsert/BatchReplace/BatchSave 同理_, err := db.BatchInsert("user", gdb.List {    {"name": "john_1"},    {"name": "john_2"},    {"name": "john_3"},    {"name": "john_4"},}, 10)

14. Data Update/delete

    // db.Update/db.Delete 同理r, err := db.Update("user", gdb.Map {"name": "john"}, "uid=?", 10000)r, err := db.Update("user", "name='john'", "uid=10000")r, err := db.Update("user", "name=?", "uid=?", "john", 10000)

    Note that parameter domains support and recommend the use of preprocessing mode for input, avoiding the risk of SQL injection.

Chained operation

2. Chained query

   // 查询多条记录并使用Limit分页r, err := db.Table("user u").LeftJoin("user_detail ud", "u.uid=ud.uid").Fields("u.*, ud.site").Where("u.uid > ?", 1).Limit(0, 10).Select()// 查询符合条件的单条记录(第一条)r, err := db.Table("user u").LeftJoin("user_detail ud", "u.uid=ud.uid").Fields("u.*,ud.site").Where("u.uid=?", 1).One()// 查询字段值r, err := db.Table("user u").LeftJoin("user_detail ud", "u.uid=ud.uid").Fields("ud.site").Where("u.uid=?", 1).Value()// 分组及排序r, err := db.Table("user u").LeftJoin("user_detail ud", "u.uid=ud.uid").Fields("u.*,ud.city").GroupBy("city").OrderBy("register_time asc").Select()

4. Chained Update/delete

   // 更新r, err := db.Table("user").Data(gdb.Map{"name" : "john2"}).Where("name=?", "john").Update()r, err := db.Table("user").Data("name='john3'").Where("name=?", "john2").Update()// 删除r, err := db.Table("user").Where("uid=?", 10).Delete()

6. Chained Write/Save

   r, err := db.Table("user").Data(gdb.Map{"name": "john"}).Insert()r, err := db.Table("user").Data(gdb.Map{"uid": 10000, "name": "john"}).Replace()r, err := db.Table("user").Data(gdb.Map{"uid": 10001, "name": "john"}).Save()

8. Chained Bulk Write

   r, err := db.Table("user").Data(gdb.List{    {"name": "john_1"},    {"name": "john_2"},    {"name": "john_3"},    {"name": "john_4"},}).Insert()

   You can specify the number of write-per-batch writes to the database in batch operations:

   r, err := db.Table("user").Data(gdb.List{    {"name": "john_1"},    {"name": "john_2"},    {"name": "john_3"},    {"name": "john_4"},}).Batch(2).Insert()

10. Chain-saving Batch

    r, err := db.Table("user").Data(gdb.List{    {"uid":10000, "name": "john_1"},    {"uid":10001, "name": "john_2"},    {"uid":10002, "name": "john_3"},    {"uid":10003, "name": "john_4"},}).Save()

Transactional operations

An open transaction operation can be performed by means of a method db.Begin that returns a transaction's operand, type *gdb.Tx , through which subsequent database operations are performed, and can be modified by tx.Commit committing, or by tx.Rollback rolling back.

2. Turn on transaction operations

   if tx, err := db.Begin(); err == nil {    fmt.Println("开启事务操作")}

   Transactional operations objects can execute methods of all DB objects, refer to the API documentation for details.

4. Transaction rollback Operations

   if tx, err := db.Begin(); err == nil {    r, err := tx.Save("user", gdb.Map{        "uid"  :  1,        "name" : "john",    })    tx.Rollback()    fmt.Println(r, err)}

6. Transaction commit operation

   if tx, err := db.Begin(); err == nil {    r, err := tx.Save("user", gdb.Map{        "uid"  :  1,        "name" : "john",    })    tx.Commit()    fmt.Println(r, err)}

8. Transactional chaining operations
   A transactional Operation object can still tx.Table tx.From return an object of a chained operation either through or through a method, which is the same as the db.Table db.From return value of the method, except that the database operation executes on the transaction and can be committed or rolled back.

   if tx, err := db.Begin(); err == nil {    r, err := tx.Table("user").Data(gdb.Map{"uid":1, "name": "john_1"}).Save()    tx.Commit()    fmt.Println(r, err)}

   Please refer to the chain Operation section above for other chain operation.