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.

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

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

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: esqueleto-2.1.2

Syntax sugar for case_.

Since: esqueleto-2.1.2

Syntax sugar for case_.

Since: esqueleto-2.1.2

Syntax sugar for case_.

Since: esqueleto-2.1.2

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 ^. UserName !=. val Bob

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

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

COUNT.

COUNT(*) value.

COUNT(DISTINCT x).

Since: esqueleto-2.4.1

EXISTS operator. For example:

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

NOT EXISTS operator.

Apply a PersistField constructor to SqlExpr Value arguments.

Apply extra SqlExpr Value arguments to a PersistField constructor

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: esqueleto-2.4.3

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

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.

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: esqueleto-2.2.12

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.

Since: esqueleto-3.5.10.0

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'

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: esqueleto-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: esqueleto-1.4.3

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: esqueleto-2.2.9

Same as castNum, but for nullable values.

Since: esqueleto-2.2.9

Ascending order of this field or SqlExpression.

Descending order of this field or SqlExpression.

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

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: esqueleto-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 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: esqueleto-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: esqueleto-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: esqueleto-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: esqueleto-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: esqueleto-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: esqueleto-3.2.0

Get the first table of a given type from a chain of tables joined with (:&).

This can make it easier to write queries with a large number of join clauses:

select $ do
(people :& followers :& blogPosts) <-
    from $ table @Person
    `innerJoin` table @Follow
    `on` (\(person :& follow) ->
            person ^. PersonId ==. follow ^. FollowFollowed)
    `innerJoin` table @BlogPost
    `on` (\((getTable @Follow -> follow) :& blogPost) ->
            blogPost ^. BlogPostAuthorId ==. follow ^. FollowFollower)
where_ (people1 ^. PersonName ==. val "John")
pure (followers, people2)

This example is a bit trivial, but once you've joined five or six tables it becomes enormously helpful. The above example uses a ViewPattern to call the function and assign the variable directly, but you can also imagine it being written like this:

    `on` (\(prev :& blogPost) ->
            let
                follow = getTable @Follow prev
             in
                blogPost ^. BlogPostAuthorId ==. follow ^. FollowFollower)

This function will pluck out the first table that matches the applied type, so if you join on the same table multiple times, it will always select the first one provided.

The (:&) operator associates so that the left hand side can be a wildcard for an arbitrary amount of nesting, and the "most recent" or "newest" table in a join sequence is always available on the rightmost - so (prev :& bar) is a pattern that matches bar table (the most recent table added) and prev tables (all prior tables in the join match).

By calling getTable on the prev, you can select exactly the table you want, allowing you to omit a large number of spurious pattern matches. Consider a query that does several LEFT JOIN on a first table:

SELECT *
FROM person
LEFT JOIN car
  ON person.id = car.person_id
LEFT JOIN bike
  ON person.id = bike.person_id
LEFT JOIN food
  ON person.id = food.person_id
LEFT JOIN address
  ON person.id = address.person_id

The final on clause in esqueleto would look like this:

    `on` do
        \(person :& _car :& _bike :& _food :& address) ->
            person.id ==. address.personId

First, we can change it to a prev :& newest match. We can do this because of the operator associativity. This is kind of like how a list : operator associates, but in the other direction: a : (b : c) = a : b : c.

    `on` do
        \(prev :& address) ->
            let (person :& _car :& _bike :& _food) = prev
             in person.id ==. address.personId

Then, we can use getTable to select the Person table directly, instead of pattern matching manually.

    `on` do
        \(prev :& address) ->
            let person = getTable @Person prev
             in person.id ==. address.personId

Finally, we can use a ViewPattern language extension to "inline" the access.

    `on` do
        \((getTable @Person -> person) :& address) ->
           person.id ==. address.personId

With this form, you do not need to be concerned about the number and wildcard status of tables that do not matter to the specific ON clause.

Since: esqueleto-3.5.9.0

A variant of getTable that operates on possibly-null entities.

Since: esqueleto-3.5.9.0