Database Migrations

Manage database schema changes

Database Migrations

PULSE uses SQL migrations to manage database schema changes.

Understanding Migrations

Migrations are numbered SQL files in the migrations/ directory:

migrations/
├── 001_initial_schema.sql
├── 002_sessions.sql
├── 003_indexes.sql
├── 004_cohorts.sql
├── ...
└── 021_performance_indexes.sql

Each migration:

• Is idempotent (safe to run multiple times)
• Has no dependencies on other migrations
• Should be reversible (include DROP statements)

Running Migrations

For Local Development

wrangler d1 execute pulse_analytics --local --file=migrations/001_initial_schema.sql

Or apply all:

wrangler d1 execute pulse_analytics --local --batch migrations/*.sql

For Remote (Staging/Production)

wrangler d1 execute pulse_analytics --remote --file=migrations/001_initial_schema.sql

Creating New Migrations

1. Create new file: migrations/XXX_description.sql

-- Migration: Add custom_fields to events table
-- Created: 2024-03-05

CREATE TABLE IF NOT EXISTS custom_fields (
  field_id TEXT PRIMARY KEY,
  site_id TEXT NOT NULL,
  name TEXT NOT NULL,
  type TEXT NOT NULL,
  created_at INTEGER NOT NULL,
  FOREIGN KEY (site_id) REFERENCES sites(id)
);

CREATE INDEX idx_custom_fields_site
  ON custom_fields(site_id);

2. Test locally:

wrangler d1 execute pulse_analytics --local --file=migrations/022_custom_fields.sql

3. Deploy:

# Staging first
npm run deploy:staging

# Verify works
curl https://staging-api.example.com/health

# Then production
npm run deploy:blue

Migration Strategy

Blue-Green

1. Deploy new Worker code without migrations
2. Run migrations
3. Workers automatically use new schema

Backward Compatibility

Keep old code working with new schema:

// Query with fallback
const result = await db.prepare(`
  SELECT id, name, custom_field
  FROM events
`).first()

// Handle missing custom_field
const customField = result?.custom_field || null

Common Migrations

Add Column

ALTER TABLE events ADD COLUMN custom_data TEXT;

Add Index

CREATE INDEX idx_events_custom ON events(custom_data);

Rename Column

ALTER TABLE events RENAME COLUMN old_name TO new_name;

Drop Column

ALTER TABLE events DROP COLUMN old_column;

Reversing a Migration

If a migration causes issues:

1. Identify breaking migration
2. Create reverse migration:

-- Reverse migration 022
-- Created: 2024-03-06

DROP TABLE IF EXISTS custom_fields;

3. Deploy rollback:

npm run deploy:rollback

Best Practices

1. Test in Development

   wrangler d1 execute pulse_analytics --local --file=migrations/022.sql

2. Verify Production First

   wrangler d1 execute pulse_analytics --remote --file=migrations/022.sql --preview

3. Use Transactions (if supported)

   BEGIN TRANSACTION;
   -- Migration code
   COMMIT;

4. Document Changes

   -- Migration: Description
   -- Date: YYYY-MM-DD
   -- Reason: Explain why this migration exists

Migration Status

Check which migrations have been applied:

# View all migrations
ls -la migrations/

# Check current schema
wrangler d1 execute pulse_analytics --remote --command="SELECT name FROM sqlite_master WHERE type='table'"

Next Steps

• Troubleshooting — Resolve issues
• Compliance — GDPR and compliance