GF Framework GDB-powerful and easy to use 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 provides a very powerful configuration management, method operation, chain operation and other functions. 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 operations examples.

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 (q string, args ... interface{}) (*sql. Rows, error) Exec (q string, args ... interface{}) (SQL. Result, error) Prepare (Q string) (*sql. STMT, error)//data table record query://Query A single record, query multiple records, query individual field values (chained operation similarly) GetAll (q string, args ... interface{}) (List, error) GetOne (q String, args ... interface{}) (Map, error) GetValue (q string, args ... interface{}) (interface{}, error)//transaction operation Begin () (*sql . Tx, error) Commit () errorrollback () 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 a chained Action object (table is an alias for from) table (tables String) (*glinkop) from (Tables string) (*glinkop)//Close database closing () 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-operated interface via GDB. Table/gdb. The From method returns a chained operand Glinkop based on the specified data table, which is defined as a non-public object and can perform the following methods.

func LeftJoin(joinTable string, on string) (*gLinkOp)func RightJoin(joinTable string, on string) (*gLinkOp)func InnerJoin(joinTable string, on string) (*gLinkOp)func Fields(fields string) (*gLinkOp)func Limit(start int, limit int) (*gLinkOp)func Data(data interface{}) (*gLinkOp)func Batch(batch int) *gLinkOpfunc Where(where string, args...interface{}) (*gLinkOp)func GroupBy(groupby string) (*gLinkOp)func OrderBy(orderby string) (*gLinkOp)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.

16. Transactional operations

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

    The transaction operation is very simple and requires only 3 methods of Begin/commit/rollback.

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()

12. Transaction operation

     db. Begin () r, Err: = db. Table ("User"). Data (GDB. map{"UID": 1, "name": "John_1"}). Save () If Err = = nil {db.commit ()} else {db. Rollback ()}