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.
• ON CONFLICT clause for INSERT statements.

Other features not currently implemented

This is a list of known unsupported SQL features. If you need one of these, please open an issue!

This list was generated by an LLM, and may not be complete.

General Query Structure

• TABLESAMPLE clause
• ONLY keyword for table inheritance
• WINDOW clause and window functions (OVER)
• INTO clause (SELECT ... INTO ...)
• ORDER BY USING
• FOR READ ONLY locking clause
• FOR UPDATE/SHARE OF with qualified table names.
• WHERE CURRENT OF for cursors
• Aliasing a JOIN clause directly (e.g., (SELECT * FROM t1 JOIN t2 ON ...) AS myalias)
• NATURAL JOIN
• USING join qualification (e.g., JOIN ... USING (col))
• LIMIT with comma offset (e.g. LIMIT 10, 20)
• LIMIT ALL
• FETCH clause
• Advanced GROUP BY features (GROUPING SETS, CUBE, ROLLUP)
• Multi-row VALUES clause (e.g. VALUES (1, 'a'), (2, 'b'))

Common Table Expressions (WITH clauses)

• Recursive WITH clauses (WITH RECURSIVE ...)
• MATERIALIZED / NOT MATERIALIZED hints
• Column lists for CTEs are only partially supported (e.g., not for top-level SELECT statements)
• Data-modifying statements (INSERT, UPDATE, DELETE) within a WITH clause

Data Manipulation (INSERT/UPDATE/DELETE)

• INSERT ... DEFAULT VALUES
• INSERT INTO table (columns) SELECT ... (must omit column list)
• OVERRIDING clause for identity columns in INSERT
• Column indirection in INSERT target lists (e.g., INSERT INTO tbl (col[1]) ...)
• Complex relation expressions in UPDATE or DELETE targets
• Column indirection in UPDATE SET clauses (e.g., UPDATE tbl SET col[1] = ...)
• UPDATE with multiple-column SET (e.g. SET (a,b) = (1,2))

Expressions and Functions

• LIKE with ESCAPE
• OPERATOR() syntax
• Parameter indirection (e.g., $1[i])
• Indirection on parenthesized expressions (e.g., (expr)[i])
• Aggregate FILTER clause
• WITHIN GROUP clause for aggregates
• DISTINCT in function arguments
• ORDER BY in function arguments
• Function name indirection (e.g., schema.func)
• Named or colon-syntax function arguments (e.g. my_func(arg_name => 'val'))

Types and Casting

• 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

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 [✔]