Skip to main content

Schema as Code vs Hand-Written Migrations: Why the Diff Approach Wins

· 4 min read
PostKit Team
PostKit Team
Appri Technologies

There are two schools of thought on database migrations. The first says: write every ALTER TABLE by hand, accumulate them as numbered files, and replay them in order. The second says: declare the desired schema state, let a tool figure out what SQL to generate. PostKit uses the second approach. Here's why.

Schema as Code vs Hand-Written Migrations

The Traditional Approach​

Tools like Flyway, Liquibase, and raw dbmate work like this:

migrations/
├── 001_create_users.sql
├── 002_add_email_to_users.sql
├── 003_create_posts.sql
├── 004_add_index_on_posts_user_id.sql
└── 005_rename_posts_to_articles.sql

To understand your current schema, you have to mentally replay all 200+ migrations. To change a column type, you write an ALTER COLUMN. To add an index, you write CREATE INDEX. Every migration is a manual, imperative instruction.

This model has real benefits — migrations are explicit, auditable, and easy to reason about individually. But it has a compounding cost: as the migration count grows, the cognitive overhead of understanding and maintaining the schema grows with it.

The Declarative Approach​

PostKit uses pgschema, a diff engine for PostgreSQL. Instead of writing migrations, you maintain SQL files that describe what your schema should look like:

-- db/schema/public/tables/users.sql
CREATE TABLE public.users (
  id         UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  email      TEXT NOT NULL UNIQUE,
  display_name TEXT,
  created_at TIMESTAMPTZ DEFAULT NOW()
);

When you want to add a column, you add it to the file. When you want to remove an index, you delete it from the file. The schema files are always the single source of truth for your current schema shape.

postkit db plan compares those files against the actual database and generates the minimal migration:

-- Generated by pgschema — you don't write this
ALTER TABLE public.users ADD COLUMN display_name TEXT;

What You Don't Have to Write​

With pgschema generating the diffs, you stop writing:

  • ALTER TABLE ... ADD COLUMN
  • ALTER TABLE ... DROP COLUMN
  • CREATE INDEX / DROP INDEX
  • ALTER TABLE ... ALTER COLUMN ... TYPE
  • CREATE TABLE for new tables
  • DROP TABLE for removed tables
  • Column default additions and removals
  • Constraint additions and removals (within a schema)

These are the bread-and-butter of day-to-day schema work. Getting them for free removes a whole category of typos and missed steps.

What You Still Write Manually​

Some SQL has to be written as manual migrations because pgschema can't diff it:

  • CREATE ROLE / CREATE EXTENSION / CREATE SCHEMA → use db/infra/
  • Cross-schema foreign keys and views → use postkit db migration
  • Data backfills (UPDATE, INSERT) → use postkit db migration
  • One-off operations (RENAME, column type changes with custom casts) → use postkit db migration

See Plan Command Limitations for the full list.

The pattern: structural schema changes within a single schema → let pgschema generate it. Everything else → write a manual migration.

The Schema Files as Documentation​

A side effect of the declarative model: your db/schema/ directory is always an accurate picture of your current database schema. No need to reconstruct it from 200 migration files. Junior developers joining the team can read db/schema/public/tables/ and understand the data model immediately.

Compare to the traditional model where the "documentation" is the migration history — useful for understanding how you got here, not for understanding where you are now.

Mixing Both Approaches​

PostKit doesn't force you to abandon explicit migrations. The postkit db migration command creates a manual migration file that runs alongside pgschema-generated ones. In a single session:

  1. Edit schema files (pgschema generates the diff on plan)
  2. Add a manual migration for a data backfill
  3. postkit db apply runs both in sequence: pgschema's SQL first, then the manual migrations

This means you get the declarative model for structural changes and the imperative model for everything else — each where it's appropriate.

The Workflow at a Glance​

# Edit db/schema/public/tables/users.sql — add a column
# Edit db/schema/public/indexes/ — add an index

postkit db plan
# → Shows: ADD COLUMN display_name, CREATE INDEX idx_users_display_name

postkit db apply
# → Runs the migration on your local clone

postkit db commit
postkit db deploy
# → Dry-run, then deploy to production

The migration files are generated, not authored. Your job is maintaining the schema declaration; PostKit's job is figuring out the SQL.