Postgres with database/sql

No ORM. Learn what each query does, because SQL outlives every ORM.

Topic 52 of 98 · 5 min to read · 50 min to build

After this topic you can

Explain why *sql.DB is a connection pool and configure its limits.
Write single-row and multi-row queries that close rows and check rows.Err().
Fix SQL injection by using placeholders, and handle nullable columns.

What a database layer is

So far everything your program stores disappears when it stops. A database keeps it. It also enforces rules that your Go code would otherwise have to remember every time: this column cannot be empty, this email must be unique, this row must point at a real user.

Go talks to databases through `database/sql`, a package in the standard library. On its own it knows no particular database. It defines a common interface, and a driver (a separate package, here pgx for Postgres) speaks the database's wire protocol.

There is no ORM here. An ORM turns database rows into objects for you and hides the SQL. That is convenient until it produces a query you have to fight. With database/sql you write every query yourself, so you always know what runs. SQL is also a skill that outlives every ORM.

Get a database running

terminal

1docker run -d --name pg \
2  -e POSTGRES_PASSWORD=secret -e POSTGRES_DB=api \
3  -p 5432:5432 postgres:16
4
5go get github.com/jackc/pgx/v5/stdlib

database/sql is the stdlib interface; pgx is the Postgres driver behind it. That is one dependency, not a framework.

*sql.DB is a pool, not a connection

sql.Open does not connect. It creates a pool. The pool is safe for concurrent use by many goroutines, you create one for the whole program, and you never close it per request.

db.go

1db, err := sql.Open("pgx", os.Getenv("DATABASE_URL"))
2if err != nil {
3	return err
4}
5
6db.SetMaxOpenConns(25)                   // MUST set this. The default is UNLIMITED.
7db.SetMaxIdleConns(25)                   // match it, or you churn connections
8db.SetConnMaxLifetime(5 * time.Minute)
9db.SetConnMaxIdleTime(5 * time.Minute)
10
11if err := db.PingContext(ctx); err != nil {   // Open is lazy — Ping actually connects
12	return err
13}

With no limit, a traffic spike opens as many connections as there are concurrent queries. Postgres allows 100 by default (`max_connections`), so the extra ones fail with "too many clients". The pool limit makes requests queue in Go instead.

One row

query-one.go

1// ctx comes from the request (r.Context()), so a client that hangs up
2// cancels the query. Add a deadline so a slow query cannot hang forever.
3ctx, cancel := context.WithTimeout(ctx, 3*time.Second)
4defer cancel()
5
6var u User
7err := db.QueryRowContext(ctx,
8	`SELECT id, name, email FROM users WHERE id = $1`, id,
9).Scan(&u.ID, &u.Name, &u.Email)
10
11if errors.Is(err, sql.ErrNoRows) {
12	return nil, ErrNotFound          // translate to YOUR domain error
13}
14if err != nil {
15	return nil, fmt.Errorf("get user %d: %w", id, err)   // wrap, keep the cause
16}

Scan takes pointers, in the same order as the columns in the SELECT. The `$1` placeholder sends `id` separately from the SQL, so it can never be run as SQL.

Many rows — and the three things people forget

query-many.go

1rows, err := db.QueryContext(ctx,
2	`SELECT id, name FROM users ORDER BY id LIMIT $1 OFFSET $2`, limit, offset)
3if err != nil {
4	return nil, err
5}
6defer rows.Close()                   // 1. or you leak a pooled connection
7
8var out []User
9for rows.Next() {
10	var u User
11	if err := rows.Scan(&u.ID, &u.Name); err != nil {
12		return nil, err              // 2. check every Scan
13	}
14	out = append(out, u)
15}
16
17return out, rows.Err()               // 3. iteration errors surface ONLY here

Watch out: Never SELECT *. Someone adds a column, and every Scan behind a SELECT * on that table fails at runtime with "sql: expected 4 destination arguments in Scan, not 3". List the columns.

Insert with a returned id, and transactions

write.go

1err = db.QueryRowContext(ctx,
2	`INSERT INTO users (name, email) VALUES ($1, $2) RETURNING id, created_at`,
3	u.Name, u.Email,
4).Scan(&u.ID, &u.CreatedAt)
5
6// A transaction: all of it, or none of it
7tx, err := db.BeginTx(ctx, nil)
8if err != nil {
9	return err
10}
11defer tx.Rollback()                  // a no-op once Commit has succeeded
12
13if _, err := tx.ExecContext(ctx, q1, args...); err != nil {
14	return err                       // the deferred Rollback undoes everything
15}
16if _, err := tx.ExecContext(ctx, q2, args...); err != nil {
17	return err
18}
19
20return tx.Commit()

A transaction holds a connection for its whole life. Keep them short, and never make an HTTP call inside one.

The two rules that are not negotiable

Always use `$1, $2` placeholders. Never build SQL with `fmt.Sprintf`. This is your main defence against SQL injection, and it applies everywhere, including "internal" endpoints.
Always use the `...Context` variants. QueryRowContext, not QueryRow. A query without a context cannot be cancelled when the client disconnects, so the database keeps working for nobody.

Nullable columns

Scanning a SQL NULL into a Go string fails at runtime. Three ways out, easiest first: COALESCE(col, '') in the query, sql.NullString in the scan, or a *string field.

The short version

*sql.DB is a pool. Create one, share it, always set SetMaxOpenConns.
Placeholders always. fmt.Sprintf near a query is a SQL injection.
defer rows.Close(), check each Scan, and check rows.Err() at the end.
Translate sql.ErrNoRows into your own domain error at the repository boundary.

Remember: Learn SQL, not an ORM. database/sql shows you exactly which queries run, and that is a skill that outlives every library.

Build this before moving on

Create a users table and write five functions against it: Create, GetByID, List with limit/offset, Update, Delete. Make Delete return your ErrNotFound when RowsAffected() is zero.

Then try these in your own editor

1Open a pool without calling SetMaxOpenConns, fire 500 concurrent requests, and watch the connection count in pg_stat_activity.
2Forget defer rows.Close() in a loop and watch the pool run out of connections.
3Try to inject SQL through a fmt.Sprintf query, then switch to $1 and try the same input.
4Scan a NULL column into a string and read the error. Fix it three ways: COALESCE, sql.NullString, and *string.

Official documentation

Check yourselfTake the Databases test