In short
A database is a connection to your data, declared once in the workspace and then linked to the projects that need it. You attach a schema to it so Linkr knows how to read its tables — a copy it can override —, and its detail page gives you statistics and column-level exploration. Linkr reads your data: it never writes to it, and a database export never carries any data.
A database belongs to the workspace
This is the part that surprises people at first: you don’t create a database “inside a project”. You declare it once in the workspace warehouse, and every project that needs it links to it.
From the workspace
The database is created, edited, exported and versioned.
This is the only place where the connection itself can be touched. Deleting here is permanent: it removes the files and the cached statistics, and unlinks every project that used it.
Adding a database
From Data Warehouse › Databases, the Add database button opens two options.
Add connection
Point to data that already exists: a file, a folder of Parquet files, or a database server.
New from schema
Create an empty database whose tables are built from an installed schema’s DDL. This is the natural target of an ETL pipeline.
The Import button next to it accepts an exported database ZIP, or installs a published database from the data catalog.
Choosing a connection type
The wizard asks for the type first. Today only one is open: Database, which covers DuckDB, PostgreSQL, MySQL, Apache Doris and SQLite. The FHIR Server tile is visible but disabled, marked “coming soon”. Then comes the configuration, in three tabs: General, Connection and Metadata.
Add a database
Choose a connection type
FHIR Servercoming soon
Connect to a FHIR R4 endpoint
Available engines depend on the mode
In browser mode, only DuckDB and SQLite are offered: they are files, and the browser can read them. PostgreSQL, MySQL and Apache Doris assume a server that opens the network connection on your behalf, so they only appear in server mode. What that server must have installed to reach them is in Database connectors.
Naming and identifying
The General tab asks for three things, only one of which is required.
- Name — required, and unique within the workspace.
- Identifier — a short slug, derived automatically from the name. This is how your SQL queries refer to the database.
- Description — optional, but this is what shows on the card.
The identifier is frozen after creation
It becomes the database’s DuckDB catalog name. Changing it would orphan every script and query that uses it, so the field is read-only as soon as the database exists. Worth getting right the first time — and worth giving each database a distinct one.
Configuring the connection
The Connection tab adapts to the chosen engine.
| Engine | What you provide |
|---|---|
| DuckDB | A .duckdb file, or a folder of Parquet files — two buttons switch the import mode. |
| SQLite | A .sqlite or .db file. |
| PostgreSQL, MySQL | Host, port, database, schema, username, password. |
| Apache Doris | The same fields (port 9030 by default), plus the Arrow Flight SQL port, which speeds up large results. |
This is also where you pick the database’s schema — see the next section. In server mode, a PostgreSQL database also offers Allow Linkr to write into this database: that is what lets you derive a cohort into a new SQL schema there, without ever touching the existing tables. The Metadata tab adds free-form badges and a version number; the Attribution tab, available when editing, carries the author and organization.
Passwords are never returned
In server mode, a password is encrypted before being stored and is only decrypted for as long as it takes to open a connection. The API never returns it, and editing a database’s other fields does not wipe it. It doesn’t leave on export either: see Server configuration.
Files: where do they live?
This is the most concrete difference between the two deployment modes.
Browser mode
Data is copied into the browser, with a ceiling.
A warning appears around 500 MB and the import is blocked beyond 2 GB. On Chrome and Edge, direct access lifts the limit: Linkr reads the file in place without copying anything. The database then has to be reconnected after a page reload, since the browser does not keep the permission.
Server mode
Files stream to the server in chunks, with no ceiling.
A second option appears: Choose on the server, pointing at a file or folder that is already there — nothing is copied. The detail page then shows the absolute path, ready to copy, so the same database can be read from an R or Python script outside Linkr.
Don't close the dialog during an import
While a transfer is running, the dialog refuses to close — the X, Escape and clicking outside are all neutralised. An interrupted import cannot resume: it has to be started again.
Attaching a schema
A database with no schema is still queryable in SQL, but Linkr has no idea what its tables represent. The Schema field on the Connection tab answers that question, offering the schemas installed in the workspace.
The choice is optional — No schema (raw SQL access) is a legitimate option for a database you only want to query. But it governs a good part of the warehouse:
| Without a schema | With a schema |
|---|---|
| Row counts per table | Patients, hospitalizations, unit stays, ages by gender, admission timeline |
| Free-form SQL exploration | Concepts, cohorts, patient data, quality checks |
The database copies the schema’s definition rather than pointing at it, while keeping a record of where the copy came from and its version. A database imported onto an instance where the schema isn’t installed therefore stays readable: Linkr simply shows “Schema (not installed)”. See One schema, several databases.
A database’s mapping
A database’s Mapping tab shows its copy of the mapping, headed by the schema and version it comes from. It is the same editor as on the schema’s page — same sub-tabs, same blocks, same SQL window, see Mapping a schema — with one difference: what you change there applies to this database only.
A relation changed here becomes an override: for this database alone, it replaces the schema’s relation — or adds one. It answers the common case of a site whose model follows OMOP, except for one table stored its own way. There, the SQL window checks and previews on the database directly.
linkr_event_measurementsOverriddenDefined in SQL. The columns it fills:
omoplinkr_event_diagnosescondition_occurrencepatient_id*person_idconcept_id*condition_concept_idstart_datetime*condition_start_datetimevisit_idvisit_occurrence_idend_datetimecondition_end_datetimeomop- Overridden — the badge of a relation that belongs to the database. A warning triangle joins it when the schema has changed that relation since: the override then deserves a check.
- Revert to preset — drops the override; the database reads the schema’s relation again.
- Promote to preset — carries the override into the schema itself, where all its databases benefit from it; this one follows the schema again. You need to be allowed to edit the schema.
- Update from preset — only shows when the schema has changed since the copy. It replaces the copy with the schema’s current version and keeps the overrides; the confirmation says which of them the schema has changed in the meantime.
An override replaces or adds a relation: it cannot remove one of the schema’s.
Changing schema means starting over
Choosing a different schema in Edit makes the copy again and drops every override. To follow the same schema’s changes, use Update from preset.
The detail page
Clicking a card opens the database, on seven tabs. Opened from a project, the same page is read-only: see A project’s databases.
Statistics have not been computed yet
The counts below read "—" until you load them. On large databases this can be slow, so they are only computed on request.
Readme
MIMIC-IV demo (OMOP)
A demo extract of MIMIC-IV converted to OMOP CDM 5.4: 100 patients, their ICU stays, their measurements and their prescriptions.
- Source: MIMIC-IV Clinical Database Demo 2.2 (PhysioNet)
- Dates shifted per patient, as in MIMIC
- Vocabularies: SNOMED, LOINC, RxNorm, ICD-10
About
Connection
/srv/linkr/data/databases/mimic_iv_demo.duckdb412.3 MB
- Overview — the counters, the readme, and side cards: About (author, organization, license, version), Derived from a cohort when the database was cut out of another one, the attached schema, and the connection — status, engine, identifier, file path.
- Statistics — gender distribution, ages and lengths of stay, age distribution by gender, admission timeline, and the row count of every table.
- Schema — a three-pane explorer: the tables with their row counts, the chosen table’s columns with their type and completeness, and the chosen column’s statistics — distinct values, min/max, distribution, most frequent values. Copy SELECT copies a query listing every column. The database has to be connected.
- Mapping — the copy of the schema’s mapping, and its overrides: see A database’s mapping.
- SQL — a console, see Writing SQL against a database.
- Cohorts — the database’s own cohorts, built on it rather than in a project. It is from one of them that you derive a sub-database.
The More menu gathers Readme, License, Versioning and the Export action. The active tab is written into the URL, so a link to a database points at the right tab.
Statistics are computed on demand only
Connecting to a database triggers no counting — on a large warehouse that would be slow and pointless. The counters show ”—” until you click Load statistics. The result is then cached, with the date of the last computation and a Refresh button.
Writing SQL against a database
A database’s SQL tab is a console: you write a query, run it with Run, Ctrl/⌘ + Enter (selection or current line) or Ctrl/⌘ + Shift + Enter (the whole editor), and the result appears below with the number of rows, the duration, and the date and time it ran. Nothing there is saved: to keep queries, use a SQL script collection.
A database’s identifier is how you name it in your queries, but you need it less often than you’d think: a query touching a single database is written with bare table names.
SELECT person_id, year_of_birth
FROM person
WHERE year_of_birth > 1980The prefix only becomes necessary for a query that spans several databases, and only in browser mode — where the database is attached under the name ds_<identifier>. The editor of SQL script collections then has, in its toolbar, a Copy schema reference for SQL button.
SELECT a.person_id
FROM "ds_mimic_iv_raw".person a
JOIN "ds_local_registry".inclusion b USING (person_id)In an ETL pipeline, never write this prefix
ETL scripts name their databases by role — source., target., vocab. — which Linkr rewrites to the real schema at execution time. A script that hardcodes ds_something stops working as soon as the pipeline is exported to another site, where that identifier does not exist. See ETL pipelines.
What Linkr does not do to your data
A database export never carries data
Exporting a database carries its documentation, its schema and its metadata — never its data. This is deliberate: an exported database cannot be the route by which patient data leaves a hospital.
The practical consequence: re-importing an exported database gives you an empty shell, flagged by a banner. You then point it back at the files, through Edit.
In the same spirit, databases are attached read-only. Neither an exploratory query, nor a widget, nor an analysis script can write into a source database. Only an ETL pipeline’s target is opened for writing, and only while it runs.
The other direction stays open: a public database — MIMIC-IV demo, synthetic data — installs from the catalog with its data. Nothing leaves, everything can come in.
See a real database
The demo contains two example databases built from MIMIC-IV, one raw and one converted to OMOP. Open one to browse its statistics and its table explorer.
Maintaining a database
A few actions are only occasionally needed, but worth knowing about.
- Test connection again — a database in error stays in error until it is retested. In server mode, failed databases are retried automatically on load.
- Recreate from schema — replaces the contents with empty tables. Destructive and irreversible.
- Compact — in server mode, on a database Linkr manages: rewrites the file to reclaim the space left by deleted tables. The data doesn’t change, but the operation needs free disk space and nothing must write to the database while it runs.
Going further
- A project’s databases — linking a database to a project, and what the project sees of it.
- Schemas — define how Linkr reads a database’s tables.
- ETL pipelines — transform a source database into a target one.
- Data quality — check what a database contains.
- SQL script collections — store and share queries.