Database Migrations

Manage database schema changes with migrations.

Migration Files

migrations/
├── 002_multi_agent.sql
├── 003_agent_auth.sql
├── 004_workflow_executions.sql
├── 005_cloudflare_settings.sql
├── 006_browser_actions_types.sql
└── 007_puppeteer_action_types.sql

Initial Setup

Apply Main Schema

npx wrangler d1 execute agents-dashboard --file=./schema.sql

Apply All Migrations

# Apply in order
npx wrangler d1 execute agents-dashboard --file=./migrations/002_multi_agent.sql
npx wrangler d1 execute agents-dashboard --file=./migrations/003_agent_auth.sql
npx wrangler d1 execute agents-dashboard --file=./migrations/004_workflow_executions.sql
npx wrangler d1 execute agents-dashboard --file=./migrations/005_cloudflare_settings.sql
npx wrangler d1 execute agents-dashboard --file=./migrations/006_browser_actions_types.sql
npx wrangler d1 execute agents-dashboard --file=./migrations/007_puppeteer_action_types.sql

Local Development

# Apply to local database
npx wrangler d1 execute agents-dashboard --local --file=./schema.sql
npx wrangler d1 execute agents-dashboard --local --file=./migrations/002_multi_agent.sql
# ... continue for all migrations

Creating Migrations

1. Create Migration File

touch migrations/008_new_feature.sql

2. Write Migration SQL

-- migrations/008_new_feature.sql

-- Add new column
ALTER TABLE agents ADD COLUMN new_field TEXT;

-- Create new table
CREATE TABLE IF NOT EXISTS new_table (
  id TEXT PRIMARY KEY DEFAULT (lower(hex(randomblob(4)))),
  agent_id TEXT NOT NULL REFERENCES agents(id),
  data TEXT,
  created_at DATETIME DEFAULT CURRENT_TIMESTAMP
);

-- Create index
CREATE INDEX IF NOT EXISTS idx_new_table_agent
ON new_table(agent_id);

3. Test Locally

npx wrangler d1 execute agents-dashboard --local --file=./migrations/008_new_feature.sql

4. Apply to Production

npx wrangler d1 execute agents-dashboard --file=./migrations/008_new_feature.sql

Migration Patterns

Add Column

ALTER TABLE agents ADD COLUMN new_field TEXT;
ALTER TABLE agents ADD COLUMN count INTEGER DEFAULT 0;

Add Table

CREATE TABLE IF NOT EXISTS new_table (
  id TEXT PRIMARY KEY DEFAULT (lower(hex(randomblob(4)))),
  name TEXT NOT NULL,
  created_at DATETIME DEFAULT CURRENT_TIMESTAMP
);

Add Index

CREATE INDEX IF NOT EXISTS idx_table_column ON table_name(column_name);
CREATE INDEX IF NOT EXISTS idx_table_multi ON table_name(col1, col2);

Add Foreign Key

SQLite doesn't support ADD CONSTRAINT. Recreate table:

-- Create new table with constraint
CREATE TABLE new_table_temp (
  id TEXT PRIMARY KEY,
  agent_id TEXT NOT NULL REFERENCES agents(id),
  data TEXT
);

-- Copy data
INSERT INTO new_table_temp SELECT * FROM new_table;

-- Drop old table
DROP TABLE new_table;

-- Rename
ALTER TABLE new_table_temp RENAME TO new_table;

Add Default Value

ALTER TABLE agents ADD COLUMN status TEXT DEFAULT 'active';

Migration Best Practices

1. Always Use IF NOT EXISTS

CREATE TABLE IF NOT EXISTS new_table (...);
CREATE INDEX IF NOT EXISTS idx_name ON table(column);

2. Make Migrations Idempotent

Safe to run multiple times:

-- Safe
CREATE TABLE IF NOT EXISTS users (...);

-- Not safe (will fail on re-run)
CREATE TABLE users (...);

3. Keep Migrations Small

One feature per migration:

-- migrations/008_add_user_preferences.sql
ALTER TABLE users ADD COLUMN preferences TEXT;

4. Name Migrations Descriptively

008_add_user_preferences.sql
009_create_audit_log.sql
010_add_workflow_tags.sql

5. Test Before Production

# Test locally first
npx wrangler d1 execute agents-dashboard --local --file=./migrations/008_new.sql

# Verify
npx wrangler d1 execute agents-dashboard --local --command "SELECT * FROM sqlite_master WHERE type='table'"

Rollback Strategy

D1 doesn't support transactions for DDL. Create reverse migrations:

Forward Migration

-- migrations/008_add_feature.sql
ALTER TABLE agents ADD COLUMN new_field TEXT;

Rollback Migration

-- rollbacks/008_remove_feature.sql
-- Note: SQLite doesn't support DROP COLUMN easily
-- May need to recreate table

Schema Inspection

List Tables

npx wrangler d1 execute agents-dashboard \
  --command "SELECT name FROM sqlite_master WHERE type='table'"

Describe Table

npx wrangler d1 execute agents-dashboard \
  --command "PRAGMA table_info(agents)"

List Indexes

npx wrangler d1 execute agents-dashboard \
  --command "SELECT name FROM sqlite_master WHERE type='index'"

CI/CD Migrations

GitHub Actions

- name: Apply migrations
  run: |
    npx wrangler d1 execute agents-dashboard --file=./schema.sql
    for f in migrations/*.sql; do
      npx wrangler d1 execute agents-dashboard --file="$f"
    done
  env:
    CLOUDFLARE_API_TOKEN: ${{ secrets.CF_API_TOKEN }}

Migration Tracking

Consider a migrations table:

CREATE TABLE IF NOT EXISTS _migrations (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  name TEXT NOT NULL UNIQUE,
  applied_at DATETIME DEFAULT CURRENT_TIMESTAMP
);

Apply with tracking:

-- At end of migration
INSERT OR IGNORE INTO _migrations (name) VALUES ('008_add_feature');

Troubleshooting

Migration Failed

1. Check syntax errors
2. Verify table/column exists
3. Check constraints

# Get error details
npx wrangler d1 execute agents-dashboard --command "SELECT * FROM agents LIMIT 1"

Duplicate Column

Error: duplicate column name: new_field

Already applied. Skip or check IF NOT EXISTS.

Foreign Key Violation

Error: FOREIGN KEY constraint failed

Ensure referenced row exists or set ON DELETE behavior.