BSD-3-Clause licensed and maintained by Purely Agile
This version can be pinned in stack with:opaleye-,6199

Module documentation for

Brief introduction to Opaleye Hackage version 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 is absolutely fantastic. It has been solid in production for years!” – Matt Wraith

“Opaleye just works, and it’s my personal recommendation … I like it a lot” – William Yao

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.

Discuss and ask questions about Opaleye

You are welcome to use the Opaleye GitHub issue tracking page for discussion of or questions about Opaleye even if they don’t relate to a bug or issue.


You are welcome to make PRs to Opaleye. If you would like to discuss the design of your PR before you start work on it feel free to do so by filing a new issue.

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

The only person authorised to merge to master or upload this package to Hackage is Tom Ellis.

However, to ensure continuity of service to Opaleye users there are backup maintainers.

  • If Tom Ellis is unavailable or unresponsive to maintenance requests for three months then full ownership of the project, including the GitHub repository, Hackage upload rights, and the right to amend this backup maintainers policy, passes to Oliver Charles ([email protected]).

  • If Tom Ellis is unavailable or unresponsive to maintenance requests for four months, and this policy has not been changed to the contrary, then full ownership of the project, including the GitHub repository, Hackage upload rights, and the right to amend this backup maintainers policy passes to Shane O’Brien (@duairc).

  • If Tom Ellis is unavailable or unresponsive to maintenance requests for six months, and this policy has not been changed to the contrary, then full ownership of the project, including the GitHub repository, Hackage upload rights, and the right to amend this backup maintainers policy passes to Joe Hermaszewski (@expipiplus1).


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.


Fix ISO 8601 date fomatting. Thanks to Michal @kozak.

No user-visible changes

  • Added matchMaybe

  • Added SqlVarcharN and supporting functions

  • Added enumMapperWithSchema. Thanks to Steve Mao.

  • Added SqlInterval. Thanks to Bas van Dijk.

  • Added distinctOnCorrect and distinctOnByCorrect which will replace distinctOn and distinctOnBy in a future version.

  • Added DefaultFromField SqlJson(b) instances for Strict/Lazy Text/ByteString. Thanks to Nathan Jaremko.

  • Added jsonAgg, jsonBuildObject and jsonBuildObjectField. Thanks to Nathan Jaremko.

  • Added now function. Thanks to Nathan Jaremko.

  • Added Opaleye.Exists.exists. Thanks to @duairc.

  • Added Opaleye.Experimental.Enum

  • Added Opaleye.Operators.array_position and Opaleye.Operators.sqlElem. Thanks to Ashesh Ambasta.

  • Added Opaleye.Experimental.Enum for an easy way to deal with Postgres ENUM types.

  • Added Opaleye.Manipulation.rReturningI which has better type inference.

  • Added Opaleye.Operators.where_ for easier restriction in monadic style.

  • Added Opaleye.Operators.sqlLength and Opaleye.Operators.dateOfTimestamp.

  • Many renamings have taken place to help make Opaleye easier to understand. The old versions have been deprecated.

  • All previously deprecated functions have been removed.

Old New
Query Select
QueryArr SelectArr
PGType SqlType
PGClass SqlClass
Constant ToFields
QueryRunner FromFields
QueryRunnerColumn FromField
QueryRunnerColumnDefault DefaultFromField
TableProperties TableFields
optional optionalTableField
required requiredTableField
readOnly readOnlyTableField


  • Added Opaleye.RunSelect.runSelectI and Opaleye.ToFields.toFieldsI which have better inferability.

  • Preliminary FOR UPDATE support in Opaleye.Internal.Locking.

  • Added fromFieldArray for making FromFields for arrays.


  • Thanks to Shane (@duairc) and Ollie Charles (@ocharles) for writing most of the lateral- and MaybeFields-related code in this release.

  • Add a Monad instance for Select (and SelectArr i).

  • Add Opaleye.Lateral, to support LATERAL subqueries.

  • Add Opaleye.Join.optionalRestrict and Opaleye.Join.optional, as more convenient and composable ways of doing left/right joins.

  • Add Opaleye.MaybeFields

  • Add optionalTableField, readOnlyTableField, requiredTableField, to replace optional, readOnly and required in a later version.

  • Add valuesSafe, a version of values. values of an empty list generates incorrect queries when mixed with @OUTER@/@LEFT@/@RIGHT JOIN@s. valuesSafe will replace it in version 0.7

  • Add Opaleye.Adaptors as the forward-compatible place to import Unpackspec and unpackspecField from, as well as other adaptors.

  • Unicode characters are escaped properly in sqlString/toFields

  • Add inSelect, to replace inQuery in a future version.

  • Add unsafeCoerceField, to replace unsafeCoerceColumn in a future version.

  • Generalise label to type label :: String -> S.SelectArr a b -> S.SelectArr a b

  • Fix invalid queries bug in union, unionAll, except and exceptAll where one side was empty.


  • No user-visible changes


  • Fixed exponential 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