Database migrations with Exposed

While working on a project using Ktor and Exposed, I quickly ran into the need for simple database migrations creation - ideally via a single command line.

Exposed’s example is a great starting point, I just had to add a few sprinkles to make it work for my way.

Setup
#

I started with a script that connects to the database, gets the migration name from user, and generates the migration using Exposed’s MigrationUtils.generateMigrationScript utility.

This Kotlin script, called generate-migrations.kt, is placed it under src/main/kotlin, next to actual app sources. Database credentials needs to be provided via ENV variables.

fun getConnection(): Database {
    val url = System.getenv("APP_DB_URL") ?: error("Missing env variable: APP_DB_URL")
    val user = System.getenv("APP_DB_USER") ?: error("Missing env variable: APP_DB_USER")
    val password =
        System.getenv("APP_DB_PASSWORD") ?: error("Missing env variable: APP_DB_PASSWORD")

    return Database.connect(
        url = url,
        driver = "org.postgresql.Driver", // we're connecting to postgresql
        user = user,
        password = password,
    )
}

fun getMigrationName(): String {
    return System.getenv("MIGRATION_NAME") ?: error("Missing env variable: MIGRATION_NAME")
}

@OptIn(ExperimentalDatabaseMigrationApi::class)
fun main() {
    val db = getConnection()
    val name = getMigrationName()

    transaction(db) {
        MigrationUtils.generateMigrationScript(
            MyTable,
            OtherTable, // list your tables here
            scriptDirectory = MIGRATIONS_DIRECTORY, // to be defined
            scriptName = name,
            withLogs = true,
        )
    }
}

Next, I added a Gradle task that runs the script and accepts a migration name, following the vX__name convention:

tasks.register<JavaExec>("generateMigrations") {
    group = "database"
    description = "Generate migration scripts"
    classpath = sourceSets.main.get().runtimeClasspath
    mainClass = "Generate_migrationsKt"

    doFirst {
        environment["MIGRATION_NAME"] =
            project.properties["migrationName"]
                ?: error("missing migration name. Pass via -PmigrationName=vX__your_name")
    }
}

Last, but not least, a place where migrations will end up:

const val MIGRATIONS_DIRECTORY = "src/main/resources/migrations"

Now it’s as easy as calling the task to generate the migrations. It can be used to create a very first one actualy:

./gradlew :app:generateMigrations -PmigrationName="V1__initial"

Conclusion
#

Now the script needs to be run whenever you do a change in database - with a different name of course!

Setup #

Conclusion #

Setup
#

Conclusion
#