How to use PostgreSQL with Haskell. Elephantine library review 2023
Tags: haskell, functional, database, postgres
Published: 2023-10-03 03:23:46

As of October 2023, there are around a dozen mature PostgreSQL libraries, all levels of abstractions, from low-level queries to the type level. More than enough for anybody. *(Shout out to everyone who says Haskell has no libraries.)* 

Which one to use? Let’s see. Foreach library, we’ll talk about features and basics like writing queries, complexity, pitfalls, (everyone’s favorite topic) errors, and so on.

*We assume you are familiar with the fundamentals of SQL and PostgreSQL.*

--- 

📹 Hate reading articles? Check out the complementary video, which covers the same content.

{% embed https://youtu.be/KCrCazaRHUA %}


## Mise en place

Before integrating with the database, let’s discuss the data and the setup.

> 💡 If you want to follow allow at home, the [repository](https://github.com/Zelenya/elephants ) contains all the imports and data types — we omit most of them from the tutorial for simplicity.

### Data

Let’s imagine that we’re building a (tiny) warehouse management system: 

- **Warehouse** has multiple **products**, some quantity of each.
- **Product** has a label and a description.
- **Product** can belong to **categories** (*many-to-many*).
- **Category** has a label.

The `scripts/create_tables.sql` contains all the definitions:

```sql
CREATE TABLE product (
    id SERIAL PRIMARY KEY,
    label TEXT NOT NULL,
    description TEXT,
    UNIQUE (label)
);

CREATE TABLE category (
    id SERIAL PRIMARY KEY,
    label TEXT NOT NULL,
    UNIQUE (label)
);

CREATE TABLE product_category (
    category_id INT NOT NULL,
    product_id INT NOT NULL,
    PRIMARY KEY (category_id, product_id),
    FOREIGN KEY (product_id) REFERENCES product(id),
    FOREIGN KEY (category_id) REFERENCES category(id)
);

CREATE TABLE warehouse (
    id SERIAL PRIMARY KEY, 
    product_id INT NOT NULL,
    quantity INT NOT NULL,
    created TIMESTAMP,
    modified TIMESTAMP,
    FOREIGN KEY (product_id) REFERENCES product(id)
);
```

![Schema](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/4am4fw9yxo953l1c75tj.png)

### Postgres server

Here are a few things you need to know to play along at home. 

> 🐘 If you don’t care, don’t like docker, or already have an easily-accessible postgres server, feel free to skip this section.

First, install docker. 

`docker compose up` (`docker-compose up` on older versions) starts [PostgreSQL](https://hub.docker.com/_/postgres) (see `docker-compose.yml`) and initializes the databases and tables (using the `scripts/create_tables.sql`). It mounts the data to the `postgres-data/` (in case you need to wipe it or something).

Some of the hardcoded things that the code relies on:

```docker
environment:
  - POSTGRES_DB=warehouse
  - POSTGRES_USER=postgres
  - POSTGRES_PASSWORD=password
ports:
  - 5432:5432
```

> 💡 These hardcoded values are also hardcoded in the code in `Hardcoded.hs` (for laziness reasons)

You can connect to the container and run arbitrary queries using `psql`: 

```bash
docker exec -it elephants-postgres-1 psql -U postgres -d warehouse
```

**Note:** `elephants-postgres-1` is a container name, which might be different for you; check with `docker ps` to get the correct container id (or name). We also pass a couple of flags: `-U postgres` for the user name and `-d warehouse` for the database name.

`docker compose down` to stop and remove the containers.

### Project Overview

If you have `stack` [installed](https://www.haskell.org/ghcup/install/#how-to-install), `stack build` to build and `stack run` to run. 

> 💡 We use [lts-21.7](https://www.stackage.org/lts-21.7) (ghc-9.4.5), published on 2023-08-14.
>

> 🤷 To build the backend, you might need the `libpq` development libraries installed (e.g., `libpq-dev` on Debian-based distributions).
> 
### Extensions overview

Note that most of the libraries rely on using various extensions. Here is a quick overview of the most important ones.

---

`OverloadedStrings`

Used to simplify the construction of query values — we can use literal strings, like `"SELECT * FROM user"`, instead of manually constructing the whole type; for example, `Query . toByteString . stringUtf8 $ "SELECT * FROM user"` (see [Query](https://hackage.haskell.org/package/postgresql-simple-0.7.0.0/docs/Database-PostgreSQL-Simple.html#t:Query) in `postgresql-simple`).

---

`TemplateHaskell`

Template Haskell (TH) is fairly useful for generating boilerplate code. Some libraries provide the TH machinery to derive typeclass instances and/or generate custom type-safe data types at the compile time.

---

`DeriveAnyClass` and `DeriveGeneric` enable alternative ways to get free typeclass instances.

We’ll use `DerivingStrategies` to make the derivation explicit, for example:

```haskell
data Category = Category {label :: Text}
  deriving (Show, Generic)           -- Derive Generic instance,
  deriving anyclass (ToRow, FromRow) -- used for these instances
```

---

`QuasiQuotes`

Some libraries (e.g., `postgresql-simple`) provide quasi quoters for less tedious sql construction in Haskell code. For example:

```haskell
[sql| select label from product |]
```

---

`DuplicateRecordFields`

Only one postgres library requires it. But it’s a stylistic choice — we use this extension because we have multiple data types with a `label` field.

---

`OverloadedRecordDot`

To access record fields as well as specific columns of the tables (for example, `product.label`)

---

Note that the project uses [`GHC2021`](https://ghc.gitlab.haskell.org/ghc/doc/users_guide/exts/control.html#extension-GHC2021), which includes [`DeriveGeneric`](https://ghc.gitlab.haskell.org/ghc/doc/users_guide/exts/generics.html#extension-DeriveGeneric), [`TypeApplications`](https://ghc.gitlab.haskell.org/ghc/doc/users_guide/exts/type_applications.html#extension-TypeApplications), and many other extensions required for some libraries to work. Also, for the tutorial's sake, we’ll use the included `ScopedTypeVariables` to demonstrate some intermediate types.