# squeal-postgresql-qq

This library provides a Template Haskell quasiquoter
parsing SQL as the quoted language and producing corresponding
[`squeal-postgresql`](https://hackage.haskell.org/package/squeal-postgresql)
expressing. The goal is to provide an easier way to use the
[`squeal-postgresql`](https://hackage.haskell.org/package/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.

## 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)
* `ON CONFLICT` with a column list conflict target (e.g. `ON CONFLICT (col1, col2) ...`)
* `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 * from users where id = $1 [✔]
  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 users_copy (id, name, bio) values ($1, $2, $3) [✔]
  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 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 = $1 where id = $2 [✔]
  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.id in (select emails.user_id from emails) [✔]
  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 [✔]
```