Doobie is one of the most popular Scala libraries to work with relational databases. It has a number of benefits. Using Doobie you can:

precisely delimit transactions with no additional complexity needed to pass the transactional context

compose larger database-related programs from smaller pieces

work safely with multi-threaded code

leverage full power of SQL

These are all important traits of any database-related library; but what about testing?

Nidzkie lake in Masuria. Remember to keep calm when testing your code!

As a running example, let’s focus on a simple scenario, where we’ll be adding “points” (e.g. loyalty points) for a user. We add 1 or 3 points through the Points.increase method, depending on the number of points accumulated so far:

We’d like to test the above code, if possible testing the business logic (calculation of points) in isolation. What approaches can we take?

All examples (in runnable form) can be found on github.

1. Testing with an embedded database

First of all, we can use an embedded database and test our business logic combined with the dao layer. That way, we test both the logic and the queries that we are using. This has disadvantages: we are not testing the business logic in isolation, but combined with the persistence layer. Moreover, such a test runs slower than a “pure” unit test, as it needs to setup the database.

On the other hand, such a test covers a large area of the code. This approach is perfectly sufficient for simple cases, when the business logic is not complex, and doesn’t have a lot of alternative control flows.

For a more complete example of setting up end-to-end tests with an embedded Postgres database, see the tests in Bootzooka.

Are such tests unit tests? By most definitions, probably no. However, it all boils down to what we consider a unit in our code. One might consider the queries needed to implement the business logic part of that functionality’s unit, or it might be considered separate. There are also other ways of classifying tests which might be more useful than the traditional unit-functional-integration distinction, which take into account the isolation, intention and encapsulation of the test.

In tests with an embedded database, we use a “real” Transactor to interpret the transaction fragment defined by Points.increase into a transactional side-effect, and run the side effect to completion to verify the value:

Note that we interpret the Doobie programs into lazy side-effecting computations ( IO values), and then run them to completion using the blocking call .unsafeRunSync . That’s only one of the possible approaches of testing such code; another would be to compose the test itself as an IO value.

2. Testing pure business logic

In some cases, the business logic can be extracted as a pure function: that is, a function which has no effects in its signature (and which doesn’t cheat by running unconstrained side effects in its body!). In such a case, we can write unit tests which test only the business logic (possibly covering multiple alternative execution paths), and separate tests which test integration with the database.

The logic of our simple example indeed is such a function. We can make it public and test in isolation:

Whatever approach we take, we need to test our queries; both from a syntax perspective, and from a functionality perspective. To test the syntax of the queries, Doobie offers some help. However, it still needs a live database connection, hence we’ll need an embedded database for that anyway. To test the semantics of our queries (if it’s anything more than a simple SELECT / UPDATE / DELETE , it’s probably worth verifying), we’ll also need an embedded database. There’s no escaping from actually verifying that your code — and SQL is definitely part of the code — works with your database of choice!

However, in practice we usually don’t have the luxury of working with pure, non-side-effecting functions. The business logic often needs to access the database conditionally, basing on the result of previous operations. Transactions are composed dynamically, from multiple ConnectionIO fragments, not following the simple read-compute-update pattern. In these cases, we can’t extract the business logic as a pure function, and test it separately.

3. Using an abstract effect

Taking a step back — what’s the main problem preventing us from testing the business logic without a database? It’s the fact that we’re using a concrete effect type: ConnectionIO . This mandates that to evaluate these effects, we need a Transactor instance (so that we can call (program: ConnectionIO[T]).transact(transactor) ).

However, our business logic only composes fragments of the transaction into a larger structure — it doesn’t perform any database-related operations. Hence to test that the composition is correct, we just need to know that there’s some side effects involved, not that we work with a specific ConnectionIO effect.

We can refactor our Dao trait so that it returns results wrapped in an arbitrary effect; the default implementation will use ConnectionIO as the effect type:

The business logic must work with the same wrapper as the Dao . However, the wrapper cannot be arbitrary. We need to be able to sequence side-effecting operations (using a for -comprehension, which desugares into a flatMap ). In other words, F[_] needs to be a monad. Using the Monad abstraction from cats:

In the test, we can use an arbitrary Monad: even Id ! Our test then becomes:

This technique is known in the Scala world as tagless final. One disadvantage is that it’s quite viral — once you start parametrising your code with abstract effect wrappers, you’ll quickly need to do that in many other places. It’s also a bit more complex to use, hence the controversions (see Death of Tagless Final and Final Tagless seen alive).

4. Using a stub transactor

Finally, maybe we can create a fake transactor, which only pretends to work with a database? Of course, we won’t be able to execute any queries, but maybe we can provide already computed query results as ConnectionIO values? Yes! 5.pure[ConnectionIO] (using syntax from cats) lifts a strict value into the ConnectionIO context.

In theory, we should be able to evaluate a Doobie program consisting only of non-database-related composition of ConnectionIO values, and strict values lifted to ConnectionIO . In practice, that’s possible as well! First, we’ll need to create a stub transactor. To create a transactor, we need to provide a way to provide java.sql.Connection resources. And that’s were we cheat: we’ll just use null values (ugly, but does the trick).

This means that whenever we want to execute a query in our test, or some other database-related operation that is not stubbed, we’ll get a NullPointerException .

We’ll need StubDao , which provides strict values lifted into a ConnectionIO , instead of running the query:

And now we can write our test:

If you’re a fan of mocking libraries, it would also be possible to create a mock[Dao] , which returns strict values lifted to ConnectionIO from the methods that are being used by the business logic.

Summary

Writing good tests is a very fine art. On one hand, we want to write small, fast unit tests, which test components of our application in isolation. On the other, we still need to test that the components integrate as intended.

Too fine-grained tests can end up repeating the implementation of the tested subject, use a lot of mocks/stubs, and provide more maintenance problems than they bring value. Too coarse-grained tests fail to verify a sufficient number of flow paths in our code, and might be impractically slow. Hopefully one of the approaches to testing Doobie programs will help in striking the right balance in your database-related tests!