go-database
/go-database
Use when writing or reviewing Go SQL queries, transactions, repositories, pools, migrations, or row mapping with database/sql, pgx, or an ORM; also for slow queries and queries in loops.
- Author
- h0rn3t ↗
- Repository
- golang-skills ↗
- Licence
- Apache-2.0
- Last repository change
- 27.10.2026
Assessment and security
- Files in this package
- 3
- Scripts
- 0
- HTML comments
- 0
- URLs in SKILL.md
- 0
No signals detected. Repository changed on 27.10.2026, without references to retired models or APIs.
Apache-2.0, otwarta: można używać i zmieniać, z zachowaniem licencji i informacji o zmianach.
What it does and when to use it
Use when writing or reviewing Go SQL queries, transactions, repositories, pools, migrations, or row mapping with database/sql, pgx, or an ORM; also for slow queries and queries in loops.
Original author description
Use when writing or reviewing Go SQL queries, transactions, repositories, pools, migrations, or row mapping with database/sql, pgx, or an ORM; also for slow queries and queries in loops.
How it works
- Resource Routing
- Stdlib First
- Every Query Carries a Context
- sql.DB Is a Pool
- Rows: Close and Check Err
- Transactions
- Parameters, Never Concatenation
- Queries in Loops
- Nullable Columns and Migrations
- Testing
SKILL.md preview
---
name: go-database
description: Use when writing or reviewing Go SQL queries, transactions, repositories, pools, migrations, or row mapping with database/sql, pgx, or an ORM; also for slow queries and queries in loops.
---
# Go Database Access
> Compatibility: Baseline Go 1.27 (see the plugin repository's COMPATIBILITY.md). Go examples use
> `database/sql`; `sql.Null[T]` requires Go 1.22+.
## Resource Routing
- `references/SQL-PATTERNS.md` - Read when writing the rows loop, a transaction helper, nullable columns, batch lookups, keyset pagination, or pool settings — the full code for each rule below.
- [references/POSTGRESQL.md](references/POSTGRESQL.md) - Read for PostgreSQL schema design, constraints, indexes, upserts, or migrations on live tables. Check the target PostgreSQL version; these rules do not apply to other databases.
## Stdlib First
> **Normative**: `database/sql` plus a driver covers most services. An ORM is
> a new module, the last rung of the dependency ladder in
> [go-packages](../go-packages/SKILL.md).
```
What does the repository already use?
├─ Nothing yet → database/sql (+ pgx stdlib driver for Postgres)
├─ Needs typed, generated queries → sqlc over database/sql
├─ Postgres-only, hot path → pgx native (batches, COPY, typed scans)
└─ An ORM already → keep it; WithContext on every call, Select the columns,
Preload/Joins instead of a query per row
```
pgx native means one `*pgxpool.Pool` per process from `pgxpool.New(ctx, dsn)`
— a `*pgx.Conn` is not safe for concurrent use — and rows read with
`pgx.CollectRows(rows, pgx.RowToStructByName[T])`, which closes them and
returns their error.
---
## Every Query Carries a Context
> **Normative**: `QueryContext`, `QueryRowContext`, `ExecContext`, `BeginTx`.
> The ctx-less forms are unbounded and `noctx` in the lint gate flags them.
---
## `*sql.DB` Is a Pool
Open once in `run()`, `PingContext` to fail fast, inject it into repositories.
Never open per request, never store it in a global.
Configure the pool — the defaults are unlimited open connections and no
lifetime:
| Setting | Why |
|---|---|
| `SetMaxOpenConns` | Per process: the server's `max_connections` minus admin headroom, divided by every process that shares it at peak — replicas during a rolling deploy, workers, migration jobs |
| `SetMaxIdleConns` | Same as max open, or connections churn under load |
| `SetConnMaxLifetime` | Rotate through load balancers and credential changes |
| `SetConnMaxIdleTime` | Release idle connections back to the server |
An exhausted pool shows `db.Stats().WaitCount` climbing and every endpoint slow
at once. Fix it where connections are held: rows left open, or a transaction
kept across a network call. Raising `SetMaxOpenConns` past that budget only
moves the queue into the database. Instances that scale without a fixed count
share the budget through a pooler such as PgBouncer. Behind PgBouncer in
transaction pooling, pgx's default named prepared statements need PgBouncer
1.21+ with `max_prepared_statements` > 0; otherwise set
`default_query_exec_mode=exec` in the DSN.
A read that must see this request's own write — the response to a create, an
idempotency-key or permission check — goes to the primary pool; a replica
answers from before the write.
---
## Rows: Close and Check `Err`
```go
rows, err := db.QueryContext(ctx, q, customerID)
if err != nil {
return nil, fmt.Errorf("list orders: %w", err)
}
defer rows.Close()
var orders []Order
for rows.Next() {
var o Order
if err := rows.Scan(&o.ID, &o.Total); err != nil {
return nil, fmt.Errorf("scan order: %w", err)
}
orders = append(orders, o)
}
if err := rows.Err(); err != nil {
return nil, fmt.Errorf("list orders: %w", err)
}
return orders, nil
```
- `rows.Err()` after the loop is not optional — a dropped connection ends the
loop silently and looks like an empty result. `rowserrcheck` flags it.
- `defer rows.Close()` immediately after the error check; `sqlclosecheck`
flags a missing close.
- Name the columns. `SELECT *` into a positional `Scan` returns
`sql: expected 6 destination arguments in Scan, not 5` on every row once a
migration adds a column — on each instance still on the old binary, the
expand step of expand-contract.
- For lookups, match `sql.ErrNoRows` with `errors.Is` and map it to the domain
sentinel (`ErrNotFound`) at the repository boundary. For writes with
`RETURNING`, interpret a missing row according to the write contract. Keep
`sql.ErrNoRows` out of handlers — [go-error-handling](../go-error-handling/SKILL.md) owns wrapping.
- A value that must be unique is decided by its constraint, not by a lookup
before the `INSERT`: two concurrent requests both pass the lookup. Insert,
then map the violation to the domain sentinel — with pgx,
`pgErr, ok := errors.AsType[*pgconn.PgError](err); ok && pgErr.Code == "23505"`
(`pgErr.ConstraintName` names the key) — or write `ON CONFLICT DO NOTHING`
and read zero rows affected as the conflict.
---
## Transactions
```go
tx, err := db.BeginTx(ctx, nil)
if err != nil {
return fmt.Errorf("begin: %w", err)
}
defer tx.Rollback() // no-op after Commit; the ErrTxDone it returns is expected
// Both rows locked in id order, whichever way the money moves: opposite
// transfers that lock from-then-to deadlock each other.
const lock = `SELECT id FROM accounts WHERE id IN (\$1, \$2) ORDER BY id FOR UPDATE`
if _, err := tx.ExecContext(ctx, lock, from, to); err != nil {
return fmt.Errorf("lock accounts: %w", err)
}
// RETURNING turns a missing account into sql.ErrNoRows; a bare ExecContext
// succeeds on zero rows and would commit the debit alone.
const move = `UPDATE accounts SET balance_cents = balance_cents + \$1 WHERE id = \$2 RETURNING id`
var id int64
err = tx.QueryRowContext(ctx, move, -amount, from).Scan(&id)
if errors.Is(err, sql.ErrNoRows) {
return fmt.Errorf("debit %d: %w", from, ErrNotFound)
}
if err != nil {
return fmt.Errorf("debit %d: %w", from, err)
}
err = tx.QueryRowContext(ctx, move, amount, to).Scan(&id)
if errors.Is(err, sql.ErrNoRows) {
return fmt.Errorf("credit %d: %w", to, ErrNotFound)
}
if err != nil {
return fmt.Errorf("credit %d: %w", to, err)
}
if err := tx.Commit(); err != nil {
return fmt.Errorf("commit transfer: %w", err)
}
return nil
```
- Check the `Commit` error; under Repeatable Read or Serializable any statement
can also fail with 40001, so the retry reruns the whole transaction on
either ([SQL-PATTERNS.md](references/SQL-PATTERNS.md)).
- Everything inside uses `tx`, never `db`: a `db` call inside a transaction
takes a second connection and deadlocks the pool at its limit.
- Keep transactions short: no network calls, no user waits, no logging that
blocks. Lock order is part of the contract — same order everywhere.
---
## Parameters, Never Concatenation
> **Normative**: Every value goes through a placeholder (`$N`, `?`). A value
> formatted into SQL with `fmt.Sprintf` or `+` is an injection, full stop.
Identifiers that vary — a sort column, a table suffix — come from an allow-list
switch in Go, never from input. `gosec` in the lint gate flags string-built
queries; [go-security](../go-security/SKILL.md) owns the threat model.
---
## Queries in Loops
A query inside `for _, o := range orders` is the most common performance bug in
a service. Fix it with one query — `= ANY` over the IDs as one array
parameter (pgx), an expanded `IN (...)`, or a `JOIN` — then map in Go.
Unbounded lists take a `LIMIT` and keyset pagination (`WHERE id >` the last ID
served, `ORDER BY id`); deep `OFFSET` scans everything it skips.
Measure before restructuring: `EXPLAIN (ANALYZE, BUFFERS)` on the real
database decides whether an index or a rewrite is the fix.
[go-performance](../go-performance/SKILL.md) owns the benchmark discipline.
---
## Nullable Columns and Migrations
- Use `NOT NULL` when the domain requires a value, and defaults only when
they have domain meaning. Preserve absent versus empty/zero values. Scan
nullable columns into `sql.Null[T]` (Go 1.22+) or a pointer; scanning `NULL`
into a `string` is a runtime error.
- Migrations are versioned, forward-only files shipped with the binary via
`//go:embed` ([go-packages](../go-packages/SKILL.md)) and applied by a
migration step at deploy — never from `init()`. Each one states its rollback
or says it has none.
---
## Testing
Integration tests run against a real database (a container or a CI service),
not a mocked driver — a mock proves the code calls the mock. Unit-test only the
row-to-struct mapping. [go-testing](../go-testing/SKILL.md) owns the
integration harness — build tag, DSN from the environment, skip when unset —
in [`go-testing/references/INTEGRATION.md`](../go-testing/references/INTEGRATION.md#real-databases).
> **Validation**: `golangci-lint run` with `rowserrcheck`, `sqlclosecheck`,
> `noctx`, and `gosec` from the [go-linting](../go-linting/SKILL.md) baseline,
> then `go test -tags integration -race ./...`. Report a skipped
> integration run as skipped.
---
## Related Skills
- [go-resilience](../go-resilience/SKILL.md): idempotency, replay budgets, atomicity across remote side effects.
- [go-context](../go-context/SKILL.md): query timeouts and what may outlive the request.
- [go-http](../go-http/SKILL.md): the handler that calls the repository and maps its errors to status codes.
Files in this package (3)
- SKILL.md 9 KB
- references/POSTGRESQL.md 6 KB
- references/SQL-PATTERNS.md 7 KB
Versions
| Repository | Score | Licence |
|---|---|---|
| This page · h0rn3t/golang-skills | 100 | Apache-2.0 |
| eduardo-sl/go-agent-skills | 100 | MIT |
| muratmirgun/gophers | 100 | MIT |
| muratmirgun/gophers | 100 | MIT |
Database snapshot: 13.11.2026 · package version 139528e6ca51 (SHA-256 digest of the entire package)
Downloads in the last 30 days: 0 · ranking
Report a problem with this skill: harmful content, incorrect descriptions or an author’s removal request.
Similar skills
- 100sql-optimization-patterns
Master SQL query optimization, indexing strategies, and EXPLAIN analysis to dramatically improve database performance and eliminate slow queries. Use when debugging slow queries, designing database... - 100go-data-persistence
Data persistence patterns in Go covering raw SQL with sqlx/pgx, ORMs like Ent and GORM, connection pooling, migrations with golang-migrate, and transaction management. Use when implementing database access, designing repositories, or managing schema migrations. - 100database-best-practices
Rules for database design, queries, migrations and data access (primarily relational/SQL). Use when designing schemas, writing queries or ORM code, adding indexes, writing migrations, handling transactions, or diagnosing slow queries and N+1 problems. - 100database-design
Database decisions - DB and ORM, schema, indexes, queries, migrations. Use when designing or changing a data model. - 95database-fundamentals
Reviews schema design, SQL queries, ORM patterns. Use when junior creates schema, writes queries, adds migrations, works with Prisma/MongoDB/PostgreSQL, or asks "is this SQL safe", "N+1", "index". - 90sql-optimization-patterns
Provides SQL optimization patterns for query performance, indexing strategies, schema design, and database tuning. Use when optimizing slow queries, designing indexes, or tuning database performance.