Go database/sql package

Examples

Open does not connect. It validates the driver name and returns a pool, so the first real failure shows up on Ping or on the first query.

import (
    "database/sql"
    _ "github.com/jackc/pgx/v5/stdlib" // registers "pgx"
)

db, err := sql.Open("pgx", dsn)
if err != nil {
    return nil, err
}

db.SetMaxOpenConns(25)
db.SetMaxIdleConns(25)
db.SetConnMaxLifetime(5 * time.Minute)

ctx, cancel := context.WithTimeout(context.Background(), 5*time.Second)
defer cancel()
if err := db.PingContext(ctx); err != nil {
    return nil, err // this is where a bad DSN shows up
}

Output

connected, pool capped at 25

QueryRowContext returns no error of its own. Everything surfaces from Scan, including the no-rows case, which is a normal outcome rather than a failure.

var u User
err := db.QueryRowContext(ctx,
    `SELECT id, email, name FROM users WHERE id = $1`, id,
).Scan(&u.ID, &u.Email, &u.Name)

if errors.Is(err, sql.ErrNoRows) {
    return User{}, fmt.Errorf("user %d: %w", id, err)
}
if err != nil {
    return User{}, err
}

Output

{1 ada@example.com {Ada true}}

Three things are mandatory: defer Close, check Scan, and check Err after the loop. Next returns false both at the end of the results and on a read error.

rows, err := db.QueryContext(ctx,
    `SELECT id, email, name FROM users ORDER BY id`)
if err != nil {
    return nil, err
}
defer rows.Close() // holds a connection until this runs

var out []User
for rows.Next() {
    var u User
    if err := rows.Scan(&u.ID, &u.Email, &u.Name); err != nil {
        return nil, err
    }
    out = append(out, u)
}
return out, rows.Err() // without this, a read error looks empty

Output

3 users

defer Rollback right after BeginTx. Rollback after a successful Commit is a no-op, so the deferred call is safe and covers every early return.

tx, err := db.BeginTx(ctx, &sql.TxOptions{
    Isolation: sql.LevelSerializable,
})
if err != nil {
    return err
}
defer tx.Rollback() // no-op once Commit has run

if _, err := tx.ExecContext(ctx,
    `UPDATE accounts SET cents = cents - $1 WHERE id = $2`,
    cents, from); err != nil {
    return err
}
if _, err := tx.ExecContext(ctx,
    `UPDATE accounts SET cents = cents + $1 WHERE id = $2`,
    cents, to); err != nil {
    return err
}
return tx.Commit()

Output

transfer committed

Function	Description
Open(driverName, dataSourceName string) (*DB, error)	Creates a DB handle. It does not connect, so an error here means a bad driver name, not an unreachable database.
The driver is imported for its side effect: its `init` registers the name that `Open` takes. That is why the import is blank and the package is never referenced. `import ( "database/sql" _ "github.com/jackc/pgx/v5/stdlib" // "pgx" _ "github.com/go-sql-driver/mysql" // "mysql" _ "modernc.org/sqlite" // "sqlite" ) // Open one pool for the life of the program. It is safe to // share across goroutines, and opening per request defeats // the pooling entirely. db, err := sql.Open("pgx", dsn)`
type DB struct	A pool of connections, safe for concurrent use. Create one per database and share it.
(*DB) PingContext(ctx) error	Opens a connection to verify the database is actually reachable.
(DB) QueryRowContext(ctx, query, args...) Row `db.QueryRowContext(ctx, q, id).Scan(&u.ID)`	One row. Errors surface from Scan, not here.
(DB) QueryContext(ctx, query, args...) (Rows, error)	Many rows. The returned Rows holds a connection until closed.
An unclosed `Rows` holds its connection. Enough of them and the pool is exhausted, which looks like the database hanging rather than a leak in the caller. `rows, err := db.QueryContext(ctx, q) if err != nil { return err } defer rows.Close() for rows.Next() { // A return in here still closes, because of the defer. } // Next returned false. Was that the end, or a failure? return rows.Err()`
(*DB) ExecContext(ctx, query, args...) (Result, error)	A statement returning no rows: INSERT, UPDATE, DELETE, DDL.
(DB) PrepareContext(ctx, query) (Stmt, error)	A prepared statement, reusable across calls. Close it when done.
(DB) BeginTx(ctx, opts TxOptions) (*Tx, error)	Starts a transaction on one connection.
A `Tx` is pinned to one connection for its whole life, so a long transaction takes a connection out of the pool for that time. `// Isolation levels, weakest to strongest: sql.LevelReadUncommitted sql.LevelReadCommitted // the Postgres default sql.LevelRepeatableRead sql.LevelSerializable // A read-only transaction lets the database optimize. tx, err := db.BeginTx(ctx, &sql.TxOptions{ReadOnly: true})`
(*DB) SetMaxOpenConns(n int)	Caps total connections. Unlimited by default, which is rarely what you want.
The default is unlimited connections, which means load spikes open connections until the database refuses them. Cap it below what the server allows. `db.SetMaxOpenConns(25) db.SetMaxIdleConns(25) // match, or idle conns are closed and reopened db.SetConnMaxLifetime(5 * time.Minute) db.SetConnMaxIdleTime(2 * time.Minute) // WaitCount climbing means the cap is too low for the load. s := db.Stats() fmt.Println(s.OpenConnections, s.InUse, s.Idle, s.WaitCount)`
(*DB) SetMaxIdleConns(n int)	How many idle connections to keep. Set it equal to MaxOpenConns to avoid churn.
(*DB) SetConnMaxLifetime(d time.Duration)	Retires a connection after this long, which lets load balancers and failovers take effect.
(*DB) SetConnMaxIdleTime(d time.Duration)	Closes a connection that has sat idle this long.
(*DB) Stats() DBStats	Live pool counters: open, in use, idle, and how often callers waited.
type Rows struct	An iterator over a result set. Next, Scan, Err, Close.
(*Rows) Next() bool	Advances to the next row. Returns false at the end and on error, which is why Err must be checked.
(*Rows) Scan(dest ...any) error	Copies the current row's columns into the destinations, in order.
(*Rows) Err() error	The error that ended the iteration, if any. Skipping this hides read failures as empty results.
type Tx struct	A transaction. Commit or Rollback exactly once.
type Result interface	LastInsertId() (int64, error) and RowsAffected() (int64, error). Postgres does not support LastInsertId.
ErrNoRows `errors.Is(err, sql.ErrNoRows)`	Returned by Row.Scan when the query matched nothing. Match it with errors.Is.
NullString, NullInt64, NullBool, NullTime, NullFloat64	Wrappers for columns that can be NULL. Each carries the value plus a Valid bool.
Scanning a NULL into a plain `string` fails. The Null types carry the value and a `Valid` flag saying whether it was there. `type User struct { ID int64 Email string Name sql.NullString // this column can be NULL } if u.Name.Valid { fmt.Println(u.Name.String) } else { fmt.Println("(no name)") } // A pointer works too, and reads more naturally: var name *string row.Scan(&id, &email, &name)`
Named(name string, value any) NamedArg `sql.Named("id", 7)`	A named parameter, for drivers that support them.
type Scanner interface	Scan(src any) error. Implement it and your type can be a Scan destination.
driver.Valuer	Value() (driver.Value, error). Implement it and your type can be a query argument.

Related: context errors time

Previous crypto Next Topic embed

The database/sql Package

Examples