Migration Guide

This guide documents the process for migrating MonoTask data from SQLite to Cloudflare D1.

Overview

The migration uses a dual-write strategy to ensure zero downtime and safe rollback capability:

Phase 1: Dual Write - Write to both SQLite (primary) and D1 (secondary)
Phase 2: Data Migration - Bulk copy existing data from SQLite to D1
Phase 3: Validation - Verify data consistency
Phase 4: Cutover - Switch reads to D1 (D1 becomes primary)
Phase 5: Cleanup - Remove dual-write and deprecate SQLite

Prerequisites

D1 database created in Cloudflare dashboard
D1 binding configured in wrangler.toml
Database schema migrated to D1 (run all migrations on D1)
Dual-write adapter configured and tested

Migration Process

1. Enable Dual-Write Mode

Configure your application to write to both databases:

typescript

import { createSQLiteAdapter } from '@monotask/shared/database/sqlite-adapter';
import { createD1Adapter } from '@monotask/shared/database/d1-adapter';
import { createDualWriteAdapter } from '@monotask/shared/database/dual-write-adapter';

const sqliteAdapter = createSQLiteAdapter(db);
const d1Adapter = createD1Adapter(env.DB);

const adapter = createDualWriteAdapter(sqliteAdapter, d1Adapter, {
  mode: 'primary-sqlite', // SQLite is still primary for reads
  verifyWrites: true,      // Verify data matches
  continueOnSecondaryFailure: true, // Don't fail if D1 write fails
  collectMetrics: true     // Track performance
});

Monitor for issues:

Check logs for verification failures
Monitor D1 write latency
Watch for secondary write failures

2. Run Data Migration Script

Once dual-write is stable, migrate existing data:

bash

# Dry run to test migration
MIGRATION_DRY_RUN=true \
D1_DATABASE_BINDING=your-d1-binding \
bun migrations/009-sqlite-to-d1-migration.ts

# Actual migration with validation
MIGRATION_VALIDATE=true \
MIGRATION_BATCH_SIZE=1000 \
D1_DATABASE_BINDING=your-d1-binding \
bun run db:migrate

Migration Options:

Environment Variable	Default	Description
`MIGRATION_BATCH_SIZE`	1000	Rows to migrate per batch
`MIGRATION_VALIDATE`	true	Validate after migration
`MIGRATION_CONTINUE_ON_ERROR`	false	Continue if table fails
`MIGRATION_DRY_RUN`	false	Test without writing

Expected Output:

🚀 Starting SQLite to D1 migration...

📊 Found 95 tables to migrate

📦 Migrating table: users
  Total rows: 150
  Progress: 100% (150/150 rows)
  ✓ Successfully migrated 150 rows

🔍 Validating table: users
  ✓ Row counts match: 150
  ✓ Data validation passed

...

==========================================================
📊 Migration Summary
==========================================================
Total tables: 95
Total rows: 150,000
Migrated rows: 150,000
Failed rows: 0
Validation errors: 0
Duration: 45.23s
==========================================================

✓ Migration completed successfully

3. Validate Data Consistency

Run comprehensive validation:

typescript

import { validateDatabases, quickValidate } from '@monotask/shared/database/data-validation';

// Quick validation (row counts only)
const quick = await quickValidate(sqliteAdapter, d1Adapter);

// Full validation (sample rows)
const full = await validateDatabases(sqliteAdapter, d1Adapter, {
  sampleSize: 100,        // Validate 100 random rows per table
  validateAllRows: false, // Set true for complete validation
  continueOnError: true
});

console.log(full.summary);

Validation checks:

Row counts match between databases
Sample rows have identical data
Primary keys exist in both databases
JSON fields parse correctly

4. Switch to D1 Primary

Once validation passes, switch reads to D1:

typescript

const adapter = createDualWriteAdapter(sqliteAdapter, d1Adapter, {
  mode: 'primary-d1', // ← D1 is now primary for reads
  verifyWrites: true,
  continueOnSecondaryFailure: true
});

Or use the helper method:

typescript

adapter.switchPrimary(); // Swaps primary and secondary

Monitor closely:

Watch for read latency changes
Check for any data inconsistency errors
Monitor D1 request rates and quotas

5. Gradual Rollout

For safety, use feature flags to gradually route traffic:

typescript

const useDualWrite = await featureFlags.isEnabled('dual-write-d1');
const adapter = useDualWrite
  ? createDualWriteAdapter(sqliteAdapter, d1Adapter, { mode: 'primary-d1' })
  : sqliteAdapter;

Rollout stages:

5% of traffic → Monitor for 24 hours
25% of traffic → Monitor for 24 hours
50% of traffic → Monitor for 24 hours
100% of traffic → Monitor for 1 week

6. Remove Dual-Write

After confirming D1 stability, remove dual-write:

typescript

// Before
const adapter = createDualWriteAdapter(sqliteAdapter, d1Adapter, {
  mode: 'primary-d1'
});

// After
const adapter = createD1Adapter(env.DB);

Cleanup steps:

Update all repository instantiations
Remove SQLite adapter code
Archive SQLite database files
Remove dual-write adapter code

Rollback Procedures

Rolling Back from D1 to SQLite

If issues occur during dual-write phase:

Stop writing to D1:

typescript

const adapter = createSQLiteAdapter(db); // Back to SQLite only

No data loss - SQLite has all data
Investigate and fix issues
Restart dual-write when ready

If issues occur after switching to D1 primary:

Switch back to SQLite primary:

typescript

adapter.switchPrimary(); // SQLite becomes primary again

Monitor dual-write to ensure SQLite is current

Optionally migrate any missing data from D1 to SQLite:

bash

# Run reverse migration (D1 → SQLite)
SOURCE_DB=d1 \
TARGET_DB=sqlite \
bun migrations/rollback-d1-to-sqlite.ts

Once SQLite is current, switch to SQLite only

If D1 is completely broken:

Switch to SQLite immediately:

typescript

const adapter = createSQLiteAdapter(db);

Deploy emergency fix

Restore from SQLite backup if needed:

bash

cp .monotask/backups/project-YYYY-MM-DD.db .monotask/project.db

Data Recovery

Create backup before migration:

bash

# SQLite backup
cp .monotask/project.db .monotask/backups/project-$(date +%Y-%m-%d).db

# Or use SQLite backup command
sqlite3 .monotask/project.db ".backup '.monotask/backups/project-$(date +%Y-%m-%d).db'"

D1 backup (export to JSON):

bash

# Export all tables to JSON
wrangler d1 execute <DATABASE_NAME> \
  --command="SELECT * FROM users" \
  --json > backups/users.json

Restore from backup:

bash

# SQLite restore
cp .monotask/backups/project-YYYY-MM-DD.db .monotask/project.db

# D1 restore (from JSON)
bun scripts/restore-d1-from-json.ts

Monitoring and Metrics

Dual-Write Metrics

typescript

const metrics = adapter.getMetrics();

console.log({
  totalWrites: metrics.totalWrites,
  successfulDualWrites: metrics.successfulDualWrites,
  secondaryFailures: metrics.secondaryFailures,
  verificationFailures: metrics.verificationFailures,
  averagePrimaryLatency: metrics.averagePrimaryLatency,
  averageSecondaryLatency: metrics.averageSecondaryLatency
});

Key metrics to watch:

secondaryFailures should be near zero
verificationFailures should be zero
averageSecondaryLatency should be < 100ms for good UX

Health Checks

Add health check endpoints:

typescript

app.get('/health/database', async (c) => {
  const sqliteHealth = await checkSQLiteHealth(sqliteAdapter);
  const d1Health = await checkD1Health(d1Adapter);

  return c.json({
    sqlite: sqliteHealth,
    d1: d1Health,
    dualWrite: {
      mode: adapter.getMetadata().mode,
      metrics: adapter.getMetrics()
    }
  });
});

Performance Optimization

D1 Best Practices

Batch writes when possible:

typescript

await adapter.batch([
  adapter.prepare('INSERT INTO ...').bind(...),
  adapter.prepare('INSERT INTO ...').bind(...),
]);

Use prepared statements:

typescript

const stmt = adapter.prepare('SELECT * FROM users WHERE id = ?');
const user = await stmt.bind(userId).first();

Add indexes for common queries:

sql

CREATE INDEX idx_tasks_state ON tasks(current_state);
CREATE INDEX idx_tasks_project ON tasks(project_id);

Cache frequently accessed data:

typescript

const cache = new Map();
const cachedUser = cache.get(userId) ?? await fetchUser(userId);

Optimizing Migration Speed

For large datasets, optimize batch size:

bash

# Larger batches = faster migration but more memory
MIGRATION_BATCH_SIZE=5000 bun run db:migrate

# Parallel table migration (if supported)
MIGRATION_PARALLEL_TABLES=4 bun run db:migrate

Troubleshooting

Common Issues

Issue: Verification failures during dual-write

[DUAL-WRITE] Verification failed: primary and secondary results differ

Solution:

Check D1 schema matches SQLite exactly
Verify JSON serialization is consistent
Check for timezone/date handling differences
Disable verification temporarily: verifyWrites: false

Issue: D1 quota exceeded

Error: D1 quota exceeded for account

Solution:

Reduce batch size: MIGRATION_BATCH_SIZE=100
Add delays between batches
Upgrade Cloudflare plan
Migrate during off-peak hours

Issue: Migration timeout

Error: Migration timeout after 60000ms

Solution:

Increase timeout: MIGRATION_TIMEOUT=300000
Migrate tables individually
Use smaller batch sizes

Issue: Row count mismatch after migration

❌ Row count mismatch: SQLite=1000, D1=998

Solution:

Re-run migration for affected table
Check migration logs for errors
Verify network stability during migration
Run with MIGRATION_CONTINUE_ON_ERROR=false to catch failures

Testing Migration

Local Testing

Test migration in development:

bash

# 1. Create test D1 database
wrangler d1 create monotask-test

# 2. Update wrangler.toml
[[d1_databases]]
binding = "DB"
database_name = "monotask-test"
database_id = "xxx"

# 3. Run test migration
MIGRATION_DRY_RUN=true bun run db:migrate

# 4. Validate
bun test packages/shared/src/database/data-validation.test.ts

Staging Testing

Full migration test in staging:

Clone production database to staging
Run migration with validation
Run full test suite against D1
Load test to verify performance
Test rollback procedure

Migration Checklist

[ ] Backup SQLite database
[ ] Run schema migrations on D1
[ ] Enable dual-write mode
[ ] Monitor dual-write for 24 hours
[ ] Run dry-run migration
[ ] Run actual migration
[ ] Validate data consistency
[ ] Switch to D1 primary (10% traffic)
[ ] Monitor for 24 hours
[ ] Gradually increase traffic to D1
[ ] Monitor for 1 week
[ ] Remove dual-write code
[ ] Archive SQLite database
[ ] Document lessons learned
[ ] Update monitoring dashboards

Support

For issues during migration:

Check logs: bun run monitor
Review metrics: adapter.getMetrics()
Test rollback: adapter.switchPrimary()
Contact Cloudflare support for D1 issues

Migration Guide ​

Overview ​

Prerequisites ​

Migration Process ​

1. Enable Dual-Write Mode ​

2. Run Data Migration Script ​

3. Validate Data Consistency ​

4. Switch to D1 Primary ​

5. Gradual Rollout ​

6. Remove Dual-Write ​

Rollback Procedures ​

Rolling Back from D1 to SQLite ​

Data Recovery ​

Monitoring and Metrics ​

Dual-Write Metrics ​

Health Checks ​

Performance Optimization ​

D1 Best Practices ​

Optimizing Migration Speed ​

Troubleshooting ​

Common Issues ​

Testing Migration ​

Local Testing ​

Staging Testing ​

Migration Checklist ​

Support ​

References ​

Migration Guide

Overview

Prerequisites

Migration Process

1. Enable Dual-Write Mode

2. Run Data Migration Script

3. Validate Data Consistency

4. Switch to D1 Primary

5. Gradual Rollout

6. Remove Dual-Write

Rollback Procedures

Rolling Back from D1 to SQLite

Data Recovery

Monitoring and Metrics

Dual-Write Metrics

Health Checks

Performance Optimization

D1 Best Practices

Optimizing Migration Speed

Troubleshooting

Common Issues

Testing Migration

Local Testing

Staging Testing

Migration Checklist

Support

References