In short
A collection is a folder of SQL scripts, arranged in a tree and run against a warehouse database. The editor runs a selection, a script or the whole collection, with a schema browser alongside. Like every Linkr entity, a collection exports, versions and publishes.
Why store your queries
The queries that interrogate a warehouse already exist, in quantity. They are simply scattered: spread across several git repositories, sometimes not versioned at all, and above all rarely pooled. Each team rewrites what another has already written.
A collection gathers them where the data lives. They run against the database concerned, they carry a name, and they follow the same sharing path as everything else — export, git repository, catalog.
This is not a transformation tool
A collection queries. It builds no database and writes nowhere: every database is attached read-only. To feed one database from another, use an ETL pipeline.
The editor
Three areas: the script tree on the left, the editor in the middle, the results below. A schema browser opens on demand.
Scripts are arranged in folders, like files. .md files are accepted too: a collection can therefore mix queries and notes explaining what they are for — and a markdown file renders rather than showing as text.
Running
Four gestures, from the narrowest to the broadest:
| Action | Shortcut | What runs |
|---|---|---|
| Run selection | Cmd/Ctrl + Enter | The selected text |
| Run current line | Cmd/Ctrl + Enter | The cursor’s line, when nothing is selected |
| Run current script | Run button | The whole file |
| Run all scripts | Cmd/Ctrl + Shift + Enter | The entire collection |
The same shortcut covers the selection and the line: if there is a selection it runs, otherwise the cursor’s line does. This is RStudio’s convention, and it makes working on a long query much more direct — you can run one branch of a WITH without splitting the file.
Cmd/Ctrl + S saves. A toolbar button lists every shortcut.
The schema browser
The side panel lists the active database’s tables with their columns, types and nullability, plus a search box. Two buttons save time:
- Copy SELECT — puts a
SELECTlisting every column of the table on the clipboard, rather than retyping them. - Copy schema reference — copies the prefix that names the database in a query.
The second button only appears in browser mode
And rightly so. In browser mode a database is attached under the name ds_<identifier>, which is useful for a query spanning several databases. In server mode that prefix does not exist: scripts use bare table names. The button is hidden rather than offering a reference that would not work. See Databases.
Choosing the database
A collection runs against one database at a time, chosen in the toolbar. The collection remembers a default, which you can change at any point.
This means one collection can serve several databases of the same model: OMOP check scripts written once run against the production database and against a test copy alike, simply by switching the active database.
In server mode a result is capped at 10,000 rows
The query runs entirely on the server, but only the first 10,000 rows come back to the browser. This is a safeguard: a SELECT * on a table of several million rows will not pull the whole warehouse into a tab.
For more than that, aggregate in SQL, or go through a dataset — which is built to carry whole tables.
Sharing a collection
A collection is an entity like any other: ZIP export, git repository, catalog publication, readme and license. Its content is text, so it versions particularly well — a git diff on a query is readable.
It is the natural format for distributing a set of queries: OMOP checks, the computation of a score, a study’s extraction queries. See Entities and sharing.
Going further
- Databases — what the scripts query, and how to name them.
- Data quality — to turn a checking query into a repeatable verification.
- ETL pipelines — for when you need to write, not only read.
- IDE — for an analysis in R or Python rather than SQL.