Unit 2 · Module 3

Migrations Are Not Failure

Schema evolution as a managed practice, not a confession

The myth that needs to die first

A common, quiet belief among people learning to build apps is that experienced developers design the schema correctly on day one and never have to change it afterward. That belief makes any schema refactor feel like proof that you didn't think hard enough up front.

It isn't true, and the evidence is hiding in plain sight. Every serious database tool — Supabase, Prisma, Drizzle, Rails, Django, Laravel, every one of them — has an entire subsystem called migrations, built specifically to support changing the schema over time. If schema-from-day-one were achievable, that whole subsystem would not exist. The fact that every framework treats migrations as a first-class feature tells you everything about how often schemas actually evolve in real projects.

What a migration actually is

A migration is a named, ordered file containing SQL (or equivalent) that changes the database's schema. Each migration represents one logical unit of work — a single coherent change, which might be one statement or twenty. The grouping is about what belongs together, not about a per-statement limit.

Migrations are written once, committed to the codebase like any other file, and applied in order. The database keeps track of which migrations it has already run, so applying the same migration twice does nothing the second time. That makes the schema reproducible: any developer (or any environment — local, staging, production) can run the migrations in order and end up with the same schema.

A small migration — one change, one statement

-- supabase/migrations/20260415120000_add_phone_to_clients.sql

ALTER TABLE clients
  ADD COLUMN phone TEXT;

That's a small migration. The long number at the front of the filename is a timestamp, which is how the system knows the order to apply migrations in. Each new migration gets a newer timestamp, so the order is determined automatically without anyone having to renumber anything.

A more typical migration in a real project is much bigger. Shipping a new feature usually means adding several related tables, their enums, their indexes, and the RLS policies that govern who can read or write the rows — all in one file, because none of those pieces make sense without the others:

A more typical feature migration

-- supabase/migrations/20260420090000_add_tagging_feature.sql

-- 1. Enum has to come first — the tags table references it
CREATE TYPE tag_category AS ENUM ('topic', 'mood', 'audience');

-- 2. The tables for the feature
CREATE TABLE tags (
  id        UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  name      TEXT UNIQUE NOT NULL,
  category  tag_category NOT NULL
);

CREATE TABLE post_tags (
  post_id UUID REFERENCES posts(id) ON DELETE CASCADE,
  tag_id  UUID REFERENCES tags(id)  ON DELETE CASCADE,
  PRIMARY KEY (post_id, tag_id)
);

-- 3. Indexes for the queries this feature will run
CREATE INDEX idx_post_tags_tag ON post_tags(tag_id);

-- 4. RLS so only the post's owner can attach tags to it
ALTER TABLE post_tags ENABLE ROW LEVEL SECURITY;

CREATE POLICY "owners can tag their own posts"
  ON post_tags FOR ALL
  USING (
    EXISTS (
      SELECT 1 FROM posts
      WHERE posts.id = post_tags.post_id
        AND posts.user_id = auth.uid()
    )
  );

Everything in that file belongs to "adding the tagging feature." Shipping half of it (the tables but not the policies, or the policies but not the indexes) would leave the system in a broken state. SQL runs top to bottom, so the order inside the file matters — the enum has to exist before the column that uses it, the tables have to exist before the indexes built on them, RLS has to be enabled before policies are created on it.

Analogy: The version history of the database

Code lives in a git repo where every change is recorded as a commit. You can read the history, you can roll back, you can see what changed and when.

Migrations are the same idea applied to the database schema. Each migration file is a commit on the schema. Running the migrations in order reconstructs the current schema from a series of small, readable changes. The schema has a history, the same way the code does.

Why migrations, instead of just changing the schema directly?

The Supabase dashboard's table editor lets you change a schema by clicking buttons. Add a column with a few clicks. Drop a table with one more. That's the equivalent of editing a production file by hand. It works in the moment and leaves no record afterward.

Migrations solve the problems that click-to-edit creates:

Every change is recorded. The migration file is in the codebase. You can read it in six months and know exactly what changed. Click-edits leave no trace.
Every environment ends up the same. Run the migrations on your laptop, on staging, on production — same schema. With click-edits, every environment can drift independently and nobody knows which one is correct.
New developers can catch up. Pull the repo, run the migrations, end up with a working database. Without migrations, a new developer has to find someone willing to walk them through every manual change that's ever been applied.
Rollbacks become possible. Most migration systems support writing an "undo" alongside each migration, so a bad change can be reversed cleanly.

What a few real migrations look like

To make the pattern concrete, here are a few migrations representing common changes:

Adding a column

-- 20260415120000_add_timezone_to_clients.sql

ALTER TABLE clients
  ADD COLUMN timezone TEXT NOT NULL DEFAULT 'America/Chicago';

Creating a new table

-- 20260420090000_create_tags_table.sql

CREATE TABLE tags (
  id    UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  name  TEXT UNIQUE NOT NULL
);

CREATE TABLE post_tags (
  post_id UUID REFERENCES posts(id),
  tag_id  UUID REFERENCES tags(id),
  PRIMARY KEY (post_id, tag_id)
);

A real refactor: splitting clients into people and businesses

-- 20260501140000_split_clients_into_people_and_businesses.sql

-- 1. Create the new tables
CREATE TABLE people (
  id     UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  name   TEXT NOT NULL,
  email  TEXT UNIQUE NOT NULL
);

CREATE TABLE businesses (
  id          UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  legal_name  TEXT NOT NULL,
  tax_id      TEXT UNIQUE
);

-- 2. Move data over (simplified — real migrations check the kind field)
INSERT INTO people (id, name, email)
SELECT id, name, email FROM clients WHERE kind = 'person';

INSERT INTO businesses (id, legal_name, tax_id)
SELECT id, name, tax_id FROM clients WHERE kind = 'business';

-- 3. Drop the original table once everything is moved
-- (In practice, this step often comes in a later migration after
-- application code has been updated to read from the new tables.)
DROP TABLE clients;

The first two are tiny. The third is what a real "I picked the wrong schema and need to fix it" migration looks like. It's not catastrophic. It's a procedure. Three steps, applied in order, with a clear plan.

The Supabase workflow, briefly

In a Supabase project, migrations live in supabase/migrations/ inside the repo. Two common workflows:

Write SQL directly. Create a new file with a timestamped name, write the ALTER or CREATE or DROP statements, and run supabase db push (or apply them through the dashboard) to execute them.
Let the CLI capture changes. Make schema changes locally against a development branch, then run supabase db diff to generate a migration file with the SQL needed to recreate those changes in another environment.

Both produce the same kind of artifact: a numbered file in the migrations folder. The schema's history lives there and is part of the project's permanent record.

Why even senior developers run migrations all the time

Reasons that come up in real projects, not as failures, but as ordinary parts of the work:

The product changed. A feature got added. A category got split. A new kind of user needs different fields. The schema changes because the thing it's modeling changed.
Reads got slow. A page that was fast at launch is slow at scale. The fix might be a new index, a denormalized field, or a different table structure altogether.
A pattern repeated itself. The third time a related feature got added with copy-pasted columns, the right move is to factor those columns into their own table.
An assumption turned out to be wrong. The first design assumed every client was an individual; it turns out half of them are organizations. The schema gets restructured to handle both.

None of these are mistakes. They're the schema responding to reality. Migrations are the tool that makes that response controlled and reversible instead of chaotic and silent.

The mental shift this page is asking for

The shift is small but real: schemas are not built, they are grown. The first version is a best guess. The second version reflects what the product turned out to need. The fifth version, two years in, looks nothing like the first and is also exactly right for what the system actually does now.

Migrations are the medium for that growth. They make every change auditable, repeatable, and reversible. A project with a long migration history isn't a project that got it wrong over and over — it's a project that took its schema seriously enough to record every change.

The last page of this module brings every concept so far into one place: designing a schema for a real product description, one decision at a time.