opaleye
An SQL-generating DSL targeting PostgreSQL
https://github.com/tomjaguarpaw/haskell-opaleye
Version on this page: | 0.10.3.1@rev:1 |
LTS Haskell 22.39: | 0.10.3.1@rev:1 |
Stackage Nightly 2024-10-31: | 0.10.4.0 |
Latest on Hackage: | 0.10.4.0 |
opaleye-0.10.3.1@sha256:63757519ae53eb62ef302c0a5e502d374a0c9590026740093f7a038596bf50a4,6201
Module documentation for 0.10.3.1
- Opaleye
- Opaleye.Adaptors
- Opaleye.Aggregate
- Opaleye.Binary
- Opaleye.Column
- Opaleye.Distinct
- Opaleye.Exists
- Opaleye.Experimental
- Opaleye.Field
- Opaleye.FunctionalJoin
- Opaleye.Internal
- Opaleye.Internal.Aggregate
- Opaleye.Internal.Binary
- Opaleye.Internal.Column
- Opaleye.Internal.Constant
- Opaleye.Internal.Distinct
- Opaleye.Internal.HaskellDB
- Opaleye.Internal.Helpers
- Opaleye.Internal.Inferrable
- Opaleye.Internal.JSONBuildObjectFields
- Opaleye.Internal.Join
- Opaleye.Internal.Lateral
- Opaleye.Internal.Locking
- Opaleye.Internal.Manipulation
- Opaleye.Internal.Map
- Opaleye.Internal.MaybeFields
- Opaleye.Internal.Operators
- Opaleye.Internal.Optimize
- Opaleye.Internal.Order
- Opaleye.Internal.PGTypes
- Opaleye.Internal.PGTypesExternal
- Opaleye.Internal.PackMap
- Opaleye.Internal.PrimQuery
- Opaleye.Internal.Print
- Opaleye.Internal.QueryArr
- Opaleye.Internal.Rebind
- Opaleye.Internal.RunQuery
- Opaleye.Internal.RunQueryExternal
- Opaleye.Internal.Sql
- Opaleye.Internal.Table
- Opaleye.Internal.Tag
- Opaleye.Internal.TypeFamilies
- Opaleye.Internal.Unpackspec
- Opaleye.Internal.Values
- Opaleye.Internal.Window
- Opaleye.Join
- Opaleye.Label
- Opaleye.Lateral
- Opaleye.Manipulation
- Opaleye.MaybeFields
- Opaleye.Operators
- Opaleye.Order
- Opaleye.RunSelect
- Opaleye.Select
- Opaleye.Sql
- Opaleye.SqlTypes
- Opaleye.Table
- Opaleye.ToFields
- Opaleye.TypeFamilies
- Opaleye.Values
- Opaleye.Window
- Opaleye.With
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.
Getting Opaleye
- Github: https://github.com/tomjaguarpaw/haskell-opaleye
- Hackage: https://hackage.haskell.org/package/opaleye
Tutorials
Please get started with Opaleye by referring to these two tutorials
Advanced
Contact
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.
PRs
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 Ellie Hermaszewska (@expipiplus1).
Contributors
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.
Changes
0.10.3.1
- Improve Haddock
0.10.3.0
- Added
enumShowSqlType
andsqlTypeWithSchema
(thanks to @stevemao)
0.10.2.3
- Documentation improvements
0.10.2.2
- Documentation improvements
0.10.2.1
- Fixed bug that generated broken queries when using ordered
aggregateOrdered
withdistinctAggregator
, and when using set aggregation.
0.10.2.0
-
Added
isJustAnd
-
Add
withMaterialized
(thanks to Shane O’Brien) -
Added ’dateTruncTimestamp
and
dateTruncTimestamptz` (thanks to @njaremko)
0.10.1.1
- Fix bugs in
WITH
. See https://github.com/tomjaguarpaw/haskell-opaleye/pull/572 (thanks to Shane O’Brien)
0.10.1.0
- Added
withRecursiveDistinct
(thanks to Shane O’Brien)
0.10.0.0
-
Changed
relationValuedExpr
to work in more cases. (This is a breaking change to an internal function.) -
Removed the following, which were all previously deprecated:
valuesSafe
,valuesSafeExplicit
,valuesUnsafe
,valuesUnsafeExplicit
,ValuesspecSafe
,fieldQueryRunnerColumn
,fieldParserQueryRunnerColumn
,queryRunner
,joinF
,leftJoinF
,rightJoinF
,fromFieldToFieldsEnum
,keepWhen
See their documentation in the 0.9 series to learn about their replacements.
0.9.7.0
- Added
filterWhere
(thanks to Shane O’Brien)
0.9.6.2
- No externally visible changes
0.9.6.1
- No externally visible changes
0.9.6.0
- Add
Opaleye.Window
to support window functions. Thanks to Shane O’Brien.
0.9.5.1
- Actually expose
arrayAgg_
0.9.5.0
- Add
arrayAgg_
for aggregating nullable fields
0.9.4.1
- Actually expose
ascNullsLast
anddescNullsFirst
.
0.9.4.0
-
Added
instance DefaultFromField (T.SqlArray_ Nullable a) [Maybe b]
-
Changed
ascNullsFirst
anddescNullsLast
to work with nullable fields. This rectifies an oversight from theColumn
toField
change. This may technically be a PVP violation but I think the risk of breakage is very small. If you experience breakage please report it on the issue tracker. -
Added
ascNullsLast
anddescNullsFirst
. -
Thanks to @abigailalice for pointing out the oversights in the
Column
toField
change.
0.9.3.3
-
No externally visible changes
-
Substantial internal changes to
Opaleye.Values
0.9.3.2
- No externally visible changes
0.9.3.1
- No externally visible changes
0.9.3.0
-
Add
with
andwithRecursive
(thanks to Erik Hesselink and Shane O’Brien). -
Add
Default ToFields
andDefaultFromField
instances for postgresql-simple’sAeson
(thanks to Bas Van Dijk).
0.9.2.0
- Added
nullableToMaybeFields
andmaybeFieldsToNullable
0.9.1.0
-
Added
distinctOnExplicit
anddistinctOnByExplicit
-
Added
label'
as a future replacement forlabel
-
Exported
SqlFractional
fromOpaleye.SqlTypes
-
Fixed a bug in
forUpdate
-
The internal implementation of
QueryArr
has changed.
0.9.0.0
The switch from Column
to Field
is complete. This is a small yet
pervasive change. To update your code please change all usages of
Column
as follows:
Column
of a non-nullable type: toField
Column
of a nullable type: toFieldNullable
Column
of a nullability-polymorphic type: toField_ n
For example
Column SqlText
->Field SqlText
Column (Nullable SqlInt4)
->FieldNullable SqlInt4
Column a
->Field_ n a
This is the only change that has been made in this version, in order to ease user transition.
0.8.1.0
- Cosmetic and re-export changes only.
0.8.0.1
- Support GHC 9.2
0.8.0.0
-
Removed the following deprecated functions, types and modules
Opaleye.Query
,Query
,QueryArr
,queryRunnerColumnDefault
Opaleye.RunQuery
,runQuery
,runQueryFold
,queryRunnerColumn
Opaleye.Constant
,constant
- The
Table
andTableWithSchema
constructors View
,Writer
,required
,optional
,readOnly
,tableColumn
,queryTable
Nulled
,leftJoinInferrable
,rightJoinInferrable
,fullJoinInferrable
unpackspecColumn
TableField
runInsertManyReturningOnConflictDoNothing
,runInsertManyReturning
,runUpdateEasy
,runUpdateReturning
,runDelete
charLength
,exists
,notExists
,inQuery
PGIsJson
,PGOrd
,PG<typename>
showSqlForPostgres
,showSqlForPostgresUnopt
-
Replaced the following old internal names
QueryRunnerColumnDefault
->DefaultFromField
QueryRunnerColumn
->FromField
(type alias and constructor)QueryRunner
->FromFields
(type alias and constructor)
-
Opaleye.Join.optional
exported from top-level -
Bug fix:
distinctOn
anddistinctOnBy
now return a single row if zero columns are chosen to be distinct. -
Add
runInsert
/Update
/Delete
without underscore
0.7.6.2
Fix ISO 8601 date formatting. Thanks to Michal @kozak.
0.7.6.1
No user-visible changes
0.7.6.0
-
Added
matchMaybe
-
Added
SqlVarcharN
and supporting functions
0.7.5.0
-
Added
enumMapperWithSchema
. Thanks to Steve Mao. -
Added
SqlInterval
. Thanks to Bas van Dijk.
0.7.4.0
- Added
distinctOnCorrect
anddistinctOnByCorrect
which will replacedistinctOn
anddistinctOnBy
in a future version.
0.7.3.0
- Added DefaultFromField SqlJson(b) instances for Strict/Lazy Text/ByteString. Thanks to Nathan Jaremko.
0.7.2.0
-
Added
jsonAgg
,jsonBuildObject
andjsonBuildObjectField
. 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
andOpaleye.Operators.sqlElem
. Thanks to Ashesh Ambasta.
0.7.1.0
-
Added
Opaleye.Experimental.Enum
for an easy way to deal with PostgresENUM
types. -
Added
Opaleye.Manipulation.rReturningI
which has better type inference. -
Added
Opaleye.Operators.where_
for easier restriction in monadic style. -
Added
Opaleye.Operators.sqlLength
andOpaleye.Operators.dateOfTimestamp
.
0.7.0.0
-
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 |
0.6.7006.0
-
Added
Opaleye.RunSelect.runSelectI
andOpaleye.ToFields.toFieldsI
which have better inferability. -
Preliminary
FOR UPDATE
support inOpaleye.Internal.Locking
. -
Added
fromFieldArray
for makingFromField
s for arrays.
0.6.7005.0
-
Thanks to Shane (@duairc) and Ollie Charles (@ocharles) for writing most of the
lateral
- andMaybeFields
-related code in this release. -
Add a
Monad
instance forSelect
(andSelectArr i
). -
Add
Opaleye.Lateral
, to support LATERAL subqueries. -
Add
Opaleye.Join.optionalRestrict
andOpaleye.Join.optional
, as more convenient and composable ways of doing left/right joins. -
Add
Opaleye.MaybeFields
-
Add
optionalTableField
,readOnlyTableField
,requiredTableField
, to replaceoptional
,readOnly
andrequired
in a later version. -
Add
valuesSafe
, a version ofvalues
.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 importUnpackspec
andunpackspecField
from, as well as other adaptors. -
Unicode characters are escaped properly in
sqlString
/toFields
-
Add
inSelect
, to replaceinQuery
in a future version. -
Add
unsafeCoerceField
, to replaceunsafeCoerceColumn
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
andexceptAll
where one side was empty.
0.6.7004.2
- No user-visible changes
0.6.7004.1
-
Fixed exponential slowdown in
removeEmpty
. -
Fixed
read
compatibility with time-1.9 in test suite.
0.6.7004.0
-
Many changes to the documentation that use the new names. See entry for version 0.6.7000.0.
-
Added
fromPGSFromField
to replacefieldQueryRunnerColumn
. -
Added
fromPGSFieldParser
to replacefieldParserQueryRunnerColumn
. -
Added
defaultFromField
to replacequeryRunnerColumnDefault
. -
Added
tableField
to replacetableColumn
. -
Added
unsafeFromField
to replacequeryRunnerColumn
. -
Added
toFieldsExplicit
to replaceconstantExplicit
. -
Added
TableRecordField
to replaceTableField
inOpaleye.TypeFamilies
. The latter may be used to replaceTableColumn
in the future. -
Added array functions
arrayAppend
,arrayRemove
,arrayRemoveNulls
.
0.6.7003.1
- Bumped some dependencies so there is an install plan on GHC 8.6
0.6.7003.0
-
Add
tableField
as a future replacement fortableColumn
-
Export
Opaleye.Field
andOpaleye.RunSelect
fromOpaleye
-
Use new nomenclature in tutorials
0.6.7002.0
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
andNN
because they were the wrong way round.
0.6.7001.0
-
Fix bug with infinity in range bounds
-
Fix incompatibility with GHC 8.4
-
Add range accessors,
upperBound
andlowerBound
-
Add
distinctOn
anddistinctOnBy
0.6.7000.0
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 NOTHING
support forUPDATE
-
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 becomeField
in 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.
Details
-
Added
Opaleye.RunSelect
-
Added
Opaleye.Field
-
queryTable
is renamedselectTable
-
Query
/QueryArr
are renamedSelect
/SelectArr
-
QueryRunner
is renamedFromFields
-
QueryRunnerColumn
is renamedFromField
-
Constant
is renamedToFields
-
constant
is renamedtoFields
-
Added
Opaleye.SqlTypes
andsql
/Sql...
names instead ofpg
/PG...
names -
Added
runInsert_
,runUpdate_
,runDelete_
and supporting functionality -
Add
PGNumeric
type -
Added
leftJoinA
-
Added
liesWithin
0.6.1.0
-
Added
ZonedTime
toPGTimestamptz
mappings -
ArrowChoice
instance forQueryArr
0.6.0.0
-
Added
runUpdateEasy
-
Deprecated
Show
instance ofColumn a
Manipulation.arrange...
showPGType
literalColumn
unsafePgFormatTime
prepareQuery
formatAndShowSQL
-
Removed
unsafeCoerce
-
Added
TableColumn
andtableColumn
which selectsoptional
orrequired
based on write type. -
Added
TableColumns
as synonym forTableProperties
.TableProperties
will be deprecated in version 0.7. -
Added
table
as synonym forTable
.Table
will be deprecated in version 0.7. -
Added
tableWithSchema
as synonym forTableWithSchema
.Table
will be deprecated in version 0.7. -
Replaced
ColumnMaker
withUnpackspec
, which is identical to it. -
Added
Profunctor
instance forTable
-
Added
restrictExists
andrestrictNotExists
as synonyms forexists
andnotExists
. The latter will be deprecated in version 0.7.
0.5.4.0
- Added cursor interface (
Cursor
and friends)
0.5.3.1
distinctAggregator
,joinNullable
,exists
,notExists
,index
,timestamptzAtTimeZone
0.5.3.0
- Added support for range types
0.5.2.2
- Corrected fixity for .&&
0.5.2.1
- Improved documentation
0.5.2.0
- Added
Opaleye.FunctionalJoin
- Fixed handling of
BinExpr OpIn _ (ListExpr _)
indefaultSqlExpr
. in_
now actually uses the SQLIN
operator.- Added support for
ILIKE
0.5.1.0
- Added
- support for JSON operators
- Many improvements to the Haddocks
- RIGHT and FULL OUTER joins
0.5.0.0
- Added
(.===)
,aggregateOrdered
,countStar
,countRows
,quot_
,rem_
, ’charLength`- intersection and except query binary operators
Constant
instances forMaybe
and listsrunInsertManyReturning
runQueryFold
0.4.2.0
- Added
.===
and./==
for comparison of product types - Added
keepWhen
as an alternative torestrict
- Added
constant
conversion to and from Aeson - Added
pgValueJSON
andpgValueJSONB
0.4.1.0
- Added
Opaleye.Constant
for lifting constant values - Support microseconds in
pgLocalTime
,pgTimeOfDay
andpgUTCTime
- Added
unsafeCompositeField
to help with defining composite types Order
is an instance ofSemigroup
Thanks to Adam Bergmark and Matt Wraith for helping with these changes.
0.4.0.0
- Added
runUpdateReturning
- Ordering operators and
max
andmin
aggregators are now restricted to a typeclass - Added
stringAgg
andarrayAgg
aggregations. - Added
PGOrd
typeclass for typesafe ordering operations. - Support sorting NULLs first or last with
ascNullsFirst
anddescNullsLast
- Added JSON types
- Added
runInsertMany
Thanks to Travis Staton, Jakub Ryška and Christopher Lewis for helping with these changes.
0.3.1.2
- Use time >= 1.4 and time-locale-compat
0.3.1.1
- Bump time to >= 1.5
0.3.1
- 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
0.3
- Replace
Default QueryRunner
with a new classDefaultQueryRunnerColumn
, migrate withs/Default QueryRunner/DefaultQueryRunnerColumn
ands/def/queryRunnerColumnDefault/
- Remove
ShowConstant
, use the monomorphic functions defined in the new moduleOpaleye.PGTypes
instead. You will need to replaceColumn Bool
withColumn PGBool
etc. in query signatures - Re-export more modules from
Opaleye
- Add
boolAnd
,boolOr,
max
, andmin
aggregators - Add
lower
andupper
- Add operator fixities
- Add
maybeToNullable
- Add column instances for
Bool
,UUID
,Text
, andUTCTime
- Expose fieldQueryRunnerColumn from Opaleye.RunQuery
- Add
unsafeCast
- Re-export
Unpackspec
fromOpaleye.Manipulation