Part 2 - Adding a Database to Our Backend

Introduction

Now that we have completed the first part of our application, we will now update it to replace our ArrayList data source with a database. If you haven’t read part 1, go back and read that first, as this section may not make as much sense!

Start the refactoring

First up, let’s update our POM.xml file to bring in the new dependencies that we are going to need.

<dependency>
  <groupId>io.quarkus</groupId>
  <artifactId>quarkus-jdbc-h2</artifactId>
  <scope>test</scope>
</dependency>
<dependency>
  <groupId>io.quarkus</groupId>
  <artifactId>quarkus-test-h2</artifactId>
  <scope>test</scope>
</dependency>
<dependency>
  <groupId>io.quarkus</groupId>
  <artifactId>quarkus-flyway</artifactId>
</dependency>
<dependency>
  <groupId>io.quarkus</groupId>
  <artifactId>quarkus-agroal</artifactId>
</dependency>
<dependency>
  <groupId>io.quarkus</groupId>
  <artifactId>quarkus-jdbc-postgresql</artifactId>
</dependency>
<dependency>
  <groupId>com.github.seratch</groupId>
  <artifactId>kotliquery</artifactId>
  <version>1.1.3</version>
</dependency>

We are bringing in two test scoped dependencies (jdbc-H2 and test-H2), and four other dependencies; Flyway for managing database migrations, agroal for datasource connection pooling, postgres as our database, and kotliquery as a database client library to simplify db access.

Managing the Database

There are a couple of files we need to update to get the database up and running. First off, as we are using Flyway to manage our database migrations, we need to specify our database schema in a db migrations file. Flyway expects the SQL file to be in a certain format (V##__description.sql) V version_number double_underscore description .sql. The version number is any sequential number. In our case, we will be using V0.1__initial.sql.

So, create a file src/main/resources/db/migration/V0.1__initial.sql and add the following sql code.

CREATE TABLE todo (
   id SERIAL PRIMARY KEY,
   txt VARCHAR NOT NULL,
   completed boolean default false
);

This should represent identically the ArrayList and the AtomicLong incrementAndGet functionality we had in the first part.

Next we need to configure our database in the application.properties. Open the file src/main/resources/application.properties, and add the following.

quarkus.datasource.url=jdbc:postgresql://localhost/todo
quarkus.datasource.driver=org.postgresql.Driver
quarkus.datasource.username=pg
quarkus.datasource.password=pg
quarkus.datasource.min-size=3
quarkus.datasource.max-size=13
quarkus.flyway.migrate-at-start=true
%test.quarkus.datasource.url=jdbc:h2:tcp://localhost/mem:test
%test.quarkus.datasource.driver=org.h2.Driver
%test.quarkus.flyway.migrate-at-start=true

The final 3 lines configures an H2 database when running in test mode.

The first 7 lines set up a link to a local postgres database, with a user / password of pg / pg, a connection pool of 3–13, and flyway set up to load the SQL schema on application start up.

This assumes you have a running local instance of postgres. If you don’t, then my usual approach is to use Docker to give me a temporary database to get up and running. If you want to try this approach, you should be able to run the following command (assuming you have docker installed)

docker run -it — rm — name pg-docker -e POSTGRES_USER=pg -e POSTGRES_PASSWORD=pg -e POSTGRES_DB=todo -p 5432:5432 postgres

Updating the TodoService

That’s it for all the additional files, so we will only be updating the TodoService.kt and TodoServiceTest.kt from here on in.

Open up the TodoService.kt file and add the following to the imports.

import kotliquery.*
import javax.inject.Inject
import javax.enterprise.inject.Default
import javax.sql.DataSource

Next, delete the todos ArrayList, idSeq AtomicLong and createTodo function, and replace it with the following to setup the datasource.

@Inject
@field: Default
lateinit var ds: DataSource

Quarkus uses CDI to inject the datasource into our application based on the properties that we set up in the application.properties. We will use this datasource when accessing the database to run SQL statements as we modify our endpoints.

Lets start updating the application one requirement at a time.

Req#1: List all Todos

Update the getAll function to look as follows

fun getAll() = using(sessionOf(ds)) { session ->
   session.run(queryOf("SELECT id, txt, completed FROM todo").map {
      row -> Todo(row.long("id"), row.string("txt"), row.boolean("completed"))
   }.asList)
}

Most of this code is Kotliquery specific. If you want to learn more about this, you can read about it at the Kotliquery GitHub page (https://github.com/seratch/kotliquery). The important part is the query code, which then maps each “row” to a Todo object, and then returns a list. The list is automatically converted to JSON and returned as per the previous implementation.

Req#2: Get a single Todo

Update the getOne function to look as follows

fun getOne(@PathParam("id") id: Long) = using(sessionOf(ds)) { session ->
   session.run(queryOf("SELECT id, txt, completed FROM todo WHERE id=?", id).map {
      row -> Todo(row.long("id"), row.string("txt"), row.boolean("completed"))
   }.asSingle)
}

This update is almost identical to the getAll function, with the exception of the WHERE clause and the asSingle collector instead of asList. Note that the queryOf function takes the id as a parameter. The queryOf function signature is (sqlStatement, varargs). If there were more than one bind variables (Placeholders labelled ?), each parameter would be comma separated.

Req#3:Create a Todo

Update the addOne function to look as follows

fun addOne(txt: String): Todo? {
   val newId: Long? = using(sessionOf(ds, true)) { session ->
      session.run(queryOf("INSERT INTO todo (txt) VALUES (?)", txt).asUpdateAndReturnGeneratedKey )
   }
   return if (newId != null) getOne(newId) else null
}

With this update, we again are using queryOf to execute a SQL query, but we are not using a map, instead we are using an asUpdate. To ensure we get the complete Todo, we get the generated autonumber (id SERIAL) by returning the generated key (asUpdateAndReturnGeneratedKey).

Once we have the ID, we use this to lookup the Todo by calling the GetOne function we created in requirements 2. The main advantage of this approach is that the default values set at the database level (completed == false, for example) will be returned by retrieving the full Todo from the database by the ID.

Req#4: Delete a Todo

Replace the deleteOne function with the below code.

fun deleteOne(@PathParam("id") id: Long) =
   using(sessionOf(ds)) { session -> session.run(queryOf("DELETE FROM todo WHERE id=?", id).asUpdate)} == 1

This is the simpler of the updates in this section. We simply execute a DELETE statement, passing in the ID, and execute as an update. Much of this logic we have already encountered before, so there is no need to focus on it. The asUpdate executor is new, but is very similar to the asUpdateAndReturnGeneratedKey, except we simply update and return the affected row count. We are expecting a single row deleted, and therefore a row count of 1, so we complete the function with == 1, which will return true if only a single row is deleted, or false otherwise.

Req#5: Update Todo And finally, lets update our final function.

fun updateOne(@PathParam("id") id: Long, todo: Todo): Todo? {
   using(sessionOf(ds)) { session ->
      session.run(queryOf("UPDATE todo SET txt=?, completed=? WHERE id=?", todo.txt, todo.completed, id).asUpdate) }
   return getOne(id)
}

This update is slightly more involved, so lets deconstruct it. First of all, we are passing in both a PathParam, and a JSON representation of the Todo (which will auto convert into a Todo object). We the execute an UPDATE SQL statement, passing in three parameters to replace the bind variables. Finally we execute the whole thing using the asUpdate executor.

Just like in Req#3, once we have updated the Todo, we then return the Todo by calling the GetOne function to retrieve the Todo from the database.

The main application is now updated. So let’s go ahead and test it to make sure it is all working as expected.

Manual Testing

If you want to manually test, until we write our front-end app, use the same cURL commands as used in Part 1.

Automatic Testing

Now lets head back over to our test class, /src/test/kotlin/codemwnci/TodoServiceTest.kt

First off, let’s get the additional imports sorted.

import org.junit.jupiter.api.BeforeEach
import kotliquery.*
import javax.inject.Inject
import javax.enterprise.inject.Default
import javax.sql.DataSource
import org.flywaydb.core.Flyway
import io.quarkus.test.common.QuarkusTestResource
import io.quarkus.test.h2.H2DatabaseTestResource

And next add the annotations above the class definition

@QuarkusTestResource(H2DatabaseTestResource::class)

This annotation will ensure the H2 database is started alongside the test suite, just like the postgres database would be started on application startup.

First, to improve our tests, we will split the tests into multiple unit. To do this, we need to clear the clear down the database before each test is run so that we are starting with a clean database. However, it is not enough to simply delete the data, because we also need to reset the ID sequence to start at 1. We are using Flyway to manage our database, and it provides a convenient API to allow us to delete and recreate the database, so let’s inject Flyway into our test suite, and create a BeforeEach function to to do just that.

@Inject
lateinit var flyway: Flyway;
// Create the DB structure before each test
@BeforeEach
fun dropAndRecreate() {
   flyway.clean()
   flyway.migrate()
}

Our final piece of test setup is a simple function to add some test todos directly to the database. This is so that we are not reliant on the addTodo REST endpoint, and as such can be confident that each unit test is self contained.

@Inject
@field: Default
lateinit var ds: DataSource
fun addTestTodos(vararg todos: String) {
   using(sessionOf(ds)) { session ->
      for(todo in todos) {
         session.run(queryOf("INSERT INTO todo (txt) VALUES (?);", todo).asUpdate)
      } 
   }
}

This function will iterate through a list of strings, and create a new todo for each string. We will use this function in some of the tests coming up.

Next, delete the one existing test that we created, and then add the following test.

@Test
fun testEmptyTodoList() {

given().`when`().get(“/todos”).then().statusCode(200).body(containsString("[]"))
}

This will simply test that when the database is empty, calling the getAll REST endpoint.

Our next test will test that we can add a new todo, and then retrieve it as a getAll.

@Test
fun testAdd() {
   // TEST THE POST RETURNS A SINGLE JSON
   given().body("test todo").`when`()
      .post("/todos").then().statusCode(200)
      .body(containsString("test todo"), containsString("""{"id":1,"txt":"test todo","completed":false}"""))

   // TEST GET NOW RETURNS AN ARRAY WITH A SINGLE ITEM
   given().`when`().get("/todos").then().statusCode(200)
.body(containsString("""[{"id":1,"txt":"test todo","completed":false}]"""))
}

Our next test will add two todos to the database using the database function we created specifically for this test harness, and then we will retrieve the first todo using the GetOne REST endpoint. We will test that the returned todo contains the expected JSON, and does NOT contain data from the second todo.

@Test
fun testGetOne() {
   addTestTodos("number1", "test 2")
   given().`when`().get("/todos/1").then().statusCode(200)
.body(containsString("""{"id":1,"txt":"number1","completed":false}"""), not(containsString("test 2")))
}

Our next test will confirm the delete functionality. Once again we will add a few todos, confirm the first todo is in the returned list, then delete the todo through the REST endpoint confirming that the deletion was successful, and then finally, rechecking the list to ensure it isn’t returned on the after-deletion request.

@Test
fun testDelete() {
   addTestTodos("number1", "test 2")
   // confirm the item is there
  given().`when`().get("/todos").then().statusCode(200).body(containsString("number1")) 
  // delete the todo, and confirm true is returned
  given().`when`().delete("/todos/1").then().statusCode(200).body(containsString("true")) 
  // confirm the item is no longer there, and is therefore deleted
 given().`when`().get("/todos").then().statusCode(200).body(not(containsString("number1")))

To wrap up our testing, we simply need to test the update functionality. The update should confirm that the Todo can be updated (both the text and completed flag), but that the ID cannot be updated. We’ll use the same method to add a few todos initially, confirm the todo is in the list, update the todo, confirm todo has been updated, and finally confirm the ID has not been updated.

addTestTodos("number1", "test 2")
   // confirm the item is there
   given().`when`().get("/todos").then().statusCode(200).body(containsString("number1"))
   // confirm it returned the updated details
   given().contentType("application/json").body("""{"id":1,"txt":"1_isupdated_to_number2","completed":true}""")
      .`when`().put("/todos/1").then().statusCode(200)
      .body(not(containsString("number1")), containsString("1_isupdated_to_number2"), containsString("true"))
   // confirm it returns from get as expected
   given().`when`().get("/todos").then().statusCode(200).body(containsString("1_isupdated_to_number2"))
   // make sure we cant change the ID and that it returned the updated details
   given().contentType("application/json").body("""{"id":1111,"txt":"1_isupdated_to_number3","completed":true}""")
      .`when`().put("/todos/1").then().statusCode(200)
      .body(not(containsString("number1")), not(containsString("1111")))
   // confirm it returns fron getAll as expected
   given().`when`().get("/todos").then().statusCode(200)
      .body(containsString("""{"id":1,"txt":"1_isupdated_to_number3","completed":true}"""))

Finally

And that’s it for Part 2. We now have a database backed version of our Todo list application, with an in-memory database used for testing and a decent set of tests to run against our API.

Next up, we’ll finish the whole thing off with a SvelteJS based front end to make use of our REST API. Continue on to Part 3.