Module Database.PostgreSQL.PQTypes.SQL.Builder offers a nice monadic DSL for building SQL statements on the fly. Some examples:

>>> :{
>>> | sqlSelect "documents" $ do
>>> |  sqlResult "id"
>>> |  sqlResult "title"
>>> |  sqlResult "mtime"
>>> |  sqlOrderBy "documents.mtime DESC"
>>> |  sqlWhereILike "documents.title" "%pattern%"
>>> :}
SQL " SELECT  id, title, mtime FROM documents WHERE (documents.title ILIKE <\"%pattern%\">)   ORDER BY documents.mtime DESC  "

SQL.Builder supports SELECT as sqlSelect and data manipulation using sqlInsert, sqlInsertSelect, sqlDelete and sqlUpdate.

>>> import Data.Time.Clock
>>> now <- getCurrentTime
>>> :{
>>> | sqlInsert "documents" $ do
>>> |   sqlSet "title" title
>>> |   sqlSet "ctime" now
>>> |   sqlResult "id"
>>> :}
SQL " INSERT INTO documents (title, ctime) VALUES (<\"title\">, <\"2017-02-01 17:56:20.324894547 UTC\">) RETURNING id"

The sqlInsertSelect is particulary interesting as it supports INSERT of values taken from a SELECT clause from same or even different tables.

There is a possibility to do multiple inserts at once. Data given by sqlSetList will be inserted multiple times, data given by sqlSet will be multiplied as many times as needed to cover all inserted rows (it is common to all rows). If you use multiple sqlSetList then lists will be made equal in length by appending DEFAULT as fill element.

>>> :{
>>> | sqlInsert "documents" $ do
>>> |   sqlSet "ctime" now
>>> |   sqlSetList "title" ["title1", "title2", "title3"]
>>> |   sqlResult "id"
>>> :}
SQL " INSERT INTO documents (ctime, title) VALUES (<\"2017-02-01 17:56:20.324894547 UTC\">, <\"title1\">) , (<\"2017-02-01 17:56:20.324894547 UTC\">, <\"title2\">) , (<\"2017-02-01 17:56:20.324894547 UTC\">, <\"title3\">) RETURNING id"

The above will insert 3 new documents.

SQL.Builder provides quite a lot of SQL magic, including ORDER BY as sqlOrderBy, GROUP BY as sqlGroupBy.

>>> :{
>>> | sqlSelect "documents" $ do
>>> |   sqlResult "id"
>>> |   sqlResult "title"
>>> |   sqlResult "mtime"
>>> |   sqlOrderBy "documents.mtime DESC"
>>> |   sqlOrderBy "documents.title"
>>> |   sqlGroupBy "documents.status"
>>> |   sqlJoinOn "users" "documents.user_id = users.id"
>>> |   sqlWhere $ mkSQL "documents.title ILIKE" <?> "%pattern%"
>>> :}
SQL " SELECT  id, title, mtime FROM documents  JOIN  users  ON  documents.user_id = users.id WHERE (documents.title ILIKE <\"%pattern%\">) GROUP BY documents.status  ORDER BY documents.mtime DESC, documents.title  "

Joins are done by sqlJoinOn, sqlLeftJoinOn, sqlRightJoinOn, sqlJoinOn, sqlFullJoinOn. If everything fails use sqlJoin and sqlFrom to set join clause as string. Support for a join grammars as some kind of abstract syntax data type is lacking.

>>> :{
>>> | sqlDelete "mails" $ do
>>> |   sqlWhere "id > 67"
>>> :}
SQL " DELETE FROM mails  WHERE (id > 67) "

>>> :{
>>> | sqlUpdate "document_tags" $ do
>>> |   sqlSet "value" (123 :: Int)
>>> |   sqlWhere "name = 'abc'"
>>> :}
SQL " UPDATE document_tags SET value=<123>  WHERE (name = 'abc') "

Exception returning and kWhyNot are a subsystem for querying why a query did not provide expected results. For example:

let query = sqlUpdate "documents" $ do
  sqlSet "deleted" True
  sqlWhereEq "documents.id" 12345
  sqlWhereEqE DocumentDeleteFlagMustBe "documents.deleted" False
  sqlWhereILikeE DocumentTitleMustContain "documents.title" "%important%"
result <- kRun query

If the result is zero then no document was updated. We would like to know what happened. In query we have three filtering clauses. One is a baseline: the one mentioning documents.id. Baseline clauses define what objects we are talking about. Other clauses are correctness checks and may fail if status of on object is unexpected. Using kWhyNot we can see what is wrong with an object:

problems <- kWhyNot query

Now problems should contain a list of issues with rows that could be possibly be affected by weren't due to correctness clauses. For example it may state:

problems = [[ DocumentDeleteFlagMustBe { documentDeleteFlagMustBe = False
                                       , documentDeleteFlagReallyIs = True
                                       }
            , DocumentTitleMustContain { documentTitleMustContain = "%important%"
                                       , documentTitleReallyIs = "Some contract v2"
                                       }
            ]]

Note: problems is a nested array, for each object we get a list of issues pertaining to that object. If that list is empty, then it means that baseline conditions failed or there is no such object that fullfills all conditions at the same time although there are some that fullfill each one separatelly.

Note: kWhyNot is currently disabled. Use kWhyNot1 instead, which returns a single exception.