61. Production database access with database/sql and pgx

61.1 database/sql is a pool, not a connection

db, err := sql.Open("postgres", dsn)
if err != nil {
	return err
}
db.SetMaxOpenConns(25)
db.SetMaxIdleConns(25)
db.SetConnMaxLifetime(30 * time.Minute)

ctx, cancel := context.WithTimeout(context.Background(), 3*time.Second)
defer cancel()
if err := db.PingContext(ctx); err != nil {
	return err
}

sql.Open validates arguments; it does not prove the database is reachable. Use PingContext during startup.

61.2 Always pass context into database calls

func (s *Store) User(ctx context.Context, id string) (User, error) {
	const q = `select id, email, created_at from users where id = $1`
	var u User
	if err := s.db.QueryRowContext(ctx, q, id).Scan(&u.ID, &u.Email, &u.CreatedAt); err != nil {
		if errors.Is(err, sql.ErrNoRows) {
			return User{}, ErrUserNotFound
		}
		return User{}, fmt.Errorf("query user %s: %w", id, err)
	}
	return u, nil
}

Context ties DB work to request cancellation and deadlines. Without it, a client can disconnect while the database query continues pointlessly.

61.3 Transactions: defer rollback, check commit

tx, err := db.BeginTx(ctx, nil)
if err != nil {
	return err
}
defer tx.Rollback()

if _, err := tx.ExecContext(ctx, `update accounts set balance = balance - $1 where id = $2`, amount, from); err != nil {
	return err
}
if _, err := tx.ExecContext(ctx, `update accounts set balance = balance + $1 where id = $2`, amount, to); err != nil {
	return err
}
if err := tx.Commit(); err != nil {
	return err
}

The deferred rollback is cleanup for every early return. After Commit, it does nothing useful and its error is normally ignored.

61.4 pgx for PostgreSQL-heavy services

database/sql is portable. pgx is often better when PostgreSQL is the system of record and you want Postgres-specific features, better type support, COPY, notifications, or direct pool control.

pool, err := pgxpool.New(ctx, os.Getenv("DATABASE_URL"))
if err != nil {
	return err
}
defer pool.Close()

var email string
if err := pool.QueryRow(ctx, `select email from users where id=$1`, id).Scan(&email); err != nil {
	return err
}

61.5 Migrations are part of the app lifecycle

Use a migration tool (golang-migrate, goose, Atlas, Flyway, Liquibase) and keep migrations in source control:

migrations/
  0001_create_users.up.sql
  0001_create_users.down.sql
  0002_add_last_login.up.sql
  0002_add_last_login.down.sql

Never rely on "the app creates whatever tables it needs" for production schema evolution. Schema changes need review, ordering, rollback planning, and observability just like code changes.