PricingLoginStart free
Kotlin Academy · Lesson

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

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

Lesson 3 of 413 steps

CRUD with the Query DSL: insert, select, update, delete is a free Kotlin Academy lesson on CoddyKit. This is lesson 3 of 4. Any 3 lessons of this learning path are free to read in full — after that, CoddyKit PRO unlocks every lesson, plus hands-on practice with a built-in code editor and a 24/7 AI tutor. It is part of the Kotlin Academy learning path, and your progress syncs across the web and the CoddyKit app. The Kotlin Academy course includes 4 lessons in total.

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?

Answer it in the interactive lesson

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
Free to start

Learn Kotlin with an AI tutor — free

Write and run real code in your browser, get instant help from a 24/7 AI tutor, and pick up where you left off on the web or in the app.

Courses
51
Lessons
203
Start learning freeView the full course

Frequently Asked Questions

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

Yes — any 3 lessons of the Kotlin Academy learning path, including “CRUD with the Query DSL: insert, select, update, delete”, are free to read in full here on the web. After that, CoddyKit PRO unlocks every lesson, plus interactive practice with a built-in code editor and a 24/7 AI tutor. The Kotlin Academy course includes 4 lessons in total.

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, so you can start here or from the beginning and move at your own pace. This is lesson 3 of 4.

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