Esqueleto

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

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

Note carefully that the order of the ON clauses is reversed! You're required to write your ons in reverse order because that helps composability (see the documentation of on for more details).

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.

Tests and Postgres

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

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