Composability question

[teh]

Hey, this is a a pretty mad idea but also really cool. The only thing that's not great is composability - e.g. adding a WHERE restriction at the end requires copying and pasting the entire query.

Do you have any ideas or tricks for solving that? I've thought about this for a while but can't come up with any clever ideas.

[dylex]

I'm not exactly sure what you mean but have a couple suggestions if either of these applies:

If you want to, say, have two queries that you know at compile time but which share some common text that you don't want to re-type, or otherwise just compose them from strings with ++:

q1 = [pgSQL|SELECT blah FROM foo WHERE a|]
q2 = [pgSQL|SELECT blah FROM foo WHERE b|]

I couldn't think of a great way to fit this into the high-level interface, but you can use the lower level interface instead:

import Database.PostgreSQL.Typed.Query
qbase = "SELECT blah FROM table1 "
q1 = $(makePGQuery simpleQueryFlags (qbase ++ "WHERE a"))
q2 = $(makePGQuery simpleQueryFlags (qbase ++ "WHERE b"))

The (template haskell) limitation here is that qbase has to be defined in a different, imported module. (You can find a perhaps overly complicated example of composing queries dynamically including selecting columns and doing joins here.)

Alternatively, if you have some part of the query, like a WHERE clause, that you don't know at compile time but instead want to generate at runtime, you can use unsafeModifyQuery:

qbase = [pgSQL|SELECT blah FROM foo |]
q1 = unsafeModifyQuery qbase (<> "WHERE a")

However, you need to be careful with this, since the modified query is not type-checked and so may fail badly at runtime if it changes the input or output.

[teh]

That's for your detailed answer! I tried composing string and it works OKish by having all snippets in a separate file.

I think the last option I had was using views. That's punting how the views are defined to a different system but to me that seems to be the most readable solution for now.

Would you want any of this discussion in the README or any other docs? I.e. shall I write it up so it's not lost?

[dylex]

If you're so inclined it would be great to have some module-level haddock documentation for Database.PostgreSQL.Typed.Query module about how to convert constructed string queries into PGQuery via template haskell, but only if you find it to be a useful approach. I'm open to alternatives (possibly by some sort of string-interpolation) but am not exactly sure what they would look like.

[jekor]

I've seen the need for this commonly in web apps, e.g. when using query params from the URL to build a search query. Sometimes the developer doesn't have the option to add a function/view to the database (which seems like the safest solution).

I like the idea of unsafeModifyQuery. I'm assuming pgLiteral is the easiest way to escape any user input that you're adding to the query string?

I think it is definitely worth putting the different approaches to this in the main documentation, with a running example or 2.

[dylex]

I just added a bit of docs to unsafeModifyQuery in b93cdb9, but it could certainly use more. The best thing to use to escape literals in this case is pgSafeLiteral (or pgLiteralRep) from Database.PostgreSQL.Typed.Dynamic, which should be easier to use than pgLiteral.

[szg251]

In some cases, additional restrictions need to be inserted in the middle of a query. For example, if the query has a count(*), that also requires a GROUP BY at the end.

It would be cool, if one could compose queries like this:

myQuery :: Maybe Int -> PGSimpleQuery (Text, Int32)
myQuery ageFilter =
  [pgSQL|
    SELECT name, count(*)
    FROM mytable
    WHERE deleted_at IS NOT NULL
      ${optionalFilter}
    GROUP BY name
  |]
  where
    optionalFilter = 
      case ageFilter of 
        Nothing  -> ""
        Just age -> "AND age >=  " <> pgSafeLiteral age

or even simplifying the use of pgSafeLiteral with a template:

[pgSQLFragment| AND age >= ${age}|]

Does it seem possible? I'm sorry, I could read the code myself yet.

EDIT:
Right after I wrote this comment, I found a rather hacky solution, using Postgresql only:

myQuery :: Maybe Int -> PGSimpleQuery (Text, Int32)
myQuery ageFilter =
  [pgSQL|
    SELECT name, count(*)
    FROM mytable
    WHERE deleted_at IS NOT NULL
      AND (${isFiltered} IS FALSE OR age >= ${filterValue}) -- the right side won't be evaluated if isFiltered is false
    GROUP BY name
  |]
  where
    isFiltered = isJust ageFilter
    filterValue = fromMaybe 0 ageFilter

[dylex]

Addressing only a bit of that, there is a pgSQLFragment-like functionality using [pgSQL|#...|] which just does pgSubstituteLiterals substitutions but nothing else.

As for the main question, the nature of the type safety is such that the query needs to be able to be fully determined at compile time (just as types do). However, you could build something like that by moving the Maybe part of that type signature into a type class, but it certainly won't simplify your code, just better ensure type safety.