Sqlx package for MySQL in go language

Time:2021-9-10

Friendly tips:This article takes about 14 minutes and 5 seconds to read. Please give more advice on the shortcomings. Thank you for reading.Subscribe to this site

Sqlx Library

sqlxIt’s go’s software package, which is built in excellentdatabase/sqlA set of extensions is provided based on the software package.

The library is compatiblesqlThe native package also provides more powerful and elegant query and insert functions.

The library provides four processing types:

  • sqlx.DB – Similar to native  sql.DB
  • sqlx.Tx – Similar to native  sql.Tx
  • sqlx.Stmt – Similar to nativesql.Stmt, prepare SQL statement operation;
  • sqlx.NamedStmt – Name and bind specific parameters to generate SQL statement operations.

Two cursor types are provided:

  • sqlx.Rows – Similar to native  sql.Rows, from  Queryxreturn;
  • sqlx.Row  – Similar to native  sql.Row, from  QueryRowxreturn.

Install sqlx Library

go get github.com/jmoiron/sqlx

Use operation

Connect to database

//Initialize database
func initMySQL() (err error) {
    dsn := "root:[email protected](127.0.0.1:3306)/database"
    db, err = sqlx.Open("mysql", dsn)
    if err != nil {
        fmt.Printf("connect server failed, err:%v\n", err)
        return
    }
    db.SetMaxOpenConns(200)
    db.SetMaxIdleConns(10)
    return
}

SetMaxOpenConnsandSetMaxIdleConnsSet the maximum number of connections and the maximum number of idle connections respectively.

Data expression and reference

A user structure is declared here in advanceuser, will*sqlx.DBAs a global variable, of course, you should also refer to the MySQL driver package in advance. The design is as follows:

import (
    "fmt"
    _ "github.com/go-sql-driver/mysql"
    "github.com/jmoiron/sqlx"
)

var db *sqlx.DB

type user struct {
    Id   int    `db:"id"`
    Age  int    `db:"age"`
    Name string `db:"name"`
}

Query operation

Query a row of data

Query a row of data usingsqlxIn LibraryGetFunction implementation:

func (db *DB) Get(dest interface{}, query string, args ...interface{}) error

destThe user declares variables to receive query results,queryTo query SQL statements,argsAssign values to binding parameters.

//Query a row of data
func queryRow() {
    sqlStr := "SELECT id, name, age FROM user WHERE id = ?"

    var u user
    if err := db.Get(&u, sqlStr, 1); err != nil {
        fmt.Printf("get data failed, err:%v\n", err)
        return
    }
    fmt.Printf("id:%d, name:%s, age:%d\n", u.Id, u.Name, u.Age)
}

Query multi row data

The query of multi row data usesSelectFunction:

func (db *DB) Select(dest interface{}, query string, args ...interface{}) error

useSelectWhen querying the function, you need to declare a structure array to receive the mapped data:

//Query multi row data
func queryMultiRow() {
    sqlStr := "SELECT id, name, age FROM user WHERE id > ?"
    var users []user
    if err := db.Select(&users, sqlStr, 0); err != nil {
        fmt.Printf("get data failed, err:%v\n", err)
        return
    }

    for i := 0; i < len(users); i++ {
        fmt.Printf("id:%d, name:%s, age:%d\n", users[i].Id, users[i].Name, users[i].Age)
    }
}

Insert, update, delete

staysqlxIn the library, insert, update and delete operations are and nativesqlThe library implementation is consistent and adoptsExecFunction:

Insert operation

//Insert data
func insertRow() {
    sqlStr := "INSERT INTO user(name, age) VALUES(?, ?)"
    Result, err: = dB. Exec (sqlstr, "Meng Xiaoyu", 22)
    if err != nil {
        fmt.Printf("exec failed, err:%v\n", err)
        return
    }
    insertID, err := result.LastInsertId()
    if err != nil {
        fmt.Printf("get insert id failed, err:%v\n", err)
        return
    }
    fmt.Printf("insert data success, id:%d\n", insertID)
}

update operation

//Update data
func updateRow() {
    sqlStr := "UPDATE user SET age = ? WHERE id = ?"
    result, err := db.Exec(sqlStr, 22, 6)
    if err != nil {
        fmt.Printf("exec failed, err:%v\n", err)
        return
    }
    affectedRows, err := result.RowsAffected()
    if err != nil {
        fmt.Printf("get affected failed, err:%v\n", err)
        return
    }
    fmt.Printf("update data success, affected rows:%d\n", affectedRows)
}

Delete operation

//Delete a row
func deleteRow() {
    sqlStr := "DELETE FROM user WHERE id = ?"
    result, err := db.Exec(sqlStr, 4)
    if err != nil {
        fmt.Printf("exec failed, err:%v\n", err)
        return
    }
    affectedRows, err := result.RowsAffected()
    if err != nil {
        fmt.Printf("get affected failed, err:%v\n", err)
        return
    }
    fmt.Printf("delete data success, affected rows:%d\n", affectedRows)
}

Parameter binding

The most commonly used in the library isNamedQueryandNamedExecFunction, one is to name and bind the query parameters, and the other is to bind the query parameter names of cud operations:

NamedQuery

//Bind query
func selectNamedQuery() {
    sqlStr := "SELECT id, name, age FROM user WHERE age = :age"
    rows, err := db.NamedQuery(sqlStr, map[string]interface{}{
        "age": 22,
    })
    if err != nil {
        fmt.Printf("named query failed failed, err:%v\n", err)
        return
    }
    defer rows.Close()
    for rows.Next() {
        var u user
        if err := rows.StructScan(&u); err != nil {
            fmt.Printf("struct sacn failed, err:%v\n", err)
            continue
        }
        fmt.Printf("%#v\n", u)
    }
}

NamedExec

//Insert data using named method
func insertNamedExec() {
    sqlStr := "INSERT INTO user(name, age) VALUES(:name, :age)"
    result, err := db.NamedExec(sqlStr, map[string]interface{}{
        "Name": "Reese",
        "age":  18,
    })
    if err != nil {
        fmt.Printf("named exec failed, err:%v\n", err)
        return
    }
    insertId, err := result.LastInsertId()
    if err != nil {
        fmt.Printf("get last insert id failed, err:%v\n", err)
        return
    }
    fmt.Printf("insert data success, id:%d\n", insertId)
}

Transaction operation

useBeginFunctionsRollbackFunction andCommitThe function implements transaction operations:

//Open transaction
func (db *DB) Begin() (*Tx, error) 
//Rollback transaction
func (tx *Tx) Rollback() error 
//Commit transaction
func (tx *Tx) Commit() error

Example code:

//Transaction operation
func updateTransaction() (err error) {
    tx, err := db.Begin()
    if err != nil {
        fmt.Printf("transaction begin failed, err:%v\n", err)
        return err
    }

    defer func() {
        if p := recover(); p != nil {
            _ = tx.Rollback()
            panic(p)
        } else if err != nil {
            fmt.Printf("transaction rollback")
            _ = tx.Rollback()
        } else {
            err = tx.Commit()
            fmt.Printf("transaction commit")
            return
        }
    }()

    sqlStr1 := "UPDATE user SET age = ? WHERE id = ? "
    reuslt1, err := tx.Exec(sqlStr1, 18, 1)
    if err != nil {
        fmt.Printf("sql exec failed, err:%v\n", err)
        return err
    }
    rows1, err := reuslt1.RowsAffected()
    if err != nil {
        fmt.Printf("affected rows is 0")
        return
    }
    sqlStr2 := "UPDATE user SET age = ? WHERE id = ? "
    reuslt2, err := tx.Exec(sqlStr2, 19, 5)
    if err != nil {
        fmt.Printf("sql exec failed, err:%v\n", err)
        return err
    }
    rows2, err := reuslt2.RowsAffected()
    if err != nil {
        fmt.Printf("affected rows is 0\n")
        return
    }

    if rows1 > 0 && rows2 > 0 {
        fmt.Printf("update data success\n")
    }
    return
}

Open source support

Finally, put this open source project here. If you are interested, you can give this open source project a star. Thank you.

GitHub jmoiron/sqlx

Reference articles