Sqlx Library Usage Guide

In projects we may usually use database/sql to connect to MySQL databases. This article introduces the sqlx.In and DB.NamedExec methods in sqlx that you may have overlooked, with the help of an example of using sqlx to achieve bulk insert data.

Introduction to sqlx

In a project we may usually use database/sql to connect to a MySQL database. sqlx can be thought of as a superset of the Go language’s built-in database/sql, which provides a set of extensions to the excellent built-in database/sql. Among these extensions are Get(dest interface{}, ...)', which is commonly used to query error and Select(dest interface{}, ...) error, there are many other powerful features.

Install sqlx

`1`	`go get github.com/jmoiron/sqlx`

Basic Use

Connecting to the database

var db *sqlx.DB

func initDB() (err error) {
	dsn := "user:password@tcp(127.0.0.1:3306)/sql_test?charset=utf8mb4&parseTime=True"
	// You can also use MustConnect to panic if the connection is not successful.
	db, err = sqlx.Connect("mysql", dsn)
	if err != nil {
		fmt.Printf("connect DB failed, err:%v\n", err)
		return
	}
	db.SetMaxOpenConns(20)
	db.SetMaxIdleConns(10)
	return
}

Query

The sample code for querying a single line of data is as follows.

func queryRowDemo() {
	sqlStr := "select id, name, age from user where id=?"
	var u user
	err := db.Get(&u, sqlStr, 1)
	if err != nil {
		fmt.Printf("get failed, err:%v\n", err)
		return
	}
	fmt.Printf("id:%d name:%s age:%d\n", u.ID, u.Name, u.Age)
}

The sample code for querying multiple rows of data is as follows.

func queryMultiRowDemo() {
	sqlStr := "select id, name, age from user where id > ?"
	var users []user
	err := db.Select(&users, sqlStr, 0)
	if err != nil {
		fmt.Printf("query failed, err:%v\n", err)
		return
	}
	fmt.Printf("users:%#v\n", users)
}

Insert, update and delete

The exec method in sqlx is basically the same as the use of exec in native sql

// Insert
func insertRowDemo() {
	sqlStr := "insert into user(name, age) values (?,?)"
	ret, err := db.Exec(sqlStr, "foo", 19)
	if err != nil {
		fmt.Printf("insert failed, err:%v\n", err)
		return
	}
	theID, err := ret.LastInsertId() // Self-incrementing ID
	if err != nil {
		fmt.Printf("get lastinsert ID failed, err:%v\n", err)
		return
	}
	fmt.Printf("insert success, the id is %d.\n", theID)
}

// Update
func updateRowDemo() {
	sqlStr := "update user set age=? where id = ?"
	ret, err := db.Exec(sqlStr, 39, 6)
	if err != nil {
		fmt.Printf("update failed, err:%v\n", err)
		return
	}
	n, err := ret.RowsAffected() // Number of rows affected by the operation
	if err != nil {
		fmt.Printf("get RowsAffected failed, err:%v\n", err)
		return
	}
	fmt.Printf("update success, affected rows:%d\n", n)
}

// Delete
func deleteRowDemo() {
	sqlStr := "delete from user where id = ?"
	ret, err := db.Exec(sqlStr, 6)
	if err != nil {
		fmt.Printf("delete failed, err:%v\n", err)
		return
	}
	n, err := ret.RowsAffected() // Number of rows affected by the operation
	if err != nil {
		fmt.Printf("get RowsAffected failed, err:%v\n", err)
		return
	}
	fmt.Printf("delete success, affected rows:%d\n", n)
}

NamedExec

The DB.NamedExec method is used to bind a SQL statement to a field of the same name in a structure or map.

func insertUserDemo()(err error){
	sqlStr := "INSERT INTO user (name,age) VALUES (:name,:age)"
	_, err = db.NamedExec(sqlStr,
		map[string]interface{}{
			"name": "foo",
			"age": 28,
		})
	return
}

NamedQuery

Same as DB.NamedExec, here is the support query.

func namedQuery(){
	sqlStr := "SELECT * FROM user WHERE name=:name"
	// Using map for named queries
	rows, err := db.NamedQuery(sqlStr, map[string]interface{}{"name": "七米"})
	if err != nil {
		fmt.Printf("db.NamedQuery failed, err:%v\n", err)
		return
	}
	defer rows.Close()
	for rows.Next(){
		var u user
		err := rows.StructScan(&u)
		if err != nil {
			fmt.Printf("scan failed, err:%v\n", err)
			continue
		}
		fmt.Printf("user:%#v\n", u)
	}

	u := user{
		Name: "七米",
	}
	// Use structure named queries to map structure fields based on their db tags
	rows, err = db.NamedQuery(sqlStr, u)
	if err != nil {
		fmt.Printf("db.NamedQuery failed, err:%v\n", err)
		return
	}
	defer rows.Close()
	for rows.Next(){
		var u user
		err := rows.StructScan(&u)
		if err != nil {
			fmt.Printf("scan failed, err:%v\n", err)
			continue
		}
		fmt.Printf("user:%#v\n", u)
	}
}

Transaction

For transactional operations, we can use the db.Beginx() and tx.Exec() methods provided in sqlx. The sample code is as follows.

func transactionDemo2()(err error) {
	tx, err := db.Beginx() // Start a transaction
	if err != nil {
		fmt.Printf("begin trans failed, err:%v\n", err)
		return err
	}
	defer func() {
		if p := recover(); p != nil {
			tx.Rollback()
			panic(p) // re-throw panic after Rollback
		} else if err != nil {
			fmt.Println("rollback")
			tx.Rollback() // err is non-nil; don't change it
		} else {
			err = tx.Commit() // err is nil; if Commit returns error update err
			fmt.Println("commit")
		}
	}()

	sqlStr1 := "Update user set age=20 where id=?"

	rs, err := tx.Exec(sqlStr1, 1)
	if err!= nil{
		return err
	}
	n, err := rs.RowsAffected()
	if err != nil {
		return err
	}
	if n != 1 {
		return errors.New("exec sqlStr1 failed")
	}
	sqlStr2 := "Update user set age=50 where i=?"
	rs, err = tx.Exec(sqlStr2, 5)
	if err!=nil{
		return err
	}
	n, err = rs.RowsAffected()
	if err != nil {
		return err
	}
	if n != 1 {
		return errors.New("exec sqlStr1 failed")
	}
	return err
}

`sqlx.In`

sqlx.In is a very handy function provided by sqlx.

Bulk insert example for sqlx.

Table Structure

To demonstrate the insert data operation, a user table is created with the following table structure

CREATE TABLE `user` (
    `id` BIGINT(20) NOT NULL AUTO_INCREMENT,
    `name` VARCHAR(20) DEFAULT '',
    `age` INT(11) DEFAULT '0',
    PRIMARY KEY(`id`)
)ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4;

struct

Define a user structure with fields that are consistent with the columns of the user table in the database via tag.

type User struct {
	Name string `db:"name"`
	Age  int    `db:"age"`
}

bindvars

Query placeholders? is known internally as bindvars (query placeholders), and it is very important. You should always use them to send values to the database, as they prevent SQL injection attacks. database/sql does not attempt any validation of the query text; it is sent to the server as is, along with the encoded parameters. Unless the driver implements a special interface, the query is prepared on the server before execution. Therefore bindvars is database-specific:

MySQL uses ?
PostgreSQL uses enumerated $1, $2 and other bindvar syntax
SQLite uses ? and $1 syntax are supported
Oracle uses the :name syntax

A common misconception about bindvars is that they are used to insert values into sql statements. They are actually only used for parameterization and are not allowed to change the structure of the SQL statement. For example, using bindvars to try to parameterize column or table names will not work.

// ? Cannot be used to insert table names (as placeholders for table names in SQL statements)
db.Query("SELECT * FROM ?", "mytable")
 
// ? Nor can it be used to insert column names (as placeholders for column names in SQL statements)
db.Query("SELECT ?, ? FROM people", "name", "location")

Splice your own statements to achieve bulk insertion

It’s rather dumb, but it’s well understood. It’s just splicing as many Users as there are (? , ?) .

// BatchInsertUsers 
func BatchInsertUsers(users []*User) error {
	// Store (? , ?) of the slice
	valueStrings := make([]string, 0, len(users))
	// The slice where the values are stored
	valueArgs := make([]interface{}, 0, len(users) * 2)
	// Iterate through users to prepare relevant data
	for _, u := range users {
		// Here the placeholders should correspond to the number of inserted values
		valueStrings = append(valueStrings, "(?, ?)")
		valueArgs = append(valueArgs, u.Name)
		valueArgs = append(valueArgs, u.Age)
	}
	// The specific statements to be executed by self-splicing
	stmt := fmt.Sprintf("INSERT INTO user (name, age) VALUES %s",
		strings.Join(valueStrings, ","))
	_, err := DB.Exec(stmt, valueArgs...)
	return err
}

Bulk insert using sqlx.

The prerequisite is that our structure needs to implement the driver.Valuer interface.

1
2
3

func (u User) Value() (driver.Value, error) {
	return []interface{}{u.Name, u.Age}, nil
}

The bulk insert code using sqlx.In is as follows.

// BatchInsertUsers2 uses sqlx.In to help us splice statements and parameters, note that the parameters passed in are []interface{}
func BatchInsertUsers2(users []interface{}) error {
	query, args, _ := sqlx.In(
		"INSERT INTO user (name, age) VALUES (?), (?), (?)",
		users..., // If arg implements driver.Valuer, sqlx.In will expand it by calling Value()
	)
	fmt.Println(query) // View the generated querystring
	fmt.Println(args)  // View the generated args
	_, err := DB.Exec(query, args...)
	return err
}

Bulk insertion using NamedExec

Note: This feature requires 1.3.1 version or higher, and 1.3.1 version currently has some problems, sql statements can not have spaces and ; at the end, see issues/690 for details.

The code to achieve bulk insertion using NamedExec is as follows.

func BatchInsertUsers3(users []*User) error {
	_, err := DB.NamedExec("INSERT INTO user (name, age) VALUES (:name, :age)", users)
	return err
}

Try a combination of the three methods above.

func main() {
	err := initDB()
	if err != nil {
		panic(err)
	}
	defer DB.Close()
	u1 := User{Name: "foo", Age: 18}
	u2 := User{Name: "bar", Age: 28}
	u3 := User{Name: "zoo", Age: 38}

	// Method 1
	users := []*User{&u1, &u2, &u3}
	err = BatchInsertUsers(users)
	if err != nil {
		fmt.Printf("BatchInsertUsers failed, err:%v\n", err)
	}

	// Method 2
	users2 := []interface{}{u1, u2, u3}
	err = BatchInsertUsers2(users2)
	if err != nil {
		fmt.Printf("BatchInsertUsers2 failed, err:%v\n", err)
	}

	// Method 3
	users3 := []*User{&u1, &u2, &u3}
	err = BatchInsertUsers3(users3)
	if err != nil {
		fmt.Printf("BatchInsertUsers3 failed, err:%v\n", err)
	}
}

Query example for `sqlx.In`

Here is another usage of sqlx.In, which implements the In query and the FIND_IN_SET function in the sqlx query statement. That is, implement SELECT * FROM user WHERE id in (3, 2, 1); and SELECT * FROM user WHERE id in (3, 2, 1) ORDER BY FIND_IN_SET(id, '3,2,1');.

in Query

Query the data whose id is in the given id set.

// QueryByIDs
func QueryByIDs(ids []int)(users []User, err error){
	// Dynamic fill id
	query, args, err := sqlx.In("SELECT name, age FROM user WHERE id IN (?)", ids)
	if err != nil {
		return
	}
	// sqlx.In returns a query statement with `? ` bindvar query statement, which we rebind using Rebind()
	query = DB.Rebind(query)

	err = DB.Select(&users, query, args...)
	return
}

in query and FIND_IN_SET function

Query the data whose ids are in the given id set and maintain the order of the given id set.

// QueryAndOrderByIDs 
func QueryAndOrderByIDs(ids []int)(users []User, err error){
	// Dynamic fill id
	strIDs := make([]string, 0, len(ids))
	for _, id := range ids {
		strIDs = append(strIDs, fmt.Sprintf("%d", id))
	}
	query, args, err := sqlx.In("SELECT name, age FROM user WHERE id IN (?) ORDER BY FIND_IN_SET(id, ?)", ids, strings.Join(strIDs, ","))
	if err != nil {
		return
	}

	// sqlx.In returns a query statement with `? ` bindvar query statement, which we rebind using Rebind()
	query = DB.Rebind(query)

	err = DB.Select(&users, query, args...)
	return
}

Of course, in this example you can also use the IN query first, and then sort the query results by the given ids through the code.

Reference https://www.liwenzhou.com/posts/Go/sqlx/

Table of Contents