How to use PostgreSQL with Haskell: persistent + esqueleto
Tags: haskell, functional, database, postgres
Published: 2023-10-03 03:27:59

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

According to the readme, *”[Persistent's](https://github.com/yesodweb/persistent) 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](http://hackage.haskell.org/package/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`](https://hackage.haskell.org/package/persistent) (`2.14.5.1`), [`persistent-postgresql`](https://hackage.haskell.org/package/persistent-postgresql) (`2.13.5.2`), and [`esqueleto`](https://hackage.haskell.org/package/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.

```haskell
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`:

```haskell
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`](https://hackage.haskell.org/package/esqueleto-3.5.10.3/docs/Database-Esqueleto-Experimental.html#v:rawExecute):

```haskell
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:

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

---

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

```haskell
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:

```haskell
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_`:

```haskell
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**.

```haskell
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:

```haskell
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`](https://hackage.haskell.org/package/esqueleto-3.5.10.3/docs/Database-Esqueleto-Experimental.html#v: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 [`:&`](https://hackage.haskell.org/package/esqueleto-3.5.10.3/docs/Database-Esqueleto-Experimental.html#t::-38-) operator to pattern match against the joined tables. We use `_` placeholder to ignore the previous references to the table.

This generates this query:

```sql
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:

```haskell
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](https://hackage.haskell.org/package/exceptions) package to handle exceptions. (We don’t use the exceptions from [Control.Exception](https://hackage.haskell.org/package/base-4.16.3.0/docs/Control-Exception.html) 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](https://www.yesodweb.com/book/persistent), 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](https://www.yesodweb.com/book/persistent#persistent_migrations) 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.