DSL

Table
Insert
Read
Query
Pending

Table

In Exposed, the Table class is the core abstraction for defining database tables. This class provides methods to define various column types, constraints, and other table-specific properties.

Table is located in the org.jetbrains.exposed.v1.core package of the exposed-core module.

The following example defines a table with an auto-incrementing integer id column and string name and email column:

object Customer : Table("customers") {
    val id = integer("id").autoIncrement()
    val name = text("name")
    val email = text("email").uniqueIndex()
}

Insert

Exposed provides several functions to insert rows into a table:

Insert a single row

To create a new table row, use the .insert() function:

suspendTransaction(database) {
    Customer.insert {
        it[name] = "Mary"
        it[email] = "mary@xtec.dev"
    }
}

The .insert() function returns a InsertStatement object that contains the inserted row’s primary key value.

val id = Customer.insert {
    it[name] = "Susan"
    it[email] = "susan@xtec.dev"
} get Customer.id  // or }.get(Customer.id)

If the same row already exists in the table, it throws an exception.

Batch insert

.batchInsert() allows mapping a list of entities into table rows in a single SQL statement.

It is more efficient than using the insert query for each row as it initiates only one statement.

suspendTransaction {
    val customers = listOf(
        Pair("John", "john@xtec.dev"),
        Pair("Anne", "anne@xtec.dev")
    )
    Customer.batchInsert(customers) { (name, email) ->
        this[Customer.name] = name
        this[Customer.email] = email
    }
}

Read

Retrieve all records

To retrieve all records from a table, use the .selectAll() method:

Query inherits Iterable so it is possible to traverse it using .map() or .forEach():

suspendTransaction {

    val customers = listOf(
        Pair("John", "john@xtec.dev"),
        Pair("Anne", "anne@xtec.dev"),
        Pair("Mike", "mike@xtec.dev")
    )
    Customer.batchInsert(customers) { (name, email) ->
        this[Customer.name] = name
        this[Customer.email] = email
    }
}

suspendTransaction(database) {
    val result = Customer.selectAll().map { it[Customer.name] }.toList()
    result shouldBe listOf("John", "Anne", "Mike")
}

Retrieve a record

The .select() function allows you to select specific columns or/and expressions.

suspendTransaction {
    val result = Customer.select(Customer.email).toList()
    result.map { it[Customer.email] } shouldBe listOf("john@xtec.dev", "anne@xtec.dev", "mike@xtec.dev")

}

Exposed - DSL