squeal-postgresql-qq

This library provides a Template Haskell quasiquoter parsing SQL as the quoted language and producing corresponding squeal-postgresql expressing. The goal is to provide an easier way to use the squeal-postgresql library, by eliminating (or at least reducing) the need for the user to learn the squeal "DSL" and allowing her to write regular SQL instead.

Stability

I would give this package a 5 out of 10 for stability where 0 is completely unstable and experimental and 10 is maybe like the aeson package.

I think I've got a very large and usable segment of SQL supported but of course there are unsupported features that are kind of important such as common table expressions.

I don't foresee backwards incompatable changes being a problem because, after all, the "interface" is mostly the SQL language, which is stable. Most work will be about supporting new corners of SQL.

In terms of maintenance, I intend to be responsive to any bugs and to keep up to date with the latest dependencies and GHC versions. In other words, this is a maintained package, even if I experience a lull in adding new supported SQL features.

Production usage

I would feel relatively comfortable using this in production. The risk regarding stability/maintenance is pretty low.

If you have queries that are supported, great! They'll continue to be supported. If you have a query that is not supported, you can always fall back to crafting squeal expressions manually. (File an issue! I'll prioritize real-world usage.)

If you have a supported SQL statement that you find you have to modify in a way that makes it unsupported, you can always tell GHC to -ddump-splices and use the quasi-quoter generated squeal as a starting point for your modifications.

How to use this library.

See the haddocks.

Most important features not currently implemented

• Prepared statement parameters
  • See the haddock documentation for how to get haskell values into your sql statements.
• WITH clauses (Common Table Expressions)
• ON CONFLICT clause

Other features is not currently implemented

(Generated by an LLM. Maybe not complete.)

• TABLESAMPLE clause
• ONLY keyword for table inheritance
• WINDOW clause and window functions (OVER)
• INTO clause (SELECT ... INTO ...)
• LIKE with ESCAPE
• IN with a subquery
• OPERATOR() syntax
• Parameter indirection (e.g., $1[i])
• Indirection on parenthesized expressions (e.g., (expr)[i])
• Aggregate FILTER clause
• WITHIN GROUP clause
• DISTINCT in function arguments
• ORDER BY in function arguments
• Function name indirection (e.g., schema.func)
• SETOF type modifier
• BIT and BIT VARYING types
• INTERVAL with qualifiers
• Types with precision/scale (TIMESTAMP, TIME, FLOAT, NUMERIC, etc.)
• Qualified type names (e.g., schema.my_type)
• CURRENT_TIMESTAMP with precision
• Multidimensional arrays with explicit bounds
• ORDER BY USING
• FOR READ ONLY locking clause
• Advanced GROUP BY features (GROUPING SETS, CUBE, ROLLUP)
• Multi-row VALUES clause
• OVERRIDING clause for identity columns
• Column indirection in INSERT target lists
• WHERE CURRENT OF for cursors
• Complex relation expressions in UPDATE or DELETE targets
• Column indirection in UPDATE SET clauses

Supported features.

This is the output from the test suite, which gives a pretty good indication of the supported SQL language features.

queries
  select * from users [✔]
  select * from public.users [✔]
  SELECT * FROM "users" AS "users" [✔]
  select * from users where name = 'bob' [✔]
  select users.name from users [✔]
  select name from users [✔]
  select count(*) from users group by () [✔]
  select name, id from users [✔]
  select id, name from users [✔]
  select users.id, employee_id from users [✔]
  select users.* from users [✔]
  select users.* from other.users [✔]
  select * from users limit 3 [✔]
  select * from users limit inline(lim) [✔]
  select * from users offset inline(off) [✔]
  select * from users offset 1 [✔]
  select users.id, employee_id as emp_id from users [✔]
  select users.id as user_id, employee_id from users [✔]
  select users.id from users left outer join emails on emails.user_id = users.id [✔]
  select users.id, users.name, emails.email from users left outer join emails on emails.user_id = users.id where emails.email = inline("targetEmail") [✔]
  select 'text_val' [✔]
  select 1 [✔]
  select 1 AS num, 'text_val' AS txt [✔]
  group by
    select name from users group by name [✔]
    select employee_id, count(id) from users group by employee_id [✔]
    select employee_id, name, count(id) from users group by employee_id, name [✔]
  common table expressions
    with users_cte as (select * from users) select * from users_cte [✔]
    with users_cte as (select * from users), emails_cte as (select * from emails) select users_cte.*, emails_cte.email from users_cte join emails_cte on users_cte.id = emails_cte.user_id [✔]
inserts
  insert into emails (id, user_id, email) values (1, 'user-1', 'foo@bar') [✔]
  insert into emails (id, user_id, email) values (1, 'user-1', $1) [✔]
  insert into emails (id, user_id, email) values (1, $2, $1) [✔]
  insert into emails (id, user_id, email) values (inline(i), inline(uid), inline_param(e)) [✔]
  default keyword
    insert into emails (id, user_id, email) values (default, 'foo', 'bar') [✔]
    insert into emails (id, user_id, email) values (deFault, 'foo', 'bar') [✔]
    insert into emails (id, user_id, email) values (DEFAULT, 'foo', 'bar') [✔]
  null keyword
    insert into emails (id, user_id, email) values (DEFAULT, 'foo', null) [✔]
    insert into emails (id, user_id, email) values (DEFAULT, 'foo', NULL) [✔]
    insert into emails (id, user_id, email) values (DEFAULT, 'foo', NuLL) [✔]
  insert ... select ...
    insert into emails select id, user_id, email from emails where id = 1 [✔]
    insert into emails select id, user_id, email from emails where id = $1 [✔]
    insert into users_copy select id, name, bio from users where users.id = 'uid1' [✔]
  returning clause
    insert into emails (id, user_id, email) values (1, 'user-1', 'foo@bar') returning id [✔]
    insert into emails (id, user_id, email) values (1, 'user-1', 'foo@bar') returning * [✔]
  with common table expressions
    with new_user (id, name, bio) as (values ('id_new', 'new_name', 'new_bio')) insert into users_copy select * from new_user [✔]
deletes
  delete from users where true [✔]
  delete from emails where id = 1 [✔]
  delete from emails where email = inline(e) [✔]
  delete from users where id = 'some-id' returning id [✔]
  with common table expressions
    with to_delete as (select id from users where name = 'Alice') delete from users where id in (select to_delete.id from to_delete) [✔]
    with to_delete as (select id from users where name = 'Alice') delete from users using to_delete where users.id = to_delete.id [✔]
updates
  update users set name = 'new name' where id = 'some-id' [✔]
  update users set name = 'new name', bio = 'new bio' where id = 'some-id' [✔]
  update users set name = inline(n) where id = 'some-id' [✔]
  update users set name = 'new name' where id = 'some-id' returning id [✔]
  with common table expressions
    with to_update as (select id from users where name = 'Alice') update users set name = 'Alicia' from to_update where users.id = to_update.id [✔]
scalar expressions
  select users.id != 'no-such-user' as neq from users [✔]
  select * from users where users.id <> 'no-such-user' [✔]
  select * from emails where emails.id > 0 [✔]
  select * from emails where emails.id >= 0 [✔]
  select * from emails where emails.id < 10 [✔]
  select * from emails where emails.id <= 10 [✔]
  select emails.id + 1 as plus_one from emails [✔]
  select emails.id - 1 as minus_one from emails [✔]
  select emails.id * 2 as times_two from emails [✔]
  select * from users where users.id = 'a' and users.name = 'b' [✔]
  select * from users where users.id = 'a' or users.name = 'b' [✔]
  select * from users where users.name like 'A%' [✔]
  select * from users where users.name ilike 'a%' [✔]
  select * from users where not (users.name = 'no-one') [✔]
  select -emails.id as neg_id from emails [✔]
  select * from users where users.bio is null [✔]
  select * from users where users.bio is not null [✔]
  function calls
    select coalesce(users.bio, 'no bio') as bio from users [✔]
    select lower(users.name) as lower_name from users [✔]
    select char_length(users.name) as name_len from users [✔]
    select character_length(users.name) as name_len_alias from users [✔]
    select "upper"(users.name) as upper_name from users [✔]
    select now() as current_time [✔]
    select current_date as today [✔]
    haskell variables in expressions [✔]
  select (emails.id + 1) * 2 as calc from emails [✔]
  select * from users where users.name in ('Alice', 'Bob') [✔]
  select * from users where users.name not in ('Alice', 'Bob') [✔]
  select * from emails where emails.id between 0 and 10 [✔]
  select * from emails where emails.id not between 0 and 10 [✔]
  select (e.id :: text) as casted_id from emails as e [✔]
  select * from users for update [✔]
  select * from jsonb_test [✔]
  select * from json_test [✔]
  select distinct name from users [✔]
  select distinct * from users [✔]
  select distinct on (employee_id) employee_id, name from users [✔]
  select distinct on (employee_id, name) employee_id, name, id from users [✔]
  order by
    select * from users order by name [✔]
    select * from users order by name asc [✔]
    select * from users order by name desc [✔]
  having clause
    select employee_id, count(id) from users group by employee_id having count(id) > 1 [✔]