Given the recent posts about compile-to-SQL languages, I thought I'd post here a
link to FunSQL.jl which is another interesting point in the design space and
which, I think, has quite a few really useful design decisions.
The library presents an API of combinators for query construction but it's
totally possible to build a concrete syntax around it (in fact I did it for my
port in OCaml). But syntax isn't really this important (it is of course) but the
compositional semantics FunSQL.jl provides:
1. Query combinators can be composed together in any order (if they are
"compatible") unlike SQL where structure SELECT .. FROM .. WHERE .. is rigid:
from users
where is_active
select username
(also `from` being the first combinator makes code completion much powerful).
2. The way FunSQL.jl treats `group by` as just another kind of namespace,
aggregates are then specified as just expressions under this namespace:
from users as u
join (from comments group by user_id) as c on c.user_id = u.id
select
u.username,
c.count() as comment_count,
c.max(created_date) as comment_last_created_date
In the example above the `c` subrelation is grouped by `user_id` but it doesn't
specify any aggregates - they are specified in the `select` below so you have
all selection logic co-located in a single place.
This is a really useful feature, from my point of view, as it's allows you to
construct re-usable query fragments first and then combine them to build
complete quries.
3. In other aspects FunSQL.jl is really close to SQL which I think is a big
plus as well — familiriaty.
