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.

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

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)
);

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 (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:

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:

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, stack build to build and stack run to run.

💡 We use 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 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:

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:

[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, which includes DeriveGeneric, 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.