How to use PostgreSQL with Haskell: postgresql-simple

Let’s start simple. 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.

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, bracket, or (better) a connection pool.

The library doesn’t support pools, but you can use the resource-pool package (or something similar).

How to modify data

We use execute and 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:

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

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:

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:

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

But preferable, we use dedicated types:

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 typeclass, which is derivable using GHC generics:

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:

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_ takes a query with no substitutes:

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 (recall ToRow from the previous section). For example, let’s get a BasicProduct list by label using query:

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:

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 ids of created rows:

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 to wrap multiple queries in a transaction:

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, commit, and 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:

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. For instance, if we have a mismatching number of substitutions (? and actual values):

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

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:

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. For example, if we use execute with insert query that has returning:

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:

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.

🗂️ This is a fork of an archived postgresql-simple-migration

In summary

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.