Learn notes about the Database/sql pack in Golang (reprint)

This is a creation in Article, where the information may have evolved or changed.

Overview

sql.DBis not a connection, it is the abstract interface of the database. It can turn off database connections according to driver, and manage connection pooling. The connection being used is marked as busy, and then back to the connection pool to wait for the next use. Therefore, if you do not release the connection back to the connection pool, it causes too many connections to drain the system resources.

Using DB

Import Driver

MySQL drivers is used here.

import (    "database/sql"    _ "github.com/go-sql-driver/mysql")

Connect db

func main() {    db, err := sql.Open("mysql",        "user:password@tcp(127.0.0.1:3306)/hello")    if err != nil {        log.Fatal(err)    }    defer db.Close()}

sql.OpenThe first parameter is the driver name, and the second parameter is the information for the driver connection database, each driver may be different. The DB is not a connection, and the connection is created only when it is needed, and if you want to Ping() Verify the connection immediately, you need to use the method as follows:

err = db.Ping()if err != nil {    // do something here}

Sql. The DB is designed to be used as a long connection. Do not frequently open, Close. It is good practice to build a DB object for each of the different datastore, keeping these objects open. If a short connection is required, pass the DB as a parameter to function instead of open, Close in function.

Read db

If the method Query contains, then this method is used for querying and returning rows. Other conditions should be Exec() used.

var (    id int    name string)rows, err := db.Query("select id, name from users where id = ?", 1)if err != nil {    log.Fatal(err)}defer rows.Close()for rows.Next() {    err := rows.Scan(&id, &name)    if err != nil {        log.Fatal(err)    }    log.Println(id, name)}err = rows.Err()if err != nil {    log.Fatal(err)}

The process of the above code db.Query() is: To send a defer rows.Close() query to the database, it is very rows.Next() important to traverse the rows to use, the rows.Scan() traversed data into the variable to use, Check for error after traversal is complete. There are a few points to note:

• Check if traversal has error
• The underlying connection is in a busy state until the result set (rows) is not closed. An internal EOF error occurs automatically rows.Close() when the traversal reads to the last record, but if you exit the loop early, rows does not close, the connection does not go back to the connection pool, and the connection does not close. So manual shutdown is very important. rows.Close() can be called multiple times, is harmless operation.

Single-line Query

Err is Scan only generated after this, so you can write as follows:

var name stringerr = db.QueryRow("select name from users where id = ?", 1).Scan(&name)if err != nil {    log.Fatal(err)}fmt.Println(name)

Modify data, transaction

Generally used prepared statements and Exec() finish INSERT , UPDATE , DELETE operation.

stmt, err := db.Prepare("INSERT INTO users(name) VALUES(?)")if err != nil {    log.Fatal(err)}res, err := stmt.Exec("Dolly")if err != nil {    log.Fatal(err)}lastId, err := res.LastInsertId()if err != nil {    log.Fatal(err)}rowCnt, err := res.RowsAffected()if err != nil {    log.Fatal(err)}log.Printf("ID = %d, affected = %d\n", lastId, rowCnt)

Transaction

db.Begin()Start a transaction Commit() , Rollback() or close a transaction. Tx Remove a connection from the connection pool and use this connection before closing. TX cannot be COMMIT mixed with the BEGIN db layer.

If you need to modify the connection state through multiple statements, you must use TX, for example:

• Create a temporary table that is visible only to a single connection
• Set variables, such asSET @var := somevalue
• Change connection options, such as Character set, timeout

Prepared statements

Prepared Statements and Connection

At the database level, Prepared statements is bound to a single database connection. The client sends a statement to the server with a placeholder, the servers return a statement ID, and the client sends the ID and parameters to perform the statement.

In go, the connection is not exposed directly, you cannot bind the statement to the connection, but only the DB or TX binding. database/sql The package has features such as automatic retry. When you generate a prepared Statement

1. Automatically binds to an idle connection in the connection pool
2. StmtObject remembers which connection was bound
3. When Stmt executing, try to use the connection. If not available, such as when the connection is closed or busy, it is automatically re-prepare and bound to another connection.

This leads to high concurrency scenarios where excessive use of statement can lead to statement leaks, statement the process of repeating prepare and re-prepare, and even the maximum number of server-side statement.

Some operations use PS, for example db.Query(sql, param1, param2) , and automatically turn off statement at the end.

Some scenarios are not suitable for statement:

1. Database is not supported. such as Sphinx,memsql. They support MySQL wire protocol, but do not support "binary" protocol.
2. Statement does not need to be reused many times, and there are other ways to keep it safe. Example

Using PS in transaction

PS uniquely binds a connection in TX and does not re-prepare.

TX and statement cannot be detached, and statement created in DB cannot be used in TX because they must be careful not to use the same connection using TX, such as the following code:

tx, err := db.Begin()if err != nil {    log.Fatal(err)}defer tx.Rollback()stmt, err := tx.Prepare("INSERT INTO foo VALUES (?)")if err != nil {    log.Fatal(err)}defer stmt.Close() // danger!for i := 0; i < 10; i++ {    _, err = stmt.Exec(i)    if err != nil {        log.Fatal(err)    }}err = tx.Commit()if err != nil {    log.Fatal(err)}// stmt.Close() runs here!

*sql.TxOnce released, the connection is returned to the connection pool, where stmt cannot find the connection when it is closed. So statement must be closed before TX commit or rollback.

Handling Error

Error of looping rows

If an error occurs in the loop that rows.Close() will run rows.Err() automatically, the Close method can be called multiple times with the receive error. It is necessary to judge the error after the loop.

for rows.Next() {    // ...}if err = rows.Err(); err != nil {    // handle the error here}

Error when closing resultsets

If you exit the loop before the rows traverse ends, you must manually turn off resultset and receive the error.

for rows.Next() {    // ...    break; // whoops, rows is not closed! memory leak...}// do the usual "if err = rows.Err()" [omitted here]...// it's always safe to [re?]close here:if err = rows.Close(); err != nil {    // but what should we do if there's an error?    log.Println(err)}

Error of Queryrow ()

var name stringerr = db.QueryRow("select name from users where id = ?", 1).Scan(&name)if err != nil {    log.Fatal(err)}fmt.Println(name)

If the ID of 1 does not exist, err is SQL. Errnorows, a situation that does not exist in a general application needs to be handled separately. In addition, the error returned by query is deferred until the scan is called, so the following code should be written:

var name stringerr = db.QueryRow("select name from users where id = ?", 1).Scan(&name)if err != nil {    if err == sql.ErrNoRows {        // there were no rows, but otherwise no error occurred    } else {        log.Fatal(err)    }}fmt.Println(name)

The null result is treated as an error to force the programmer to handle the empty result.

Analyze Database error

Different database processing method is not the same, MySQL for example:

if driverErr, ok := err.(*mysql.MySQLError); ok {     // Now the error number is accessible directly    if driverErr.Number == 1045 {        // Handle the permission-denied error    }}

MySQLError, Number are db-specific, and other databases may be other types or fields. The numbers here can be replaced with constants, such as this package MySQL error numbers maintained by Vividcortex

Connection error

Null value handling

Simply put, the design of the database do not appear null, processing is very laborious. The null type is limited, for example sql.NullUint64 not, and the null value does not have a default value of 0.

for rows.Next() {    var s sql.NullString    err := rows.Scan(&s)    // check err    if s.Valid {       // use s.String    } else {       // NULL value    }}

Unknown column

rows.Columns()Used to handle situations where the number or type of result fields cannot be known, for example:

cols, err := rows.Columns()if err != nil {    // handle the error} else {    dest := []interface{}{ // Standard MySQL columns        new(uint64), // id        new(string), // host        new(string), // user        new(string), // db        new(string), // command        new(uint32), // time        new(string), // state        new(string), // info    }    if len(cols) == 11 {        // Percona Server    } else if len(cols) > 8 {        // Handle this case    }    err = rows.Scan(dest...)    // Work with the values in dest}

cols, err := rows.Columns() // Remember to check err afterwardsvals := make([]interface{}, len(cols))for i, _ := range cols {    vals[i] = new(sql.RawBytes)}for rows.Next() {    err = rows.Scan(vals...)    // Now you can check each element of vals for nil-ness,    // and you can use type introspection and type assertions    // to fetch the column into a typed variable.}

About connection pooling

1. Avoid error actions, such as lock table, with insert deadlock, because two operations are not the same connection, the Insert connection does not have a table lock.
2. When a connection is required and there are no available connections in the connection pool, the new connection is created.
3. By default there is no connection cap, you can set one, but this may cause the database to produce an error "too many connections"
4. db.SetMaxIdleConns(N)Set the maximum number of idle connections
5. db.SetMaxOpenConns(N)Setting the maximum number of open connections
6. Maintaining idle connections for long periods may result in DB timeout