Thursday, October 22, 2009

Databases in Haskell or "Release the power of monad transformers"

Many years ago almost all my programs were ‘some kind of interface to database’. I have been programming on C/C++ so work with databases was simple and required a lot of stupid code.

Several days ago I returned to the databases but now with Haskell in my hands and functional-oriented mind in my head :). First time I coded in the C-style from Haskell, but now, I think, I have found more functional way to work with databases. Of course this approach is not universal, and database ninjas could blame me for it, but let me continue :).

Combining transactions with monad transformers

I worked with sqlite3 database, but this approach should work with other databases too.

I enclosed all my database code into ReaderT monad in the following way:

> import Database.HDBC
> import Control.Monad
> import Control.Monad.Reader
> type DbTransaction a = ReaderT Connection IO a
> -- return the connection from the reader monad
> conn :: DbTransaction Connection
> conn = ask
> -- some useful functions to work with databases
> -- query function executes simple query without returning any results
> -- useful for create table and update queries
> query :: String -> [SqlValue] -> DbTransaction ()
> query q v = do
> c <- conn
> liftM (const ()) $ liftIO $ run c q v
> -- query' function executes query with results (for select queries)
> query' :: String -> [SqlValue] -> DbTransaction [[SqlValue]]
> query' q v = do
> c <- conn
> liftIO $ quickQuery' c q v

From that moment we can create our own queries like following:

-- get the user id from the database
> getUserId :: String -> DbTransaction (Maybe String)
> getUserId name =
> liftM (maybeId) $ query' "SELECT id FROM users WHERE name = ?" [toSql name]
> where maybeId [] = Nothing
> maybeId [[id]] = Just $ fromSql id

-- add new article to the database
> addArticle :: String -> String -> String -> DbTransaction ()
> addArticle user_id title content =
> query "INSERT INTO (user_id, title, body) VALUES (?,?,?)" $
> map toSql [user_id, title, content]

Also we can combine all our actions under one big transaction, i. e. take a user name and article title and body, add article with user id:

> addArticle :: String -> String -> String -> DbTransaction ()
> addArticle user title content = do
> uid <- getUserId user
> when (isJust uid) $ do
> addArticle (fromJust uid) title content

Now we have to explore how to run our transactions from the main program. I designed the function that takes the database name, connects to it, runs the transaction and commits everything:

> runTransaction :: String -> DbTransaction a -> IO a
> runTransaction dbname io = do
> c <- connectSqlite3 dbname
> runReaderT withCommit c
> where
> withCommit = do
> result <- io
> conn >>= liftIO . commit
> return result

So each time we need to query database, we will do the following:

> runTransaction "mydb.db" $ 
> addArticle "Hamlet" "The Question" "To be or not to be"

Don’t know how are you, but I like this code. It is short, clear, database-dependent code wrote in the separate monad and couldn’t be randomly combined with other code, and also safe. If you will want to use another database interface, you only will have to change one function (runTransaction).

It is easy to add error handling to this code. Here is two ways:

  • wrap the DbTransaction into the ErrorT monad, and wrap all HDBC functions to re-throw exceptions, or

  • use the internal HDBC exception handling mechanism (default behavior)

Anyway, this is only one example how to use monad transformers in real life, even for small projects. In the next posts I’ll show another uses of this approach.


  1. Great motivating case for use of the reader monad.

    For some reason, it was tricky for me to "get" for a while.

  2. I am teaching myself about functional programming and pure functional programming. I read about functional programming monads, and get the idea that they are at least to encapsulate the non-functional actions in functions. I can understand how that's true for a select statement to a database, but for an insert statement, isn't that still a side effect? How do databases fit conceptually into functional programming? Or don't they really?

  3. Databases don't fit into pure functional programming, unfortunately. This is the same as read/write files, and they are also work through IO monad, which handles all interaction with the `rest of world`.

    Unfortunately IO is to fat for me :) So I prefer to enclose different parts of `world` into separate monads, to distinguish different interfaces. And described method is the best for me now.

    The only drawback, is that I can't merge different reader monads (say database in/out and network communication, which is enclosed in separate reader monad), in the single piece of code. My solution now is to create threads for each task, and communicate them through pipes.