Module SQL2 offers some nice monadic function that build SQL commands on the fly. Some examples:

kRun_ $ sqlSelect "documents" $ do
  sqlResult "id"
  sqlResult "title"
  sqlResult "mtime"
  sqlOrderBy "documents.mtime DESC"
  sqlWhereILike "documents.title" pattern

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

kRun_ $ sqlInsert "documents" $ do
  sqlSet "title" title
  sqlSet "ctime" now
  sqlResult "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.

kRun_ $ sqlInsert "documents" $ do
  sqlSet "ctime" now
  sqlSetList "title" [title1, title2, title3]
  sqlResult "id"

The above will insert 3 new documents.

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

kRun_ $ 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 $ SQL "documents.title ILIKE ?" [toSql pattern]

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.

kRun_ $ sqlDelete "mails" $ do
  sqlWhere "id > 67"

kRun_ $ sqlUpdate "document_tags" $ do
  sqlSet "value" (123 :: Int)
  sqlWhere "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

In 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 are we 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.