5 Ways to Test Applications that Access a Database in Haskell


At work I often write REST JSON APIs that access a SQL database in Haskell. Recently, I've been using Servant to define the API and Persistent for database access.

One problem I've been trying to solve is how to elegantly test application code that accesses a database. Like any other language, there is no straightforward way in Haskell to test code that does IO, such as writing to disk, writing to the network, accessing a database, etcetera.

I've assembled five methods for testing applications that access a database. The first two are database-specific. The last three, on the other hand, leverage some of the power of Haskell for a gain in abstraction: they'll work fine for databases, but you can also use them to test other forms of IO as well. For each of the five, I've listed the pros and cons and provided a link to a sample application demonstrating the method.

The sample applications for all five methods are very similar. Each is a REST JSON API that manages blog plosts. It accepts CRUD requests for Creating a new blog post, Returning the information for an existing blog post, Updating an existing blog post, and Deleting an existing blog post.

For simplicity, the sample applications all use Persistent to access a SQLite database. Don't worry if your production application uses something else though--each method can easily be applied to most types of databases, not just SQLite.

In order to understand the example code, you should have a good understanding of typeclasses, monads, monad transformers, and GADTs.

Before we get started, here is a Github repository with the sample projects and an explanation of how to compile and run them. I've commented the projects extensively (especially the three methods that aren't database-specific), so don't hesitate to dive into the code if you want to understand them better.

Method 1: Test Database

This is the simplest method. Let's assume you have a production database called production.sqlite and a test database called testing.sqlite. During testing, you simply pass a connection string that points to the latter database.

For example, the following code is used to connect to the production.sqlite database and run the API server:

main :: IO ()
main = runStderrLoggingT $ withSqliteConn "production.sqlite" $
    \conn -> liftIO $ run 8080 $
               serve blogPostApiProxy $ server conn

In testing we simply specify a different database:

main :: IO ()
main = runNoLoggingT $ withSqliteConn "testing.sqlite" $
    \conn -> liftIO $ hspec $ spec $ return $
               serve blogPostApiProxy $ server conn
Test Database Links
Test Database Pros
  • This is by far the easiest method. All you have to do is change the connection string.
  • You can test all of your production code as-is. When we get to the methods that don't actually use a database in testing, we will see how it is possible to introduce bugs into your tests. Doing so is not possible with this method.
Test Database Cons
  • Accessing a database in tests can be very slow. Speed might not be a problem when the number of tests is small, but as the number of tests grow developers can become reluctant to run them.
  • Because data is actually being inserted into the database, you might be unable to run the tests in parallel.
  • The tests generally have to be constructed so that all the data is set to a consistent state between each one. Doing so can cause the tests to take even longer.
  • It may not be practical (or even possible) to use this method when targeting a database in the cloud, like Amazon DynamoDB.[4]
  • A test database has to be setup and maintained in the continuous integration environment. If you're using a tool like Docker, this may not be too difficult. Still, it's one more thing you have to worry about.

Method 2: In-memory Database

This method is very similar to the Test Database method, but it only works for a certain class of database. In this method, an in-memory database is used for testing.

The production code is the same as the Test Database method, but the test code is changed to the following:

main :: IO ()
main = runNoLoggingT $ withSqliteConn ":memory:" $
    \conn -> liftIO $ hspec $ spec $ return $
        serve blogPostApiProxy $ server conn

Instead of specifying a database like testing.sqlite, we replace the connection string with :memory:. For this test we are using an in-memory database. It doesn't exist on disk, and it disappears completely after the tests are run.

In-memory Database Links
In-memory Database Pros
  • This method will almost always be faster than the Test Database method.
  • By passing a separate in-memory database to each test, it is easy to get multiple tests to run in parallel without having them step on each other's toes.
  • The in-memory database method is especially practical when using Persistent to access a SQL database. Persistent allows you to abstract database access, so the same code should work whether you are accessing a MySQL database, a PostgreSQL database, or a SQLite in-memory database.
In-memory Database Cons
  • On the other hand, this last pro has a corresponding con. Let's say, for example, that your production system uses PostgreSQL, but the tests use an in-memory SQLite database. Thanks to Persistent, this is easy to do. However, there is a chance that the behavior of PostgreSQL and SQLite will be slightly different and your tests may fail to catch a serious bug.
  • It may not be practical (or even possible) to use the In-memory Database method when targeting a database in the cloud, such as AWS's DynamoDB.[4]

Method 3: Typeclass

This is the first of the three methods that do not access a database in the tests. Each of these last three methods share a central idea. First, we create a structure that abstracts calls to the database. Second, we create two instances of this structure, one to be used in production, and one in testing. The production instance calls the database directly, while the testing instance simulates database access through something like a hashmap.

In overall form, these methods are similar to the concept of dependency injection used in object-oriented languages.

Everything should become clear as we dive into the methods themselves.

This first method (I call it the Typeclass method) is straightforward and often seen in Haskell code. It is, in fact, the method that Persistent itself uses.[1]

First, a typeclass is defined to represent all the ways the database can be accessed:

class Monad m => DBAccess m where

    getDb :: Key BlogPost -> m (Maybe BlogPost)

    insertDb :: BlogPost -> m (Key BlogPost)

    runDb :: m a -> EitherT ServantErr IO a

As you can see, the typeclass above has three methods. The getDb method fetches a blog post from the database given its Key. The insertDb method inserts a new blog post into the database and returns its Key. Finally, runDb takes a getDb or insertDb statement and runs it against the database. You can see that runDb is running in Servant's EitherT ServantErr IO monad.

In production, we use an instance of the typeclass that actually accesses the database:

instance DBAccess (SqlPersistT IO) where

    getDb :: Key BlogPost -> SqlPersistT IO (Maybe BlogPost)
    getDb key = Persistent.get key

    insertDb :: BlogPost -> SqlPersistT IO (Key BlogPost)
    insertDb blogPost = Persistent.insert blogPost

    runDb :: SqlPersistT IO a -> EitherT ServantErr IO a
    runDb query = liftIO $ Persistent.runSql query

In testing, however, we have an instance that just simulates database access with a hashmap:

instance DBAccess (State IntMap) where

    runDb :: State IntMap a -> EitherT ServantErr IO a
    runDb state = return $ evalState state IntMap.empty

    getDb :: Key BlogPost -> State IntMap (Maybe BlogPost)
    getDb key = do
        intMap <- get
        return $ IntMap.lookup key intMap

    insertDb :: BlogPost -> State IntMap (Key BlogPost)
    insertDb blogPost = do
        intMap <- get
        let freeId = nextFreeKey intMap
            newIntMap = IntMap.insert freeId blogPost intMap
        put newIntMap
        return freeId
Typeclass Links
Typeclass Pros
  • This method is well known and widely used.
  • The code is relatively simple and well structured.
  • Since a real database is not used, tests should be much faster than the Test Database method.
  • Tests can easily be written even when the production database is something that can't be run locally, like Amazon DynamoDB.[4]
Typeclass Cons
  • When abstracting CRUD operations for blog posts, the typeclass is very simple. However, abstracting something more complicated (for instance, all of SQL) can be much more difficult and time consuming. Spending the extra time may not make business sense.
  • When writing the testing instance, it is possible to make mistakes in the database-access semantics.[2]
  • A newtype wrapper needs to be used if we want two similar instances that have different behavior.
  • There are some Haskellers that say typeclasses should not be used unless there are laws that describe them. For example, the Monad typeclass follows the Monad laws.

    There are no such laws for our DBAccess typeclass. We could come up with laws (for instance, after an insertDb, a getDb always returns the thing inserted), but these are somewhat ad-hoc, not laws dictated by mathematics.[3]

    Some Haskellers say a typeclass without laws should be converted into a plain datatype. That brings us to the next method.

Method 4: Datatype

All typeclasses can be mechanically converted to datatypes. In the Typeclass method, we had the following typeclass:

class Monad m => DBAccess m where

    getDb :: Key BlogPost -> m (Maybe BlogPost)

    insertDb :: BlogPost -> m (Key BlogPost)

    runDb :: m a -> EitherT ServantErr IO a

This typeclass can be converted to the following datatype:

data DBAccess m =
    DBAccess { getDb :: Key BlogPost -> m (Maybe BlogPost)
             , insertDb :: BlogPost -> m (Key BlogPost)
             , runDb :: forall a . m a -> EitherT ServantErr IO a

It is similarly straightforward to convert the testing and production instances of the typeclass to datatypes.

Datatype Links
Datatype Pros
  • It is easier to work with multiple instances of the same datatype than the Typeclass method. You do not need to resort to newtype wrappers.
  • Most pros from the Typeclass method apply to the Datatype method as well.
Datatype Cons
  • The production datatype (the one with methods that actually access the database) has to be passed throughout the code. The hassle of this can be alleviated with a Reader monad, however.
  • Most cons from the Typeclass method apply to the Datatype method as well.

Method 5: Free Monad

Free monads are quite possibly the most flexible way to abstract code that does IO. A DSL is created to describe what actions can be performed on the database. The DSL is similar to the typeclass we created in the third method.

Our typeclass for database access looked like the following:

class Monad m => DBAccess m where

    getDb :: Key BlogPost -> m (Maybe BlogPost)

    insertDb :: BlogPost -> m (Key BlogPost)

    runDb :: m a -> EitherT ServantErr IO a

We rewrite the typeclass as a DSL modeled as a GADT:

data DbAction a where
    GetDb    :: Key BlogPost -> DbAction (Maybe BlogPost)
    InsertDb :: BlogPost -> DbAction (Key BlogPost)

We then create two separate interpreters for the DSL. Similar to our two typeclass instances, one interpreter is for production and accesses the database, while the other interpreter is for testing and uses a hashmap in memory.

Here is the interpreter for production. The code actually uses the Operational monad, but code using free monads would look very similar:

type DbDSL = Program DbAction

runDbDSLInServant :: DbDSL a -> EitherT ServantErr IO a
runDbDSLInServant dbDSL = runSql (runDbDSLInPersistent dbDSL)
    runDbDSLInPersist :: DbDSL b
                      -> SqlPersistT (EitherT ServantErr IO) b
    runDbDSLInPersist dbDSL' =
        case view dbDSL' of
            Return a -> return a
            (GetDb key) :>>= nextStep -> do
                maybeVal <- Persistent.get key
                runDbDSLInPersist $ nextStep maybeVal
            (InsertDb blogPost) :>>= nextStep -> do
                key <- Persistent.insert blogPost
                runDbDSLInPersist $ nextStep key

If you only look at the lines dealing with GetDb and InsertDb, the interpreter looks a lot like the code for the typeclass instance used in production. Here is the code from that typeclass to refresh your memory:

instance DBAccess (SqlPersistT IO) where

    getDb :: Key BlogPost -> SqlPersistT IO (Maybe BlogPost)
    getDb key = Persistent.get key

    insertDb :: BlogPost -> SqlPersistT IO (Key BlogPost)
    insertDb blogPost = Persistent.insert blogPost

As you can see, it's quite similar. The code for the interpreter used in testing is also very similar to the typeclass instance using in testing. Please see the links below if you are interested.

Free Monad Links
Free Monad Pros
  • This method is possibly the most flexible of all.
  • Most pros from the Typeclass method apply to the Free Monad method as well.
Free Monad Cons
  • With great power comes great responsibility. Free monads can be slightly more complicated than plain typeclasses. If you don't need the flexibility that free monads give you, it might be a good idea to stick with the Typeclass method.
  • The interpreter has to be passed throughout the code. The hassle of doing this can be alleviated with a Reader monad.
  • Most cons from the Typeclass method apply to the Free Monad method as well.

Conclusion and Recommendations

When you are writing your next application in Haskell, which method should you pick?

If you are using Persistent and your production database is SQL, then using the In-memory Database method would be a good bet.

However, if it is a mission-critical application, using a different database in testing and production won't cut it. Instead, you should try the Test Database method.

If you're using a database that doesn't have an in-memory variant, then you should look into the Typeclass method. If you don't like lawless typeclasses, then checkout the Datatype method. Finally, if you need a lot of flexibility, the Free Monad method might be the right fit for you.

If you are trying to test some IO operation other than database access, then the Typeclass or Free Monad methods are often used.


  1. Actually, Persistent uses a combination of the Typeclass method and the Datatype method. General database access methods are abstracted using the Typeclass method. SQL access methods are abstracted using the Datatype method.
  2. I actually did this when writing the example application, despite its simplicity. The database access typeclass for the sample application has two additional methods to match the "Update" and "Delete" part of CRUD. The "Update" part is handled by the typeclass method updateDb. When writing the DBAccess instance for testing, I made updateDb insert a new record if an old one didn't exist. This is the opposite of what Persistent actually does.
  3. There are some additional comments about this on reddit.
  4. I was incorrect about this! It is actually possible to run DynamoDB locally. Thanks to vosper1 and tags: haskell