0Pricing
Kotlin Academy · Lesson

CRUD with the Query DSL: insert, select, update, delete

Perform all CRUD operations using Exposed's type-safe query DSL.

CRUD with the Query DSL: insert, select, update, delete is a free Kotlin Academy lesson on CoddyKit — lesson 3 of 4. You can read the complete lesson below for free — then practise it hands-on in the browser with a built-in code editor and a 24/7 AI tutor. It is part of the Kotlin Academy learning path, one of 4 lessons in the course, and your progress syncs across the web and the CoddyKit app.

Query DSL Overview

Exposed's Query DSL lets you write typesafe SQL operations using Kotlin functions. All operations are performed inside a transaction { } block. There are no magic strings — column references are Kotlin properties, so typos are caught at compile time.

Insert

Use Table.insert { } to add a row. The lambda receives an InsertStatement and you set column values using the [column] operator:

transaction {
    Users.insert {
        it[Users.name]  = "Alice"
        it[Users.email] = "alice@example.com"
        it[Users.age]   = 30
    }
}

Insert and Get Generated ID

Use insertAndGetId { } on an IdTable to retrieve the auto-generated primary key after the insert:

val newId: Long = transaction {
    Users.insertAndGetId {
        it[Users.name]  = "Bob"
        it[Users.email] = "bob@example.com"
    }.value
}

Batch Insert

Use batchInsert(list) { item -> ... } for efficient multi-row inserts. Exposed wraps them in a single prepared statement:

val newUsers = listOf("Carol" to "carol@e.com", "Dave" to "dave@e.com")
transaction {
    Users.batchInsert(newUsers) { (name, email) ->
        this[Users.name]  = name
        this[Users.email] = email
    }
}

Select All Rows

Use Table.selectAll() to fetch every row. Iterate the result as a sequence of ResultRow objects; access column values via row[Column]:

transaction {
    Users.selectAll().forEach { row ->
        println("${row[Users.id]}: ${row[Users.name]}")
    }
}

Select with a Where Clause

Chain .where { condition } (Exposed 0.46+) or .select { condition } to filter rows. Conditions use operator overloads (eq, like, greater, etc.):

transaction {
    Users.selectAll()
        .where { Users.age greater 25 }
        .forEach { println(it[Users.name]) }
}

Selecting Specific Columns

Pass a list of columns to .select(col1, col2) to retrieve only those columns — equivalent to SELECT col1, col2 FROM ...:

transaction {
    Users.select(Users.name, Users.email)
        .where { Users.age lessEq 30 }
        .map { it[Users.name] to it[Users.email] }
        .forEach(::println)
}

Update

Use Table.update({ condition }) { } to modify existing rows. The inner lambda receives an UpdateStatement; use [column] to set new values:

transaction {
    Users.update({ Users.id eq 1L }) {
        it[Users.name] = "Alice Updated"
        it[Users.age]  = 31
    }
}

Delete

Use Table.deleteWhere { condition } to remove rows matching the predicate. It returns the number of deleted rows:

transaction {
    val deleted = Users.deleteWhere { Users.id eq 5L }
    println("Deleted $deleted rows")
}

Ordering and Limiting

Chain .orderBy(column to SortOrder.ASC) and .limit(n) (with optional offset) to sort and paginate results:

transaction {
    Users.selectAll()
        .orderBy(Users.name to SortOrder.ASC)
        .limit(10, offset = 20)
        .map { it[Users.name] }
        .forEach(::println)
}

Aggregations and Count

Use standard SQL aggregate functions via Exposed's column operators: Users.id.count(), Users.age.avg(), Users.age.max(), etc.:

transaction {
    val total = Users.selectAll().count()
    println("Total users: $total")
}

Quick Check

Which Exposed function returns the auto-generated primary key after inserting a row into an IdTable?

Recap: CRUD with the Query DSL

Key takeaways:

  • insert { } — add a row; insertAndGetId { } — add and get the PK
  • selectAll().where { } — query with filter; chain .orderBy(), .limit()
  • update({ condition }) { } — modify rows; deleteWhere { } — remove rows
  • All operations run inside transaction { }
  • Column references are compile-time safe — no string column names

Frequently asked questions

Is the “CRUD with the Query DSL: insert, select, update, delete” lesson free?

Yes — the full text of “CRUD with the Query DSL: insert, select, update, delete” is free to read here on the web, and the Kotlin Academy course includes 4 lessons in total. To practise it interactively (a built-in code editor and a 24/7 AI tutor) and unlock the rest of the Kotlin Academy course, upgrade to CoddyKit PRO.

What will I learn in “CRUD with the Query DSL: insert, select, update, delete”?

Perform all CRUD operations using Exposed's type-safe query DSL. You practise Kotlin Academy with hands-on code you run directly in the browser, and a 24/7 AI tutor answers your questions as you work through the lesson.

Do I need any experience to start Kotlin Academy?

No prior experience is required. Kotlin Academy on CoddyKit is structured for beginners through advanced learners; this is — lesson 3 of 4, so you can start here or from the beginning and move at your own pace.

How long does the “CRUD with the Query DSL: insert, select, update, delete” lesson take?

Most CoddyKit lessons take about 5–10 minutes. Each one is bite-sized and interactive, so you make steady progress and pick up exactly where you left off across the web and the app.

Can I write and run code in this Kotlin Academy lesson?

Yes. Every Kotlin Academy lesson includes a built-in code editor, so you write and run real code right in your browser and get instant AI feedback — no local setup required.

All lessons in this course

  1. Exposed Setup: Database Connection and Transaction DSL
  2. Defining Tables with the Table DSL
  3. CRUD with the Query DSL: insert, select, update, delete
  4. Exposed DAO: Entity Classes and Relationships
← Back to Kotlin Academy