Exposed: tables and CRUD via the DSL

1. Why Exposed, and why it is not an ORM

When you have already written a couple of JDBC methods, a strong feeling appears that you are working not as a programmer, but as a copying machine: prepareStatement everywhere, parameter indexes everywhere, the same try/use everywhere, and you also need to remember that parameter indexing starts at 1. At that moment, Exposed looks like salvation: “you can write queries the Kotlin way.” But it is important not to fall into the illusion that Exposed “cancels SQL” — it just makes it less noisy.

Exposed is a JetBrains library that provides a Kotlin DSL for SQL. In this lecture, we will talk specifically about the Exposed DSL (it is sometimes called SQL DSL), not about the Exposed DAO API with Entity/EntityClass. The DAO part exists, but it brings more abstractions and magic, and our goal is clear code for a beginner: “I see the table, I see the insert, I see the select.”

To feel the difference, it is useful to keep this comparison in mind:

SQL string + setString(1, …)

Table.insert { it[column] = value }

ResultSet + rs.getLong("id")

select { … }.map { row -> … }

autoCommit=false + commit/rollback

transaction { … }

And yes: we still must think about transactions, data integrity, and the fact that UPDATE without WHERE is a “self-destruct button.” It is just that now the code will look like you are writing Kotlin, not “SQL in strings + shamanism around resources.”

2. Setup: dependencies and Database.connect

With Exposed there is one honest point: for the “magic to start,” you need to add the library, and you also need the JDBC driver for your DB. Exposed does not replace the driver: it sits on top of it. So project setup is two steps: dependencies + DB connection. After that, you write DSL code and do not think about low-level details on every query.

Below is a minimal example of dependencies (as a concept). Versions depend on your project, so treat this as a “shape,” not as “the only correct numbers”:

// build.gradle.kts (idea)
dependencies {
    implementation("org.jetbrains.exposed:exposed-core:<version>")
    implementation("org.jetbrains.exposed:exposed-jdbc:<version>")

    implementation("org.xerial:sqlite-jdbc:<version>") // if we use SQLite
}

Connecting to the DB in code is usually done once at application startup. For a learning project, we will take SQLite and the file "app.db" next to the program.

import org.jetbrains.exposed.sql.Database

fun connectDb() {
    Database.connect(
        url = "jdbc:sqlite:app.db",
        driver = "org.sqlite.JDBC"
    )
}

Here it is important to remember a simple idea: Database.connect(...) means “we told Exposed where to connect.” It does not execute any queries; it is just configuration. The operations themselves will run inside transaction { ... }.

3. Describing a table: LongIdTable and columns

Up to this point, our table existed as an SQL string CREATE TABLE .... In Exposed, a table becomes a Kotlin object where we declare columns as properties. This is convenient for two reasons: first, the IDE starts helping with auto-completion (fewer typos in column names). Second, you get “type safety” at the column level: you will not accidentally put a string into a Long; the compiler will stop you before the database does.

To continue the practical example, imagine we are evolving our console expense-tracking project. We will have an Expense entity: amount (in cents), category, and a short description.

data class Expense(
    val id: Long,
    val title: String,
    val amountCents: Long,
    val category: String
)

Now let us describe the table. For a beginner, the most friendly option is LongIdTable: it already contains an auto-increment id and removes some of the routine work.

import org.jetbrains.exposed.dao.id.LongIdTable

object Expenses : LongIdTable("expenses") {
    val title = varchar("title", length = 200)
    val amountCents = long("amount_cents")
    val category = varchar("category", length = 50)
}

It looks almost like a data structure declaration. And this is a great moment to lock in: Exposed does not store data in this object. object Expenses is a schema description, not a collection of expenses in memory.

For clarity, you can imagine it like this:

flowchart TD
    A["object Expenses : LongIdTable(...)
column description"] -->|DSL builds SQL| B["SQL query"]
    B --> C["JDBC driver"]
    C --> D["Database (SQLite file app.db)"]

4. Transactions: transaction {} as the working frame

If JDBC code forced you to constantly think about autoCommit and careful rollback, Exposed offers a nicer “frame”: transaction { ... }. Inside the block you write queries, and outside you get a clear result. But there is a catch that people often trip over: transaction {} is not just “syntactic sugar,” it is a boundary within which Exposed’s correct work with the connection and transaction is guaranteed.

The first practical action you usually need in a learning project is to create tables. Exposed can do this via SchemaUtils.

import org.jetbrains.exposed.sql.SchemaUtils
import org.jetbrains.exposed.sql.transactions.transaction

fun initSchema() {
    transaction {
        SchemaUtils.create(Expenses)
    }
}

This is “create the table if it does not exist” (within the capabilities of a specific DB). Yes, it is similar to migrations, but migrations as a separate discipline we are not touching today — it is important that the project simply works.

I strongly recommend getting used to the style: everything that talks to the DB goes inside transaction, and outside you keep ordinary Kotlin code. In this sense, Exposed disciplines architecture better than “JDBC everywhere.”

5. CRUD in the Exposed DSL

Create: insert { ... }

When you first see insert in Exposed, it feels “too pretty to be true.” In reality, it just builds a parameterized SQL query and executes it via JDBC. That means the safety and the idea of parameters did not go anywhere — you just stopped writing ? and setString(1, ...) by hand.

Let us add a function to create an expense. For simplicity, we will not return the id (you can do it, but it adds details that are not critical right now). We will return Unit, and for a learning project for now, success/failure can be expressed by an exception.

import org.jetbrains.exposed.sql.insert
import org.jetbrains.exposed.sql.transactions.transaction

fun addExpense(title: String, amountCents: Long, category: String) {
    transaction {
        Expenses.insert {
            it[Expenses.title] = title
            it[Expenses.amountCents] = amountCents
            it[Expenses.category] = category
        }
    }
}

Pay attention to the form it[Column] = value. Here, it is the DSL context for building the INSERT.

If you want to be “a little more grown-up,” you can add simple input validation at the domain level (we have already done this earlier via require/check), so you do not send obviously bad values to the DB:

fun addExpenseSafe(title: String, amountCents: Long, category: String) {
    require(title.isNotBlank()) { "title must not be blank" }
    require(amountCents > 0) { "amountCents must be positive" }

    addExpense(title.trim(), amountCents, category.trim())
}

This is not an “Exposed feature,” it is application discipline: the DB is not a trash can; it already tolerates a lot.

Read: select and mapping ResultRow → Expense

Reading data is the most common CRUD scenario: show an expense list, find by category, find by id. In JDBC, you read a ResultSet with a cursor via rs.next(). In Exposed, you get the read result as a set of ResultRows, and then you usually map it into your data class. This is exactly the same approach we used earlier with collections: “got a set of elements → transformed it.” This style works well with standard collection operations like map.

Let us start with “read everything.” In Exposed it looks very straightforward:

import org.jetbrains.exposed.sql.selectAll
import org.jetbrains.exposed.sql.transactions.transaction

fun listExpenses(): List<Expense> = transaction {
    Expenses.selectAll().map { row ->
        Expense(
            id = row[Expenses.id].value,
            title = row[Expenses.title],
            amountCents = row[Expenses.amountCents],
            category = row[Expenses.category]
        )
    }
}

Yes, there is a .value here. That is because LongIdTable stores the id as EntityID<Long> (a wrapper), while your domain usually needs a plain Long.

Now filtering, for example by category. Here eq (equals) appears. It is convenient to read it as “category equals …”.

import org.jetbrains.exposed.sql.SqlExpressionBuilder.eq
import org.jetbrains.exposed.sql.select
import org.jetbrains.exposed.sql.transactions.transaction

fun listByCategory(category: String): List<Expense> = transaction {
    Expenses.select { Expenses.category eq category }.map { r ->
        Expense(r[Expenses.id].value, r[Expenses.title], r[Expenses.amountCents], r[Expenses.category])
    }
}

Here is an important “learning” idea: you should not return ResultRow to the outside. This is Exposed’s internal format. It is better to return your domain model (Expense). Then the rest of the code does not depend on the chosen DB library — very much in the spirit of architectural boundaries we discussed earlier.

Update and Delete: update, deleteWhere, and checking row counts

Changing and deleting are where it is easy to make a mistake because “it compiled.” In JDBC, we discussed that executeUpdate() returns the number of affected rows, and you need to check it. In Exposed, it is exactly the same idea: update and deleteWhere return an Int — how many rows actually changed/were deleted. If you expected 1 but got 0, that is not “oh well” — it is a signal that the record was not found or the criteria is wrong.

Let us update an expense title by id:

import org.jetbrains.exposed.sql.SqlExpressionBuilder.eq
import org.jetbrains.exposed.sql.transactions.transaction
import org.jetbrains.exposed.sql.update

fun renameExpense(id: Long, newTitle: String): Boolean = transaction {
    val updated = Expenses.update({ Expenses.id eq id }) {
        it[Expenses.title] = newTitle
    }
    updated == 1
}

Delete by id:

import org.jetbrains.exposed.sql.SqlExpressionBuilder.eq
import org.jetbrains.exposed.sql.deleteWhere
import org.jetbrains.exposed.sql.transactions.transaction

fun deleteExpense(id: Long): Boolean = transaction {
    val deleted = Expenses.deleteWhere { Expenses.id eq id }
    deleted == 1
}

If you noticed, the pattern repeats: “ran it → compared to 1.” This is not redundancy, it is your application contract: the “rename/delete by id” operation must touch exactly one record. Anything else is a scenario error.

For aesthetics, you can make a small cheat-sheet table of what returns what:

insert { ... }

select { ... }

update({ ... }) { ... }

deleteWhere { ... }

6. Where is DAO here: hide Exposed behind a repository

When you first add Exposed, you really want to write transaction { Expenses.selectAll() ... } right from main. It works! But a week later, you will realize your code has turned into “transaction spaghetti,” and any schema change breaks half the application. So we need a boundary: a place where data access lives, and a place where scenario logic lives. In previous architecture days we called this a repository/storage layer; in pattern terms, the word DAO sits nearby.

DAO (Data Access Object) in an applied sense is an object/class/module that knows how to read and write data. It hides SQL/Exposed details and exposes clear methods like addExpense, listExpenses, deleteExpense.

Let us start with the repository contract. It does not have to involve generics, but the principle “contract as an interface” works great with Kotlin types and generics that we have already met in the language.

interface ExpenseRepository {
    fun add(title: String, amountCents: Long, category: String)
    fun listAll(): List<Expense>
    fun deleteById(id: Long): Boolean
}

Now an Exposed implementation:

import org.jetbrains.exposed.sql.SqlExpressionBuilder.eq
import org.jetbrains.exposed.sql.deleteWhere
import org.jetbrains.exposed.sql.insert
import org.jetbrains.exposed.sql.selectAll
import org.jetbrains.exposed.sql.transactions.transaction

class ExposedExpenseRepository : ExpenseRepository {
    override fun add(title: String, amountCents: Long, category: String) = transaction {
        Expenses.insert {
            it[Expenses.title] = title
            it[Expenses.amountCents] = amountCents
            it[Expenses.category] = category
        }
    }

    override fun listAll(): List<Expense> = transaction {
        Expenses.selectAll().map { r ->
            Expense(r[Expenses.id].value, r[Expenses.title], r[Expenses.amountCents], r[Expenses.category])
        }
    }

    override fun deleteById(id: Long): Boolean = transaction {
        Expenses.deleteWhere { Expenses.id eq id } == 1
    }
}

Yes, it is a bit more than 10 lines — but it is a cohesive chunk that shows the idea “all DB logic lives in one place.” In a real project you would split it into private functions and make it shorter, but right now it is more useful to see the whole “skeleton.”

Now main (or the CLI layer) works with ExpenseRepository, not with Exposed directly:

fun main() {
    connectDb()
    initSchema()

    val repo: ExpenseRepository = ExposedExpenseRepository()
    repo.add("Coffee", 19900, "food")

    println(repo.listAll()) // [Expense(id=1, title=Coffee, amountCents=19900, category=food)]
}

So “where is DAO here”? Here it is: ExposedExpenseRepository. This is DAO/Repository in the applied sense. You hid Exposed inside, and the rest of the code does not need to know that you have SQL tables under the hood.

It is very important not to mix this up: the Exposed DAO API (with Entity) is a specific Exposed API. DAO as a pattern is an architectural role. Today we are talking about the pattern and using the Exposed DSL as a tool inside it.

7. Common mistakes

Mistake #1: running queries outside transaction {} and being surprised by strange crashes.
Exposed is designed so that most operations must be executed inside a transaction, because that is where the library manages the connection and execution context. If you try to “just select a little” outside, you will get errors like “no transaction in context” or similar surprises. The fix is not spells, but discipline: any read/write operations — only inside transaction.

Mistake #2: dragging ResultRow outside instead of a domain model.
At first it seems convenient to return a list of ResultRow and “let the caller pull out the fields.” But that instantly ties the whole project to Exposed. In a couple of days, row[Expenses.title] will start appearing in layers where the database should not exist at all. The right style is mapping to Expense right in the repository and returning clean models. And yes, map { ... } is your best friend — it has long been familiar from the standard library.

Mistake #3: not checking the result of update/deleteWhere.
If you update or delete a record by id, and the operation affected 0 rows — that is not “whatever,” that is “we thought the record exists, but it does not.” If it affected more than one row — that is even worse; it means the condition is wrong. The habit of comparing the result with the expected number of rows sharply increases program reliability.

Mistake #4: smearing transaction {} all over the code, including the UI/CLI layer.
The most common architectural failure looks like: “in main I read a command, immediately open a transaction, and immediately print to the console.” This mixes responsibilities: the storage layer starts living in the same place as the user scenario. It is much easier to maintain the project if transactions live inside the repository/DAO, and the outside sees methods at the “add/list/delete” level.

Mistake #5: thinking Exposed removes the need to understand SQL and the schema.
Exposed makes code shorter, but the meaning of operations remains SQL-like: SELECT reads, INSERT creates, UPDATE changes, DELETE deletes. Table constraints, keys, uniqueness, and transaction logic are still important. If you do not understand what the query does, the DSL will not save you — it will only make the mistake look more elegant.

Mistake #6: too much logic inside transaction {}.
A transaction should be short and tied to data consistency. If user input, large-text formatting, long computations, or “let me ask the user 20 more times” ends up inside, the transaction becomes long in time, and you create problems for yourself. Try to keep only DB operations and minimal related result handling inside the transaction.