Although it covers most of the useful cases, persistent's API may not be enough for some of your tasks. May be you need some complex JOIN query, or a database-specific command needs to be issued.

To issue raw SQL queries you could use withStmt, which allows you to do anything you need. However, its API is low-level and you need to parse each row yourself. However, most of your complex queries will have simple results -- some of your entities and maybe a couple of derived columns.

This is where rawSql comes in. Like withStmt, you may issue any SQL query. However, it does all the hard work for you and automatically parses the rows of the result. It may return:

• An Entity, that which selectList returns. All of your entity's fields are automatically parsed.
• A Single a, which is a single, raw column of type a. You may use a Haskell type (such as in your entity definitions), for example Single Text or Single Int, or you may get the raw column value with Single PersistValue.
• A tuple combining any of these (including other tuples). Using tuples allows you to return many entities in one query.

The only difference between issuing SQL queries with rawSql and using other means is that we have an entity selection placeholder, the double question mark ??. It must be used whenever you want to SELECT an Entity from your query. Here's a sample SQL query sampleStmt that may be issued:

SELECT ??, ??
FROM "Person", "Likes", "Object"
WHERE "Person".id = "Likes"."personId"
AND "Object".id = "Likes"."objectId"
AND "Person".name LIKE ?

To use that query, you could say

do results <- rawSql sampleStmt ["%Luke%"]
   forM_ results $
     \( Entity personKey person
      , Entity objectKey object
      ) -> do ...

Note that rawSql knows how to replace the double question marks ?? because of the type of the results.