postgresql

PostgreSQL & Goose Migrations

Stack

Component	Technology
Database	PostgreSQL 16+
Migrations	goose/v3 (SQL)
Query builder	Squirrel (sq)

Migration Conventions

Rule	Do	Don't
Naming	{number}_{description}.sql (zero-padded)	Timestamps, gaps
Format	-- +goose Up / -- +goose Down	Missing Down section
Down idempotent	DROP TABLE IF EXISTS	DROP TABLE without IF EXISTS
Applied migrations	Never edit or delete — always create a new migration	Modify in place
New columns	DEFAULT value or NULL	NOT NULL without default on existing tables

SQL Style

Rule	Do	Don't
Keywords/types	UPPERCASE	create table, text
Column names	lowercase_snake_case	camelCase
Alignment	Align types vertically, 4-space indent	Ragged, tabs

Table Design

Rule	Do	Don't
Timestamps	TIMESTAMP(0) WITHOUT TIME ZONE, DEFAULT CURRENT_TIMESTAMP	TIMESTAMPTZ unless you need time zones
Foreign keys	Inline REFERENCES public.table(id)	Separate constraint when inlinable
Strings	TEXT	VARCHAR(N)
Booleans	BOOLEAN NOT NULL DEFAULT false	Nullable booleans
JSON	JSONB DEFAULT NULL	Multiple columns for nested config
Soft delete	deleted_at TIMESTAMP(0) ... DEFAULT NULL	is_deleted flag
Indexes	CREATE INDEX {table}_{columns}_idx	Anonymous indexes

Index Strategy

Rule	Do	Don't
Filter cols	Index WHERE columns	Index every column
Sort cols	Index ORDER BY columns	Missing sort indexes on paginated queries
Unique	CREATE UNIQUE INDEX for natural keys	App-level uniqueness only
FK cols	Index FK columns	Missing FK indexes

Verifying Data Migrations

When a migration transforms existing data (not just adding columns), verify the full round-trip through the API — not just the SQL transformation:

1. Seed on old schema: checkout base branch, reset DB, insert realistic rows
2. Migrate: checkout branch, run goose up
3. Verify via psql: confirm columns transformed correctly
4. Verify via API: write a scratch test that reads the migrated data through the endpoint that uses it
5. Clean up: delete scratch test files before committing

The test suite seeds fresh data per test and never touches pre-existing rows, so passing tests alone do not prove migrated data works.

Non-Goals

Never: use CREATE TYPE enums (use integer codes), raw SQL in handlers, edit applied migrations.