Opaleye Implementation Guide

Learning Goal

If you are considering using Opaleye in a mid-to-large scale project, there are a number of decisions that you will need to take as an Opaleye user. This chapter guides you through all such decisions. This is battle-tested (but opinionated) advice based on our experience of using Opaleye at Vacation Labs, where our product has more than 150 DB tables and more than 70,000 lines of Haskell code¹

• At the very beginning you should decide whether you like Opaleye's Query DSL, or not (ref: Opaleye Query Tutorial). See if the tradeoffs discussed in Opalaye's Query DSL - Pros & Cons are acceptable for your use-case.
• Next, you will need to decide what kind of data-structures you'd like to use for the DB <=> Haskell bridge, and how type-safe you'd like this bridge to be. This is discussed in Pick your data-structures
• For sizeable projects that are evolving over time (which ones aren't?), you'll need to contend with the Opaleye boilerplate required to configure various Table writeFields viewFields values. This is discussed in Auto-generate the boilerplate
• Since Opaleye does not deal with managing database connections, connection pooling, or logging, it is highly recommended that you write your own wrappers on top of few core Opaleye functions. Finally, you should probably Formalize all of this in a custom monad

Opalaye's Query DSL - Pros & Cons

The two biggest advantages of using Opaleye's DSL, instead of writing raw SELECT queries by hand, are:

• Composability and correctness
• Type-safety

The biggest disadvantages of Opaleye's DSL are:

• Very large queries, possibly resulting in a performance penalty: This, in my opinion, is a direct consequence of how Opaleye achieves composability and correctness. Every time you compose two queries (for example, using a join), or add a restriction to a query (i.e. aWHERE clause), or even add a LIMIT/OFFSET clause, the query is wrapped into another nested level of SELECT. This is done to ensure no part of the sub-query conflicts with anything else that is added later to the query. This results in very large queries and very nested queries that potentially cause performance issues as are currently being discussed on the following Github issues:
  • Serious performance degradation due to SQL query nesting
  • Query building performance discussion
• Using ad-hoc joins will force you to make one of one of the following choices. If you want to understand them in detail please read Opaleye Query Tutorial
  • abandon your core data-structures, and revert to tuples
  • have complicated core data-structures that depend on TypeFamilies and other type-level magic
• Another minor disadvantage could be the dependency on using the Arrows language extension. Some people still consider it to be highly experimental.

Pick your data-structures

First, you will need to configure a someTable :: Table writeFields viewFields value for each table that you wish to interface with, via Opaleye. This will force you to pick concrete types for writeFields and viewFields.

In the example given at Teaching Opaleye your table structure we used tuples for writeFields and viewFields (and both types were same), to get the job done. While simple to understand, this approach doesn't scale to an application that has many tables (with each table having dozens of columns). In all probability you will want use record-types instead of tuples.

Now, this gives you the following choices:

1. Use same record-types for writeFields and viewFields as discussed in Using Opaleye with simple Haskell records
2. Use polymorphic records as discussed in Using Opaleye with polymorphic Haskell records
3. Go one step further down the rabit-hole of type-level magic, and use polymorphic records along with type-families, as discussed in Supercharged polymorphic records with type-families

Second, you will need to decide whether you'd like each table's primary key to be a different type or not - in all probability it will be some type of newtype over Int (or Int64). This will have a ripple effect on using the same newytpes for foreign-keys that reference these primary-keys. Even here, you have two choices, and you'll need to pick one:

• Use a simple newtype for each primary-key, as discussed in Simple newtypes over Int (or Int64 ) for your primary keys
• Use a newtype with a phantom type-variable, as discussed in Phantom types for reducing newtype boilerplate for your primary keys

Third, you will need to decide whether you'd like to use primitive Haskell types for "special" columns, or you'd like to define custom types for them. Few examples of such "special columns":

• Enumerations: fulfilment_status, which can only contain pending, processing, shipped, delivered. On the Haskell side, you can use String for it, or something like:

  data FulfilmentStatus = FSPending | FSProcessing | FSShipped | FSDelivered
                          deriving (Eq, Show, Enum, Generic)

• JSON/JSONB columns: By default, json and jsonb columns are converted to values of type Aeson.Value on the Haskell side. You might want to use a custom data-types if your JSON structures are fixed.

• HSTORE columns: Today, with top-notch support for jsonb columns in Postgres, hstore columns aren't very popular nowadays. However, at Vacation Labs, we started off with with v9 which had very poor support for json, and IIRC, didn't even have jsonb. Therefore, we have quite a few hstore columns in our schema. If you do to, you will need to decide whether you'd like to map them to [(Text, Text)], Map Text Text, HashMap Text Text, or use a custom-type depending upon what you're storing in them ((similar to jsonb).

• Custom Postgres types: If you've created custom Postgres types then Opaleye won't even know the standard/native Haskell type to use for them. You'll need to teach it how to map your custom Postgres type to a custom Haskell type.

All of these scenarios are discussed in Core mechanism for mapping custom Haskell types to PG types

Auto-generate the boilerplate

Once you've decided what kinds of types/data-structures you want to use on the Haskell side, you'll be staring at the daunting task of writing (and maintaining) tonnes of boilerplate code:

• Creating someTable :: Table writeFields viewFields - one for each table in your DB schema, and keeping them up-to-date every time your schema changes.
• Write, and maintain, FromFields, ToFields and Default FromFields typeclass instances for each custom Haskell type you wish to use with Opaleye.

It is highly recommended to save yourself this pain and auto-generate this boilerplate using one of the following methods:

• Use opaleye-steroids (TODO: Write a note about when this library will be released!)
• Use opaleye-gen which is similar to opaleye-steroid, but smaller and simpler (you can use this as a building block to write your own scripts)
• If none of the above fit your requirements, consider writing your own script, or TemplateHaskell functions, to auto-generate this boilerplate.

Formalize all of this in a custom monad

It is strongyly recommend that you formalize DB access in your application by writing a custom monad, instead of coming up with different ad-hoc solutions to the following at different places in your codebase:

• Managing your database connections
• Dealing with database transactions
• Logging related to DB/Opaleye

There are multiple approaches to building your custom monad and they strongly correlated to how you want to manage your DB connections:

• Custom monad with one DB connection per thread
• Custom monad with one DB connection per logical DB operation
• Custom monad using opaleye-trans package

-- We are using a connection pool
transferMoney :: Pool Connection -> AccountId -> AccountId -> Int -> IO ()
transferMoney dbPool fromAccount toAccount amount = 
  Data.Pool.withResource dbPool $ \conn -> do
    withTransaction conn $ do
      -- be careful to use the SAME CONNECTION as you've used in withTransaction
      updateAccountBalance conn fromAccount (negate amount)
      updateAccountBalance conn toAccount amount

updateAccountBalance :: Connection -> AccountId -> Int -> IO ()
updateAccountBalance conn acId = void $ runUpdate_ conn Update
  { uTable = ..         -- whatever
  , uUpdateWith = ..    -- whatever
  , uWhere = ..         -- whatever
  , uReturning = rCount
  }

-- picking a different name so that it doesn't clash with runSelect and 
-- forces us to use qualified imports
dbSelect :: (Default FromFields fields haskells)
         => FastLogger
         -- ^ From the fast-logger library. You may be using a different
         -- logging library and may need to change this.
         -> Connection
         -> Select fields
         -> IO [haskells]
dbSelect logger conn sel = do
  startTime <- getCurrentTime
  let action = logSql logger startTime (runSelect conn sel)
      handler = logSqlError logger startTime conn sel
  catch action handler
  where
    logSql = do
      result <- runSelect conn sel
      endTime <- getCurrentTime
      logger $ toLogStr $ "SELECT " <>