Multi-Database Support: CompositeHandler for managing multiple databases in single migration workflow

[vlavrynovych]

Overview

Add support for managing multiple databases within a single migration workflow through a CompositeHandler pattern. This enables migrations that coordinate operations across multiple database systems.

Motivation

Real-World Use Case: Firebase

• Firebase projects often use both Realtime Database (RTDB) and Firestore
• Common migration pattern: transitioning from RTDB to Firestore while maintaining both
• Need to synchronize data between databases during transition period
• Current workaround: separate migration runners for each database (error-prone)

Other Scenarios:

• PostgreSQL (relational) + Redis (cache) + Elasticsearch (search)
• MySQL (main) + S3 (data lake)
• Multi-tenant systems with database-per-tenant
• Microservices with service-per-database requiring coordination

Proposed Solution

CompositeHandler Pattern

// 1. Define composite database interface
interface ICompositeDB extends IDB {
    handlers: Map<string, IDatabaseMigrationHandler<any>>;
    getHandler(name: string): IDatabaseMigrationHandler<any>;
    listHandlers(): string[];
}

// 2. Implement composite handler
export class CompositeHandler implements IDatabaseMigrationHandler<ICompositeDB> {
    db: ICompositeDB;
    backup: IBackupService;
    schemaVersion: ISchemaVersionService<ICompositeDB>;
    
    constructor(handlers: Record<string, IDatabaseMigrationHandler<any>>) {
        this.db = {
            handlers: new Map(Object.entries(handlers)),
            getHandler: (name) => this.db.handlers.get(name)!,
            listHandlers: () => Array.from(this.db.handlers.keys())
        };
        
        this.schemaVersion = new CompositeSchemaVersionService(handlers);
        this.backup = new CompositeBackupService(handlers);
    }
    
    async checkConnection(): Promise<void> {
        // Check all database connections in parallel
        const checks = Array.from(this.db.handlers.values())
            .map(h => h.checkConnection());
        await Promise.all(checks);
    }
    
    getName(): string {
        const names = Array.from(this.db.handlers.values())
            .map(h => h.getName())
            .join(' + ');
        return `Composite(${names})`;
    }
}

Usage Example

// Firebase: Migrate from RTDB to Firestore
class MigrateUsersToFirestore implements IRunnableScript<ICompositeDB> {
    async up(db: ICompositeDB, info: IMigrationInfo): Promise<string> {
        // Access both databases
        const rtdbHandler = db.getHandler('realtime');
        const firestoreHandler = db.getHandler('firestore');
        
        // Read from RTDB
        const snapshot = await rtdbHandler.db.database.ref('/users').once('value');
        const users = snapshot.val();
        
        // Write to Firestore
        const batch = firestoreHandler.db.firestore.batch();
        Object.entries(users).forEach(([id, user]) => {
            const ref = firestoreHandler.db.firestore.collection('users').doc(id);
            batch.set(ref, user);
        });
        await batch.commit();
        
        return `Migrated ${Object.keys(users).length} users from RTDB to Firestore`;
    }
    
    async down(db: ICompositeDB): Promise<string> {
        // Rollback Firestore changes
        const firestoreHandler = db.getHandler('firestore');
        const batch = firestoreHandler.db.firestore.batch();
        
        const snapshot = await firestoreHandler.db.firestore.collection('users').get();
        snapshot.docs.forEach(doc => batch.delete(doc.ref));
        await batch.commit();
        
        return 'Rolled back Firestore users';
    }
}

// Create executor with composite handler
const compositeHandler = new CompositeHandler({
    realtime: realtimeDatabaseHandler,
    firestore: firestoreHandler
});

const executor = new MigrationScriptExecutor<ICompositeDB>({
    handler: compositeHandler,
    config
});

await executor.up();

Design Challenges & Solutions

1. Schema Version Tracking

Option A: Primary Database (Recommended)

class CompositeSchemaVersionService implements ISchemaVersionService<ICompositeDB> {
    constructor(
        private handlers: Record<string, IDatabaseMigrationHandler<any>>,
        private primaryDb: string = Object.keys(handlers)[0]
    ) {}
    
    async create(): Promise<boolean> {
        // Use primary database for schema tracking
        return this.handlers[this.primaryDb].schemaVersion.create();
    }
    
    async recordMigration(script: MigrationScript<ICompositeDB>): Promise<void> {
        // Record in primary database only
        return this.handlers[this.primaryDb].schemaVersion.recordMigration(script);
    }
}

Option B: All Databases

• Record migration history in every database
• Adds redundancy but ensures each database knows its state
• More complex to maintain consistency

Recommendation: Start with Option A (primary database), add Option B as configurable behavior.

2. Backup/Restore Coordination

class CompositeBackupService implements IBackupService {
    async backup(): Promise<string> {
        // Create backups for all databases
        const backups = await Promise.all(
            Object.entries(this.handlers).map(async ([name, handler]) => ({
                name,
                path: await handler.backup.backup()
            }))
        );
        
        // Create manifest file
        const manifest = {
            timestamp: Date.now(),
            databases: backups,
            version: '1.0.0'
        };
        
        const manifestPath = `./backups/composite-${Date.now()}.json`;
        fs.writeFileSync(manifestPath, JSON.stringify(manifest, null, 2));
        
        return manifestPath;
    }
    
    async restore(backupPath: string): Promise<void> {
        // Read manifest
        const manifest = JSON.parse(fs.readFileSync(backupPath, 'utf8'));
        
        // Restore all databases in parallel
        await Promise.all(
            manifest.databases.map(({ name, path }) =>
                this.handlers[name].backup.restore(path)
            )
        );
    }
}

3. Transaction Handling

Reality Check: Cross-database distributed transactions (2-phase commit) are complex and rarely needed.

Solution: Document that transactions are per-database:

// Composite handler doesn't support cross-database transactions
config.transaction.mode = TransactionMode.NONE;

Migrations handle coordination manually:

async up(db: ICompositeDB): Promise<string> {
    try {
        // Each database operation is atomic within that database
        await db.getHandler('db1').db.query('INSERT ...');
        await db.getHandler('db2').db.query('INSERT ...');
        return 'Success';
    } catch (error) {
        // Use down() method for rollback coordination
        throw error;
    }
}

4. Validation

class CompositeValidationService {
    async validate(scripts: MigrationScript<ICompositeDB>[]): Promise<IValidationResult[]> {
        const issues: IValidationIssue[] = [];
        
        scripts.forEach(script => {
            // Check if migration accesses at least one database
            // Warn about potential cross-database consistency issues
            // Validate down() method handles rollback properly
        });
        
        return issues;
    }
}

Implementation Plan

Phase 1: Core Support (v0.8.0 or v1.0.0)

Add to MSR Core:

// src/interface/ICompositeDB.ts
export interface ICompositeDB extends IDB {
    handlers: Map<string, IDatabaseMigrationHandler<any>>;
    getHandler<K extends string>(name: K): IDatabaseMigrationHandler<any>;
    listHandlers(): string[];
}

// src/handler/CompositeHandler.ts
export class CompositeHandler implements IDatabaseMigrationHandler<ICompositeDB> {
    // Implementation
}

// src/service/CompositeSchemaVersionService.ts
export class CompositeSchemaVersionService implements ISchemaVersionService<ICompositeDB> {
    // Implementation
}

// src/service/CompositeBackupService.ts
export class CompositeBackupService implements IBackupService {
    // Implementation
}

Files to Create:

• src/interface/ICompositeDB.ts
• src/handler/CompositeHandler.ts
• src/service/CompositeSchemaVersionService.ts
• src/service/CompositeBackupService.ts
• test/unit/handler/CompositeHandler.test.ts
• test/integration/composite-handler.test.ts
• docs/guides/multi-database-support.md
• docs/examples/firebase-composite.md

Phase 2: Firebase Adapter (msr-firebase v2.0.0)

Create Firebase-specific composite handler:

// Firebase-specific implementation
export class FirebaseCompositeHandler extends CompositeHandler {
    constructor(config: {
        realtimeDatabase?: database.Database;
        firestore?: firestore.Firestore;
        storage?: storage.Storage;
    }) {
        const handlers: any = {};
        
        if (config.realtimeDatabase) {
            handlers.realtime = new RealtimeDatabaseHandler(config.realtimeDatabase);
        }
        
        if (config.firestore) {
            handlers.firestore = new FirestoreHandler(config.firestore);
        }
        
        if (config.storage) {
            handlers.storage = new StorageHandler(config.storage);
        }
        
        super(handlers);
    }
}

Benefits

✅ Real-world use case - Directly addresses Firebase RTDB + Firestore migration scenario
✅ Clean abstraction - Single executor manages multiple databases
✅ Type-safe - TypeScript generics maintain type safety across handlers
✅ Coordinated operations - Backup/restore all databases together atomically
✅ Flexible - Can still use single-database handlers (backward compatible)
✅ Extensible - Easy to add more databases to the composite
✅ Differentiator - Most migration tools don't support multi-database scenarios

Potential Issues

⚠️ No distributed transactions - Each database operates independently (document limitation)
⚠️ Complex rollback - Partial failures need careful handling in down() methods
⚠️ Schema tracking decision - Need to choose primary database or replicate across all
⚠️ Validation complexity - Harder to validate cross-database migrations automatically
⚠️ Testing complexity - Need to mock/emulate multiple databases in tests

Documentation Requirements

• Guide: Multi-Database Support Overview
• Guide: Creating Composite Handlers
• Guide: Best Practices for Cross-Database Migrations
• Example: Firebase RTDB + Firestore Migration
• Example: PostgreSQL + Redis Coordination
• API Reference: CompositeHandler
• API Reference: ICompositeDB
• Migration Guide: Single DB → Composite DB

Testing Requirements

• Unit tests for CompositeHandler
• Unit tests for CompositeSchemaVersionService
• Unit tests for CompositeBackupService
• Integration tests with mock handlers
• Integration tests with real databases (optional)
• Example migrations in test suite
• Rollback scenario tests
• Partial failure handling tests

Success Criteria

• ✅ CompositeHandler supports 2+ databases
• ✅ Schema version tracking configurable (primary vs all)
• ✅ Backup/restore coordinates all databases
• ✅ Validation warns about cross-database risks
• ✅ 100% test coverage maintained
• ✅ Documentation complete with examples
• ✅ Firebase adapter demonstrates real-world usage
• ✅ No breaking changes to existing API

Related Work

• Multi-tenancy - Could extend to support multiple instances of same database
• Connection pooling - CompositeHandler could manage connection pools
• Health checks - Aggregate health status across databases
• Metrics - Collect metrics per database in composite setup

Future Enhancements

• Database routing - Route migrations to specific databases based on metadata
• Dependency graphs - Declare dependencies between database operations
• Distributed transactions (optional) - 2-phase commit for systems that support it
• Database discovery - Auto-detect and register databases at runtime

References

• Firebase use case discussion (this conversation)
• MSR Core v0.7.0 architecture (Facade and Factory patterns)
• Microservices patterns: Database per Service
• Martin Fowler: PolyglotPersistence

---

Next Steps

1. Community feedback on design approach
2. Decide on schema tracking strategy (Option A vs B)
3. Prototype CompositeHandler in feature branch
4. Validate with Firebase RTDB + Firestore use case
5. Implement full feature in v0.8.0 or v1.0.0

Target Version: v0.8.0 or v1.0.0
Estimated Effort: 2-3 weeks (including tests and docs)
Priority: Medium (valuable but not blocking current use cases)