Skip to content
Documentation

Database

FieldValue
TypeSkill Resource
Source~/.copilot/skills/backend/references/database.md
DescriptionNot specified

Source Content

Database

The repository layer (references/layering.md) owns all SQL — no query, transaction, or row-mapping code lives above it. This file is what goes inside a repository once you’re there: transaction discipline, isolation and locking, retry semantics, pagination, scanning, bulk writes, error mapping, and how each of those gets tested.

Transactions

defer tx.Rollback(ctx) immediately after Begin(ctx) succeeds — before any statement runs. Rollback is a no-op once Commit(ctx) has succeeded, so this single line is what guarantees the transaction is closed on every early-return path, not just the ones you remembered to handle.

internal/repository/invoice.go
func (r *InvoiceRepository) FinalizeAndCharge(ctx context.Context, inv domain.Invoice, chargeCents int64) error {
  tx, err := r.pool.Begin(ctx)
  if err != nil {
    return fmt.Errorf("begin tx: %w", err)
  }
  defer tx.Rollback(ctx) // no-op after Commit; guarantees cleanup on every early return


  if _, err := tx.Exec(ctx, `UPDATE invoices SET status = 'finalized' WHERE id = $1`, inv.ID); err != nil {
    return fmt.Errorf("finalize invoice %s: %w", inv.ID, err)
  }
  if _, err := tx.Exec(ctx, `INSERT INTO charges (invoice_id, amount_cents) VALUES ($1, $2)`, inv.ID, chargeCents); err != nil {
    return fmt.Errorf("record charge for %s: %w", inv.ID, err)
  }


  if err := tx.Commit(ctx); err != nil {
    return fmt.Errorf("commit finalize %s: %w", inv.ID, err)
  }
  return nil
}

Isolation levels

Isolation levelPreventsUse when
Read Committed (default)Dirty readsOrdinary single- or multi-statement work with no cross-statement consistency requirement
Repeatable ReadDirty reads, non-repeatable readsA transaction takes multiple reads of the same rows and must see one consistent snapshot throughout
SerializableDirty reads, non-repeatable reads, phantom reads, write skewMultiple transactions read-then-write overlapping data and correctness requires as-if-executed-one-at-a-time behavior

Row locking

  • FOR UPDATE — takes an exclusive lock, blocking other lockers until you commit. Use for a read-modify-write inside one transaction, like decrementing an invoice balance.
  • FOR UPDATE NOWAIT — same lock, but fails immediately instead of queuing. Use when a contested row means the request should fail fast rather than pile up behind other transactions.
  • FOR SHARE — a shared lock: other readers can also FOR SHARE, but writers block. Use when you need the row to stay stable for a decision but aren’t writing to it yet.
SELECT id, status, total_cents FROM invoices WHERE id = $1 FOR UPDATE;

Retry rule: 40001 means retry the whole transaction

Postgres error code 40001 (serialization_failure) is raised under Serializable (and sometimes Repeatable Read) when the transaction can’t be placed in a valid serial order. The fix is to retry the entire transaction from Begin, not just the last statement — the transaction’s earlier reads may already be stale.

func (r *InvoiceRepository) withSerializableRetry(ctx context.Context, fn func(tx pgx.Tx) error) error {
  const maxAttempts = 3


  for attempt := 1; attempt <= maxAttempts; attempt++ {
    tx, err := r.pool.BeginTx(ctx, pgx.TxOptions{IsoLevel: pgx.Serializable})
    if err != nil {
      return fmt.Errorf("begin serializable tx: %w", err)
    }


    err = fn(tx)
    if err == nil {
      err = tx.Commit(ctx)
    }
    if err == nil {
      return nil
    }
    tx.Rollback(ctx)


    var pgErr *pgconn.PgError
    if errors.As(err, &pgErr) && pgErr.Code == "40001" {
      continue // serialization_failure: retry from the top, not just the failing statement
    }
    return err
  }
  return fmt.Errorf("exceeded %d attempts on serialization failure", maxAttempts)
}

Pagination: keyset beats offset

OFFSET still scans and discards every skipped row inside Postgres before returning a page, so cost grows with offset + limit — page 500 costs roughly 500 times what page 1 costs. A keyset (cursor) query with an index on the ordering column is O(limit) regardless of how deep the cursor is.

-- offset: scans and discards `offset` rows every time, cost grows with page depth
SELECT id, status, total_cents FROM invoices ORDER BY id OFFSET $1 LIMIT $2;
-- keyset: the index seeks straight to the cursor, cost is flat regardless of depth
SELECT id, status, total_cents FROM invoices WHERE id > $1 ORDER BY id LIMIT $2;

Scanning: pgx.CollectRows with pointer fields for nullable columns

pgx.CollectRows with pgx.RowToStructByName[T] is the default scan pattern — it maps columns to struct fields by name, so a query and its destination type stay obviously in sync. For nullable columns, prefer a pointer field (*string, *int64) over sql.NullString/sql.NullInt64: pgx scans directly into the pointer, and a domain struct reading inv.Notes != nil is more natural than unwrapping a NullString{String, Valid} pair everywhere it’s used.

type invoiceRow struct {
  ID         domain.InvoiceID
  Status     string
  TotalCents int64
  Notes      *string // nullable column; nil means "no notes", no sql.NullString needed
}


func (r *InvoiceRepository) ByID(ctx context.Context, id domain.InvoiceID) (domain.Invoice, error) {
  rows, err := r.pool.Query(ctx, `SELECT id, status, total_cents, notes FROM invoices WHERE id = $1`, id)
  if err != nil {
    return domain.Invoice{}, fmt.Errorf("query invoice %s: %w", id, err)
  }


  row, err := pgx.CollectExactlyOneRow(rows, pgx.RowToStructByName[invoiceRow])
  if err != nil {
    if errors.Is(err, pgx.ErrNoRows) {
      return domain.Invoice{}, domain.ErrNotFound
    }
    return domain.Invoice{}, fmt.Errorf("scan invoice %s: %w", id, err)
  }
  return row.toDomain(), nil
}

Bulk insert: multi-row VALUES up to ~1,000 rows, CopyFrom beyond it

A single INSERT ... VALUES (...), (...), ... statement is the sweet spot in the 100–1,000 row range — one round trip, one planned statement. Past that, switch to pgx.CopyFrom, which uses Postgres’s binary COPY protocol and scales to large one-shot loads without building an enormous SQL string.

func (r *InvoiceRepository) InsertMany(ctx context.Context, invoices []domain.Invoice) error {
  const cols = 3
  placeholders := make([]string, 0, len(invoices))
  args := make([]any, 0, len(invoices)*cols)


  for i, inv := range invoices {
    placeholders = append(placeholders, fmt.Sprintf("($%d, $%d, $%d)", i*cols+1, i*cols+2, i*cols+3))
    args = append(args, inv.ID, inv.Status, inv.TotalCents)
  }


  sql := "INSERT INTO invoices (id, status, total_cents) VALUES " + strings.Join(placeholders, ", ")
  if _, err := r.pool.Exec(ctx, sql, args...); err != nil {
    return fmt.Errorf("bulk insert %d invoices: %w", len(invoices), err)
  }
  return nil
}
func (r *InvoiceRepository) CopyInvoices(ctx context.Context, invoices []domain.Invoice) (int64, error) {
  rows := make([][]any, len(invoices))
  for i, inv := range invoices {
    rows[i] = []any{inv.ID, inv.Status, inv.TotalCents}
  }


  n, err := r.pool.CopyFrom(ctx,
    pgx.Identifier{"invoices"},
    []string{"id", "status", "total_cents"},
    pgx.CopyFromRows(rows),
  )
  if err != nil {
    return 0, fmt.Errorf("copy invoices: %w", err)
  }
  return n, nil
}

Error mapping: pgx.ErrNoRows to domain.ErrNotFound

errors.Is(err, sql.ErrNoRows) — or pgx’s own pgx.ErrNoRows when querying through pgx directly — maps to the domain sentinel domain.ErrNotFound at the repository boundary, the moment the error crosses out of the persistence layer. This is the same sentinel-error pattern from references/errors.md: the repository translates a storage-specific “not found” into a domain-level one, and every layer above only ever checks errors.Is(err, domain.ErrNotFound).

Testing

Three layers cover a repository, each with a different job:

  • Service unit tests against a fake repository — already covered in references/testing.md; the fake substitutes for the database entirely, no SQL involved.
  • sqlmock-style tests (e.g. pgxmock) for asserting the exact SQL a repository method issues. Reserve these for repositories with complex, hand-tuned queries where the SQL shape itself — not just the result — is worth pinning against regressions.
func TestInvoiceRepository_ByID_SQLShape(t *testing.T) {
  mock, err := pgxmock.NewPool()
  if err != nil {
    t.Fatalf("new mock pool: %v", err)
  }
  defer mock.Close()


  mock.ExpectQuery(`SELECT id, status, total_cents, notes FROM invoices WHERE id = \$1`).
    WithArgs("inv_42").
    WillReturnRows(pgxmock.NewRows([]string{"id", "status", "total_cents", "notes"}).
      AddRow("inv_42", "draft", int64(1000), (*string)(nil)))


  repo := NewInvoiceRepository(mock)
  if _, err := repo.ByID(context.Background(), "inv_42"); err != nil {
    t.Fatalf("ByID() err = %v", err)
  }
  if err := mock.ExpectationsWereMet(); err != nil {
    t.Fatalf("unmet expectations: %v", err)
  }
}
  • //go:build integration-tagged tests against real Postgres via testcontainers-go — the pattern is already in references/testing.md (TestInvoiceRepository_RoundTrip). Isolate each test with a rollback-per-test transaction, or a t.Cleanup that truncates the exercised tables, so tests don’t leak state into each other.

Connection pool metrics

Expose db_open_connections, db_in_use, db_idle, and db_wait_duration as a Prometheus collector wired the same way as the RED metrics middleware in references/observability.md — a saturated pool is invisible until it’s the cause of every request’s latency, so it earns the same first-commit treatment as the rest of observability.