Golang Database

Go Database Engineering

Prioritize correctness at transaction and trust boundaries. Match the project's database, driver, migration system, generated code, and error conventions.

Establish context

Before changing code:

1. Read go.mod and the existing database initialization, repository/query layer, schema, migrations, and tests.
2. Identify the database engine and version, driver placeholder syntax, transaction semantics, and deployment migration policy.
3. Trace the requested operation from validated input through query execution and result mapping.
4. Determine whether the public behavior includes not-found, conflict, retry, or idempotency guarantees.

Do not switch between database/sql, pgx, sqlx, generated queries, or an ORM merely to apply a preferred style.

Query safety and correctness

• Pass data values through driver parameters. Never concatenate untrusted values into SQL.
• Placeholders cannot represent identifiers, keywords, sort direction, or whole clauses. Map those choices from a fixed allowlist to known SQL fragments.
• Build dynamic filters so each generated clause and argument stays paired. Test empty and multi-value cases.
• Propagate context to query and transaction calls, with deadlines set at an appropriate request or job boundary.
• Close rows, then check rows.Err after iteration. Check errors from scanning, commit, rollback when relevant, and affected-row expectations.
• Model SQL NULL deliberately with nullable types, pointers, or domain-specific conversion. Do not silently collapse NULL into a meaningful zero value.
• Preserve error identity needed by callers while avoiding driver details and secrets in client-facing messages.

Use database-specific features only when they materially improve the requirement and the project accepts the portability tradeoff.

When implementing a query rather than reviewing its policy, read query and scan recipes for concrete database/sql, sqlx, and pgx call shapes. Follow only the branch already selected by go.mod.

Transactions

Keep a transaction around one coherent business invariant, not an entire request by default.

A safe shape is:

1. Begin with context and an intentional isolation level when the default is insufficient.
2. Arrange rollback on every pre-commit exit; tolerate the expected already-completed result.
3. Run every participating query through the transaction handle.
4. Lock or use atomic statements when a read-modify-write invariant would otherwise race.
5. Commit once, and return a commit failure.

Do not add blind retries. Retry only errors documented as transient by the selected database or driver, and only when the complete operation is idempotent or can be safely replayed. Bound attempts and respect context cancellation.

Read transaction and test recipes when the change crosses a transaction boundary, depends on locking or isolation, or needs a database-backed test.

Connections, migrations, and performance

sql.DB and most driver pools represent concurrency-safe pools, not one connection. Create and share the pool at application startup, verify connectivity separately when startup must fail fast, and close it during shutdown.

Tune pool size, lifetime, and idle behavior from database capacity and observed wait metrics. Avoid universal numeric settings. Measure before changing queries or indexes, and inspect an execution plan for the actual engine and representative data.

For migrations:

• use the repository's migration tool and naming convention;
• make rollout compatibility explicit when old and new application versions overlap;
• separate destructive cleanup from the deployment that stops using the old schema;
• account for engine-specific locking and transactional-DDL behavior;
• never run destructive production migrations merely because a local migration succeeded.

Prefer keyset pagination for large, stable ordered datasets when offset cost or consistency is a demonstrated concern. Batch sizes and bulk-loading techniques should be measured, not copied as fixed thresholds.

Testing strategy

Use the narrowest test that proves the behavior:

• unit tests for domain logic around a small repository interface;
• query-shape tests only when they add signal;
• integration tests against the actual database engine for SQL syntax, constraints, isolation, locking, migrations, and driver behavior.

Cover empty results, NULL values, duplicate/conflict paths, cancellation, partial iteration errors, transaction rollback, and concurrent invariants where applicable. Keep test data deterministic and isolate parallel tests.

Review checklist

• Values are parameterized and dynamic identifiers are allowlisted.
• Context reaches every blocking database operation.
• Rows, transactions, and pools have explicit ownership and cleanup.
• Not-found and constraint failures are classified without brittle string matching when the driver exposes structured errors.
• Transaction boundaries enforce the stated invariant.
• Queries do not assume an unordered result is stable.
• Pool and index changes are justified by evidence.
• Schema and code remain compatible throughout deployment.
• Logs and errors do not expose credentials, raw sensitive queries, or personal data.

Run the repository's existing database and Go checks, including focused integration tests when the changed behavior depends on the real engine.