SQL on FHIR WG Meetings
SQL on FHIR WG Meeting — April 28, 2023
Apr 28, 2023

Topics discussed:

  • Josh took the view syntax and ran it over an NDJSON file that had never been near a database — live in the browser, and from the command line into CSV or SQLite. The variables became plain nested loops: loop over each contact, then over that contact's phone numbers, and emit a row at the innermost loop. The first run was slow, and nearly all of it was recompiling the same FHIRPath expression inside the loop; precompiling it made the whole thing feel reasonable.
  • To turn an expression into SQL you have to know which steps along the path are arrays, which means loading every StructureDefinition for the right FHIR version. Dan floated annotating it in the expression instead — empty brackets after the repeating elements — on the grounds that the author knows anyway and it tells the reader something useful. The counter: it breaks compatibility with off-the-shelf FHIRPath parsers and pushes work onto the user. Nikolai partly defused it by measuring: throw away all the metadata except types and array flags and the whole lookup index zips down to about four kilobytes, so shipping it isn't the burden it sounded like.
  • Pulling the raw ID out of a reference still isn't clean FHIRPath, and a getId function looked like it would break the group's own rule about staying standard. It turns out it doesn't: FHIRPath already lets extra functions be supplied in the evaluation context, much like user-defined functions in a database, so getId can exist without redefining the language.
  • The discipline that emerged: constrain, don't change. Restricting which functions an implementation must support is fine; changing what an expression means is not, because the whole value of FHIRPath is that the same expression means the same thing wherever it appears. exists was argued in on the grounds that with empty, unknown, true and false there's no other way to express it, and or was argued in because it's cheap to implement and saves pushing trivial logic into another SQL layer.
  • Someone asked for namespaced view names before it was too late — "it's usually always too late to introduce namespaces when you understand that you need it". The thinking: views get packaged in sets, the name inside a view stays unqualified, and the implementer decides which database schema a package lands in.