Ktor + Exposed: Coordinating Schema Migrations Across Stateless Instances

Recommended Coordination Pattern

Because SchemaUtils.createMissingTablesAndColumns is not designed for distributed coordination and lacks a versioning mechanism, you must implement an external synchronization primitive to prevent concurrent DDL execution. The most reliable pattern for stateless instances is a distributed lock.

Implementation Strategy

• Database-Level Locking: Use a dedicated migration_lock table. The first instance to successfully update a locked_at timestamp or insert a row gains the right to run SchemaUtils. Other instances should poll this lock or wait until the lock is released before proceeding to start the Ktor server.
• External Lock: Use Redis (via Redisson or similar) to acquire a global lock before invoking the migration block.

Flyway Integration and Baselines

Establishing a Flyway baseline from an existing Exposed schema cannot be done automatically within the Exposed DSL, as Exposed does not export its internal state to a versioned format. To transition without manual SQL extraction:

1. Use a database dump tool (e.g., mysqldump --no-data) to capture the current state of the schema.
2. Save this as the V1__Initial_Schema.sql migration file.
3. Execute flyway baseline on the production database to mark all existing tables as part of version 1, preventing Flyway from attempting to recreate them.

Sequencing and Threading

To prevent blocking the Ktor event loop and ensure data integrity, migrations must be sequenced as a blocking prerequisite to the server startup.

Execution Flow

Place the migration logic in the main function or a dedicated Application.module initialization block before the embeddedServer or EngineMain starts listening for requests. Since JDBC is synchronous, wrap the migration call in Dispatchers.IO.

// Example sequencing logic
fun Application.module() {
    launch(Dispatchers.IO) {
        transaction {
            // 1. Acquire Distributed Lock
            // 2. SchemaUtils.createMissingTablesAndColumns(...) 
            // 3. Release Distributed Lock
        }
    }.join() // Block module loading until migration completes

    // Initialize routes and other services
    configureRouting()
}

Assumptions and Constraints

• Implicit Commits: This approach assumes you are aware that MySQL/MariaDB commit DDL implicitly. If SchemaUtils fails halfway, the database is left in a partial state that cannot be rolled back by the transaction.
• Version: Assumes Ktor 2.x/3.x and Exposed 0.40+; behavior may vary in legacy versions.

Diagnostic Detail Needed: Are you utilizing a container orchestrator (like Kubernetes) that supports initContainers? If so, the recommended pattern shifts from application-level locking to moving the migration logic into a separate Init Container, removing the concurrency problem entirely from the Ktor instances.