Go CLI SQL Storage

Treat *sql.DB as a long-lived pool owned by the composition root. Keep SQL, driver behavior, migrations, and sensitive connection details inside adapters.

Core Workflow

1. Choose the database and driver from workload, platform, and distribution needs.
2. Define domain operations and transaction ownership before repository methods.
3. Open and configure one long-lived pool at the composition root.
4. Verify required startup connectivity with a bounded PingContext.
5. Apply versioned migrations using an explicit serialization policy.
6. Implement parameterized queries and map driver errors to stable domain errors.
7. Test consumers with fakes and adapters with the real driver and production schema.
8. Build the final artifact and smoke-test database initialization.

Read Next

| Task | Load | |---|---| | Design storage for a CLI | guidelines.md, workflows/build-sql-storage.md | | Add or change migrations | workflows/apply-schema-migrations.md | | Test an adapter | workflows/test-sql-adapter.md | | Review pools, transactions, queries, or conversions | references/sql-storage/rules.md | | Understand driver and pool contracts | references/sql-storage/knowledge.md | | Build a multi-process SQLite cache | references/sql-storage/sqlite.md | | Review patterns | references/sql-storage/examples.md |

Guardrails

• Do not treat sql.Open as a connectivity check.
• Do not interpolate untrusted values or identifiers into SQL.
• Do not treat embedded schema text as a migration system.
• Do not expose driver error types as the application contract.
• Do not use a broad repository interface that mirrors an adapter.
• Do not assume an in-memory SQLite database is shared across pooled connections.

Source Notes

Guidance is transformed and paraphrased from Inanc Gumus, Go by Example: Programmer's Guide to Idiomatic and Testable Programs (Manning, 2025), Chapter 10. Examples are original.

Nullable values, row lifecycle, and pool guidance also incorporates transformed material from Teiva Harsanyi, 100 Go Mistakes and How to Avoid Them (Manning, 2022), Chapter 10.

Book: https://www.manning.com/books/go-by-example

Verify current contracts against https://pkg.go.dev/database/sql, https://pkg.go.dev/database/sql/driver, and the selected driver's documentation. For SQLite, also verify https://sqlite.org/wal.html, https://sqlite.org/lang_transaction.html, and https://sqlite.org/backup.html.