Where should a SQL query live — Library or something small of its own? — Jun 23, 2025

Clinical reasoning wants to reference SQL wherever it references CQL

From a Measure, to prepopulate a Questionnaire, to pick inclusion criteria in a PlanDefinition, as dynamic values in an ActivityDefinition. Library is the existing container for that logic, so the proposal was to put SQL in a Library.

First complaint: the content sits in base64, so you can't read the query without decoding it.

The tension underneath

Library is a well-modelled space built for named components, manifests and versioning, and you don't evaluate a Library directly — you evaluate a Measure that uses it. That's genuine value in clinical reasoning and a big lift for someone who just wants to run a query.

Getting clinical reasoning to abandon Library is a non-starter; forcing every SQL user through the whole framework isn't reasonable either. Nobody resolved it.

Why the two don't line up — CQL bundles what SQL on FHIR splits

CQL bundles extraction and evaluation together — it maps onto the FHIR model directly, so the parser is embedded in the execution environment, and a Measure's output is a MeasureReport, which is still FHIR and still has to be flattened before an analyst can use it.

SQL on FHIR splits those layers, with the ViewDefinition in between and the SQL engine doing the rest.

That split is also why you can't pass generic SQL around: table and column names come from the ViewDefinitions, so a query means nothing without them.

Gino's OperationDefinition draft

As a plain operation it's much like the Library version, bar serialisation. The payout is kind: query — an OperationDefinition that plugs into _query in the search API, so a complicated cohort filter (this condition in the last six months, hospitalised within a year, on this medication) becomes a normal search parameter.

Today people define exactly this kind of operation and leave every implementer to work out the inside on their own.

Why not raw SQL from a REST API

He'd rather not accept it and evaluate it — stored procedures with mapped parameters were the preference, which reuses a parameter-passing mechanism that already exists.

And since SQL dialects have no registered MIME types, the sketch carried a tuple of a dialect coding plus the expression.

The counter-proposal that drew agreement

A small dedicated query resource: readable, discoverable, able to name the views it depends on and map their names, still pointable-at from an OperationDefinition via relatedArtifact, and still packageable into a Library.

Stuffing it all into extensions was called terrible to actually use.