README.md

FunSQL.jl

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

Overview

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 presentations at OHDSI DevCon 2023 (slides) and JuliaCon 2021 (slides), explore the Examples, or go straight to the Usage Guide.

Example

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

@funsql begin
    from(person)
    filter(1930 <= year_of_birth <= 1940)
    join(
        from(location).filter(state == "IL").as(location),
        on = location_id == location.location_id)
    left_join(
        from(visit_occurrence).group(person_id).as(visit_group),
        on = person_id == visit_group.person_id)
    select(
        person_id,
        latest_visit_date => visit_group.max(visit_start_date))
end

Generated SQL

SELECT
  "person_2"."person_id",
  "visit_group_1"."max" AS "latest_visit_date"
FROM (
  SELECT
    "person_1"."person_id",
    "person_1"."location_id"
  FROM "person" AS "person_1"
  WHERE
    (1930 <= "person_1"."year_of_birth") AND
    ("person_1"."year_of_birth" <= 1940)
) AS "person_2"
JOIN (
  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")
LEFT JOIN (
  SELECT
    max("visit_occurrence_1"."visit_start_date") AS "max",
    "visit_occurrence_1"."person_id"
  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")