Bits & Flames
bitsandflames/fyron

FHIR SQL

fyron.fhir.sql queries relational FHIR stores with explicit SQL and returns analysis-ready DataFrames. Use it when your institution exposes FHIR resources through Postgres or a warehouse layer and SQL joins are more appropriate than paginated REST calls.

When To Use It

  • cohort-scale extraction from FHIR warehouse tables,
  • joins across patients, encounters, observations, conditions, and imaging metadata,
  • reproducible SQL queries versioned with the analysis,
  • chunked or parallel SQL jobs for large extracts.

Use FHIR REST for live API access, server write-back, or smaller endpoint-driven workflows.

Required Inputs

InputNeeded forNotes
connection settingsall queriesFHIR_DB_DSN or host/db/user/password variables.
sqlall query methodsSQL statement with optional placeholders.

Optional Inputs

InputUse
paramssafe parameterized queries
renamenormalize output column names
chunksizestream large result sets
column_maprow-wise SQL from a cohort table
parallelsplit repeated row-wise jobs

Minimal Example

python
from fyron import FHIRSQLClient

client = FHIRSQLClient(dsn="postgresql://user:password@localhost/fhir")
patients = client.query_df(
    "select id as patient_id, birth_date from patient limit 100"
)

Clinical Example

python
labs = client.query_df(
    """
    select
      o.subject_id as patient_id,
      o.effective_datetime,
      o.value_quantity_value as hemoglobin,
      o.value_quantity_unit as unit
    from observation o
    where o.code_code = %(loinc)s
      and o.effective_datetime >= %(start)s
    """,
    params={"loinc": "718-7", "start": "2020-01-01"},
)

Function Table

FunctionRequiredOptionalReturns
FHIRSQLClient(...)DSN or DB env varstimeout, loggingclient
query(sql, ...)SQLparamslist of dict rows
query_df(sql, ...)SQLparams, renameDataFrame
query_df_stream(sql, ...)SQLparams, rename, chunksizeiterator of DataFrames
query_df_parallel(jobs, ...)jobsworkers, merge, unique keysDataFrame
query_df_from(df, ...)input table, SQL, column mapchunking, parallelismDataFrame

Common Pitfalls

  • SQL table names and JSON paths vary by FHIR warehouse implementation.
  • Prefer parameterized SQL over string formatting.
  • Keep patient identifiers explicit and avoid accidental cross-patient joins.
  • Store the SQL text with analysis outputs for auditability.
  • FHIR for the overview.
  • FHIR REST for endpoint-driven queries and write-back.
  • Cohort Tables for joining extracted features to outcomes.