Go Data Persistence

Data Persistence in Go

Quick Reference

Choosing Your Approach

Pick the right tool based on your project's needs:

When to Use Raw SQL (sqlx/pgx)

• You need full control over query performance and execution plans
• Your domain has complex joins, CTEs, window functions, or recursive queries
• You want zero abstraction overhead and direct access to PostgreSQL features
• Your team is comfortable writing and maintaining SQL
• You need advanced PostgreSQL features like LISTEN/NOTIFY, advisory locks, or COPY

pgx is the recommended PostgreSQL driver for Go. It provides native PostgreSQL protocol support, better performance than database/sql, and access to PostgreSQL-specific features. Use sqlx when you need database/sql compatibility or work with multiple database backends.

When to Use an ORM (Ent/GORM)

• You want type-safe, generated query builders and avoid writing SQL
• Your schema is mostly CRUD with straightforward relationships
• You value generated code, schema-as-code, and automatic migrations (Ent)
• You are prototyping quickly and want to iterate on the schema fast

Ent is preferred over GORM for new projects. It uses code generation for type safety, has a declarative schema DSL, built-in migration support, and integrates with GraphQL. GORM is suitable if the team already knows it or if the project is small.

Connection Setup

Every Go application connecting to a database needs a properly configured connection pool. The database/sql package manages pooling automatically, but the defaults are not suitable for production.

db, err := sql.Open("postgres", connStr)
if err != nil {
    return fmt.Errorf("opening db: %w", err)
}

// Connection pool configuration
db.SetMaxOpenConns(25)                 // Max simultaneous connections
db.SetMaxIdleConns(10)                 // Connections kept alive when idle
db.SetConnMaxLifetime(5 * time.Minute) // Recycle connections
db.SetConnMaxIdleTime(1 * time.Minute) // Close idle connections

// Verify connection
if err := db.PingContext(ctx); err != nil {
    return fmt.Errorf("pinging db: %w", err)
}

Pool Settings Explained

MaxOpenConns -- The maximum number of open connections to the database. This prevents your application from overwhelming the database with too many concurrent connections. Set to approximately 25 for typical web apps. To calculate: divide your database's max_connections (minus a reserve for admin and replication) by the number of application instances. If your DB allows 100 connections, you have 3 app instances, and you reserve 10 for admin, set this to (100 - 10) / 3 = 30.

MaxIdleConns -- The number of connections kept alive in the pool when not in use. These warm connections avoid the latency of establishing new connections for each request. Set to approximately 10, or roughly 40% of MaxOpenConns. Setting this too high wastes database connections; setting it too low causes frequent reconnections.

ConnMaxLifetime -- The maximum amount of time a connection can be reused. After this duration, the connection is closed and a new one is created on the next request. This helps pick up DNS changes (important for cloud databases that failover to new IPs), rebalance load across read replicas, and prevent connections from becoming stale. A value of 5 minutes is typical. Set shorter (1-2 min) if your infrastructure uses DNS-based failover.

ConnMaxIdleTime -- The maximum amount of time a connection can sit idle before it is closed. This releases connections back to the database during low-traffic periods, freeing resources. A value of 1 minute is typical. This should be shorter than ConnMaxLifetime.

For pgx-specific pooling with native PostgreSQL support, see references/connection-pooling.md.

Repository Pattern

Define a store interface at the consumer for testability. Implement against a concrete database driver. This pattern keeps your domain logic decoupled from the database.

// Store interface for testability
type UserStore interface {
    GetUser(ctx context.Context, id string) (*User, error)
    ListUsers(ctx context.Context, limit, offset int) ([]*User, error)
    CreateUser(ctx context.Context, u *User) error
}

// sqlx implementation
type PostgresUserStore struct {
    db *sqlx.DB
}

func NewPostgresUserStore(db *sqlx.DB) *PostgresUserStore {
    return &PostgresUserStore{db: db}
}

func (s *PostgresUserStore) GetUser(ctx context.Context, id string) (*User, error) {
    var u User
    err := s.db.GetContext(ctx, &u, "SELECT * FROM users WHERE id = $1", id)
    if errors.Is(err, sql.ErrNoRows) {
        return nil, ErrNotFound
    }
    return &u, err
}

func (s *PostgresUserStore) ListUsers(ctx context.Context, limit, offset int) ([]*User, error) {
    var users []*User
    err := s.db.SelectContext(ctx, &users,
        "SELECT * FROM users ORDER BY created_at DESC LIMIT $1 OFFSET $2",
        limit, offset,
    )
    return users, err
}

func (s *PostgresUserStore) CreateUser(ctx context.Context, u *User) error {
    _, err := s.db.NamedExecContext(ctx,
        `INSERT INTO users (id, email, name, created_at, updated_at)
         VALUES (:id, :email, :name, :created_at, :updated_at)`, u)
    return err
}

Model Struct Tags

Use db tags for sqlx column mapping and keep models close to the store:

type User struct {
    ID        string    `db:"id"`
    Email     string    `db:"email"`
    Name      string    `db:"name"`
    CreatedAt time.Time `db:"created_at"`
    UpdatedAt time.Time `db:"updated_at"`
}

Sentinel Errors

Define domain-specific errors that callers can check without importing database packages:

var (
    ErrNotFound  = errors.New("not found")
    ErrConflict  = errors.New("conflict")
)

Map database errors to domain errors in the store layer:

func (s *PostgresUserStore) CreateUser(ctx context.Context, u *User) error {
    _, err := s.db.NamedExecContext(ctx, query, u)
    if err != nil {
        var pgErr *pgconn.PgError
        if errors.As(err, &pgErr) && pgErr.Code == "23505" {
            return ErrConflict
        }
        return fmt.Errorf("inserting user: %w", err)
    }
    return nil
}

Migrations

Use golang-migrate for managing schema changes. Migrations are pairs of SQL files: one for applying changes (up) and one for reverting them (down).

migrations/
├── 000001_create_users.up.sql
├── 000001_create_users.down.sql
├── 000002_add_user_roles.up.sql
└── 000002_add_user_roles.down.sql

Run migrations at application startup:

import "github.com/golang-migrate/migrate/v4"

func runMigrations(dbURL string) error {
    m, err := migrate.New("file://migrations", dbURL)
    if err != nil {
        return fmt.Errorf("creating migrator: %w", err)
    }

    if err := m.Up(); err != nil && err != migrate.ErrNoChange {
        return fmt.Errorf("running migrations: %w", err)
    }

    return nil
}

Key rules: always write both up and down migrations, use IF NOT EXISTS / IF EXISTS for idempotency, never modify a migration that has been applied in production. For full migration patterns, CI/CD integration, and safe migration strategies, see references/migrations.md.

Transactions

Use a transaction helper to ensure consistent commit/rollback handling. Transactions should be managed at the service layer, not the store layer, so that multiple store operations can be composed into a single atomic unit.

func WithTx(ctx context.Context, db *sql.DB, fn func(tx *sql.Tx) error) error {
    tx, err := db.BeginTx(ctx, nil)
    if err != nil {
        return fmt.Errorf("beginning transaction: %w", err)
    }

    if err := fn(tx); err != nil {
        if rbErr := tx.Rollback(); rbErr != nil {
            return fmt.Errorf("rollback failed: %v (original error: %w)", rbErr, err)
        }
        return err
    }

    return tx.Commit()
}

Store methods accept a *sql.Tx parameter so they can participate in a caller-controlled transaction:

func (s *OrderService) PlaceOrder(ctx context.Context, order *Order) error {
    return WithTx(ctx, s.db, func(tx *sql.Tx) error {
        if err := s.orderStore.CreateWithTx(ctx, tx, order); err != nil {
            return fmt.Errorf("creating order: %w", err)
        }
        if err := s.inventoryStore.DecrementWithTx(ctx, tx, order.Items); err != nil {
            return fmt.Errorf("updating inventory: %w", err)
        }
        return nil
    })
}

For isolation levels, deadlock prevention, context propagation, and testing strategies, see references/transactions.md.

When to Load References

Load connection-pooling.md when:

• Configuring pgx native pools (pgxpool.Pool)
• Sizing connection pools for production workloads
• Working with cloud databases, PgBouncer, or connection limits
• Monitoring pool health and metrics

Load migrations.md when:

• Setting up golang-migrate for the first time
• Writing new migration files
• Integrating migrations into CI/CD pipelines
• Dealing with migration failures or rollbacks

Load transactions.md when:

• Implementing multi-step operations that must be atomic
• Designing service-layer transaction boundaries
• Choosing transaction isolation levels
• Debugging deadlocks or long-running transactions

Gates (objective checks before merge)

Run these in order; do not rationalize past a failed step.

1. Migrations 1. List the migration file paths you are adding or relying on (both .up.sql and .down.sql per version). 2. Pass: Each new version has a matching pair on disk with consistent naming (see Migrations). 3. Pass: You did not rewrite migration content that is already applied anywhere you care about (production or shared dev); you added a new version instead.

2. Query safety 1. Scan the diff for dynamic SQL built with fmt.Sprintf, +, or string concatenation involving request fields, JSON, or other external input. 2. Pass: Every such query uses bind parameters ($1, :name) or an ORM/query builder that emits parameterized statements; identifiers (table/column names) that must be dynamic use an explicit allowlist, not raw strings from users.

3. Pool and context 1. Confirm database pool construction (sql.Open, pgxpool.New, etc.) runs once at process startup and is shared, not inside per-request handlers. 2. Pass: Code paths that should respect cancellation/timeouts use QueryContext, ExecContext, GetContext, or equivalent—not Query/Exec without context—for work tied to context.Context.

Anti-Patterns

Using string concatenation for queries

// BAD -- SQL injection vulnerability
query := "SELECT * FROM users WHERE name = '" + name + "'"

Always use parameterized queries ($1, $2, etc.) or named parameters (:name).

Leaking database types into handlers

// BAD -- handler depends on sql.ErrNoRows
func (s *Server) handleGetUser(w http.ResponseWriter, r *http.Request) {
    user, err := s.store.GetUser(ctx, id)
    if errors.Is(err, sql.ErrNoRows) { // handler knows about sql package
        http.NotFound(w, r)
        return
    }
}

Return domain errors (ErrNotFound) from the store and check those in handlers instead.

Opening a new connection per request

// BAD -- bypasses connection pooling entirely
func (s *Server) handleGetUser(w http.ResponseWriter, r *http.Request) {
    db, _ := sql.Open("postgres", connStr) // new pool per request!
    defer db.Close()
}

Open the database connection once at startup and share the pool across the application.

SELECT * in production code

// BAD -- fragile, breaks when columns change
err := db.GetContext(ctx, &u, "SELECT * FROM users WHERE id = $1", id)

Explicitly list the columns you need. This makes the query resilient to schema changes and avoids fetching unnecessary data.

Not handling context cancellation

// BAD -- ignores context, query runs even if client disconnects
rows, err := db.Query("SELECT * FROM large_table")

Always use the Context variants (QueryContext, ExecContext, GetContext) and pass the request context so that queries are cancelled when the caller gives up.

Transactions in store methods

// BAD -- store controls transaction, caller cannot compose
func (s *UserStore) CreateUser(ctx context.Context, u *User) error {
    tx, _ := s.db.BeginTx(ctx, nil)
    // ... insert user ...
    return tx.Commit()
}

Let the service layer manage transactions and pass *sql.Tx into store methods. See references/transactions.md for the correct pattern.

Connection Pooling in Go

How database/sql Pooling Works

Go's database/sql package manages a pool of connections internally. When you call db.QueryContext() or db.ExecContext(), the pool:

1. Checks for an available idle connection 2. If none available and under MaxOpenConns, creates a new connection 3. If at MaxOpenConns, blocks until a connection is returned to the pool 4. After the query completes, returns the connection to the idle pool 5. If the idle pool is full (MaxIdleConns), closes the connection instead

This means sql.Open() does not actually open a connection -- it only validates the DSN and prepares the pool. The first real connection happens on the first query or Ping().

Pool Lifecycle

Request arrives
    |
    v
Pool has idle conn? --yes--> Use it --> Return to idle pool
    |                                        |
    no                                  Idle pool full?
    |                                   /          \
    v                                 yes           no
Under MaxOpenConns?                Close conn    Keep idle
    |          |
   yes         no
    |          |
    v          v
Open new     Block until
connection   one is returned

Sizing Guidelines

Formula

MaxOpenConns = (DB max_connections - reserved_connections) / app_instances

Where:

• max_connections is the database server's maximum connection limit (check with SHOW max_connections in PostgreSQL)
• reserved_connections are connections reserved for superuser access, replication, monitoring, and migrations (typically 10-20)
• app_instances is the number of running application replicas

Workload-Based Sizing

Important Considerations

• More connections does not mean more throughput. PostgreSQL performance degrades significantly above ~100 active connections due to lock contention and context switching.
• If your app needs more than ~50 connections per instance, consider using PgBouncer or a similar connection pooler between your app and the database.
• Monitor actual connection usage before tuning. Use db.Stats() to check pool utilization.

pgx Native Pooling

For PostgreSQL-only applications, use pgxpool.Pool instead of database/sql. It provides better performance, native PostgreSQL protocol support, and additional features like health checks.

import (
    "context"
    "fmt"
    "os"
    "time"

    "github.com/jackc/pgx/v5/pgxpool"
)

func NewPool(ctx context.Context) (*pgxpool.Pool, error) {
    config, err := pgxpool.ParseConfig(os.Getenv("DATABASE_URL"))
    if err != nil {
        return nil, fmt.Errorf("parsing db config: %w", err)
    }

    config.MaxConns = 25
    config.MinConns = 5
    config.MaxConnLifetime = 5 * time.Minute
    config.MaxConnIdleTime = 1 * time.Minute
    config.HealthCheckPeriod = 30 * time.Second

    pool, err := pgxpool.NewWithConfig(ctx, config)
    if err != nil {
        return nil, fmt.Errorf("creating pool: %w", err)
    }

    return pool, nil
}

pgxpool vs database/sql

pgx with database/sql Compatibility

If you need database/sql compatibility (for libraries that require it) but still want pgx as the driver:

import (
    "database/sql"

    _ "github.com/jackc/pgx/v5/stdlib"
)

func NewDB(connStr string) (*sql.DB, error) {
    db, err := sql.Open("pgx", connStr)
    if err != nil {
        return nil, fmt.Errorf("opening db: %w", err)
    }

    db.SetMaxOpenConns(25)
    db.SetMaxIdleConns(10)
    db.SetConnMaxLifetime(5 * time.Minute)
    db.SetConnMaxIdleTime(1 * time.Minute)

    return db, nil
}

Health Checks and Connection Validation

pgxpool Health Checks

pgxpool performs automatic health checks on idle connections at the interval set by HealthCheckPeriod. This detects broken connections (network failures, database restarts) before they are used for a real query.

config.HealthCheckPeriod = 30 * time.Second

If a health check fails, the connection is removed from the pool and a new one is created on demand.

Manual Health Check Endpoint

Expose a health check endpoint that verifies database connectivity:

func (s *Server) handleHealth(w http.ResponseWriter, r *http.Request) {
    ctx, cancel := context.WithTimeout(r.Context(), 2*time.Second)
    defer cancel()

    if err := s.db.PingContext(ctx); err != nil {
        http.Error(w, "database unreachable", http.StatusServiceUnavailable)
        return
    }

    w.WriteHeader(http.StatusOK)
    w.Write([]byte("ok"))
}

Use a short timeout (1-2 seconds) for health check pings. If the database does not respond within that window, the instance should be marked unhealthy.

Monitoring Pool Metrics

database/sql Stats

func (s *Server) handleDBStats(w http.ResponseWriter, r *http.Request) {
    stats := s.db.Stats()

    fmt.Fprintf(w, "Open connections: %d\n", stats.OpenConnections)
    fmt.Fprintf(w, "In use: %d\n", stats.InUse)
    fmt.Fprintf(w, "Idle: %d\n", stats.Idle)
    fmt.Fprintf(w, "Wait count: %d\n", stats.WaitCount)
    fmt.Fprintf(w, "Wait duration: %s\n", stats.WaitDuration)
    fmt.Fprintf(w, "Max idle closed: %d\n", stats.MaxIdleClosed)
    fmt.Fprintf(w, "Max lifetime closed: %d\n", stats.MaxLifetimeClosed)
}

Key Metrics to Watch

If WaitCount is steadily increasing, your application is running out of connections. Either increase MaxOpenConns (if the database can handle it) or reduce query duration.

Prometheus Integration

import "github.com/prometheus/client_golang/prometheus"

func registerDBMetrics(db *sql.DB) {
    prometheus.MustRegister(prometheus.NewGaugeFunc(
        prometheus.GaugeOpts{
            Name: "db_open_connections",
            Help: "Number of open database connections",
        },
        func() float64 { return float64(db.Stats().OpenConnections) },
    ))

    prometheus.MustRegister(prometheus.NewGaugeFunc(
        prometheus.GaugeOpts{
            Name: "db_in_use_connections",
            Help: "Number of in-use database connections",
        },
        func() float64 { return float64(db.Stats().InUse) },
    ))

    prometheus.MustRegister(prometheus.NewGaugeFunc(
        prometheus.GaugeOpts{
            Name: "db_idle_connections",
            Help: "Number of idle database connections",
        },
        func() float64 { return float64(db.Stats().Idle) },
    ))

    prometheus.MustRegister(prometheus.NewCounterFunc(
        prometheus.CounterOpts{
            Name: "db_wait_count_total",
            Help: "Total number of connections waited for",
        },
        func() float64 { return float64(db.Stats().WaitCount) },
    ))
}

pgxpool Stats

func logPoolStats(pool *pgxpool.Pool) {
    stat := pool.Stat()

    slog.Info("pool stats",
        "total_conns", stat.TotalConns(),
        "acquired_conns", stat.AcquiredConns(),
        "idle_conns", stat.IdleConns(),
        "constructing_conns", stat.ConstructingConns(),
        "max_conns", stat.MaxConns(),
        "new_conns_count", stat.NewConnsCount(),
        "max_lifetime_destroy_count", stat.MaxLifetimeDestroyCount(),
        "max_idle_destroy_count", stat.MaxIdleDestroyCount(),
    )
}

Cloud Database Considerations

Connection Limits by Provider

PgBouncer

When using PgBouncer (common in managed PostgreSQL services like Supabase), adjust your application settings:

// With PgBouncer in transaction mode
db.SetMaxOpenConns(50)  // Can be higher -- PgBouncer multiplexes
db.SetMaxIdleConns(5)   // Keep low -- PgBouncer handles idle
db.SetConnMaxLifetime(0) // Disable -- PgBouncer manages lifetime

Important PgBouncer considerations:

• Transaction pooling mode (most common): connections are assigned per transaction, not per session. Prepared statements do not work across transactions.
• Session pooling mode: connections are assigned per session. Prepared statements work normally but you get less multiplexing benefit.
• If using pgx with PgBouncer in transaction mode, disable prepared statements:

config, _ := pgxpool.ParseConfig(connStr)
config.ConnConfig.DefaultQueryExecMode = pgx.QueryExecModeSimpleProtocol

DNS-Based Failover

Cloud databases often use DNS to point to the current primary. Set ConnMaxLifetime to a short value so your application picks up DNS changes after failover:

db.SetConnMaxLifetime(1 * time.Minute) // Short lifetime for fast failover

Without this, long-lived connections may keep pointing to the old primary after a failover event, causing errors.

Database Migrations with golang-migrate

Overview

golang-migrate is the standard migration tool for Go applications using raw SQL. It manages versioned migration files, tracks which migrations have been applied, and supports both programmatic and CLI usage.

Install the CLI:

go install -tags 'postgres' github.com/golang-migrate/migrate/v4/cmd/migrate@latest

File Naming Convention

Migrations are pairs of SQL files stored in a migrations/ directory:

migrations/
├── 000001_create_users.up.sql
├── 000001_create_users.down.sql
├── 000002_add_user_roles.up.sql
├── 000002_add_user_roles.down.sql
├── 000003_create_orders.up.sql
└── 000003_create_orders.down.sql

Format: {version}_{description}.{direction}.sql

• version: zero-padded sequential number (6 digits recommended for sorting)
• description: snake_case description of the change
• direction: up (apply) or down (revert)

Generate a new migration pair with the CLI:

migrate create -ext sql -dir migrations -seq add_user_roles

This creates both up.sql and down.sql files with the next sequential version number.

Example Migrations

Creating a table

-- 000001_create_users.up.sql
CREATE TABLE IF NOT EXISTS users (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    email TEXT UNIQUE NOT NULL,
    name TEXT NOT NULL,
    created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
    updated_at TIMESTAMPTZ NOT NULL DEFAULT now()
);

CREATE INDEX idx_users_email ON users(email);

-- 000001_create_users.down.sql
DROP TABLE IF EXISTS users;

Adding columns

-- 000002_add_user_roles.up.sql
ALTER TABLE users ADD COLUMN IF NOT EXISTS role TEXT NOT NULL DEFAULT 'user';
CREATE INDEX idx_users_role ON users(role);

-- 000002_add_user_roles.down.sql
DROP INDEX IF EXISTS idx_users_role;
ALTER TABLE users DROP COLUMN IF EXISTS role;

Creating a related table

-- 000003_create_orders.up.sql
CREATE TABLE IF NOT EXISTS orders (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    user_id UUID NOT NULL REFERENCES users(id) ON DELETE CASCADE,
    total_cents BIGINT NOT NULL,
    status TEXT NOT NULL DEFAULT 'pending',
    created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
    updated_at TIMESTAMPTZ NOT NULL DEFAULT now()
);

CREATE INDEX idx_orders_user_id ON orders(user_id);
CREATE INDEX idx_orders_status ON orders(status);

-- 000003_create_orders.down.sql
DROP TABLE IF EXISTS orders;

Running Migrations in Code

Embed migrations in your binary and run them at application startup:

import (
    "embed"
    "fmt"

    "github.com/golang-migrate/migrate/v4"
    _ "github.com/golang-migrate/migrate/v4/database/postgres"
    "github.com/golang-migrate/migrate/v4/source/iofs"
)

//go:embed migrations/*.sql
var migrationsFS embed.FS

func runMigrations(dbURL string) error {
    source, err := iofs.New(migrationsFS, "migrations")
    if err != nil {
        return fmt.Errorf("creating migration source: %w", err)
    }

    m, err := migrate.NewWithSourceInstance("iofs", source, dbURL)
    if err != nil {
        return fmt.Errorf("creating migrator: %w", err)
    }

    if err := m.Up(); err != nil && err != migrate.ErrNoChange {
        return fmt.Errorf("running migrations: %w", err)
    }

    version, dirty, _ := m.Version()
    slog.Info("migrations complete", "version", version, "dirty", dirty)

    return nil
}

File-based migrations (without embedding)

func runMigrations(dbURL string) error {
    m, err := migrate.New("file://migrations", dbURL)
    if err != nil {
        return fmt.Errorf("creating migrator: %w", err)
    }

    if err := m.Up(); err != nil && err != migrate.ErrNoChange {
        return fmt.Errorf("running migrations: %w", err)
    }

    return nil
}

Integration in main()

func run(ctx context.Context) error {
    dbURL := os.Getenv("DATABASE_URL")

    // Run migrations before opening the connection pool
    if err := runMigrations(dbURL); err != nil {
        return fmt.Errorf("running migrations: %w", err)
    }

    db, err := sql.Open("postgres", dbURL)
    if err != nil {
        return fmt.Errorf("opening db: %w", err)
    }
    defer db.Close()

    // ... rest of app setup ...
}

CLI Usage

# Apply all pending migrations
migrate -database "$DATABASE_URL" -path migrations up

# Apply the next N migrations
migrate -database "$DATABASE_URL" -path migrations up 2

# Rollback the last migration
migrate -database "$DATABASE_URL" -path migrations down 1

# Rollback all migrations
migrate -database "$DATABASE_URL" -path migrations down

# Go to a specific version
migrate -database "$DATABASE_URL" -path migrations goto 3

# Show current migration version
migrate -database "$DATABASE_URL" -path migrations version

# Force a version (useful for fixing dirty state)
migrate -database "$DATABASE_URL" -path migrations force 3

Writing Safe Migrations

Idempotency

Always use IF NOT EXISTS and IF EXISTS so that migrations can be retried safely after partial failures:

-- Good: idempotent
CREATE TABLE IF NOT EXISTS users (...);
CREATE INDEX IF NOT EXISTS idx_users_email ON users(email);
ALTER TABLE users ADD COLUMN IF NOT EXISTS role TEXT DEFAULT 'user';

-- Bad: fails on re-run
CREATE TABLE users (...);
CREATE INDEX idx_users_email ON users(email);

Use Transactions

Wrap DDL statements in transactions when the database supports transactional DDL (PostgreSQL does):

-- 000004_add_audit_fields.up.sql
BEGIN;

ALTER TABLE users ADD COLUMN IF NOT EXISTS last_login_at TIMESTAMPTZ;
ALTER TABLE users ADD COLUMN IF NOT EXISTS login_count INTEGER NOT NULL DEFAULT 0;

COMMIT;

If any statement within the transaction fails, all changes are rolled back, leaving the schema in a consistent state.

Large Table Migrations

For tables with millions of rows, certain operations lock the table and block reads/writes. Use these strategies:

-- Bad: locks the entire table while building the index
CREATE INDEX idx_orders_created_at ON orders(created_at);

-- Good: builds the index without locking
CREATE INDEX CONCURRENTLY IF NOT EXISTS idx_orders_created_at ON orders(created_at);

Note: CREATE INDEX CONCURRENTLY cannot run inside a transaction. For migrations that include concurrent index creation, do not wrap them in BEGIN/COMMIT.

For adding columns with defaults on large tables (PostgreSQL 11+), ALTER TABLE ADD COLUMN ... DEFAULT is safe and fast because PostgreSQL stores the default value in the catalog rather than rewriting the table.

Separate Data and Schema Migrations

Keep data transformations in separate migration files from schema changes:

migrations/
├── 000005_add_full_name_column.up.sql      # Schema: add column
├── 000005_add_full_name_column.down.sql
├── 000006_populate_full_name.up.sql         # Data: backfill
├── 000006_populate_full_name.down.sql
├── 000007_drop_first_last_name.up.sql       # Schema: remove old columns
└── 000007_drop_first_last_name.down.sql

This three-step approach (add new, backfill, remove old) allows zero-downtime deployments because the application can read from either the old or new columns during the transition.

Rolling Back Migrations

When to Roll Back

• A migration introduced a bug that affects production
• A deployment failed partway through and the database is in a dirty state
• You need to revert a schema change before deploying a fix

How to Roll Back

# Roll back the last applied migration
migrate -database "$DATABASE_URL" -path migrations down 1

Handling Dirty State

If a migration fails partway through, golang-migrate marks the migration version as "dirty." You cannot apply further migrations until the dirty flag is cleared.

# Check current state
migrate -database "$DATABASE_URL" -path migrations version
# Output: 5 (dirty)

# Option 1: Fix the issue and force the version
migrate -database "$DATABASE_URL" -path migrations force 4  # Revert to last clean version

# Option 2: Manually fix the database, then force to the current version
migrate -database "$DATABASE_URL" -path migrations force 5  # Mark as clean

Writing Reversible Down Migrations

Not all migrations are easily reversible. For destructive operations, the down migration should be a best-effort approximation:

-- 000005_drop_legacy_column.up.sql
ALTER TABLE users DROP COLUMN IF EXISTS legacy_field;

-- 000005_drop_legacy_column.down.sql
-- Cannot restore data, but can restore the column
ALTER TABLE users ADD COLUMN IF NOT EXISTS legacy_field TEXT;

Document in comments when a down migration cannot fully restore the previous state.

Migration Rules

1. Never modify a migration that has been applied in production. Create a new migration to make corrections. 2. Always write both up and down migrations. Even if the down migration is imperfect, it provides a rollback path. 3. Use `IF NOT EXISTS` / `IF EXISTS` for idempotent, retriable migrations. 4. Use transactions for multi-statement migrations (except when using CONCURRENTLY). 5. Separate data migrations from schema migrations. This keeps each migration focused and allows staged rollouts. 6. Add indexes concurrently on large tables to avoid blocking reads and writes. 7. Test migrations against a copy of production data before deploying. Schema changes that work on an empty table may lock or fail on a table with millions of rows. 8. Version control your migrations. They are part of the codebase and should be reviewed in pull requests.

Migrations in CI/CD

CI Pipeline

Run migrations as part of your test pipeline against a test database:

# GitHub Actions example
jobs:
  test:
    services:
      postgres:
        image: postgres:16
        env:
          POSTGRES_USER: test
          POSTGRES_PASSWORD: test
          POSTGRES_DB: testdb
        ports:
          - 5432:5432
        options: >-
          --health-cmd pg_isready
          --health-interval 10s
          --health-timeout 5s
          --health-retries 5

    steps:
      - uses: actions/checkout@v4

      - name: Run migrations
        run: |
          migrate -database "postgres://test:test@localhost:5432/testdb?sslmode=disable" \
                  -path migrations up

      - name: Run tests
        run: go test ./...
        env:
          DATABASE_URL: postgres://test:test@localhost:5432/testdb?sslmode=disable

CD Pipeline

For production deployments, run migrations before deploying the new application version:

# 1. Run migrations against production database
migrate -database "$PROD_DATABASE_URL" -path migrations up

# 2. Deploy new application version
# (only after migrations succeed)

If migrations fail, do not deploy the new application version. Fix the migration issue first, then retry.

Multi-Instance Deployments

golang-migrate uses an advisory lock in PostgreSQL to prevent concurrent migration runs. This means it is safe to run migrations from multiple instances simultaneously -- only one will execute, the others will wait or skip.

However, it is cleaner to run migrations as a separate step (e.g., a Kubernetes Job or an init container) rather than having every application instance attempt to migrate on startup.

Transaction Management in Go

Why Service-Layer Transactions

Transactions should be managed at the service layer, not the store (repository) layer. The service layer knows which operations must be atomic. Individual store methods should not start their own transactions because:

• The caller cannot compose multiple store operations into a single transaction
• Each store method would commit independently, breaking atomicity
• Error handling becomes inconsistent -- some operations commit, others roll back

The pattern: the service begins a transaction, passes it to store methods, and commits or rolls back based on the outcome of all operations.

Basic Transaction Pattern

func transferFunds(ctx context.Context, db *sql.DB, from, to string, amount int64) error {
    tx, err := db.BeginTx(ctx, nil)
    if err != nil {
        return fmt.Errorf("beginning transaction: %w", err)
    }

    // Debit source account
    _, err = tx.ExecContext(ctx,
        "UPDATE accounts SET balance = balance - $1 WHERE id = $2 AND balance >= $1",
        amount, from,
    )
    if err != nil {
        tx.Rollback()
        return fmt.Errorf("debiting account: %w", err)
    }

    // Credit destination account
    _, err = tx.ExecContext(ctx,
        "UPDATE accounts SET balance = balance + $1 WHERE id = $2",
        amount, to,
    )
    if err != nil {
        tx.Rollback()
        return fmt.Errorf("crediting account: %w", err)
    }

    return tx.Commit()
}

This works but has problems: repetitive rollback handling, and forgetting tx.Rollback() on any error path causes a connection leak.

Transaction Helper Function

Encapsulate the begin/commit/rollback lifecycle in a helper:

func WithTx(ctx context.Context, db *sql.DB, fn func(tx *sql.Tx) error) error {
    tx, err := db.BeginTx(ctx, nil)
    if err != nil {
        return fmt.Errorf("beginning transaction: %w", err)
    }

    if err := fn(tx); err != nil {
        if rbErr := tx.Rollback(); rbErr != nil {
            return fmt.Errorf("rollback failed: %v (original error: %w)", rbErr, err)
        }
        return err
    }

    return tx.Commit()
}

With Custom Isolation Level

func WithTxOptions(ctx context.Context, db *sql.DB, opts *sql.TxOptions, fn func(tx *sql.Tx) error) error {
    tx, err := db.BeginTx(ctx, opts)
    if err != nil {
        return fmt.Errorf("beginning transaction: %w", err)
    }

    if err := fn(tx); err != nil {
        if rbErr := tx.Rollback(); rbErr != nil {
            return fmt.Errorf("rollback failed: %v (original error: %w)", rbErr, err)
        }
        return err
    }

    return tx.Commit()
}

// Usage with serializable isolation
err := WithTxOptions(ctx, db, &sql.TxOptions{
    Isolation: sql.LevelSerializable,
}, func(tx *sql.Tx) error {
    // operations that require serializable isolation
    return nil
})

Service-Layer Transactions

The service layer coordinates multiple store operations within a single transaction:

type OrderService struct {
    db             *sql.DB
    orderStore     *OrderStore
    inventoryStore *InventoryStore
    paymentStore   *PaymentStore
}

func (s *OrderService) PlaceOrder(ctx context.Context, order *Order) error {
    return WithTx(ctx, s.db, func(tx *sql.Tx) error {
        // All operations share the same transaction
        if err := s.orderStore.CreateWithTx(ctx, tx, order); err != nil {
            return fmt.Errorf("creating order: %w", err)
        }

        if err := s.inventoryStore.DecrementWithTx(ctx, tx, order.Items); err != nil {
            return fmt.Errorf("updating inventory: %w", err)
        }

        if err := s.paymentStore.ChargeWithTx(ctx, tx, order.Payment); err != nil {
            return fmt.Errorf("charging payment: %w", err)
        }

        return nil
    })
}

Store Pattern Accepting Transactions

Store methods should accept a transaction parameter so they can participate in caller-controlled transactions:

type OrderStore struct{}

func (s *OrderStore) CreateWithTx(ctx context.Context, tx *sql.Tx, order *Order) error {
    _, err := tx.ExecContext(ctx,
        "INSERT INTO orders (id, user_id, total) VALUES ($1, $2, $3)",
        order.ID, order.UserID, order.Total,
    )
    return err
}

Dual Interface Pattern

Some store methods need to work both with and without an explicit transaction. Use an interface that both *sql.DB and *sql.Tx satisfy:

// DBTX is satisfied by both *sql.DB and *sql.Tx
type DBTX interface {
    ExecContext(ctx context.Context, query string, args ...any) (sql.Result, error)
    QueryContext(ctx context.Context, query string, args ...any) (*sql.Rows, error)
    QueryRowContext(ctx context.Context, query string, args ...any) *sql.Row
}

type UserStore struct {
    db DBTX
}

func NewUserStore(db DBTX) *UserStore {
    return &UserStore{db: db}
}

func (s *UserStore) GetUser(ctx context.Context, id string) (*User, error) {
    var u User
    err := s.db.QueryRowContext(ctx,
        "SELECT id, email, name FROM users WHERE id = $1", id,
    ).Scan(&u.ID, &u.Email, &u.Name)
    if errors.Is(err, sql.ErrNoRows) {
        return nil, ErrNotFound
    }
    return &u, err
}

// Usage without transaction
store := NewUserStore(db)
user, err := store.GetUser(ctx, "123")

// Usage within transaction
WithTx(ctx, db, func(tx *sql.Tx) error {
    store := NewUserStore(tx)
    user, err := store.GetUser(ctx, "123")
    // ...
    return nil
})

Context-Based Transaction Propagation

For deeply nested call chains, propagate the transaction through context:

type ctxKey struct{}

// TxFromContext retrieves a transaction from context, if present.
func TxFromContext(ctx context.Context) *sql.Tx {
    tx, _ := ctx.Value(ctxKey{}).(*sql.Tx)
    return tx
}

// ContextWithTx stores a transaction in the context.
func ContextWithTx(ctx context.Context, tx *sql.Tx) context.Context {
    return context.WithValue(ctx, ctxKey{}, tx)
}

// Store uses transaction from context if available, otherwise uses db.
func (s *UserStore) GetUser(ctx context.Context, id string) (*User, error) {
    var querier DBTX = s.db
    if tx := TxFromContext(ctx); tx != nil {
        querier = tx
    }

    var u User
    err := querier.QueryRowContext(ctx,
        "SELECT id, email, name FROM users WHERE id = $1", id,
    ).Scan(&u.ID, &u.Email, &u.Name)
    return &u, err
}

Use this pattern sparingly. It makes the transaction boundary less visible in the code. Prefer explicit *sql.Tx parameters when the call chain is shallow.

Isolation Levels

PostgreSQL supports four isolation levels. Choose based on your consistency requirements:

PostgreSQL treats Read Uncommitted as Read Committed. *PostgreSQL's Repeatable Read also prevents phantom reads (unlike the SQL standard minimum).

When to Change Isolation Level

Read Committed (default): Suitable for most web application queries. Each statement sees the latest committed data. Use this unless you have a specific reason not to.

Repeatable Read: Use when a transaction reads the same data multiple times and needs consistent results (e.g., generating a report where totals must be consistent across queries).

tx, err := db.BeginTx(ctx, &sql.TxOptions{
    Isolation: sql.LevelRepeatableRead,
})

Serializable: Use for operations where concurrent transactions could produce inconsistent results (e.g., checking inventory and placing an order). Serializable transactions may fail with serialization errors and must be retried.

func PlaceOrderSerializable(ctx context.Context, db *sql.DB, order *Order) error {
    for retries := 0; retries < 3; retries++ {
        err := WithTxOptions(ctx, db, &sql.TxOptions{
            Isolation: sql.LevelSerializable,
        }, func(tx *sql.Tx) error {
            // Check inventory, place order, etc.
            return nil
        })

        if err == nil {
            return nil
        }

        // Check for serialization failure (PostgreSQL error code 40001)
        var pgErr *pgconn.PgError
        if errors.As(err, &pgErr) && pgErr.Code == "40001" {
            continue // Retry
        }

        return err // Non-retryable error
    }

    return fmt.Errorf("transaction failed after 3 retries")
}

Deadlock Prevention

Deadlocks occur when two transactions wait for each other to release locks. PostgreSQL detects deadlocks and aborts one of the transactions.

Consistent Lock Ordering

The primary strategy for preventing deadlocks is to always acquire locks in the same order:

// BAD -- Transaction A locks user then order, Transaction B locks order then user
// This can deadlock

// GOOD -- Always lock in the same order (e.g., alphabetical by table, ascending by ID)
func (s *OrderService) PlaceOrder(ctx context.Context, order *Order) error {
    return WithTx(ctx, s.db, func(tx *sql.Tx) error {
        // Sort items by ID to ensure consistent lock ordering
        sort.Slice(order.Items, func(i, j int) bool {
            return order.Items[i].ProductID < order.Items[j].ProductID
        })

        for _, item := range order.Items {
            _, err := tx.ExecContext(ctx,
                "UPDATE inventory SET quantity = quantity - $1 WHERE product_id = $2",
                item.Quantity, item.ProductID,
            )
            if err != nil {
                return err
            }
        }

        return nil
    })
}

Advisory Locks

For application-level locking (e.g., ensuring only one instance processes a job):

func withAdvisoryLock(ctx context.Context, tx *sql.Tx, lockID int64, fn func() error) error {
    // Acquire lock (released when transaction ends)
    _, err := tx.ExecContext(ctx, "SELECT pg_advisory_xact_lock($1)", lockID)
    if err != nil {
        return fmt.Errorf("acquiring advisory lock: %w", err)
    }

    return fn()
}

Long-Running Transactions

Long-running transactions hold connections from the pool and can cause problems:

• Connection starvation: Other requests wait for a connection while the transaction holds one
• Lock contention: Rows locked by the transaction block other writes
• WAL bloat: PostgreSQL retains WAL segments until long transactions complete
• Vacuum blocking: VACUUM cannot clean up rows visible to the long transaction

Mitigation

1. Set a statement timeout to prevent runaway queries within a transaction:

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

// Set a 30-second timeout for this transaction
_, err = tx.ExecContext(ctx, "SET LOCAL statement_timeout = '30s'")
if err != nil {
    tx.Rollback()
    return err
}

2. Use context with timeout so the entire transaction is bounded:

ctx, cancel := context.WithTimeout(ctx, 30*time.Second)
defer cancel()

err := WithTx(ctx, db, func(tx *sql.Tx) error {
    // If context expires, the transaction is automatically rolled back
    return nil
})

3. Break large operations into batches instead of processing everything in one transaction:

// BAD -- single transaction updating millions of rows
WithTx(ctx, db, func(tx *sql.Tx) error {
    _, err := tx.ExecContext(ctx, "UPDATE users SET status = 'active'")
    return err
})

// GOOD -- batch processing
for {
    result, err := db.ExecContext(ctx,
        "UPDATE users SET status = 'active' WHERE status = 'pending' LIMIT 1000",
    )
    if err != nil {
        return err
    }
    rows, _ := result.RowsAffected()
    if rows == 0 {
        break
    }
}

4. Never call external APIs inside a transaction. If you need to coordinate with an external service, use the saga pattern or outbox pattern instead.

Testing Transactions

Test with Real Database

Use a test database and roll back after each test:

func TestPlaceOrder(t *testing.T) {
    db := setupTestDB(t)

    // Start a transaction for the test
    tx, err := db.BeginTx(context.Background(), nil)
    if err != nil {
        t.Fatal(err)
    }
    t.Cleanup(func() {
        tx.Rollback() // Undo all changes after the test
    })

    // Create store using the test transaction
    store := NewOrderStore(tx)

    // ... run test assertions ...
}

Test Helper with Savepoints

For tests that need to verify transaction behavior:

func setupTestDB(t *testing.T) *sql.DB {
    t.Helper()

    db, err := sql.Open("postgres", os.Getenv("TEST_DATABASE_URL"))
    if err != nil {
        t.Fatal(err)
    }
    t.Cleanup(func() { db.Close() })

    return db
}

func withTestTx(t *testing.T, db *sql.DB, fn func(tx *sql.Tx)) {
    t.Helper()

    tx, err := db.BeginTx(context.Background(), nil)
    if err != nil {
        t.Fatal(err)
    }
    defer tx.Rollback()

    fn(tx)
    // Transaction is always rolled back -- test data is never committed
}

Testing Transaction Rollback

func TestPlaceOrder_RollsBackOnPaymentFailure(t *testing.T) {
    db := setupTestDB(t)

    withTestTx(t, db, func(tx *sql.Tx) {
        // Setup: create a user and product
        _, err := tx.ExecContext(context.Background(),
            "INSERT INTO users (id, email, name) VALUES ($1, $2, $3)",
            "user-1", "test@example.com", "Test",
        )
        if err != nil {
            t.Fatal(err)
        }

        // Create a service that will fail on payment
        svc := &OrderService{
            db:         db,
            orderStore: NewOrderStore(),
            paymentStore: &FailingPaymentStore{}, // Always returns error
        }

        err = svc.PlaceOrder(context.Background(), &Order{
            UserID: "user-1",
            Total:  1000,
        })

        // Verify the error
        if err == nil {
            t.Fatal("expected error from failing payment")
        }

        // Verify the order was NOT created (transaction rolled back)
        var count int
        tx.QueryRowContext(context.Background(),
            "SELECT COUNT(*) FROM orders WHERE user_id = $1", "user-1",
        ).Scan(&count)

        if count != 0 {
            t.Errorf("expected 0 orders after rollback, got %d", count)
        }
    })
}

Anti-Patterns

Starting transactions in store methods

// BAD -- store controls transaction, caller cannot compose
func (s *UserStore) CreateUser(ctx context.Context, u *User) error {
    tx, _ := s.db.BeginTx(ctx, nil)
    _, err := tx.ExecContext(ctx, "INSERT INTO users ...", ...)
    if err != nil {
        tx.Rollback()
        return err
    }
    return tx.Commit()
}

The caller cannot add this operation to a larger transaction. Move transaction management to the service layer.

Forgetting to handle rollback errors

// BAD -- rollback error is silently ignored
if err := fn(tx); err != nil {
    tx.Rollback() // What if this fails?
    return err
}

Log or wrap the rollback error so you know if cleanup failed.

Holding transactions open during external API calls

// BAD -- holds a connection and locks while waiting for HTTP response
WithTx(ctx, db, func(tx *sql.Tx) error {
    order, _ := orderStore.CreateWithTx(ctx, tx, order)

    // This HTTP call might take seconds or time out
    paymentResult, err := paymentAPI.Charge(order.Total)
    if err != nil {
        return err // Transaction held open the entire time
    }

    return orderStore.UpdateStatusWithTx(ctx, tx, order.ID, "paid")
})

Make external calls outside the transaction. Use an outbox pattern if you need to coordinate:

// GOOD -- transaction is short, external call is outside
var order *Order

err := WithTx(ctx, db, func(tx *sql.Tx) error {
    var err error
    order, err = orderStore.CreateWithTx(ctx, tx, newOrder)
    return err
})
if err != nil {
    return err
}

// External call outside the transaction
paymentResult, err := paymentAPI.Charge(order.Total)
if err != nil {
    // Mark order as failed in a separate transaction
    return orderStore.UpdateStatus(ctx, order.ID, "payment_failed")
}

return orderStore.UpdateStatus(ctx, order.ID, "paid")

Not passing context to transaction operations

// BAD -- query is not cancellable
_, err := tx.Exec("SELECT * FROM large_table")

// GOOD -- respects context cancellation
_, err := tx.ExecContext(ctx, "SELECT * FROM large_table")

Always use the Context variants so queries are cancelled when the request context is done.