Go Database Patterns
Database access is where most Go services spend their complexity budget.
Get connection management, transactions, and query patterns right.
Detailed reference material, loaded on demand:
references/query-patterns.md — full query/scan/rows patterns, null
handling, N+1 avoidance, connection-leak examples.
references/tooling.md — repository pattern implementation, sqlc
annotated queries, migration tooling and rules.
Read a reference file only when the summary below is not enough.
1. Connection Management
Configure the pool explicitly — the default is unbounded connections:
func OpenDB(dsn string) (*sql.DB, error) {
db, err := sql.Open("postgres", dsn)
if err != nil {
return nil, fmt.Errorf("open db: %w", err)
}
db.SetMaxOpenConns(25)
db.SetMaxIdleConns(10)
db.SetConnMaxLifetime(5 * time.Minute)
db.SetConnMaxIdleTime(1 * time.Minute)
if err := db.PingContext(context.Background()); err != nil {
return nil, fmt.Errorf("ping db: %w", err)
}
return db, nil
}
| Setting |
Guideline |
MaxOpenConns |
Match your DB's max connections / number of app instances |
MaxIdleConns |
40-50% of MaxOpenConns |
ConnMaxLifetime |
5-10 minutes (prevents stale connections behind load balancers) |
ConnMaxIdleTime |
1-2 minutes |
2. Query Rules
- Parameterized queries only — string concatenation into SQL is an
injection vulnerability, no exceptions.
- Always pass context — use the
*Context variants
(QueryContext, QueryRowContext, ExecContext) so queries respect
cancellation and timeouts.
defer rows.Close() immediately after the error check, and check
rows.Err() after the iteration loop.
- Handle
sql.ErrNoRows explicitly with errors.Is, mapping it to
a domain error like ErrUserNotFound.
var user User
err := db.QueryRowContext(ctx,
"SELECT id, name, email FROM users WHERE id = $1", id,
).Scan(&user.ID, &user.Name, &user.Email)
if errors.Is(err, sql.ErrNoRows) {
return nil, ErrUserNotFound
}
if err != nil {
return nil, fmt.Errorf("get user %s: %w", id, err)
}
Multi-row iteration patterns: references/query-patterns.md.
3. Transactions
Use a helper that guarantees rollback on error:
func WithTx(ctx context.Context, db *sql.DB, fn func(tx *sql.Tx) error) error {
tx, err := db.BeginTx(ctx, nil)
if err != nil {
return fmt.Errorf("begin tx: %w", err)
}
if err := fn(tx); err != nil {
if rbErr := tx.Rollback(); rbErr != nil {
return fmt.Errorf("rollback failed: %v (original: %w)", rbErr, err)
}
return err
}
if err := tx.Commit(); err != nil {
return fmt.Errorf("commit tx: %w", err)
}
return nil
}
Set isolation explicitly for critical operations:
sql.TxOptions{Isolation: sql.LevelSerializable}.
4. Structure and Tooling
- Repository pattern: define the interface at the consumer side,
implement it with concrete database access, map driver errors to
domain errors at this boundary.
- sqlc: prefer it for raw-SQL projects — generates type-safe Go from
annotated SQL, catching query/schema mismatches at build time.
- Migrations: use a tool (
goose, golang-migrate, atlas), one
migration per change, forward-only in production, with down SQL, run
as a separate step — not at server startup.
Implementations and examples: references/tooling.md.
5. Common Pitfalls
- Null columns: use
sql.NullString/sql.NullInt64 or pointer
fields (*string, nil = SQL NULL). Scanning NULL into a plain string
errors at runtime.
- N+1 queries: a query inside a loop over query results. Replace
with a JOIN or a batch query (
WHERE id = ANY($1)).
- Connection leaks: any early
return between Query and
defer rows.Close() leaks a connection from the pool.
Worked examples of each pitfall: references/query-patterns.md.
Verification Checklist
- Connection pool configured with explicit limits (
MaxOpenConns, MaxIdleConns, lifetimes)
- All queries use parameterized placeholders, never string concatenation
- All
QueryContext results have defer rows.Close() immediately after error check
rows.Err() checked after row iteration loop
sql.ErrNoRows handled explicitly with errors.Is
- Transactions use a helper that guarantees rollback on error
- Context propagated to all database calls (
*Context variants)
- Nullable columns use
sql.NullString / sql.NullInt64 or pointer types
- No N+1 query patterns — use JOINs or batch queries
- Migrations are versioned, reversible, and run separately from app startup
1---
2name: go-database
3description: >
4 Database patterns for Go services: database/sql, connection management,
5 transactions, migrations, query builders, and ORM usage (sqlc, GORM, ent).
6 Use when: "database access", "SQL query", "connection pool",
7 "transactions", "database migration", "sqlc", "GORM", "ent", "prepared
8 statement", "repository pattern".
9 Not for: in-memory structures (go-data-structures), SQL security
10 (go-security-audit), query profiling (go-performance-review).
11user-invocable: true
12license: MIT
13compatibility: Designed for Claude Code or similar AI coding agents working on Go projects. Requires the Go toolchain. sqlc, golang-migrate and Docker are optional.
14allowed-tools: Read Edit Write Glob Grep Bash(go:*) Bash(gofmt:*)
15metadata:
16 source: https://skillmd.com/skills/eduardo-sl/go-database
17 author: eduardo-sl
18 version: "1.2.1"
19---
20
21# Go Database Patterns
22
23Database access is where most Go services spend their complexity budget.
24Get connection management, transactions, and query patterns right.
25
26Detailed reference material, loaded on demand:
27
28- `references/query-patterns.md` — full query/scan/rows patterns, null
29 handling, N+1 avoidance, connection-leak examples.
30- `references/tooling.md` — repository pattern implementation, sqlc
31 annotated queries, migration tooling and rules.
32
33Read a reference file only when the summary below is not enough.
34
35## 1. Connection Management
36
37Configure the pool explicitly — the default is unbounded connections:
38
39```go
40func OpenDB(dsn string) (*sql.DB, error) {
41 db, err := sql.Open("postgres", dsn)
42 if err != nil {
43 return nil, fmt.Errorf("open db: %w", err)
44 }
45
46 db.SetMaxOpenConns(25)
47 db.SetMaxIdleConns(10)
48 db.SetConnMaxLifetime(5 * time.Minute)
49 db.SetConnMaxIdleTime(1 * time.Minute)
50
51 if err := db.PingContext(context.Background()); err != nil {
52 return nil, fmt.Errorf("ping db: %w", err)
53 }
54
55 return db, nil
56}
57```
58
59| Setting | Guideline |
60|---|---|
61| `MaxOpenConns` | Match your DB's max connections / number of app instances |
62| `MaxIdleConns` | 40-50% of MaxOpenConns |
63| `ConnMaxLifetime` | 5-10 minutes (prevents stale connections behind load balancers) |
64| `ConnMaxIdleTime` | 1-2 minutes |
65
66## 2. Query Rules
67
681. **Parameterized queries only** — string concatenation into SQL is an
69 injection vulnerability, no exceptions.
702. **Always pass context** — use the `*Context` variants
71 (`QueryContext`, `QueryRowContext`, `ExecContext`) so queries respect
72 cancellation and timeouts.
733. **`defer rows.Close()`** immediately after the error check, and check
74 `rows.Err()` after the iteration loop.
754. **Handle `sql.ErrNoRows` explicitly** with `errors.Is`, mapping it to
76 a domain error like `ErrUserNotFound`.
77
78```go
79var user User
80err := db.QueryRowContext(ctx,
81 "SELECT id, name, email FROM users WHERE id = $1", id,
82).Scan(&user.ID, &user.Name, &user.Email)
83
84if errors.Is(err, sql.ErrNoRows) {
85 return nil, ErrUserNotFound
86}
87if err != nil {
88 return nil, fmt.Errorf("get user %s: %w", id, err)
89}
90```
91
92Multi-row iteration patterns: `references/query-patterns.md`.
93
94## 3. Transactions
95
96Use a helper that guarantees rollback on error:
97
98```go
99func WithTx(ctx context.Context, db *sql.DB, fn func(tx *sql.Tx) error) error {
100 tx, err := db.BeginTx(ctx, nil)
101 if err != nil {
102 return fmt.Errorf("begin tx: %w", err)
103 }
104
105 if err := fn(tx); err != nil {
106 if rbErr := tx.Rollback(); rbErr != nil {
107 return fmt.Errorf("rollback failed: %v (original: %w)", rbErr, err)
108 }
109 return err
110 }
111
112 if err := tx.Commit(); err != nil {
113 return fmt.Errorf("commit tx: %w", err)
114 }
115 return nil
116}
117```
118
119Set isolation explicitly for critical operations:
120`sql.TxOptions{Isolation: sql.LevelSerializable}`.
121
122## 4. Structure and Tooling
123
124- **Repository pattern:** define the interface at the consumer side,
125 implement it with concrete database access, map driver errors to
126 domain errors at this boundary.
127- **sqlc:** prefer it for raw-SQL projects — generates type-safe Go from
128 annotated SQL, catching query/schema mismatches at build time.
129- **Migrations:** use a tool (`goose`, `golang-migrate`, `atlas`), one
130 migration per change, forward-only in production, with `down` SQL, run
131 as a separate step — not at server startup.
132
133Implementations and examples: `references/tooling.md`.
134
135## 5. Common Pitfalls
136
137- **Null columns:** use `sql.NullString`/`sql.NullInt64` or pointer
138 fields (`*string`, nil = SQL NULL). Scanning NULL into a plain string
139 errors at runtime.
140- **N+1 queries:** a query inside a loop over query results. Replace
141 with a JOIN or a batch query (`WHERE id = ANY($1)`).
142- **Connection leaks:** any early `return` between `Query` and
143 `defer rows.Close()` leaks a connection from the pool.
144
145Worked examples of each pitfall: `references/query-patterns.md`.
146
147## Verification Checklist
148
1491. Connection pool configured with explicit limits (`MaxOpenConns`, `MaxIdleConns`, lifetimes)
1502. All queries use parameterized placeholders, never string concatenation
1513. All `QueryContext` results have `defer rows.Close()` immediately after error check
1524. `rows.Err()` checked after row iteration loop
1535. `sql.ErrNoRows` handled explicitly with `errors.Is`
1546. Transactions use a helper that guarantees rollback on error
1557. Context propagated to all database calls (`*Context` variants)
1568. Nullable columns use `sql.NullString` / `sql.NullInt64` or pointer types
1579. No N+1 query patterns — use JOINs or batch queries
15810. Migrations are versioned, reversible, and run separately from app startup