golang-database

Go Database Driver Mechanics

postgres-patterns/mysql-patterns decide what SQL to write; database-migrations decides how schema changes ship safely; this skill covers the layer between them and your Go code — connection pooling, context propagation into queries, and scanning results without silent bugs.

Connection pooling: database/sql

sql.DB is already a connection pool, not one connection — sql.Open doesn't even connect until the first use. Configure it explicitly; the zero-value defaults (unlimited open connections, unlimited idle connections, no connection lifetime) are wrong for production:

db.SetMaxOpenConns(25)          // cap total connections the pool can hold
db.SetMaxIdleConns(25)          // keep idle conns ready instead of reopening
db.SetConnMaxLifetime(5 * time.Minute)  // force rotation past a load balancer/DB restart
db.SetConnMaxIdleTime(1 * time.Minute)  // release idle conns the pool doesn't need

SetMaxOpenConns with no SetMaxIdleConns set can thrash — connections open under load then get closed immediately once idle because the idle limit defaults lower, then reopen on the next request. Set both, and size them against the database's actual max_connections, not just your app's guess at concurrency.

Connection pooling: pgxpool (jackc/pgx/v5)

pgxpool.New(ctx, connString) or pgxpool.NewWithConfig(ctx, cfg) parse their own equivalents of the settings above onto a *pgxpool.Config: MaxConns, MinConns, MaxConnLifetime, MaxConnIdleTime, HealthCheckPeriod. Prefer pgxpool over database/sql + a pgx driver shim when you're on Postgres exclusively and want pgx-native features (typed arrays, COPY, batch, native context cancellation mid-query) — reach for database/sql when the code must stay database-agnostic or already depends on a database/sql-based library.

Always pass context through to the call that does I/O

Every query, exec, and prepare has a *Context variant — QueryContext, ExecContext, QueryRowContext, PrepareContext (pgx: Query, Exec, QueryRow already take ctx as their first argument). Using the non-context variant (db.Query instead of db.QueryContext(ctx, ...)) means the caller's cancellation or timeout has no way to reach the database driver — the query runs to completion regardless of what upstream gave up on.