# Migrations & transactions

> Version your schema with goose, change live tables without downtime, and use transactions in Go that stay correct under concurrency.

Backend with Go roadmap · Data · 17 min read · https://levelcode.org/roadmaps/go-backend/migrations-and-transactions

Your schema will change as long as your service lives, and every change has to reach development laptops, CI and production in the same order. Meanwhile, requests run concurrently against that schema, and any operation that touches more than one row needs to succeed or fail as a unit. This lesson covers both halves of keeping data consistent: **migrations** for evolving the schema, and **transactions** for evolving the data.

## Why schema changes need migrations

A migration is a small, numbered SQL file that moves the schema one step forward. A migration tool applies pending files in order and records each applied version in a table in the database itself. Every environment converges on the same schema just by running "apply everything pending".

That gives you three rules that prevent most schema disasters:

1. **Migrations live in the repository** next to the code that needs them, and go through code review.
2. **Never edit a migration that has been applied anywhere shared.** The tool won't re-run it, so environments silently diverge. Write a new migration instead.
3. **Never change production by hand** with `psql`. If it isn't in a migration, it doesn't exist.

## Writing migrations with goose

This lesson uses goose, a popular Go migration tool. (`golang-migrate` works the same way, with separate `.up.sql` and `.down.sql` files.) Install the CLI and point it at your database with environment variables:

```bash
go install github.com/pressly/goose/v3/cmd/goose@latest

export GOOSE_DRIVER=postgres
export GOOSE_DBSTRING="postgres://app:dev@localhost:5432/app?sslmode=disable"
export GOOSE_MIGRATION_DIR=./migrations

mkdir -p migrations   # goose create fails if the directory doesn't exist
goose create create_accounts sql
```

That creates `migrations/20261011090000_create_accounts.sql`, a timestamped file. Each file has an Up section and a Down section, which undoes it:

```sql
-- +goose Up
CREATE TABLE accounts (
    id         bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    owner      text   NOT NULL,
    balance    bigint NOT NULL CHECK (balance >= 0),
    created_at timestamptz NOT NULL DEFAULT now()
);

-- +goose Down
DROP TABLE accounts;
```

Apply it and check the state:

```text
$ goose up
OK   20261011090000_create_accounts.sql (3.47ms)
goose: successfully migrated database to version: 20261011090000

$ goose status
    Applied At                  Migration
    =======================================
    Sun Oct 11 08:12:14 2026 -- 20261011090000_create_accounts.sql
```

goose records versions in a `goose_db_version` table. `goose down` rolls back the most recent migration. Down migrations are useful in development, but in production you almost always roll *forward* with a new fix, because a down migration that drops a column also drops its data.

goose runs each SQL migration inside a transaction. PostgreSQL supports transactional DDL, so if statement three of five fails, the first two are rolled back too and the database is never left half-migrated. Two annotations adjust parsing: wrap function bodies containing semicolons in `-- +goose StatementBegin` / `-- +goose StatementEnd`, and add `-- +goose NO TRANSACTION` for statements PostgreSQL refuses to run inside a transaction, such as `CREATE INDEX CONCURRENTLY`.

## Running migrations from your Go binary

Shipping migrations inside the binary means a deploy can't run code against a schema it doesn't expect. Embed the files and run them with goose's `Provider` at startup, or from a `migrate` subcommand your deploy pipeline runs before the new version starts:

```go
//go:embed migrations/*.sql
var embedded embed.FS

func Migrate(ctx context.Context, pool *pgxpool.Pool) error {
	migrations, err := fs.Sub(embedded, "migrations")
	if err != nil {
		return err
	}
	// A PostgreSQL advisory lock stops two replicas migrating at once.
	locker, err := lock.NewPostgresSessionLocker()
	if err != nil {
		return err
	}
	db := stdlib.OpenDBFromPool(pool) // goose needs a *sql.DB
	defer db.Close()                  // does not close the pool

	provider, err := goose.NewProvider(goose.DialectPostgres, db, migrations,
		goose.WithSessionLocker(locker))
	if err != nil {
		return err
	}
	results, err := provider.Up(ctx)
	for _, r := range results {
		log.Printf("migrated %s (%s)", r.Source.Path, r.Duration)
	}
	return err
}
```

## Changing a live schema safely

During a rolling deploy, old and new versions of your code run against the same database at the same time. So every migration must be compatible with the code running *before* it and the code running *after* it. Renaming `owner` to `owner_name` in one step breaks every old instance the moment it applies.

The fix is the **expand/contract** pattern, spread across several deploys:

1. **Expand**: add the new column `owner_name`, nullable. Old code ignores it.
2. **Dual write**: deploy code that writes both columns, then backfill old rows in batches.
3. **Switch reads** to `owner_name`, and add the `NOT NULL` constraint.
4. **Contract**: once no running code uses `owner`, drop it.

Also watch locks. Most `ALTER TABLE` forms take an `ACCESS EXCLUSIVE` lock, and while one waits behind a long-running query, every other query on that table queues behind *it*. Starting migrations with `SET lock_timeout = '5s';` makes them fail fast and retry instead of stalling your traffic.

| Change | Safe on a busy table? |
|---|---|
| Add a nullable column, or one with a constant default | Yes (metadata-only since PostgreSQL 11) |
| `CREATE INDEX` | No: blocks writes. Use `CREATE INDEX CONCURRENTLY` |
| Add `NOT NULL` to an existing column | Scans the table under lock. Add a `CHECK (col IS NOT NULL) NOT VALID` constraint, then `VALIDATE CONSTRAINT` |
| Change a column's type | Usually rewrites the table. Use expand/contract |
| Rename or drop a column old code still reads | No. Use expand/contract |

## Transactions in Go

A transaction groups statements so they all commit or none do. Moving money between accounts is the classic case: debiting one account without crediting the other must be impossible. With pgx, you begin a transaction from the pool and then run every statement on the returned `pgx.Tx`:

```go
var (
	ErrInsufficientFunds = errors.New("insufficient funds")
	ErrAccountNotFound   = errors.New("account not found")
)

func Transfer(ctx context.Context, pool *pgxpool.Pool, from, to, amount int64) error {
	tx, err := pool.Begin(ctx)
	if err != nil {
		return err
	}
	defer tx.Rollback(ctx) // returns ErrTxClosed after a successful Commit; safe to ignore

	// Lock both rows in ascending id order so opposite transfers can't deadlock.
	rows, _ := tx.Query(ctx,
		`SELECT id FROM accounts WHERE id IN ($1, $2) ORDER BY id FOR UPDATE`, from, to)
	locked, err := pgx.CollectRows(rows, pgx.RowTo[int64])
	if err != nil {
		return err
	}
	if len(locked) != 2 {
		return ErrAccountNotFound
	}

	tag, err := tx.Exec(ctx,
		`UPDATE accounts SET balance = balance - $2 WHERE id = $1 AND balance >= $2`, from, amount)
	if err != nil {
		return err
	}
	if tag.RowsAffected() == 0 {
		return ErrInsufficientFunds
	}
	if _, err := tx.Exec(ctx,
		`UPDATE accounts SET balance = balance + $2 WHERE id = $1`, to, amount); err != nil {
		return err
	}
	return tx.Commit(ctx)
}
```

The `defer tx.Rollback(ctx)` line is the key idiom. Every early `return` (an error, insufficient funds, a panic) rolls back automatically, and after `Commit` succeeds the deferred rollback does nothing. You never have to remember to clean up on each error path. pgx also offers `pgx.BeginFunc(ctx, pool, func(tx pgx.Tx) error { ... })`, which commits if your function returns nil and rolls back otherwise.

> [!WARNING]
> Inside a transaction, use `tx` for every statement. A stray `pool.Exec` runs on a *different* connection, outside the transaction: it commits even if the transaction rolls back, and it can deadlock waiting for a row lock your own transaction holds.

To let store functions run either standalone or inside a caller's transaction, accept a small interface that both `*pgxpool.Pool` and `pgx.Tx` satisfy:

```go
type DBTX interface {
	Exec(ctx context.Context, sql string, args ...any) (pgconn.CommandTag, error)
	Query(ctx context.Context, sql string, args ...any) (pgx.Rows, error)
	QueryRow(ctx context.Context, sql string, args ...any) pgx.Row
}
```

## Concurrency inside transactions

A transaction makes changes atomic, but it doesn't automatically stop two transactions from interfering. The classic bug is the **lost update**: two requests each `SELECT balance` (both read 100), each compute `100 - 30` in Go, and each write `70`. One withdrawal vanishes. Under PostgreSQL's default `READ COMMITTED` isolation, wrapping that code in a transaction does not fix it.

`Transfer` above uses the two standard fixes:

- **Do the arithmetic in SQL.** `SET balance = balance - $2 WHERE balance >= $2` reads and writes the row atomically, and the condition enforces the business rule at the same moment. `RowsAffected() == 0` tells you it failed.
- **Lock rows you read before writing.** `SELECT ... FOR UPDATE` locks the rows until the transaction ends, so a second transfer touching the same account waits.

Locking in a **consistent order** matters too. If transfer A→B locks A then B while B→A locks B then A, each waits for the other forever. PostgreSQL detects the deadlock and aborts one with SQLSTATE `40P01`. Sorting by `id` before locking makes that impossible.

For complex invariants that are hard to express with locks, run the transaction at `SERIALIZABLE` isolation (`pool.BeginTx(ctx, pgx.TxOptions{IsoLevel: pgx.Serializable})`). PostgreSQL then aborts any transaction that could have produced an anomaly with SQLSTATE `40001`, and your job is to retry it:

```go
func withRetry(ctx context.Context, attempts int, fn func() error) error {
	var err error
	for i := range attempts {
		err = fn()
		var pgErr *pgconn.PgError
		if !errors.As(err, &pgErr) || (pgErr.Code != "40001" && pgErr.Code != "40P01") {
			return err // success, or an error that retrying won't fix
		}
		select {
		case <-time.After(time.Duration(i+1) * 20 * time.Millisecond):
		case <-ctx.Done():
			return ctx.Err()
		}
	}
	return err
}
```

> [!TIP]
> Keep transactions short. Never make an HTTP call, send an email or wait on user input while one is open: it holds row locks and a pool connection the whole time. Gather inputs first, run the transaction, then do side effects after `Commit`.

## Practice

1. Add a goose migration that creates a `transfers` table (`id`, `from_id` and `to_id` referencing `accounts`, `amount bigint` with `CHECK (amount > 0)`, `created_at`), with a Down section.
2. Change `Transfer` so it also inserts a `transfers` row in the same transaction.
3. Write a `main` that runs `Migrate`, seeds three accounts with 1,000 each, fires 200 concurrent random transfers between them, then prints the outcome counts and the total balance. The total must still be 3,000, with no deadlock errors.

**Solution**

`migrations/20261013100000_create_transfers.sql`:

```sql
-- +goose Up
CREATE TABLE transfers (
    id         bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    from_id    bigint NOT NULL REFERENCES accounts (id),
    to_id      bigint NOT NULL REFERENCES accounts (id),
    amount     bigint NOT NULL CHECK (amount > 0),
    created_at timestamptz NOT NULL DEFAULT now(),
    CHECK (from_id <> to_id)
);

-- +goose Down
DROP TABLE transfers;
```

In `Transfer`, before `return tx.Commit(ctx)`:

```go
if _, err := tx.Exec(ctx,
	`INSERT INTO transfers (from_id, to_id, amount) VALUES ($1, $2, $3)`, from, to, amount); err != nil {
	return err
}
```

The driver program (imports `math/rand/v2` and `sync`):

```go
func main() {
	ctx := context.Background()
	pool, err := pgxpool.New(ctx, os.Getenv("DATABASE_URL"))
	if err != nil {
		log.Fatal(err)
	}
	defer pool.Close()
	if err := Migrate(ctx, pool); err != nil {
		log.Fatal(err)
	}
	_, err = pool.Exec(ctx, `TRUNCATE transfers, accounts RESTART IDENTITY;
		INSERT INTO accounts (owner, balance) VALUES ('ana', 1000), ('ben', 1000), ('cy', 1000)`)
	if err != nil {
		log.Fatal(err)
	}

	var wg sync.WaitGroup
	var mu sync.Mutex
	counts := map[string]int{}
	for range 200 {
		wg.Add(1)
		go func() {
			defer wg.Done()
			from := rand.Int64N(3) + 1
			to := from%3 + 1 // a different account
			err := Transfer(ctx, pool, from, to, rand.Int64N(50)+1)
			mu.Lock()
			defer mu.Unlock()
			if err == nil {
				counts["ok"]++
			} else {
				counts[err.Error()]++
			}
		}()
	}
	wg.Wait()

	var total int64
	if err := pool.QueryRow(ctx, `SELECT sum(balance) FROM accounts`).Scan(&total); err != nil {
		log.Fatal(err)
	}
	fmt.Println(counts, "total:", total)
}
```

```text
map[ok:200] total: 3000
```

You may also see some `insufficient funds` results, depending on the random amounts. Delete the `FOR UPDATE` query and run it again: some transfers now fail with `deadlock detected (SQLSTATE 40P01)`, because the cycle 1→2, 2→3, 3→1 locks rows in conflicting orders.

## Key takeaways

- Migrations are versioned SQL files applied in order and recorded in the database; never edit one that has been applied, and never patch production by hand.
- goose wraps each SQL migration in a transaction; use `NO TRANSACTION` for `CREATE INDEX CONCURRENTLY`.
- Every migration must work with both the old and new code: use expand/contract and set a `lock_timeout`.
- Begin with `pool.Begin`, `defer tx.Rollback(ctx)`, run everything on `tx`, and finish with `tx.Commit`.
- Prevent lost updates with arithmetic in SQL or `SELECT ... FOR UPDATE`, lock rows in a consistent order, and retry `40001`/`40P01` errors.
- Keep transactions short and do side effects after commit.

## Quiz

1. A migration that was applied to staging last week turns out to have a typo in a column default. What should you do?

   A. Edit the original migration file and re-run goose up
   B. Fix the default directly in each database with psql
   C. Write a new migration that alters the column default
   D. Delete the row from goose_db_version and re-run goose up

   **Answer: C.** goose won't re-run an applied migration, so editing it makes environments diverge silently. A new migration moves every environment forward consistently. Manual psql changes and tampering with goose_db_version bypass version control entirely.

2. Why is `defer tx.Rollback(ctx)` placed right after `pool.Begin` even though the function ends with `tx.Commit(ctx)`?

   A. It rolls back on any early return or panic, and is a harmless no-op once Commit has succeeded
   B. pgx requires a deferred Rollback or Commit will fail
   C. It makes the transaction run at SERIALIZABLE isolation
   D. It releases row locks before Commit runs, improving throughput

   **Answer: A.** Every error path is covered by one line. After a successful Commit, Rollback just returns ErrTxClosed, which is ignored. It has no effect on isolation and doesn't run before Commit.

3. Two requests each run `SELECT balance` (both see 100), compute 100 - 30 in Go, and run `UPDATE accounts SET balance = 70` inside their own transactions at the default isolation level. What is the result?

   A. PostgreSQL rejects the second UPDATE with a serialization error
   B. The balance ends at 40
   C. The second transaction deadlocks forever
   D. The balance ends at 70 and one withdrawal is lost

   **Answer: D.** This is a lost update. READ COMMITTED doesn't prevent it: both wrote 70. Fix it with `SET balance = balance - 30` (arithmetic in SQL) or by locking the row with SELECT ... FOR UPDATE before reading.

4. You need to rename the column `owner` to `owner_name` on a table used by a service deployed with rolling updates. What's the safe approach?

   A. ALTER TABLE ... RENAME COLUMN in one migration, deployed with the code change
   B. Add owner_name, dual-write and backfill, switch reads, then drop owner in a later deploy
   C. Run the rename with goose NO TRANSACTION so it doesn't lock the table
   D. Rename it during the night when traffic is low

   **Answer: B.** During a rolling deploy, old and new code run at the same time, so a one-step rename breaks whichever version expects the other name. Expand/contract keeps every intermediate state compatible with both.