What is a database migration? (changing the shape of your data, safely)

What it actually is

A database migration is a small file that describes exactly one change to your database’s structure: add this column, create this table, rename that field. Instead of one person opening the database and typing changes by hand, every change gets written down as a dated, ordered step, kept in a folder, and run in sequence.

Think of it like a shared recipe card box instead of one person’s memory of how they cooked the dish last time. Anyone, on any machine, can pull the same cards in the same order and end up with the exact same result. Skip a card or run them out of order, and the dish comes out wrong.

In practice, a migration is usually a .sql file or an entry generated by a tool. Supabase’s CLI creates files like supabase/migrations/20260721103000_add_status_to_orders.sql, holding plain SQL such as ALTER TABLE orders ADD COLUMN status text;. Prisma works from your schema.prisma file and generates the matching SQL for you when you run prisma migrate dev. Either way, the database itself keeps a table recording which migrations have already run (_prisma_migrations for Prisma, supabase_migrations.schema_migrations for Supabase), so the same file never gets applied twice by accident.

Why your AI just did this

When you ask your agent to add a field or change a column, it could in theory connect straight to the database and run the change right there. A careful agent doesn’t, because that change would only exist in that one database. Your local copy, your staging copy, and your live production copy would all end up different, with no record of what changed or when.

Writing a migration file instead means the exact same change can be applied everywhere, in order, by running that one file. It gets committed to git alongside the code that needs it, so a teammate pulling your branch (or your production deploy) can catch up automatically. And if something goes wrong, there’s a specific, named step to roll back to, rather than a database that’s just quietly different from what it used to be.

When you’ll run into it

• Your agent says something like “I’ve created a migration to add the status column, should I run it?”, meaning the file exists but hasn’t been applied to any real database yet.
• relation "orders" already exists or a similar Postgres error, usually from a migration that already ran being run a second time.
• Prisma’s Error: P3009 migrate found failed migrations in the target database, new migrations will not be applied, meaning an earlier migration crashed partway through and has to be resolved (prisma migrate resolve) before anything new can go on top of it.
• Your app works locally but crashes in production with something like column "status" of relation "orders" does not exist, because the migration file exists in the repo but was never actually run against the production database.
• Asking “which migrations still need to run?” because the folder has more files in it than the database has recorded as applied.

What to check

• Migration files live inside version control (supabase/migrations/, prisma/migrations/) and get committed to git, not left sitting only on your machine
• The migration has actually run against every environment that needs it. A file existing in the repo does not mean production has it yet
• Migrations are applied in timestamp order. Never hand-edit an old migration that has already run somewhere; add a new one instead
• A failed or half-applied migration gets explicitly resolved before you stack more migrations on top of it
• Read the actual SQL inside a migration before approving it, especially anything with DROP or a column rename. That’s where real data can be lost, not just structure

Say it like a dev

Instead of: “just change the database” Say: “write a migration for this instead of editing the database directly”

Instead of: “it works on my computer but the live site is broken” Say: “did this migration actually run against production too, or only local?”

Instead of: “undo the last database change” Say: “roll back the last migration”