pg-store-0.5.0: Simple storage interface to PostgreSQL

Copyright(c) Ole Krüger 2016
MaintainerOle Krüger <>
Safe HaskellNone




All of the quasi quoters in this module accept the same language. It is almost identical to the language that a PostgreSQL server accepts. Certain operators have been reused in order to achieve a nice integration with the features of this library.

Each quasi quoter uses QueryGenerator as an intermediate representation.


Other query generators can be embedded using the $(haskell code) construct.

genCatSound :: QueryGenerator a
genCatSound = Code "'meow'"

listCats :: Query String
listCats =
    [pgQuery| SELECT name
              FROM animals
              WHERE sound = $(genCatSound) |]

We inline the genCatSound generator which produces the SQL code 'meow'. As a result we have a query with this statement:

FROM animals
WHERE sound = 'meow'

It is also possible to produce QueryGenerators with the pgQueryGen quasi quoter.

genCatSound :: QueryGenerator a
genCatSound = [pgQueryGen| 'meow' |]

pgQueryGen supports the same operations as pgQuery.


Everything that has an instance of Entity can also be used in these quasi quoters. So far, one can only utilize them in the form of named expressions.

The $ operator will cause the value of the following named expression to be used in the query. Any use of $name is just a short cut for $(embedEntity name).

listPeople :: Int -> Query String
listPeople minimumAge =
    [pgQuery| SELECT name
              FROM people
              WHERE age > $minimumAge |]

This query will list the names of people above a given age.


The TableEntity type class lets you associate a table name and the name of its column with a data type.

Given a table schema like the following:

    first  INTEGER NOT NULL,

We produce Haskell code like this:

data MyTable = MyTable {
    first  :: Int,
    second :: String
} deriving (Show, Eq, Ord, Generic)

instance Entity MyTable

instance TableEntity MyTable

Alternatively we can implement Entity and TableEntity ourselves. In this case it is not needed.

We utilize these type classes in the following way:

listMyTable :: Query MyTable
listMyTable =
    [pgQuery| SELECT #MyTable
              FROM @MyTable |]

We expand the absolute column names using #MyTable and the table name using @MyTable. This results in the following SQL:

SELECT MyTable.first, MyTable.second
FROM MyTable

Aliasing the table name is also possible:

listMyTable :: Query MyTable
listMyTable =
    [pgQuery| SELECT #MyTable(t)
              FROM @MyTable t |]

The alias is included in the resulting SQL:

SELECT t.first, t.second
FROM MyTable t

Preparable queries

When building preparable queries, one uses the PrepQuery type to mark the query as preparable.

We utilize the shortcut $n (where 0 <= n <= 9) to integrate parameters into the query.

In the following example, we turn listPeople :: Int -> Query String into a preparable query.

listPeople :: PrepQuery '[Int] String
listPeople =
    [pgQuery| SELECT name
              FROM people
              WHERE age > $0 |]

listPeople is now a query which takes 1 parameter.

Before we can utilize listPeople, we have to prepare it once.

runErrand db (prepare listPeople)

Now that everything is set up, it is possible to execute the prepared query.

runErrand db (query listPeople 25)


Quasi quoters

pgQueryGen :: QuasiQuoter Source #

Generate a QueryGenerator expression.

See Database.PostgreSQL.Store.Query.TH for detailed description of the language accepted by this quasi quoter.

pgQuery :: QuasiQuoter Source #

Generate a Query. This utilizes an intermediate query generator of type QueryGenerator ().

See Database.PostgreSQL.Store.Query.TH for detailed description of the language accepted by this quasi quoter.

pgPrepQuery :: QuasiQuoter Source #

Generate a PrepQuery. The intermediate query generator has type QueryGenerator (Tuple ts) where ts has kind [Type]. ts represents the types of the parameters to this prepared query.

It is highly recommended that supply a type signature, if you give the resulting expression a name, to avoid ambiguity.

q :: PrepQuery '[Int, String] User
q = [pgPrepQuery| SELECT #User(u) FROM @User u WHERE age < $(param0) AND name LIKE $(param1) |]

See Database.PostgreSQL.Store.Query.TH for detailed description of the language accepted by this quasi quoter.