Database persistence with Exposed

In this series of tutorials, we'll show you how to create a simple blog application in Ktor:

In the first tutorial, we showed how to host static content like images and HTML pages.
In the second tutorial, we added interactivity to our application using the FreeMarker template engine.
In this tutorial, we'll add persistence to our website using the Exposed framework. We'll use the H2 local database to store articles.
In the next tutorial, we'll look at how to implement database connection pooling and caching using the HikariCP and Ehcache libraries, respectively.

Add dependencies

First, you need to add dependencies for the Exposed and H2 libraries. Open the gradle.properties file and specify library versions:

exposed_version = 0.41.1
h2_version = 2.1.214

Then, open build.gradle.kts and add the following dependencies:

val exposed_version: String by project
val h2_version: String by project

dependencies {
    implementation("org.jetbrains.exposed:exposed-core:$exposed_version")
    implementation("org.jetbrains.exposed:exposed-dao:$exposed_version")
    implementation("org.jetbrains.exposed:exposed-jdbc:$exposed_version")
    implementation("com.h2database:h2:$h2_version")
}

Click the Load Gradle Changes icon in the top right corner of the build.gradle.kts file to install newly added dependencies.

Update a model

Exposed uses the org.jetbrains.exposed.sql.Table class as a database table. To update the Article model, open the models/Article.kt file and replace the existing code with the following:

package com.example.models

import org.jetbrains.exposed.sql.*

data class Article(val id: Int, val title: String, val body: String)

object Articles : Table() {
    val id = integer("id").autoIncrement()
    val title = varchar("title", 128)
    val body = varchar("body", 1024)

    override val primaryKey = PrimaryKey(id)
}

The id, title, and body columns will store information about our articles. The id column will act as a primary key.

Connect to a database

A data access object (DAO) is a pattern that provides an interface to a database without exposing the details of the specific database. We'll define a DAOFacade interface later to abstract our specific requests to the database.

Every database access using Exposed is started by obtaining a connection to the database. For that, you pass JDBC URL and the driver class name to the Database.connect function. Create the dao package inside com.example and add a new DatabaseFactory.kt file. Then, insert this code:

package com.example.dao

import com.example.models.*
import kotlinx.coroutines.*
import org.jetbrains.exposed.sql.*
import org.jetbrains.exposed.sql.transactions.*
import org.jetbrains.exposed.sql.transactions.experimental.*

object DatabaseFactory {
    fun init() {
        val driverClassName = "org.h2.Driver"
        val jdbcURL = "jdbc:h2:file:./build/db"
        val database = Database.connect(jdbcURL, driverClassName)
    }
}

Create a table

After obtaining the connection, all SQL statements should be placed inside a transaction:

fun init() {
    // ...
    val database = Database.connect(jdbcURL, driverClassName)
    transaction(database) {
        // Statements here
    }
}

In this code sample, the default database is passed explicitly to the transaction function. If you have only one database, you can omit it. In this case, Exposed automatically uses the last connected database for transactions.

Given that the Articles table is already declared, we can call SchemaUtils.create(Articles) wrapped in transaction call at the bottom of the init function to instruct the database to create this table if it doesn't yet exist:

fun init() {
    // ...
    val database = Database.connect(jdbcURL, driverClassName)
    transaction(database) {
        SchemaUtils.create(Articles)
    }
}

Execute queries

For our convenience, let's create a utility function dbQuery inside the DatabaseFactory object, which we'll be using for all future requests to the database. Instead of using the transaction to access it in a blocking way, let's take advantage of coroutines and start each query in its own coroutine:

    suspend fun <T> dbQuery(block: suspend () -> T): T =
        newSuspendedTransaction(Dispatchers.IO) { block() }

The resulting DatabaseFactory.kt file should look as follows:

package com.example.dao

import com.example.models.*
import kotlinx.coroutines.*
import org.jetbrains.exposed.sql.*
import org.jetbrains.exposed.sql.transactions.*
import org.jetbrains.exposed.sql.transactions.experimental.*

object DatabaseFactory {
    fun init() {
        val driverClassName = "org.h2.Driver"
        val jdbcURL = "jdbc:h2:file:./build/db"
        val database = Database.connect(jdbcURL, driverClassName)
        transaction(database) {
            SchemaUtils.create(Articles)
        }
    }

    suspend fun <T> dbQuery(block: suspend () -> T): T =
        newSuspendedTransaction(Dispatchers.IO) { block() }
}

Load database config at startup

Finally, we need to load the created configuration at the application startup. Open Application.kt and call DatabaseFactory.init from the Application.module body:

import com.example.dao.*

fun Application.module() {
    DatabaseFactory.init()
    configureRouting()
    configureTemplating()
}

Implement persistence logic

Now let's create an interface to abstract the necessary operations for updating articles. Create the DAOFacade.kt file inside the dao package and fill it with the following code:

package com.example.dao

import com.example.models.*

interface DAOFacade {
    suspend fun allArticles(): List<Article>
    suspend fun article(id: Int): Article?
    suspend fun addNewArticle(title: String, body: String): Article?
    suspend fun editArticle(id: Int, title: String, body: String): Boolean
    suspend fun deleteArticle(id: Int): Boolean
}

We need to list all articles, view an article by its ID, add a new article, edit, or delete it. Since all these functions perform database queries under the hood, they are defined as suspending functions.

To implement the DAOFacade interface, place the caret at its name, click a yellow bulb icon next to this interface and select Implement interface. In the invoked dialog, leave the default settings and click OK.

In the Implement Members dialog, select all the functions and click OK.

IntelliJ IDEA creates the DAOFacadeImpl.kt file inside the dao package. Let's implement all functions using Exposed DSL.

Get all articles

Let's start with a function returning all entries. Our request is wrapped into a dbQuery call. We call the Table.selectAll extension function to get all the data from the database. The Articles object is a subclass of Table, so we use Exposed DSL methods to work with it.

package com.example.dao

import com.example.dao.DatabaseFactory.dbQuery
import com.example.models.*
import kotlinx.coroutines.runBlocking
import org.jetbrains.exposed.sql.*
import org.jetbrains.exposed.sql.SqlExpressionBuilder.eq

class DAOFacadeImpl : DAOFacade {
    private fun resultRowToArticle(row: ResultRow) = Article(
        id = row[Articles.id],
        title = row[Articles.title],
        body = row[Articles.body],
    )

    override suspend fun allArticles(): List<Article> = dbQuery {
        Articles.selectAll().map(::resultRowToArticle)
    }
}

Table.selectAll returns an instance of Query, so to get the list of Article instances, we need to manually extract data for each row and convert it to our data class. We accomplish that using the helper function resultRowToArticle that builds an Article from the ResultRow.

The ResultRow provides a way to get the data stored in the specified Column by using a concise get operator, allowing us to use the bracket syntax, similar to an array or a map.

Get an article

Now let's implement a function returning one article:

    override suspend fun article(id: Int): Article? = dbQuery {
        Articles
            .select { Articles.id eq id }
            .map(::resultRowToArticle)
            .singleOrNull()
    }

The select function takes an extension lambda as an argument. The implicit receiver inside this lambda is of type SqlExpressionBuilder. You don't use this type explicitly, but it defines a bunch of useful operations on columns, which you use to build your queries. You can use comparisons (eq, less, greater), arithmetic operations (plus, times), check whether value belongs or doesn't belong to a provided list of values (inList, notInList), check whether the value is null or non-null, and many more.

select returns a list of Query values. As before, we convert them to articles. In our case, it should be one article, so we return it as a result.

Add a new article

To insert a new article into the table, use the Table.insert function, which takes a lambda argument:

    override suspend fun addNewArticle(title: String, body: String): Article? = dbQuery {
        val insertStatement = Articles.insert {
            it[Articles.title] = title
            it[Articles.body] = body
        }
        insertStatement.resultedValues?.singleOrNull()?.let(::resultRowToArticle)
    }

Inside this lambda, we specify which value is supposed to be set for which column. The it argument has a type InsertStatement on which we can call the set operator taking column and value as arguments.

Edit an article

To update the existing article, the Table.update is used:

    override suspend fun editArticle(id: Int, title: String, body: String): Boolean = dbQuery {
        Articles.update({ Articles.id eq id }) {
            it[Articles.title] = title
            it[Articles.body] = body
        } > 0
    }

Delete an article

Finally, use Table.deleteWhere to remove an article from the database:

    override suspend fun deleteArticle(id: Int): Boolean = dbQuery {
        Articles.deleteWhere { Articles.id eq id } > 0
    }

Initialize DAOFacade

Let's create an instance of DAOFacade and add a sample article into be inserted to the database before the application is started. Add the following code at the bottom of DAOFacadeImpl.kt:

val dao: DAOFacade = DAOFacadeImpl().apply {
    runBlocking {
        if(allArticles().isEmpty()) {
            addNewArticle("The drive to develop!", "...it's what keeps me going.")
        }
    }
}

Update routes

Now we are ready to use implemented database operations inside route handlers. Open the plugins/Routing.kt file. To show all articles, call dao.allArticles inside the get handler:

            get {
                call.respond(FreeMarkerContent("index.ftl", mapOf("articles" to dao.allArticles())))
            }

To post a new article, call the dao.addNewArticle function inside post:

            post {
                val formParameters = call.receiveParameters()
                val title = formParameters.getOrFail("title")
                val body = formParameters.getOrFail("body")
                val article = dao.addNewArticle(title, body)
                call.respondRedirect("/articles/${article?.id}")
            }

To get an article for showing and editing, use dao.article inside get("{id}") and get("{id}/edit"), respectively:

            get("{id}") {
                val id = call.parameters.getOrFail<Int>("id").toInt()
                call.respond(FreeMarkerContent("show.ftl", mapOf("article" to dao.article(id))))
            }
            get("{id}/edit") {
                val id = call.parameters.getOrFail<Int>("id").toInt()
                call.respond(FreeMarkerContent("edit.ftl", mapOf("article" to dao.article(id))))
            }

Finally, go to the post("{id}") handler and use dao.editArticle to update an article and dao.deleteArticle to delete it:

            post("{id}") {
                val id = call.parameters.getOrFail<Int>("id").toInt()
                val formParameters = call.receiveParameters()
                when (formParameters.getOrFail("_action")) {
                    "update" -> {
                        val title = formParameters.getOrFail("title")
                        val body = formParameters.getOrFail("body")
                        dao.editArticle(id, title, body)
                        call.respondRedirect("/articles/$id")
                    }
                    "delete" -> {
                        dao.deleteArticle(id)
                        call.respondRedirect("/articles")
                    }
                }
            }

Run the application

Let's see if our journal application is performing as expected. We can run our application by pressing the Run button next to fun main(...) in our Application.kt:

IntelliJ IDEA will start the application, and after a few seconds, we should see the confirmation that the app is running:

[main] INFO Application - Responding at http://0.0.0.0:8080

Open http://localhost:8080/ in a browser and try to create, edit, and delete articles. Articles will be saved in the build/db.mv.db file. In IntelliJ IDEA, you can see the content of this file in a Database tool window.

Last modified: 14 December 2022