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.