Irbisa · cheatsheetSeptember 19, 2026

Databases & SQL in Go

database/sql pooling, transactions, NULLs, pgx, migrations

Middle Developer16 itemscompressed for a skim

01
sql.Open returns no error against a database that is not running, and the service logs "connected" before the first request fails. What did sql.Open actually do?
Easy
sql.Open never dials. It builds a *sql.DB, which is a pool holding zero connections, and the first real connection is opened lazily by the first query or by Ping.
```
func openDB(ctx context.Context, dsn string) (*sql.DB, error) {
	// No network traffic happens here. An error means "no such driver" or a
	// malformed DSN, never "the database is down".
	db, err := sql.Open("pgx", dsn)
	if err != nil {
		return nil, fmt.Errorf("open: %w", err)
…
```
02
A cleanup job runs db.Query("DELETE FROM sessions WHERE expires_at < now()") every minute. The rows do disappear, and after a few hours every other request blocks. What is wrong with the call?
Easy
Query hands you an open *sql.Rows that keeps a pool connection checked out until you close it — a write issued through Query is never closed, so each run leaks one connection until the pool has none left and every caller waits.
```
// ❌ Query on a write: the rows are never closed, so the connection is never
// returned to the pool. The DELETE itself runs fine, which is what hides it.
rows, err := db.Query("DELETE FROM sessions WHERE expires_at < now()")
if err != nil {
	return err
}
…
```
03
GET /users/42 for a user that does not exist answers 500, and the log line is sql: no rows in result set. Why did QueryRow not return that error, and where does it belong?
Easy
QueryRow defers every error to Scan, and a missing row arrives there as the sentinel sql.ErrNoRows — the repository has to translate it into a not-found of its own instead of letting an unknown error reach the handler.
```
var ErrUserNotFound = errors.New("user not found")

func (r *Repo) User(ctx context.Context, id int64) (User, error) {
	var u User
	err := r.db.QueryRowContext(ctx,
		"SELECT id, email, created_at FROM users WHERE id = $1", id,
…
```
04
Adding one nullable column to a SELECT turns a query that ran for months into converting NULL to string is unsupported. Name the ways out, and say which one quietly changes the meaning of the data.
Easy
A Go string cannot represent NULL, so Scan refuses: scan into *string or sql.Null[T], or remove the NULL in SQL with COALESCE — and only COALESCE erases the difference between "no value" and "empty value".
```
func profile(ctx context.Context, db *sql.DB, id int64) error {
	// ❌ a plain string has no room for NULL:
	// sql: Scan error on column index 0, name "nickname":
	// converting NULL to string is unsupported
	// var nickname string
…
```
05
Most nights the export writes 4,812 rows; some nights four thousand and something, and the log never shows an error. The body is a plain for rows.Next() { ... }. What is missing?
Medium
rows.Err() after the loop. Next returns false both when the result set is exhausted and when the iteration failed, so a connection dropped mid-stream is indistinguishable from a clean finish unless you ask.
```
func exportUsers(ctx context.Context, db *sql.DB, w io.Writer) error {
	rows, err := db.QueryContext(ctx, "SELECT id, email FROM users ORDER BY id")
	if err != nil {
		return fmt.Errorf("query users: %w", err)
	}
	defer rows.Close() // still needed: an early return inside the loop leaks the conn
…
```
06
Postgres logs sorry, too many clients already at peak. Someone adds db.SetMaxOpenConns(10); the errors stop and p99 goes from 40 ms to 3 s while the database sits at 12% CPU. Which knobs are wrong, and what do you read to size them?
Medium
Both symptoms are one mis-sized pool: unlimited, every goroutine opened its own backend until the server's max_connections ran out; at ten, the requests now queue inside Go, and db.Stats().WaitCount/WaitDuration is where those seconds are.
```
func configurePool(db *sql.DB) {
	// Per pod. Postgres max_connections is shared by every pod, every cron job
	// and whatever a human has open in psql.
	db.SetMaxOpenConns(20)
	// Equal to MaxOpenConns: otherwise the pool re-dials constantly under load.
	db.SetMaxIdleConns(20)
…
```
07
Search builds its query with fmt.Sprintf("... WHERE name ILIKE '%%%s%%' ORDER BY %s", q, sortBy). Placeholders fix one of those interpolations. What do you do with the other?
Medium
Values go in placeholders, identifiers cannot — $1 makes q safe because the value is sent to the server separately from the SQL text, but a column name in ORDER BY is only safe if you match it against a whitelist you wrote.
```
// ✅ the value is bound; the ordering is chosen from a map you control
var sortable = map[string]string{
	"newest":   "created_at DESC",
	"oldest":   "created_at ASC",
	"relevant": "rank DESC",
}
…
```
08
The client disconnects after 2 s and the handler returns immediately, but pg_stat_activity still shows that query running 40 s later. What was not wired up, and what does the context actually do when it is?
Medium
The query went out through db.Query instead of db.QueryContext(ctx, ...), so the request's cancellation never reached the driver — and even when it does, a client-side cancel is best-effort: statement_timeout on the server is the half that cannot be lost.
```
func (r *Repo) TopSellers(ctx context.Context, since time.Time) ([]Row, error) {
	// The handler's context carries the client's disconnect; this query gets a
	// slice of it, so one slow report cannot hold a pool connection for a minute.
	ctx, cancel := context.WithTimeout(ctx, 500*time.Millisecond)
	defer cancel()
…
```
09
Someone replaces every db.QueryContext with a *sql.Stmt prepared once at startup "for speed". p99 gets worse and PgBouncer starts returning errors. What does Prepare actually mean when a pool sits underneath?
Medium
A prepared statement belongs to one connection. A *sql.Stmt shared across a pool re-prepares itself on every connection it happens to land on, and with a transaction-mode pooler in front, the connection it prepared on is not the connection it executes on.
```
// ❌ a process-wide Stmt over a pool: re-prepared on every connection it touches,
// and it breaks outright behind a transaction-mode pooler.
// var getUser *sql.Stmt // prepared in main(), used from every handler

// ✅ just pass the args: one round trip on any driver implementing QueryerContext,
// and pgx keeps the parsed statement in its per-connection cache for you.
…
```
10
Inside a BeginTx block a helper is called that takes *sql.DB. In tests it is fine; under load the service stops dead with every connection busy. Name the two bugs in that one line.
Medium
A *sql.Tx owns one pool connection for its whole life, and a helper that reaches for db runs on a second connection outside the transaction — so it cannot see the uncommitted rows, it blocks on the rows the transaction locked, and every request now needs two connections to finish.
```
// Everything a query needs, implemented by *sql.DB, *sql.Tx and *sql.Conn.
type DBTX interface {
	ExecContext(context.Context, string, ...any) (sql.Result, error)
	QueryContext(context.Context, string, ...any) (*sql.Rows, error)
	QueryRowContext(context.Context, string, ...any) *sql.Row
}
…
```
11
An import loads 200,000 rows with one INSERT per row inside a single transaction and takes eleven minutes. The database is bored. What do you use instead, and what is the limit on the obvious middle option?
Medium
200,000 round trips is the runtime — use COPY (pgx.CopyFrom) for a bulk load, or multi-row INSERTs if you must stay on database/sql, remembering that the wire protocol allows at most 65,535 bind parameters per statement.
```
// ❌ 200k round trips, however short each statement is
// for _, e := range events {
// 	tx.ExecContext(ctx, "INSERT INTO events (user_id, kind, at) VALUES ($1,$2,$3)",
// 		e.UserID, e.Kind, e.At)
// }
…
```
12
An endpoint returning 100 orders issues 101 queries. Each one takes 0.4 ms in the database, yet the endpoint takes 220 ms. Where did the time go, and what replaces the loop?
Medium
Round trips, not query time. 101 sequential queries at roughly 2 ms of network and protocol overhead each is the 220 ms; collect the parent ids and fetch all the children in one query with = ANY($1), then stitch them together in Go.
```
// ❌ N+1: one query for the orders, one more for every order's items
// for i, o := range orders {
// 	rows, _ := db.QueryContext(ctx, "SELECT sku, qty FROM items WHERE order_id = $1", o.ID)
// 	orders[i].Items = scanItems(rows)
// }
…
```
13
The team is picking between GORM, sqlx and sqlc for a service whose hot path is three queries. What does each one cost, and what does the choice not change?
Hard
The axis that matters is whether the SQL that runs is the SQL you can read in review — sqlc generates Go from SQL you wrote, sqlx only removes the Scan boilerplate, and GORM buys write velocity by hiding the query behind a builder.
```
// The boundary the service sees. One interface, any of the three behind it.
type OrderStore interface {
	Order(ctx context.Context, id int64) (Order, error)
	Create(ctx context.Context, o Order) (int64, error)
}
…
```
14
A transfer worker running eight-wide starts failing a few times a minute with SQLSTATE 40P01, deadlock detected, once traffic doubles. Each transaction locks both accounts with SELECT ... FOR UPDATE. What is the fix that is not a retry?
Hard
Acquire the row locks in a deterministic order. A transfer A→B and a transfer B→A lock in opposite orders and wait on each other; sorting the ids before the `SELECT ...
```
// ❌ lock order follows the transfer direction, so A→B and B→A deadlock
// tx.ExecContext(ctx, "SELECT 1 FROM accounts WHERE id = $1 FOR UPDATE", from)
// tx.ExecContext(ctx, "SELECT 1 FROM accounts WHERE id = $1 FOR UPDATE", to)

// ✅ one deterministic order for every transaction in the system
func transfer(ctx context.Context, tx *sql.Tx, from, to int64, amount int64) error {
…
```
15
After moving the ledger to sql.LevelSerializable, the service returns 500s with could not serialize access due to read/write dependencies among transactions (40001). Where does the retry go, and what must never be inside the loop?
Hard
The whole transaction is the unit of retry. A serialisation failure rolls back everything the transaction did, so you re-run the closure from BeginTx and re-read every value — and anything that is not a database write has to live outside the loop.
```
func (r *Repo) InSerializableTx(ctx context.Context, fn func(DBTX) error) error {
	const maxAttempts = 4
	var err error
	for attempt := 1; attempt <= maxAttempts; attempt++ {
		err = r.runOnce(ctx, fn) // the entire transaction, re-read from scratch
		if err == nil || !isRetryable(err) {
…
```
16
A release renames users.name to full_name in the same deploy as the code that reads it, and during the rolling update both the old and the new pods return 500s. How do you ship a rename with no failed requests?
Hard
Expand, migrate, contract. For the length of a rolling deploy two versions of the code talk to one database, so a rename ships as add, dual-write, backfill, switch reads, drop — across at least two releases, never one.
```
// 001_add_full_name.sql — release 1, additive only, and it never blocks for long
// -- +goose Up
// SET lock_timeout = '3s';                      -- fail fast, do not queue behind a SELECT
// ALTER TABLE users ADD COLUMN full_name text;  -- nullable, so no table rewrite
// -- +goose Down
// ALTER TABLE users DROP COLUMN full_name;
…
```

Databases & SQL in Go

`sql.Open` returns no error against a database that is not running, and the service logs "connected" before the first request fails. What did `sql.Open` actually do?

A cleanup job runs `db.Query("DELETE FROM sessions WHERE expires_at < now()")` every minute. The rows do disappear, and after a few hours every other request blocks. What is wrong with the call?

`GET /users/42` for a user that does not exist answers 500, and the log line is `sql: no rows in result set`. Why did `QueryRow` not return that error, and where does it belong?

Adding one nullable column to a SELECT turns a query that ran for months into `converting NULL to string is unsupported`. Name the ways out, and say which one quietly changes the meaning of the data.

Most nights the export writes 4,812 rows; some nights four thousand and something, and the log never shows an error. The body is a plain `for rows.Next() { ... }`. What is missing?

Postgres logs `sorry, too many clients already` at peak. Someone adds `db.SetMaxOpenConns(10)`; the errors stop and p99 goes from 40 ms to 3 s while the database sits at 12% CPU. Which knobs are wrong, and what do you read to size them?

Search builds its query with `fmt.Sprintf("... WHERE name ILIKE '%%%s%%' ORDER BY %s", q, sortBy)`. Placeholders fix one of those interpolations. What do you do with the other?

The client disconnects after 2 s and the handler returns immediately, but `pg_stat_activity` still shows that query running 40 s later. What was not wired up, and what does the context actually do when it is?

Someone replaces every `db.QueryContext` with a `*sql.Stmt` prepared once at startup "for speed". p99 gets worse and PgBouncer starts returning errors. What does `Prepare` actually mean when a pool sits underneath?

Inside a `BeginTx` block a helper is called that takes `*sql.DB`. In tests it is fine; under load the service stops dead with every connection busy. Name the two bugs in that one line.

An import loads 200,000 rows with one `INSERT` per row inside a single transaction and takes eleven minutes. The database is bored. What do you use instead, and what is the limit on the obvious middle option?

An endpoint returning 100 orders issues 101 queries. Each one takes 0.4 ms in the database, yet the endpoint takes 220 ms. Where did the time go, and what replaces the loop?

The team is picking between GORM, `sqlx` and `sqlc` for a service whose hot path is three queries. What does each one cost, and what does the choice not change?

A transfer worker running eight-wide starts failing a few times a minute with SQLSTATE `40P01`, `deadlock detected`, once traffic doubles. Each transaction locks both accounts with `SELECT ... FOR UPDATE`. What is the fix that is not a retry?

After moving the ledger to `sql.LevelSerializable`, the service returns 500s with `could not serialize access due to read/write dependencies among transactions` (40001). Where does the retry go, and what must never be inside the loop?

A release renames `users.name` to `full_name` in the same deploy as the code that reads it, and during the rolling update both the old and the new pods return 500s. How do you ship a rename with no failed requests?

sql.Open returns no error against a database that is not running, and the service logs "connected" before the first request fails. What did sql.Open actually do?

A cleanup job runs db.Query("DELETE FROM sessions WHERE expires_at < now()") every minute. The rows do disappear, and after a few hours every other request blocks. What is wrong with the call?

GET /users/42 for a user that does not exist answers 500, and the log line is sql: no rows in result set. Why did QueryRow not return that error, and where does it belong?

Adding one nullable column to a SELECT turns a query that ran for months into converting NULL to string is unsupported. Name the ways out, and say which one quietly changes the meaning of the data.

Most nights the export writes 4,812 rows; some nights four thousand and something, and the log never shows an error. The body is a plain for rows.Next() { ... }. What is missing?

Postgres logs sorry, too many clients already at peak. Someone adds db.SetMaxOpenConns(10); the errors stop and p99 goes from 40 ms to 3 s while the database sits at 12% CPU. Which knobs are wrong, and what do you read to size them?

Search builds its query with fmt.Sprintf("... WHERE name ILIKE '%%%s%%' ORDER BY %s", q, sortBy). Placeholders fix one of those interpolations. What do you do with the other?

The client disconnects after 2 s and the handler returns immediately, but pg_stat_activity still shows that query running 40 s later. What was not wired up, and what does the context actually do when it is?

Someone replaces every db.QueryContext with a *sql.Stmt prepared once at startup "for speed". p99 gets worse and PgBouncer starts returning errors. What does Prepare actually mean when a pool sits underneath?

Inside a BeginTx block a helper is called that takes *sql.DB. In tests it is fine; under load the service stops dead with every connection busy. Name the two bugs in that one line.

An import loads 200,000 rows with one INSERT per row inside a single transaction and takes eleven minutes. The database is bored. What do you use instead, and what is the limit on the obvious middle option?

An endpoint returning 100 orders issues 101 queries. Each one takes 0.4 ms in the database, yet the endpoint takes 220 ms. Where did the time go, and what replaces the loop?

The team is picking between GORM, sqlx and sqlc for a service whose hot path is three queries. What does each one cost, and what does the choice not change?

A transfer worker running eight-wide starts failing a few times a minute with SQLSTATE 40P01, deadlock detected, once traffic doubles. Each transaction locks both accounts with SELECT ... FOR UPDATE. What is the fix that is not a retry?

After moving the ledger to sql.LevelSerializable, the service returns 500s with could not serialize access due to read/write dependencies among transactions (40001). Where does the retry go, and what must never be inside the loop?

A release renames users.name to full_name in the same deploy as the code that reads it, and during the rolling update both the old and the new pods return 500s. How do you ship a rename with no failed requests?

`sql.Open` returns no error against a database that is not running, and the service logs "connected" before the first request fails. What did `sql.Open` actually do?

A cleanup job runs `db.Query("DELETE FROM sessions WHERE expires_at < now()")` every minute. The rows do disappear, and after a few hours every other request blocks. What is wrong with the call?

`GET /users/42` for a user that does not exist answers 500, and the log line is `sql: no rows in result set`. Why did `QueryRow` not return that error, and where does it belong?

Adding one nullable column to a SELECT turns a query that ran for months into `converting NULL to string is unsupported`. Name the ways out, and say which one quietly changes the meaning of the data.

Most nights the export writes 4,812 rows; some nights four thousand and something, and the log never shows an error. The body is a plain `for rows.Next() { ... }`. What is missing?

Postgres logs `sorry, too many clients already` at peak. Someone adds `db.SetMaxOpenConns(10)`; the errors stop and p99 goes from 40 ms to 3 s while the database sits at 12% CPU. Which knobs are wrong, and what do you read to size them?

Search builds its query with `fmt.Sprintf("... WHERE name ILIKE '%%%s%%' ORDER BY %s", q, sortBy)`. Placeholders fix one of those interpolations. What do you do with the other?

The client disconnects after 2 s and the handler returns immediately, but `pg_stat_activity` still shows that query running 40 s later. What was not wired up, and what does the context actually do when it is?

Someone replaces every `db.QueryContext` with a `*sql.Stmt` prepared once at startup "for speed". p99 gets worse and PgBouncer starts returning errors. What does `Prepare` actually mean when a pool sits underneath?

Inside a `BeginTx` block a helper is called that takes `*sql.DB`. In tests it is fine; under load the service stops dead with every connection busy. Name the two bugs in that one line.

An import loads 200,000 rows with one `INSERT` per row inside a single transaction and takes eleven minutes. The database is bored. What do you use instead, and what is the limit on the obvious middle option?

The team is picking between GORM, `sqlx` and `sqlc` for a service whose hot path is three queries. What does each one cost, and what does the choice not change?

A transfer worker running eight-wide starts failing a few times a minute with SQLSTATE `40P01`, `deadlock detected`, once traffic doubles. Each transaction locks both accounts with `SELECT ... FOR UPDATE`. What is the fix that is not a retry?

After moving the ledger to `sql.LevelSerializable`, the service returns 500s with `could not serialize access due to read/write dependencies among transactions` (40001). Where does the retry go, and what must never be inside the loop?

A release renames `users.name` to `full_name` in the same deploy as the code that reads it, and during the rolling update both the old and the new pods return 500s. How do you ship a rename with no failed requests?