What this prompt does
This prompt asks the AI to build a type-safe database layer for a Go [app_type] using sqlc with [database]. It organizes query files by domain ([domains]), generates typed queries with proper null handling via pgtype, and wraps everything in a transaction helper that auto-commits or rolls back and supports nested transactions through savepoints. Connection pooling uses [pool_lib], migrations are managed with [migration_tool], and a repository layer wraps the generated sqlc code so your data access stays testable.
The structure works because sqlc validates SQL at compile time, eliminating a whole class of runtime query bugs, and this prompt builds the surrounding scaffolding that sqlc alone does not provide. Naming [domains] keeps query files organized instead of dumped into one folder, and the transaction wrapper plus repository pattern give you clean boundaries. Batch operations for [batch_operations] using COPY or multi-row INSERT, slow-query detection, and error mapping (unique violation to ConflictError) turn raw generated code into a layer you can actually grow on. Connection pooling through [pool_lib] with min/max sizing and health checks is the piece that keeps the layer stable under load, since an exhausted or stale pool surfaces as intermittent failures that are hard to trace back to the database.
When to use it
- You are building the persistence layer for a Go backend and want compile-time SQL checking instead of runtime surprises.
- You need clean transaction handling with automatic rollback and savepoint-based nesting.
- Your data access is sprawling and you want queries grouped by domain like
[domains]. - You import or bulk-update large datasets and need efficient batch operations.
- You want database errors mapped to domain errors so callers do not parse driver strings.
- You want integration tests that run against a real database via
[test_strategy].
Example output
Expect an sqlc.yaml configuration, domain-grouped .sql query files, the generated Go code, a transaction wrapper type, repository structs over the generated queries, migration files for [migration_tool], and integration test scaffolding using [test_strategy]. Error-mapping helpers and pool configuration for [pool_lib] are included.
Pro tips
- Set
[database]precisely (e.g. PostgreSQL 16) so pgtype null handling and feature use match your server version. - List
[domains]as the real bounded contexts in your app; this directly drives query-file organization and keeps generated code navigable. - Be explicit about
[batch_operations]— "import 10K rows" pushes toward COPY, while "bulk status update" leans on multi-row UPDATE. - Choose
[pool_lib]to match your driver; pgxpool pairs naturally with pgx and pgtype, and mixing pools and drivers causes subtle issues. - For
[test_strategy], testcontainers with per-test transaction rollback keeps tests isolated and fast — ask for it explicitly if speed matters. - Verify the savepoint logic in the nested-transaction wrapper; nested rollback semantics are easy to get subtly wrong, so read that code carefully.
