sql

This is a historical learning note and may contain outdated or incomplete understanding.

1. What It Is

2. What It Contains

2.1. Connections

DB is safe for concurrent use.
func Open(driverName, dataSourceName string) (*DB, error): Open only validates the connection-string format; it does not open a connection. Use Ping.
func (db *DB) Ping() error: creates a connection when needed and verifies that the connection is alive.
0 < MaxidleConnection < MaxOpenConnection.
func (db *DB) Close() error closes a connection and waits for all queries on that connection to finish. It is rarely used.

2.2. CRUD

func (db *DB) Exec(query string, args ...interface{}) (Result, error) executes CUD SQL. The second parameter corresponds to placeholders and should be passed with args.... It returns Result, which wraps lastInsertedId and rowsAffected.
func (db *DB) QueryRow(query string, args ...interface{}) *Row queries at most one row. It returns a non-nil Row, but Row.Scan must be called. If multiple rows exist, only the first is used; if none exist, an error is returned.
func (db *DB) Query(query string, args ...interface{}) (*Rows, error) queries multiple rows and returns a non-nil Rows. Call Next to check whether another row exists, then Rows.Scan to copy columns into destination values.
- During Scan, if one field is wrong, the later fields are set to zero values.
- Scan cannot directly handle NULL values; fields need types such as sql.NullInt64.
func (db *DB) Prepare(query string) (*Stmt, error) prepares SQL and returns a Stmt, which must be closed.

2.3. Transactions

func (db *DB) Begin() (*Tx, error) starts a transaction and returns a Tx. The default isolation level depends on the driver.
func (tx *Tx) Commit() error commits the transaction.
func (tx *Tx) Rollback() error rolls back the transaction.
Tx also has QueryRow, Query, and Exec methods.

3. Prepared Statements

SQL preparation has two purposes: preventing SQL injection and improving performance through caching.

The reason it can prevent injection is that after compilation the syntax tree is fixed and cannot be changed, so injected syntax cannot alter it.

The performance benefit comes from the database not needing to repeatedly parse SQL and generate execution plans. This requires support from the client-side driver library for caching.

The go-sql-driver parameter InterpolateParams defaults to false. In this mode, prepared statements are enabled and there are three interactions with the server. If it is true, placeholders (?) in the SQL are directly replaced with concrete values. This requires only one interaction with the server, but there is no server-side preparation, so injection can become a concern. go-sql-driver escapes values on the client side, which addresses part of the problem, although multi-encoding environments can still be problematic. Bytes are UTF-8 by default, so this issue normally does not occur; GORM therefore sets this value to true by default.

go-sql-driver itself does not cache prepared statements. GORM provides this through PrepareStmt. When PrepareStmt=true, GORM uses go-sql-driver to prepare the SQL and then caches the prepared statement locally so that it can be reused next time.

4. Other Libraries

4.1. sql-builder

package sqlbuilder_demo

import (
	"database/sql"
	"fmt"
	_ "github.com/go-sql-driver/mysql"
	"github.com/huandu/go-sqlbuilder"
	"time"
)

var db *sql.DB
var userStruct = sqlbuilder.NewStruct(new(TbUser))

//type TbUser struct {
//	Id       sql.NullInt64         `db:"id"`
//	Username string        `db:"username"`
//	Password string        `db:"password"`
//	Level    sql.NullInt64 `db:"level"`
//	Created  int64    `db:"created"`
//	Updated  int64    `db:"updated"`
//
//}

type TbUserVo struct {
	Collection string
	Created    int64
	Email      string
	Id         int64
	Level      int64
	Password   string
	Perms      string
	Phone      string
	Updated    int64
	Username   string
}

type TbUser struct {
	Collection sql.NullString `db:"collection"`
	Created    time.Time      `db:"created"`
	Email      sql.NullString `db:"email"`
	Id         int64          `db:"id"`
	Level      sql.NullInt64  `db:"level"`
	Password   string         `db:"password"`
	Perms      sql.NullString `db:"perms"`
	Phone      sql.NullString `db:"phone"`
	Updated    time.Time      `db:"updated"`
	Username   string         `db:"username"`
}

func SelectDemo() {
	selectBuilder := sqlbuilder.NewSelectBuilder()
	selectBuilder.Select("id", "username")
	selectBuilder.From("tb_user")
	selectBuilder.Where(selectBuilder.In("phone", "<PHONE_1>", "<PHONE_2>"))
	sql, args := selectBuilder.Build()
	fmt.Println(sql, args)
}

func UserCountSql() {

	builder := sqlbuilder.NewSelectBuilder()
	builder.Select("count(*)")
	builder.From("tb_user")
	builder.Where(builder.GreaterThan("id", 1))
	sql, args := builder.Build()
	fmt.Println(sql, args)
	row := db.QueryRow(sql, args...)
	var count int
	err := row.Scan(&count)
	if err != nil {
		fmt.Println(err)
	}
	fmt.Println(count)
}

func UserListSql() {
	// Query all fields by default.
	builder := userStruct.SelectFrom("tb_user")
	builder.Where(builder.GreaterThan("id", 10))
	sql, args := builder.Build()
	fmt.Println(sql, args)
	rows, err := db.Query(sql, args...)
	if err != nil {
		fmt.Println(err)
		return
	}

	users := make([]TbUser, 0)
	for rows.Next() {
		var user TbUser
		// Use reflection to obtain all fields.
		err = rows.Scan(userStruct.Addr(&user)...)
		//err = rows.Scan(&user.Id, &user.Level,&user.Password)
		if err != nil {
			fmt.Println(err)
			continue
		}
		users = append(users, user)
	}

	fmt.Println(users)
}

func UserInsertSql(user TbUser) {
	// No need to specify columns and values.
	builder := userStruct.InsertInto("tb_user", user)
	// They can also be specified explicitly.
	//builder.Cols("id", "username", "password", "created", "updated")
	//builder.Values(user.Id, user.Username, user.Password, user.Created, user.Updated)
	sql, args := builder.Build()
	fmt.Println(sql, args)
	result, err := db.Exec(sql, args...)
	if err != nil {
		fmt.Println(err)
		return
	}
	fmt.Println(result.LastInsertId())
}

func UserDeleteSql() {

	builder := userStruct.DeleteFrom("tb_user")
	builder.Where(builder.Equal("id", 1))
	sql, args := builder.Build()
	fmt.Println(sql, args)
	result, err := db.Exec(sql, args...)
	if err != nil {
		fmt.Println(err)
		return
	}
	fmt.Println(result.RowsAffected())
}

func UserUpdateSqlSelective(userVo TbUserVo) {
	updateBuilder := sqlbuilder.NewUpdateBuilder()
	updateBuilder.Update("tb_user")
	updateBuilder.SetMore(updateBuilder.Assign("email", sql.NullString{
		String: userVo.Email,
		Valid:  true,
	}))

	if userVo.Collection != "" {
		updateBuilder.SetMore(updateBuilder.Assign("collection", sql.NullString{
			String: userVo.Collection,
			Valid:  true,
		}))
	}

	if userVo.Level != 0 {
		updateBuilder.SetMore(updateBuilder.Assign("level", sql.NullInt64{
			Int64: userVo.Level,
			Valid: true,
		}))
	}
	if userVo.Password != "" {
		updateBuilder.SetMore(updateBuilder.Assign("password", userVo.Password))
	}
	if userVo.Phone != "" {
		updateBuilder.SetMore(updateBuilder.Assign("phone", sql.NullString{
			String: userVo.Phone,
			Valid:  true,
		}))
	}

	if userVo.Perms != "" {
		updateBuilder.SetMore(updateBuilder.Assign("perms", sql.NullString{
			String: userVo.Perms,
			Valid:  true,
		}))
	}
	updateBuilder.SetMore(updateBuilder.Assign("updated", time.Now()))
	updateBuilder.SetMore(updateBuilder.Assign("created", time.Now()))

	updateBuilder.Where(updateBuilder.Equal("id", userVo.Id))
	sql, args := updateBuilder.Build()
	fmt.Println(sql, args)
	result, err := db.Exec(sql, args...)
	if err != nil {
		fmt.Println(err)
		return
	}
	fmt.Println(result.RowsAffected())
}

func UserUpdateSql(user TbUser) {
	builder := userStruct.Update("tb_user", user)
	builder.Where(builder.Equal("id", 666))
	sql, args := builder.Build()
	fmt.Println(sql, args)
	result, err := db.Exec(sql, args...)
	if err != nil {
		fmt.Println(err)
		return
	}
	fmt.Println(result.RowsAffected())
}

func TestTx() {
	// Add a timeout.
	ctx, cancel := context.WithTimeout(context.Background(), time.Duration(2)*time.Second)
	defer cancel()
	// Start the transaction.
	tx, err := db.BeginTx(ctx, nil)
	if err != nil {
		fmt.Println(err)
	}
	_, err = tx.Exec("insert into ttest (status, creater, created) values (?, ?, ?)", "test", "user1", time.Now())
	if err != nil {
		fmt.Println(err)
		tx.Rollback()
		return
	}
	_, err = tx.Exec("insert into ttest (status, creater, created) values (?, ?, ?)", "test2", "user2", time.Now())
	if err != nil {
		fmt.Println(err)
		tx.Rollback()
		return
	}

	// Simulate other work that does not execute SQL.
	// If it times out, it is automatically rolled back.
	// Do not put time-consuming tasks inside a transaction, because the connection cannot be released.
	time.Sleep(time.Duration(5) * time.Second)

	if err := tx.Commit(); err != nil {
		fmt.Println(err)
		tx.Rollback()
	}

	// A committed transaction cannot be committed or rolled back again.
	//if err := tx.Commit(); err != nil {
	//	fmt.Println(err)//sql: transaction has already been committed or rolled back
	//	if err := tx.Rollback(); err != nil {
	//		fmt.Println(err)//sql: transaction has already been committed or rolled back
	//	}
	//}
}

func init() {
	var err error
	db, err = sql.Open("mysql", "root:<PASSWORD>@(127.0.0.1:3306)/tao?charset=utf8")
	if err != nil {
		panic(err)
	}
}

4.2. xorm

4.2.1. reverse tool

4.2.1.1. Installation

go get xorm.io/reverse
go get github.com/go-sql-driver/mysql

4.2.1.2. Usage

Configuration file tao.yml

kind: reverse
name: mydb
source:
  database: mysql
  conn_str: 'root:<PASSWORD>@(127.0.0.1:3306)/tao?charset=utf8'
targets:
  - type: codes
    language: golang
    output_dir: ./

Command

cd models
reverse -f tao.yml