FunSQL.jl

Julia library for compositional construction of SQL queries
Popularity
147 Stars
Updated Last
1 Month Ago
Started In
March 2021

FunSQL.jl

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

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.

FunSQL | OHDSI DevCon 2023

FunSQL | JuliaCon 2021

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")