Database.Esqueleto.Experimental

WHERE clause: restrict the query's result.

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)

An alias for groupBy that avoids conflict with the term from Data.List groupBy.

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 ())
  ...

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.

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

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]
  ...

HAVING.

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.

FOR UPDATE syntax.

Usage:

 locking forUpdate

FOR UPDATE SKIP LOCKED syntax.

 locking forUpdateSkipLocked

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}

An alias for isNothing that avoids clashing with the function from Data.Maybe 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'.

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'

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

COUNT(*) value.

COUNT.

COUNT(DISTINCT x).

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.

Same as castNum, but for nullable values.

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.

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

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.

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.

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.

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.

Like toBaseId, but works on Maybe keys.

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)

As fromBaseId, but works on Maybe keys.

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.

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).

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

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)

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.

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.

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

Syntax sugar for case_.

Syntax sugar for case_.

Syntax sugar for case_.

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.

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.