On the study of Database/sql package in Golang

Go-sql-driver

There are several ways to request a connected function, which is slightly different in the way that the connection is done, roughly as follows:

• Db. After the Ping () call is complete, the connection is returned to the connection pool immediately.

• Db. When the Exec () call is complete, the connection is returned to the connection pool immediately, but the result object it returns retains a reference to the connection, and the connection is reused when the subsequent code needs to process the result set.

• Db. Query () passes the connection to SQL after the call is complete. The rows type, of course the latter iterates over or displays the call. After the Clonse () method, the connection is freed back to the connection pool.

• Db. After the Queryrow () call is complete, the connection is passed to SQL. Row type, which releases the connection back to the connection pool after the. Scan () method call.

• Db. Begin () passes the connection to SQL after the call is complete. The TX type Object when the connection is released after the. Commit () or. Rollback () method call.

  Connect dbsql.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 verify the connection immediately, you need to usePing()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 contains Query , then this method is used for querying and returning rows. Other conditions should be used Exec() .

  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 is: db.Query() to send a query to the database, it defer rows.Close() is very important to traverse rows.Next() the rows to use, the traversed data into the variable use rows.Scan() , after the completion of the loop to check the error. There are a few points to note:

  1. Check if traversal has error

  2. The underlying connection is in a busy state until the result set (rows) is not closed. An internal EOF error occurs automatically when the traversal reads to the last record, 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, 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 prepare () and Exec() finished INSERT , UPDATE , DELETE operation.

  Transaction

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

  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

  Error in handling error loop rows

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

  Error when closing resultsets

  If you exit the loop before the rows traverse ends, you must manually close

About connection pooling

1. When a connection is required and there are no available connections in the connection pool, the new connection is created.

2. There is no default connection limit, which may cause the database to produce an error "too many connections"

3. db.SetMaxIdleConns(N)Set the maximum number of idle connections

4. db.SetMaxOpenConns(N)Setting the maximum number of open connections

5. Maintaining idle connections for long periods may result in DB timeout

On the study of Database/sql package in Golang