sqlalchemy-sql-on-fhir

SQLAlchemy dialect and Apache Superset engine spec for querying FHIR data via the SQL on FHIR $sql-run operation.

Installation

pip install sqlalchemy-sql-on-fhir

Usage

SQLAlchemy

from sqlalchemy import create_engine, text

engine = create_engine("sqlonfhir://localhost:8080/fhir")
with engine.connect() as conn:
    result = conn.execute(text("SELECT * FROM patients LIMIT 10"))
    for row in result:
        print(row)

Apache Superset

After installing the package, restart Superset. The "SQL on FHIR" database type will appear in the database connection dialog. Use a connection string like:

sqlonfhir://your-fhir-server:8080/fhir

Tables in Superset correspond to ViewDefinitions registered on the server.

ViewDefinition metadata is cached per connection. New connections fetch their own metadata using their authentication and tenant headers; call conn.refresh_view_definitions() on a DBAPI connection to reload its views.

Dependency compatibility

The driver targets the current Superset 6.1 dependency requirements: Python 3.10+, SQLAlchemy 1.4, and SQLGlot 28.x. These requirements apply to both standalone and Superset installations. The uv.lock file records the resolved versions for reproducible development environments.

Authentication

If your server is unauthenticated, leave Secure extra empty ({}) — the driver just makes plain requests.

For secured servers, the driver supports three modes, in order of precedence: OAuth2 client-credentials (SMART), static bearer token, and HTTP basic auth.

OAuth2 client-credentials (SMART)

SQL-on-FHIR servers commonly secure access via SMART client-credentials, issuing short-lived JWTs (often 5 minutes) — for example, Pathling's authorization. A static token is impractical in that setting, so the driver fetches and refreshes tokens itself. Provide client_id, client_secret, token_url, and optionally scope. Tokens are fetched on connect, refreshed proactively before expiry, and retried once on a 401.

In Superset → Edit database → Advanced → Security → Secure extra:

{"connect_args": {
  "client_id": "your-client-id",
  "client_secret": "your-client-secret",
  "token_url": "https://auth.example/realms/your-realm/protocol/openid-connect/token",
  "scope": "system/*.rs",
  "scheme": "https"
}}

Pair it with a minimal SQLAlchemy URI: sqlonfhir://fhir.example.com:443/fhir.

The same kwargs work via the SQLAlchemy URL query string (handy for tests, but avoid in production — secrets land in logs):

engine = create_engine(
    "sqlonfhir://fhir.example.com:443/fhir"
    "?client_id=cid&client_secret=csec"
    "&token_url=https://auth.example/realms/r/protocol/openid-connect/token"
    "&scope=system/*.rs"
)

Static bearer token

For a long-lived token (e.g. a personal access token in dev), pass token:

{"connect_args": {"token": "eyJhbGciOi...", "scheme": "https"}}

Basic auth

Username and password go straight into the SQLAlchemy URI:

sqlonfhir://user:password@your-fhir-server:8080/fhir

Standalone DBAPI

from sqlonfhir.dbapi import connect

conn = connect(
    host="fhir.example.com",
    port=443,
    scheme="https",
    client_id="your-client-id",
    client_secret="your-client-secret",
    token_url="https://auth.example/realms/r/protocol/openid-connect/token",
    scope="system/*.rs",
)
cursor = conn.cursor()
cursor.execute("SELECT patient_id, gender FROM patients")
for row in cursor.fetchall():
    print(row)

Testing

Unit tests

uv sync --extra dev --locked
uv run --locked pytest

The default run is offline and skips anything marked integration.

Integration tests

End-to-end checks run the suite against a real Pathling FHIR server in a Docker container. The fixture in tests/integration/conftest.py spins up the container, waits for /fhir/metadata to return 200, and tears it down at session end.

Requires Docker. Run with:

uv run pytest -m integration

By default the image tag is resolved at session start from the upstream aehrc/pathling GitHub release feed — the newest non-prerelease server-v* tag wins. Override knobs (in precedence order):

Variable	Effect
PATHLING_IMAGE	Full image reference; skips upstream lookup entirely.
PATHLING_VERSION	Version only; combined with PATHLING_REPOSITORY.
PATHLING_REPOSITORY	Repository without tag (default ghcr.io/aehrc/pathling).
PATHLING_FALLBACK_VERSION	Used only if the GitHub API call fails.

# Pin a specific server release
PATHLING_VERSION=3.0.0 uv run pytest -m integration

# Test against a private mirror
PATHLING_IMAGE=my.registry/pathling:custom uv run pytest -m integration

Continuous upstream-compatibility check

.github/workflows/pathling-compat.yml runs the integration suite daily against the newest published server-v* release of aehrc/pathling. On scheduled failures it opens (or comments on) a pathling-compat issue so an upstream change that breaks the driver doesn't go unnoticed. Trigger manually with workflow_dispatch and an optional image input to test any specific reference.