How to use PostgreSQL with Haskell: opaleye
Tags: haskell, functional, postgres, database
Published: 2023-10-03 03:30:33

*Okay, what if we did something quite similar but quite different?*

Opaleye is *“an SQL-generating DSL targeting PostgreSQL. Allows Postgres queries to be written within Haskell in a typesafe and composable fashion”*.

Install [`opaleye`](https://hackage.haskell.org/package/opaleye) (`0.10.1.0` released in 2023) and `product-profunctors`, which the library uses under the hood.

`opaleye` is built on top of `postgresql-simple`, which is used for connection management, transaction support, serialization, and deserialization.

> 💡 We assume you’ve seen the part on `postgresql-simple`.

### How to connect to a database

We use `postgresql-simple` straight away. Reminder:

```haskell
connectionInfo :: ConnectInfo
connectionInfo =
  defaultConnectInfo
    { connectHost = Hardcoded.host
    , connectDatabase = Hardcoded.database
    , connectUser = Hardcoded.user
    , connectPassword = Hardcoded.password
    }
```

```haskell
Simple.withConnect connectionInfo $ \connection -> do
    doFoo connection
    doBar connection
```

### How to define tables

We define a table using the `table` function — specify the table name and the type of fields. 

```haskell
warehouseTable ::
  Table
    (Maybe (Field SqlInt4), ProductIdField, Field SqlInt4, Maybe (Field SqlTimestamp), Maybe (Field SqlTimestamp))
    (Field SqlInt4, ProductIdField, Field SqlInt4, Field SqlTimestamp, Field SqlTimestamp)
warehouseTable =
  table "warehouse"
    $ p5
      ( tableField "id"
      , pProductId (ProductId (tableField "product_id"))
      , tableField "quantity"
      , tableField "created"
      , tableField "modified"
      )
```

`Table` takes **write fields** and **view fields**; for example, the first parameter (`id`) is auto-generated, so it’s optional on write (so we specify `Maybe (Field SqlInt4))` but always there on view/read (so we specify `Field SqlInt4`).

`p5` is a tiny glue function from `product-profunctors`; the number corresponds to the tuple arity  (number of columns). *We don’t need to know more than that.*

`tableField` infers a required or an optional field depending on the write type.

*(We’ll cover `ProductId` a bit later)*

We can also use records instead of tuples; for example, for product:

```haskell
data Product' a b c = Product {pId :: a, pLabel :: b, pDescription :: c}
type Product = Product' ProductId Text (Maybe Text)
deriving instance Show Product

type ProductFieldWrite = Product' (ProductId' (Maybe (Field SqlInt4))) (Field SqlText) (FieldNullable SqlText)
type ProductField = Product' (ProductIdField) (Field SqlText) (FieldNullable SqlText)
```

*Note that we prefix field names because we’ll have some derivable code that can’t handle duplicate record fields.*

`Product'` is polymorphic in all its fields. We’ll use `Product` in “normal” code and `ProductField` when interacting with a database. Because **id** is optional on write, we distinguish between `ProductFieldWrite` and `ProductField`.

We indicate nullable fields with `FieldNullable`, which will be converted into `Maybe` when executed.

We need some typeclass instances, which we can get with Template Haskell:

```haskell
$(makeAdaptorAndInstance "pProduct" ''Product')
```

---

💡 If you’d rather write these by hand, see [Data.Profunctor.Product.TH](https://hackage.haskell.org/package/product-profunctors/docs/Data-Profunctor-Product-TH.html).

---

And then, we define the table:

```haskell
productTable :: Table ProductFieldWrite ProductField
productTable =
  table "product"
    $ pProduct
      Product
        { pId = pProductId $ ProductId $ tableField "id"
        , pLabel = tableField "label"
        , pDescription = tableField "description"
        }
```

Note that instead of `pN`, we use `pProduct`, which we just generated with TH.

The library’s basic tutorial suggests using newtypes for ids. For example, we use one for the product id:

```haskell
newtype ProductId' a = ProductId a
$(makeAdaptorAndInstance "pProductId" ''ProductId')
type ProductId = ProductId' Int
deriving instance Show ProductId

type ProductIdField = ProductId' (Field SqlInt4)
```

*See the [repo](https://github.com/Zelenya/elephants) for the rest of the boilerplate.*

### How to modify data

For raw queries, we can use `postgresql-simple`:

```haskell
cleanUp :: Connection -> IO ()
cleanUp connection =
  void $ Simple.execute_ connection "truncate warehouse, product_category, product, category"
```

Otherwise, we create an insert by using `Insert`:

```haskell
insert1 :: Insert [ProductId]
insert1 =
  Insert
    { iTable = productTable
    , iRows =
        [ Product (ProductId Nothing) "Wood Screw Kit 1" (maybeToNullable Nothing)
        , Product (ProductId Nothing) "Wood Screw Kit 2" (maybeToNullable (Just "245-pieces"))
        ]
    , iReturning = rReturning (\(p :: ProductField) -> p.pId)
    , iOnConflict = Nothing
    }
```

We specify the tables, rows to be inserted, conflict-handling strategy, and what to return. In this case, we return product id using `rReturning`.

And then we run the insert with `runInsert`:

```haskell
insertStuff :: Connection -> IO ()
insertStuff connection = do
  result1 <- runInsert connection insert1
  putStrLn $ "Inserted products: " <> show result1
```

If we want to return the number of affected rows, we can use `rCount`:

```haskell
insert2 :: Insert Int64
insert2 =
  Insert
    { iTable = categoryTable
    , iRows = [Category (CategoryId 1) "Screws", Category (CategoryId 2) "Wood Screws", Category (CategoryId 3) "Concrete Screws"]
    , iReturning = rCount
    , iOnConflict = Nothing
    }
```

### How to query data

The basic select is simple:

```haskell
selectProduct :: Select ProductField
selectProduct = selectTable productTable
```

> 💡 Note that we can use `showSql` to print `Select` and see what sql queries get executed.

We run a select with `runSelect`:

```haskell
queryData :: Connection -> IO ()
queryData connection = do
  result1 :: [Product] <- runSelect connection selectProduct
  putStrLn $ "Query 1: " <> show result1
```

`runSelect` “*converts a "record" of Opaleye fields to a list of "records" of Haskell values.”* We must specify the return type (`[Product]`) to help type inference. 

We can select specific fields and narrow down the results:

```haskell
select2 :: Select (Field SqlText, FieldNullable SqlText)
select2 = do
  (Product _ aLabel description) <- selectProduct
  where_ (aLabel .== "Wood Screw Kit 2")
  pure (aLabel, description)
```

```haskell
result2 :: [(Text, Maybe Text)] <- runSelect connection select2
```

We use `where_` to filter the rows and `.==` to compare for equality. We can also use `in_`:

```haskell
select3 :: Select (Field SqlText)
select3 = do
  p <- selectProduct
  where_ $ in_ ["Wood Screw Kit 2", "Wood Screw Kit 3"] p.pLabel
  pure (p.pLabel)
```

```haskell
result3 :: [Text] <- runSelect connection select3
```

### How to use transactions

We use `postgresql-simple` for transactions:

```haskell
insertWithTransaction :: Connection -> IO ()
insertWithTransaction connection = Simple.withTransaction connection $ do
  [newProduct] :: [ProductId] <-
    runInsert connection
      $ Insert
        { iTable = productTable
        , iRows = [Product (ProductId Nothing) "Drywall Screws Set" (maybeToNullable $ Just "8000pcs")]
        , iReturning = rReturning (\(p :: ProductField) -> p.pId)
        , iOnConflict = Nothing
        }

  [newCategory] :: [CategoryId] <-
    runInsert connection
      $ Insert
        { iTable = categoryTable
        , iRows = [Category (CategoryId 123) "Drywall Screws"]
        , iReturning = rReturning (.cId)
        , iOnConflict = Nothing
        }

  void
    $ runInsert connection
    $ Insert
      { iTable = productCategoryTable
      , iRows = [Mapping (toFields newProduct) (toFields newCategory)]
      , iReturning = rCount
      , iOnConflict = Nothing
      }

  void
    $ runInsert connection
    $ Insert
      { iTable = warehouseTable
      , iRows = [(Nothing, (toFields newProduct), 10, Nothing, Nothing)]
      , iReturning = rCount
      , iOnConflict = Nothing
      }

  putStrLn $ "Insert with transaction"
```

### How to query using joins

Opaleye provides a couple of APIs for joins. They recommend using [`where_`](https://hackage.haskell.org/package/opaleye-0.10.0.0/docs/Opaleye-Operators.html#v:where_) directly for inner joins and [`optional`](https://hackage.haskell.org/package/opaleye-0.10.0.0/docs/Opaleye-Join.html#v:optional) for left/right joins. Which gives us something like this:

```haskell
join :: Select (Field SqlInt4, Field SqlText, FieldNullable SqlText, MaybeFields (Field SqlText))
join = do
  (_, wProductId, quantity, _, _) <- selectTable warehouseTable
  p <- selectTable productTable
  mpc <- optional $ do
    pc <- selectTable productCategoryTable
    where_ $ pc.productId .=== p.pId
    pure pc
  mc <- optional $ do
    c <- selectTable categoryTable
    where_ $ isJustAnd mpc $ \pc -> c.cId .=== pc.categoryId
    pure c

  where_ $ wProductId .=== p.pId
  where_ $ quantity .> 3

  let category = cLabel <$> mc
  pure (quantity, p.pLabel, p.pDescription, category)
```

> 💡 Note that we use `isJustAnd` that will be added to Opaleye in the future.
> 
> ```haskell
> isJustAnd :: MaybeFields a -> (a -> Field SqlBool) -> Field SqlBool
> isJustAnd ma cond = matchMaybe ma $ \case
>   Nothing -> sqlBool False
>   Just a -> cond a
> ``` 

Which generates:

```SQL
SELECT
"quantity2_1" as "result1_7",
"label1_2" as "result2_7",
"description2_2" as "result3_7",
NOT (("rebind0_6") IS NULL) as "result4_7",
"label1_5" as "result5_7"
FROM (SELECT
      *
      FROM (SELECT *
            FROM
            (SELECT *
             FROM
             (SELECT
              *
              FROM (SELECT
                    "id" as "id0_1",
                    "product_id" as "product_id1_1",
                    "quantity" as "quantity2_1",
                    "created" as "created3_1",
                    "modified" as "modified4_1"
                    FROM "warehouse" as "T1") as "T1",
                   LATERAL
                   (SELECT
                    "id" as "id0_2",
                    "label" as "label1_2",
                    "description" as "description2_2"
                    FROM "product" as "T1") as "T2") as "T1"
             LEFT OUTER JOIN
             LATERAL
             (SELECT
              TRUE as "rebind0_4",
              *
              FROM (SELECT
                    *
                    FROM (SELECT
                          "product_id" as "product_id0_3",
                          "category_id" as "category_id1_3"
                          FROM "product_category" as "T1") as "T1"
                    WHERE (("product_id0_3") = ("id0_2"))) as "T1") as "T2"
             ON
             TRUE) as "T1"
            LEFT OUTER JOIN
            LATERAL
            (SELECT
             TRUE as "rebind0_6",
             *
             FROM (SELECT
                   *
                   FROM (SELECT
                         "id" as "id0_5",
                         "label" as "label1_5"
                         FROM "category" as "T1") as "T1"
                   WHERE (CASE WHEN NOT (("rebind0_4") IS NULL) THEN ("id0_5") = ("category_id1_3") ELSE CAST(FALSE AS boolean) END)) as "T1") as "T2"
            ON
            TRUE) as "T1"
      WHERE (("quantity2_1") > (CAST(3 AS integer))) AND (("product_id1_1") = ("id0_2"))) as "T1"
```

### Errors

Once again, type-safety and query validation equal compilation errors. 

But because there isn’t much “type-level magic”, we only need to occasionally help the compiler with type inference. And it’s mainly about input and return types — not intermediate/internal library structures. For example, `[Product]` in this snippet:

```haskell
result :: [Product] <- runSelect connection selectProduct
 where
  selectProduct = selectTable productTable
```

Sometimes, if you don’t specify enough types, profunctors show up:

```haskell
Ambiguous type variable ‘haskells0’ arising from a use of ‘runSelect’
prevents the constraint ‘(Default
                            FromFields fields0 Product)’ from being solved.
Probable fix: use a type annotation to specify what ‘haskells0’ should be.
```

Runtime sql errors are again from `postgresql-simple`. Review the relevant error section if you need a reminder.

```haskell
errors :: Connection -> IO ()
errors connection = do
  insertDuplicateScrew
  insertDuplicateScrew
    `catch` (\err@SqlError{} -> putStrLn $ "Caught SQL Error: " <> displayException err)
 where
  insertDuplicateScrew =
    void
      $ runInsert connection
      $ Insert
        { iTable = productTable
        , iRows = [Product (ProductId Nothing) "Duplicate screw" (maybeToNullable Nothing)]
        , iReturning = rCount
        , iOnConflict = Nothing
        }
```

### Resources

There are a couple of basic tutorials in the repo and some external ones.

One thing to remember: sometimes, the library provides multiple ways of doing things (for example, left joins using `optional` vs. deprecated `leftJoin` or monadic vs. arrow syntax), and documentation/tutorials can do it one way or even deprecated way.

### Migrations

Opaleye assumes a database already exists — no support for migrations or creating tables and databases.

### In summary

Opaleye allows us to define tables and write type-safe postgres queries using Haskell code.

The library uses `product-profunctors` and typeclasses. Both only come up in copy-pasteable boilerplate and when you under-specify the return types. No deep knowledge is required.