Database Migrations

A database migration is a version-controlled script that changes your database structure — adding a table, renaming a column, or updating a relationship. Instead of modifying your database by hand, you write the change as code so it runs the same way in development, staging, and production every time.

If Cartara flagged this concept in your recent diff, you likely added, edited, or generated a migration file.

What Is a Database Migration?

Your database schema — the tables, columns, and relationships that store your app's data — is not set in stone. As your app grows, you need to change it: add a created_at column, create a new payments table, change a field from optional to required.

A migration captures one such change as a file. Each file has a unique, ordered identifier (usually a timestamp) and contains the exact SQL or ORM instructions needed to move the database from one version of the schema to the next.

The key benefit is that schema changes become reviewable, repeatable, and tracked — just like your application code.

Why Not Just Edit the Database Directly?

Editing a production database by hand is one of the riskiest things you can do:

• There's no record of what changed, so your dev and production databases drift out of sync over time.
• A typo or wrong command can delete or corrupt data with no undo.
• A new teammate joining the team can't reproduce the current schema.

Migrations remove the improvisation. Every change is written as a file, committed to git, tested in staging, and applied automatically.

How Migration Tools Work

Most frameworks and ORMs ship with a built-in migration system. Common ones include Prisma Migrate, Drizzle Kit, Django migrations, Rails Active Record, Flyway, and Liquibase.

They all follow the same model:

1. You write a migration file describing the change.
2. The tool keeps a _migrations table inside your database tracking which files have already run.
3. When you run migrate, only the new, unapplied files run — in order.

This means the same command is safe to run in any environment and always results in the same schema.

Migrations typically run automatically as part of your CI/CD pipeline, just before a new version of the app is deployed.

What You'll See in Your Code

A Cartara analysis mentioning database migrations usually means your diff contains one of the following:

A new migration file — often in a folder like migrations/, db/migrate/, or prisma/migrations/. It will contain SQL statements or ORM calls like createTable, addColumn, or alterColumn.

A schema file change — tools like Prisma update a schema.prisma file when you change your data model. The migration is then generated from the diff between the old and new schema.

A seed file — sometimes grouped with migrations, seed files populate the database with initial or test data.

A simple migration might look like this:

-- 20240801_add_status_to_orders.sql
ALTER TABLE orders ADD COLUMN status TEXT NOT NULL DEFAULT 'pending';

Or in an ORM like Drizzle:

export async function up(db: Kysely<Database>): Promise<void> {
  await db.schema
    .alterTable('orders')
    .addColumn('status', 'text', col => col.notNull().defaultTo('pending'))
    .execute();
}

Safe Migration Practices

Back up before migrating production. Most managed databases (Supabase, PlanetScale, Railway) let you snapshot on demand — do it before any destructive change.

Be careful with large tables. Adding a column or changing a type on a table with millions of rows can lock the table and stall your app. Add columns as nullable first, then backfill and constrain them in a later migration.

Backfill in batches. Updating millions of existing rows in a single SQL statement can lock the table or run out of memory. Process rows in chunks of a few thousand at a time.

Test on realistic data volumes. A migration that takes 10ms on your local dev database with 50 rows can take minutes on a production table with 5 million.

Zero-Downtime Migrations (Advanced)

If your app never goes fully offline during a deploy — using a rolling deploy or blue-green strategy — your old and new code versions run simultaneously against the same database. This creates a hazard: if a migration drops a column the old code still reads, requests start failing mid-deploy.

The solution is the expand-and-contract pattern:

1. Expand — add new structure without removing old. To rename a column, add the new one first.
2. Migrate — deploy code that writes to both columns and backfills existing rows.
3. Contract — once all traffic uses the new column, drop the old one in a separate, later migration.

More steps, but each one is safe on its own. You likely won't need this until you have enough traffic that a few seconds of downtime is unacceptable — but it's worth knowing the pattern exists.

For Small Teams Getting Started

Use your ORM's built-in migration tool from day one — it's nearly free to set up and saves you from manual-change chaos later. The habits to build:

• Commit migrations alongside the code that depends on them.
• Run migrations automatically in your pipeline, not manually.
• Back up before any production migration that touches existing data.

Related Concepts

• Database Fundamentals
• CI/CD Basics
• Deployment Strategies
• Database Scaling and Performance
• From Prototype to Production