Migration Best Practices

These practices will help you avoid common migration pitfalls and build a reliable database evolution workflow. Whether you’re working solo or on a team, following these guidelines ensures your migrations are predictable, testable, and production-safe.

Naming Conventions

Good naming makes migrations self-documenting. When you look at a migration file months later, its name should tell you exactly what it does.

File Naming Pattern

Drizzle Kit generates migrations with this format:

XXXX_description.sql

Where:

• XXXX is a sequential number (0000, 0001, 0002…)
• description explains the change

Descriptive Naming Examples

✅ Good Names	❌ Poor Names
0001_create_users_table.sql	0001_update.sql
0002_add_bio_to_users.sql	0002_changes.sql
0003_create_posts_table.sql	0003_new_stuff.sql
0004_add_user_avatar_url.sql	0004_fix.sql
0005_create_comments_index.sql	0005_temp.sql

Naming Best Practices

1. Use action verbs: create_, add_, remove_, rename_, update_
2. Include the table name: add_bio_to_users, create_posts_table
3. Be specific about the change: add_avatar_url not just add_column
4. Use snake_case: Consistent with SQL conventions

migrations/
├── 0000_create_users_table.sql
├── 0001_create_posts_table.sql
├── 0002_add_bio_to_users.sql
├── 0003_add_foreign_key_posts_author.sql
├── 0004_create_comments_table.sql
└── 0005_add_published_at_to_posts.sql

When Drizzle Kit Generates Names

Drizzle Kit auto-generates descriptive names based on your schema changes. You can customize the name during generation:

# Default: auto-generated name
pnpm drizzle-kit generate

# Custom name (if supported by your drizzle.config.ts)
pnpm drizzle-kit generate --name add_user_profile

Atomic Migrations

An atomic migration is a single, focused change that either succeeds completely or fails completely—there’s no partial state.

Why Atomicity Matters

flowchart LR
  subgraph Atomic["Atomic Migration"]
      A1["Start"] --> A2["All changes apply"]
      A2 --> A3["Success ✓"]
  end

  subgraph NonAtomic["Non-Atomic Migration"]
      B1["Start"] --> B2["Some changes apply"]
      B2 --> B3["Error occurs"]
      B3 --> B4["Partial state ✗"]
  end

Expand

Atomic Migrations	Non-Atomic Migrations
Easy to debug	Hard to determine state
Safe to retry	May require manual cleanup
Clear rollback path	Rollback is complex
One logical change	Multiple unrelated changes

Principles of Atomic Migrations

Each migration should be:

• Small: One logical change per migration
• Complete: All related changes together
• Independent: No reliance on external state
• Reversible: Can be undone if needed (even if via forward migration)

Atomic Migration Examples

✅ Good: Single table creation

0001_create_users_table.sql

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

CREATE UNIQUE INDEX `users_email_unique` ON `users` (`email`);

This is atomic because:

• Creates one table with its constraints
• Index is directly related to the table
• Either the whole table exists or it doesn’t

✅ Good: Related changes together

0002_add_user_profile_fields.sql

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

This is atomic because:

• All columns are part of “user profile” feature
• Changes are logically related
• Rolling back means removing all profile fields

❌ Bad: Unrelated changes mixed

0003_various_updates.sql

-- Adding user profile
ALTER TABLE `users` ADD COLUMN `bio` text;

-- Creating a completely different feature
CREATE TABLE `notifications` (...);

-- Performance optimization unrelated to above
CREATE INDEX `posts_created_at_idx` ON `posts` (`created_at`);

This is problematic because:

• Three unrelated features in one migration
• If notifications table fails, bio column already exists
• Hard to understand what this migration “does”

Breaking Down Large Changes

If a feature requires many database changes, split it into sequential atomic migrations:

# Feature: Add commenting system

migrations/
├── 0010_create_comments_table.sql       # Table structure
├── 0011_add_comments_indexes.sql        # Performance indexes
└── 0012_add_comment_count_to_posts.sql  # Denormalized counter

Each migration is focused, and together they build the complete feature.

Testing Migrations Locally

Always test locally first. This is the most important rule of migration safety.

The Local Testing Workflow

flowchart TD
  A["Modify schema.ts"] --> B["Generate migration"]
  B --> C["Review generated SQL"]
  C --> D["Apply to local D1"]
  D --> E{Tests pass?}
  E -->|Yes| F["Commit migration"]
  E -->|No| G["Fix issues"]
  G --> A

Expand

Step 1: Generate the Migration

Terminal

pnpm drizzle-kit generate

Expected output:

drizzle-kit: v0.20.14
1 migrations generated
└── 0003_add_bio_to_users.sql

Step 2: Review the Generated SQL

Always inspect what Drizzle Kit generated:

Terminal

cat migrations/0003_add_bio_to_users.sql

Look for:

• Correct table and column names
• Appropriate data types
• Expected constraints
• No unexpected destructive changes

Step 3: Apply to Local Database

Terminal

pnpm wrangler d1 migrations apply DB --local

Expected output:

Migrations to be applied:
┌─────────────────────────────┐
│ name                        │
├─────────────────────────────┤
│ 0003_add_bio_to_users.sql   │
└─────────────────────────────┘
✅ Migration 0003_add_bio_to_users.sql applied successfully

Step 4: Verify the Change

Query your local database to confirm the change:

Terminal

pnpm wrangler d1 execute DB --local --command "PRAGMA table_info(users)"

Expected output:

┌─────┬────────────┬─────────┬─────────┬──────────────┬──────┐
│ cid │ name       │ type    │ notnull │ dflt_value   │ pk   │
├─────┼────────────┼─────────┼─────────┼──────────────┼──────┤
│ 0   │ id         │ INTEGER │ 1       │              │ 1    │
│ 1   │ name       │ TEXT    │ 1       │              │ 0    │
│ 2   │ email      │ TEXT    │ 1       │              │ 0    │
│ 3   │ created_at │ INTEGER │ 1       │              │ 0    │
│ 4   │ bio        │ TEXT    │ 0       │              │ 0    │
└─────┴────────────┴─────────┴─────────┴──────────────┴──────┘

Step 5: Test Your Application

Run your application against the migrated local database:

Terminal

pnpm dev

Test the affected features:

• Can you create records with the new column?
• Do existing features still work?
• Are there any runtime errors?

Step 6: Run Automated Tests

Terminal

pnpm test

If tests pass, your migration is ready to commit.

Do’s and Don’ts

✅ Do’s

Do: Review Generated Migrations

Always read the SQL before applying:

# Review before applying
cat migrations/0005_latest_change.sql

# Then apply
pnpm wrangler d1 migrations apply DB --local

Do: Commit Migration Files

Migration files are as important as your code:

git add migrations/
git add drizzle/meta/
git commit -m "feat(db): add bio column to users"

Do: Test the Full Cycle Locally

# 1. Generate
pnpm drizzle-kit generate

# 2. Apply locally
pnpm wrangler d1 migrations apply DB --local

# 3. Run your app
pnpm dev

# 4. Run tests
pnpm test

Do: Use Descriptive Commit Messages

git commit -m "feat(db): add comments table with foreign keys

- Creates comments table with post_id and author_id references
- Adds indexes for common query patterns
- Part of commenting feature (#123)"

Do: Document Breaking Changes

Add comments to migrations that require application changes:

migrations/0010_rename_username.sql

-- BREAKING CHANGE: Renames 'username' to 'display_name'
-- Application code must be updated before deploying this migration
-- Related PR: #456

ALTER TABLE users RENAME COLUMN username TO display_name;

Do: Keep Production Seeds Separate

Never mix test data with migrations:

migrations/0001_create_users.sql

-- ✅ Good: Only schema, no data
CREATE TABLE users (...);

scripts/seed-local.sql

-- Test data lives in seed scripts, not migrations
INSERT INTO users (name, email) VALUES ('Test User', 'test@example.com');

❌ Don’ts

Don’t: Edit Applied Migrations

Once a migration has been applied to any environment (including a teammate’s local database), never modify it:

migrations/0001_create_users.sql

-- ❌ DON'T edit this file after it's been applied
-- This will cause checksum mismatches and failed deployments
CREATE TABLE users (...);

Instead, create a new migration:

migrations/0006_fix_users_table.sql

-- ✅ Create a new migration to make changes
ALTER TABLE users ADD COLUMN missing_column text;

Don’t: Delete Migration Files

Migration files must never be deleted from version control:

# ❌ Never do this
rm migrations/0003_old_migration.sql
git commit -m "cleanup old migrations"

The migration history is permanent. Deleting files breaks the migration chain.

Don’t: Skip Local Testing

# ❌ DON'T do this
pnpm drizzle-kit generate
git add . && git commit -m "add migration"
git push  # Deploying untested migrations!

# ✅ DO this instead
pnpm drizzle-kit generate
pnpm wrangler d1 migrations apply DB --local  # Test first!
pnpm dev  # Verify app works
pnpm test  # Run tests
git add . && git commit -m "add migration"

Don’t: Mix Data Migrations with Schema Migrations

-- ❌ Avoid: Schema and data changes together
CREATE TABLE settings (
  key text PRIMARY KEY,
  value text
);

-- Don't seed data in schema migrations
INSERT INTO settings (key, value) VALUES ('version', '1.0');
INSERT INTO settings (key, value) VALUES ('feature_flag', 'true');

-- ✅ Better: Schema only
CREATE TABLE settings (
  key text PRIMARY KEY,
  value text
);

-- Use separate seed scripts for data

Don’t: Create Dependencies on External State

-- ❌ Avoid: Migration depends on specific data existing
UPDATE users SET role = 'admin' WHERE email = 'specific@email.com';

This migration will behave differently depending on whether that email exists.

Don’t: Use Non-Deterministic Values

-- ❌ Avoid: Different results each time
ALTER TABLE users ADD COLUMN created_at integer DEFAULT (strftime('%s', 'now'));

-- ✅ Better: Deterministic schema, handle defaults in application
ALTER TABLE users ADD COLUMN created_at integer;

Quick Reference Checklist

Use this checklist before committing any migration:

Pre-Commit Checklist

• Migration has a descriptive name
• Migration makes one logical change (atomic)
• Generated SQL has been reviewed
• Migration applied successfully to local D1
• Application tested against migrated database
• Automated tests pass
• Breaking changes are documented in comments
• No test/seed data in migration file

Deployment Checklist

• Migration tested locally
• Migration applied to staging environment
• Staging application tested
• Team notified of any breaking changes
• Rollback strategy documented (if needed)
• Production deployment scheduled

Summary

Following migration best practices ensures reliable database evolution:

Practice	Why It Matters
Descriptive naming	Self-documenting migration history
Atomic migrations	Predictable, debuggable changes
Local testing first	Catch issues before they reach production
Never edit applied migrations	Maintains consistent state across environments
Commit everything	Migration files are as important as code

By following these patterns, your migrations become a reliable, trustworthy part of your development workflow rather than a source of anxiety and production incidents.