Building a nicely-typed lightweight database-backed API with Haskell, Servant, and Sqlite

I recently wanted to make a small API layer over some data I had, but spinning up a full Yesod or even Snap project seemed like overkill. So, I decided to try out Servant.

In my favorite kinds of Haskell code, everything flows nicely from the types, and hopefully here is no exception. This API is going to be sending information about piano exercises, with content like how fast they should be played, or whether it’s the kind of thing that should be played in all twelve keys. However, this should pretty straightforwardly extend to any kind of simple data you want an API for.

Types and JSON shape

data Exercise = Exercise
  { exerciseBpm :: Int
  , exerciseName :: Text
  , exerciseAll12Keys :: Bool
  } deriving (Eq, Show)

For convenience and niceness, I want my Haskell records prefixed, and my JSON labels all lowercase. That’s easy enough to do by passing a custom fieldLabelModifier to deriveJSON….

dropPrefix :: Text -> Options
dropPrefix s = defaultOptions
  {fieldLabelModifier = map toLower . drop (T.length s)}

$(deriveJSON (dropPrefix "exercise") ''Exercise)

…However, doing this directly, you’ll run into this lovely little error. The fix is pretty simple—just move dropPrefix to another module—but it does mean this project has a Util module with only one lonely function in it.

    GHC stage restriction:
      ‘dropPrefix2’ is used in a top-level splice, quasi-quote, or annotation,
      and must be imported, not defined locally
   |
31 | $(deriveJSON (dropPrefix2 "exercise") ''Exercise)
   |               ^^^^^^^^^^^

Database

For database backing, we’ll use sqlite-simple. This library wants FromRow and ToRow instances on things we store in the database, so let’s write them in just about the simplest way we possibly can.

instance FromRow Exercise where
  fromRow =
    Exercise <$> field <*> field <*> field

instance ToRow Exercise where
  toRow (Exercise bpm name all12keys) =
    toRow (bpm, name, all12keys)

Continuing on towards the direction of the database, let’s create a migration/setup function that makes our database tables, types, and so on. The SQL for that looks like this:

CREATE TABLE IF NOT EXISTS exercises 
  (bpm INTEGER, name TEXT PRIMARY KEY, all12keys BOOLEAN)

To actually run this, we’ll need some kind of connection to the db in Haskell, but let’s come back to that in a second, after finishing up some other queries. We also want to be able to get a list of all existing exercises

SELECT * FROM exercises

as well as insert new exercises

INSERT INTO exercises (bpm, name, all12keys)
  VALUES (?,?,?)

We can run these queries using code like this, opening a connection conn, running the query, and then closing the connection.

addExercise :: Exercise -> IO Exercise
addExercise exercise = do
  conn <- open "test.db"
  execute conn "INSERT INTO exercises (bpm, name, all12keys) \
    VALUES (?,?,?)" exercise
  close conn
  pure exercise

Connection Management

However, this quickly gets repetitive and boring, and it’s annoying to remember to close connections. Instead, let’s abstract away the connection-finding logic with a function

withConn :: (Connection -> IO a) -> IO a
withConn action = do
  conn <- open "test.db"
  a <- action conn
  close conn
  pure a

I modeled this function after whenJust, with its type Applicative m => Maybe a -> (a -> m ()) -> m (). Using whenJust in practice often looks like this construction:

  whenJust maybeSomething $ \something -> do

It’s a way to unpack a Just value (when you have one) and use it in some block, giving it the name something via a lambda function.

Let’s use this style and see that we can now grab a conn whenever we need one for some block of code, and it’ll be closed for us when we’re done.

addExercise :: Exercise -> IO Exercise
addExercise exercise = withConn $ \conn -> do
  execute conn "INSERT INTO exercises (bpm, name, all12keys) \
    VALUES (?,?,?)" exercise
  pure exercise

There’s a similar, but more complex kind of logic going on in bracket. bracket :: IO a -> (a -> IO b) -> (a -> IO c) -> IO c also has the kind of “beginning, middle, and end” structure of “open a connection, use it, and close it”, but it does a better job cleaning up in case an exception occurs. In fact, our connection code eventually calls through to bracket. It’s a useful tool to help manage resources in IO.

Looking back at our other queries, we can now turn them into fully-fledged Haskell functions by adding withConn and the appropriate sqlite-simple functions.

migrate :: IO ()
migrate = withConn $ \conn ->
  execute_ conn "CREATE TABLE IF NOT EXISTS exercises \
    (bpm INTEGER, name TEXT PRIMARY KEY, all12keys BOOLEAN)"

exercises :: Handler [Exercise]
exercises = withConn $ \conn ->
  query_ conn "SELECT * FROM exercises"

Servant

Think of Servant as a bunch of fancy types that let us express the shape of our API as a big type. Mirroring our database-wrapping functions we’re already able to run, we’ll want an API listing to GET a list of Exercises, as well as one that takes some new Exercise and POSTs it, returning the value of the new exercise. We can express those constraints in Servant like this:

type API =
  "list" :> Get '[JSON] [Exercise] :<|>
  "add" :> ReqBody '[JSON] Exercise :> Post '[JSON] Exercise

Note that this is kind of “tip of the iceberg”. For example, what should our API do if we upload an Exercise that we don’t want to allow (for example, one with a BPM of 999)? Right now, we just happily accept anything that parses correctly into our db types. That’s actually already a moderately strong guarantee on the kinds of things our database will have in it, but there is definitely more that you can do if you want to dive into Servant.

Along with the big API type, we also want to define an Application that uses that type and provides Handlers. For this to work, we should change the types of addExercise and exercises to from IO to Handler. They should return Handler Exercise and Handler [Exercise] respectively. Luckily, it’s easy enough to run IO actions in handler (just throw a liftIO before the IO you want to run).

app :: Application
app = serve (Proxy :: Proxy API) (exercises :<|> addExercise)

Now that our little app is defined, and has a proper API type routing to handlers that talk to the database, all that’s left is to run that app on some port.

main :: IO ()
main = do
  migrate
  run 1234 app

Conclusion

This isn’t the bare minimum amount of code you need to spin up a database-backed API in Haskell. However, I found it provided a fairly good set of tradeoffs among ease of use, amount of code, and impenetrability of types for this kind of tiny project. It’s also fairly easy to swap to a different database backing, using e.g. postgresql-simple or mysql-simple.

I think the next step I would take if I wanted to make this nicer to work with, but still not overwhelmingly complex, would be to use a library like persistent to define database models, and thereby avoid having strings of raw SQL littered throughout the code. However, I’m already fairly familiar with how persistent works, so maybe a different SQL library would be better for you. (Update: I’ve done this switch to Persistent in my next post)

As a quick way to wrap some database queries in a nicely-typed API, I found this combo pretty nifty. Here’s the repository with the code seen here