Esqueleto CI

Skeleton Image courtesy Chrissy Long

Esqueleto, a SQL DSL for Haskell

Esqueleto is a bare bones, type-safe EDSL for SQL queries that works with unmodified persistent SQL backends. The name of this library means “skeleton” in Portuguese and contains all three SQL letters in the correct order =). It was inspired by Scala’s Squeryl but created from scratch. Its language closely resembles SQL. Currently, SELECTs, UPDATEs, INSERTs and DELETEs are supported.

In particular, esqueleto is the recommended library for type-safe JOINs on persistent SQL backends. (The alternative is using raw SQL, but that’s error prone and does not offer any composability.). For more information read esqueleto.

Setup

If you’re already using persistent, then you’re ready to use esqueleto, no further setup is needed. If you’re just starting a new project and would like to use esqueleto, take a look at persistent’s book first to learn how to define your schema.

If you need to use persistent’s default support for queries as well, either import it qualified:

-- For a module that mostly uses esqueleto.
import Database.Esqueleto
import qualified Database.Persistent as P

or import esqueleto itself qualified:

-- For a module that uses esqueleto just on some queries.
import Database.Persistent
import qualified Database.Esqueleto as E

Other than identifier name clashes, esqueleto does not conflict with persistent in any way.

Goals

The main goals of esqueleto are:

  • Be easily translatable to SQL. (You should be able to know exactly how the SQL query will end up.)
  • Support the most widely used SQL features.
  • Be as type-safe as possible.

It is not a goal to be able to write portable SQL. We do not try to hide the differences between DBMSs from you

Introduction

For the following examples, we’ll use this example schema:

share [mkPersist sqlSettings, mkMigrate "migrateAll"] [persist|
  Person
    name String
    age Int Maybe
    deriving Eq Show
  BlogPost
    title String
    authorId PersonId
    deriving Eq Show
  Follow
    follower PersonId
    followed PersonId
    deriving Eq Show
|]

Select

Most of esqueleto was created with SELECT statements in mind, not only because they’re the most common but also because they’re the most complex kind of statement. The most simple kind of SELECT would be:

putPersons :: SqlPersist m ()
putPersons = do
  people <- select $
              from $ \person -> do
              return person
  liftIO $ mapM_ (putStrLn . personName . entityVal) people

which generates this SQL:

SELECT *
FROM Person

esqueleto knows that we want an Entity Person just because of the personName that is printed.

Where

Filtering by PersonName:

select $
from $ \p -> do
where_ (p ^. PersonName ==. val "John")
return p

which generates this SQL:

SELECT *
FROM Person
WHERE Person.name = "John"

The (^.) operator is used to project a field from an entity. The field name is the same one generated by persistents Template Haskell functions. We use val to lift a constant Haskell value into the SQL query.

Another example:

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

select $
from $ \p -> do
where_ (p ^. PersonAge >=. just (val 18))
return p

which generates this SQL:

SELECT *
FROM Person
WHERE Person.age >= 18

Since age is an optional Person field, we use just to liftval 18 :: SqlExpr (Value Int) into just (val 18) ::SqlExpr (Value (Maybe Int)).

Experimental/New Joins

There’s a new way to write JOINs in esqueleto! It has less potential for runtime errors and is much more powerful than the old syntax. To opt in to the new syntax, import:

import Database.Esqueleto.Experimental

This will conflict with the definition of from and on in the Database.Esqueleto module, so you’ll want to remove that import.

This style will become the new “default” in esqueleto-4.0.0.0, so it’s a good idea to port your code to using it soon.

The module documentation in Database.Esqueleto.Experimental has many examples, and they won’t be repeated here. Here’s a quick sample:

select $ do
  (a :& b) <-
    from $
      Table @BlogPost
      `InnerJoin`
      Table @Person
        `on` do \(bp :& a) ->
          bp ^. BlogPostAuthorId ==. a ^. PersonId
  pure (a, b)

Advantages:

  • ON clause is attached directly to the relevant join, so you never need to worry about how they’re ordered, nor will you ever run into bugs where the on clause is on the wrong JOIN
  • The ON clause lambda will all the available tables in it. This forbids runtime errors where an ON clause refers to a table that isn’t in scope yet.
  • You can join on a table twice, and the aliases work out fine with the ON clause.
  • You can use UNION, EXCEPT, INTERSECTION etc with this new syntax!
  • You can reuse subqueries more easily.

Legacy Joins

Implicit joins are represented by tuples.

For example, to get the list of all blog posts and their authors, we could write:

select $
from $ \(b, p) -> do
where_ (b ^. BlogPostAuthorId ==. p ^. PersonId)
orderBy [asc (b ^. BlogPostTitle)]
return (b, p)

which generates this SQL:

SELECT BlogPost.*, Person.*
FROM BlogPost, Person
WHERE BlogPost.authorId = Person.id
ORDER BY BlogPost.title ASC

However, you may want your results to include people who don’t have any blog posts as well using a LEFT OUTER JOIN:

select $
from $ \(p `LeftOuterJoin` mb) -> do
on (just (p ^. PersonId) ==. mb ?. BlogPostAuthorId)
orderBy [asc (p ^. PersonName), asc (mb ?. BlogPostTitle)]
return (p, mb)

which generates this SQL:

SELECT Person.*, BlogPost.*
FROM Person LEFT OUTER JOIN BlogPost
ON Person.id = BlogPost.authorId
ORDER BY Person.name ASC, BlogPost.title ASC

Left Outer Join

On a LEFT OUTER JOIN the entity on the right hand side may not exist (i.e. there may be a Person without any BlogPosts), so while p :: SqlExpr (Entity Person), we have mb :: SqlExpr (Maybe (Entity BlogPost)). The whole expression above has type SqlPersist m [(Entity Person, Maybe (Entity BlogPost))]. Instead of using (^.), we used (?.) to project a field from a Maybe (Entity a).

We are by no means limited to joins of two tables, nor by joins of different tables. For example, we may want a list of the Follow entity:

select $
from $ \(p1 `InnerJoin` f `InnerJoin` p2) -> do
on (p2 ^. PersonId ==. f ^. FollowFollowed)
on (p1 ^. PersonId ==. f ^. FollowFollower)
return (p1, f, p2)

which generates this SQL:

SELECT P1.*, Follow.*, P2.*
FROM Person AS P1
INNER JOIN Follow ON P1.id = Follow.follower
INNER JOIN Person AS P2 ON P2.id = Follow.followed

Update and Delete

do update $ \p -> do
     set p [ PersonName =. val "João" ]
     where_ (p ^. PersonName ==. val "Joao")
   delete $
     from $ \p -> do
     where_ (p ^. PersonAge <. just (val 14))

The results of queries can also be used for insertions. In SQL, we might write the following, inserting a new blog post for every user:

 insertSelect $ from $ \p->
 return $ BlogPost <# "Group Blog Post" <&> (p ^. PersonId)

which generates this SQL:

INSERT INTO BlogPost
SELECT ('Group Blog Post', id)
FROM Person

Individual insertions can be performed through Persistent’s insert function, reexported for convenience.

Re-exports

We re-export many symbols from persistent for convenience:

  • “Store functions” from “Database.Persist”.
  • Everything from “Database.Persist.Class” except for PersistQuery and delete (use deleteKey instead).
  • Everything from “Database.Persist.Types” except for Update, SelectOpt, BackendSpecificFilter and Filter.
  • Everything from “Database.Persist.Sql” except for deleteWhereCount and updateWhereCount.

RDBMS Specific

There are many differences between SQL syntax and functions supported by different RDBMSs. Since version 2.2.8, esqueleto includes modules containing functions that are specific to a given RDBMS.

  • PostgreSQL: Database.Esqueleto.PostgreSQL
  • MySQL: Database.Esqueleto.MySQL
  • SQLite: Database.Esqueleto.SQLite

In order to use these functions, you need to explicitly import their corresponding modules.

Unsafe functions, operators and values

Esqueleto doesn’t support every possible function, and it can’t - many functions aren’t available on every RDBMS platform, and sometimes the same functionality is hidden behind different names. To overcome this problem, Esqueleto exports a number of unsafe functions to call any function, operator or value. These functions can be found in Database.Esqueleto.Internal.Sql module.

Warning: the functions discussed in this section must always be used with an explicit type signature,and the user must be careful to provide a type signature that corresponds correctly with the underlying code. The functions have extremely general types, and if you allow type inference to figure everything out for you, it may not correspond with the underlying SQL types that you want. This interface is effectively the FFI to SQL database, so take care!

The most common use of these functions is for calling RDBMS specific or custom functions, for that end we use unsafeSqlFunction. For example, if we wish to consult the postgres now function we could so as follow:

postgresTime :: (MonadIO m, MonadLogger m) => SqlWriteT m UTCTime
postgresTime =
  result <- select (pure now)
  case result of
    [x] -> pure x
    _ -> error "now() is guaranteed to return a single result"
  where
    now :: SqlExpr (Value UTCTime)
    now = unsafeSqlFunction "now" ()

which generates this SQL:

SELECT now()

With the now function we could now use the current time of the postgres RDBMS on any query. Do notice that now does not use any arguments, so we use () that is an instance of UnsafeSqlFunctionArgument to represent no arguments, an empty list cast to a correct value will yield the same result as ().

We can also use unsafeSqlFunction for more complex functions with customs values using unsafeSqlValue which turns any string into a sql value of whatever type we want, disclaimer: if you use it badly you will cause a runtime error. For example, say we want to try postgres’ date_part function and get the day of a timestamp, we could use:

postgresTimestampDay :: (MonadIO m, MonadLogger m) => SqlWriteT m Int
postgresTimestampDay =
  result <- select (return $ dayPart date)
  case result of
    [x] -> pure x
    _ -> error "dayPart is guaranteed to return a single result"
  where
    dayPart :: SqlExpr (Value UTCTime) -> SqlExpr (Value Int)
    dayPart s = unsafeSqlFunction "date_part" (unsafeSqlValue "\'day\'" :: SqlExpr (Value String) ,s)
    date :: SqlExpr (Value UTCTime)
    date = unsafeSqlValue "TIMESTAMP \'2001-02-16 20:38:40\'"

which generates this SQL:

SELECT date_part('day', TIMESTAMP '2001-02-16 20:38:40')

Using unsafeSqlValue we were required to also define the type of the value.

Another useful unsafe function is unsafeSqlCastAs, which allows us to cast any type to another within a query. For example, say we want to use our previews dayPart function on the current system time, we could:

postgresTimestampDay :: (MonadIO m, MonadLogger m) => SqlWriteT m Int
postgresTimestampDay = do
  currentTime <- liftIO getCurrentTime
  result <- select (return $ dayPart (toTIMESTAMP $ val currentTime))
  case result of
    [x] -> pure x
    _ -> error "dayPart is guaranteed to return a single result"
  where
    dayPart :: SqlExpr (Value UTCTime) -> SqlExpr (Value Int)
    dayPart s = unsafeSqlFunction "date_part" (unsafeSqlValue "\'day\'" :: SqlExpr (Value String) ,s)
    toTIMESTAMP :: SqlExpr (Value UTCTime) -> SqlExpr (Value UTCTime)
    toTIMESTAMP = unsafeSqlCastAs "TIMESTAMP"

which generates this SQL:

SELECT date_part('day', CAST('2019-10-28 23:19:39.400898344Z' AS TIMESTAMP))

SQL injection

Esqueleto uses parameterization to prevent sql injections on values and arguments on all queries, for example, if we have:

myEvilQuery :: (MonadIO m, MonadLogger m) => SqlWriteT m ()
myEvilQuery =
  select (return $ val ("hi\'; DROP TABLE foo; select \'bye\'" :: String)) >>= liftIO . print

which generates this SQL(when using postgres):

SELECT 'hi''; DROP TABLE foo; select ''bye'''

And the printed value is hi\'; DROP TABLE foo; select \'bye\' and no table is dropped. This is good and makes the use of strings values safe. Unfortunately this is not the case when using unsafe functions. Let’s see an example of defining a new evil now function:

myEvilQuery :: (MonadIO m, MonadLogger m) => SqlWriteT m ()
myEvilQuery =
  select (return nowWithInjection) >>= liftIO . print
  where
    nowWithInjection :: SqlExpr (Value UTCTime)
    nowWithInjection = unsafeSqlFunction "0; DROP TABLE bar; select now" ([] :: [SqlExpr (Value Int)])

which generates this SQL:

SELECT 0; DROP TABLE bar; select now()

If we were to run the above code we would see the postgres time printed but the table bar will be erased with no indication whatsoever. Another example of this behavior is seen when using unsafeSqlValue:

myEvilQuery :: (MonadIO m, MonadLogger m) => SqlWriteT m ()
myEvilQuery =
  select (return $ dayPart dateWithInjection) >>= liftIO . print
  where
    dayPart :: SqlExpr (Value UTCTime) -> SqlExpr (Value Int)
    dayPart s = unsafeSqlFunction "date_part" (unsafeSqlValue "\'day\'" :: SqlExpr (Value String) ,s)
    dateWithInjection :: SqlExpr (Value UTCTime)
    dateWithInjection = unsafeSqlValue "TIMESTAMP \'2001-02-16 20:38:40\');DROP TABLE bar; select (16"

which generates this SQL:

SELECT date_part('day', TIMESTAMP '2001-02-16 20:38:40');DROP TABLE bar; select (16)

This will print 16 and also erase the bar table. The main take away of this examples is to never use any user or third party input inside an unsafe function without first parsing it or heavily sanitizing the input.

Tests

To run the tests, do stack test. This tests all the backends, so you’ll need to have MySQL and Postgresql installed.

Postgres

Using apt-get, you should be able to do:

sudo apt-get install postgresql postgresql-contrib
sudo apt-get install libpq-dev

Using homebrew on OSx

brew install postgresql
brew install libpq

Detailed instructions on the Postgres wiki here

The connection details are located near the bottom of the test/PostgreSQL/Test.hs file:

withConn =
  R.runResourceT . withPostgresqlConn "host=localhost port=5432 user=esqutest password=esqutest dbname=esqutest"

You can change these if you like but to just get them working set up as follows on linux:

$ sudo -u postgres createuser esqutest
$ sudo -u postgres createdb esqutest
$ sudo -u postgres psql
postgres=# \password esqutest

And on osx

$ createuser esqutest
$ createdb esqutest
$ psql postgres
postgres=# \password esqutest

MySQL

To test MySQL, you’ll need to have a MySQL server installation. Then, you’ll need to create a database esqutest and a 'travis'@'localhost' user which can access it:

mysql> CREATE DATABASE esqutest;
mysql> CREATE USER 'travis'@'localhost';
mysql> ALTER USER 'travis'@'localhost' IDENTIFIED BY 'esqutest';
mysql> GRANT ALL ON esqutest.* TO 'travis';

Changes

3.5.2.1

  • @cdparks
    • #273 - Avoid generating an empty list as the left operand to NOT IN.

3.5.2.0

  • @ivanbakel
    • #268 - Added SqlSelect instance for (:&), allowing it to be returned from queries just like (,) tuples.

3.5.1.0

  • @ibarrae
    • #265 - Added selectOne

3.5.0.0

  • @belevy
    • #228 - Destroy all GADTs; Removes the From GADT and SqlExpr GADT - From GADT is replaced with a From data type and FromRaw - SqlExpr is now all defined in terms of ERaw - Modified ERaw to contain a SqlExprMeta with any extra information that may be needed - Experimental top level is now strictly for documentation and all the implementation details are in Experimental.* modules
  • @parsonsmatt
    • #259
      • Create the Database.Esqueleto.Legacy module. The Database.Esqueleto module now emits a warning, directing users to either import Database.Esqueleto.Legacy to keep the old behavior or to import Database.Esqueleto.Experimental to opt in to the new behavior.
      • Deleted the deprecated modules Database.Esqueleto.Internal.{Language,Sql}. Please use Database.Esqueleto.Internal.Internal instead, or ideally post what you need from the library so we can support you safely.
      • Support GHC 9

3.4.2.2

  • @parsonsmatt
    • #255
      • Fix a bug where a composite primary key in a groupBy clause would break.

3.4.2.1

  • @parsonsmatt
    • #245
      • Support persistent-2.13

3.4.2.0

  • @parsonsmatt
    • #243
      • Support persistent-2.12

3.4.1.1

  • @MaxGabriel
    • #240
      • Improve recommend hlint to avoid doing x = NULL SQL queries

3.4.1.0

  • @arthurxavierx
    • #238
      • Fix non-exhaustive patterns in unsafeSqlAggregateFunction
  • @Vlix
    • #232
      • Export the ValidOnClauseValue type family

3.4.0.1

  • @arthurxavierx
    • #221
      • Deprecate ToAliasT and ToAliasReferenceT
  • @parsonsmatt
    • #226
      • Support persistent-2.11
  • @belevy
    • #225
      • Simplify ToFromT extracting the overlapping and type error instances
      • Make ToFromT and associated type family of ToFrom

3.4.0.0

  • @belevy, @charukiewicz
    • #215
      • Added support for common table expressions (with, withRecursive)
      • Added support for lateral JOINs with updated example (Example #6)
      • Deprecated SelectQuery, removing the neccessity to tag SqlQuery values
      • Deprecated use of data constructors for SQL set operations (replaced with functions)
      • Refactored module structure to fix haddock build (fixes build from 3.3.4.0)

3.3.4.1

  • @maxgabriel
    • #214
      • Add suggested hlint rules for proper isNothing usage

3.3.4.0

  • @parsonsmatt
    • #205
      • More documentation on the Experimental module

      • Database.Esqueleto.Experimental now reexports Database.Esqueleto, so the new “approved” import syntax is less verbose. Before, you’d write:

        import Database.Esqueleto hiding (from, on)
        import Database.Esqueleto.Experimental
        

        Now you can merely write:

        import Database.Esqueleto.Experimental
        

        Users will get ‘redundant import’ warnings if they followed the original syntax, the solution is evident from the error message provided.

3.3.3.3

  • @belevy
    • #191 - Bugfix rollup: Fix issue with extra characters in generated SQL; Fix ToAliasReference for already referenced values; Fix Alias/Reference for Maybe Entity
  • @maxgabriel
    • #203 Document isNothing
  • @sestrella
    • #198 - Allow PostgreSQL aggregate functions to take a filter clause

3.3.3.2

  • @maxgabriel
    • #190 Further document and test ToBaseId

3.3.3.1

  • @belevy
    • #189 - Fix bug in function calls with aliased values introduced by SubQuery joins.

3.3.3.0

  • @belevy
    • #172 - Introduce new experimental module for joins, set operations (eg UNION), and safer queries from outer joins.

3.3.2

  • @belevy
    • #177 Fix natural key handling in (^.)

3.3.1.1

  • @parsonsmatt
    • #170 Add documentation to groupBy to explain tuple nesting.

3.3.1

  • @charukiewicz, @belevy, @joemalin95
    • #167: Exposed functions that were added in 3.3.0

3.3.0

  • @charukiewicz, @belevy, @joemalin95
    • #166: Add several common SQL string functions: upper_, trim_, ltrim_, rtrim_, length_, left_, right_

3.2.3

  • @hdgarrood
    • #163: Allow unsafeSqlFunction to take up to 10 arguments without needing to nest tuples.

3.2.2

  • @parsonsmatt
    • #161: Fix an issue where nested joins didn’t get the right on clause.

3.2.1

  • @parsonsmatt
    • #159: Add an instance of UnsafeSqlFunction () for 0-argument SQL functions.

3.2.0

  • @parsonsmatt
    • #153: Deprecate sub_select and introduce subSelect, subSelectMaybe, and subSelectUnsafe.
  • @parsonsmatt
    • #156: Remove the restriction that on clauses must appear in reverse order to the joining tables.

3.1.3

  • @JoseD92
    • #155: Added insertSelectWithConflict postgres function.

3.1.2

  • @tippenein
    • #149: Added associateJoin query helpers.

3.1.1

  • @JoseD92

    • #149: Added upsert support.
  • @parsonsmatt

    • #133: Added renderQueryToText and related functions.

3.1.0

  • @Vlix
    • #128: Added Database.Esqueleto.PostgreSQL.JSON module with JSON operators and JSONB data type.
  • @ibarrae
    • #127: Added between and support for composite keys in unsafeSqlBinOp.

3.0.0

  • @parsonsmatt
    • #122: Support persistent-2.10.0. This is a breaking change due to the removal of deprecated exports from the persistent library.
    • #113: Remove the esqueleto type class. To migrate here, use SqlExpr, SqlQuery, and SqlBackend instead of using the polymorphic Esqueleto sqlExpr sqlQuery sqlBackend => ... types.

2.7.0

  • @parsonsmatt
    • #117: Removed sqlQQ and executeQQ functions from export, fixing doc build and building with persistent >= 2.9

2.6.1

  • @ChrisCoffey
    • #114: Fix Haddock by working around an upstream bug.

2.6.0