Enriching an SQLite database with a TSV join

After creating a SQLite file (see Indexing a BIDS dataset or Creating a SQLite file from Parquet files), you may want to enrich the data table with additional per-subject or per-session metadata stored in a separate TSV or CSV file — for example, a phenotype file or an ID-mapping file. utilities/db_join.py performs that join and writes the result back into the database.

Prerequisites

• An existing .sqlite database (e.g., produced by bids2db.py or parquets2db.py)
• A TSV or CSV file with a column that shares values with a column in the database table

Usage

uv run python utilities/db_join.py DATABASE CSV_FILE -k DB_KEY -c CSV_KEY [options]

Required arguments

Optional arguments

Step-by-step

1. Identify your join columns

Open db.sqlite in SQLite Browser and note the column name you want to join on. Then check your TSV or CSV file for the matching column name — the two do not need to have the same name.

For example, a bids2table-generated database stores the subject identifier in a column called sub (bids2table v2) or ent__sub (bids2table v0.x) (e.g., 01, MOA01), while a phenotype file or participants TSV typically uses participant_id (e.g., sub-01, sub-MOA01).

Handling the sub- prefix in bids2table databases

BIDS subject identifiers in phenotype and mapping files commonly include a sub- prefix that is absent from sub / ent__sub values in a bids2table-generated database. If your values don’t match, strip the prefix from participant_id before running the join:

python -c "
import pandas as pd
df = pd.read_csv('phenotype.tsv', sep='\t')
df['participant_id'] = df['participant_id'].str.removeprefix('sub-')
df.to_csv('phenotype_stripped.tsv', sep='\t', index=False)
"

Then join on phenotype_stripped.tsv using -c participant_id.

2. Choose a join type

For most enrichment workflows where you want to keep all imaging records and simply append metadata columns, left is the safest choice.

3. Run the script

uv run python utilities/db_join.py \
    db.sqlite \
    FILL_IN_THE_BLANK.tsv \
    -k FILL_IN_THE_BLANK_DB_COLUMN \
    -c FILL_IN_THE_BLANK_TSV_COLUMN \
    --join-type left \
    --replace

A realistic example joining a phenotype file into a bids2table database:

uv run python utilities/db_join.py \
    db.sqlite \
    phenotype_stripped.tsv \
    -k sub \
    -c participant_id \
    --join-type left \
    --replace

The script prints progress as it runs.

4. Verify the result

Reload db.sqlite in SQLite Browser. The data table should now contain the original columns plus the new columns from the TSV file.

Notes

• If the output table already exists, you must pass --replace; otherwise the script exits with an error. This is intentional — it prevents accidental overwrites.
• If you specify an --output-table name that differs from the source --table, the original table is left untouched and the joined result is written as a new table alongside it.
• When both the database and TSV have a column with the same name (other than the join key), pandas will suffix them with _x (database) and _y (TSV) to avoid collisions.
• The separator is auto-detected: .tsv and .tab files are read as tab-separated; all other extensions are assumed to be comma-separated. Use -s to override (e.g., -s ";" for semicolon-delimited files).