Add connection validation method to IDB interface

[vlavrynovych]

Problem

Currently, MSR does not validate database connectivity before attempting to run migrations. If the database connection is invalid or unavailable, the migration process fails during execution, which could be:

• After creating backups
• After initializing schema version table
• Partway through migration execution

This wastes time and resources, and makes error diagnosis harder.

Proposed Solution

Add a connection validation method to the IDB interface that allows database handlers to verify connectivity before any migration operations begin.

Design Options to Consider

Option 1: Optional Method (Non-Breaking)

export interface IDB {
    [key: string]: unknown;
    
    /**
     * Optional: Check if database connection is healthy.
     * If not implemented, connection check is skipped.
     */
    checkConnection?(): Promise<boolean>;
}

Option 2: Required Method (Breaking Change)

export interface IDB {
    [key: string]: unknown;
    
    /**
     * Required: Verify database connection is healthy.
     * @returns true if connection is valid
     * @throws Error if connection fails with details
     */
    checkConnection(): Promise<boolean>;
}

Option 3: Separate Interface

export interface IDBWithHealthCheck extends IDB {
    checkConnection(): Promise<boolean>;
}

Implementation Examples

PostgreSQL:

class PostgresDB implements IDB {
    async checkConnection(): Promise<boolean> {
        try {
            await this.pool.query('SELECT 1');
            return true;
        } catch (error) {
            this.logger.error('DB connection failed:', error);
            return false;
        }
    }
}

MySQL:

class MySQLDB implements IDB {
    async checkConnection(): Promise<boolean> {
        try {
            await this.connection.ping();
            return true;
        } catch (error) {
            return false;
        }
    }
}

MongoDB:

class MongoDB implements IDB {
    async checkConnection(): Promise<boolean> {
        try {
            await this.client.db().admin().ping();
            return true;
        } catch (error) {
            return false;
        }
    }
}

When to Call checkConnection()

The connection check should be called:

1. Before migration execution in MigrationScriptExecutor.migrate()
2. Before rollback operations in MigrationScriptExecutor.rollbackTo()
3. Before validation in MigrationScriptExecutor.validate()
4. Optionally before backup creation (if backup uses the same connection)

Error Handling Strategy

Option A: Fail Fast (Recommended)

if (db.checkConnection && !(await db.checkConnection())) {
    throw new Error('Database connection check failed. Cannot proceed with migrations.');
}

Option B: Return Result

const connectionValid = db.checkConnection 
    ? await db.checkConnection() 
    : true; // Assume valid if not implemented

if (!connectionValid) {
    this.logger.warn('Connection check failed');
    // User decides whether to proceed
}

Option C: Auto-Retry

async function checkConnectionWithRetry(db: IDB, maxRetries = 3): Promise<boolean> {
    for (let i = 0; i < maxRetries; i++) {
        if (db.checkConnection && await db.checkConnection()) {
            return true;
        }
        await sleep(1000 * (i + 1)); // Exponential backoff
    }
    return false;
}

Design Questions to Resolve

1. Breaking vs Non-Breaking: Should this be optional or required?

   • Optional = no breaking change, but users might not implement it
   • Required = forces good practices, but breaks existing implementations

2. Return Type: Boolean vs throw?

   • Boolean: Simple, caller handles logic
   • Throw: More expressive, includes error details

3. Retry Logic: Should MSR handle retries or leave it to implementers?

4. Timing: Should we check once at start or before each operation?

5. Connection Pooling: How does this interact with connection pools?

   • One check for the pool?
   • Check each connection?

Recommended Approach

Phase 1 (v0.4.0): Non-breaking optional method

export interface IDB {
    [key: string]: unknown;
    checkConnection?(): Promise<boolean>;
}

• Add to MigrationScriptExecutor before major operations
• Log warning if not implemented
• Document as recommended best practice

Phase 2 (v1.0.0): Make required with proper migration guide

export interface IDB {
    [key: string]: unknown;
    checkConnection(): Promise<boolean>;
}

• Breaking change for major version
• Update all documentation and examples
• Provide migration guide for existing users

Related Work

• Issue Add support for SQL migration files (Hybrid TypeScript/SQL handler) #58: SQL Migration Files Support (uses ISqlDB interface pattern)
• Connection validation aligns with fail-fast philosophy
• Similar pattern used by Flyway, Liquibase, and golang-migrate

Implementation Checklist

• Decide on interface design (optional vs required)
• Add checkConnection() to IDB interface
• Update MigrationScriptExecutor to call connection check
• Add connection check before backup creation
• Add configuration option to skip check (for edge cases)
• Write unit tests for connection check logic
• Write integration tests with mock connection failures
• Update documentation with implementation examples
• Add to migration guide / best practices
• Document in API reference

Benefits

✅ Fail Fast: Detect connection issues before wasting time
✅ Better UX: Clear error messages about connectivity
✅ Safer Operations: Don't start backups with invalid connections
✅ Implementation Flexibility: Each DBMS can implement optimally
✅ Debuggability: Separate connection issues from migration issues

[vlavrynovych]

✅ Implementation complete and tested.

Summary:

• Added required checkConnection(): Promise<boolean> to IDB interface
• Connection check called before up(), down(), validate() operations
• Fail-fast behavior with clear error messages
• 100% test coverage (701 tests passing)

Breaking Change:
All IDB implementations must now implement checkConnection() method.

Implementation:

• IDB interface updated with required checkConnection() method
• MigrationScriptExecutor checks connection before all major operations
• 11 new integration tests covering all scenarios
• Updated all test mocks to implement checkConnection()

Error Handling:

• Clear error message: "Database connection check failed. Cannot proceed with migration operations."
• Throws immediately if connection fails (fail-fast)
• Prevents wasted time on backup creation, schema initialization, etc.

Database Examples:

• PostgreSQL: SELECT 1 query
• MySQL: ping() method
• MongoDB: admin().ping()
• SQLite/In-memory: Always return true

Key Design:

• Required method (not optional) - enforces best practices
• Called before operations (not wrapped in hooks) - it's a prerequisite
• Returns boolean - should not throw, return false instead
• Fail-fast - prevents wasted resources

Ready for v0.4.0 release.
Documentation tracked in #89.

[vlavrynovych]

✅ Feature complete and merged.

Summary:

• Required checkConnection() method added to IDB interface
• Connection validated before up(), down(), validate() operations
• Fail-fast with clear error messages
• 100% test coverage (701 tests passing)

Breaking Change:
All IDB implementations must now implement checkConnection() method.

Benefits:

• Detects connection issues immediately
• Prevents wasted time and resources
• Clear error messages for debugging
• Database-agnostic implementation

Ready for v0.4.0 release.
Documentation tracked in #89.