What ?

postgresql-query is a library for more simple query generation for PostgreSQL database. It is not an ORM (but contains some part of). It contains interpolating quasiquote for simple query generation.

Motivation

When you want to perform some complex SQL query using postgresql-simple you writing this query by hands like that:

let q = mconcat
        [ "SELECT u.id, u.name, count(p.id) "
        , "FROM users as u INNER JOIN posts as p "
        , "ON u.id = p.user_id "
        , "WHERE u.name like ? AND u.created BETWEEN ? AND ? "
        , "GROUP BY u.id ORDER BY ? " ]

Well, this is not realy complex. Now what you need to perform the query is to paste parameters instead of this ? signs:

query con q ("%Peyton%", now, yesterday)

Did you see the mistake? We forgot about ordering field. To perform this query we must

query con q ("%Peyton%", now, yesterday, Identifier "...

Oups!. If we use Identifier "u.name" we will get "u.name" in our query which is just not right. Sql syntax assumes "u"."name" for this query. We can not use query parameter to paste optional field here.

Next example is more complex:

let name = Just "%Peyton%" -- assume we getting parameters from query
    minage = Just 10
    maxage = Just 50
    condlist = catMaybes
               [ const " u.name like ? " <$> name
               , const " u.age > ? " <$> minage
               , const " u.age < ? " <$> maxage ]
    paramlist = catMaybes
                [ toField <$> name
                , toField <$> minage
                , toField <$> maxage ]
    cond = if L.null condlist
           then mempty
           else "WHERE " <> (mconcat $ L.intersperse " AND " condlist)
    q = "SELECT u.id, u.name, u.age FROM users AS u " <> cond
query con q paramlist

So much to write and so many chances to make a mistake. What if we could write this like:

let name = Just "%Peyton%" -- assume we getting parameters from query
    minage = Just 10
    maxage = Just 50
    ord = "u.name" :: FN -- <- special type for field names!
    condlist = catMaybes
               [ (\a -> [sqlExp|u.name like #{a}|]) <$> name
               , (\a -> [sqlExp|u.age > #{a}|])     <$> minage
               , (\a -> [sqlExp|u.age < #{a}|])     <$> maxage ]
    cond = if L.null condlist
           then mempty
           else [sqlExp|WHERE ^{mconcat $ L.intersperse " AND " condlist}|]
pqQuery [sqlExp|SELECT u.id, u.name, u.age
                FROM users AS u ^{cond}
                ORDER BY ^{ord}|]

Much better!

sqlExp

Quasiquote sqlExp has two way of interpolation:

  • #{exp} - pastes inside query arbitrary value which type is instance of ToField typeclass. It performs correct strings escaping so it is not the same as stupid string interpolation. Dont worry about sql-injections when using it.

  • ^{exp} - pastes inside query arbitrary value which type has instance of ToSqlBuilder typeclass.

sqlExp returns SqlBuilder which has a Monoid instance and made for effective concatination (bytestring builder works inside).

This quasiquote correctly handles string literals and quoted identifiers. It also removes line and block (even nested) sql comments from resulting query as well as sequences of space characters. You are free to write queries like

WHERE name SIMILAR TO '\^{2,3}' -- line comment #{ololo}

or even

WHERE "#{strange}identifier" SIMILAR TO '#{1,10}' /*nested/*^{block}*/comment*/

sqlExp will remove all comments and will not interpolate inside string literals or quoted identifiers at all.

sqlExpEmbed and sqlExpFile

If you have realy huge hardcore sql template you can

pgQuery $(sqlExpEmbed "sql/foo/bar.sql")

It works just like Yesod's templates. You can use interpolation inside templates like inside sqlExp.

pgQuery $(sqlExpFile "foo/bar")

Is absolutely the same as above. It just prepends sql/ and appends .sql to your string. If you agree to follow naming conventions you are welcome to use sqlExpFile.

Changes

CHANGELOG

3.1.0

Changed

  • Support for inflections-0.3 (fixed buildability). Tested with LTS 8.5

3.0.1

Changed

  • Buildability with ghc-8.0.1

3.0.0

Added

  • Ability to mask some sensitive query arguments in query logs. In case you dont want someone can see your secret data in logs. Added interpolation syntax #?{argumentExpression} for masked parameters Added pgQueryWithMasker and pgExecuteWithMasker for custom log masker
  • MonadPostgres type synonim

Changed

  • Refactoring of SqlBuilder and Entity modules
  • Some low-level intefaces changed

2.3.0

Added

  • derivePgEnum TH generator for enum fields

Changed

  • TH code splitted to modules

2.2.0

Added

  • MonadHReader instance for PgMonadT

2.1.0

Changed

  • use hset-2.0 and hreader-1.0

2.0.0

Changed

  • Entity typeclass now use FN instead of Text. This provides an ability to define dot-separated table names like schemaname.tablename. This changes breaks backward compatibility, so major version is bumped to 2.

  • TH code changed according to changes in Entity typeclass.

1.4.0

Changed

  • eoDeriveClasse renamed to eoDeriveClasses

Added

  • Grammar added to documentation

1.3.1

Changed

  • work with hset-1.0.0

1.3.0

Added

  • dependency from hreader and hset
  • HasPostgres instance for HReaderT transformer

1.2.1

  • remove version constraints for transformers package

1.2.0

Added

  • pgWithTransactionMode and its friends, like pgWithTransactionSerializable added.

1.1.1

Changed

  • pgInsertManyEntities returns count of inserted entities
  • pgDeleteEntity returns True if entity was actually deleted
  • pgUpdateEntity returns True if entity was actually updated

1.1.0

Added

  • deriveEntity - TH derivation Entity instances for you types
  • deriveEverything - TH derivation of Entity, ToRow and FromRow in one shot

Changed

  • Docs improoved

1.0.1

The first usable version

comments powered byDisqus