Database Migrations

Database schema changes in Aero2 are managed through numbered SQL migration files. Migrations are applied manually and follow the expand-contract pattern to ensure zero-downtime deployments.

Migration Files

Migration files live in the migrations/ directory with sequential numbering:

migrations/
  0001_schema.sql    # Complete schema for fresh installs

The current schema is consolidated into a single file. Future migrations are added as incremental files (0002_*.sql, 0003_*.sql, etc.).

Creating a Migration

touch migrations/NNNN_description.sql

Use the next sequential number and a brief descriptive name. Write standard SQL (SQLite dialect) in the file.

Safe Migration Process

Migrations follow the expand-contract pattern to avoid downtime. The key rule: never ship code changes and schema changes in the same PR.

Step 1: PR 1 -- Code That Works with Both Schemas

Write code that works with both the old schema and the new schema. Merge and deploy this PR first.

Examples:

If adding a column, write code that handles the column being absent
If renaming a column, read from both old and new column names
If adding a table, the code should not depend on the table existing yet

Step 2: PR 2 -- Migration Files Only

Create a PR containing only the migration SQL files. No code changes. This PR triggers CI but does not auto-apply the migration.

Step 3: Apply Migrations

After the migration PR is merged, apply the migration manually:

# Apply to QA first
npm run migrate:qa
 
# Verify the QA environment works correctly
curl https://qa.aero2.dev/health
 
# Then apply to production
npm run migrate:prod
 
# Verify production
curl https://aero2.dev/health

Step 4: PR 3 -- Clean Up (Optional)

After the migration is applied to all environments, you can submit a follow-up PR to remove backward-compatibility code (e.g., stop reading from the old column name).

Safe vs Dangerous Migrations

Safe Migrations (Can Apply Anytime)

These migrations are backward-compatible and can be applied without code changes:

Operation	Example
Add a column	`ALTER TABLE users ADD COLUMN phone TEXT`
Create a table	`CREATE TABLE user_credentials (...)`
Create an index	`CREATE INDEX idx_users_phone ON users(phone)`
Add a default value	New column with `DEFAULT` clause

Dangerous Migrations (Use Expand-Contract)

These migrations can break running code and require the expand-contract pattern:

Operation	Risk	Mitigation
Drop a column	Existing code references the column	Deploy code that stops using the column first
Rename a column	Existing code references the old name	Add new column, migrate data, update code, then drop old column
Change column type	Existing code expects the old type	Add new column with new type, migrate data
Add NOT NULL without default	Existing rows will fail the constraint	Add column as nullable first, backfill, then add NOT NULL
Drop a table	Existing code references the table	Remove all code references first

Example: Adding a Column

The expand-contract pattern for adding a new column:

Migration file (0002_add_phone.sql):

-- Add column as nullable (safe — existing code ignores it)
ALTER TABLE users ADD COLUMN phone TEXT;
 
-- Add index if queries will filter on this column
CREATE INDEX idx_users_phone ON users(app_id, phone);

Code changes to use the new column go in a separate PR, deployed before or after the migration.

Applying Migrations Manually

Under the hood, the npm scripts run wrangler D1 commands:

# What npm run migrate:qa does:
npx wrangler d1 execute aero2 --env qa --file=./migrations/NNNN_name.sql
 
# What npm run migrate:prod does:
npx wrangler d1 execute aero2 --env production --file=./migrations/NNNN_name.sql

Local Development

For local development, apply all migrations to the local D1 database:

npx wrangler d1 execute aero2 --local --file=./migrations/0001_initial_schema.sql

To start fresh:

rm -rf .wrangler/state/
npx wrangler d1 execute aero2 --local --file=./migrations/0001_initial_schema.sql

Important Rules

Never drop columns in the same PR as code changes. Always use separate PRs.
Always apply to QA first and verify before applying to production.
Migration files are append-only. Never modify an already-applied migration file.
Test migrations locally before submitting the PR.
D1 does not support ALTER TABLE DROP COLUMN in SQLite. Use the expand-contract pattern with a new column instead.