An SQL-generating DSL targeting PostgreSQL
|Version on this page:||0.9.3.2|
|LTS Haskell 20.15:||0.9.6.1|
|Stackage Nightly 2023-03-26:||0.9.6.1|
|Latest on Hackage:||0.9.6.1|
Module documentation for 0.9.3.2
Brief introduction to Opaleye
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, Silk.co
“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.
- Github: https://github.com/tomjaguarpaw/haskell-opaleye
- Hackage: https://hackage.haskell.org/package/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.
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.
Opaleye exports a number of modules named
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.
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 for Opaleye is provided by Purely Agile.
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 (firstname.lastname@example.org).
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.
- No externally visible changes
- No externally visible changes
withRecursive(thanks to Erik Hesselink and Shane O’Brien).
DefaultFromFieldinstances for postgresql-simple’s
Aeson(thanks to Bas Van Dijk).
label'as a future replacement for
Fixed a bug in
The internal implementation of
The switch from
Field is complete. This is a small yet
pervasive change. To update your code please change all usages of
Column as follows:
Columnof a non-nullable type: to
Columnof a nullable type: to
Columnof a nullability-polymorphic type: to
Column (Nullable SqlInt4)->
Field_ n a
This is the only change that has been made in this version, in order to ease user transition.
- Cosmetic and re-export changes only.
- Support GHC 9.2
Removed the following deprecated functions, types and modules
Replaced the following old internal names
FromField(type alias and constructor)
FromFields(type alias and constructor)
Opaleye.Join.optionalexported from top-level
distinctOnBynow return a single row if zero columns are chosen to be distinct.
Fix ISO 8601 date fomatting. Thanks to Michal @kozak.
No user-visible changes
SqlVarcharNand supporting functions
enumMapperWithSchema. Thanks to Steve Mao.
SqlInterval. Thanks to Bas van Dijk.
distinctOnByCorrectwhich will replace
distinctOnByin a future version.
- Added DefaultFromField SqlJson(b) instances for Strict/Lazy Text/ByteString. Thanks to Nathan Jaremko.
jsonBuildObjectField. Thanks to Nathan Jaremko.
nowfunction. Thanks to Nathan Jaremko.
Opaleye.Exists.exists. Thanks to @duairc.
Opaleye.Operators.sqlElem. Thanks to Ashesh Ambasta.
Opaleye.Experimental.Enumfor an easy way to deal with Postgres
Opaleye.Manipulation.rReturningIwhich has better type inference.
Opaleye.Operators.where_for easier restriction in monadic style.
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.
Opaleye.ToFields.toFieldsIwhich have better inferability.
FOR UPDATEsupport in
FromFields for arrays.
Thanks to Shane (@duairc) and Ollie Charles (@ocharles) for writing most of the
MaybeFields-related code in this release.
Opaleye.Lateral, to support LATERAL subqueries.
Opaleye.Join.optional, as more convenient and composable ways of doing left/right joins.
requiredTableField, to replace
requiredin a later version.
valuesSafe, a version of
valuesof an empty list generates incorrect queries when mixed with @OUTER@/@LEFT@/@RIGHT JOIN@s.
valuesSafewill replace it in version 0.7
Opaleye.Adaptorsas the forward-compatible place to import
unpackspecFieldfrom, as well as other adaptors.
Unicode characters are escaped properly in
inSelect, to replace
inQueryin a future version.
unsafeCoerceField, to replace
unsafeCoerceColumnin a future version.
Generalise label to type
label :: String -> S.SelectArr a b -> S.SelectArr a b
Fix invalid queries bug in
exceptAllwhere one side was empty.
- No user-visible changes
Fixed exponential slowdown in
readcompatibility 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.
Opaleye.TypeFamilies. The latter may be used to replace
TableColumnin the future.
Added array functions
- Bumped some depedencies so there is an install plan on GHC 8.6
tableFieldas a future replacement for
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.
NNbecause they were the wrong way round.
Fix bug with infinity in range bounds
Fix incompatibility with GHC 8.4
Add range accessors,
This is a pre-release of version 0.7.0.0. GHC >= 8.0 is required. It contains the following new important features
A new API for manipulation, including
ON CONFLICT DO NOTHINGsupport for
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,
Fieldin 0.7.0.0. You should be able to almost completely port your code to the 0.7.0.0 names whilst remaining compatible with 0.6.7000.0.
Sql...names instead of
runDelete_and supporting functionality
requiredbased on write type.
TableColumnsas synonym for
TablePropertieswill be deprecated in version 0.7.
tableas synonym for
Tablewill be deprecated in version 0.7.
tableWithSchemaas synonym for
Tablewill be deprecated in version 0.7.
Unpackspec, which is identical to it.
restrictNotExistsas synonyms for
notExists. The latter will be deprecated in version 0.7.
- Added cursor interface (
- Added support for range types
- Corrected fixity for .&&
- Improved documentation
- Fixed handling of
BinExpr OpIn _ (ListExpr _)in
in_now actually uses the SQL
- Added support for
- support for JSON operators
- Many improvements to the Haddocks
- RIGHT and FULL OUTER joins
- intersection and except query binary operators
./==for comparison of product types
keepWhenas an alternative to
constantconversion to and from Aeson
Opaleye.Constantfor lifting constant values
- Support microseconds in
unsafeCompositeFieldto help with defining composite types
Orderis an instance of
Thanks to Adam Bergmark and Matt Wraith for helping with these changes.
- Ordering operators and
minaggregators are now restricted to a typeclass
PGOrdtypeclass for typesafe ordering operations.
- Support sorting NULLs first or last with
- Added JSON types
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 the types
Default QueryRunnerwith a new class
DefaultQueryRunnerColumn, migrate with
ShowConstant, use the monomorphic functions defined in the new module
Opaleye.PGTypesinstead. You will need to replace
Column PGBooletc. in query signatures
- Re-export more modules from
- Add operator fixities
- Add column instances for
- Expose fieldQueryRunnerColumn from Opaleye.RunQuery