SQL databases in Go

The code that talks to your database is where many production incidents start: leaked connections, injected SQL, a NULL nobody expected. This lesson shows how Go talks to PostgreSQL through the standard database/sql package and the PostgreSQL-specific pgx driver. You'll open a pool, run queries, scan rows into structs, handle NULLs, and learn the pool behaviours that matter under load.

#Two ways to talk to PostgreSQL

Go gives you two layers to choose from.

database/sql is the standard library's generic interface. It defines *sql.DB, Query, Scan and friends, and leaves the wire protocol to a driver you import separately. The same code shape works for PostgreSQL, MySQL or SQLite.

pgx (github.com/jackc/pgx/v5) is the most widely used PostgreSQL driver for Go. It can act as a database/sql driver, but it also has its own native interface, with its own pool in the pgxpool package.

If your service only targets PostgreSQL, use pgxpool. When a library insists on a *sql.DB (a migration tool, say), stdlib.OpenDBFromPool(pool) wraps the same pool. This lesson uses pgxpool and shows database/sql where it differs.

go get github.com/jackc/pgx/v5

#Opening a connection pool

Opening a PostgreSQL connection costs a TCP handshake, often TLS, and authentication. A pool keeps connections open and lends one to each query. Create one pool at startup, share it everywhere (it's safe for concurrent use), and close it on shutdown.

func openPool(ctx context.Context, dsn string) (*pgxpool.Pool, error) {
	cfg, err := pgxpool.ParseConfig(dsn)
	if err != nil {
		return nil, fmt.Errorf("parse dsn: %w", err)
	}
	cfg.MaxConns = 10
	cfg.MinConns = 2
	cfg.MaxConnLifetime = time.Hour
	cfg.MaxConnIdleTime = 5 * time.Minute

	pool, err := pgxpool.NewWithConfig(ctx, cfg)
	if err != nil {
		return nil, fmt.Errorf("create pool: %w", err)
	}

	// The pool connects lazily, so check the database is really there.
	pingCtx, cancel := context.WithTimeout(ctx, 5*time.Second)
	defer cancel()
	if err := pool.Ping(pingCtx); err != nil {
		pool.Close()
		return nil, fmt.Errorf("ping database: %w", err)
	}
	return pool, nil
}

The DSN is a URL: postgres://app:secret@localhost:5432/app?sslmode=disable. Read it from an environment variable, never from source code.

The database/sql version looks similar. The blank import registers pgx under the driver name "pgx":

import (
	"database/sql"

	_ "github.com/jackc/pgx/v5/stdlib"
)

func openDB(dsn string) (*sql.DB, error) {
	db, err := sql.Open("pgx", dsn)
	if err != nil {
		return nil, err
	}
	db.SetMaxOpenConns(10) // default is 0: unlimited
	db.SetMaxIdleConns(10) // default is 2
	db.SetConnMaxLifetime(time.Hour)
	db.SetConnMaxIdleTime(5 * time.Minute)
	return db, nil
}

database/sql defaults to unlimited connections, which can overwhelm PostgreSQL in a traffic spike; pgxpool defaults to the greater of 4 and the CPU count. Set the limit explicitly either way.

#Running queries

Every example below uses this table:

CREATE TABLE users (
    id         bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    email      text NOT NULL UNIQUE,
    name       text NOT NULL,
    bio        text,
    created_at timestamptz NOT NULL DEFAULT now()
);

There are three query methods, chosen by what you expect back.

QueryRow for exactly one row. RETURNING lets an INSERT hand back generated columns in the same round trip:

func (s *UserStore) Create(ctx context.Context, email, name string) (User, error) {
	u := User{Email: email, Name: name}
	err := s.pool.QueryRow(ctx,
		`INSERT INTO users (email, name) VALUES ($1, $2) RETURNING id, created_at`,
		email, name,
	).Scan(&u.ID, &u.CreatedAt)
	if err != nil {
		return User{}, fmt.Errorf("insert user: %w", err)
	}
	return u, nil
}

When no row matches, Scan returns pgx.ErrNoRows (sql.ErrNoRows in database/sql; pgx's error also matches sql.ErrNoRows with errors.Is). Translate it into your own error so HTTP handlers never import the driver:

var ErrNotFound = errors.New("not found")

func (s *UserStore) Get(ctx context.Context, id int64) (User, error) {
	var u User
	err := s.pool.QueryRow(ctx,
		`SELECT id, email, name, bio, created_at FROM users WHERE id = $1`, id,
	).Scan(&u.ID, &u.Email, &u.Name, &u.Bio, &u.CreatedAt)
	if errors.Is(err, pgx.ErrNoRows) {
		return User{}, ErrNotFound
	}
	if err != nil {
		return User{}, fmt.Errorf("get user %d: %w", id, err)
	}
	return u, nil
}

Query for zero or more rows. The loop has a fixed shape: defer rows.Close(), iterate with Next, then check rows.Err(), which reports anything that went wrong mid-stream, such as a dropped connection:

func (s *UserStore) ListSince(ctx context.Context, since time.Time) ([]User, error) {
	rows, err := s.pool.Query(ctx,
		`SELECT id, email, name, bio, created_at FROM users
		 WHERE created_at >= $1 ORDER BY id`, since)
	if err != nil {
		return nil, err
	}
	defer rows.Close()

	var users []User
	for rows.Next() {
		var u User
		if err := rows.Scan(&u.ID, &u.Email, &u.Name, &u.Bio, &u.CreatedAt); err != nil {
			return nil, err
		}
		users = append(users, u)
	}
	return users, rows.Err()
}

Exec when you don't need rows back. The command tag tells you how many rows were affected:

tag, err := s.pool.Exec(ctx, `DELETE FROM users WHERE id = $1`, id)
if err != nil {
	return err
}
if tag.RowsAffected() == 0 {
	return ErrNotFound
}

#Placeholders, never string formatting

$1, $2 are placeholders. The SQL text and the values travel to PostgreSQL separately, so a value can never be interpreted as SQL. Compare:

// DANGEROUS: name = "x'; DROP TABLE users; --" rewrites your query.
q := fmt.Sprintf("SELECT id FROM users WHERE name = '%s'", name)

// Safe: name is always just a value.
row := s.pool.QueryRow(ctx, "SELECT id FROM users WHERE name = $1", name)

Placeholders work only for values, not for table or column names. If those must vary (a sort column, for example), pick them from a hard-coded allowlist.

#Less boilerplate with CollectRows

Writing Scan(&u.ID, &u.Email, ...) in every function is tedious and easy to get out of order. pgx can map columns to struct fields by name using db tags:

type User struct {
	ID        int64     `db:"id"`
	Email     string    `db:"email"`
	Name      string    `db:"name"`
	Bio       *string   `db:"bio"`
	CreatedAt time.Time `db:"created_at"`
}

func (s *UserStore) List(ctx context.Context, limit int) ([]User, error) {
	rows, _ := s.pool.Query(ctx,
		`SELECT id, email, name, bio, created_at FROM users ORDER BY id LIMIT $1`, limit)
	return pgx.CollectRows(rows, pgx.RowToStructByName[User])
}

Ignoring the error from Query here is a documented pgx idiom: the returned rows carry the error, and CollectRows reports it and closes the rows. For a single row, pgx.CollectOneRow returns pgx.ErrNoRows when the result is empty.

RowToStructByName requires every selected column to match a struct field, which catches typos and forgotten columns. Use RowToStructByNameLax if the struct deliberately has extra fields. Avoid SELECT *: adding a column to the table would break the mapping.

#NULLs and type mapping

SQL NULL has no equivalent in a Go string or int64. Scanning one into a plain value fails:

can't scan into dest[3] (col: bio): cannot scan NULL into *string

You have three options for nullable columns:

1. A pointer (*string, *time.Time): nil means NULL. Simple, and it encodes as JSON null. This is what User.Bio uses.
2. A null wrapper: pgtype.Text (pgx) or sql.NullString (database/sql), with a Valid flag.
3. COALESCE(bio, '') in SQL, when NULL and empty genuinely mean the same thing to your program.

Common PostgreSQL to Go mappings with pgx:

Slices make "fetch these IDs" queries easy and avoid building IN (...) lists by hand:

rows, _ := s.pool.Query(ctx,
	`SELECT id, email, name, bio, created_at FROM users WHERE id = ANY($1)`, ids)
users, err := pgx.CollectRows(rows, pgx.RowToStructByName[User])

#Context, timeouts and pool pitfalls

Every pgx method takes a context.Context. Pass the request's context: if the client disconnects, the query is cancelled and the connection returns to the pool. Add a deadline for queries that must not run long:

ctx, cancel := context.WithTimeout(r.Context(), 2*time.Second)
defer cancel()
u, err := store.Get(ctx, id)

The pool has a fixed number of connections, and most database outages in Go services are really pool exhaustion: every connection is busy, and new requests wait in Acquire until they time out. The usual causes:

• Rows that are never closed. A connection stays checked out until its rows are closed. Always defer rows.Close() right after checking the error, or use CollectRows.
• Slow work while holding rows. Calling an external API inside a rows.Next() loop holds the connection for the whole call. Collect the rows first, then do the slow work.
• N+1 queries. Fetching 50 posts and then running one query per post for its author is 51 round trips. Use a join, or one = ANY($1) query for all the authors.
• Pools sized past the server. PostgreSQL's max_connections defaults to 100. Ten replicas with MaxConns = 20 each need 200. Size pools so the total across every instance stays below the server's limit.

pool.Stat() exposes counters such as AcquiredConns(), IdleConns() and EmptyAcquireCount() (how many acquires had to wait). Exporting these as metrics lets you see exhaustion coming.

#Practice

Start PostgreSQL locally (for example docker run --rm -e POSTGRES_PASSWORD=dev -p 5432:5432 postgres:17) and create a products table with id bigint identity, name text NOT NULL, price_cents bigint NOT NULL and tags text[] NOT NULL DEFAULT '{}'.

Write a ProductStore with:

1. Create(ctx, name, priceCents, tags) returning the new product with its ID.
2. Get(ctx, id) returning ErrNotFound when the product doesn't exist.
3. ListByTag(ctx, tag) returning every product with that tag, using CollectRows.

In main, read DATABASE_URL, open a pool with openPool, create two products and print each method's result.

package main

import (
	"context"
	"errors"
	"fmt"
	"log"
	"os"
	"time"

	"github.com/jackc/pgx/v5"
	"github.com/jackc/pgx/v5/pgxpool"
)

var ErrNotFound = errors.New("not found")

type Product struct {
	ID         int64    `db:"id"`
	Name       string   `db:"name"`
	PriceCents int64    `db:"price_cents"`
	Tags       []string `db:"tags"`
}

type ProductStore struct{ pool *pgxpool.Pool }

func (s *ProductStore) Create(ctx context.Context, name string, price int64, tags []string) (Product, error) {
	rows, _ := s.pool.Query(ctx,
		`INSERT INTO products (name, price_cents, tags) VALUES ($1, $2, $3)
		 RETURNING id, name, price_cents, tags`, name, price, tags)
	return pgx.CollectOneRow(rows, pgx.RowToStructByName[Product])
}

func (s *ProductStore) Get(ctx context.Context, id int64) (Product, error) {
	rows, _ := s.pool.Query(ctx,
		`SELECT id, name, price_cents, tags FROM products WHERE id = $1`, id)
	p, err := pgx.CollectOneRow(rows, pgx.RowToStructByName[Product])
	if errors.Is(err, pgx.ErrNoRows) {
		return Product{}, ErrNotFound
	}
	return p, err
}

func (s *ProductStore) ListByTag(ctx context.Context, tag string) ([]Product, error) {
	rows, _ := s.pool.Query(ctx,
		`SELECT id, name, price_cents, tags FROM products
		 WHERE $1 = ANY(tags) ORDER BY id`, tag)
	return pgx.CollectRows(rows, pgx.RowToStructByName[Product])
}

func main() {
	ctx, cancel := context.WithTimeout(context.Background(), 10*time.Second)
	defer cancel()

	pool, err := openPool(ctx, os.Getenv("DATABASE_URL")) // from the lesson
	if err != nil {
		log.Fatal(err)
	}
	defer pool.Close()
	store := &ProductStore{pool: pool}

	kb, err := store.Create(ctx, "Keyboard", 4999, []string{"hardware", "office"})
	if err != nil {
		log.Fatal(err)
	}
	if _, err := store.Create(ctx, "Desk lamp", 2500, []string{"office"}); err != nil {
		log.Fatal(err)
	}

	got, err := store.Get(ctx, kb.ID)
	fmt.Printf("get: %+v %v\n", got, err)
	_, err = store.Get(ctx, -1)
	fmt.Println("missing:", errors.Is(err, ErrNotFound))
	office, err := store.ListByTag(ctx, "office")
	fmt.Printf("office: %d products %v\n", len(office), err)
}

get: {ID:1 Name:Keyboard PriceCents:4999 Tags:[hardware office]} <nil>
missing: true
office: 2 products <nil>

#Key takeaways

• Use pgxpool for PostgreSQL-only services; use database/sql (with pgx's stdlib driver) when a tool needs a *sql.DB.
• Create one pool at startup, share it, set its size explicitly, and Ping so bad configuration fails fast.
• Always pass values as $n placeholders; only allowlisted identifiers may be formatted into SQL.
• Close rows (or use CollectRows), check rows.Err(), and map ErrNoRows to your own ErrNotFound.
• Scan nullable columns into pointers or null wrappers; plain values fail on NULL.
• Pass the request context to every query, and watch for pool exhaustion caused by leaked rows, slow loops and N+1 queries.