The Database Connection Is a Long-Lived Thing

*sql.DB is a pool of connections, safe for concurrent use, created once at startup and borrowed by requests that last milliseconds. Most database problems a Go service has come from treating it as a single connection: one per request exhausts the server’s connection limit, closing it after a query destroys the pool, and copying a size from a blog post produces a service that queues under load for reasons the logs do not show.

The lifecycle

sqlConnOpts := []sqlconn.Option{
	sqlconn.WithDefaultDriver(dbcfg.Driver),
	sqlconn.WithPingTimeout(time.Duration(dbcfg.TimeoutPing) * time.Second),
	sqlconn.WithConnMaxOpen(dbcfg.ConnMaxOpen),
	sqlconn.WithConnMaxIdleCount(dbcfg.ConnMaxIdleCount),
	sqlconn.WithConnMaxIdleTime(time.Duration(dbcfg.ConnMaxIdleTime) * time.Second),
	sqlconn.WithConnMaxLifetime(time.Duration(dbcfg.ConnMaxLifetime) * time.Second),
	sqlconn.WithShutdownWaitGroup(wg),
	sqlconn.WithShutdownSignalChan(sc),
	sqlconn.WithSQLOpenFunc(mtr.SqlOpen),
}

sqlConn, err := sqlconn.Connect(ctx, dbcfg.DSN, sqlConnOpts...)
if err != nil {
	return nil, nil, fmt.Errorf("failed to connect to %s DB: %w", name, err)
}

The context here is not the pool’s lifetime

The documentation is emphatic about this:

The context passed to New/Connect bounds connection establishment only (dialing and the initial health check). It does NOT control the pool lifetime: a request- or timeout-scoped context will not close the pool when it ends.

sql.Open does not connect: it validates the DSN and returns a pool that dials lazily on first use, so the ctx passed to Connect bounds the dial and the ping and nothing after. Closing the pool needs WithLifetimeContext, WithShutdownSignalChan or a direct Shutdown call.

The DSN and the driver

// The DSN may embed the driver name as a "<driver>://" prefix (for example
// "pgx://postgres://user:pass@host:5432/db"); sqlconn.Connect parses it and
// falls back to the configured db.*.driver when the prefix is absent (the
// plain MySQL DSN format has no "://").
sqlConn, err := sqlconn.Connect(ctx, dbcfg.DSN, sqlConnOpts...)

Two forms, because MySQL and PostgreSQL disagree about what a DSN looks like: MySQL’s user:pass@tcp(host:3306)/db has no scheme, PostgreSQL’s is a URL. The prefix lets one configuration key carry either, and WithDefaultDriver covers the case where there is no prefix to read.

Whatever else the DSN needs goes in the configuration rather than in this function, and the comment in the reference implementation says why:

Driver-specific DSN parameters belong in the configuration, not here: appending a MySQL query string in this shared function would break the Postgres DSNs the same code path supports.

The MySQL configurations end their DSN with ?parseTime=true, which is a detail that costs an afternoon when met cold: it makes the driver return time.Time for DATETIME and TIMESTAMP, and without it they arrive as []byte and scanning into a time.Time fails on the first row. The item table’s created_at is the column that depends on it. columnsWithAlias=true is the other one worth knowing, prefixing column names with their table alias so a join reading two id columns by name can tell them apart.

Two connections

reldb.Main, healthchecks, err = newDatabase(ctx, "main", cfg.DB.Main, healthchecks, mtr, wg, sc)
reldb.Read, healthchecks, err = newDatabase(ctx, "read", cfg.DB.Read, healthchecks, mtr, wg, sc)

Main carries writes and the reads that must observe them, read points at a replica, and they stay separate even when both DSNs are identical.

Replication is asynchronous, so a read from a replica immediately after a write to the primary can return the old row. Two named connections force the question “can this read tolerate lag” to be answered at each call site rather than discovered in production, let a read-heavy service size a large read pool against a small write pool, and make adding a replica later a configuration change rather than a code change.

The interface makes the choice explicit at the call site:

type SQLConn interface {
	DB() *sql.DB
	HealthCheck(ctx context.Context) error
	Shutdown(ctx context.Context) error
}

type Databases struct {
	Enabled bool
	Main    SQLConn
	Read    SQLConn
}

A repository takes Databases and picks per method, against the interface rather than the concrete type, so a unit test can pass a mock.

The pool settings

Four numbers that decide how the service behaves under load:

ConnMaxOpen

The one to reason about rather than copy:

At the limit, a request asking for a connection blocks until one is returned, holding a handler goroutine and spending its budget. Latency goes up with no error, and from the database’s side everything is fine.

Too low and you queue at moderate load. Too high and you push the queue into the database, which is worse: one serving 200 connections with capacity for 60 spends its time context-switching, and every query gets slower.

Start from the ceiling: take the database’s connection limit, subtract what other consumers and administrative connections need, and divide by the replicas you will run at peak:

MySQL max_connections                            = 500
Other consumers, migrations, monitoring, humans  = 100
Available to this service                        = 400
Peak replicas                                    = 8
ConnMaxOpen                                      = 50

Then check that 50 exceeds the concurrency you need, by Little’s law: request rate multiplied by mean service time. 200 requests per second per replica at 20ms of database time each is 4 concurrent connections, so 50 is generous. If the arithmetic demands more than your share allows, the answer is fewer replicas, a bigger database, or less database time per request, and none of them is a larger number here.

ConnMaxIdleCount

How many connections stay open when traffic drops. Too low and a spike pays for a TCP handshake, a TLS handshake and authentication on most requests. Setting it equal to ConnMaxOpen trades idle database resources for latency consistency and suits steady load; the default of 5 is conservative.

ConnMaxLifetime

Connections are retired after an hour, healthy or not.

That looks wasteful, and it exists because of what sits between the service and the database. A proxy drops long-lived connections silently. A failover promotes a replica, leaving old connections pointed at a machine that is no longer the primary. DNS changes and existing connections do not notice. Retiring on a schedule bounds how long the pool holds a connection to the wrong place.

Set it below any idle timeout in the path: the database’s wait_timeout, the load balancer’s, a NAT gateway’s. If the database closes idle connections at 300 seconds and your lifetime is 3600, the pool hands out dead connections and the application sees intermittent invalid connection errors.

The startup ping

sqlconn.WithPingTimeout(time.Duration(dbcfg.TimeoutPing) * time.Second)

Connect dials and runs a validation query before returning, and a failure aborts bind, then startup, with exit code 2.

The alternative looks friendlier and is worse: a service that starts without its database reports itself up and then fails every request, the orchestrator sends it traffic, the deployment succeeds, and the alert fires from the error rate minutes later instead of from a container that will not start.

The default timeout of one second is a check against a database that should be milliseconds away, not a retry loop. restartPolicy: Always with exponential backoff is the loop you would otherwise write, implemented by something that can also give up and tell somebody.

Instrumentation

sqlconn.WithSQLOpenFunc(mtr.SqlOpen)

and after connecting:

err = mtr.InstrumentDB("db_"+name, sqlConn.DB())

WithSQLOpenFunc replaces sql.Open with the metrics client’s version, wrapping the driver so query duration and errors are recorded. InstrumentDB registers pool statistics: open, in use, idle, wait count, wait duration, and the counts closed by each of the three limits. Those last ones are direct feedback on the settings above: MaxIdleClosed rising means ConnMaxIdleCount is below your working set, MaxLifetimeClosed is expected and steady, and WaitCount rising means ConnMaxOpen is the constraint.

The db_main and db_read naming gives one label per pool, and a saturated write pool and a saturated read pool have different causes.

Transactions

// internal/item/repository.go

err := sqltransaction.Exec(ctx, r.write, func(ctx context.Context, tx *sql.Tx) error {
	_, ierr := tx.ExecContext(ctx, sqlInsertItem, it.ID, it.Name, it.Quantity, it.CreatedAt)

	return mapWriteError(ierr, it.Name)
})

Begin, run, commit on success, roll back on failure, with the rollback deferred and guarded.

sql.ErrTxDone is ignored, and rollback is skipped after a successful commit. Calling Rollback on a committed transaction returns that error, and code that logs every rollback failure produces a stream of harmless errors that train people to ignore the log.

Rollback failures are joined onto the current error rather than replacing it. If the logic failed and the rollback then failed, both are diagnostically relevant and the first is the cause.

A panic still rolls back, because the rollback is in a defer. Without that, a panic mid-transaction leaves it open until the connection is returned to the pool and reused, and the next borrower inherits an open transaction.

Hand-written, that is twenty lines with four places to get it wrong, and what people write instead is:

tx, _ := db.Begin()
defer tx.Rollback() // discards the error
// ...
tx.Commit()

which works and loses every rollback error, including the ones that mean the transaction did not roll back.

Keep them short

A transaction holds locks, and locks block other writers. Do the validation, the parsing and the outbound calls before Exec: a transaction that waits on an HTTP call holds row locks for the duration of somebody else’s latency. Read what you need before starting, unless the read has to be part of the consistent snapshot, and do not loop over a result set doing per-row updates where one statement would do.

ExecWithOptions takes *sql.TxOptions when the isolation level matters:

err := sqltransaction.ExecWithOptions(ctx, db, fn, &sql.TxOptions{
	Isolation: sql.LevelSerializable,
})

The default is the database’s: REPEATABLE READ for MySQL’s InnoDB, READ COMMITTED for PostgreSQL. Where the level matters to the logic, set it explicitly rather than inheriting a different answer per environment.

Spending the budget inside a query

Page 09’s thread, at the bottom of the stack.

func (r *Repository) Get(ctx context.Context, id string) (*Item, error) {
	qctx, cancel := context.WithTimeout(ctx, 2*time.Second)
	defer cancel()

	var it Item

	row := r.read.QueryRowContext(qctx, sqlSelectItem, id)

	err := row.Scan(&it.ID, &it.Name, &it.Quantity, &it.CreatedAt)
	if err != nil {
		return nil, fmt.Errorf("failed scanning item row: %w", err)
	}

	return &it, nil
}

Every database/sql call has a Context variant, and the non-context version is the one to avoid: QueryContext, ExecContext, QueryRowContext, BeginTx, PrepareContext.

When the context is cancelled mid-query the driver tells the server, MySQL with a KILL QUERY on a separate connection and PostgreSQL with a cancel request. The call returns context.Canceled or context.DeadlineExceeded and the connection goes back to the pool. Without it the query runs to completion for a handler that already returned a 503, which is how a pool fills with work for clients who left.

The cancellation is not free

A cancelled query still ran, so cancelling stops the service waiting rather than undoing the cost, and the cancellation is a round trip of its own. A service timing out at 100ms on queries that take 150ms issues a query and a cancellation per request, and the database does more work than with no timeout at all. Timeouts bound waits; they are not a capacity solution.

Migrations

The reference stack uses Flyway, with SQL files under resources/db/:

resources/db/mysql/schema/       baseline schema
resources/db/mysql/int/          integration test fixtures
  V1001__example_table.sql

V<version>__<description>.sql. Flyway records what it has applied in a table in the database, so running it repeatedly is safe and a partially migrated database is detectable. Two operational rules matter more than the choice of tool.

Migrations run outside the service. Not from main, not from an init hook. Six replicas starting at once run six migrations concurrently, and the locking that prevents damage makes five of them wait, turning a rolling deploy into a serialised one. Run them as a job before the deployment.

Migrations have to be backward compatible during a rollout. Old and new code run against the same schema at once, so dropping a column the old code selects breaks replicas that are still serving. Expand and contract: add the new column, deploy code that writes both and reads the new one, then remove the old one. That is why a schema change is usually two deployments.

When the database is disabled

// internal/cli/bind.go

reldb := db.Databases{Enabled: cfg.DB.Enabled}

if !cfg.DB.Enabled {
	return reldb, healthchecks, nil
}

Off by default in the reference configuration, which page 04’s conditional validation is what makes possible without demanding DSNs nobody has.

What the switch must not become is silent degradation. The example propagates Enabled to the feature that needs it, and the item routes are never registered when it is false, so a request for /items is a 404 from the router rather than an empty list from a handler with nothing to read. Page 14 has that wiring and the nil interface trap inside it.

Next: Ping, Status, Metrics: Three Different Questions, on the endpoints that report what Part IV has connected.