Database connectors

What each connector needs

DuckDB is Linkr’s query engine. It connects to other databases through extensions — small modules it downloads the first time it needs them, then keeps in the data folder.

Excel files (.xlsx) imported in server mode also go through an extension, excel.

With Docker

Nothing to do: the image ships the extensions and the Doris driver, precisely so that it works without internet access. What remains is opening, from the API container, the ports of the database to reach.

In a manual installation

DuckDB extensions without internet access

On a connected machine, the first connection to a database downloads the extension from extensions.duckdb.org and nobody notices. On a server cut off from the internet — a project space of a health data warehouse, for instance — that first connection fails with a download error.

So install them once, from the repository root, after installing the backend:

npm run duckdb:extensions

They go into the data folder (LINKR_DATA_DIR, under _duckdb_ext), where Linkr looks for them. If the server cannot reach extensions.duckdb.org at all, two options:

• A mirror: if your institution runs a Nexus- or Artifactory-style repository, it can proxy http://extensions.duckdb.org. Give its address:

  npm run duckdb:extensions -- --repository https://nexus.example.org/repository/duckdb-extensions

• A copy: run the command on a connected machine with the same operating system and the same DuckDB version as the server, then copy its _duckdb_ext folder into the server’s data folder.

SQL Server and Oracle

Their community extensions are only installed when asked:

npm run duckdb:extensions -- --community

Without internet access, --community-repository names a mirror of http://community-extensions.duckdb.org, as --repository does for the others. The Oracle extension speaks Oracle’s protocol itself: no Oracle Instant Client to install.

Then give each Linkr user a read-only login; Linkr refuses any other:

• SQL Server: a login that is a member of db_datareader (or holds SELECT grants), and of no role that writes (db_datawriter, db_ddladmin, db_owner, sysadmin). The connection is always encrypted, but the extension version Linkr uses today does not verify the server’s certificate: keep SQL Server on the SPE’s private network.
• Oracle: a login with CREATE SESSION and SELECT on the tables of the warehouse’s schema, that owns no table itself — not the schema’s owner. The form takes the service name (e.g. FREEPDB1) and the schema to read.

Linkr’s own queries are computed by Oracle: counts, statistics, cohorts, patient data, the data catalog and the SQL console are translated into Oracle’s SQL and run by Oracle through its official Python driver (python-oracledb, which needs no Oracle client either), in a read-only transaction. What Oracle would answer differently (string concatenation, among others) is computed by Linkr instead, so an answer never changes. SQL you write in editors, notebooks or pipelines, however, still reads its tables whole: fine on a datamart, slow on a large warehouse.

The database page’s SQL console also offers native Oracle SQL: the query is sent as written, with its optimizer hints or Oracle’s own functions, in a read-only transaction. Linkr refuses links to other databases (@), PL/SQL and the packages that reach outside the database (UTL_*, DBMS_*…).

One index makes the difference. A cohort criterion bound to the stay (“stays with a lactate > 2”) looks, for each stay, for its patient’s events between its dates. With an index on (person_id, start date) of each event table (condition_occurrence, drug_exposure, measurement…), Oracle reads those events directly: on 25,000 Synthea patients, such a criterion drops from 45 s to under 3 s. If the warehouse lacks it, ask the database administrator.

The Arrow Flight SQL driver for Apache Doris

Linkr connects to Doris over the MySQL protocol, which is enough for counts and statistics. Large results — an extraction of several million rows — are much faster over Arrow Flight SQL, a protocol built for bulk data transfer. It needs a Python driver, the doris extra:

pip install -e "apps/api[dev,doris]"

Without it everything works, but slowly for large results, and the database page says so: “Linkr’s server cannot use Arrow Flight SQL (its “doris” extra is not installed)…”. Arrow Flight SQL also needs the FE’s arrow_flight_sql_port (8070 by default) and every BE to be reachable from the Linkr server.

Extras combine: apps/api[dev,postgres,doris].

Going further

• Databases — adding a database and configuring its connection.
• Manual installation — installing the backend before the connectors.
• Configuration — the other settings of a server on a closed network, including package repositories.