WHERE clause: restrict the query's result.

An ON clause, useful to describe how two tables are related. Cross joins and tuple-joins do not need an on clause, but InnerJoin and the various outer joins do.

Database.Esqueleto.Experimental in version 4.0.0.0 of the library. The Experimental module has a dramatically improved means for introducing tables and entities that provides more power and less potential for runtime errors.

If you don't include an on clause (or include too many!) then a runtime exception will be thrown.

As an example, consider this simple join:

select $
from $ \(foo `InnerJoin` bar) -> do
  on (foo ^. FooId ==. bar ^. BarFooId)
  ...

We need to specify the clause for joining the two columns together. If we had this:

select $
from $ \(foo `CrossJoin` bar) -> do
  ...

Then we can safely omit the on clause, because the cross join will make pairs of all records possible.

You can do multiple on clauses in a query. This query joins three tables, and has two on clauses:

select $
from $ \(foo `InnerJoin` bar `InnerJoin` baz) -> do
  on (baz ^. BazId ==. bar ^. BarBazId)
  on (foo ^. FooId ==. bar ^. BarFooId)
  ...

Old versions of esqueleto required that you provide the on clauses in reverse order. This restriction has been lifted - you can now provide on clauses in any order, and the SQL should work itself out. The above query is now totally equivalent to this:

select $
from $ \(foo `InnerJoin` bar `InnerJoin` baz) -> do
  on (foo ^. FooId ==. bar ^. BarFooId)
  on (baz ^. BazId ==. bar ^. BarBazId)
  ...

GROUP BY clause. You can enclose multiple columns in a tuple.

select $ from \(foo `InnerJoin` bar) -> do
  on (foo ^. FooBarId ==. bar ^. BarId)
  groupBy (bar ^. BarId, bar ^. BarName)
  return (bar ^. BarId, bar ^. BarName, countRows)

With groupBy you can sort by aggregate functions, like so (we used let to restrict the more general countRows to SqlSqlExpr (Value Int) to avoid ambiguity---the second use of countRows has its type restricted by the :: Int below):

r <- select $ from \(foo `InnerJoin` bar) -> do
  on (foo ^. FooBarId ==. bar ^. BarId)
  groupBy $ bar ^. BarName
  let countRows' = countRows
  orderBy [asc countRows']
  return (bar ^. BarName, countRows')
forM_ r $ \(Value name, Value count) -> do
  print name
  print (count :: Int)

Need more columns?

The ToSomeValues class is defined for SqlExpr and tuples of SqlExprs. We only have definitions for up to 8 elements in a tuple right now, so it's possible that you may need to have more than 8 elements.

For example, consider a query with a groupBy call like this:

groupBy (e0, e1, e2, e3, e4, e5, e6, e7)

This is the biggest you can get with a single tuple. However, you can easily nest the tuples to add more:

groupBy ((e0, e1, e2, e3, e4, e5, e6, e7), e8, e9)

ORDER BY clause. See also asc and desc.

Multiple calls to orderBy get concatenated on the final query, including distinctOnOrderBy.

Ascending order of this field or SqlExpression.

Descending order of this field or SqlExpression.

LIMIT. Limit the number of returned rows.

OFFSET. Usually used with limit.

DISTINCT. Change the current SELECT into SELECT DISTINCT. For example:

select $ distinct $
  from \foo -> do
  ...

Note that this also has the same effect:

select $
  from \foo -> do
  distinct (return ())
  ...

Since: 2.2.4

DISTINCT ON. Change the current SELECT into SELECT DISTINCT ON (SqlExpressions). For example:

select $
  from \foo ->
  distinctOn [don (foo ^. FooName), don (foo ^. FooState)] $ do
  ...

You can also chain different calls to distinctOn. The above is equivalent to:

select $
  from \foo ->
  distinctOn [don (foo ^. FooName)] $
  distinctOn [don (foo ^. FooState)] $ do
  ...

Each call to distinctOn adds more SqlExpressions. Calls to distinctOn override any calls to distinct.

Note that PostgreSQL requires the SqlExpressions on DISTINCT ON to be the first ones to appear on a ORDER BY. This is not managed automatically by esqueleto, keeping its spirit of trying to be close to raw SQL.

Supported by PostgreSQL only.

Since: 2.2.4

Erase an SqlExpression's type so that it's suitable to be used by distinctOn.

Since: 2.2.4

A convenience function that calls both distinctOn and orderBy. In other words,

distinctOnOrderBy [asc foo, desc bar, desc quux] $ do
  ...

is the same as:

distinctOn [don foo, don  bar, don  quux] $ do
  orderBy  [asc foo, desc bar, desc quux]
  ...

Since: 2.2.4

HAVING.

Since: 1.2.2

Add a locking clause to the query. Please read LockingKind documentation and your RDBMS manual. Unsafe since not all locking clauses are implemented for every RDBMS

If multiple calls to locking are made on the same query, the last one is used.

Since: 2.2.7

Project a field of an entity.

Project an EntityField of a nullable entity. The result type will be Nullable, meaning that nested Maybe won't be produced here.

As of v3.6.0.0, this will attempt to combine nested Maybe. If you want to keep nested Maybe, then see ??..

Lift a constant value from Haskell-land to the query.

IS NULL comparison.

For IS NOT NULL, you can negate this with not_, as in not_ (isNothing (person ^. PersonAge))

Warning: Persistent and Esqueleto have different behavior for != Nothing:

In SQL, = NULL and != NULL return NULL instead of true or false. For this reason, you very likely do not want to use !=. Nothing in Esqueleto. You may find these hlint rules helpful to enforce this:

- error: {lhs: v Database.Esqueleto.==. Database.Esqueleto.nothing, rhs: Database.Esqueleto.isNothing v, name: Use Esqueleto's isNothing}
- error: {lhs: v Database.Esqueleto.==. Database.Esqueleto.val Nothing, rhs: Database.Esqueleto.isNothing v, name: Use Esqueleto's isNothing}
- error: {lhs: v Database.Esqueleto.!=. Database.Esqueleto.nothing, rhs: not_ (Database.Esqueleto.isNothing v), name: Use Esqueleto's not isNothing}
- error: {lhs: v Database.Esqueleto.!=. Database.Esqueleto.val Nothing, rhs: not_ (Database.Esqueleto.isNothing v), name: Use Esqueleto's not isNothing}

Analogous to Just, promotes a value of type typ into one of type Maybe typ. It should hold that val . Just === just . val.

This function will try not to produce a nested Maybe. This is in accord with how SQL represents NULL. That means that just . just = just. This behavior was changed in v3.6.0.0. If you want to produce nested Maybe, see just'.

Like just, but this function does not try to collapse nested Maybe. This may be useful if you have type inference problems with just.

Since: 3.6.0.0

NULL value.

Join nested Maybes in a Value into one. This is useful when calling aggregate functions on nullable fields.

As of v3.6.0.0, this function will attempt to work on both SqlExpr (Value (Maybe a)) as well as SqlExpr (Value (Maybe (Maybe a))) inputs to make transitioning to NullableFieldProjection easier. This may make type inference worse in some cases. If you want the monomorphic variant, see joinV'

Like joinV, but monomorphic: the input type only works on SqlExpr (Value (Maybe (Maybe a))).

This function may be useful if you have type inference issues with joinV.

Since: 3.6.0.0

Project an SqlExpression that may be null, guarding against null cases.

COUNT(*) value.

COUNT.

COUNT(DISTINCT x).

Since: 2.4.1

This operator produces the SQL operator =, which is used to compare values for equality.

Example:

 query :: UserId -> SqlPersistT IO [Entity User]
 query userId = select $ do
     user <- from $ table @User
     where_ (user ^. UserId ==. val userId)
     pure user

This would generate the following SQL:

 SELECT user.*
 FROM user
 WHERE user.id = ?

This operator translates to the SQL operator >=.

Example:

 where_ $ user ^. UserAge >=. val 21

This operator translates to the SQL operator >.

Example:

 where_ $ user ^. UserAge >. val 20

This operator translates to the SQL operator <=.

Example:

 where_ $ val 21 <=. user ^. UserAge

This operator translates to the SQL operator <.

Example:

 where_ $ val 20 <. user ^. UserAge

This operator translates to the SQL operator !=.

Example:

 where_ $ user ^. UserName !=. val Bob

This operator translates to the SQL operator AND.

Example:

 where_ $
         user ^. UserName ==. val Matt
     &&. user ^. UserAge >=. val 21

This operator translates to the SQL operator AND.

Example:

 where_ $
         user ^. UserName ==. val Matt
     ||. user ^. UserName ==. val John

a between (b, c) translates to the SQL expression a >= b AND a <= c. It does not use a SQL BETWEEN operator.

@since: 3.1.0

This operator translates to the SQL operator +.

This does not require or assume anything about the SQL values. Interpreting what +. means for a given type is left to the database engine.

Example:

 user ^. UserAge +. val 10

This operator translates to the SQL operator -.

This does not require or assume anything about the SQL values. Interpreting what -. means for a given type is left to the database engine.

Example:

 user ^. UserAge -. val 10

This operator translates to the SQL operator /.

This does not require or assume anything about the SQL values. Interpreting what /. means for a given type is left to the database engine.

Example:

 user ^. UserAge /. val 10

This operator translates to the SQL operator *.

This does not require or assume anything about the SQL values. Interpreting what *. means for a given type is left to the database engine.

Example:

 user ^. UserAge *. val 10

Allow a number of one type to be used as one of another type via an implicit cast. An explicit cast is not made, this function changes only the types on the Haskell side.

Caveat: Trying to use castNum from Double to Int will not result in an integer, the original fractional number will still be used! Use round_, ceiling_ or floor_ instead.

Safety: This operation is mostly safe due to the Num constraint between the types and the fact that RDBMSs usually allow numbers of different types to be used interchangeably. However, there may still be issues with the query not being accepted by the RDBMS or persistent not being able to parse it.

Since: 2.2.9

Same as castNum, but for nullable values.

Since: 2.2.9

COALESCE function. Evaluates the arguments in order and returns the value of the first non-NULL SqlExpression, or NULL (Nothing) otherwise. Some RDBMSs (such as SQLite) require at least two arguments; please refer to the appropriate documentation.

Since: 1.4.3

Like coalesce, but takes a non-nullable SqlExpression placed at the end of the SqlExpression list, which guarantees a non-NULL result.

Since: 1.4.3

LOWER function.

UPPER function. @since 3.3.0

TRIM function. @since 3.3.0

LTRIM function. @since 3.3.0

RTRIM function. @since 3.3.0

LENGTH function. @since 3.3.0

LEFT function. @since 3.3.0

RIGHT function. @since 3.3.0

LIKE operator.

ILIKE operator (case-insensitive LIKE).

Supported by PostgreSQL only. Deprecated in version 3.6.0 in favor of the version available from Database.Esqueleto.PostgreSQL.

Since: 2.2.3

The string %. May be useful while using like and concatenation (concat_ or ++., depending on your database). Note that you always have to type the parenthesis, for example:

name `like` (%) ++. val "John" ++. (%)

The CONCAT function with a variable number of parameters. Supported by MySQL and PostgreSQL. SQLite supports this in versions after 3.44.0, and persistent-sqlite supports this in versions 2.13.3.0 and after.

The || string concatenation operator (named after Haskell's ++ in order to avoid naming clash with ||.).

Supported by SQLite and PostgreSQL.

MySQL support requires setting the SQL mode to PIPES_AS_CONCAT or ANSI - see this StackOverflow answer.

Cast a string type into Text. This function is very useful if you want to use newtypes, or if you want to apply functions such as like to strings of different types.

Safety: This is a slightly unsafe function, especially if you have defined your own instances of SqlString. Also, since Maybe is an instance of SqlString, it's possible to turn a nullable value into a non-nullable one. Avoid using this function if possible.

Execute a subquery SELECT in an SqlExpression. Returns a list of values.

Lift a list of constant value from Haskell-land to the query.

Same as just but for ValueList. Most of the time you won't need it, though, because you can use just from inside subList_select or Just from inside valList.

Since: 2.2.12

IN operator. For example if you want to select all Persons by a list of IDs:

SELECT *
FROM Person
WHERE Person.id IN (?)

In esqueleto, we may write the same query above as:

select $
from $ \person -> do
where_ $ person ^. PersonId `in_` valList personIds
return person

Where personIds is of type [Key Person].

NOT IN operator.

EXISTS operator. For example:

select $
from $ \person -> do
where_ $ exists $
         from $ \post -> do
         where_ (post ^. BlogPostAuthorId ==. person ^. PersonId)
return person

NOT EXISTS operator.

SET clause used on UPDATEs. Note that while it's not a type error to use this function on a SELECT, it will most certainly result in a runtime error.

CASE statement. For example:

select $
return $
case_
   [ when_
       (exists $
       from $ \p -> do
       where_ (p ^. PersonName ==. val "Mike"))
     then_
       (subSelect $
       from $ \v -> do
       let sub =
               from $ \c -> do
               where_ (c ^. PersonName ==. val "Mike")
               return (c ^. PersonFavNum)
       where_ (just (v ^. PersonFavNum) >. subSelect sub)
       return $ count (v ^. PersonName) +. val (1 :: Int)) ]
   (else_ $ val (-1))

This query is a bit complicated, but basically it checks if a person named "Mike" exists, and if that person does, run the subquery to find out how many people have a ranking (by Fav Num) higher than "Mike".

NOTE: There are a few things to be aware about this statement.

• This only implements the full CASE statement, it does not implement the "simple" CASE statement.
• At least one when_ and then_ is mandatory otherwise it will emit an error.
• The else_ is also mandatory, unlike the SQL statement in which if the ELSE is omitted it will return a NULL. You can reproduce this via nothing.

Since: 2.1.2

Convert an entity's key into another entity's.

This function is to be used when you change an entity's Id to be that of another entity. For example:

Bar
  barNum Int
Foo
  bar BarId
  fooNum Int
  Primary bar

In this example, Bar is said to be the BaseEnt(ity), and Foo the child. To model this in Esqueleto, declare:

instance ToBaseId Foo where
  type BaseEnt Foo = Bar
  toBaseIdWitness barId = FooKey barId

Now you're able to write queries such as:

select $
from $ (bar `InnerJoin` foo) -> do
on (toBaseId (foo ^. FooId) ==. bar ^. BarId)
return (bar, foo)

Note: this function may be unsafe to use in conditions not like the one of the example above.

Since: 2.4.3

The inverse of toBaseId. Note that this is somewhat less "safe" than toBaseId. Calling toBaseId will usually mean that a foreign key constraint is present that guarantees the presence of the base ID. fromBaseId has no such guarantee. Consider the code example given in toBaseId:

Bar
  barNum Int
Foo
  bar BarId
  fooNum Int
  Primary bar

instance ToBaseId Foo where
  type BaseEnt Foo = Bar
  toBaseIdWitness barId = FooKey barId

The type of toBaseId for Foo would be:

toBaseId :: SqlExpr (Value FooId) -> SqlExpr (Value BarId)

The foreign key constraint on Foo means that every FooId points to a BarId in the database. However, fromBaseId will not have this:

fromBaseId :: SqlExpr (Value BarId) -> SqlExpr (Value FooId)

Since: 3.6.0.0

As fromBaseId, but works on Maybe keys.

Since: 3.6.0.0

Like toBaseId, but works on Maybe keys.

Since: 3.6.0.0

Execute a subquery SELECT in a SqlExpr. The query passed to this function will only return a single result - it has a LIMIT 1 passed in to the query to make it safe, and the return type is Maybe to indicate that the subquery might result in 0 rows.

If you find yourself writing joinV . subSelect, then consider using subSelectMaybe.

If you're performing a countRows, then you can use subSelectCount which is safe.

If you know that the subquery will always return exactly one row (eg a foreign key constraint guarantees that you'll get exactly one row), then consider subSelectUnsafe, along with a comment explaining why it is safe.

Since: 3.2.0

Execute a subquery SELECT in a SqlExpr. This function is a shorthand for the common joinV . subSelect idiom, where you are calling subSelect on an expression that would be Maybe already.

As an example, you would use this function when calling sum_ or max_, which have Maybe in the result type (for a 0 row query).

Since: 3.2.0

Performs a COUNT of the given query in a subSelect manner. This is always guaranteed to return a result value, and is completely safe.

Since: 3.2.0

Performs a sub-select using the given foreign key on the entity. This is useful to extract values that are known to be present by the database schema.

As an example, consider the following persistent definition:

User
  profile ProfileId

Profile
  name    Text

The following query will return the name of the user.

getUserWithName =
    select $
    from $ user ->
    pure (user, subSelectForeign user UserProfile (^. ProfileName)

Since: 3.2.0

Execute a subquery SELECT in a SqlExpr that returns a list. This is an alias for subList_select and is provided for symmetry with the other safe subselect functions.

Since: 3.2.0

Execute a subquery SELECT in a SqlExpr. This function is unsafe, because it can throw runtime exceptions in two cases:

1. If the query passed has 0 result rows, then it will return a NULL value. The persistent parsing operations will fail on an unexpected NULL.
2. If the query passed returns more than one row, then the SQL engine will fail with an error like "More than one row returned by a subquery used as an expression".

This function is safe if you guarantee that exactly one row will be returned, or if the result already has a Maybe type for some reason.

For variants with the safety encoded already, see subSelect and subSelectMaybe. For the most common safe use of this, see subSelectCount.

Since: 3.2.0

Class that enables one to use toBaseId to convert an entity's key on a query into another (cf. toBaseId).

Syntax sugar for case_.

Since: 2.1.2

Syntax sugar for case_.

Since: 2.1.2

Syntax sugar for case_.

Since: 2.1.2

FROM clause: bring entities into scope.

Note that this function will be replaced by the one in Database.Esqueleto.Experimental in version 4.0.0.0 of the library. The Experimental module has a dramatically improved means for introducing tables and entities that provides more power and less potential for runtime errors.

This function internally uses two type classes in order to provide some flexibility of how you may call it. Internally we refer to these type classes as the two different magics.

The innermost magic allows you to use from with the following types:

• expr (Entity val), which brings a single entity into scope.
• expr (Maybe (Entity val)), which brings a single entity that may be NULL into scope. Used for OUTER JOINs.
• A JOIN of any other two types allowed by the innermost magic, where a JOIN may be an InnerJoin, a CrossJoin, a LeftOuterJoin, a RightOuterJoin, or a FullOuterJoin. The JOINs have left fixity.

The outermost magic allows you to use from on any tuples of types supported by innermost magic (and also tuples of tuples, and so on), up to 8-tuples.

Note that using from for the same entity twice does work and corresponds to a self-join. You don't even need to use two different calls to from, you may use a JOIN or a tuple.

The following are valid examples of uses of from (the types of the arguments of the lambda are inside square brackets):

from $ \person -> ...
from $ \(person, blogPost) -> ...
from $ \(p `LeftOuterJoin` mb) -> ...
from $ \(p1 `InnerJoin` f `InnerJoin` p2) -> ...
from $ \((p1 `InnerJoin` f) `InnerJoin` p2) -> ...

The types of the arguments to the lambdas above are, respectively:

person
  :: ( Esqueleto query expr backend
     , PersistEntity Person
     , PersistEntityBackend Person ~ backend
     ) => expr (Entity Person)
(person, blogPost)
  :: (...) => (expr (Entity Person), expr (Entity BlogPost))
(p `LeftOuterJoin` mb)
  :: (...) => InnerJoin (expr (Entity Person)) (expr (Maybe (Entity BlogPost)))
(p1 `InnerJoin` f `InnerJoin` p2)
  :: (...) => InnerJoin
                (InnerJoin (expr (Entity Person))
                           (expr (Entity Follow)))
                (expr (Entity Person))
(p1 `InnerJoin` (f `InnerJoin` p2)) ::
  :: (...) => InnerJoin
                (expr (Entity Person))
                (InnerJoin (expr (Entity Follow))
                           (expr (Entity Person)))

Note that some backends may not support all kinds of JOINs.

A single value (as opposed to a whole entity). You may use (^.) or (?.) to get a Value from an Entity.

A list of single values. There's a limited set of functions able to work with this data type (such as subList_select, valList, in_ and exists).

Phantom type used by orderBy, asc and desc.

Phantom type used by distinctOn and don.

Different kinds of locking clauses supported by locking.

Note that each RDBMS has different locking support. The constructors of this datatype specify only the syntax of the locking mechanism, not its semantics. For example, even though both MySQL and PostgreSQL support ForUpdate, there are no guarantees that they will behave the same.

Since: 2.2.7

FOR UPDATE syntax.

Usage:

 locking forUpdate

Since: 3.6.0.0

FOR UPDATE SKIP LOCKED syntax.

 locking forUpdateSkipLocked

Since: 3.6.0.0

Lockable entity

Example use:

select $ do
    (p :& bp) <- from $
        table Person
        innerJoin table BlogPost
            on do
                (p :& bp) -> p ^. PersonId ==. b ^. BlogPostAuthorId
    forUpdateOf (p :& b) skipLocked
    return p

Phantom class of data types that are treated as strings by the RDBMS. It has no methods because it's only used to avoid type errors such as trying to concatenate integers.

If you have a custom data type or newtype, feel free to make it an instance of this class.

Since: 2.4.0

Data type that represents an INNER JOIN (see LeftOuterJoin for an example).

Data type that represents a CROSS JOIN (see LeftOuterJoin for an example).

Data type that represents a LEFT OUTER JOIN. For example,

select $
from $ \(person `LeftOuterJoin` pet) ->
  ...

is translated into

SELECT ...
FROM Person LEFT OUTER JOIN Pet
...

See also: from.

Data type that represents a RIGHT OUTER JOIN (see LeftOuterJoin for an example).

Data type that represents a FULL OUTER JOIN (see LeftOuterJoin for an example).

(Internal) A kind of JOIN.

Exception thrown whenever on is used to create an ON clause but no matching JOIN is found.

SQL backend for esqueleto using SqlPersistT.

An expression on the SQL backend.

Raw expression: Contains a SqlExprMeta and a function for building the expr. It recieves a parameter telling it whether it is in a parenthesized context, and takes information about the SQL connection (mainly for escaping names) and returns both an string (Builder) and a list of values to be interpolated by the SQL backend.