Database Connections

I’ve had database connection issues crop up in almost every Go project I’ve been on. I think the hard thing for new gophers to wrap their heads around is that the sql.DB object is a concurrency-safe pool of connections instead of a single database connection. This means that if you forget to return your connections to the pool you can easily exhaust the number of connections and your application can grind to a halt.

For instance, the connection pool contains both Open and Idle connections which are configured through:

SetConnMaxLifetime : the maximum amount of time a connection may be reused

: the maximum amount of time a connection may be reused SetMaxIdleConns : maximum number of connections in the idle connection pool

: maximum number of connections in the idle connection pool SetMaxOpenConns : maximum number of open connections to the database

Note that even if you configure the max open connections to 200, the application can still exhaust the number of open connections the database will accept, making a shutdown or restart necessary. You need to check the database settings or coordinate with whoever has the permissions to ensure you’re correctly setting these limits.

If you don’t configure a limit, your application can easily use all the connections the database will accept.

Back to exhausting the connection pool. When querying the database a lot of developers forget to close the *sql.Rows object. This leads to hitting the max connections limit and causes deadlock or high latency. Here’s a snippet of code showing this:

package main import (

"context"

"database/sql"

"fmt"

"log"

) var (

ctx context.Context

db *sql.DB

) func main() {

age := 27

ctx, cancel := context.WithTimeout(context.Background(), time.Minute)

defer cancel() rows, err := db.QueryContext(ctx, "SELECT name FROM users WHERE age=?", age)

if err != nil {

log.Fatal(err)

} for rows.Next() {

var name string

if err := rows.Scan(&name); err != nil {

log.Fatal(err)

}

fmt.Println(name)

}

... }

You’ll notice, just as you can add context to an HTTP request, you can also add a context with a timeout to a database query (or an execution of a prepared statement, ping, etc.) But that’s not the problem.

As mentioned above we need to close the rows object to prevent further enumeration and release the connection back to the connection pool:

rows, err := db.QueryContext(ctx, "SELECT name FROM users WHERE age=?", age)

if err != nil {

log.Fatal(err)

}

defer rows.Close()

This becomes particularly difficult to spot if you’re passing open connections across functions and packages.