Migration Fundamentals

Migrations are the backbone of database evolution. They provide a structured, version-controlled way to change your database schema over time—ensuring your application’s data layer grows alongside your features.

What Are Migrations?

A database migration is a file that describes how to modify your database structure. Think of migrations as “Git for your database schema”—each migration is a commit that records a specific change.

Why Migrations Matter

Without migrations, database changes are:

Manual: Someone runs SQL directly on the database
Undocumented: No record of what changed or when
Error-prone: Easy to forget a step or make mistakes
Environment-specific: Hard to replicate across dev, staging, and production

With migrations, database changes are:

Automated: Run a command, changes apply
Version-controlled: Every change is tracked in Git
Reproducible: Same migrations produce identical databases
Team-friendly: Everyone’s database stays in sync

Real-World Scenario

Imagine you’re adding a “profile picture” feature:

Without migrations: You add the column manually to your local database, forget to tell your teammate, they deploy without the column, the app crashes.
With migrations: You create a migration file, commit it, your teammate pulls and runs migrations, everyone’s database has the new column.

Migration File Structure

When you generate migrations with Drizzle Kit, it creates SQL files in your migrations directory:

migrations/
├── 0000_initial.sql           # First migration
├── 0001_add_user_profile.sql  # Second migration
├── 0002_create_comments.sql   # Third migration
└── meta/                      # Drizzle Kit metadata
    ├── _journal.json
    └── 0000_snapshot.json

Anatomy of a Migration File

Each migration file contains pure SQL statements:

-- Create the users table
CREATE TABLE `users` (
  `id` integer PRIMARY KEY AUTOINCREMENT NOT NULL,
  `name` text NOT NULL,
  `email` text NOT NULL,
  `created_at` integer NOT NULL
);

-- Add unique constraint on email
CREATE UNIQUE INDEX `users_email_unique` ON `users` (`email`);

-- Create the posts table
CREATE TABLE `posts` (
  `id` integer PRIMARY KEY AUTOINCREMENT NOT NULL,
  `title` text NOT NULL,
  `content` text NOT NULL,
  `author_id` integer NOT NULL,
  `created_at` integer NOT NULL,
  FOREIGN KEY (`author_id`) REFERENCES `users`(`id`)
);

Migration File Naming

Drizzle Kit names migration files with a sequential prefix:

Component	Example	Purpose
Sequence number	`0000`, `0001`, `0002`	Ensures correct execution order
Description	`_initial`, `_add_user_profile`	Human-readable context
Extension	`.sql`	Pure SQL for D1 compatibility

The sequence number is critical—migrations always run in order, from lowest to highest.

The Meta Directory

The meta/ folder contains Drizzle Kit’s internal state:

_journal.json: Tracks which migrations have been generated
XXXX_snapshot.json: Schema snapshot after each migration

These files are auto-managed by Drizzle Kit. Commit them to Git but don’t edit them manually.

Up vs Down Migrations

Migrations can work in two directions:

Up Migrations (Forward)

Up migrations apply changes—they move your database forward to a new state:

-- UP: Add avatar column to users
ALTER TABLE users ADD COLUMN avatar_url text;

Common up operations:

CREATE TABLE — Add a new table
ALTER TABLE ADD COLUMN — Add a column
CREATE INDEX — Add an index for performance
INSERT INTO — Seed initial data

Down Migrations (Rollback)

Down migrations reverse changes—they restore the previous state:

-- DOWN: Remove avatar column from users
ALTER TABLE users DROP COLUMN avatar_url;

Common down operations:

DROP TABLE — Remove a table
ALTER TABLE DROP COLUMN — Remove a column
DROP INDEX — Remove an index
DELETE FROM — Remove seeded data

D1 and Down Migrations

Important: Cloudflare D1 and Drizzle Kit focus on forward-only migrations. Down migrations are not automatically generated.

Why forward-only?

SQLite limitations: SQLite (which D1 uses) has limited ALTER TABLE support, making rollbacks complex
Production safety: Rollbacks can cause data loss; forward fixes are safer
Simplicity: One-directional migrations are easier to reason about

If you need to undo a change, create a new forward migration that reverses the effect:

-- Instead of rolling back, create a new migration
ALTER TABLE users DROP COLUMN avatar_url;

Migration Lifecycle

Understanding how migrations flow from development to production helps you work with them confidently:

flowchart TD
  subgraph Development["Development Phase"]
      A["Modify schema.ts"] --> B["Run drizzle-kit generate"]
      B --> C["Review generated SQL"]
      C --> D["Apply to local D1"]
      D --> E["Test application"]
  end

  subgraph VersionControl["Version Control"]
      E --> F["Commit migration files"]
      F --> G["Push to repository"]
  end

  subgraph Deployment["Deployment Phase"]
      G --> H["CI/CD pipeline triggered"]
      H --> I{"Apply to staging?"}
      I -->|Yes| J["Run migrations on staging D1"]
      J --> K["Test on staging"]
      K --> L{"Apply to production?"}
      L -->|Yes| M["Run migrations on production D1"]
      M --> N["Verify deployment"]
  end

Step-by-Step Walkthrough

Modify your schema: Update schema.ts with new tables, columns, or constraints
Generate migrations: Run pnpm drizzle-kit generate to create SQL files
Review the SQL: Always check the generated migration—Drizzle Kit is smart, but you should understand what it’s doing
Apply locally: Test migrations on your local D1 database first
Commit and push: Migration files are part of your codebase—they go in Git
Deploy to staging: Apply migrations to staging environment, verify everything works
Deploy to production: Once validated, apply the same migrations to production

Migration State Tracking

When you run migrations, the system tracks which ones have been applied:

Applied migrations:
✓ 0000_initial.sql           (applied 2024-01-15)
✓ 0001_add_user_profile.sql  (applied 2024-01-18)
○ 0002_create_comments.sql   (pending)

This tracking ensures:

Each migration runs exactly once
Migrations run in the correct order
New team members get all previous migrations

Common Migration Patterns

Adding a Table

The most common migration creates a new table:

CREATE TABLE comments (
  id integer PRIMARY KEY AUTOINCREMENT NOT NULL,
  post_id integer NOT NULL,
  author_id integer NOT NULL,
  content text NOT NULL,
  created_at integer NOT NULL,
  FOREIGN KEY (post_id) REFERENCES posts(id),
  FOREIGN KEY (author_id) REFERENCES users(id)
);

Adding a Column

Extend an existing table with new columns:

ALTER TABLE users ADD COLUMN bio text;
ALTER TABLE users ADD COLUMN website text;

Adding an Index

Improve query performance with indexes:

CREATE INDEX posts_author_id_idx ON posts(author_id);
CREATE INDEX comments_post_id_idx ON comments(post_id);

Renaming with SQLite Limitations

SQLite doesn’t support ALTER TABLE RENAME COLUMN in older versions. The workaround involves creating a new table:

-- Create new table with desired structure
CREATE TABLE users_new (
  id integer PRIMARY KEY AUTOINCREMENT NOT NULL,
  full_name text NOT NULL,  -- renamed from 'name'
  email text NOT NULL
);

-- Copy data
INSERT INTO users_new SELECT id, name, email FROM users;

-- Swap tables
DROP TABLE users;
ALTER TABLE users_new RENAME TO users;

Best Practices

1. Keep Migrations Small

Each migration should do one thing well:

-- ✅ Good: Single focused change
ALTER TABLE users ADD COLUMN avatar_url text;

-- ❌ Avoid: Multiple unrelated changes
ALTER TABLE users ADD COLUMN avatar_url text;
CREATE TABLE notifications (...);
ALTER TABLE posts ADD COLUMN view_count integer;

2. Never Edit Applied Migrations

Once a migration has been applied (especially in production), treat it as immutable:

Need to fix something? Create a new migration
Found a bug? Write a corrective migration
Want to change the approach? Add another migration

3. Test Before Deploying

Always apply migrations to a test database before production:

# Local testing
pnpm wrangler d1 migrations apply my-db --local

# Staging environment
pnpm wrangler d1 migrations apply my-db --remote --env staging

4. Commit Migration Files

Migration files are as important as your application code:

Commit the migrations/ directory to Git
Include the meta/ directory as well
Review migrations in pull requests

Summary

Migrations are essential for managing database changes:

What they are: Version-controlled SQL files that modify your schema
File structure: Numbered SQL files in a migrations/ directory
Up migrations: Apply changes (forward direction)
Down migrations: Reverse changes (but D1 uses forward-only approach)
Lifecycle: Schema → Generate → Review → Apply → Commit → Deploy

With this foundation, you’re ready to learn how to run migrations in different environments.