FunSQL is a Julia library for compositional construction of SQL queries.
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.
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))
endGenerated 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")