Simple Kotlin Object Relational Mapping

The nicest Kotlin multiplatform ORM around. Fully multiplatform. Coroutines-enabled.

Five main concepts:

• Database - Root container for your data model

• Schema - Logical grouping of entities (see Configuration)

• Entity - Corresponds to a table or view (defined in kddl syntax)

• Instance - A single row in a table, read-only; a MutableInstance adds the writes

• Attribute - Custom queries and mutations (see ksql syntax), with five variants:

  • ScalarAttribute, returning Any?
  • RowAttribute (and NullableRowAttribute), returning a row: an Instance, or a plain kson object
  • RowSetAttribute, returning a Flow of rows
  • ScalarSetAttribute, returning a Flow of a column's values
  • MutationAttribute, returning Long (either the number of modified rows, or the generated serial value)
  • TransactionAttribute, work in progress (a mut block of statements is a MutationAttribute, run in one transaction)

Four main methods in the lifecycle of database objects instances (along with transaction handling):

• MutableInstance.insert()
• Entity.fetch(primaryKey)
• MutableInstance.update()
• MutableInstance.delete()

The runtime has two halves: Database, Schema, Entity, Instance read; the objects of a mutable database also implement MutableDatabase, MutableSchema, MutableEntity, MutableInstance, which is where perform and the writes live. Instance is an interface over a read-only map (InstanceImpl); a MutableInstance is backed by a mutable one (MutableInstanceImpl) — a read-only row has no put at all.

Three main customization points (see Configuration):

• identifiers mapping (snake to camel, prefix/suffix removal, lowercase ...)
• fields filtering (hide secret field, mark field as read-only, ...)
• values filtering (transform timestamps, etc.)

Two model definition formats: kddl for DDL (schema structure), ksql for DML (custom queries and mutations). And two concrete database connectors, one for JDBC and one for a service API. More to come, hopefully.

One goal: maximum simplicity without conceeding anyting to extensibility.

Zero annotation. Zero SQL code fragmentation.

Let's create a simple todo list application.

database todo_app {
  schema todos {
    table task {
      *task_id serial
      title varchar(200)
      completed boolean = false
    }
  }
}

database todo_app {
  schema todos {
    attr pendingCount: Int =
      SELECT count(*) FROM task WHERE completed = false;

    mut Task.toggle =
      UPDATE task SET completed = NOT completed WHERE task_id = {task_id};
  }
}

plugins {
    kotlin("multiplatform") version "2.4.0"
    id("com.republicate.skorm") version "0.23"
}

skorm {
    model.set(file("src/commonMain/model/todo.kddl"))
    attributes.set(file("src/commonMain/model/todo.ksql"))    // optional
    destPackage.set("com.example.todo")
    dialect.set("postgresql")                                 // or "hypersql"
    // readOnly.set(true)                                     // only the read-only half, for a build that never writes
}

dependencies {
    // For JVM backend
    implementation("com.republicate.skorm:skorm-core")
    implementation("com.republicate.skorm:skorm-jdbc")

    // Or for JS/WASM client
    implementation("com.republicate.skorm:skorm-api-client")
}

Nothing else to declare: the single generateSkormCode task registers its output on your Kotlin source sets, so Gradle sequences generation before compilation on its own — no srcDir, no dependsOn. What it emits follows the platforms you build for, and core / client force either one on or off, for instance to emit client code in a project that has no JS target of its own. One call, initialize(), registers the entities, the navigations and the ksql attributes.

Options:

• model — the kddl model; or datasource, a JDBC URL to reverse-engineer it from. Exactly one.
• attributes — the ksql attributes declared over it. Without them, only the model is generated.
• destPackage — package of the generated code.
• dialect — postgresql or hypersql, required to emit the creation script.
• core / client — unset, they follow the project's targets; set, they force.
• readOnly — generate the read-only half alone (see below).
• outputDirectory — defaults to build/generated-src.

Generated code is laid out by role, not by source set, because one directory often feeds several of them — client serves jsMain, wasmJsMain and linuxX64Main alike:

build/generated-src/
├── common/kotlin       row interfaces and their impl classes, navigations, attribute accessors
├── core/kotlin         server-side attribute registrations
├── core/resources      database creation script
└── client/kotlin       REST client attribute registrations

common is registered on commonMain, core on the JVM target's source set, client on every JS, wasm and native one. In a plain kotlin("jvm") project there is a single source set, and common and core are both registered on main.

The generated types are nested (ExampleDatabase.BookshelfSchema.Book); alias in your own code the ones you use, e.g. typealias Book = ExampleDatabase.BookshelfSchema.Book.

The generator emits a read-only database and a mutable one extending it, so that code which must not write — a render, a user-edited template — is handed a database that cannot, not one that promises not to:

ExampleDatabase                          MutableExampleDatabase : ExampleDatabase, MutableDatabase
  BookshelfSchema                          MutableBookshelfSchema : BookshelfSchema, MutableSchema
    interface Book : Instance                interface MutableBook : Book, MutableInstance
      companion object : Entity               companion object : Entity, MutableEntity
      val title; fun author(); fun tags()      override var title; override fun tags(): Flow<MutableTag>
      fun currentBorrower()                    fun lend(dude: Int)            // ksql mutations
    open class BookImpl : InstanceImpl, Book   open class MutableBookImpl : MutableInstanceImpl, MutableBook

A row type is an interface, so a table hierarchy (MutableVip : Vip, MutablePerson) and mutability inherit side by side; the Impl class behind it is the storage, and carries the blocking twins. The companion object is the entity: Book.browse(), Book.fetch(id) read through the read-only database, MutableBook.browse(), MutableBook.new() through the mutable one. A row has no constructor: MutableBook.new() builds one to insert. Read attributes and navigations are declared once, on the read-only interface, and inherited; the mutable interface redeclares those returning rows, covariantly, and adds the mutations.

Constructing a MutableExampleDatabase also constructs its read-only sibling over the same processor, reachable as mutableDb.readOnly (and ExampleDatabase.instance): both share the attribute registry and any ambient transaction, and the sibling reads on the processor's read connector when one is configured. A read-only database holds the processor through a read-only view (ReadOnlyProcessor): a caller that ignores the Kotlin types, a template engine calling processor.perform by reflection, gets an exception, not a write. An app that never writes constructs ExampleDatabase alone — and can set readOnly on the plugin, so that the mutable half is not even generated.

// Initialize database (JVM): the mutable one, since we insert below
val database = MutableTodoAppDatabase(CoreProcessor(JdbcConnector()))
database.configure(mapOf(
    "core" to mapOf(
        "jdbc" to mapOf(
            "url" to "jdbc:h2:mem:todo",
            "login" to "sa"
        )
    )
))
database.initialize()   // registers the entities, the navigations and the ksql attributes (pendingCount, toggle)
// the tables must exist: run the generated create-script.sql first (the bookshelf example does it with a "create" mutation)

// Create a task
val task = MutableTask.new().apply {
    title = "Learn skorm"
    completed = false
    insert()
}

// Fetch and update
val fetched = MutableTask.fetch(task.taskId)
fetched?.let {
    it.completed = true
    it.update()
}

// Browse all tasks, read-only rows through the read-only sibling
Task.browse().collect { println(it.title) }

That's it! The skorm Gradle plugin generates all the necessary Kotlin types from your .kddl file.

You can also use skorm without the code generator, accessing entities dynamically:

// Navigate the model: a mutable database's entities hand out mutable rows
val schema = database.schema("todos")
val taskEntity = schema.entity("task")

// CRUD operations
val task = taskEntity.new() as MutableInstance
task.put("title", "Learn skorm")      // the typed write; a row is not a MutableMap, so no task["title"] = …
task.put("completed", false)
task.insert()

val fetched = taskEntity.fetch(task["taskId"]!!) as MutableInstance?
fetched?.let {
    it.put("completed", true)
    it.update()
}

taskEntity.browse().collect { println(it["title"]) }

This is useful for generic tools, migrations, or when the schema is only known at runtime.

Transactions are ambient: wrap any suspend code in database.transaction { ... } and every skorm operation against that database inside the block — entity ops, attributes, raw eval/perform — joins the same transaction, without passing any handle around.

database.transaction {        // or transaction("schema") { ... } for multi-schema databases
    task.update()
    MutableTask.new().apply { title = "follow-up"; insert() }
}                             // commit on exit; rollback (and rethrow) on any throw

Nested blocks on the same database join the enclosing transaction (single commit). The transaction holds one connection: don't fan out parallel coroutines inside the block, and collect row flows before the block exits. Not available in REST mode.

Database *—— Schema *—— Entity *—— Instance

Five main verbs to interact with attributes:

• eval(name, params...) - returns a scalar value
• harvest(name, params...) - returns a flow of scalar values, the one column of every row
• retrieve(name, params...) - returns a single row (plus Entity.fetch(params...) to get an instance by ID)
• query(name, params...) - returns a rowset
• perform(name, params...) - for mutations, on the mutable objects only; a mut declared as a block of statements (= { …; …; }) runs them in one transaction

Skorm automatically maps between database identifiers and Kotlin property names:

database.configure(mapOf(
    "core" to mapOf(
        "mapping" to mapOf(
            "read" to "snake_to_camel",   // DB columns: user_name → Kotlin: userName
            "write" to "camel_to_snake"   // Kotlin: userName → DB columns: user_name
        )
    )
))

A value is a comma-separated list of mappers, composed. Without read, it is snake_to_camel; without write, camel_to_snake then quoted in the database's own case.

Built-in mappers:

• snake_to_camel / camel_to_snake
• snake_to_pascal / pascal_to_snake
• lowercase / uppercase
• Custom mappers can be registered (IdentifiersMapping["name"] = { … })

Transform values as they are read, by SQL type:

ValuesFiltering["trim"] = { it?.toString()?.trim() }   // stock filters: lowercase, uppercase, parseJson
database.configure(mapOf(
    "core" to mapOf(
        "filter" to mapOf(
            "read" to mapOf(
                "text" to "trim"
            )
        )
    )
))

json/jsonb columns are parsed by default. filter.write is accepted but not applied yet.

JDBC Connector:

database.configure(mapOf(
    "core" to mapOf(            // the processor's settings
        "jdbc" to mapOf(        // its connector's
            "url" to "jdbc:postgresql://localhost:5432/mydb",
            "login" to "dbuser",
            "password" to "secret"
        )
    )
))

Rows are a Flow. browse(), navigations to many rows and * attributes return a cold Flow: nothing runs until it is collected, rows are fetched as they are collected, and what backs them is released when the collection ends, exhausted or not. Collect once, inside the transaction the flow was queried in. Every call the processor makes to its connector runs on CoreProcessor.blockingContext, Dispatchers.IO on the JVM, so a handler on an event loop awaits rows instead of blocking on them. On JDBC a many-row read streams: the driver fetches jdbc.fetchSize rows at a time (1000 by default) on a connection held until the flow is done, then committed and released, so a million-row query costs a million rows of memory nowhere. Blocking twins are the exception by construction: they park the calling thread, so a template that uses them is rendered off the event loop, withContext(Dispatchers.IO) { template.merge(…) }.

Read connector. CoreProcessor(connector, readConnector) runs the reads of a read-only database on the second connector — a pool on a SELECT-only role is what makes the database actually read-only. It takes its settings from core.read.<tag> (core.read.jdbc.url, …), or the write connector's when absent. With a single connector, core.read.* is ignored: the read-only build's SELECT-only guarantee comes from the database role, not from the library, which only routes the reads. Inside a transaction { } of the mutable database, the read-only one reads on the transaction's connection.

API Client (for JS/WASM):

val database = TodoAppDatabase(ApiClient("https://api.example.com"))
database.initialize()
// Same code works on browser/Node.js!

The kddl (Kotlin Data Definition Language) format defines your database structure. It generates both SQL DDL scripts and Kotlin classes.

database <name> {
  schema <name> {
    table <name> {
      <field_name> <type> [modifiers]
    }
  }
}

• Strings: varchar(length), char(length), text
• Numbers: tinyint, smallint, int, bigint, long, float, double, numeric(p,s), money (the last two read as Double for now)
• Booleans: boolean
• Dates: date, time, timestamp, timestamptz
• Special: serial, bigserial (auto-increment), uuid, json, blob
• Enums: enum('value1', 'value2', ...), or the name of a schema-level enum level(low, high)

• * - part of the primary key (prefix)
• ! - unique constraint (prefix)
• ? - nullable field
• = <value> - default value

Example:

table user {
  *user_id serial                 // primary key
  !email varchar(255)             // unique, non-null
  name varchar(100)?              // nullable
  age int = 18                    // default value
  status enum('active', 'inactive') = 'active'
  created_at timestamp = now()
}

Declare them with *. A table that a link references without declaring one gets an implicit <table_name>_id serial key — deprecated, kddl warns about it:

table book {
  *book_id serial
  title varchar(100)
}

A link's * marks the many side, which holds the foreign key; a chevron (>) pointing at the one side restricts traversal to the reference. With no chevron, both navigations are generated.

book *-- author      // book holds author_id — Book.author() and Author.books()
borrowing -> book    // borrowing holds book_id — Borrowing.book() only
book *-* tag         // join table book_tag — Book.tags() and Tag.books()

table book {
  donor -- dude?     // field link, both ways — Book.donor() and Dude.donorBooks()
  editor -> dude?    // field link, reference only — Book.editor()
}

When two links reach the same table, as donor and editor do here, the collection is named after the field (donorBooks) rather than the table (books).

A many-to-many is always navigable both ways. a -- b between two tables is rejected, nothing saying which side holds the key (kddl ≥ 0.27).

The kddl compiler generates:

1. SQL DDL scripts for database creation
2. Kotlin row interfaces with typed properties, read-only and mutable
3. Navigation methods for the relationships above, as members of the generated interfaces — each with a blocking twin on the impl class (BookImpl.tagsBlocking(), reachable by reflection as tags()) for callers that cannot suspend

For complete kddl documentation, see the kddl project.

Beyond the basic CRUD operations, skorm allows you to define custom queries and mutations using the ksql format. These definitions generate type-safe Kotlin objects and member functions on their receivers.

attr [Entity.]name[(params)]: ReturnType = SQL
mut [Entity.]name[(params)] = SQL
mut [Entity.]name[(params)] = { SQL; SQL; ... }

• attr - defines a query attribute (SELECT)
• mut - defines a mutation attribute (INSERT/UPDATE/DELETE)
• Schema-level: attr name - member function of the schema class
• Entity-level: attr Entity.name - member function of the row interface (a mut: of the mutable one)

A composite field takes a ? of its own: (name: String, born: LocalDate?) reads born as LocalDate?, its siblings stay non-null. A composite may have a single field, (seen: LocalDateTime?): a row with one named value.

Supported scalar types: Int, Long, String, Boolean, Double, Float, LocalDate, LocalDateTime, LocalTime

SQL parameters are enclosed in curly braces:

attr getUserByEmail(email: String): User? =
  SELECT * FROM users WHERE email = {email};

For entity-level attributes, all entity fields are automatically available:

attr Book.currentBorrower: Dude? =
  SELECT dude.* FROM borrowing
    JOIN dude USING (dude_id)
    WHERE book_id = {book_id}  // book_id from Book instance
    AND returned_date IS NULL;

Any other parameter is declared in the signature, for attributes and mutations alike:

mut Book.lend(dude_id: Long) =
  INSERT INTO borrowing (book_id, dude_id, borrowed_date)
    VALUES ({book_id}, {dude_id}, now());

Schema-level scalar:

attr booksCount: Int =
  SELECT count(*) FROM book;

// Generates, in BookshelfSchema: suspend fun booksCount(): Int

Entity-level composite object:

attr Book.currentBorrower: (Dude, borrowing_date: LocalDateTime)? =
  SELECT dude.*, borrowing_date FROM bookshelf.borrowing
    JOIN dude USING (dude_id)
    WHERE book_id = {book_id}
    AND restitution_date IS NULL;

// Generates:
// class CurrentBorrower : DudeImpl { val borrowingDate: LocalDateTime }
// in Book: suspend fun currentBorrower(): CurrentBorrower?

Mutation with parameters:

mut Book.lend(dude_id: Long) =
  INSERT INTO borrowing (dude_id, book_id, borrowing_date)
    VALUES ({dude_id}, {book_id}, now());

// Generates, in MutableBook: suspend fun lend(dude_id: Long): Long

Anonymous object:

attr Book.stats: (title_length: Int, borrowed: Int) =
  SELECT
    CHARACTER_LENGTH(title) title_length,
    (SELECT COUNT(*) FROM borrowing WHERE book_id = {book_id}) borrowed
  FROM book
  WHERE book_id = {book_id};

// Generates:
// class Stats { val titleLength: Int; val borrowed: Int }
// in Book: suspend fun stats(): Stats

Flow (rowset):

attr topBorrowers: (dude_id: Long, borrow_count: Int)* =
  SELECT dude_id, COUNT(*) borrow_count
  FROM borrowing
  GROUP BY dude_id
  ORDER BY borrow_count DESC
  LIMIT 10;

// Generates:
// class TopBorrowers { val dudeId: Long; val borrowCount: Int }
// in BookshelfSchema: suspend fun topBorrowers(): Flow<TopBorrowers>

All generated functions are coroutine-based (suspend) and type-safe, providing compile-time checking of parameters and return types.

Let's build a complete bookshelf application that tracks books and borrowings, demonstrating both JVM backend and JS frontend using the same business logic.

database example {
  schema bookshelf {

    table dude { *dude_id serial  name varchar(100) }

    table author { *author_id serial  name varchar(100) }

    table book {
      *book_id serial
      title varchar(100)
      genre enum('essay', 'literature', 'art')
      language char(2)?
    }

    table borrowing {
      borrowing_date date = now()
      restitution_date date?
    }

    book *-- author
    borrowing -> book
    borrowing -> dude
  }
}

This generates:

• SQL creation script
• Row interfaces: Dude, Author, Book, Borrowing, and their Mutable twins
• Relationship methods: book.author(), author.books(), borrowing.book(), borrowing.dude()

database example {
  schema bookshelf {

    // Schema-level scalar attribute
    attr booksCount: Int =
      SELECT count(*) FROM book;

    // Entity-level attribute returning a composite object
    attr Book.currentBorrower: (Dude, borrowing_date: LocalDate)? =
      SELECT dude.*, borrowing_date FROM bookshelf.borrowing
        JOIN dude USING (dude_id)
        WHERE book_id = {book_id}
        AND restitution_date IS NULL;

    // Mutation with parameters
    mut Book.lend(dude_id: Long) =
      INSERT INTO borrowing (dude_id, book_id, borrowing_date)
        VALUES ({dude_id}, {book_id}, now());

    // Mutation without parameters
    mut Book.restitute =
      UPDATE borrowing SET restitution_date = NOW()
        WHERE book_id = {book_id} AND restitution_date IS NULL;

    // Attribute returning an anonymous object
    attr Book.stats: (title_length: Int, borrowed: Int) =
      SELECT
        CHARACTER_LENGTH(title) title_length,
        (SELECT COUNT(*) FROM borrowing WHERE book_id = {book_id}) borrowed
      FROM book
      WHERE book_id = {book_id};

    // Attribute returning a sequence
    attr topBorrowers: (dude_id: Long, borrowed: Int)* =
      SELECT dude_id,
        COUNT(restitution_date) borrowed
      FROM borrowing
      GROUP BY dude_id
      ORDER BY borrowed DESC;
  }
}

import com.republicate.skorm.core.CoreProcessor
import com.republicate.skorm.jdbc.JdbcConnector

val database = MutableExampleDatabase(CoreProcessor(JdbcConnector()))

fun Application.configureDatabase() {
    // Configure from application.conf
    database.configure(environment.config.config("skorm").toMap())
    database.initialize()

    // Create test data
    runBlocking {
        val author = MutableAuthor.new().apply {
            name = "Isaac Asimov"
            insert()
        }

        val book = MutableBook.new().apply {
            title = "Foundation"
            authorId = author.authorId
            genre = Genre.essay
            insert()
        }

        MutableDude.new().apply {
            name = "Alice"
            insert()
        }
    }
}

fun Application.configureRouting() {
    routing {
        get("/") {
            call.respondHtml {
                body {
                    h1 { +"My Bookshelf" }
                    ul {
                        runBlocking {
                            Book.browse().collect { book ->
                                val author = book.author()
                                val borrower = book.currentBorrower()
                                li {
                                    +book.title
                                    i { +" by ${author.name}" }

                                    if (borrower != null) {
                                        +" - borrowed by ${borrower.name}"
                                    } else {
                                        +" - available"
                                    }
                                }
                            }
                        }
                    }
                }
            }
        }

        // REST API endpoint: the mutable schema, so the write routes exist
        route("/api/example") {
            rest(MutableExampleDatabase.bookshelf)
        }
    }
}

import com.republicate.skorm.ApiClient
import kotlinx.browser.window

// Same database definition, different processor — mutable, since this client lends
val database = MutableExampleDatabase(ApiClient("${window.location.origin}/api"))

fun main() {
    window.onload = {
        database.initialize()

        // Same code as backend!
        document.querySelector(".lend-form")?.addEventListener("submit") { event ->
            event.preventDefault()
            GlobalScope.launch {
                val bookId = form.getAttribute("data-book_id")
                val book = MutableBook.fetch(bookId) ?: error("Book not found")
                val dudeId = selectElement.value.toLong()

                book.lend(dudeId)  // Calls REST API transparently
                document.location?.reload()
            }
        }
    }
}

The same business logic code works on both JVM and JS:

// This code is identical on server and client (lend and restitute are on the mutable row):
val book = MutableBook.fetch(bookId)
book?.let {
    val borrower = it.currentBorrower()
    it.lend(dudeId)
    it.restitute()
}

On JVM: CoreProcessor → JDBC → Database On JS: ApiClient → HTTP → REST API → CoreProcessor → JDBC → Database

The Processor abstraction makes your code platform-agnostic!

Versions 0.7 and upwards have been co-autored with AI agents.