DEV Community

Zelenya
Zelenya

Posted on • Updated on

How to use PostgreSQL with Haskell: opaleye

#haskell #functional #postgres #database

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

connectionInfo :: ConnectInfo
connectionInfo =
  defaultConnectInfo
    { connectHost = Hardcoded.host
    , connectDatabase = Hardcoded.database
    , connectUser = Hardcoded.user
    , connectPassword = Hardcoded.password
    }
Enter fullscreen mode Exit fullscreen mode 
Simple.withConnect connectionInfo $ \connection -> do
    doFoo connection
    doBar connection
Enter fullscreen mode Exit fullscreen mode

How to define tables

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

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"
      )
Enter fullscreen mode Exit fullscreen mode

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:

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)
Enter fullscreen mode Exit fullscreen mode

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:

$(makeAdaptorAndInstance "pProduct" ''Product')
Enter fullscreen mode Exit fullscreen mode

💡 If you’d rather write these by hand, see Data.Profunctor.Product.TH.

And then, we define the table:

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

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:

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

type ProductIdField = ProductId' (Field SqlInt4)
Enter fullscreen mode Exit fullscreen mode

See the repo for the rest of the boilerplate.

How to modify data

For raw queries, we can use postgresql-simple:

cleanUp :: Connection -> IO ()
cleanUp connection =
  void $ Simple.execute_ connection "truncate warehouse, product_category, product, category"
Enter fullscreen mode Exit fullscreen mode

Otherwise, we create an insert by using Insert:

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
    }
Enter fullscreen mode Exit fullscreen mode

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:

insertStuff :: Connection -> IO ()
insertStuff connection = do
  result1 <- runInsert connection insert1
  putStrLn $ "Inserted products: " <> show result1
Enter fullscreen mode Exit fullscreen mode

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

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
    }
Enter fullscreen mode Exit fullscreen mode

How to query data

The basic select is simple:

selectProduct :: Select ProductField
selectProduct = selectTable productTable
Enter fullscreen mode Exit fullscreen mode

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

We run a select with runSelect:

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

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:

select2 :: Select (Field SqlText, FieldNullable SqlText)
select2 = do
  (Product _ aLabel description) <- selectProduct
  where_ (aLabel .== "Wood Screw Kit 2")
  pure (aLabel, description)
Enter fullscreen mode Exit fullscreen mode 
result2 :: [(Text, Maybe Text)] <- runSelect connection select2
Enter fullscreen mode Exit fullscreen mode

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

select3 :: Select (Field SqlText)
select3 = do
  p <- selectProduct
  where_ $ in_ ["Wood Screw Kit 2", "Wood Screw Kit 3"] p.pLabel
  pure (p.pLabel)
Enter fullscreen mode Exit fullscreen mode 
result3 :: [Text] <- runSelect connection select3
Enter fullscreen mode Exit fullscreen mode

How to use transactions

We use postgresql-simple for transactions:

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"
Enter fullscreen mode Exit fullscreen mode

How to query using joins

Opaleye provides a couple of APIs for joins. They recommend using where_ directly for inner joins and optional for left/right joins. Which gives us something like this:

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)
Enter fullscreen mode Exit fullscreen mode

💡 Note that we use isJustAnd that will be added to Opaleye in the future.

isJustAnd :: MaybeFields a -> (a -> Field SqlBool) -> Field SqlBool
isJustAnd ma cond = matchMaybe ma $ \case
  Nothing -> sqlBool False
  Just a -> cond a

Which generates:

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"
Enter fullscreen mode Exit fullscreen mode

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:

result :: [Product] <- runSelect connection selectProduct
 where
  selectProduct = selectTable productTable
Enter fullscreen mode Exit fullscreen mode

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

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.
Enter fullscreen mode Exit fullscreen mode

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

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
        }
Enter fullscreen mode Exit fullscreen mode

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.

Top comments (0)

Read next

vkazulkin profile image

Data API for Amazon Aurora Serverless v2 with AWS SDK for Java - Part 2 Executing SQL statements

Vadym Kazulkin -

josethz00 profile image

Benchmark: Snowflake vs UUIDv4

José Thomaz -

tommyc profile image

SQL Database Normalization with Easy Examples

Tommy -

jao_dev profile image

Docker para iniciantes: Criando Containers de Bancos de Dados

João Vitor -