Invercargill-Sql/plans/phase-2-migration-system.md

Phase 2: Migration System Design

Overview

This document describes the design for the explicit versioned migration system in Invercargill-Sql. Migrations are defined as classes with up() and down() methods using a fluent MigrationBuilder API.

Design Goals

• Explicit versioning: Each migration has a version number and is tracked in the database
• Rollback support: Full up() and down() methods for apply/revert
• Fluent schema builder: Abstract type system that translates to database-specific DDL
• Provider-agnostic: Same migration code works across SQLite, PostgreSQL, etc.

Architecture Diagram

classDiagram
    direction TB
    
    namespace Migration_Core {
        class MigrationRunner {
            -Connection _connection
            -SqlDialect _dialect
            -Vector~Migration~ _migrations
            +register~T~() void
            +migrate_to_latest() void
            +migrate_to(int version) void
            +rollback(int steps) void
            +get_applied_versions() Enumerable~int~
            +get_pending_migrations() Enumerable~Migration~
        }
        
        class Migration {
            <<abstract>>
            +int version*
            +string name*
            +up(MigrationBuilder b)*
            +down(MigrationBuilder b)*
        }
        
        class MigrationBuilder {
            -SqlDialect _dialect
            -Vector~SchemaOperation~ _operations
            +create_table(string name, TableBuilder config) MigrationBuilder
            +drop_table(string name) MigrationBuilder
            +alter_table(string name, AlterTableBuilder config) MigrationBuilder
            +create_index(string name, string table, string[] columns) MigrationBuilder
            +drop_index(string name) MigrationBuilder
            +execute_sql(string sql) MigrationBuilder
            +get_operations() Vector~SchemaOperation~
        }
        
        class TableBuilder {
            -string _table_name
            -SqlDialect _dialect
            -Vector~ColumnDefinition~ _columns
            -Vector~TableConstraint~ _constraints
            +column~T~(string name) ColumnBuilder
            +primary_key(string[] columns) TableBuilder
            +unique(string name, string[] columns) TableBuilder
            +foreign_key(string name, string[] columns, string ref_table, string[] ref_columns) TableBuilder
        }
        
        class AlterTableBuilder {
            -string _table_name
            -SqlDialect _dialect
            -Vector~AlterOperation~ _operations
            +add_column~T~(string name) ColumnBuilder
            +drop_column(string name) AlterTableBuilder
            +rename_column(string old_name, string new_name) AlterTableBuilder
            +alter_column(string name) ColumnAlterBuilder
            +add_constraint(string constraint) AlterTableBuilder
            +drop_constraint(string name) AlterTableBuilder
        }
        
        class ColumnBuilder {
            -TableBuilder _parent
            -ColumnDefinition _column
            -SqlDialect _dialect
            +primary_key() TableBuilder
            +not_null() TableBuilder
            +unique() TableBuilder
            +default_value~T~(T value) TableBuilder
            +default_now() TableBuilder
            +auto_increment() TableBuilder
            +references(string table, string column) TableBuilder
        }
    }
    
    namespace Schema_Operations {
        class SchemaOperation {
            <<interface>>
            +to_sql(SqlDialect dialect) string
        }
        
        class CreateTableOperation {
            +string table_name
            +Vector~ColumnDefinition~ columns
            +Vector~TableConstraint~ constraints
            +to_sql(SqlDialect dialect) string
        }
        
        class DropTableOperation {
            +string table_name
            +to_sql(SqlDialect dialect) string
        }
        
        class AddColumnOperation {
            +string table_name
            +ColumnDefinition column
            +to_sql(SqlDialect dialect) string
        }
        
        class DropColumnOperation {
            +string table_name
            +string column_name
            +to_sql(SqlDialect dialect) string
        }
        
        class CreateIndexOperation {
            +string index_name
            +string table_name
            +string[] columns
            +bool is_unique
            +to_sql(SqlDialect dialect) string
        }
    }
    
    namespace Dialect {
        class SqlDialect {
            <<interface>>
            +translate_type(ColumnType type) string
            +create_table_sql(CreateTableOperation op) string
            +drop_table_sql(DropTableOperation op) string
            +add_column_sql(AddColumnOperation op) string
            +drop_column_sql(DropColumnOperation op) string
            +create_index_sql(CreateIndexOperation op) string
            +drop_index_sql(DropIndexOperation op) string
        }
        
        class SqliteDialect {
            +translate_type(ColumnType type) string
            +create_table_sql(CreateTableOperation op) string
            +drop_table_sql(DropTableOperation op) string
            +add_column_sql(AddColumnOperation op) string
            +drop_column_sql(DropColumnOperation op) string
            +create_index_sql(CreateIndexOperation op) string
            +drop_index_sql(DropIndexOperation op) string
        }
    }
    
    MigrationRunner --> Migration : manages
    MigrationRunner --> SqlDialect : uses
    Migration --> MigrationBuilder : uses
    MigrationBuilder --> TableBuilder : creates
    MigrationBuilder --> AlterTableBuilder : creates
    TableBuilder --> ColumnBuilder : creates
    AlterTableBuilder --> ColumnBuilder : creates
    ColumnBuilder --> TableBuilder : returns to parent
    ColumnBuilder --> AlterTableBuilder : returns to parent
    SchemaOperation <|.. CreateTableOperation : implements
    SchemaOperation <|.. DropTableOperation : implements
    SchemaOperation <|.. AddColumnOperation : implements
    SqlDialect <|.. SqliteDialect : implements

Migration Tracking Table

Migrations are tracked in a dedicated table:

CREATE TABLE __migrations (
    version INTEGER PRIMARY KEY,
    name TEXT NOT NULL,
    applied_at INTEGER NOT NULL  -- Unix epoch timestamp
);

Migration Class Structure

public abstract class Migration : Object {
    /** Unique version number for this migration */
    public abstract int version { get; }
    
    /** Human-readable name for this migration */
    public abstract string name { get; }
    
    /** Apply the migration */
    public abstract void up(MigrationBuilder b) throws SqlError;
    
    /** Revert the migration */
    public abstract void down(MigrationBuilder b) throws SqlError;
}

Example Migrations

Creating a Table

public class V001_CreateUsers : Migration {
    public override int version { get { return 1; } }
    public override string name { get { return "CreateUsers"; } }
    
    public override void up(MigrationBuilder b) {
        b.create_table("users", t => {
            t.column<int64>("id")
                .primary_key()
                .auto_increment();
            t.column<string>("name")
                .not_null();
            t.column<string>("email")
                .unique();
            t.column<bool>("is_active")
                .default_value(true);
            t.column<DateTime>("created_at")
                .default_now();
        });
        
        b.create_index("idx_users_email", "users", {"email"});
    }
    
    public override void down(MigrationBuilder b) {
        b.drop_index("idx_users_email");
        b.drop_table("users");
    }
}

Adding Columns

public class V002_AddUserStatus : Migration {
    public override int version { get { return 2; } }
    public override string name { get { return "AddUserStatus"; } }
    
    public override void up(MigrationBuilder b) {
        b.alter_table("users", t => {
            t.add_column<string>("status")
                .default_value("active");
            t.add_column<int>("login_count")
                .default_value(0);
        });
    }
    
    public override void down(MigrationBuilder b) {
        b.alter_table("users", t => {
            t.drop_column("status");
            t.drop_column("login_count");
        });
    }
}

Creating Relationships

public class V003_CreateOrders : Migration {
    public override int version { get { return 3; } }
    public override string name { get { return "CreateOrders"; } }
    
    public override void up(MigrationBuilder b) {
        b.create_table("orders", t => {
            t.column<int64>("id")
                .primary_key()
                .auto_increment();
            t.column<int64>("user_id")
                .not_null()
                .references("users", "id");
            t.column<double>("total")
                .not_null();
            t.column<DateTime>("order_date")
                .default_now();
        });
        
        b.create_index("idx_orders_user_id", "orders", {"user_id"});
    }
    
    public override void down(MigrationBuilder b) {
        b.drop_index("idx_orders_user_id");
        b.drop_table("orders");
    }
}

Complex Migration with Data Transform

public class V004_SplitUserName : Migration {
    public override int version { get { return 4; } }
    public override string name { get { return "SplitUserName"; } }
    
    public override void up(MigrationBuilder b) throws SqlError {
        // Add new columns
        b.alter_table("users", t => {
            t.add_column<string>("first_name");
            t.add_column<string>("last_name");
        });
        
        // Execute custom SQL for data migration
        b.execute_sql(@"
            UPDATE users SET 
                first_name = substr(name, 1, instr(name, ' ') - 1),
                last_name = substr(name, instr(name, ' ') + 1)
            WHERE name LIKE '% %'
        ");
        
        // Make columns required after migration
        // Note: SQLite doesn't support NOT NULL on ALTER COLUMN
        // This would work in PostgreSQL
    }
    
    public override void down(MigrationBuilder b) throws SqlError {
        // Restore name from split fields
        b.execute_sql(@"
            UPDATE users SET name = first_name || ' ' || last_name
        ");
        
        // Drop the columns
        b.alter_table("users", t => {
            t.drop_column("first_name");
            t.drop_column("last_name");
        });
    }
}

MigrationRunner API

public class MigrationRunner : Object {
    /**
     * Creates a new migration runner.
     * @param connection The database connection
     * @param dialect The SQL dialect for generating DDL
     */
    public MigrationRunner(Connection connection, SqlDialect dialect);
    
    /**
     * Registers a migration class.
     */
    public void register<T>() where T : Migration;
    
    /**
     * Registers a migration instance.
     */
    public void register_migration(Migration migration);
    
    /**
     * Creates the migrations tracking table if it doesn't exist.
     */
    public void ensure_migrations_table() throws SqlError;
    
    /**
     * Gets all applied migration versions.
     */
    public Enumerable<int> get_applied_versions() throws SqlError;
    
    /**
     * Gets all pending migrations that haven't been applied.
     */
    public Enumerable<Migration> get_pending_migrations() throws SqlError;
    
    /**
     * Gets the current schema version.
     */
    public int get_current_version() throws SqlError;
    
    /**
     * Applies all pending migrations.
     */
    public void migrate_to_latest() throws SqlError;
    
    /**
     * Migrates to a specific version.
     * If version > current, applies migrations up to that version.
     * If version < current, rolls back migrations down to that version.
     */
    public void migrate_to(int target_version) throws SqlError;
    
    /**
     * Rolls back the last N migrations.
     */
    public void rollback(int steps = 1) throws SqlError;
    
    /**
     * Rolls back all migrations.
     */
    public void rollback_all() throws SqlError;
    
    /**
     * Async variants.
     */
    public async virtual void migrate_to_latest_async() throws SqlError;
    public async virtual void migrate_to_async(int target_version) throws SqlError;
    public async virtual void rollback_async(int steps = 1) throws SqlError;
}

Usage Example

using InvercargillSql;
using InvercargillSql.Migrations;

void main() {
    try {
        // Create connection
        var conn = ConnectionFactory.create_and_open("sqlite:///myapp.db");
        
        // Get dialect for this connection
        var dialect = new SqliteDialect();
        
        // Create migration runner
        var runner = new MigrationRunner(conn, dialect);
        
        // Register migrations in order
        runner.register<V001_CreateUsers>();
        runner.register<V002_AddUserStatus>();
        runner.register<V003_CreateOrders>();
        runner.register<V004_SplitUserName>();
        
        // Check pending migrations
        var pending = runner.get_pending_migrations();
        print("Pending migrations: %u\n", pending.count());
        
        // Apply all pending migrations
        runner.migrate_to_latest();
        print("Migrated to version: %d\n", runner.get_current_version());
        
        // Or migrate to specific version
        // runner.migrate_to(2);
        
        // Or rollback
        // runner.rollback(1);  // Roll back last migration
        
        conn.close();
    } catch (SqlError e) {
        stderr.printf("Migration error: %s\n", e.message);
    }
}

SQL Dialect Extensions for Migrations

The SqlDialect interface needs additional methods for migration support:

public interface SqlDialect : Object {
    // ... existing methods ...
    
    // Migration-specific methods
    public abstract string create_table_sql(CreateTableOperation op);
    public abstract string drop_table_sql(DropTableOperation op);
    public abstract string add_column_sql(AddColumnOperation op);
    public abstract string drop_column_sql(DropColumnOperation op);
    public abstract string rename_column_sql(RenameColumnOperation op);
    public abstract string alter_column_sql(AlterColumnOperation op);
    public abstract string create_index_sql(CreateIndexOperation op);
    public abstract string drop_index_sql(DropIndexOperation op);
    public abstract string add_constraint_sql(AddConstraintOperation op);
    public abstract string drop_constraint_sql(DropConstraintOperation op);
    
    // Migration table creation
    public abstract string create_migrations_table_sql();
}

SQLite Dialect Implementation Notes

SQLite has limitations that affect migrations:

1. No native ALTER COLUMN: Cannot modify column type or constraints
2. Limited DROP COLUMN: Only in recent SQLite versions (3.35.0+)
3. No native DATETIME: Store as INTEGER (Unix epoch) or TEXT (ISO8601)

Workarounds:

• For complex column changes: Create new table, copy data, drop old, rename
• For datetime: Use INTEGER and translate in the dialect

File Structure

src/
├── migrations/
│   ├── migration.vala               # Abstract Migration class
│   ├── migration-runner.vala        # MigrationRunner class
│   ├── migration-builder.vala       # Fluent builder for schema operations
│   ├── table-builder.vala           # Builder for CREATE TABLE
│   ├── alter-table-builder.vala     # Builder for ALTER TABLE
│   ├── column-builder.vala          # Builder for column definitions
│   └── schema-operations.vala       # Schema operation classes
└── dialects/
    └── sqlite-dialect.vala          # SQLite DDL generation (extended)

Implementation Checklist

• Create SchemaOperation interface and operation classes
• Create CreateTableOperation, DropTableOperation
• Create AddColumnOperation, DropColumnOperation
• Create CreateIndexOperation, DropIndexOperation
• Create RenameColumnOperation, AlterColumnOperation
• Implement ColumnBuilder with migration-specific methods
• Implement TableBuilder for CREATE TABLE
• Implement AlterTableBuilder for ALTER TABLE
• Implement MigrationBuilder as facade
• Create abstract Migration class
• Implement MigrationRunner with version tracking
• Add migration table creation SQL to SqlDialect
• Extend SqliteDialect with all DDL methods
• Handle SQLite limitations (no ALTER COLUMN, etc.)
• Add async variants for all migration operations
• Write unit tests for schema operations
• Write integration tests for migration runner
• Test rollback scenarios

Integration with ORM

The migration system integrates with the ORM through shared components:

// ORM and migrations share EntityMapper metadata
var user_mapper = EntityMapper.build_for<User>(b => b
    .table("users")
    .column<int64>("id", u => u.id, (u, v) => u.id = v)
        .primary_key()
    .column<string>("name", u => u.name, (u, v) => u.name = v)
        .required());

// ORM uses mapper for queries
orm.register_entity(user_mapper);

// Migrations can use mapper metadata (future enhancement)
// b.create_table_from_mapper(user_mapper);

Future Enhancements

1. Auto-generate migrations from EntityMapper changes - Compare entity versions
2. Migration templates - Generate migration class stubs
3. Dry run mode - Show SQL without executing
4. Migration scripts - Export migrations as .sql files
5. Seed data - Built-in support for initial data