PostgresSQLYesod

Performing transactional DB actions in Yesod

Posted on by

Adding, modifying and removing records in a database (like Postgres) is a very common use case. Usually we perform single actions to a single record. In this tutorial we would like to show how to perform a set of actions in one transaction, so all of them are applied or none in a case of failure.

Let's take the following scenario... We have implemented a simple blog with BlogPost and related Comments in our database:

BlogPost
  uuid Text
  title Text
  content Text
  UniqueBlogPostUuid uuid
  Primary uuid

Comment
  uuid Text
  content Text
  blogPostId BlogPostId
  UniqueCommentUuid uuid
  Primary uuid

Now we would like to delete BlogPost. If there are already comments relating to this BlogPost the action would fail because of ForeignKey contraints. We could cascade the delete command but this is risky in more complex applications, so we decide to remove the related comments first and afterwards the BlogPost itself. This coul look like this inside a Yesod handler:

getBlogPostDeleteR :: BlogPostId -> Handler Html
getBlogPostDeleteR blogPostId = do
  _ <- runDB $ get404 blogPostId
  _ <- runDB $ deleteWhere [CommentBlogPostId ==. blogPostId] 
  _ <- runDB $ delete blogPostId
  redirect BlogIndexR

Here, we first check for the existence of the BlogPost by utilizing Yesods get404 function, which will redirect to a 404 page if the BlogPostId is invalid (no if statements and manual redirects). We can then use deleteWhere to remove all comments and finally remove the BlogPost itself.

But there is a problem here, what if the last delete command fails ? We already deleted all comments but the BlogPost is still there. To avoid this we can move deleteWhere and delete into one transaction:

getBlogPostDeleteR :: BlogPostId -> Handler Html
getBlogPostDeleteR blogPostId = do
  _ <- runDB $ get404 blogPostId
  _ <- runDB $ do
    _ <- deleteWhere [CommentBlogPostId ==. blogPostId] 
    _ <- delete blogPostId
    pure ()
  redirect BlogIndexR

Everything, which is executed inside runDB results in a single transaction. This way we ensure that comments and BlogPost is deleted or none of them.

If you have any questions, suggestions or comments, please use the comment section below.