Brief introduction to Opaleye Hackage version Linux build status

Opaleye is a Haskell library that provides an SQL-generating embedded domain specific language for targeting Postgres. You need Opaleye if you want to use Haskell to write typesafe and composable code to query a Postgres database.

“Opaleye really is great. You’ve managed to bring what is so wonderful about relational databases and give it type safety and composition (i.e. what is wonderful about Haskell)” – Daniel Patterson, Position Development

“We use it for most of our DB code. It’s very flexible and almost always as performant as manually written queries” – Adam Bergmark,

Opaleye allows you to define your database tables and write queries against them in Haskell code, and aims to be typesafe in the sense that if your code compiles then the generated SQL query will not fail at runtime. A wide range of SQL functionality is supported including inner and outer joins, restriction, aggregation, distinct, sorting and limiting, unions and differences. Facilities to insert to, update and delete from tables are also provided. Code written using Opaleye is composable at a very fine level of granularity, promoting code reuse and high levels of abstraction.

Getting Opaleye


Please get started with Opaleye by referring to these two tutorials


Contact the author

The main author of Opaleye is Tom Ellis. He can be contacted via email.

File bugs

Please file bugs on the Opaleye GitHub issue tracking page.

Mailing list

Please join the opaleye-users mailing list.

Internal modules

Opaleye exports a number of modules named Opaleye.Internal..... They are provided in case of urgent need for access to the internals, but they are not intended to be used by API consumers and if you find yourself repeatedly accessing them this is a sign that either you or Opaleye are doing something wrong. In such a case please file a bug.

The interface of Internal modules does not follow the PVP and may break between minor releases, so be careful.

Running tests

You must have running PostgreSQL server to run tests. Specify the database by setting the POSTGRES_CONNSTRING environment variable:

POSTGRES_CONNSTRING="user=tom dbname=opaleye_test" stack test

Commercial support

Commercial support for Opaleye is provided by Purely Agile.

Backup maintainers

In the event of the main developer becoming unreachable, please contact the following who are authorised to make bugfixes and dependency version bumps:

  • Adam Bergmark
  • Erik Hesselink
  • Oliver Charles


The Opaleye Project was founded by Tom Ellis, inspired by theoretical work on databases by David Spivak. Much of the implementation was based on ideas and code from the HaskellDB project by Daan Leijen, Conny Andersson, Martin Andersson, Mary Bergman, Victor Blomqvist, Bjorn Bringert, Anders Hockersten, Torbjorn Martin, Jeremy Shaw and Justin Bailey.

The following individuals and organisations made helpful contributions which were important in helping to get the project off the ground.

  • Silk (Erik Hesselink, Adam Bergmark)
  • Karamaan (Christopher Lewis)
  • Fynder (Renzo Carbonara, Oliver Charles)
  • Daniel Patterson
  • Jakub Ryška
  • Travis Staton

Joseph Abrahamson, Alfredo Di Napoli and Mietek Bak performed useful reviews of early versions which helped improve the codebase. Since then there have been helpful contributions from many others. Thanks to them all for their help.



  • Fixed quadratic slowdown in removeEmpty.

  • Fixed read compatibility with time-1.9 in test suite.


  • Many changes to the documentation that use the new names. See entry for version 0.6.7000.0.

  • Added fromPGSFromField to replace fieldQueryRunnerColumn.

  • Added fromPGSFieldParser to replace fieldParserQueryRunnerColumn.

  • Added defaultFromField to replace queryRunnerColumnDefault.

  • Added tableField to replace tableColumn.

  • Added unsafeFromField to replace queryRunnerColumn.

  • Added toFieldsExplicit to replace constantExplicit.

  • Added TableRecordField to replace TableField in Opaleye.TypeFamilies. The latter may be used to replace TableColumn in the future.

  • Added array functions arrayAppend, arrayRemove, arrayRemoveNulls.


  • Bumped some depedencies so there is an install plan on GHC 8.6


  • Add tableField as a future replacement for tableColumn

  • Export Opaleye.Field and Opaleye.RunSelect from Opaleye

  • Use new nomenclature in tutorials


This is a breaking release that doesn’t follow the PVP but because it’s essentially a pre-release for version 0.7 I’m just going to blacklist the broken versions on Hackage and forget about it.

  • Swapped N and NN because they were the wrong way round.


  • Fix bug with infinity in range bounds

  • Fix incompatibility with GHC 8.4

  • Add range accessors, upperBound and lowerBound

  • Add distinctOn and distinctOnBy


This is a pre-release of version GHC >= 8.0 is required. It contains the following new important features

  • A new API for manipulation, including ON CONFLICT DO NOTHING support for UPDATE

  • Initial support for product types written in “Higher kinded data” style (but deriving of related functionality using TH or Generics is not yet provided).

  • Type inference for outer joins

  • Many renamings. In particular, Column will become Field in You should be able to almost completely port your code to the names whilst remaining compatible with 0.6.7000.0.


  • Added Opaleye.RunSelect

  • Added Opaleye.Field

  • queryTable is renamed selectTable

  • Query/QueryArr are renamed Select/SelectArr

  • QueryRunner is renamed FromFields

  • QueryRunnerColumn is renamed FromField

  • Constant is renamed ToFields

  • constant is renamed toFields

  • Added Opaleye.SqlTypes and sql/Sql... names instead of pg/PG... names

  • Added runInsert_, runUpdate_, runDelete_ and supporting functionality

  • Add PGNumeric type

  • Added leftJoinA

  • Added liesWithin

  • Added ZonedTime to PGTimestamptz mappings

  • ArrowChoice instance for QueryArr

  • Added runUpdateEasy

  • Deprecated

    • Show instance of Column a
    • Manipulation.arrange...
    • showPGType
    • literalColumn
    • unsafePgFormatTime
    • prepareQuery
    • formatAndShowSQL
  • Removed

    • unsafeCoerce
  • Added TableColumn and tableColumn which selects optional or required based on write type.

  • Added TableColumns as synonym for TableProperties. TableProperties will be deprecated in version 0.7.

  • Added table as synonym for Table. Table will be deprecated in version 0.7.

  • Added tableWithSchema as synonym for TableWithSchema. Table will be deprecated in version 0.7.

  • Replaced ColumnMaker with Unpackspec, which is identical to it.

  • Added Profunctor instance for Table

  • Added restrictExists and restrictNotExists as synonyms for exists and notExists. The latter will be deprecated in version 0.7.

  • Added cursor interface (Cursor and friends)

  • distinctAggregator, joinNullable, exists, notExists, index, timestamptzAtTimeZone

  • Added support for range types

  • Corrected fixity for .&&

  • Improved documentation

  • Added Opaleye.FunctionalJoin
  • Fixed handling of BinExpr OpIn _ (ListExpr _) in defaultSqlExpr.
  • in_ now actually uses the SQL IN operator.
  • Added support for ILIKE

  • Added
    • support for JSON operators
    • Many improvements to the Haddocks
    • RIGHT and FULL OUTER joins

  • Added
    • (.===), aggregateOrdered, countStar, countRows, quot_, rem_, ’charLength`
    • intersection and except query binary operators
    • Constant instances for Maybe and lists
    • runInsertManyReturning
    • runQueryFold

  • Added .=== and ./== for comparison of product types
  • Added keepWhen as an alternative to restrict
  • Added constant conversion to and from Aeson
  • Added pgValueJSON and pgValueJSONB

  • Added Opaleye.Constant for lifting constant values
  • Support microseconds in pgLocalTime, pgTimeOfDay and pgUTCTime
  • Added unsafeCompositeField to help with defining composite types
  • Order is an instance of Semigroup

Thanks to Adam Bergmark and Matt Wraith for helping with these changes.

  • Added runUpdateReturning
  • Ordering operators and max and min aggregators are now restricted to a typeclass
  • Added stringAgg and arrayAgg aggregations.
  • Added PGOrd typeclass for typesafe ordering operations.
  • Support sorting NULLs first or last with ascNullsFirst and descNullsFirst
  • Added JSON types
  • Added runInsertMany

Thanks to Travis Staton, Jakub Ryška and Christopher Lewis for helping with these changes.

  • Use time >= 1.4 and time-locale-compat

  • Bump time to >= 1.5


  • SQL code generator escapes column names, so table column names can be the same as SQL keywords.
  • Add like operator
  • Add the types PGCitext, PGArray, PGBytea


  • Replace Default QueryRunner with a new class DefaultQueryRunnerColumn, migrate with s/Default QueryRunner/DefaultQueryRunnerColumn and s/def/queryRunnerColumnDefault/
  • Remove ShowConstant, use the monomorphic functions defined in the new module Opaleye.PGTypes instead. You will need to replace Column Bool with Column PGBool etc. in query signatures
  • Re-export more modules from Opaleye
  • Add boolAnd, boolOr, max, and min aggregators
  • Add lower and upper
  • Add operator fixities
  • Add maybeToNullable
  • Add column instances for Bool, UUID, Text, and UTCTime
  • Expose fieldQueryRunnerColumn from Opaleye.RunQuery
  • Add unsafeCast
  • Re-export Unpackspec from Opaleye.Manipulation