Integrate a database with Kotlin, Ktor, and Exposed

1. Navigate to the Ktor Project Generator.

2. In the Project artifact field, enter com.example.ktor-exposed-task-app as the name of your project artifact.

   

3. In the plugins section search for and add the following plugins by clicking on the Add button:

   • Routing

   • Content Negotiation

   • Kotlinx.serialization

   • Static Content

   • Status Pages

   • Exposed

   • Postgres

4. Once you have added the plugins, click on the 7 plugins button at the top right of the plugins section to see the added plugins.

   You will then see a list of all the plugins that will be added to your project:

   

5. Click the Download button to generate and download your Ktor project.

6. Open the generated project in IntelliJ IDEA or another IDE of your choice.

7. Navigate to src/main/kotlin/plugins and delete the files CitySchema.kt and UsersSchema.kt.

8. Open the Databases.kt file and remove the content of the configureDatabases() function.

   fun Application.configureDatabases() { }

   The reason for removing this functionality is that the Ktor Project Generator has added sample code to show how to persist data about users and cities to HSQLDB or PostgreSQL. You will not be requiring that sample code in this tutorial.

1. Navigate to src/main/kotlin/com/example and create a subpackage called model.

2. Inside the model package, create a new Task.kt file.

3. Open Task.kt and add an enum to represent priorities and a class to represent tasks.

   import kotlinx.serialization.Serializable enum class Priority { Low, Medium, High, Vital } @Serializable data class Task( val name: String, val description: String, val priority: Priority )

   The Task class is annotated with the Serializable type from the kotlinx.serialization library.

   As in previous tutorials, you will create an in-memory repository. However, this time the repository will implement an interface so that you can easily replace it later.

4. In the model subpackage, create a new TaskRepository.kt file.

5. Open TaskRepository.kt and add the following interface:

   package com.example.model import Priority import Task interface TaskRepository { fun allTasks(): List<Task> fun tasksByPriority(priority: Priority): List<Task> fun taskByName(name: String): Task? fun addTask(task: Task) fun removeTask(name: String): Boolean }

6. Create a new FakeTaskRepository.kt file inside the same directory.

7. Open FakeTaskRepository.kt and add the following class:

   package com.example import Priority import Task import com.example.model.TaskRepository class FakeTaskRepository : TaskRepository { private val tasks = mutableListOf( Task("cleaning", "Clean the house", Priority.Low), Task("gardening", "Mow the lawn", Priority.Medium), Task("shopping", "Buy the groceries", Priority.High), Task("painting", "Paint the fence", Priority.Medium) ) override suspend fun allTasks(): List<Task> = tasks override suspend fun tasksByPriority(priority: Priority) = tasks.filter { it.priority == priority } override suspend fun taskByName(name: String) = tasks.find { it.name.equals(name, ignoreCase = true) } override suspend fun addTask(task: Task) { if (taskByName(task.name) != null) { throw IllegalStateException("Cannot duplicate task names!") } tasks.add(task) } override suspend fun removeTask(name: String): Boolean { return tasks.removeIf { it.name == name } } }

1. Open the Serialization.kt file in src/main/kotlin/com/example/plugins.

2. Replace the existing Application.configureSerialization() function with the implementation below:

   package com.example.plugins import Task import com.example.model.TaskRepository import io.ktor.http.* import io.ktor.serialization.* import io.ktor.serialization.kotlinx.json.* import io.ktor.server.application.* import io.ktor.server.plugins.contentnegotiation.* import io.ktor.server.request.* import io.ktor.server.response.* import io.ktor.server.routing.* fun Application.configureSerialization(repository: TaskRepository) { install(ContentNegotiation) { json() } routing { route("/tasks") { get { val tasks = repository.allTasks() call.respond(tasks) } get("/byName/{taskName}") { val name = call.parameters["taskName"] if (name == null) { call.respond(HttpStatusCode.BadRequest) return@get } val task = repository.taskByName(name) if (task == null) { call.respond(HttpStatusCode.NotFound) return@get } call.respond(task) } get("/byPriority/{priority}") { val priorityAsText = call.parameters["priority"] if (priorityAsText == null) { call.respond(HttpStatusCode.BadRequest) return@get } try { val priority = Priority.valueOf(priorityAsText) val tasks = repository.tasksByPriority(priority) if (tasks.isEmpty()) { call.respond(HttpStatusCode.NotFound) return@get } call.respond(tasks) } catch (ex: IllegalArgumentException) { call.respond(HttpStatusCode.BadRequest) } } post { try { val task = call.receive<Task>() repository.addTask(task) call.respond(HttpStatusCode.NoContent) } catch (ex: IllegalStateException) { call.respond(HttpStatusCode.BadRequest) } catch (ex: JsonConvertException) { call.respond(HttpStatusCode.BadRequest) } } delete("/{taskName}") { val name = call.parameters["taskName"] if (name == null) { call.respond(HttpStatusCode.BadRequest) return@delete } if (repository.removeTask(name)) { call.respond(HttpStatusCode.NoContent) } else { call.respond(HttpStatusCode.NotFound) } } } } }

   This is the same routing that you implemented in the Create RESTful APIs tutorial, except that you are now passing the repository into the routing() function as a parameter. Because the type of the parameter is an interface, many different implementations can be injected.

   Now that you have added a parameter to configureSerialization(), existing calls will no longer compile. Fortunately, this function is only called once.

3. Open the Application.kt file within src/main/kotlin/com/example.

4. Replace the module() function with the implementation below:

   import com.example.FakeTaskRepository //... fun Application.module() { val repository = com.example.FakeTaskRepository() configureSerialization(repository) configureDatabases() configureRouting() }

   You are now injecting an instance of your com.example.FakeTaskRepository into configureSerialization(). The long-term goal is to be able to replace this with a PostgresTaskRepository.

1. Open the index.html file in src/main/resources/static.

2. Replace the current content with the implementation below:

   <html> <head> <title>A Simple SPA For Tasks</title> <script type="application/javascript"> function displayAllTasks() { clearTasksTable(); fetchAllTasks().then(displayTasks) } function displayTasksWithPriority() { clearTasksTable(); const priority = readTaskPriority(); fetchTasksWithPriority(priority).then(displayTasks) } function displayTask(name) { fetchTaskWithName(name).then(t => taskDisplay().innerHTML = `${t.priority} priority task ${t.name} with description "${t.description}"` ) } function deleteTask(name) { deleteTaskWithName(name).then(() => { clearTaskDisplay(); displayAllTasks(); }) } function deleteTaskWithName(name) { return sendDELETE(`/tasks/${name}`) } function addNewTask() { const task = buildTaskFromForm(); sendPOST("/tasks", task).then(displayAllTasks); } function buildTaskFromForm() { return { name: getTaskFormValue("newTaskName"), description: getTaskFormValue("newTaskDescription"), priority: getTaskFormValue("newTaskPriority") } } function getTaskFormValue(controlName) { return document.addTaskForm[controlName].value; } function taskDisplay() { return document.getElementById("currentTaskDisplay"); } function readTaskPriority() { return document.priorityForm.priority.value } function fetchTasksWithPriority(priority) { return sendGET(`/tasks/byPriority/${priority}`); } function fetchTaskWithName(name) { return sendGET(`/tasks/byName/${name}`); } function fetchAllTasks() { return sendGET("/tasks") } function sendGET(url) { return fetch( url, {headers: {'Accept': 'application/json'}} ).then(response => { if (response.ok) { return response.json() } return []; }); } function sendPOST(url, data) { return fetch(url, { method: 'POST', headers: {'Content-Type': 'application/json'}, body: JSON.stringify(data) }); } function sendDELETE(url) { return fetch(url, { method: "DELETE" }); } function tasksTable() { return document.getElementById("tasksTableBody"); } function clearTasksTable() { tasksTable().innerHTML = ""; } function clearTaskDisplay() { taskDisplay().innerText = "None"; } function displayTasks(tasks) { const tasksTableBody = tasksTable() tasks.forEach(task => { const newRow = taskRow(task); tasksTableBody.appendChild(newRow); }); } function taskRow(task) { return tr([ td(task.name), td(task.priority), td(viewLink(task.name)), td(deleteLink(task.name)), ]); } function tr(children) { const node = document.createElement("tr"); children.forEach(child => node.appendChild(child)); return node; } function td(content) { const node = document.createElement("td"); if (content instanceof Element) { node.appendChild(content) } else { node.appendChild(document.createTextNode(content)); } return node; } function viewLink(taskName) { const node = document.createElement("a"); node.setAttribute( "href", `javascript:displayTask("${taskName}")` ) node.appendChild(document.createTextNode("view")); return node; } function deleteLink(taskName) { const node = document.createElement("a"); node.setAttribute( "href", `javascript:deleteTask("${taskName}")` ) node.appendChild(document.createTextNode("delete")); return node; } </script> </head> <body onload="displayAllTasks()"> <h1>Task Manager Client</h1> <form action="javascript:displayAllTasks()"> <span>View all the tasks</span> <input type="submit" value="Go"> </form> <form name="priorityForm" action="javascript:displayTasksWithPriority()"> <span>View tasks with priority</span> <select name="priority"> <option name="Low">Low</option> <option name="Medium">Medium</option> <option name="High">High</option> <option name="Vital">Vital</option> </select> <input type="submit" value="Go"> </form> <form name="addTaskForm" action="javascript:addNewTask()"> <span>Create new task with</span> <label for="newTaskName">name</label> <input type="text" id="newTaskName" name="newTaskName" size="10"> <label for="newTaskDescription">description</label> <input type="text" id="newTaskDescription" name="newTaskDescription" size="20"> <label for="newTaskPriority">priority</label> <select id="newTaskPriority" name="newTaskPriority"> <option name="Low">Low</option> <option name="Medium">Medium</option> <option name="High">High</option> <option name="Vital">Vital</option> </select> <input type="submit" value="Go"> </form> <hr> <div> Current task is <em id="currentTaskDisplay">None</em> </div> <hr> <table> <thead> <tr> <th>Name</th> <th>Priority</th> <th></th> <th></th> </tr> </thead> <tbody id="tasksTableBody"> </tbody> </table> </body> </html>

   This is the same SPA that was used in previous tutorials. Because it is written in JavaScript and only uses libraries available within the browser, you do not have to worry about client-side dependencies.

1. In IntelliJ IDEA, click on the run button () to start the application.

2. Navigate to http://0.0.0.0:8080/static/index.html in your browser. You should see the client page consisting of three forms and a table displaying the filtered results.

   

3. Test the application by filling out and sending the forms using the Go buttons. Use the View and Delete buttons on the table items.

1. Open ApplicationTest.kt in src/test/kotlin/com/example and add the following tests:

   package com.example import Task import com.example.plugins.* import io.ktor.client.call.* import io.ktor.client.plugins.contentnegotiation.* import io.ktor.client.request.* import io.ktor.http.* import io.ktor.serialization.kotlinx.json.json import io.ktor.server.testing.* import kotlin.test.* class ApplicationTest { @Test fun tasksCanBeFoundByPriority() = testApplication { application { val repository = FakeTaskRepository() configureSerialization(repository) configureRouting() } val client = createClient { install(ContentNegotiation) { json() } } val response = client.get("/tasks/byPriority/Medium") val results = response.body<List<Task>>() assertEquals(HttpStatusCode.OK, response.status) val expectedTaskNames = listOf("gardening", "painting") val actualTaskNames = results.map(Task::name) assertContentEquals(expectedTaskNames, actualTaskNames) } @Test fun invalidPriorityProduces400() = testApplication { val response = client.get("/tasks/byPriority/Invalid") assertEquals(HttpStatusCode.BadRequest, response.status) } @Test fun unusedPriorityProduces404() = testApplication { application { module() } val response = client.get("/tasks/byPriority/Vital") assertEquals(HttpStatusCode.NotFound, response.status) } @Test fun newTasksCanBeAdded() = testApplication { application { module() } val client = createClient { install(ContentNegotiation) { json() } } val task = Task("swimming", "Go to the beach", Priority.Low) val response1 = client.post("/tasks") { header( HttpHeaders.ContentType, ContentType.Application.Json ) setBody(task) } assertEquals(HttpStatusCode.NoContent, response1.status) val response2 = client.get("/tasks") assertEquals(HttpStatusCode.OK, response2.status) val taskNames = response2 .body<List<Task>>() .map { it.name } assertContains(taskNames, "swimming") } }

   For these tests to compile and run, you will need to add a dependency on the Content Negotiation plugin for the Ktor Client.

2. Open the build.gradle.kts and add the following dependency:

   testImplementation("io.ktor:ktor-client-content-negotiation-jvm:$ktor_version")

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

4. In IntelliJ IDEA, click on the run button () next to the test class definition to run the tests.

   You should then see that the tests ran successfully in the Run pane.

   

1. Using your database management tool of choice, create a new database within PostgreSQL. The name does not matter as long as you remember it. In this example, we will use ktor_tutorial_db.

2. Run the SQL commands below against your database. These commands will create and populate the database schema:

   DROP TABLE IF EXISTS task; CREATE TABLE task(id SERIAL PRIMARY KEY, name VARCHAR(50), description VARCHAR(50), priority VARCHAR(50)); INSERT INTO task (name, description, priority) VALUES ('cleaning', 'Clean the house', 'Low'); INSERT INTO task (name, description, priority) VALUES ('gardening', 'Mow the lawn', 'Medium'); INSERT INTO task (name, description, priority) VALUES ('shopping', 'Buy the groceries', 'High'); INSERT INTO task (name, description, priority) VALUES ('painting', 'Paint the fence', 'Medium'); INSERT INTO task (name, description, priority) VALUES ('exercising', 'Walk the dog', 'Medium'); INSERT INTO task (name, description, priority) VALUES ('meditating', 'Contemplate the infinite', 'High');

   Note the following:

   • You are creating a single table called task, with columns for the name, description, and priority. These will need to be mapped to the properties of the Task class.

   • You are re-creating the table if it already exists, so you can run the script repeatedly.

   • There is an additional column called id, which is of type SERIAL. This will be an integer value, used to give each row its primary key. These values will be assigned by the database on your behalf.

When executing queries against the database, it's preferable for them to run asynchronously to avoid blocking the thread handling the HTTP request. In Kotlin, this is best managed through coroutines.

1. Open the TaskRepository.kt file in src/main/kotlin/com/example/model.

2. Add the suspend keyword to all interface methods:

   interface TaskRepository { suspend fun allTasks(): List<Task> suspend fun tasksByPriority(priority: Priority): List<Task> suspend fun taskByName(name: String): Task? suspend fun addTask(task: Task) suspend fun removeTask(name: String): Boolean }

   This will allow implementations of the interface methods to start jobs of work on a different Coroutine Dispatcher.

   You now need to adjust the methods of the com.example.FakeTaskRepository to match, although you do not need to switch Dispatcher in that implementation.

3. Open the com.example.FakeTaskRepository.kt file and add the suspend keyword to all methods:

   class com.example.FakeTaskRepository : TaskRepository { private val tasks = mutableListOf( ... ) override suspend fun allTasks(): List<Task> = tasks override suspend fun tasksByPriority(priority: Priority) = tasks.filter { ... } override suspend fun taskByName(name: String) = tasks.find { ... } override suspend fun addTask(task: Task) { ... } override suspend fun removeTask(name: String): Boolean { ... } }

   Up to this point, you haven’t introduced any new functionality. Instead, you’ve laid the groundwork for creating a PostgresTaskRepository that will asynchronously run queries against the database.

In the first part of this tutorial, you deleted the sample code in the configureDatabases() method, found within Databases.kt. You are now ready to add in our own implementation.

1. Open the Databases.kt file in src/main/kotlin/com/example/plugins.

2. Use the Database.connect() function to connect to your database, adjusting the values of the settings to match your environment:

   fun Application.configureDatabases() { Database.connect( "jdbc:postgresql://localhost:5432/ktor_tutorial_db", user = "postgres", password = "password" ) }

   Note that the url includes the following components:

   • localhost:5432 is the host and port on which the PostgreSQL database is running.

   • ktor_tutorial_db is the name of the database created when running services.

1. Navigate to scr/main/kotlin/com/example and create a new package called db.

2. Inside the db package, create a new mapping.kt file.

3. Open mapping.kt and add the types TaskTable and TaskDAO:

   package com.example.db import org.jetbrains.exposed.dao.IntEntity import org.jetbrains.exposed.dao.IntEntityClass import org.jetbrains.exposed.dao.id.EntityID import org.jetbrains.exposed.dao.id.IntIdTable object TaskTable : IntIdTable("task") { val name = varchar("name", 50) val description = varchar("description", 50) val priority = varchar("priority", 50) } class TaskDAO(id: EntityID<Int>) : IntEntity(id) { companion object : IntEntityClass<TaskDAO>(TaskTable) var name by TaskTable.name var description by TaskTable.description var priority by TaskTable.priority }

   These types use the Exposed library to map the properties in the Task type to the columns in the task table in the database. The TaskTable type defines the basic mapping, whilst the TaskDAO type adds the helper methods to create, find, update, and delete tasks.

   Support for the DAO types hasn’t been added by the Ktor Project Generator, so you will need to add the relevant dependency in the Gradle build file.

4. Open the gradle.build.kts file and add the following dependency:

   //... val exposed_version: String by project //.. dependencies { //... implementation("org.jetbrains.exposed:exposed-dao:$exposed_version") }

5. Open the gradle.properties file located in the project's root directory and add a new property for the Exposed version:

   exposed_version=0.41.1

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

7. Navigate back to the mapping.kt file and add the following two helper functions:

   suspend fun <T> suspendTransaction(block: Transaction.() -> T): T = newSuspendedTransaction(Dispatchers.IO, statement = block) fun daoToModel(dao: TaskDAO) = Task( dao.name, dao.description, Priority.valueOf(dao.priority) )

   suspendTransaction() takes a block of code and runs it within a database transaction, through the IO Dispatcher. This is designed to offload blocking jobs of work onto a thread pool.

   daoToModel() transforms an instance of the TaskDAO type to the Task object.

8. Add the following missing imports:

   import Task import kotlinx.coroutines.Dispatchers import org.jetbrains.exposed.sql.Transaction import org.jetbrains.exposed.sql.transactions.experimental.newSuspendedTransaction

You now have all the resources needed to create a database-specific repository.

1. Navigate to src/main/kotlin/com/example/model and create a new PostgresTaskRepository.kt file.

2. Open the PostgresTaskRepository.kt file and create a new type with the following implementation:

   package com.example.model import Priority import Task import com.example.db.TaskDAO import com.example.db.TaskTable import com.example.db.daoToModel import com.example.db.suspendTransaction import org.jetbrains.exposed.sql.SqlExpressionBuilder.eq import org.jetbrains.exposed.sql.deleteWhere class PostgresTaskRepository : TaskRepository { override suspend fun allTasks(): List<Task> = suspendTransaction { TaskDAO.all().map(::daoToModel) } override suspend fun tasksByPriority(priority: Priority): List<Task> = suspendTransaction { TaskDAO .find { (TaskTable.priority eq priority.toString()) } .map(::daoToModel) } override suspend fun taskByName(name: String): Task? = suspendTransaction { TaskDAO .find { (TaskTable.name eq name) } .limit(1) .map(::daoToModel) .firstOrNull() } override suspend fun addTask(task: Task): Unit = suspendTransaction { TaskDAO.new { name = task.name description = task.description priority = task.priority.toString() } } override suspend fun removeTask(name: String): Boolean = suspendTransaction { val rowsDeleted = TaskTable.deleteWhere { TaskTable.name eq name } rowsDeleted == 1 } }

   In this implementation, you use the helper methods of the TaskDAO and TaskTable types to interact with the database. Having created this new repository, the only remaining task is to switch to using it within your routes.

To switch to an external database, you simply need to change the repository type.

1. Open the Application.kt file in src/main/kotlin/com/example.

2. In the Application.module() function, replace the com.example.FakeTaskRepository with PostgresTaskRepository:

   //... import com.example.model.PostgresTaskRepository //... fun Application.module() { val repository = PostgresTaskRepository() configureSerialization(repository) configureDatabases() configureRouting() }

   Because you are injecting the dependency through the interface, the switch in implementation is transparent to the code for managing routes.

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

4. Navigate to http://0.0.0.0:8080/static/index.html. The UI remains unchanged, but it now fetches the data from the database.

5. To verify this, add a new task using the form and query the data held in the tasks table in PostgreSQL.