Golang Package-database/sql

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

First, guide the storage

import (    "database/sql"    _ "github.com/lib/pq")

Second, connect the DB

func main() {    db, err := sql.Open("postgres", "user=pqgotest dbname=pqgotest sslmode=verify-full")    /*db, err := sql.Open("postgres",           "postgres://pqgotest:password@localhost/pqgotest?sslmode=verify-full")*/    if err != nil {        log.Fatal(err)    }    defer db.Close()}

Sql. The first argument to open is the driver name, and the second parameter is the information for the driver connection database. The DB is not a connection, and the connection is created only when it is needed, and if you want to verify the connection immediately, you need to use the ping () 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.

There is a very basic connection pool in Database/sql, and when a connection is required and there are no available connections in the connection pool, the new connection is created and DB timeout may result if the idle connection is maintained for a long time. You can set Setmaxidleconns and Setmaxopenconns, which is the maximum idle connection and maximum number of connections, which are the following two functions:

db.SetMaxIdleConns(n)db.SetMaxOpenConns(n)

Third, query db

(1) General enquiry Query

var name, sex stringrows, err := db.Query("select name, sex from user where id = $1 ", 1)if err != nil {fmt.Println(err)}defer rows.Close()for rows.Next() {err := rows.Scan(&name, &sex)if err != nil {fmt.Println(err)}}err = rows.Err()if err != nil {fmt.Println(err)}fmt.Println("name:", name, "sex:", sex)

The procedure for the above code is:db. Query () indicates that a query is sent to the database,defer rows. Close () is very important (close the connection) and traverse rows to use rows. Next (), put the traversed data into the variable using rows. Scan ()to check for error after the traversal is complete. There are a few points to note:

(1) Check whether the traversal has error
(2) The underlying connection is busy until the result set (rows) is closed. An internal EOF error occurs when the traverse reads to the last record, and automatically calls rows. Close (), 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 and is harmless.

(2) Single query Queryrow

var name stringerr = db.QueryRow("select name from user where id = $1", 222).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)

Iv. increased erasure of exec

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 package has the function of automatic retry. When you generate a prepared Statement

(1) Automatically binds to an idle connection in the connection pool
(2) Stmt object remembers which connection was bound
(3) When executing stmt, 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 scenarios are notsuitable for statement:

(1) The 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 ensure security.

stmt, err := db.Prepare("insert into user(name, sex)values($1,$2)")if err != nil {fmt.Println(err)}rs, err := stmt.Exec("go-test", 12)if err != nil {fmt.Println(err)}//可以获得影响行数affect, err := rs.RowsAffected()fmt.Println("affect ", affect ," rows")

V. Business

tx, err := db.Begin()if err != nil {    log.Fatal(err)}defer tx.Rollback()stmt, err := tx.Prepare("INSERT INTO foo VALUES ($1)")if err != nil {    log.Fatal(err)}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)}defer stmt.Close() //runs here!

db. Begin () to start the transaction,Commit () , or Rollback () to close the transaction. TX pulls a connection from the connection pool and uses the connection before it is closed. TX cannot be mixed with the DB layer's begin, commit .