How to use an in-memory database for testing on Ktor
Usually, in a backend project, there are different instances of the same database: one for production (or more than one, it depends on the architecture), one for staging, and a local one that runs in the development machine.
However, for automated testing, none of these databases will be suitable to use. Since the purpose of testing is checking that every part of the software is working as expected, it will be necessary to test also situations where there isn’t any data saved in the database. To achieve that, the database must be cleared after every test (or group of tests) or pre-populated before.
An approach to achieve that is using an in-memory database. As the name suggests, all the data will be saved in memory and not on disk, so they can be easily deleted when closing the database connection. Another approach could be using Docker to spin up every time a dedicated container for the database, to have a database that is like the one used in production. In my case, I preferred to use an in-memory solution but if you are interested in the topic, I suggest looking at this article by Philip Hauer: Don’t use In-Memory Databases (H2, Fongo) for Tests
In this article, I will cover how to setup an in-memory database with H2 for testing on a Ktor project that uses a MySQL database in production.
This post is part of a series of posts dedicated to Ktor where I cover all the topics that made me struggle during development and that was not easy to achieve out of the box. You can check out the other instances of the series in the index above or follow me on Twitter to keep up to date.
The ORM that I’ve decided to use is Exposed from Jetbrains. It is very nice to deal with and it offers the possibility to use a typesafe DSL that wraps SQL and a lightweight data access object. Exposed supports different databases like MySQL, H2, PostgreSQL, SQLite. For a complete list, refer to the documentation.
Exposed comes with a different set of artifacts that you can decide to use. For this project I’ve added the following:
The connection to the MySQL database is performed with the JDBC driver and with a connection pool provided by Hikari.
The last required dependency is H2 that is needed only for tests.
The connection and the disposal of the database is performed through a method defined in the
This interface will then have a different implementation, depending on if the server is running in production or for unit or integration testing.
The factory implementation used in production creates a private HikariDataSource that will be used by the
connect method will be called inside the Ktor module function during the initialization and the setup of the server.
As you may have noticed, the
DatabaseFactoryImpl class uses some fields provided by
AppConfig. These fields are the driver class used for the connection, the name, user, and password of the database, and other fields that are specific to the connection. These fields are placed inside the
application.conf file to be able to change them on different instances of the server.
After adding the
database block, it is necessary to update accordingly the
For more informations about the configuration process, you can give a look at the first episode of the series: Structuring a Ktor project
For testing, it is necessary to cover two different situations: unit tests and integration tests (in this case I refer to integration tests that involve the server).
Integration testing that involves the server is performed with a
TestEngine that does not create a web server but hooks directly into the internal mechanism. For more information about testing on Ktor, you can look at the first episode of the series: Structuring a Ktor project. When this type of test is run, the same Ktor module function that initializes the server on production is called. In this way, the connection of the database is automatically performed.
When running unit tests instead, the server is not involved, so the connection to the database must be performed manually.
These two behaviors can be achieved with two implementations of the
The former receives the configuration data from the
AppConfig class since the Ktor module function will be called.
The latter instead has the configuration data hardcoded since the connection to the database must be performed manually.
Since the database used is H2, the driver and the URL change a bit.
The driver class name is now:
org.h2.Driver and the URL is:
jdbc:h2:mem:;DATABASE_TO_UPPER=false;MODE=MYSQL. The URL specifies also some features:
mem-> it tells to use the in-memory version of H2
:-> it does not specify a name for the database
DATABASE_TO_UPPER=false-> it disable the default feature of using uppercase for identifiers. For example, if it is not disabled, the table names are uppercase and queries will fail
MODE=MYSQL-> it uses the MySQL compatibility mode in order to have the same features of MySQL.
After the connection to the database, it is necessary to create its structure, since the database will be destroyed after each test (or after a set of tests).
To do that, it is possible to use the features of Exposed. After defining a table with the Exposed DSL (for more info about it, give a look at the Exposed documentation):
it is possible to create the table:
Since this operation must be repeated for every table, it is better to create a function that can be called inside the DatabaseFactory.
connect function in both the database factories will look like that:
However, during unit tests, it is necessary to manually close the connection to the database, to be sure that all the data are cleared between each test run. To be able to do that, it is necessary to store in the Factory an instance of
HikariDataSource that can be closed with the
As reference, here are the entire
As mentioned early, during integration tests that involve the server, the database connection is performed automatically since the Ktor module function will be called. The only thing to do is to replace in the Koin test module the
DatabaseFactory implementation from
DatabaseFactoryImpl, which is used in production, to
As you can see in the following example of test, it is not required any initialization or setup in the test class.
On unit tests instead, the connection and the disconnection from the database must be performed manually before and after the test, or whenever it is necessary.
And that’s it for today. You can find the code mentioned in the article on GitHub.
In the next episode, I’ll cover database migrations. You can follow me on Twitter to know when I’ll publish the next episodes.