Get started with Exposed's DAO API

In this tutorial, you’ll learn how to use Exposed’s Data Access Object (DAO) API to store and retrieve data in a relational database by building a simple console application.

By the end of this tutorial, you’ll be able to do the following:

• Configure a database connection using an in-memory database.
• Define database tables and corresponding DAO entities.
• Perform basic CRUD (Create, Read, Update, and Delete) operations using object-oriented style.

Add dependencies

Navigate to the gradle/libs.versions.toml file and define the Exposed and H2 versions and artifacts:

[versions]
//...
exposed = "%exposed_version%"
h2 = "%h2_db_version%"

[libraries]
//...
exposed-core = { module = "org.jetbrains.exposed:exposed-core", version.ref = "exposed" }
exposed-dao = { module = "org.jetbrains.exposed:exposed-dao", version.ref = "exposed" }
exposed-jdbc = { module = "org.jetbrains.exposed:exposed-jdbc", version.ref = "exposed" }
h2 = { module = "com.h2database:h2", version.ref = "h2" }

• 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-dao module allows you to work with the Data Access Object (DAO) API.
• The exposed-jdbc module is an extension of the <code>exposed-core</code> 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.dao)
    implementation(libs.exposed.jdbc)
    implementation(libs.h2)
    //...
}

Configure a database connection

Whenever you access a database using Exposed, you start by obtaining a connection and creating a transaction. To configure the database connection, use the Database.connect() function.

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 DAO API.

Define a table object

Exposed's DAO API provides the base IdTable class and its subclasses to define tables that use a standard id column as the primary key. To define the table object, follow the steps below.

In the app/src/main/kotlin/org/example/ folder, create a new Task.kt file.

Open Task.kt and add the following table definition:

{src="get-started-with-exposed-dao/src/main/kotlin/org/example/Task.kt" include-lines="1-2,5,8,10-14"}

In the IntIdTable constructor, passing the name tasks configures a custom name for the table. If you don't provide a name, Exposed will derive it from the object name, which may lead to unexpected results depending on naming conventions.

The Tasks object defines the following columns:

• title and description are String columns, created using the varchar() function. Each column has a maximum length of 128 characters.
• isCompleted is a Boolean column, defined using the bool() function. Using the default(false) call, you configure the default value to false.

The IntIdTable class automatically adds an auto-incrementing integer id column as the primary key for the table. At this point, you have defined a table with columns, which essentially creates the blueprint for the tasks table.

Define an entity

When using the DAO approach, each table defined using IntIdTable must be associated with a corresponding entity class. The entity class represents individual records in the table and is uniquely identified by a primary key.

To define the entity, update your Task.kt file with the following code:

{src="get-started-with-exposed-dao/src/main/kotlin/org/example/Task.kt" include-lines="3-4,6-8,15-28"}

• Task extends IntEntity, which is a base class for entities with an Int-based primary key.
• The EntityID<Int> parameter represents the primary key of the database row this entity maps to.
• The companion object extends IntEntityClass<Task>, linking the entity class to the Tasks table.
• Each property (title, description, and isCompleted) is delegated to its corresponding column in the Tasks table using Kotlin's by keyword.
• The toString() function customizes how a Task instance is represented as a string. This is especially useful for debugging or logging. When printed, the output will include the entity’s ID, title, and completion status.

Create and query a table

With Exposed’s DAO API, you can interact with your database using a type-safe, object-oriented syntax similar to working with regular Kotlin classes. When executing any database operations, you must run them inside a <emphasis>transaction</emphasis>.

Open your App.kt file and add the following transaction function:

{src="get-started-with-exposed-dao/src/main/kotlin/org/example/App.kt" include-lines="1-2,4-11,16-33,42-43"}

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

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

{src="get-started-with-exposed-dao/src/main/kotlin/org/example/App.kt" include-symbol="task1,task2"}

In this example, task1 and task2 are instances of the Task entity, each representing a new row in the Tasks table. Within the new block, you set the values for each column. Exposed will translate the functions into the following SQL queries:

INSERT INTO TASKS ("name", DESCRIPTION, COMPLETED) VALUES ('Learn Exposed DAO', 'Follow the DAO tutorial', FALSE)
INSERT INTO TASKS ("name", DESCRIPTION, COMPLETED) VALUES ('Read The Hobbit', 'Read chapter one', TRUE)

With the .find() method you then perform a filtered query, retrieving all tasks where isCompleted is true:

{src="get-started-with-exposed-dao/src/main/kotlin/org/example/App.kt" include-symbol="completed"}

Before you test the code, it would be handy to be able to inspect the SQL statements and queries Exposed sends to the database. For this, you need to add a logger.

Enable logging

At the beginning of your transaction block, add the following to enable SQL query logging:

{src="get-started-with-exposed-dao/src/main/kotlin/org/example/App.kt" include-lines="3,7-8,11-14,42-43"}

Run the application

The application will start in the Run tool window at the bottom of the IDE. There you will be able to see the SQL logs along with the printed results:

SQL: SELECT SETTING_VALUE FROM INFORMATION_SCHEMA.SETTINGS WHERE SETTING_NAME = 'MODE'
SQL: CREATE TABLE IF NOT EXISTS TASKS (ID INT AUTO_INCREMENT PRIMARY KEY, "name" VARCHAR(128) NOT NULL, DESCRIPTION VARCHAR(128) NOT NULL, COMPLETED BOOLEAN DEFAULT FALSE NOT NULL)
SQL: INSERT INTO TASKS ("name", DESCRIPTION, COMPLETED) VALUES ('Learn Exposed DAO', 'Follow the DAO tutorial', FALSE)
SQL: INSERT INTO TASKS ("name", DESCRIPTION, COMPLETED) VALUES ('Read The Hobbit', 'Read chapter one', TRUE)
Created new tasks with ids 1 and 2
SQL: SELECT TASKS.ID, TASKS."name", TASKS.DESCRIPTION, TASKS.COMPLETED FROM TASKS WHERE TASKS.COMPLETED = TRUE
Completed tasks: 1

Update and delete a task

Let’s extend the app’s functionality by updating and deleting a task.

In the same transaction() function, add the following code to your implementation:

{src="get-started-with-exposed-dao/src/main/kotlin/org/example/App.kt" include-lines="11,13-15,34-42"}

You update the value of a property just as you would with any property in a Kotlin class:

{src="get-started-with-exposed-dao/src/main/kotlin/org/example/App.kt" include-lines="35-36"}

Similarly, to delete a task, you use the .delete() method on the entity:

{src="get-started-with-exposed-dao/src/main/kotlin/org/example/App.kt" include-lines="40"}

You should now see the following result:

SQL: SELECT SETTING_VALUE FROM INFORMATION_SCHEMA.SETTINGS WHERE SETTING_NAME = 'MODE'
SQL: CREATE TABLE IF NOT EXISTS TASKS (ID INT AUTO_INCREMENT PRIMARY KEY, "name" VARCHAR(128) NOT NULL, DESCRIPTION VARCHAR(128) NOT NULL, COMPLETED BOOLEAN DEFAULT FALSE NOT NULL)
SQL: INSERT INTO TASKS ("name", DESCRIPTION, COMPLETED) VALUES ('Learn Exposed DAO', 'Follow the DAO tutorial', FALSE)
SQL: INSERT INTO TASKS ("name", DESCRIPTION, COMPLETED) VALUES ('Read The Hobbit', 'Read chapter one', TRUE)
Created new tasks with ids 1 and 2
SQL: SELECT TASKS.ID, TASKS."name", TASKS.DESCRIPTION, TASKS.COMPLETED FROM TASKS WHERE TASKS.COMPLETED = TRUE
Completed tasks: 1
Updated task1: Task(id=1, title=Try Exposed DAO, completed=true)
SQL: UPDATE TASKS SET COMPLETED=TRUE, "name"='Try Exposed DAO' WHERE ID = 1
SQL: DELETE FROM TASKS WHERE TASKS.ID = 2
SQL: SELECT TASKS.ID, TASKS."name", TASKS.DESCRIPTION, TASKS.COMPLETED FROM TASKS
Remaining tasks: [Task(id=1, title=Try Exposed DAO, completed=true)]

Exposed does not immediately issue an UPDATE statement when you modify entity properties such as task1.title or task1.isCompleted. Instead, it caches those changes in memory and flushes them to the database right before the next read operation or at the end of the transaction:

genericCopy

SQL: UPDATE TASKS SET COMPLETED=TRUE, "name"='Try Exposed DAO' WHERE ID = 1

{style="note"}

Next steps

Great job! You've built a simple console application using Exposed's DAO API to create, query, and manipulate task data in an in-memory database.

Now that you've covered the fundamentals, you're ready to dive deeper into what the DAO API offers. Continue exploring CRUD operations or learn how to define relationships between entities. These next chapters will help you build more complex, real-world data models using Exposed’s type-safe, object-oriented approach.