from Hacker News

FunSQL.jl – Julia library for compositional construction of SQL queries

by andreypopp on 1/25/22, 3:10 PM with 1 comments

• by andreypopp on 1/25/22, 3:24 PM

  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.