The current set of database driver libraries in Haskell are written by some very talented people, but even then, the libraries sometimes have some rough edges that make them difficult to use for the average web app developer. In fact the frustration level can be high enough that some have given up trying to work with them.

Recently I was working with John Goerzen’s HDBC library (1.1.4). I don’t remember having any problems installing it or the additional library I grabbed from darcs to run with Sqlite3.

Overall the library has been easy to work with. But I started running into a very frustrating scenario. Given a sqlite3 table defined as follows:

run dbh "create table foo(id integer not null primary key autoincrement,\
        \name text not null)" []
run dbh "create unique index foo_name on foo(name)" []

I was playing around inserting duplicate values to test the unique index constraint. For example, calling the inserttable function a couple of times results in the program throwing an exception during database close that says the “column name is not unique”. As an aside see the wiki for Haskell’s definitions of Exception and Error.

inserttable :: (IConnection c) => c -> IO ()
inserttable dbh = do
  run dbh "insert into foo(name) values(?)" [toSql "fred"]
  commit dbh

test = do
  withDB inserttable
  withDB inserttable

-- SqlError {seState = "", seNativeError = 19
-- , seErrorMsg = "finish: column name is not unique"}

Cool. Well I’m writing a web app so I’ll simply catch this exception where I’m doing an insert and tell the user to choose a unique name. Except when I write some code to catch it, it pulls a Heisenberg on me. Oh it’s still a SqlError, but now the error code and message are entirely different.

inserttable :: (IConnection c) => c -> IO ()
inserttable dbh = do
  run dbh "insert into foo(name) values(?)" [toSql "fred"]
  commit db
  `catchSql`
  (\ e@(SqlError _ _ _) -> putStrLn $ "error is " ++ show e)

test = do
  withDB inserttable
  withDB inserttable

-- error is SqlError {seState = "", seNativeError = 1
-- , seErrorMsg = "step: SQL logic error or missing database"}

Eh? What happened to my nice message about the name column not being unique? Turns out it will still give that message, but only when throwing another exception when I’m closing the database connection.

Great, that’s fine if I’m writing a client side application, but sucks if you’re writing a web app and are opening connections at a much higher level than the functions which are reading and writing to the database. Then it becomes pretty hard to give the user a decent message.

As I started to try different cases to solve the problem, I actually was able to make it worse as it started to give me an even different error when closing the database connection, “disconnect: Unable to close due to unfinalised statements”. Okay, things have just gone from gee I’m not crazy about this design to I hope it’s not corrupting my data.

John mentions in his code that this kind of problem of closing the database connection may fail due to unfinalised statements, but its not clear how to effectively use this from the API. After a bunch of research I was finally able to find a workaround.

inserttable :: (IConnection c) => c -> IO ()
inserttable dbh = do
  sth <- prepare dbh "insert into foo(name) values(?)"
  catchSql (do
      execute sth [toSql "fred"]
      commit dbh
    )
    (\ e@(SqlError _ _ m) -> do
      rollback dbh
      finish sth
      `catchSql` \x@(SqlError _ _ m2) ->
          putStrLn ("insertable: error is " ++ show e ++
                    "\ninsertable part2: error is " ++ show x)
    )

-- insertable: error is SqlError {seState = "", seNativeError = 1
-- , seErrorMsg = "step: SQL logic error or missing database"}

-- insertable part2: error is SqlError {seState = "", seNativeError = 19
-- , seErrorMsg = "finish: column name is not unique"}

Personally I’ve found that Haskell has way too much to offer to let bumps with the database libraries keep me from using it. YMMV.

Previous comments:

Luke Plant said:

Hey, thanks for this tip, that should save me a lot of time. One of the really nice thing about the IO monad is that control structures become functions, so you can wrap up patterns like this rather than repeating them everytime. I also found that when doing things from a GHCI prompt, ‘handleSqlError’ is a real help in terms of getting error messages - just stick ‘handleSqlError $’ in front of whatever statement you are trying.

I actually re-started my project (you mentioned that I had given up), and I’m having more success this time. Finding the time to build a VM for building binaries was the hard part - http://lukeplant.me.uk/blog.php?id=1107301689 .

But also I’ve found that, nearly a year later, the documentation seems to be better, or I’m reading it better, so I’m enjoying it much more now.