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
![](./example-schema.drawio.svg)
Pipeline Diagram
![](./example-pipeline.drawio.svg)
Julia Code
```julia
@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
```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")
```