All jobs

Add Flyway Migrations to a Ktor App

Yes. An agent replaces a Ktor service's CREATE TABLE at startup with versioned Flyway migrations that run before the routes, adds a Postgres test, and opens a PR.

Run this on RushOpens the console with this prompt ready to send, in a Project that has your repository.

What the agent does

The agent reads how the schema is created today. In a service like the postgres sample in ktorio/ktor-samples, build.gradle.kts applies kotlin("jvm") 2.4.20, the io.ktor.plugin 3.6.0 and the serialization plugin, depends on ktor-server-core-jvm, ktor-server-netty-jvm, content negotiation with kotlinx JSON, ktor-server-config-yaml, logback-classic and org.postgresql:postgresql 42.7.8, lists ktor-server-test-host-jvm and kotlin-test-junit for tests, and sets jvmToolchain(17). PostgresApplication.kt calls configureSerialization() and then configureRouting(dbConnection = connectToPostgres(embedded = false)); connectToPostgres reads postgres.yaml for the database name, user and password and opens a single JDBC Connection to localhost:5432. service/ArticleService.kt holds the SQL as constants and, in its init block, runs CREATE TABLE IF NOT EXISTS ARTICLES (ID SERIAL PRIMARY KEY, TITLE VARCHAR(255), BODY VARCHAR(1024)) every time the service is constructed. That is the schema management: an idempotent DDL at startup, with no way to add a column or an index later. docker-compose.yaml starts postgres:18.0-alpine with the same credentials, and there is no src/test directory.

It adds Flyway. In build.gradle.kts it declares org.flywaydb:flyway-core and org.flywaydb:flyway-database-postgresql at the same version, placed under a flyway_version property in gradle.properties beside kotlin_version and logback_version. It creates src/main/resources/db/migration/V1__create_articles.sql with the exact DDL from the init block, so a database that already has the table and a fresh one end up identical, and removes the init block and the CREATE_TABLE_ARTICLES constant from ArticleService. For an existing database that already has articles, the pull request documents the one-time baseline call so Flyway records V1 as applied instead of failing on the existing table.

The migration runs before anything touches the table. A new plugins/Database.kt holds Application.configureDatabase(), which builds a PGSimpleDataSource from the same postgres.yaml values connectToPostgres reads, calls Flyway.configure().dataSource(ds).load().migrate(), logs the number of applied migrations through environment.log, and returns the Connection the routes use. PostgresApplication.kt becomes configureSerialization(), then configureDatabase(), then configureRouting(...) with that connection, so a failed migration stops the server before Netty binds port 8080, which is what an operator wants from a bad migration. The routes in plugins/Routing.kt and the five SQL constants that remain in ArticleService are unchanged.

The agent adds src/test/kotlin/com/example/ApplicationTest.kt with testApplication, which starts the module against a Postgres it brings up from docker-compose.yaml in the sandbox, asserts that flyway_schema_history holds one row with version 1, posts an article to /articles, reads it back by id and checks the 201 and 200 responses, so the test proves both that the migration ran and that the routes still work on the migrated table. It runs ./gradlew clean check --console=plain, the command the repository's build-all.sh uses for every sample, and pastes the test report summary into the pull request.

Start it with one API call

POST https://api.phoenix.so/o/{org}/sessions with this body:

{
  "agent": "claude",
  "input": "Add Flyway migrations to this Ktor service and open a pull request. Read build.gradle.kts, gradle.properties, settings.gradle.kts, docker-compose.yaml, postgres.yaml, src/main/resources/application.yaml, src/main/kotlin/com/example/PostgresApplication.kt, plugins/Routing.kt, plugins/Serialization.kt and service/ArticleService.kt first. Add flyway-core and flyway-database-postgresql under a flyway_version property in gradle.properties. Move the CREATE TABLE statement from ArticleService's init block into src/main/resources/db/migration/V1__create_articles.sql unchanged and delete the init block and its constant. Add plugins/Database.kt with Application.configureDatabase() that builds a PGSimpleDataSource from the postgres.yaml values, runs Flyway migrate, logs the applied count and returns the JDBC Connection, and call it in PostgresApplication.kt before configureRouting. Document the baseline command for a database that already has the table. Add src/test/kotlin/com/example/ApplicationTest.kt using testApplication against the compose Postgres: assert flyway_schema_history has version 1, then POST /articles and GET /articles/{id}. Run ./gradlew clean check --console=plain and paste the summary in the pull request. Do not change the routes or the remaining SQL in ArticleService.",
  "resources": [
    {
      "url": "https://github.com/your-org/your-app"
    }
  ]
}
  1. Get an API key. In the console, open Settings, then API keys, and create a key for your org. Send it in the Authorization header as a Bearer token.
  2. Send the request. POST the body in request.json to https://api.phoenix.so/o/{org}/sessions, with your repository as the resources URL. The run starts in its own cloud sandbox.
  3. Read what came back. Follow the run on its live stream or wait for the run.completed webhook, then read the result and its trajectory.

What comes back

What comes back is a pull request that edits build.gradle.kts, gradle.properties, PostgresApplication.kt and service/ArticleService.kt, and adds plugins/Database.kt, src/main/resources/db/migration/V1__create_articles.sql and src/test/kotlin/com/example/ApplicationTest.kt. The description explains the startup order, shows the baseline command for databases that already have the table, and pastes the Gradle test summary with the new test class named.

On a repository whose CI runs ./gradlew check with a Postgres service, the pull request runs the new test before review; the samples repository's own workflow runs build-all.sh over every sample on a pull request, so the migration and test run there too. The run's record keeps the compose start, the Gradle output and the flyway_schema_history query.

A reviewer checks that V1__create_articles.sql is byte-for-byte the old DDL, that the data source uses the same credentials as the old connectToPostgres, and that the server does not bind before migrate() returns. Then they plan the baseline step for each existing environment, which is the only operational change this pull request asks for.

Run it on every service that creates its tables at startup

A team with several Ktor services sends one POST /o/{org}/sessions per repository with this prompt and that repository in resources. Each run reads its own startup code, so a service with three CREATE TABLE statements gets one V1 migration with all three in the order they ran, and a service on H2 gets flyway-core alone, and each run opens its own pull request. Send a request_id per request so a retried call returns the run it already started.

Give the key a webhook_url and every run it starts posts run.completed when its turn ends, with data.session_url to read the pull request link from. The key runs at most max_concurrent sessions at once, 4 by default; a migration request beyond that comes back 202 with status: "queued" and starts once one of the key's sandboxes ends or parks.

The use case this job follows, with its automation and trigger: Hand off a long task.

Why run it on Rush?

Moving schema creation out of application code is a change you want to see run: against a fresh database, where Flyway must create the table, and against one that already has it, where migrate() must fail until baseline is recorded. On a laptop that means a Postgres container, a JDK 17 toolchain and a Gradle build before the first assertion. In a Rush sandbox the agent starts the compose database, runs ./gradlew clean check, queries flyway_schema_history and keeps every command and its output in the run's record.

The result is a branch with a pull request and a test that proves the migration ran, so the reviewer reads evidence rather than intent. The same request works on any Ktor service that owns its schema in code; the run copies the DDL it finds into V1 and wires the startup order to match.

Frequently asked questions

What happens to a database that already has the articles table?

Flyway refuses to migrate a non-empty schema with no history table. The pull request documents a one-time Flyway.configure().baselineVersion("1").load().baseline(), or the equivalent with baselineOnMigrate set in configureDatabase() if the team prefers it automatic, so the existing table is recorded as V1 and later migrations apply normally.

Why keep a single JDBC Connection instead of adding a pool?

Because the service uses one today and the job is migrations, not connection management. configureDatabase() builds a data source for Flyway and hands the routes the same kind of Connection they had. Adding HikariCP is a separate change the reviewer can ask for in a follow-up.

Does the test need Docker in CI?

It needs a reachable Postgres with the postgres.yaml credentials. The sandbox uses the repository's docker-compose.yaml; a CI job provides the same with a service container, as the fixture repository's own workflow environment does when it builds every sample.

Can the migrations run in a separate step instead of at startup?

Yes. Ask for a Gradle flywayMigrate task through the Flyway Gradle plugin in the prompt and the agent wires that instead of, or as well as, the startup call. Running at startup is the smallest change for a service that today creates its table at startup.

More Ktor jobs

The same job on other stacks

More jobs like this: Hand off a long task

Keep reading