How to use PostgreSQL with Haskell: persistent + esqueleto

If that was not enough, it’s time to move to the type level.

According to the readme, ”Persistent's goal is to catch every possible error at compile-time, and it comes close to that. It is also designed to be adaptable to any datastore”. As a result, ”a major limitation for SQL databases is that the persistent library does not directly provide joins”.

However, we can use Esqueleto (”a bare bones, type-safe EDSL for SQL queries”) with Persistent's serialization to write type-safe SQL queries. It’s unlikely that you want to use Persistent by itself with SQL, so let’s use and review them together.

We’re using persistent (2.14.5.1), persistent-postgresql (2.13.5.2), and esqueleto (3.5.10.1), all published in 2023. Additionally, we’ll use the experimental style, which will become the new "default" in esqueleto-4.0.0.0.

We’ll also use mtl, monad-logger, unliftio-core, time, and exceptions.

The libraries require additional extensions: DataKinds, GADTs, TypeFamilies, and UndecidableInstances.

💡 (It’s not very important, but) We assume you’ve seen the part on postgresql-simple, which covers the same topics but at a slower pace.

How to connect to a database

Database.Persist.Postgresql provides various ways to connect to postgres with and without a connection pool.

First, we need a libpq connection string, which looks like this "host=localhost port=5432 user=postgres dbname=warehouse password=password".

We create the pool and run actions on it using withPostgresqlPool and passing the connection string, number of connections, and action(s) to be executed. We use liftSqlPersistMPool to run an action/transaction on a pool. And “finally”, use runNoLoggingT (runStdoutLoggingT, or alternative) to run with appropriate logging.

runNoLoggingT $ P.withPostgresqlPool Hardcoded.connectionString 3 $ \pool -> do
    le runWithPool = flip liftSqlPersistMPool pool
    runWithPool transaction1
    runWithPool transaction2
    ...

💡 We can use runStdoutLoggingT to see what sql queries get executed.

How to define tables

Persistent takes care of creating and matching Haskell datatypes and PersistEntity instances; we need to declare the entities by passing them to mkPersist:

mkPersist
  sqlSettings
  [persistLowerCase|
  Product
    label Text
    description Text Maybe
    UniqueLabel label
    deriving Eq Show
  Category
    label Text
    UniqueCategory label
    deriving Eq Show
  ProductCategory
    productId ProductId
    categoryId CategoryId
    Primary productId categoryId
    deriving Eq Show
  Warehouse
    productId ProductId
    quantity Int
    created UTCTime default=CURRENT_TIME
    modified UTCTime default=CURRENT_TIME
    deriving Eq Show
|]

persistLowerCase states that SomeTable corresponds to the SQL table some_table.

How to modify data

Even though it’s not encouraged, we can always execute raw sql; for example, we can truncate tables with rawExecute:

cleanUp :: (MonadIO m) => SqlPersistT m ()
cleanUp = rawExecute "truncate warehouse, product_category, product, category" []

What’s SqlPersistT m ()? Let’s say it’s something that can be executed with runWithPool and returns ().

💡 Note that we can also use deleteWhere to delete all the records from a table:

deleteWhere ([] :: [Filter Product]))

Because we’ve done all the groundwork, we use records right away (no tuples):

insertStuff :: (MonadIO m) => SqlPersistT m ()
insertStuff = do
  newId <- insert $ Product "Wood Screw Kit 1" (Just "245-pieces")
  liftIO $ putStrLn $ "Insert 1: " <> show newId

  newIds <- insertMany [Category "Screws", Category "Wood Screws", Category "Concrete Screws"]
  liftIO $ putStrLn $ "Insert 2: " <> show newIds

That’s it! Persistent is concise when it comes to inserts. Note that insert returns the id, and insertMany returns multiple ids.

We can use liftIO to execute IO operations like printing “inside” SqlPersistT.

How to query data

This is the part where esqueleto comes in.

The first query takes a label and returns a list of product entities:

query1 :: Text -> SqlPersistT m [Entity Product]
query1 label = select $ do
  aProduct <- from $ table @Product
  where_ (aProduct.label ==. val label)
  pure aProduct

It returns an Entity instead of a value — an Entity combines a database id and a value.

This is an experimental syntax that mimics sql. We use the TypeApplications extensions to make the table explicit, OverloadedRecordDot to select the field/column value, the ==. operator to check for equality, and val to “lift” haskell value into “sql query land”.

💡 Note that there are other alternatives for field projections (instead of OverloadedRecordDot), such as the (^.) operator and OverloadedLabels.

We can select multiple labels using in_:

query2 :: [Text] -> SqlPersistT m [Entity Product]
query2 lables = select $ do
  aProduct <- from $ table @Product
  where_ $ aProduct.label `in_` valList lables
  pure aProduct

How to use transactions

We’ve been kind-of using transactions all this time. Everything inside a single call to liftSqlPersistMPool (and other versions, with and without pooling) runs in a single transaction.

insertWithTransaction :: (MonadIO m, MonadCatch m) => SqlPersistT m ()
insertWithTransaction = handle (\(SomeException _) -> pure ()) $ do
  productId <- insert $ Product "Drywall Screws Set" (Just "8000pcs")
  categoryId <- insert $ Category "Drywall Screws"
  time <- liftIO getCurrentTime
  _ <- insert_ $ Warehouse productId 10 time time
  _ <- insert_ $ ProductCategory productId categoryId
  liftIO $ putStrLn $ "Insert with transaction"

This time, we handle exceptions (any SomeException).

💡 We generally want to split the queries into transactions and catch exceptions on each transaction. We dive deeper into error handling in the errors section.

How to query using joins

And this is the part where experimental syntax comes in handy:

query quantity = select $ do
  (warehouse :& aProduct :& _ :& category) <-
    from
      $ table @Warehouse
      `innerJoin` table @Product
      `on` do \(w :& p) -> w.productId ==. p.id
      `LeftOuterJoin` table @ProductCategory
      `on` do \(_ :& p :& pc) -> just p.id ==. pc.productId
      `LeftOuterJoin` table @Category
      `on` do \(_ :& _ :& pc :& c) -> pc.categoryId ==. c.id
  where_ (warehouse.quantity >. val quantity)
  pure $ (warehouse.quantity, aProduct.label, aProduct.description, category.label)

The on clauses are attached directly to the relevant join. The ON clause lambda has all the available tables — only the tables we have already joined into are in scope.

We use the :& operator to pattern match against the joined tables. We use _ placeholder to ignore the previous references to the table.

This generates this query:

SELECT 
  "warehouse"."quantity", 
  "product"."label", 
  "product"."description", 
  "category"."label" 
FROM 
  "warehouse" 
  INNER JOIN "product" ON "warehouse"."product_id" = "product"."id" 
  LEFT OUTER JOIN "product_category" ON "product"."id" = "product_category"."product_id" 
  LEFT OUTER JOIN "category" ON "product_category"."category_id" = "category"."id" 
WHERE 
  "warehouse"."quantity" > ?

Errors

It’s possible to write type-checked queries that fail at runtime, but most typical sql errors are caught as compile-time errors.

Sometimes, mistakes in queries will result in error messages that refer to library internals (for example, you might see PersistUniqueRead backend0, Database.Esqueleto.Internal.Internal.SqlExpr, PersistRecordBackend backend val, ‘BaseBackend backend0’, ‘SqlBackend’). This takes some time to get used to. Help the type inference, and it will help you.

Nobody is safe from runtime sql errors. For example, if we violate the uniqueness constraint, we get an exception that we need to deal with:

errors :: (MonadIO m, MonadCatch m) => SqlPersistT m ()
errors = do
  let duplicateScrew = Product "Duplicate screw" Nothing
  void $ insert duplicateScrew
  (void $ insert duplicateScrew)
    `catch` (\(SomeException err) -> liftIO $ putStrLn $ "Caught SQL Error: " <> displayException err)

Caught SQL Error: SqlError {sqlState = "23505", sqlExecStatus = FatalError, sqlErrorMsg = "duplicate key value violates unique constraint \"product_label_key\"", sqlErrorDetail = "Key (label)=(Duplicate screw) already exists.", sqlErrorHint = ""}

Note that we use the exceptions package to handle exceptions. (We don’t use the exceptions from Control.Exception as we did in postgresql-simple because we don’t want to be limited to IO).

Resources

persistent is well documented as part of the yesod book, and esqueleto has good readme and docs. The catch is that you have to keep an eye on multiple packages simultaneously.

On top of that, (currently) esqueleto supports legacy and experimental syntax, and you have to be aware that some tutorials and examples use less safe legacy syntax (or a mix of both) — the good news is that the compiler will warn you if you’re on the wrong path.

Migrations

persistent can automatically create tables and do migrations. However, the book discourages that:

“Using automated database migrations is only recommended in development environments. Allowing your application to modify your database schema in a production environment is very strongly discouraged.”

In summary

You should consider persistent with esqueleto if you mainly have a lot of simple queries, are tired of writing raw sql, but want moderately more type-safe and composable sql.

The persistent is a (very) generalized library, meaning you should be comfortable using abstractions. And you should be familiar with mtl, monad-logger, lifting/unlifting IO, and so on.

“Most kinds of errors committed when writing SQL are caught as compile-time errors — although it is possible to write type-checked esqueleto queries that fail at runtime”

If you look around, some tutorials and comparisons might say that esqueleto joins might lead to to runtime errors. Don’t worry — this refers to legacy syntax — use new/experimental syntax.