How to use PostgreSQL with Haskell: postgresql-simple
Tags: haskell, functional, database, postgres
Published: 2023-10-03 03:25:15

Let’s start simple. [postgresql-simple](https://hackage.haskell.org/package/postgresql-simple) describes itself as *“Mid-Level PostgreSQL client library“*.

In other words, we’ll write raw sql queries, and the library will deal with security and stuff.

To get started, we add `postgresql-simple` to dependencies. We’re using `v0.7.0.0` published in 2023.

### How to connect to a database

We use `connect` to acquire a connection. It accepts `ConnectInfo`, which we can get by using `defaultConnectInfo` and overriding some defaults.

```haskell
getConnection :: IO Connection
getConnection =
  connect $
    defaultConnectInfo
      { connectHost = Hardcoded.host
      , connectDatabase = Hardcoded.database
      , connectUser = Hardcoded.user
      , connectPassword = Hardcoded.password
      }
```

Eventually, we have to `close connection`. But you will probably not need to do it manually because you can use [`withConnect`](https://hackage.haskell.org/package/postgresql-simple-0.7.0.0/docs/Database-PostgreSQL-Simple.html#v:withConnect), [`bracket`](https://hackage.haskell.org/package/base-4.18.0.0/docs/Control-Exception.html#v:bracket), or (better) a **connection pool**.

The library doesn’t support pools, but you can use the [resource-pool](https://hackage.haskell.org/package/resource-pool) package (or something similar).

### How to modify data

We use [`execute`](https://hackage.haskell.org/package/postgresql-simple-0.7.0.0/docs/Database-PostgreSQL-Simple.html#v:execute) and [`execute_`](https://hackage.haskell.org/package/postgresql-simple-0.7.0.0/docs/Database-PostgreSQL-Simple.html#v:execute_) to insert, update, and delete data.

The version with the `_` suffix is simpler — it doesn’t perform any query substitutions. We can use it with hardcoded values or with straightforward queries such as truncating the tables:

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

Both *execute* functions return the number of affected rows, which isn’t relevant in case of truncate (it’s `0`) and safe to ignore ([`void`](https://hackage.haskell.org/package/base-4.18.0.0/docs/Control-Monad.html#v:void) ignores the result of evaluation).

We can use `execute` to make a proper insert and pass some values for substitutions. The simplest way is to pass a tuple:

```haskell
insert1 <-
  execute
    connection
    "insert into product (label, description) values (?, ?)"
    ("Wood Screw Kit 1" :: Text, "245-pieces" :: Text)
```

Sometimes, we must be explicit about types; for example, in cases like this, when we use string literals with `OverloadedStrings` or numeric literals (like `245`).

Because there is no tuple of 1, the library provides a custom type `Only`:

```haskell
insert2 <-
  execute
    connection
    "insert into product (label) values (?)"
    (Only "Wood Screw Kit 2" :: Only Text)
```

Alternatively, we can use lists for any number of values:

```haskell
insert3 <-
  execute
    connection
    "insert into product (label) values (?)"
    ["Wood Screw Kit 3" :: Text]
```

But preferable, we use dedicated types:

```haskell
execute
  connection
  "insert into product (label, description) values (?, ?)"
  (BasicProduct "Wood Screw Kit 4" (Just "245-pieces"))
```

A record can be turned into a list of substitutions via the [`ToRow`](https://hackage.haskell.org/package/postgresql-simple-0.7.0.0/docs/Database-PostgreSQL-Simple.html#t:ToRow) typeclass, which is derivable using GHC generics:

```haskell
data BasicProduct = BasicProduct {label :: Text, description :: Maybe Text}
  deriving (Show, Generic)
  deriving anyclass (ToRow, FromRow)
```

If we want to modify multiple rows, we can use [`executeMany`](https://hackage.haskell.org/package/postgresql-simple-0.7.0.0/docs/Database-PostgreSQL-Simple.html#v:executeMany):

```haskell
insert5 <-
 executeMany
   connection
   insert into category (label) values (?)"
   [Category "Screws", Category "Wood Screws", Category "Concrete Screws"]
```

### How to query data

The execute functions can’t return any results (other than the number of affected rows), so we have to use the query functions.

Similar to `execute_`, [`query_`](https://hackage.haskell.org/package/postgresql-simple-0.7.0.0/docs/Database-PostgreSQL-Simple.html#v:query_) takes a query with no substitutes:

```haskell
query1 :: [(Int64, Text, Maybe Text)] <-
  query_ connection "select id, label, description from product"
```

Note that we must be explicit about return types — the library can’t guess what we want. In this case, we expect an id of type `Int64` that corresponds to `Serial`, required `Text` label, and optional description.

We can specify a record return type if we derive [`FromRow`](https://hackage.haskell.org/package/postgresql-simple-0.7.0.0/docs/Database-PostgreSQL-Simple.html#t:FromRow) (recall `ToRow` from the previous section). For example, let’s get a `BasicProduct` list by label using `query`:

```haskell
query2 :: [BasicProduct] <-
  query 
    connection 
    "select label, description from product where label = ? "
    (Only "Wood Screw Kit 2" :: Only Text)
```

If we want to use the in-clause, the library provides a dedicated wrapper:

```haskell
query3 :: [BasicProduct] <-
  query connection "select label, description from product where label in ?" $
    Only (In ["Wood Screw Kit 2" :: Text, "Wood Screw Kit 3"])
```

### How to use transactions

Imagine we want to atomically insert a new listing: product, category, and quantity. This touches multiple tables and requires a transaction. Additionally, because we have a many-to-many relationship, we must first insert the product and category and then use their new ids to create a mapping.

We can use `returning` to get `id`s of created rows:

```haskell
productIds :: [Only Int64] <-
  query
    connection
    "insert into product (label, description) values (?, ?) returning id"
    (BasicProduct "Drywall Screws Set" (Just "8000pcs"))

categoryIds :: [Only Int64] <-
  query
    connection
    "insert into category (label) values (?) returning id"
    (Category "Drywall Screws")
```

Note that we must use `query` and not `execute` because these queries return results.

We can use [`withTransaction`](https://hackage.haskell.org/package/postgresql-simple-0.7.0.0/docs/Database-PostgreSQL-Simple.html#v:withTransaction) to wrap multiple queries in a transaction: 

```haskell
withTransaction connection $ do
  productIds :: [Only Int64] <- query ...
  categoryIds :: [Only Int64] <- query ...

  void $ case (productIds, categoryIds) of
    ([Only productId], [Only categoryId]) -> do
      _ <-
        execute
          connection
          "insert into warehouse (product_id, quantity, created, modified) values (?, ?, now(), now())"
          (productId, 10 :: Int)

      execute
        connection
        "insert into product_category (category_id, product_id) values (?, ?)"
        (categoryId, productId)
    _ -> 
      throwIO $ userError "Failed to insert product/category"
```

Any error will rollback the transaction (and the exception will be rethrown). In this example, we throw an explicit error if we don’t get the expected ids for some reason.

Note that in case of a **sql error**, the exception will not only rollback the transaction but, if uncaught, will propagate further (killing everything on its way and potentially crashing the whole app). So, we should (at least) wrap transactions in the exception handler(s); we’ll see how to do this later.

When you need to, you can also use granular transaction functions: [begin](https://hackage.haskell.org/package/postgresql-simple-0.7.0.0/docs/Database-PostgreSQL-Simple.html#v:begin), [commit](https://hackage.haskell.org/package/postgresql-simple-0.7.0.0/docs/Database-PostgreSQL-Simple.html#v:commit), and [rollback](https://hackage.haskell.org/package/postgresql-simple-0.7.0.0/docs/Database-PostgreSQL-Simple.html#v:rollback).

### How to query using joins

To read all these tables at once, we need to query using a few joins. The library provides a quasi-quoter that makes writing big queries easier — we can format the query and not worry about whitespaces:

```haskell
result :: [Listing] <-
  query
    connection
    [sql|
      select
        w.quantity,
        p.label,
        p.description,
        c.label
      from warehouse as w
      inner join product as p on w.product_id = p.id
      left outer join product_category as pc on p.id = pc.product_id
      left outer join category as c on c.id = pc.category_id
      where w.quantity > (?)|]
    [3 :: Int]
```

### Errors

In `postgresql-simple`, all the programmer errors (in sql or library usage) are (runtime) exceptions.

If the query string is not formatted correctly, we get [`FormatError`](https://hackage.haskell.org/package/postgresql-simple-0.7.0.0/docs/Database-PostgreSQL-Simple.html#t:FormatError). For instance, if we have a mismatching number of substitutions (`?` and actual values):

```haskell
execute
  connection
  "INSERT INTO category (label) VALUES (?)"
  ("One" :: Text, "Two" :: Text)
```

> `FormatError {fmtMessage = "1 single '?' characters, but 2 parameters", fmtQuery = "INSERT INTO category (label) VALUES (?)", fmtParams = ["One","Two"]}`
> 

Similarly, on the return side, if the number of columns doesn’t match the number of elements in the result type (in a list, a tuple, or a record), we get [`ResultError`](https://hackage.haskell.org/package/postgresql-simple-0.7.0.0/docs/Database-PostgreSQL-Simple.html#t:ResultError). The most likely variants are `Incompatible` and `UnexpectedNull`.

If we forget to wrap a nullable type on the Haskell side, we get `UnexpectedNull`. For instance, if we try to get `description` (which is nullable) as `Text` and not `Maybe Text`:

```haskell
let result :: IO [(Text, Text)] = query_ connection "select label, description from product"
```

> `UnexpectedNull {errSQLType = "text", errSQLTableOid = Just (Oid 16386), errSQLField = "description", errHaskellType = "Text", errMessage = ""}`
> 

If we mistype the types, we get `Incompatible`. For instance, if we try to parse just `id` into `BasicProduct`:

```haskell
let result :: IO [BasicProduct] = query_ connection "select id from product"
```

> `Incompatible {errSQLType = "int4", errSQLTableOid = Just (Oid 16386), errSQLField = "id", errHaskellType = "Text", errMessage = "types incompatible"}`
> 

On top of that, if we misuse the library — by mistaking `query` for `execute` or vice verse — we get [`QueryError`](https://hackage.haskell.org/package/postgresql-simple-0.7.0.0/docs/Database-PostgreSQL-Simple.html#t:QueryError). For example, if we use `execute` with *insert* query that has *returning*:

```haskell
execute_
  connection
  "INSERT INTO category (label) VALUES (Screws) returning id"
```

> `QueryError {qeMessage = "execute resulted in Col 1-column result", qeQuery = "INSERT INTO category (label) VALUES ('Deck Screws') returning id"}`
> 

And last but not least, any sql errors from postgres, will come back as [`SqlError`](https://hackage.haskell.org/package/postgresql-simple-0.7.0.0/docs/Database-PostgreSQL-Simple.html#t:SqlError):

```haskell
let result :: IO [BasicProduct] = query_ connection "select I have no idea what I'm doing"
```

> `Wrong sql: SqlError {sqlState = "42601", sqlExecStatus = FatalError, sqlErrorMsg = "syntax error at or near \"no\"", sqlErrorDetail = "", sqlErrorHint = ""}`
> 

The errors are pretty good but still not the most descriptive — if you try to write big queries, you have to concentrate on projecting the error information to the query.

### Resources

The docs are also simple; the library covers all the primary blocks, describes the functions, and provides some examples. Outside, a few blog posts cover similar things, mainly targeting beginners.

And you don’t need more than that — if you know how to write one simple query, you know how to write them all.

### Migrations

The library has a companion package, [`postgresql-migration`](https://hackage.haskell.org/package/postgresql-migration).

> 🗂️ This is a fork of an archived [`postgresql-simple-migration`](https://hackage.haskell.org/package/postgresql-simple-migration)

### In summary

[postgresql-simple](https://hackage.haskell.org/package/postgresql-simple) is a library for all levels and a great option if you love writing sql by hand and don’t need reusability.

It doesn’t parse or validate the queries, so we must pay attention to what we write: sql queries, haskell types (type-safety is our responsibility), the order of parameters, and so on.