The HAPI FHIR JPA server can optionally be configured to support SQL-like queries against the FHIR repository. This module is intended for analytical queries. It is not optimized for performance, and may take a long time to produce results.
This module uses a proprietary flavour of SQL that is specific to HAPI FHIR. It is similar to the Firely Query Language, although it also has differences.
A simple example query is shown below:
SELECT
name[0].family as family,
name[0].given[0] as given,
birthDate,
identifier.where(system='http://hl7.org/fhir/sid/us-ssn').value as SSN
FROM
Patient
WHERE
active = true
See SQL Syntax for details on this syntax.
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. It can also be built from sources by executing the following command:
mvn -DskipTests -P DIST clean install -pl :hapi-fhir-jpaserver-hfql -am
To import this driver into your database tool, import the JDBC JAR and use the following settings:
| Setting | Description |
|---|---|
| Class Name | ca.uhn.fhir.jpa.fql.jdbc.JdbcDriver |
| URL | jdbc:hapifhirql:[server_base_url] |
| Username | If provided, the username/password will be added as an HTTP Basic Authorization header on all requests to the server. |
| Password |