Julia library for compositional construction of SQL queries
124 Stars
Updated Last
1 Year Ago
Started In
March 2021


FunSQL is a Julia library for compositional construction of SQL queries.

Stable Documentation Development Documentation Zulip Chat Open Issues Build Status Code Coverage Status MIT License DOI


Julia programmers sometimes need to interrogate data with the Structured Query Language (SQL). But SQL is notoriously hard to write in a modular fashion.

FunSQL exposes full expressive power of SQL with a compositional semantics. FunSQL allows you to build queries incrementally from small independent fragments. This approach is particularly useful for building applications that programmatically construct SQL queries.

If you want to learn more about FunSQL, read about Two Kinds of SQL Query Builders, watch the JuliaCon 2021 presentation (slides), explore the Examples, or go straight to the Usage Guide.

FunSQL | JuliaCon 2021


When was the last time each person born between 1930 and 1940 and living in Illinois was seen by a healthcare provider?

Database Schema

Pipeline Diagram

Julia Code
From(:person) |>
Where(Fun.between(Get.year_of_birth, 1930, 1940)) |>
Join(From(:location) |> Where(Get.state .== "IL") |> As(:location),
     on = Get.location_id .== Get.location.location_id) |>
LeftJoin(From(:visit_occurrence) |> Group(Get.person_id) |> As(:visit_group),
         on = Get.person_id .== Get.visit_group.person_id) |>
       Get.visit_group |> Agg.max(Get.visit_start_date) |> As(:latest_visit_date))
Generated SQL
  "visit_group_1"."max" AS "latest_visit_date"
  FROM "person" AS "person_1"
  WHERE ("person_1"."year_of_birth" BETWEEN 1930 AND 1940)
) AS "person_2"
  SELECT "location_1"."location_id"
  FROM "location" AS "location_1"
  WHERE ("location_1"."state" = 'IL')
) AS "location_2" ON ("person_2"."location_id" = "location_2"."location_id")
    max("visit_occurrence_1"."visit_start_date") AS "max",
  FROM "visit_occurrence" AS "visit_occurrence_1"
  GROUP BY "visit_occurrence_1"."person_id"
) AS "visit_group_1" ON ("person_2"."person_id" = "visit_group_1"."person_id")

With FunSQL, SQL clauses such as FROM, WHERE, and JOIN are represented by invocations of From, Where, and Join connected together using the pipe (|>) operator. Note the absence of a FunSQL counterpart to nested SELECT clauses; when necessary, FunSQL automatically adds nested subqueries and threads through them column references and aggregate expressions.

Scalar expressions are straightforward: Fun.between and .== is how FunSQL represents SQL functions and operators; Agg.max is a notation for aggregate functions; Get.person_id is a reference to a column; Get.location.person_id refers to a column fenced by As(:location).

Notably, FunSQL notation does not rely on macros or anonymous functions as they hinder modular query construction. FunSQL queries and their intermediate components are first-class objects that could be constructed independently, passed around as values, and freely composed together.