Handle Database Errors in Go With Clear Useful Messages
Turn driver errors into five errors your program owns, classified by SQLSTATE and tested against ten real PostgreSQL failures, with nothing lost in wrapping
Most Go code that talks to a database handles errors in one of two ways. It returns them untouched, so that an HTTP handler ends up printing pq: duplicate key value violates unique constraint "users_email_key" to a user, or it checks if err != nil and returns a generic 500, so that a duplicate email and a dead database look the same. Both fail the caller. The first leaks the schema; the second hides a problem the user could fix, or one the service could retry. This article builds the layer in between: a small set of errors your program owns, a function that classifies driver errors into them, and tests against real failures from PostgreSQL 18.6 on Go 1.25.6.
Tested with Go 1.25.6, pgx v5.11.0, PostgreSQL 18.6 in Docker, macOS on Apple Silicon (arm64)
It belongs to the database cornerstone. The two most common cases have their own articles: detecting unique violations and ErrNoRows. The retryable class is covered in retrying serialization failures, and the pgx specific type in pgx error codes.
The errors the program owns
The rest of the program should not import a driver to decide what to do with an error. It should see a handful of names, each tied to a decision:
ErrNotFound: the thing does not exist. A 404, or a branch in the caller's logic.ErrConflict: the request clashes with existing data. A 409; the user can change the input.ErrInvalid: the data broke a rule the database enforces (a check, a foreign key, a length, a type). A 400 or 422.ErrRetryable: the operation may succeed if repeated: a serialization failure, a deadlock, a lock timeout.ErrUnavailable: the database could not be reached or ran out of time. A 503; retry with backoff, or fail fast.
Anything else is a bug or an unexpected condition: it stays as it is, logged with its details, and becomes a 500.
The classifier
package main
import (
"context"
"database/sql"
"errors"
"fmt"
"log"
"net"
"time"
"github.com/jackc/pgx/v5/pgconn"
_ "github.com/jackc/pgx/v5/stdlib"
)
// The errors the rest of the program is allowed to see.
var (
ErrNotFound = errors.New("not found")
ErrConflict = errors.New("conflict")
ErrInvalid = errors.New("invalid data")
ErrRetryable = errors.New("try again")
ErrUnavailable = errors.New("database unavailable")
)
// Op names what the code was doing, so the log says more than the driver does.
type Op struct {
Name string
Err error
}
func (o *Op) Error() string { return o.Name + ": " + o.Err.Error() }
func (o *Op) Unwrap() error { return o.Err }
// Wrap turns a driver error into one of the program's errors and keeps the original reachable with errors.Is and errors.As.
func Wrap(op string, err error) error {
if err == nil {
return nil
}
var pg *pgconn.PgError
var netErr net.Error
switch {
case errors.Is(err, sql.ErrNoRows):
return &Op{op, fmt.Errorf("%w: %w", ErrNotFound, err)}
case errors.Is(err, context.DeadlineExceeded), errors.Is(err, context.Canceled):
return &Op{op, fmt.Errorf("%w: %w", ErrUnavailable, err)}
case errors.As(err, &pg):
switch pg.Code {
case "23505": // unique_violation
return &Op{op, fmt.Errorf("%w: %w", ErrConflict, err)}
case "23503", "23514", "23502", "22001", "22P02", "22003": // foreign key, check, not null, too long, bad text, out of range
return &Op{op, fmt.Errorf("%w: %w", ErrInvalid, err)}
case "40001", "40P01", "55P03": // serialization failure, deadlock, lock not available
return &Op{op, fmt.Errorf("%w: %w", ErrRetryable, err)}
case "57014", "57P01", "53300", "08006", "08003": // cancelled, admin shutdown, too many connections, connection failures
return &Op{op, fmt.Errorf("%w: %w", ErrUnavailable, err)}
}
case errors.As(err, &netErr):
return &Op{op, fmt.Errorf("%w: %w", ErrUnavailable, err)}
}
return &Op{op, err} // unknown: keep it, and let it become a 500
}
func kind(err error) string {
for _, k := range []struct {
e error
n string
}{{ErrNotFound, "not found"}, {ErrConflict, "conflict"}, {ErrInvalid, "invalid"}, {ErrRetryable, "retryable"}, {ErrUnavailable, "unavailable"}} {
if errors.Is(err, k.e) {
return k.n
}
}
return "unknown"
}
func main() {
ctx := context.Background()
db, err := sql.Open("pgx", "postgres://postgres:[email protected]:55432/lab?sslmode=disable")
if err != nil {
log.Fatal(err)
}
defer db.Close()
db.SetMaxOpenConns(4)
for _, q := range []string{
"drop table if exists child658, parent658",
"create table parent658 (id int primary key, code text unique, qty int check (qty >= 0), name varchar(5) not null)",
"create table child658 (id int primary key, parent_id int references parent658)",
"insert into parent658 values (1, 'a', 1, 'one')",
} {
if _, err := db.ExecContext(ctx, q); err != nil {
log.Fatal(err)
}
}
type scenario struct {
name string
run func() error
}
scenarios := []scenario{
{"select a row that does not exist", func() error {
var n int
return db.QueryRowContext(ctx, "select id from parent658 where id = 99").Scan(&n)
}},
{"insert a duplicate unique value", func() error {
_, err := db.ExecContext(ctx, "insert into parent658 values (2, 'a', 1, 'two')")
return err
}},
{"insert a child with no parent", func() error {
_, err := db.ExecContext(ctx, "insert into child658 values (1, 42)")
return err
}},
{"violate a check constraint", func() error {
_, err := db.ExecContext(ctx, "insert into parent658 values (3, 'c', -5, 'three')")
return err
}},
{"insert null into a not null column", func() error {
_, err := db.ExecContext(ctx, "insert into parent658 (id, code, qty, name) values (4, 'd', 1, null)")
return err
}},
{"text too long for varchar(5)", func() error {
_, err := db.ExecContext(ctx, "insert into parent658 values (5, 'e', 1, 'far too long')")
return err
}},
{"text where an integer is expected", func() error {
_, err := db.ExecContext(ctx, "insert into parent658 values ($1, 'f', 1, 'six')", "not a number")
return err
}},
{"query a table that does not exist", func() error {
_, err := db.ExecContext(ctx, "select * from no_such_table658")
return err
}},
{"a statement longer than the context allows", func() error {
c, cancel := context.WithTimeout(ctx, 100*time.Millisecond)
defer cancel()
_, err := db.ExecContext(c, "select pg_sleep(1)")
return err
}},
{"a serialization failure", func() error {
a, _ := db.BeginTx(ctx, &sql.TxOptions{Isolation: sql.LevelSerializable})
b, _ := db.BeginTx(ctx, &sql.TxOptions{Isolation: sql.LevelSerializable})
defer a.Rollback()
defer b.Rollback()
var n int
_ = a.QueryRowContext(ctx, "select sum(qty) from parent658").Scan(&n)
_ = b.QueryRowContext(ctx, "select sum(qty) from parent658").Scan(&n)
_, _ = a.ExecContext(ctx, "update parent658 set qty = qty + 1 where id = 1")
_, _ = b.ExecContext(ctx, "insert into parent658 values (7, 'g', 1, 'seven')")
if err := a.Commit(); err != nil {
return err
}
return b.Commit()
}},
}
fmt.Printf("%-42s %-12s %s\n", "what went wrong", "classified", "SQLSTATE and message")
for _, s := range scenarios {
err := Wrap(s.name, s.run())
var pg *pgconn.PgError
detail := "(not a server error)"
if errors.As(err, &pg) {
detail = pg.Code + " " + pg.Message
}
fmt.Printf("%-42s %-12s %s\n", s.name, kind(err), detail)
}
err = Wrap("load user", sql.ErrNoRows)
var op *Op
fmt.Println("\nwrapped error text:", err, "| errors.Is ErrNotFound:", errors.Is(err, ErrNotFound), "| errors.Is sql.ErrNoRows:", errors.Is(err, sql.ErrNoRows), "| errors.As *Op:", errors.As(err, &op))
}what went wrong classified SQLSTATE and message
select a row that does not exist not found (not a server error)
insert a duplicate unique value conflict 23505 duplicate key value violates unique constraint "parent658_code_key"
insert a child with no parent invalid 23503 insert or update on table "child658" violates foreign key constraint "child658_parent_id_fkey"
violate a check constraint invalid 23514 new row for relation "parent658" violates check constraint "parent658_qty_check"
insert null into a not null column invalid 23502 null value in column "name" of relation "parent658" violates not-null constraint
text too long for varchar(5) invalid 22001 value too long for type character varying(5)
text where an integer is expected invalid 22P02 invalid input syntax for type integer: "not a number"
query a table that does not exist unknown 42P01 relation "no_such_table658" does not exist
a statement longer than the context allows unavailable (not a server error)
a serialization failure retryable 40001 could not serialize access due to read/write dependencies among transactions
wrapped error text: load user: not found: sql: no rows in result set | errors.Is ErrNotFound: true | errors.Is sql.ErrNoRows: true | errors.As *Op: trueWrap(op, err) is the only place that knows about drivers. It checks, in order:
errors.Is(err, sql.ErrNoRows)givesErrNotFound.- A context deadline or cancellation gives
ErrUnavailable: the work did not finish. - A
*pgconn.PgError, found witherrors.As, is classified by its SQLSTATE code, the five character code that is stable across versions and languages.23505is a unique violation, so conflict.23503,23514,23502,22001,22P02and22003(foreign key, check, not null, value too long, invalid text for a type, out of range) areErrInvalid.40001,40P01and55P03(serialization failure, deadlock, lock not available) are retryable.57014,57P01,53300and the08class (cancelled, shutdown, too many connections, connection failures) are unavailable. - A network error from the
netpackage givesErrUnavailable. - Anything else is returned inside an
Opwith its original error intact.
The code is the right thing to switch on. Messages change between versions and with lc_messages, so never match on text. The constraint name (pg.ConstraintName) is the right thing for telling two unique constraints apart, as the next article shows.
What happened to ten real failures
The program provokes ten failures against real tables and prints how each was classified, with the SQLSTATE and message:
| Failure | Class | Code |
|---|---|---|
| select a row that does not exist | not found | none, sql.ErrNoRows |
| duplicate unique value | conflict | 23505 |
| child row with no parent | invalid | 23503 |
| check constraint violated | invalid | 23514 |
| null into a not null column | invalid | 23502 |
text too long for varchar(5) | invalid | 22001 |
| text where an integer is expected | invalid | 22P02 |
| table that does not exist | unknown | 42P01 |
| statement longer than the context | unavailable | none, deadline |
| serialization failure between two transactions | retryable | 40001 |
Nine of ten landed in a category with an obvious response. The table that does not exist stayed unknown, and that is correct: it means a migration did not run or the code names the wrong table, and no caller can fix it. Letting it fall through to a 500 with the full error in the log is the right behaviour, and mapping it to something friendly would hide a deployment error.
The serialization failure needed two real transactions that read and wrote overlapping data at the Serializable level; PostgreSQL aborted the second commit with could not serialize access due to read/write dependencies among transactions. That is exactly the case where the caller should re-run the whole transaction, and ErrRetryable is how it knows.
Wrapping so nothing is lost
The last line of the output shows why Wrap uses fmt.Errorf("%w: %w", ErrNotFound, err): Go 1.20 and later allow more than one %w, so the resulting error answers errors.Is for both. The line shows errors.Is(err, ErrNotFound) true, errors.Is(err, sql.ErrNoRows) true and errors.As(err, &op) true for the *Op that carries the name of the operation. The caller can test the category, a log line can still find the driver's own error, and the text reads load user: not found: sql: no rows in result set.
The Op type is small and pays for itself. A bare sql: no rows in result set in a log says nothing about which of two hundred queries produced it. The operation name does. Name operations after the repository method ("users.ByEmail") and the log line is searchable.
Using it at the edges
In repositories, wrap at the boundary and nowhere else:
func (s Store) ByEmail(ctx context.Context, email string) (User, error) {
var u User
err := s.db.QueryRowContext(ctx, "select id, email from users where email = $1", email).Scan(&u.ID, &u.Email)
return u, Wrap("users.ByEmail", err)
}In handlers, translate categories into responses in one function, so the mapping is a table and not scattered ifs:
func status(err error) int {
switch {
case errors.Is(err, ErrNotFound):
return http.StatusNotFound
case errors.Is(err, ErrConflict):
return http.StatusConflict
case errors.Is(err, ErrInvalid):
return http.StatusUnprocessableEntity
case errors.Is(err, ErrUnavailable):
return http.StatusServiceUnavailable
default:
return http.StatusInternalServerError
}
}Return a stable message to the client for each category, never the driver's text: the text names tables, columns and constraints, which tells an attacker about your schema and tells a user nothing useful. Put the detail in the log, with the operation name and a request id.
Retries belong to the caller that can repeat the work
ErrRetryable is a signal, not a loop. The place that can safely repeat is the code that owns the whole unit of work: a transaction function wrapped in a retry, as in unit of work. A repository method that retries one statement inside a transaction does the wrong thing, since the transaction has already been aborted. Retry only idempotent operations, a bounded number of times, with a delay, and count the retries in a metric: a rising count is an early sign of contention.
ErrUnavailable has two sensible responses: fail fast with a 503 so the caller can decide, or retry reads with backoff within the request's deadline. Do not retry writes whose outcome is unknown (a timeout after sending an insert) unless they are idempotent.
Testing the mapping
The lab is the test: it provokes each failure against a real database and asserts the category. Keep such a test in the repository, since the classification depends on server behaviour that a mock cannot reproduce, such as which code a failed cast raises. Add a case whenever production shows you an error you had not classified. And keep the table of codes in one place, with a comment for each group, so the next person who sees 23P01 in a log knows where to add it.
The codes are listed in the PostgreSQL manual's appendix on error codes, which groups them by class, and errors.Is and errors.As are described in the errors package documentation.
