Databases & Data Access Best Practices

Q: How do kubebuilder controllers differ?

Pass reconcile ctx into repository calls; do not store *sql.DB on the reconciler struct without injection patterns from main.

Q: How do I review third-party ORM code?

Require WithContext, inspect generated SQL in staging, ban global session without request ctx.

Context deadlines, pool sizing, and observability for queries.

Apply these rules in code review, service templates, and runbooks so every handler, repository, and worker treats SQL as a shared resource with clear ownership and cancellation boundaries.

How to Use This List

Tick rules as your service template adopts them.
Encode Tier A items in CI (migration lint, banned Query without Context) where possible.
Publish a timeout and pool table next to the service README.
Revisit when adding replicas, read replicas, or cache layers.

A - Connection pool and lifecycle

One *sql.DB pool per DSN per process, wired in main. Avoid init() opening production databases.
Set MaxOpenConns from capacity math (db_max / replicas). Prevents too many connections incidents.
Set idle and lifetime limits explicitly. Do not rely on unlimited defaults under load balancers.
PingContext at startup with boot timeout. Fail deploy before traffic hits bad DSNs.
Export db.Stats() to metrics (WaitCount, InUse, Idle). Alert on pool wait growth.

B - Context, timeouts, and SQL methods

Use *Context database methods on every request-scoped path. Ban Query/Exec without Context in handlers.
Derive DB timeouts from handler or RPC ctx, not Background(). Preserves client cancel.
Publish per-query or per-repo timeout budget table. DB, cache, and outbound HTTP each get a slice.
Pair context with SQL statement_timeout when drivers lag. Defense in depth for runaway queries.
Return ctx.Err() when cancel ends a query. Map to correct HTTP/gRPC status at the edge.

C - Repository boundaries and SQL hygiene

Keep SQL in repository/storage packages, not HTTP handlers. Handlers stay transport-thin.
Use placeholders for all dynamic values. Never concatenate user input into SQL strings.
Map sql.ErrNoRows to domain ErrNotFound sentinels. Do not leak driver errors to API clients.
Name queries in logs/metrics (repo.GetOrder). Avoid logging full SQL with secrets in prod.
Close rows and stmt with defer in repositories. Leaks starve the pool.

D - Transactions and migrations

defer tx.Rollback() immediately after BeginTx. Commit only on success path.
Keep transactions short; business logic outside tx when possible. Long txs hold locks and pool connections.
Run schema migrations in deploy job or init container, not per request. Version table is source of truth.
Require down or documented irreversibility in migration review. CI applies up on ephemeral DB.
Never edit already-applied migration files. Forward-only fixes in new files.

E - ORM, cache, and async (when used)

If using GORM/sqlx, still tune underlying *sql.DB pool. ORM does not remove ops work.
Log or trace ORM SQL in staging to catch N+1 before prod. Preload review on hot paths.
Cache-aside: TTL plus invalidation on writes. Redis is not authoritative for relational invariants.
Publish async work after SQL commit (outbox or transactional pattern). Consumers must not read uncommitted rows.
Pass ctx into Redis/NATS/Kafka handlers. Shutdown drains workers cooperatively.

F - Security, testing, and observability

Separate migration DDL user from runtime app user. Least privilege on production roles.
Integration tests hit real SQL (sqlite, testcontainers) for repositories. sqlmock for unit edges only.
Track query latency histograms tagged by operation name. SLO dashboards per dependency.
Run -race on code that shares repos across goroutines. Catches unsafe shared state above SQL.
Document slow-query runbook: pool stats, active queries, cancel paths. On-call reads one page.

FAQs

Which rules are CI-blockers vs guidelines?

Tier A Context SQL methods, placeholder-only SQL, and migration CI apply should block merge.

Pool tuning tables and cache TTL policy are review-enforced.

How do I introduce repos to legacy handlers?

Extract SQL to a storage package behind an interface, inject from main, keep handler signatures unchanged one PR at a time.

Should read replicas get a second pool?

Yes when you route read-only repos to a follower DSN.

Document eventual consistency in API contracts.

How do kubebuilder controllers differ?

Pass reconcile ctx into repository calls; do not store *sql.DB on the reconciler struct without injection patterns from main.

What about golangci-lint?

Enable linters that catch context misuse and error handling; custom review rules for SQL in handlers.

Can best practices differ per service tier?

Edge APIs use tighter DB timeouts; batch workers use longer budgets but still propagate shutdown ctx.

Write differences in the timeout table.

How do I review third-party ORM code?

Require WithContext, inspect generated SQL in staging, ban global session without request ctx.

What metrics pair with pool stats?

Query p95/p99, error rate by operation, migration version lag, cache hit ratio, consumer lag.

Should TinyGo services follow the same list?

Context and SQL hygiene yes; pool sizing and ORM choices may differ on constrained targets.

Verify board targets at build.

How do these rules relate to context package best practices?

Database rules extend context Package Best Practices with storage-specific pool and migration policy.

Data Access in Go: database/sql as the Foundation - conceptual anchor
database/sql Connection Pooling & Timeouts - pool tuning detail
Transactions, Prepared Statements & sql.Tx - tx discipline
Migrations with golang-migrate & goose - CI migration jobs
context in database/sql & gRPC - cancellation wiring

Stack versions: This page was written for Go 1.26.x (Green Tea GC default, go fix modernizers - verify patch at build), chi (latest - verify at build), gin (latest - verify at build), echo (latest - verify at build), google.golang.org/grpc (latest - verify at build), sigs.k8s.io/controller-runtime (latest - verify at build), kubebuilder (latest - verify at build), tinygo (latest - verify board targets at build), wazero (latest - verify at build), and golangci-lint (latest - verify linter set at build).

Databases & Data Access Best Practices

Context deadlines, pool sizing, and observability for queries.

Apply these rules in code review, service templates, and runbooks so every handler, repository, and worker treats SQL as a shared resource with clear ownership and cancellation boundaries.

How to Use This List

Tick rules as your service template adopts them.
Encode Tier A items in CI (migration lint, banned Query without Context) where possible.
Publish a timeout and pool table next to the service README.
Revisit when adding replicas, read replicas, or cache layers.

A - Connection pool and lifecycle

One *sql.DB pool per DSN per process, wired in main. Avoid init() opening production databases.
Set MaxOpenConns from capacity math (db_max / replicas). Prevents too many connections incidents.
Set idle and lifetime limits explicitly. Do not rely on unlimited defaults under load balancers.
PingContext at startup with boot timeout. Fail deploy before traffic hits bad DSNs.
Export db.Stats() to metrics (WaitCount, InUse, Idle). Alert on pool wait growth.

B - Context, timeouts, and SQL methods

Use *Context database methods on every request-scoped path. Ban Query/Exec without Context in handlers.
Derive DB timeouts from handler or RPC ctx, not Background(). Preserves client cancel.
Publish per-query or per-repo timeout budget table. DB, cache, and outbound HTTP each get a slice.
Pair context with SQL statement_timeout when drivers lag. Defense in depth for runaway queries.
Return ctx.Err() when cancel ends a query. Map to correct HTTP/gRPC status at the edge.

C - Repository boundaries and SQL hygiene

Keep SQL in repository/storage packages, not HTTP handlers. Handlers stay transport-thin.
Use placeholders for all dynamic values. Never concatenate user input into SQL strings.
Map sql.ErrNoRows to domain ErrNotFound sentinels. Do not leak driver errors to API clients.
Name queries in logs/metrics (repo.GetOrder). Avoid logging full SQL with secrets in prod.
Close rows and stmt with defer in repositories. Leaks starve the pool.

D - Transactions and migrations

defer tx.Rollback() immediately after BeginTx. Commit only on success path.
Keep transactions short; business logic outside tx when possible. Long txs hold locks and pool connections.
Run schema migrations in deploy job or init container, not per request. Version table is source of truth.
Require down or documented irreversibility in migration review. CI applies up on ephemeral DB.
Never edit already-applied migration files. Forward-only fixes in new files.

E - ORM, cache, and async (when used)

If using GORM/sqlx, still tune underlying *sql.DB pool. ORM does not remove ops work.
Log or trace ORM SQL in staging to catch N+1 before prod. Preload review on hot paths.
Cache-aside: TTL plus invalidation on writes. Redis is not authoritative for relational invariants.
Publish async work after SQL commit (outbox or transactional pattern). Consumers must not read uncommitted rows.
Pass ctx into Redis/NATS/Kafka handlers. Shutdown drains workers cooperatively.

F - Security, testing, and observability

Separate migration DDL user from runtime app user. Least privilege on production roles.
Integration tests hit real SQL (sqlite, testcontainers) for repositories. sqlmock for unit edges only.
Track query latency histograms tagged by operation name. SLO dashboards per dependency.
Run -race on code that shares repos across goroutines. Catches unsafe shared state above SQL.
Document slow-query runbook: pool stats, active queries, cancel paths. On-call reads one page.

FAQs

Which rules are CI-blockers vs guidelines?

Tier A Context SQL methods, placeholder-only SQL, and migration CI apply should block merge.

Pool tuning tables and cache TTL policy are review-enforced.

How do I introduce repos to legacy handlers?

Extract SQL to a storage package behind an interface, inject from main, keep handler signatures unchanged one PR at a time.

Should read replicas get a second pool?

Yes when you route read-only repos to a follower DSN.

Document eventual consistency in API contracts.

How do kubebuilder controllers differ?

Pass reconcile ctx into repository calls; do not store *sql.DB on the reconciler struct without injection patterns from main.

What about golangci-lint?

Enable linters that catch context misuse and error handling; custom review rules for SQL in handlers.

Can best practices differ per service tier?

Edge APIs use tighter DB timeouts; batch workers use longer budgets but still propagate shutdown ctx.

Write differences in the timeout table.

How do I review third-party ORM code?

Require WithContext, inspect generated SQL in staging, ban global session without request ctx.

What metrics pair with pool stats?

Query p95/p99, error rate by operation, migration version lag, cache hit ratio, consumer lag.

Should TinyGo services follow the same list?

Context and SQL hygiene yes; pool sizing and ORM choices may differ on constrained targets.

Verify board targets at build.

How do these rules relate to context package best practices?

Database rules extend context Package Best Practices with storage-specific pool and migration policy.

Data Access in Go: database/sql as the Foundation - conceptual anchor
database/sql Connection Pooling & Timeouts - pool tuning detail
Transactions, Prepared Statements & sql.Tx - tx discipline
Migrations with golang-migrate & goose - CI migration jobs
context in database/sql & gRPC - cancellation wiring

Stack versions: This page was written for Go 1.26.x (Green Tea GC default, go fix modernizers - verify patch at build), chi (latest - verify at build), gin (latest - verify at build), echo (latest - verify at build), google.golang.org/grpc (latest - verify at build), sigs.k8s.io/controller-runtime (latest - verify at build), kubebuilder (latest - verify at build), tinygo (latest - verify board targets at build), wazero (latest - verify at build), and golangci-lint (latest - verify linter set at build).

Databases & Data Access Best Practices

Search Documentation

Databases & Data Access Best Practices