Start with an empty project and finish with a running, tested REST API: two entities, their relationship loaded in one query, CRUD routes on Ktor, and a test that asserts on the SQL. It takes about twenty minutes.
Start a plain Kotlin Gradle project and add Ktor and Storm. The Ktor plugin manages the Ktor versions, and the Storm plugin imports Storm's BOM and wires the metamodel processor and the compiler plugin, so most dependencies need no version. The Storm Ktor plugin builds its connection pool with HikariCP, which you add yourself. The database is H2, so there is nothing to install. Ktor's EngineMain starts the server and reads application.conf.
// build.gradle.kts plugins { kotlin("jvm") version "2.0.21" id("io.ktor.plugin") version "3.4.3" id("com.google.devtools.ksp") version "2.0.21-1.0.28" id("st.orm") version "1.15.0" } repositories { mavenCentral() } kotlin { jvmToolchain(21) } application { mainClass = "io.ktor.server.netty.EngineMain" // reads application.conf } dependencies { implementation("st.orm:storm-ktor") implementation("com.zaxxer:HikariCP:6.2.1") // the pool the plugin builds runtimeOnly("st.orm:storm-h2") runtimeOnly("com.h2database:h2:2.3.232") implementation("io.ktor:ktor-server-netty") implementation("io.ktor:ktor-server-content-negotiation") implementation("io.ktor:ktor-serialization-jackson") implementation("st.orm:storm-jackson2") testImplementation("st.orm:storm-ktor-test") }
// build.gradle.kts plugins { kotlin("jvm") version "2.1.21" id("io.ktor.plugin") version "3.4.3" id("com.google.devtools.ksp") version "2.1.21-2.0.2" id("st.orm") version "1.15.0" } repositories { mavenCentral() } kotlin { jvmToolchain(21) } application { mainClass = "io.ktor.server.netty.EngineMain" // reads application.conf } dependencies { implementation("st.orm:storm-ktor") implementation("com.zaxxer:HikariCP:6.2.1") // the pool the plugin builds runtimeOnly("st.orm:storm-h2") runtimeOnly("com.h2database:h2:2.3.232") implementation("io.ktor:ktor-server-netty") implementation("io.ktor:ktor-server-content-negotiation") implementation("io.ktor:ktor-serialization-jackson") implementation("st.orm:storm-jackson2") testImplementation("st.orm:storm-ktor-test") }
// build.gradle.kts plugins { kotlin("jvm") version "2.2.21" id("io.ktor.plugin") version "3.4.3" id("st.orm") version "1.15.0" } repositories { mavenCentral() } kotlin { jvmToolchain(21) } application { mainClass = "io.ktor.server.netty.EngineMain" // reads application.conf } dependencies { implementation("st.orm:storm-ktor") implementation("com.zaxxer:HikariCP:6.2.1") // the pool the plugin builds runtimeOnly("st.orm:storm-h2") runtimeOnly("com.h2database:h2:2.3.232") implementation("io.ktor:ktor-server-netty") implementation("io.ktor:ktor-server-content-negotiation") implementation("io.ktor:ktor-serialization-jackson") implementation("st.orm:storm-jackson2") testImplementation("st.orm:storm-ktor-test") }
// build.gradle.kts plugins { kotlin("jvm") version "2.3.21" id("io.ktor.plugin") version "3.4.3" id("st.orm") version "1.15.0" } repositories { mavenCentral() } kotlin { jvmToolchain(21) } application { mainClass = "io.ktor.server.netty.EngineMain" // reads application.conf } dependencies { implementation("st.orm:storm-ktor") implementation("com.zaxxer:HikariCP:6.2.1") // the pool the plugin builds runtimeOnly("st.orm:storm-h2") runtimeOnly("com.h2database:h2:2.3.232") implementation("io.ktor:ktor-server-netty") implementation("io.ktor:ktor-server-content-negotiation") implementation("io.ktor:ktor-serialization-jackson") implementation("st.orm:storm-jackson2") testImplementation("st.orm:storm-ktor-test") }
// build.gradle.kts plugins { kotlin("jvm") version "2.4.0" id("io.ktor.plugin") version "3.4.3" id("st.orm") version "1.15.0" } repositories { mavenCentral() } kotlin { jvmToolchain(21) } application { mainClass = "io.ktor.server.netty.EngineMain" // reads application.conf } dependencies { implementation("st.orm:storm-ktor") implementation("com.zaxxer:HikariCP:6.2.1") // the pool the plugin builds runtimeOnly("st.orm:storm-h2") runtimeOnly("com.h2database:h2:2.3.232") implementation("io.ktor:ktor-server-netty") implementation("io.ktor:ktor-server-content-negotiation") implementation("io.ktor:ktor-serialization-jackson") implementation("st.orm:storm-jackson2") testImplementation("st.orm:storm-ktor-test") }
Assembling it by hand is the point of this tutorial. When you are starting real work instead, the Ktor starter template is a project like this one, already assembled: two entities with their migration, a repository query, a service, two endpoints and their tests. Press Use this template on GitHub and it runs on H2 before you change a line.
Storm maps to an existing schema and does not generate one, so migrations stay under your control. For this tutorial a small script is enough: the application runs it on the in-memory database at startup (step 6), so there is no migration tool to set up yet. For another database, pick it in the block below and run the script yourself.
-- src/main/resources/schema.sql CREATE TABLE folder ( id INT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY, name VARCHAR(100) NOT NULL ); CREATE TABLE bookmark ( id INT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY, url VARCHAR(2000) NOT NULL, title VARCHAR(200) NOT NULL, folder_id INT NOT NULL REFERENCES folder(id) ); -- one folder to start with INSERT INTO folder (name) VALUES ('Reading');
-- src/main/resources/schema.sql CREATE TABLE folder ( id INT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY, name VARCHAR(100) NOT NULL ); CREATE TABLE bookmark ( id INT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY, url VARCHAR(2000) NOT NULL, title VARCHAR(200) NOT NULL, folder_id INT NOT NULL REFERENCES folder(id) ); -- one folder to start with INSERT INTO folder (name) VALUES ('Reading');
-- src/main/resources/schema.sql CREATE TABLE folder ( id INT PRIMARY KEY AUTO_INCREMENT, name VARCHAR(100) NOT NULL ); CREATE TABLE bookmark ( id INT PRIMARY KEY AUTO_INCREMENT, url VARCHAR(2000) NOT NULL, title VARCHAR(200) NOT NULL, folder_id INT NOT NULL, FOREIGN KEY (folder_id) REFERENCES folder(id) ); -- one folder to start with INSERT INTO folder (name) VALUES ('Reading');
-- src/main/resources/schema.sql CREATE TABLE folder ( id NUMBER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY, name VARCHAR2(100) NOT NULL ); CREATE TABLE bookmark ( id NUMBER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY, url VARCHAR2(2000) NOT NULL, title VARCHAR2(200) NOT NULL, folder_id NUMBER NOT NULL REFERENCES folder(id) ); -- one folder to start with INSERT INTO folder (name) VALUES ('Reading');
-- src/main/resources/schema.sql CREATE TABLE folder ( id INT IDENTITY PRIMARY KEY, name VARCHAR(100) NOT NULL ); CREATE TABLE bookmark ( id INT IDENTITY PRIMARY KEY, url VARCHAR(2000) NOT NULL, title VARCHAR(200) NOT NULL, folder_id INT NOT NULL REFERENCES folder(id) ); -- one folder to start with INSERT INTO folder (name) VALUES ('Reading');
-- src/main/resources/schema.sql CREATE TABLE folder ( id INTEGER PRIMARY KEY, name TEXT NOT NULL ); CREATE TABLE bookmark ( id INTEGER PRIMARY KEY, url TEXT NOT NULL, title TEXT NOT NULL, folder_id INTEGER NOT NULL REFERENCES folder(id) ); -- one folder to start with INSERT INTO folder (name) VALUES ('Reading');
Two immutable data classes. A Bookmark belongs to a Folder, and the @FK on that field declares the relationship. Field names map to column names, and a foreign key field maps to its _id column, so folder maps to folder_id.
// Folder.kt data class Folder( @PK val id: Int = 0, val name: String, ) : Entity<Int> // Bookmark.kt: @FK means the folder is loaded in the same query data class Bookmark( @PK val id: Int = 0, val url: String, val title: String, @FK val folder: Folder, ) : Entity<Int>
The ktor section tells EngineMain the port and the module to load. The storm section configures the Storm plugin: install(Storm) builds a connection pool from storm.datasource and registers the repositories the metamodel processor indexed at compile time. Schema validation in warn mode logs any mismatch between the entities and the tables.
# src/main/resources/application.conf ktor { deployment { port = 8080 } application { modules = [ ApplicationKt.module ] } } storm { datasource { jdbcUrl = "jdbc:h2:mem:bookmarks;DB_CLOSE_DELAY=-1" username = "sa" password = "" } validation { schemaMode = "warn" } # log entity/schema mismatches }
An interface that extends EntityRepository inherits the CRUD methods. Add your own queries as default methods; Bookmark_ is the generated metamodel, so a misspelled field fails to compile. FolderRepository declares nothing of its own.
// Repositories.kt: CRUD is inherited; add only your own queries interface BookmarkRepository : EntityRepository<Bookmark, Int> { fun findByFolderName(name: String): List<Bookmark> = findAll(Bookmark_.folder.name eq name) } // the minimal case (optional): one line, nothing to implement interface FolderRepository : EntityRepository<Folder, Int>
The module installs Storm, registers Storm's Jackson module for entity serialization, and mounts the routes. The migration hook runs the schema script once, after the pool is built and before schema validation. A real project runs Flyway or Liquibase there.
// Application.kt fun main(args: Array<String>) = EngineMain.main(args) fun Application.module() { install(Storm) { // reads storm.datasource, registers the repositories // runs once at startup, before schema validation migration { dataSource -> dataSource.connection.use { it.createStatement().execute("RUNSCRIPT FROM 'classpath:schema.sql'") } } } install(ContentNegotiation) { jackson { registerModule(StormModule()) } } routing { bookmarkRoutes() } }
Reads call the repository directly; writes run inside transaction { }. The transaction is a suspend function, so it runs in the request's coroutine. repository<T>() is an extension you can call in any handler.
// Routes.kt @JsonIgnoreProperties(ignoreUnknown = true) data class NewBookmark(val url: String, val title: String, val folderId: Int) fun Route.bookmarkRoutes() { get("/bookmarks") { call.respond(repository<BookmarkRepository>().findAll()) } get("/bookmarks/{id}") { val id = call.parameters.getOrFail("id").toInt() val bookmark = repository<BookmarkRepository>().findById(id) call.respond(bookmark ?: HttpStatusCode.NotFound) } post("/bookmarks") { val body = call.receive<NewBookmark>() val folder = repository<FolderRepository>().findById(body.folderId) if (folder == null) { call.respond(HttpStatusCode.BadRequest, "unknown folder"); return@post } val created = transaction { repository<BookmarkRepository>() .insertAndFetch(Bookmark(url = body.url, title = body.title, folder = folder)) } call.respond(HttpStatusCode.Created, created) } delete("/bookmarks/{id}") { val id = call.parameters.getOrFail("id").toInt() transaction { repository<BookmarkRepository>().removeById(id) } call.respond(HttpStatusCode.NoContent) } }
The list endpoint runs one query. The folder of every bookmark is joined in, so the list does not issue a query per bookmark. Toggle Show SQL to see the statement.
// GET /bookmarks returns every bookmark, each with its folder repository<BookmarkRepository>().findAll()
-- one query, no N+1: the folder is joined in SELECT b.id, b.url, b.title, b.folder_id, f.name FROM bookmark b INNER JOIN folder f ON b.folder_id = f.id
Start the server and call it with curl. Each bookmark in the response includes its folder.
# start the app ./gradlew run # create a bookmark in folder 1 curl -s -X POST localhost:8080/bookmarks \ -H 'content-type: application/json' \ -d '{"url":"https://orm.st","title":"Storm","folderId":1}' # list them back: each bookmark includes its folder object curl -s localhost:8080/bookmarks
testStormApplication from storm-ktor-test creates an in-memory database, runs your schema script, and captures the SQL of every call the application handles. This test asserts that creating a bookmark writes one row with one INSERT. count() and statements() on the capture pin down the rest of the request.
// BookmarkRoutesTest.kt @Test fun `POST creates a bookmark with a single insert`() = testStormApplication( scripts = listOf("/schema.sql"), ) { scope -> application { install(Storm) { dataSource = scope.stormDataSource } install(ContentNegotiation) { jackson { registerModule(StormModule()) } } routing { bookmarkRoutes() } } // the scope captures the SQL of every call the application handles client.post("/bookmarks") { contentType(ContentType.Application.Json) setBody("""{"url":"https://orm.st","title":"Storm","folderId":1}""") } assertEquals(1, scope.stormSqlCapture.count(Operation.INSERT)) }
You went from an empty project to a running, tested API: two entities, a relationship loaded with a join, four routes and a repository. From here: