SQL Translation Layer

The SQL Translation Layer

At the heart of Medplum's search functionality is a translation engine that converts FHIR search parameters into executable PostgreSQL queries. This process is orchestrated by the internal SqlBuilder class. It deconstructs a FHIR search request, such as GET /Patient?identifier=..., and systematically builds a corresponding SQL statement. Understanding this layer is not just an academic exercise; it's essential for predicting query performance and writing efficient FHIR search operations in a Medplum-backed environment.

Mapping FHIR and Postgres Types

The first step in the translation is mapping FHIR search parameter types to appropriate Postgres data types and query structures. This isn't always a one-to-one mapping, as FHIR defines complex matching semantics that require specialized handling in a relational database.

For example, a FHIR token search, which can match on a system, a code, or both, doesn't map cleanly to a single TEXT column. Instead, Medplum uses dedicated lookup tables to handle these multifaceted queries efficiently. Similarly, reference parameters, which point to other resources, are resolved into foreign key relationships in the database, often requiring JOIN operations.

A date search in FHIR supports prefixes like gt (greater than) or le (less than or equal), which are translated directly into SQL comparison operators (>, <=) applied to TIMESTAMPTZ columns.

FHIR Search Type	Postgres Implementation	Key Details
`token`	`JOIN` on `Identifier_Token` table	Uses a dedicated lookup table to index system and value combinations.
`reference`	`JOIN` on target resource table	Translates resource URLs into foreign key lookups on the target table's UUID.
`date`	`TIMESTAMPTZ` column with operators	FHIR prefixes (`ge`, `lt`) are converted to SQL operators (`>=`, `<`).
`string`	`TEXT` column with `ILIKE`	Handles partial matches, but can be inefficient without proper indexing.
`number`	`NUMERIC` or `INTEGER`	Direct mapping with standard SQL comparison operators.

Constructing the Query

The logic for building the WHERE clause of the SQL query resides in the buildSearchFilterExpression() function within SqlBuilder. This method iterates through the parsed FHIR search parameters and constructs an Expression object for each one. For simple types like date or number, it generates a straightforward comparison expression.

For token and reference searches, the process is more involved. The function generates JOIN clauses to link the primary resource table (e.g., Patient) to the appropriate lookup table. For a token search on Patient.identifier, it joins to a specialized Identifier_Token table. For a reference search like Observation?subject=Patient/123, it constructs a JOIN based on the subject column, resolving the Patient/123 reference to its underlying UUID.

This architecture allows Medplum to leverage the power of PostgreSQL's indexing capabilities. The lookup tables for tokens are heavily indexed, ensuring that searches against common identifiers are extremely fast.

Execution and Performance

The structure of the generated SQL query directly determines its performance. A well-formed FHIR query, translated correctly by SqlBuilder, will result in an efficient index scan. This means the database can use an index to quickly locate the required rows without reading the entire table.

For example, searching for a patient by a medical record number, which is stored as a token, will be fast: GET /Patient?identifier=urn:mrn|12345 This translates to a SQL query with a JOIN on the indexed Identifier_Token lookup table, resulting in an index scan.

Conversely, a poorly constrained or complex string search can lead to a costly full table scan: GET /Patient?name:contains=Smi This will translate to a SQL query using ILIKE '%Smi%'. Unless a specific trgm index is in place, Postgres will have to scan every row in the Patient table to find matches, which can be very slow on large datasets.

-- Efficient query leading to an Index Scan
EXPLAIN ANALYZE SELECT "Patient".*
FROM "Patient"
INNER JOIN "Identifier_Token" ON "Patient"."id" = "Identifier_Token"."resourceId"
WHERE "Identifier_Token"."value" = '12345';

-- Likely Output Snippet:
-- -> Index Scan using identifier_token_value_idx on Identifier_Token ...
-- -> Nested Loop
--   -> Index Scan using patient_pkey on Patient ...

-- Inefficient query leading to a Table Scan
EXPLAIN ANALYZE SELECT "Patient".*
FROM "Patient"
WHERE content->>'name' ILIKE '%Smi%';

-- Likely Output Snippet:
-- -> Parallel Seq Scan on Patient ...
--   Filter: ((content ->> 'name'::text) ~~* '%Smi%'::text)

By understanding the translation from FHIR parameters to SQL JOINs and WHERE clauses, you can anticipate whether a given query will be performant. When designing integrations, prioritizing searches on indexed token and reference fields over broad string searches is a critical optimization strategy.

Quiz Questions 1/5

What is the primary function of the internal SqlBuilder class in Medplum's architecture?

Quiz Questions 2/5

How does Medplum typically translate a FHIR search on a token parameter, such as Patient.identifier, into SQL?

Mastering the link between FHIR search and the underlying SQL execution plan is key to building scalable applications on the Medplum platform.

Advanced FHIR Querying with Medplum and React