Get started with Exposed, an ORM framework for Kotlin

Prerequisites

The latest Gradle distribution.
A Java Development Kit (JDK), version 8 or higher.
An integrated development environment (IDE), such as IntelliJ IDEA Ultimate.
We recommend that you install IntelliJ IDEA Ultimate which comes with built-in database tools and the Exposed plugin for code completion and inspections. However, you can use another IDE of your choice.

Create a new project

In a terminal window, navigate to the destination where you want to create your project and run the following commands to create a new folder and change directory into it:
```
$
mkdir exposed-kotlin-app
cd exposed-kotlin-app
```

Run the gradle init task to initialize a new Gradle project:

$
gradle init

When prompted, select the following options:

1: Application project type.
2: Kotlin implementation language.

For the other questions, press enter to use the default values. The output will look like the following:

Select type of build to generate:
    1: Application
    2: Library
    3: Gradle plugin
    4: Basic (build structure only)
Enter selection (default: Application) [1..4]
Select implementation language:
    1: Java
    2: Kotlin
    3: Groovy
    4: Scala
    5: C++
    6: Swift
Enter selection (default: Java) [1..6] 2
Enter target Java version (min: 7, default: 21):
Project name (default: exposed-kotlin-app):
Select application structure:
    1: Single application project
    2: Application and library project
Enter selection (default: Single application project) [1..2]
Select build script DSL:
    1: Kotlin
    2: Groovy
Enter selection (default: Kotlin) [1..2]
Select test framework:
    1: kotlin.test
    2: JUnit Jupiter
Enter selection (default: kotlin.test) [1..2]
Generate build using new APIs and behavior (some features may change in the next minor release)? (default: no) [yes, no]
> Task :init
To learn more about Gradle by exploring our Samples at https://docs.gradle.org/8.8/samples/sample_building_kotlin_applications.html
BUILD SUCCESSFUL in 28s
1 actionable task: 1 executed

Once the project has been initialized, open the project folder in your IDE. To open the project in IntelliJ IDEA, use the following command:
```
idea .
```

Add dependencies

Navigate to the gradle/libs.versions.toml file and define the Exposed version and libraries:
```
[versions]
//...
exposed = "0.60.0"

[libraries]
//...
exposed-core = { module = "org.jetbrains.exposed:exposed-core", version.ref = "exposed" }
exposed-jdbc = { module = "org.jetbrains.exposed:exposed-jdbc", version.ref = "exposed" }
```
- The exposed-core module provides the foundational components and abstractions needed to work with databases in a type-safe manner and includes the DSL API.
- The exposed-jdbc module is an extension of the exposed-core module that adds support for Java Database Connectivity (JDBC).

Navigate to the app/build.gradle.kts file and add the Exposed and H2 database modules into the dependencies block:

dependencies {
    //...
    implementation(libs.exposed.core)
    implementation(libs.exposed.jdbc)
    implementation("com.h2database:h2:2.2.224")
    //...
}

In intelliJ IDEA, click on the notification Gradle icon () on the right side of the editor to load Gradle changes.

Configure a database connection

Every database access using Exposed is started by obtaining a connection and creating a transaction. To configure the database connection, use the Database.connect() function.

Navigate to app/src/main/kotlin/org/example/ and open the App.kt file.
Replace the contents of the App.kt file with the following implementation:
```
package org.example

import org.jetbrains.exposed.sql.*

fun main() {
    Database.connect("jdbc:h2:mem:test", driver = "org.h2.Driver")
}
```
The Database.connect() function creates an instance of a class that represents the database and takes two or more parameters. In this case, the connection URL and the driver.
- jdbc:h2:mem:test is the database URL to connect to:
  - jdbc specifies that this is a JDBC connection.
  - h2 indicates that the database is an H2 database.
  - mem specifies that the database is in-memory, meaning the data will only exist in memory and will be lost when the application stops.
  - test is the name of the database.
- org.h2.Driver specifies the H2 JDBC driver to be used for establishing the connection.
note
Note that invoking Database.connect() only configures the connection settings, but it does not immediately establish a connection with the database. The actual connection to the database will be established later when a database operation is performed.
note
By default, Exposed uses a ServiceLoader to get an implementation of the DatabaseConnectionAutoRegistration interface that represents a connection accessed by the Database instance. This can be modified when calling the Database.connect method by providing an argument to connectionAutoRegistration in the parameter list.
With this, you've added Exposed to your Kotlin project and configured a database connection. You're now ready to define your data model and engage with the database using Exposed's DSL API.

Define a table object

In the app/src/main/kotlin/org/example/ folder, create a new Task.kt file.
Open Task.kt and add the following table definition:
```
import org.jetbrains.exposed.sql.Table

const val MAX_VARCHAR_LENGTH = 128

object Tasks : Table("tasks") {
    val id = integer("id").autoIncrement()
    val title = varchar("name", MAX_VARCHAR_LENGTH)
    val description = varchar("description", MAX_VARCHAR_LENGTH)
    val isCompleted = bool("completed").default(false)
}
```
In the Table constructor, passing the name tasks configures a custom name for the table. Keep in mind that if no custom name is specified, Exposed will generate one from the class name, which might lead to unexpected results.
Within the Tasks object, four columns are defined:
- id of type Int is defined with the integer() method. The autoIncrement() function indicates that this column will be an auto-incrementing integer, typically used for primary keys.
- title and description of type String are defined with the varchar() method.
- isCompleted of type Boolean is defined with the bool() method. Using the default() function, you configure the default value to false.
At this point, you have defined a table with columns, which essentially creates the blueprint for the Tasks table.
To now create and populate the table within the database, you need to open a transaction.

Create and query a table

A transaction is represented by an instance of the Transaction class, within which you can define and manipulate data using its lambda function. Exposed will automatically manage the opening and closing of the transaction in the background, ensuring seamless operation.

Navigate back to the App.kt file and add the following transaction function:

package org.example

import Tasks
import org.jetbrains.exposed.sql.*
import org.jetbrains.exposed.sql.SqlExpressionBuilder.eq
import org.jetbrains.exposed.sql.transactions.transaction

fun main() {
    Database.connect("jdbc:h2:mem:test", driver = "org.h2.Driver")

    transaction {
        SchemaUtils.create(Tasks)

        val taskId = Tasks.insert {
            it[title] = "Learn Exposed"
            it[description] = "Go through the Get started with Exposed tutorial"
        } get Tasks.id

        val secondTaskId = Tasks.insert {
            it[title] = "Read The Hobbit"
            it[description] = "Read the first two chapters of The Hobbit"
            it[isCompleted] = true
        } get Tasks.id

        println("Created new tasks with ids $taskId and $secondTaskId.")

        Tasks.select(Tasks.id.count(), Tasks.isCompleted).groupBy(Tasks.isCompleted).forEach {
            println("${it[Tasks.isCompleted]}: ${it[Tasks.id.count()]} ")
        }
    }
}

Let's break down the code and go over each section.

First, you create the tasks table using the create() method from SchemaUtils. The SchemaUtils object holds utility methods for creating, altering, and dropping database objects.

Once the table has been created, you use the Table extension method insert() to add two new Task records.

val taskId = Tasks.insert {
    it[title] = "Learn Exposed"
    it[description] = "Go through the Get started with Exposed tutorial"
} get Tasks.id

val secondTaskId = Tasks.insert {
    it[title] = "Read The Hobbit"
    it[description] = "Read the first two chapters of The Hobbit"
    it[isCompleted] = true
} get Tasks.id

Within the insert block, you set the values for each column by using the it parameter. Exposed will translate the functions into the following SQL queries:

INSERT INTO TASKS (COMPLETED, DESCRIPTION, "name") VALUES (FALSE, 'Go through the Get started with Exposed tutorial', 'Learn Exposed')

INSERT INTO TASKS (COMPLETED, DESCRIPTION, "name") VALUES (TRUE, 'Read the first two chapters of The Hobbit', 'Read The Hobbit')

Because the insert() function returns an InsertStatement, by using the get() method after the insert operation you retrieve the autoincremented id value of the newly added row.

With the select() extension function you then create a query to count the number of rows and to retrieve the isCompleted value for each row in the table.

Tasks.select(Tasks.id.count(), Tasks.isCompleted).groupBy(Tasks.isCompleted).forEach {
    println("${it[Tasks.isCompleted]}: ${it[Tasks.id.count()]} ")
}

Using groupBy() groups the results of the query by the isCompleted column, which means it will aggregate the rows based on whether they are completed or not. The expected SQL query looks like this:

SELECT COUNT(TASKS.ID), TASKS.COMPLETED FROM TASKS GROUP BY TASKS.COMPLETED

It is important to note that the query will not be executed until you call a function that iterates through the result, such as forEach(). In this example, for each group we print out the isCompleted status and the corresponding count of tasks.

Enable logging

transaction {
    // print sql to std-out
    addLogger(StdOutSqlLogger)

    // ...
}

Run the application

SQL: SELECT VALUE FROM INFORMATION_SCHEMA.SETTINGS WHERE NAME = 'MODE'
SQL: CREATE TABLE IF NOT EXISTS TASKS (ID INT AUTO_INCREMENT NOT NULL, "name" VARCHAR(128) NOT NULL, DESCRIPTION VARCHAR(128) NOT NULL, COMPLETED BOOLEAN DEFAULT FALSE NOT NULL)
SQL: INSERT INTO TASKS (COMPLETED, DESCRIPTION, "name") VALUES (FALSE, 'Go through the Get started with Exposed tutorial', 'Learn Exposed')
SQL: INSERT INTO TASKS (COMPLETED, DESCRIPTION, "name") VALUES (TRUE, 'Read the first two chapters of The Hobbit', 'Read The Hobbit')
Created new tasks with ids 1 and 2.
SQL: SELECT COUNT(TASKS.ID), TASKS.COMPLETED FROM TASKS GROUP BY TASKS.COMPLETED
false: 1
true: 1

Process finished with exit code 0

Update and delete a task

In the same transaction() function, add the following code to your implementation:
```
transaction {
    // ...

    // Update a task
    Tasks.update({ Tasks.id eq taskId }) {
        it[isCompleted] = true
    }

    val updatedTask = Tasks.select(Tasks.isCompleted).where(Tasks.id eq taskId).single()

    println("Updated task details: $updatedTask")

    // Delete a task
    Tasks.deleteWhere { id eq secondTaskId }

    println("Remaining tasks: ${Tasks.selectAll().toList()}")
}
```
Here's the breakdown:
In the Tasks.update() function, you specify the condition to find the task with id equal to the one of the previously inserted task. If the condition is met, the isCompleted field of the found task is set to true.
```
Tasks.update({ Tasks.id eq taskId }) {
    it[isCompleted] = true
}
```
Unlike the insert() function, update() returns the number of updated rows. To then retrieve the updated task, you use the select() function with the where condition to only select the tasks with id equal to taskId.
```
val updatedTask = Tasks.select(Tasks.isCompleted).where(Tasks.id eq taskId).single()
```
Using the single() extension function initiates the statement and retrieves the first result found.
The deleteWhere() function, on the other hand, deletes the task with the specified condition.
```
Tasks.deleteWhere { id eq secondTaskId }
```
Similarly to update(), it returns the number of rows that have been deleted.

In IntelliJ IDEA, click the rerun button () to restart the application.

You should now see the following result:

SQL: SELECT VALUE FROM INFORMATION_SCHEMA.SETTINGS WHERE NAME = 'MODE'
SQL: CREATE TABLE IF NOT EXISTS TASKS (ID INT AUTO_INCREMENT NOT NULL, "name" VARCHAR(128) NOT NULL, DESCRIPTION VARCHAR(128) NOT NULL, COMPLETED BOOLEAN DEFAULT FALSE NOT NULL)
SQL: INSERT INTO TASKS (COMPLETED, DESCRIPTION, "name") VALUES (FALSE, 'Go through the Get started with Exposed tutorial', 'Learn Exposed')
SQL: INSERT INTO TASKS (COMPLETED, DESCRIPTION, "name") VALUES (TRUE, 'Read the first two chapters of The Hobbit', 'Read The Hobbit')
Created new tasks with ids 1 and 2.
SQL: SELECT COUNT(TASKS.ID), TASKS.COMPLETED FROM TASKS GROUP BY TASKS.COMPLETED
false: 1
true: 1
SQL: UPDATE TASKS SET COMPLETED=TRUE WHERE TASKS.ID = 1
SQL: SELECT TASKS.COMPLETED FROM TASKS WHERE TASKS.ID = 1
Updated task details: Tasks.completed=true
SQL: DELETE FROM TASKS WHERE TASKS.ID = 2
SQL: SELECT TASKS.ID, TASKS."name", TASKS.DESCRIPTION, TASKS.COMPLETED FROM TASKS
Remaining tasks: [Tasks.id=1, Tasks.name=Learn Exposed, Tasks.description=Go through the Get started with Exposed tutorial, Tasks.completed=true]

Process finished with exit code 0

tip
It might be useful to know that if a second transaction is opened after the first one, you will find that the table and its data has been lost even though the app hasn't stopped. This is expected behavior in H2 databases when managing connections and transactions.
To keep the database open, add ;DB_CLOSE_DELAY=-1 to the database URL:
Database.connect("jdbc:h2:mem:test;DB_CLOSE_DELAY=-1", driver = "org.h2.Driver")

Get started with Exposed, an ORM framework for Kotlin﻿

Prerequisites﻿

Create a new project﻿

Add dependencies﻿

Configure a database connection﻿

note

note

Define a table object﻿

Create and query a table﻿

Enable logging﻿

Run the application﻿

Update and delete a task﻿

tip

Next steps﻿