What's the recommended way to do database migrations with Ktor + Exposed (Kotlin)?

Question

The Ktor or Exposed frameworks do not have any built-in support for database migrations. What's the recommended way to do this?

Answer 1

If you are using Ktor with Gradle I would recommend using Flyway programatically inside the entry point of your Application. This way it could easily be a part of your Continuous Delivery pipeline. You can see the Flyway docs using API here: https://flywaydb.org/documentation/api/

What I essentially do though is add the dependency (using Kotlin DSL):

    implementation("org.flywaydb:flyway-core:6.5.2")

And then all you need to do is create an instance of Flyway and call migrate when you load your module:

fun Application.module() {

  Flyway.configure().dataSource(/*config to your DB*/).load().migrate()

  //the rest of your application
  routing {

  }
}

You could of course extract the creation of Flyway to your DI tool (e.g. Koin) and add some logging to show progress.

This way your DB will be migrated (if necessary) every time just before your app is started.

As for writing up the actual migration the official docs are very helpful. What you essentially need to do is:

1. Make sure you have the required directory for migration files - src/main/resources/db/migration by default ).
2. Write plain SQL in separate migration files in the above directory. The filenames need to stick to the convention too (by default: start with capital V then a number, which you will increment for each new migration, double underscore - this tricked me at the beginning ;) - and a snake_case description, e.g. V1__Create_person_table.sql)
3. Run the app and observe magic :D

Answer 2

Database tables are migrated at deployment via Flyway and scripts can be added in db/migrations folder to add new tables or execute queries like inserting data etc on server startup. (https://github.com/arun0009/kotlin-ktor-exposed-sample-api)

Here's how Flyway works: https://flywaydb.org/getstarted/how

1. Download, install, and configure Flyway (See https://flywaydb.org/getstarted/firststeps/commandline)
2. Point it to a database, which will be the location of flyway_schema_history
3. Write your migration files for create table or insert data following their naming convention: V<version number>__<migration description> and run with flyway migrate
4. Write your repeatable migration files for create view following their naming convention: R__<migration description> and run with flyway migrate
5. Write your undo migration files for drop table or delete data following their naming convention: V<version number>__<migration description> and run with flyway migrate
6. Check your migration status with flyway info and commit your files if you're happy
7. Make any necessary modifications are rerun migration. Repeat and commit.