preface

Hello, I’m Asong.

Gendry, a third-party library, is a Go package for assisting with database operations. It is based on go-SQL-driver /mysql, which provides a series of methods to prepare parameters for calling methods in the standard library Database/SQL. For someone like me who doesn’t like to use the ORM framework, I can write dynamic SQL without using the ORM framework. Let me show you how to use this library!

Github address: github.com/didi/gendry

Initializing a connection

If you want to connect to a database, let’s start by looking at how to connect to a database directly from the standard library:

func NewMysqlClient(conf *config.Server) *sql.DB {
	connInfo := fmt.Sprintf("%s:%s@(%s)/%s? charset=utf8&parseTime=True&loc=Local", conf.Mysql.Username, conf.Mysql.Password, conf.Mysql.Host, conf.Mysql.Db)
	var err error
	db, err := sql.Open("mysql", connInfo)
	iferr ! =nil {
		fmt.Printf("init mysql err %v\n", err)
	}
	err = db.Ping()
	iferr ! =nil {
		fmt.Printf("ping mysql err: %v", err)
	}
	db.SetMaxIdleConns(conf.Mysql.Conn.MaxIdle)
	db.SetMaxOpenConns(conf.Mysql.Conn.Maxopen)
	db.SetConnMaxLifetime(5 * time.Minute)
	fmt.Println("init mysql successc")
	return db
}
Copy the code

As you can see from the above code, we need to concatenate the connection parameters ourselves, which requires us to remember the connection parameters all the time (for my memory idiot, I have to go to Baidu every time, which is very uncomfortable). Gendry provides us with a manager library to initialize the connection pool and set its various parameters. You can set any parameters supported by the Go-SQL-driver /mysql driver, so our initialization code can look like this:

func MysqlClient(conf *config.Mysql) *sql.DB {

	db, err := manager.
		New(conf.Db,conf.Username,conf.Password,conf.Host).Set(
		manager.SetCharset("utf8"),
		manager.SetAllowCleartextPasswords(true),
		manager.SetInterpolateParams(true),
		manager.SetTimeout(1 * time.Second),
		manager.SetReadTimeout(1 * time.Second),
			).Port(conf.Port).Open(true)

	iferr ! =nil {
		fmt.Printf("init mysql err %v\n", err)
	}
	err = db.Ping()
	iferr ! =nil {
		fmt.Printf("ping mysql err: %v", err)
	}
	db.SetMaxIdleConns(conf.Conn.MaxIdle)
	db.SetMaxOpenConns(conf.Conn.Maxopen)
	db.SetConnMaxLifetime(5 * time.Minute)
	// scanner.settagName ("json") // Global Settings, which can only be set once
	fmt.Println("init mysql successc")
	return db
}
Copy the code

What manager does is generate datasourceName for us, and it supports almost all of the parameters that the driver supports. We don’t need to worry about the format of datasourceName, just configure the parameters.

How to use it?

Below I will take you to a few demo study, more use methods can see the source code unlock (the reason why did not say to see the official document to solve: the document is not very detailed, but also to see the source code).

Database preparation

If you are writing sample code, you must first have a table to provide the test. The test table is as follows:

create table users
(
    id       bigint unsigned auto_increment
        primary key,
    username varchar(64)  default ' ' not null,
    nickname varchar(255) default ' ' null,
    password varchar(256) default ' ' not null,
    salt     varchar(48)  default ' ' not null,
    avatar   varchar(128)            null,
    uptime   bigint       default 0  not null.constraint username
        unique (username)
)
    charset = utf8mb4;
Copy the code

Well, the data table is also available, let’s start to show it, the following in accordance with the order of increase, delete, change and check show ~.

Insert data

Gendry provides three methods to help you construct insert SQL:

// BuildInsert work as its name says
func BuildInsert(table string, data []map[string]interface{}) (string, []interface{}, error) {
	return buildInsert(table, data, commonInsert)
}

// BuildInsertIgnore work as its name says
func BuildInsertIgnore(table string, data []map[string]interface{}) (string, []interface{}, error) {
	return buildInsert(table, data, ignoreInsert)
}

// BuildReplaceInsert work as its name says
func BuildReplaceInsert(table string, data []map[string]interface{}) (string, []interface{}, error) {
	return buildInsert(table, data, replaceInsert)
}

// BuildInsertOnDuplicateKey builds an INSERT.ON DUPLICATE KEY UPDATE clause.
func BuildInsertOnDuplicate(table string, data []map[string]interface{}, update map[string]interface{}) (string, []interface{}, error) {
	return buildInsertOnDuplicate(table, data, update)
}
Copy the code

BuildInsert: buildInsert: buildInsert: buildInsert: buildInsert

func (db *UserDB) Add(ctx context.Context,cond map[string]interface{}) (int64,error) {
	sqlStr,values,err := builder.BuildInsert(tplTable,[]map[string]interface{}{cond})
	iferr ! =nil{
		return 0,err
	}
	// TODO:DEBUG
	fmt.Println(sqlStr,values)
	res,err := db.cli.ExecContext(ctx,sqlStr,values...)
	iferr ! =nil{
		return 0,err
	}
	return res.LastInsertId()
}
// Unit tests are as follows:
func (u *UserDBTest) Test_Add(a)  {
	cond := map[string]interface{} {"username": "test_add"."nickname": "asong"."password": "123456"."salt": "oooo"."avatar": "http://www.baidu.com"."uptime": 123,
	}
	s,err := u.db.Add(context.Background(),cond)
	u.Nil(err)
	u.T().Log(s)
}
Copy the code

We put the data we want to insert into the map structure, the key is the field, the value is the value we want to insert, and we leave everything to BuildInsert. We have a lot less code. I’m sure you’re wondering how does this work? Don’t worry, we’ll decrypt it together later.

Delete the data

I like deleting data, I don’t know why, there is always a pleasure after deleting data…

To delete data, we can call the Builder. BuildDelete method directly, for example, we now want to delete the data we just inserted:

func (db *UserDB)Delete(ctx context.Context,where map[string]interface{}) error {
	sqlStr,values,err := builder.BuildDelete(tplTable,where)
	iferr ! =nil{
		return err
	}
	// TODO:DEBUG
	fmt.Println(sqlStr,values)
	res,err := db.cli.ExecContext(ctx,sqlStr,values...)
	iferr ! =nil{
		return err
	}
	affectedRows,err := res.RowsAffected()
	iferr ! =nil{
		return err
	}
	if affectedRows == 0{
		return errors.New("no record delete")}return nil
}

// Single test as follows:
func (u *UserDBTest)Test_Delete(a)  {
	where := map[string]interface{} {"username in": []string{"test_add"},
	}
	err := u.db.Delete(context.Background(),where)
	u.Nil(err)
}
Copy the code

In this case, the key is username in, and the gendry library supports this method. If the SQL has some operators, we can use this method to write, as follows:

where := map[string]interface{} {"Field operator": "value",}Copy the code

The official document provides the following supported operations:

= > <= >=! = <> in not in like not like between not betweenCopy the code

While we’re at it, let’s also mention the keywords gendry supports. The official documentation provides the following support:

_or
_orderby
_groupby
_having
_limit
_lockMode
Copy the code

Examples:

where := map[string]interface{} {"age >": 100."_or": []map[string]interface{}{
        {
            "x1":    11."x2 >=": 45}, {"x3":    "234"."x4 <>": "tx2",}},"_orderby": "fieldName asc"."_groupby": "fieldName"."_having": map[string]interface{} {"foo":"bar",},
    "_limit": []uint{offset, row_count},
    "_lockMode": "share",}Copy the code

Here are a few things to watch out for:

  • if_groupbyNot set will be ignored_having
  • _limitIt can be written like this:
    • "_limit": []uint{a,b}= >LIMIT a,b
    • "_limit": []uint{a}= >LIMIT 0,a
  • _lockModeOnly supported for nowshareandexclusive
    • shareRepresents theSELECT ... LOCK IN SHARE MODEUnfortunately, the current version does not support itSELECT ... FOR SHARE.
    • exclusiveRepresents theSELECT ... FOR UPDATE.

Update the data

Updating data can be done using the Builder.buildupdate method to build SQL statements, but note that it does not support _orderby, _groupby, _HAVING. Only this is what we need to pay attention to, other normal use is ok.


func (db *UserDB) Update(ctx context.Context,where map[string]interface{},data map[string]interface{}) error {
	sqlStr,values,err := builder.BuildUpdate(tplTable,where,data)
	iferr ! =nil{
		return err
	}
	// TODO:DEBUG
	fmt.Println(sqlStr,values)
	res,err := db.cli.ExecContext(ctx,sqlStr,values...)
	iferr ! =nil{
		return err
	}
	affectedRows,err := res.RowsAffected()
	iferr ! =nil{
		return err
	}
	if affectedRows == 0{
		return errors.New("no record update")}return nil
}
// Unit tests are as follows:
func (u *UserDBTest) Test_Update(a)  {
	where := map[string]interface{} {"username": "asong",
	}
	data := map[string]interface{} {"nickname": "shuai",
	}
	err := u.db.Update(context.Background(),where,data)
	u.Nil(err)
}
Copy the code

The input parameter becomes two, one to specify the where condition and the other to hold the data we want to update.

Query data

The query uses the Builder. BuildSelect method to build SQL statements.


func (db *UserDB) Query(ctx context.Context,cond map[string]interface{}) ([]*model.User,error) {
	sqlStr,values,err := builder.BuildSelect(tplTable,cond,db.getFiledList())
	iferr ! =nil{
		return nil, err
	}
	rows,err := db.cli.QueryContext(ctx,sqlStr,values...)
	defer func(a) {
		ifrows ! =nil{
			_ = rows.Close()
		}
	}()
	iferr ! =nil{
		if err == sql.ErrNoRows{
			return nil,errors.New("not found")}return nil,err
	}
	user := make([]*model.User,0)
	err = scanner.Scan(rows,&user)
	iferr ! =nil{
		return nil,err
	}
	return user,nil
}
// Unit tests
func (u *UserDBTest) Test_Query(a)  {
	cond := map[string]interface{} {"id in": []int{1.2},
	}
	s,err := u.db.Query(context.Background(),cond)
	u.Nil(err)
	for k,v := range s{
		u.T().Log(k,v)
	}
}
Copy the code

BuildSelect(table String, where map[string]interface{}, selectField []string) BuildSelect(table map[string]interface{}, selectField []string) SelectFiled is the field we want to query, if nil, the corresponding SQL statement is SELECT *… . This is how Gendry provides a mapped result set. Let’s take a look at how the library is used.

scanner

After the database operation is performed, the returned result set is mapped to the custom struct. Scanner provides a simple interface to bind result sets to custom types through reflection. This is what the Scanner.Scan method above does. The default tagName used is DDB :” XXX “, which you can also customize. Use scanner.settagName (“json”). Scaner. SetTagName is a global setting. To avoid ambiguity, only one setting is allowed.

Sometimes we don’t want to define a structure to store intermediate results, so Gendry also provides scanMap to use:

rows,_ := db.Query("select name,m_age from person")
result,err := scanner.ScanMap(rows)
for _,record := range result {
	fmt.Println(record["name"], record["m_age"])}Copy the code

Note the following when using scanner:

  • If you use Scan or ScanMap, you must manually close rows later
  • The passes to Scan must be references
  • ScanClose and ScanMapClose do not require manually closing rows

handwrittenSQL

For more complex queries, the Gendry method may not be able to meet our requirements, which may require us to customize our SQL. Gendry provides NamedQuery to do this, as follows:


func (db *UserDB) CustomizeGet(ctx context.Context,sql string,data map[string]interface{}) (*model.User,error) {
	sqlStr,values,err := builder.NamedQuery(sql,data)
	iferr ! =nil{
		return nil, err
	}
	// TODO:DEBUG
	fmt.Println(sql,values)
	rows,err := db.cli.QueryContext(ctx,sqlStr,values...)
	iferr ! =nil{
		return nil,err
	}
	defer func(a) {
		ifrows ! =nil{
			_ = rows.Close()
		}
	}()
	user := model.NewEmptyUser()
	err = scanner.Scan(rows,&user)
	iferr ! =nil{
		return nil,err
	}
	return user,nil
}
// Unit tests
func (u *UserDBTest) Test_CustomizeGet(a)  {
	sql := "SELECT * FROM users WHERE username={{username}}"
	data := map[string]interface{} {"username": "test_add",
	}
	user,err := u.db.CustomizeGet(context.Background(),sql,data)
	u.Nil(err)
	u.T().Log(user)
}
Copy the code

This is pure handwriting SQL, some complex places can be used in this way.

Aggregation query

Gendry also provides us with aggregated queries such as count,sum, Max,min,avg. Let’s take count as an example. Suppose we wanted to count the number of users with the same password, we could write:


func (db *UserDB) AggregateCount(ctx context.Context,where map[string]interface{},filed string) (int64,error) {
	res,err := builder.AggregateQuery(ctx,db.cli,tplTable,where,builder.AggregateCount(filed))
	iferr ! =nil{
		return 0, err
	}
	numberOfRecords := res.Int64()
	return numberOfRecords,nil
}
// Unit tests
func (u *UserDBTest) Test_AggregateCount(a)  {
	where := map[string]interface{} {"password": "123456",
	}
	count,err := u.db.AggregateCount(context.Background(),where,"*")
	u.Nil(err)
	u.T().Log(count)
}
Copy the code

So far, all the basic usage has been demonstrated, and more usage methods can be unlocked.

Cli tool

In addition to these apis, Gendry also provides a command line for code generation, which can significantly reduce your development effort. Gforge is a GenDry-based CLI tool that generates golang structures based on table names, which can lighten your load. Even GForge can generate an entire DAO layer for you.

The installation

go get -u github.com/caibirdme/gforge
Copy the code

Use gforge -h to verify that the installation is successful, and you will be prompted to use it.

Generate table structure

Table structures generated using GForge are passable to Golint and Govet. The generation instructions are as follows:

gforge table -uroot -proot1997 -h127. 0. 01. -dasong -tusers

// Users is a mapping object for users table in mysql
type Users struct {
	ID uint64 `json:"id"`
	Username string `json:"username"`
	Nickname string `json:"nickname"`
	Password string `json:"password"`
	Salt string `json:"salt"`
	Avatar string `json:"avatar"`
	Uptime int64 `json:"uptime"`
}
Copy the code

This saves us the time to customize the table structure or, more conveniently, generate the DAO layer directly.

generatedaofile

The operation instructions are as follows:

gforge dao -uroot -proot1997 -h127. 0. 01. -dasong -tusers | gofmt > dao.go
Copy the code

Here I put the generated DAO layer directly into the file, here will not stick specific code, no meaning, know how to use it.

decryption

I’m sure you’re as curious as I am about how Gendry works. Using builder.buildSelect as an example, let’s see how it does this. Other principles similar, interested in children’s shoes can see the source code to learn. Let’s take a look at the source of buildSelect.

func BuildSelect(table string, where map[string]interface{}, selectField []string) (cond string, vals []interface{}, err error) {
	var orderBy string
	var limit *eleLimit
	var groupBy string
	var having map[string]interface{}
	var lockMode string
	if val, ok := where["_orderby"]; ok {
		s, ok := val.(string)
		if! ok { err = errOrderByValueTypereturn
		}
		orderBy = strings.TrimSpace(s)
	}
	if val, ok := where["_groupby"]; ok {
		s, ok := val.(string)
		if! ok { err = errGroupByValueTypereturn
		}
		groupBy = strings.TrimSpace(s)
		if ""! = groupBy {if h, ok := where["_having"]; ok {
				having, err = resolveHaving(h)
				if nil! = err {return}}}}if val, ok := where["_limit"]; ok {
		arr, ok := val.([]uint)
		if! ok { err = errLimitValueTypereturn
		}
		if len(arr) ! =2 {
			if len(arr) == 1 {
				arr = []uint{0, arr[0]}}else {
				err = errLimitValueLength
				return
			}
		}
		begin, step := arr[0], arr[1]
		limit = &eleLimit{
			begin: begin,
			step:  step,
		}
	}
	if val, ok := where["_lockMode"]; ok {
		s, ok := val.(string)
		if! ok { err = errLockModeValueTypereturn
		}
		lockMode = strings.TrimSpace(s)
		if_, ok := allowedLockMode[lockMode]; ! ok { err = errNotAllowedLockModereturn
		}
	}
	conditions, err := getWhereConditions(where, defaultIgnoreKeys)
	if nil! = err {return
	}
	ifhaving ! =nil {
		havingCondition, err1 := getWhereConditions(having, defaultIgnoreKeys)
		if nil! = err1 { err = err1return
		}
		conditions = append(conditions, nilComparable(0))
		conditions = append(conditions, havingCondition...)
	}
	return buildSelect(table, selectField, groupBy, orderBy, lockMode, limit, conditions...)
}
Copy the code
  • Several keywords will be processed first.
  • And then it callsgetWhereConditionsThis is the way to constructsql, take a look at the internal implementation (excerpt) :
for key, val := range where {
		if _, ok := ignoreKeys[key]; ok {
			continue
		}
		if key == "_or" {
			var (
				orWheres          []map[string]interface{}
				orWhereComparable []Comparable
				ok                bool
			)
			if orWheres, ok = val.([]map[string]interface{}); ! ok {return nil, errOrValueType
			}
			for _, orWhere := range orWheres {
				if orWhere == nil {
					continue
				}
				orNestWhere, err := getWhereConditions(orWhere, ignoreKeys)
				if nil! = err {return nil, err
				}
				orWhereComparable = append(orWhereComparable, NestWhere(orNestWhere))
			}
			comparables = append(comparables, OrWhere(orWhereComparable))
			continue
		}
		field, operator, err = splitKey(key)
		if nil! = err {return nil, err
		}
		operator = strings.ToLower(operator)
		if! isStringInSlice(operator, opOrder) {return nil, ErrUnsupportedOperator
		}
		if _, ok := val.(NullType); ok {
			operator = opNull
		}
		wms.add(operator, field, val)
	}
Copy the code

This section is slice traversed, the previously processed portion of the keyword is ignored, and the _or keyword is recursively processed to retrieve all conditional data. After that, there’s nothing special to say. I’ll go back to the buildSelect method myself, and after processing the WHERE condition, I’ll do a filter if I have a HAVING condition, and then I’ll call the buildSelect method to construct the final SQL statement when all the data is built.

conclusion

Read the source code, just want to say: big guy is big guy. Source code is actually very easy to understand, this is not to do a detailed analysis, the main idea is worth learning, we suggest that we can look at gendry source code, increase knowledge ~ ~.

Recommended previous articles:

  • Mechanics-go Asynchronous task queues
  • Leaf-segment Distributed ID Generation System (Golang implementation version)
  • 10 GIFs to help you understand sorting algorithms (with go implementation code)
  • Go Book Recommendations (From Getting Started to Giving up)
  • Go parameter transfer type
  • Teach my sister how to write message queues
  • Cache avalanche, cache penetration, cache breakdown
  • Context package, read this article enough!!
  • Go -ElasticSearch: How to get started
  • Interviewer: Have you used for-range in go? Can you explain the reasons for these problems
  • Learn wire dependency injection, Cron timing task is actually so easy!
  • I heard you don’t know how to JWT or swagger. – I’m skipping meals and I’m here with my practice program