Smile CDR supports direct SQL access to the FHIR repository using the HAPI FHIR Query Language (HFQL), which is a SQL-like query syntax.
HFQL aims to provide SQL-like database connectivity to existing FHIR repositories, using standard off the shelf JDBC-compliant database tools.
A simple example query is shown below:
SELECT name.family as family, name.given as given, birthDate, identifier.where(system='http://hl7.org/fhir/sid/us-ssn').first().value as SSN FROM Patient WHERE active = true
See SQL Syntax for details on the HFQL syntax.
HFQL allows for relatively simple querying of data in the FHIR repository using standard database tools. These include database browsers as well as reporting engines.
The HFQL driver is designed to allow users who are familiar with SQL to make queries against the FHIR repository using a familiar language. Because the FHIR data model is a document format and not a table+column format, there is a natural impedance mismatch when using SQL for query, but the driver tries to minimize issues that this causes.
High performance query execution is not a goal of this driver. We are striving to make an engine that is powerful and flexible for mining data directly out of an online FHIR repository, but the system leverages the existing repository database tables and indexes and often has to rely on application-level code to perform analysis and extract data. In other words, this driver is a good candidate for jobs where useful data is a requirement but quick responses are not.
The architecture for the HFQL driver is shown below.
The JDBC driver may be installed in any database tool which supports the Java JDBC API. Only a few parts of the JDBC API have been implemented, so some tools may fail if they try to use a part of the API that has not yet been implemented. This driver has been extensively tested with DBeaver, which is a high quality database browser with an excellent free & open source Community Edition.
Communication between the JDBC client and the server uses a custom FHIR REST operation. This means that the driver should be able to communicate with any Smile CDR server that exposes a REST endpoint and has the HFQL
$hfql-execute operation enabled.
The HFQL driver uses HTTP Basic Auth for authentication. Every query happens under the authority and permissions of the authenticated user. The authorization and consent layers fully apply to HFQL queries, meaning that requests will be blocked if they try to access data that the user does not have appropriate permissions to access. Data may be filtered, redacted, or omitted by consent services and these redactions will also apply to the returned HFQL data.
You may wish to create a dedicated endpoint that exists only to serve this operation as opposed to using an existing FHIR endpoint module. This allows the HFQL endpoint to use a different security model (especially if your existing endpoint uses SMART on FHIR / OIDC as opposed to HTTP Basic Auth), and allows you to specify longer request timeouts on the HFQL endpoint.
Audit and transaction log entries are not generated for HFQL queries.
When HFQL is enabled on the server, a JDBC-compatible driver is available. This can be used to query the FHIR server directly from a JDBC compliant database browser.
This module has been tested with DBeaver, which is a free and excellent database browser. Other JDBC compatible database tools may also work. Note that not all JDBC API methods have been implemented in the driver, so other tools may use methods that have not yet been implemented. Please let us know in the Google Group if you encounter issues or have suggestions.
The JDBC driver can be downloaded from the GitHub Releases site.
To import this driver into your database tool, import the JDBC JAR and use the following settings:
|Username / Password||If provided, the username/password will be added as an HTTP Basic Authorization header on all requests to the server.|
To enable HFQL support on a Smile CDR instance: