Add support for SQL migration files (Hybrid TypeScript/SQL handler)

[vlavrynovych]

Overview

Add support for SQL migration files alongside existing TypeScript migrations, similar to Flyway. This enables users to choose the best tool for each migration:

• SQL files for simple DDL operations (CREATE TABLE, ALTER TABLE, etc.)
• TypeScript for complex data migrations and business logic

Proposed Solution

Implement a hybrid handler that detects file type and executes appropriately:

// Example migration structure
migrations/
├── V202501230001_create_tables.sql      # SQL for DDL
├── V202501230002_seed_data.ts           # TypeScript for complex logic
├── V202501230003_alter_columns.sql      # SQL for schema changes
└── V202501230004_migrate_data.ts        # TypeScript for data transformations

Features

Basic SQL Support

• Execute .sql files directly
• Parse and execute multiple statements (split by semicolons)
• Support standard SQL DDL/DML operations

Up/Down Migrations (Optional)

• Support -- @UP and -- @DOWN sections in SQL files
• Store down migrations for rollback capability
• Parse SQL file sections

Example:

-- V202501230001_create_users.sql

-- @UP
CREATE TABLE users (
  id SERIAL PRIMARY KEY,
  email VARCHAR(255) UNIQUE NOT NULL
);

-- @DOWN
DROP TABLE users;

Hybrid Handler

• Automatically detect file type (.sql vs .ts)
• Execute SQL files directly
• Load and execute TypeScript migrations
• Consistent error handling for both types

Implementation

export class HybridMigrationHandler implements IMigrationScriptHandler {
  async handle(script: MigrationScript): Promise<string> {
    if (script.filepath.endsWith('.sql')) {
      return this.handleSQL(script);
    } else if (script.filepath.endsWith('.ts')) {
      return this.handleTypeScript(script);
    }
    throw new Error(`Unsupported file type: ${script.filepath}`);
  }

  private async handleSQL(script: MigrationScript): Promise<string> {
    const sql = fs.readFileSync(script.filepath, 'utf8');
    
    // Parse and execute SQL statements
    const statements = this.parseSQL(sql);
    for (const statement of statements) {
      await this.db.query(statement);
    }
    
    return `Executed ${statements.length} SQL statements`;
  }

  private async handleTypeScript(script: MigrationScript): Promise<string> {
    await script.init(); // Load TS migration
    return await script.script.up(this.db);
  }
}

Configuration

Update Config to support SQL files:

export class Config {
  filePattern: RegExp = /^V(\d+)_(.+)\.(ts|sql)$/; // Support both .ts and .sql
  sqlMigrationSeparator?: string = '_';
  supportDownMigrations?: boolean = false;
}

Benefits

• Flyway familiarity: Developers coming from Flyway can use same patterns
• Flexibility: Choose the right tool (SQL vs TypeScript) for each migration
• Simplicity: Simple schema changes don't need TypeScript boilerplate
• Power: Complex data migrations still use TypeScript's full capabilities
• Database-agnostic: Works with any database (PostgreSQL, MySQL, MongoDB via SQL-like DSL)

Acceptance Criteria

• Handler detects and executes .sql files
• Handler supports existing .ts migrations
• SQL statements are parsed correctly (handle semicolons, comments)
• Tests for SQL migration execution
• Tests for hybrid (mixed SQL/TS) migrations
• Documentation with examples
• Support for UP/DOWN migrations (optional enhancement)
• Error handling for invalid SQL
• 100% test coverage maintained

Related Issues

• Make backup/restore optional to support down() migrations #50 - Make backup/restore optional to support down() migrations (UP/DOWN migrations in SQL files complement down() methods in TypeScript)
• Complements database-specific implementations (PostgreSQL, MySQL, etc.)
• Works with existing backup/restore functionality
• Compatible with planned CLI commands

Notes

• This should be a separate optional handler, not modify existing TypeScript handler
• Users can create their own SQL handlers if needed
• Consider creating example implementations in documentation
• SQL file UP/DOWN sections work well with optional backup/restore (Make backup/restore optional to support down() migrations #50)

[vlavrynovych]

Related to #50

This issue intersects with #50 (Make backup/restore optional to support down() migrations):

• Make backup/restore optional to support down() migrations #50 adds support for down() methods in TypeScript migrations
• Add support for SQL migration files (Hybrid TypeScript/SQL handler) #58 adds support for UP/DOWN sections in SQL files

Both features enable rollback functionality:

• TypeScript migrations: Use down() method
• SQL migrations: Use -- @DOWN section

The implementation should coordinate with #50's rollback strategy configuration to support:

• Backup/restore strategy
• Down migration strategy (TypeScript down() + SQL @DOWN)
• Both strategies combined
• No safety net

When implementing this, ensure SQL file UP/DOWN sections integrate with the rollbackStrategy configuration from #50.

[vlavrynovych]

Implementation Plan: SQL Migration Files Support (#58)

Overview

Add support for SQL migration files (.up.sql / .down.sql) alongside existing TypeScript migrations using a pluggable loader registry pattern. This enables users to choose between SQL (simple schema changes) and TypeScript (complex logic) for each migration.

User Decisions

• Rollback Format: Separate .up.sql and .down.sql files (golang-migrate style)
• SQL Execution: Execute entire file content as one statement (pass to db.query())
• Architecture: IMigrationScriptLoader registry pattern (supports future .js and other formats)

Key Design Principles

1. Zero Breaking Changes: All existing TypeScript migrations work unchanged
2. Extensibility: Loader registry pattern supports future file types (.js, .yaml, etc.)
3. Integration: Works with existing RollbackStrategy configuration
4. Type Safety: Strong typing with clear error messages

Architecture

Core Interfaces

IMigrationScriptLoader - Base loader interface

interface IMigrationScriptLoader {
    canHandle(filePath: string): boolean;
    load(script: MigrationScript): Promise<IRunnableScript>;
    getName(): string;
}

ILoaderRegistry - Loader registry

interface ILoaderRegistry {
    register(loader: IMigrationScriptLoader): void;
    findLoader(filePath: string): IMigrationScriptLoader;
    getLoaders(): IMigrationScriptLoader[];
}

Loader Implementations

TypeScriptLoader - Extracts existing logic from Utils.parseRunnable()

• Handles .ts and .js files via dynamic import
• Maintains backward compatibility

SqlLoader - New loader for SQL files

• Handles .up.sql files (discovers matching .down.sql)
• Reads file content, creates SqlScript wrapper
• SqlScript implements IRunnableScript:
  • up(): executes .up.sql content via db.query()
  • down(): executes .down.sql content via db.query() (if exists)

LoaderRegistry - Default implementation

• Singleton with createDefault() factory
• Registers TypeScript and SQL loaders by default
• Users can register custom loaders via Config.loaderRegistry

File Naming Convention

TypeScript migrations (unchanged):

V202501220100_create_users.ts

SQL migrations (new):

V202501220100_create_users.up.sql    # Required
V202501220100_create_users.down.sql  # Optional (depends on rollbackStrategy)

Mixed folders supported:

migrations/
├── V202501220100_initial.ts              # TypeScript
├── V202501220200_create_users.up.sql     # SQL
├── V202501220200_create_users.down.sql   # SQL rollback
└── V202501220300_migrate_data.ts         # TypeScript

Integration Points

1. MigrationScript.init()

Change: Accept LoaderRegistry parameter

// Before
async init(): Promise<void> {
    this.script = await Utils.parseRunnable(this);
}

// After
async init(registry: ILoaderRegistry): Promise<void> {
    const loader = registry.findLoader(this.filepath);
    this.script = await loader.load(this);
}

2. Config Class

Add: Optional loader registry property

class Config {
    // ... existing properties ...
    loaderRegistry?: ILoaderRegistry;
}

3. MigrationService File Discovery

Change: Update file pattern to match both .ts and .up.sql

// Support multiple patterns
filePatterns: RegExp[] = [
    /^V(\d{12})_.*\.ts$/,
    /^V(\d{12})_.*\.up\.sql$/
];

4. MigrationScanner Script Initialization

Change: Pass loader registry when initializing scripts

const registry = this.config.loaderRegistry || LoaderRegistry.createDefault();
await Promise.all(all.map(script => script.init(registry)));

5. MigrationValidationService

Add: SQL-specific validation

• Check .up.sql file exists and is readable
• Check .down.sql exists if rollbackStrategy requires it
• Appropriate error/warning based on RollbackStrategy

Rollback Strategy Integration

SQL migrations integrate with existing RollbackStrategy from #50:

Strategy	TypeScript Behavior	SQL Behavior
BACKUP	Use backup/restore	Use backup/restore
DOWN	Use down() method	Use .down.sql file
BOTH	Try down(), fallback to backup	Try .down.sql, fallback to backup
NONE	No rollback	No rollback

Validation Rules:

• RollbackStrategy.DOWN: Error if .down.sql missing
• RollbackStrategy.BOTH: Warning if .down.sql missing
• RollbackStrategy.BACKUP or NONE: No check for .down.sql

Implementation Steps

Phase 1: Core Infrastructure

1. Create src/interface/loader/IMigrationScriptLoader.ts
2. Create src/interface/loader/ILoaderRegistry.ts
3. Create src/interface/loader/index.ts
4. Export from main interface index

Files created: 3 new interface files

Phase 2: Loader Implementations

1. Create src/loader/TypeScriptLoader.ts (extract from Utils)
2. Create src/loader/SqlLoader.ts with SqlScript class
3. Create src/loader/LoaderRegistry.ts
4. Create src/loader/index.ts
5. Export from main index

Files created: 4 new implementation files

Phase 3: Integration

1. Update Config.ts: Add loaderRegistry property
2. Update MigrationScript.ts: Change init() signature
3. Update MigrationService.ts: Support multiple file patterns
4. Update MigrationScanner.ts: Pass registry to init()
5. Deprecate Utils.parseRunnable() (keep functional)

Files modified: 5 core files

Phase 4: Validation

1. Update MigrationValidationService.ts: Add SQL validation logic

Files modified: 1 validation file

Phase 5: Testing

1. Unit tests for loaders (TypeScript, SQL, Registry)
2. Integration tests (SQL execution, mixed migrations, rollback)
3. Test fixtures (sample SQL migrations)

Files created: 3+ test files, test fixtures directory

Phase 6: Documentation

1. Update Getting Started guide
2. Create SQL migrations guide
3. Update API reference
4. Update rollback documentation

Files modified: 4+ documentation files

Critical Files

Must Modify (5 files):

1. src/model/MigrationScript.ts - Update init() signature
2. src/service/MigrationScanner.ts - Pass registry to scripts
3. src/service/MigrationService.ts - Update file pattern matching
4. src/model/Config.ts - Add loaderRegistry property
5. src/loader/SqlLoader.ts - Core SQL execution logic (new file)

Should Read Before Implementation (3 files):

1. src/service/Utils.ts - parseRunnable() logic to extract
2. src/service/RollbackService.ts - Understand rollback flow
3. src/service/MigrationValidationService.ts - Validation patterns

Error Handling

SQL Execution Errors:

throw new Error(
    `SQL migration failed: ${scriptName}\n` +
    `Error: ${error.message}\n` +
    `SQL Preview: ${sql.substring(0, 200)}...`
);

Missing Down File:

throw new Error(
    `No down() SQL file found: ${downPath}\n` +
    `Required by rollbackStrategy: ${config.rollbackStrategy}`
);

Database Compatibility:

if (typeof db.query !== 'function') {
    throw new Error(
        `SQL migrations require db.query() method.\n` +
        `Handler: ${handler.getName()}\n` +
        `Use TypeScript migrations or implement query() method.`
    );
}

Backward Compatibility

No Breaking Changes:

• Existing API unchanged
• Default registry handles TypeScript files
• Config properties all optional with defaults
• Utils.parseRunnable() deprecated but functional

Migration Path:

1. Day 1: No changes needed, existing migrations work
2. Day 2: Create .up.sql file, automatically discovered
3. Day 3: Mix TypeScript and SQL freely

Success Criteria

• ✓ Users can create .up.sql and .down.sql files
• ✓ SQL files discovered and executed correctly
• ✓ Mixed TypeScript/SQL migrations work
• ✓ Rollback works with .down.sql files
• ✓ Checksums work for SQL files
• ✓ Validation works for SQL files
• ✓ Clear error messages for all failure modes
• ✓ 100% test coverage maintained
• ✓ Zero breaking changes
• ✓ Documentation complete

Testing Strategy

Unit Tests:

• TypeScriptLoader: canHandle, load, error cases
• SqlLoader: canHandle, load, up/down execution, missing down file
• LoaderRegistry: register, findLoader, createDefault
• SqlScript: up() execution, down() execution, db.query validation

Integration Tests:

• Execute SQL migration successfully
• Rollback with down.sql
• Mixed TypeScript/SQL execution order
• SQL syntax error handling
• Checksum calculation for SQL
• Validation with missing down.sql

Test Coverage: Maintain 100% across all new code

Notes

• LoaderRegistry pattern enables future .js migration support (Simplify migration script interface to use up/down methods #81's simplified interface)
• SQL execution delegates to database-specific db.query() implementation
• Only .up.sql files discovered; .down.sql found by SqlLoader when needed
• Entire file content passed to db.query() - database handles multi-statement execution
• Type guard ensures db.query exists before SQL execution

[vlavrynovych]

Implemented SQL migration files support with ISqlDB interface.

All tasks completed:

• ✅ SqlLoader for .up.sql/.down.sql files
• ✅ ISqlDB interface for explicit database contract
• ✅ Loader architecture (IMigrationScriptLoader, ILoaderRegistry)
• ✅ TypeScriptLoader and SqlLoader implementations
• ✅ Removed deprecated filePattern property
• ✅ 631 tests passing with 100% coverage

Documentation will be handled in #89.