Using Context with Database Queries in Go

Pass context to database/sql with QueryContext, ExecContext and BeginTx so slow queries are cancelled, and handle the errors that result.

A slow database query is one of the most common reasons a Go service hangs. The database/sql package has a context-aware version of every method, so the deadline and cancellation of a request can reach the database driver and stop the query.

The Context Methods

Each database/sql method that talks to the database has a ...Context version that takes ctx first:

db.Query(...)      -> db.QueryContext(ctx, ...)
db.QueryRow(...)   -> db.QueryRowContext(ctx, ...)
db.Exec(...)       -> db.ExecContext(ctx, ...)
db.Begin()         -> db.BeginTx(ctx, nil)
db.Ping()          -> db.PingContext(ctx)

The versions without Context use context.Background() internally, which means they can never be cancelled or timed out. In server code, always use the Context versions.

A Query With a Timeout

The following shows the shape of a typical repository function. It needs a real database driver to run, so it is shown as a fragment:

func (s *Store) UserByEmail(ctx context.Context, email string) (User, error) {
	ctx, cancel := context.WithTimeout(ctx, 2*time.Second)
	defer cancel()

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

	switch {
	case errors.Is(err, sql.ErrNoRows):
		return User{}, ErrNotFound
	case errors.Is(err, context.DeadlineExceeded):
		return User{}, fmt.Errorf("user by email: query took longer than 2s: %w", err)
	case err != nil:
		return User{}, fmt.Errorf("user by email: %w", err)
	}
	return u, nil
}

The function accepts the caller's ctx, so it stops if the HTTP request is cancelled, and also adds its own 2 second limit for this query. Whichever comes first wins.

How a cancelled query actually stops depends on the driver. PostgreSQL drivers such as pgx send a cancel request to the server, while others close the connection. Either way, your Go code gets an error back instead of waiting.

Different drivers, different error text:

Some drivers return context.DeadlineExceeded wrapped, others return their own error such as canceling statement due to user request. Checking ctx.Err() after a failed query tells you reliably whether the context was the cause, whatever the driver reports.

Simulating the Pattern Without a Database

This runnable program uses a fake store with the same shape, so you can see the timeout behavior:

package main

import (
	"context"
	"errors"
	"fmt"
	"time"
)

type Store struct{ delay time.Duration }

func (s *Store) QueryRowContext(ctx context.Context, q string) (string, error) {
	select {
	case <-time.After(s.delay):
		return "asha", nil
	case <-ctx.Done():
		return "", ctx.Err()
	}
}

func userName(ctx context.Context, s *Store) (string, error) {
	ctx, cancel := context.WithTimeout(ctx, 50*time.Millisecond)
	defer cancel()
	name, err := s.QueryRowContext(ctx, "SELECT name FROM users WHERE id = 1")
	if err != nil {
		if ctx.Err() != nil {
			return "", fmt.Errorf("user name: %w", ctx.Err())
		}
		return "", err
	}
	return name, nil
}

func main() {
	fast := &Store{delay: 5 * time.Millisecond}
	slow := &Store{delay: 500 * time.Millisecond}

	fmt.Println(userName(context.Background(), fast))
	_, err := userName(context.Background(), slow)
	fmt.Println(err, errors.Is(err, context.DeadlineExceeded))
}

Output:

asha <nil>
user name: context deadline exceeded true

Transactions and Context

db.BeginTx(ctx, opts) ties a transaction to a context. If the context is cancelled before Commit, the sql package rolls the transaction back automatically:

tx, err := db.BeginTx(ctx, nil)
if err != nil {
	return err
}
defer tx.Rollback() // no effect after a successful Commit

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

Use the same ctx for BeginTx and every statement inside the transaction.

Rows must be closed:

When you use QueryContext, always defer rows.Close() and check rows.Err() after the loop. A cancelled context can end the loop early, and only rows.Err() tells you that the results are incomplete.

Database calls that cannot hang:

Every query uses a ...Context method with the request's ctx, important queries add their own WithTimeout, and errors are checked with errors.Is. A slow database then produces fast, clear errors instead of stuck requests.

Summary

Use QueryContext, QueryRowContext, ExecContext and BeginTx instead of the context-free methods, passing the request's ctx so cancellation reaches the driver. Add a per-query WithTimeout where it matters, check ctx.Err() or errors.Is to tell timeouts from other failures, close rows and check rows.Err(), and let cancelled contexts roll back transactions automatically.