VarMT - Genetic Variant Database Tool

A Python tool for parsing VCF (Variant Call Format) files and storing genetic variants in a PostgreSQL database with aggregated frequency data across different sample collections.

Overview

VarMTdb processes VCF files and stores genetic variants in a relational database schema designed for efficient querying of variant frequencies across populations and gene-based analysis. The tool automatically creates the database structure and handles the data insertion process.

Features

• Automated Database Setup: Creates PostgreSQL database and tables automatically
• VCF Processing: Parses VCF files using pysam
• Aggregated Storage: Stores variant frequencies across different sample collections
• Conflict Handling: Uses PostgreSQL UPSERT operations to handle duplicate variants
• Performance Optimization: Creates essential database indexes for fast querying
• Web Interface: Streamlit app for querying variants by chromosome, gene, and allele frequency
• Single-Container Deployment: Docker image bundling PostgreSQL and the Streamlit UI

Quick Start (Docker Compose)

The recommended way to run VarMT. Docker Compose runs four containers: db (PostgreSQL), app (the Streamlit UI and the CLI), keycloak (user accounts and login) and keycloak-db (Keycloak's own PostgreSQL).

1. Configure

cp .env.example .env

Set the values in .env (not tracked by Git):

Variable	Purpose
POSTGRES_PASSWORD	Password of the VarMT database
VARMT_PORT	Host port for the UI (default 8502)
KEYCLOAK_DB_PASSWORD	Password of Keycloak's database
KEYCLOAK_ADMIN_USERNAME / KEYCLOAK_ADMIN_PASSWORD	First Keycloak administrator
KEYCLOAK_PORT	Host port for Keycloak (default 8180)
KEYCLOAK_URL	Address browsers open Keycloak at (default http://localhost:8180); keep it in line with KEYCLOAK_PORT
VARMT_CLIENT_SECRET	Shared secret between VarMT and Keycloak (generate with openssl rand -hex 32)
VARMT_URL	Address users open VarMT at (default http://localhost:8502); Keycloak only redirects back here after login
VARMT_COOKIE_SECRET	Signs the VarMT login cookie (generate with openssl rand -hex 32)
SMTP_HOST / SMTP_PORT	Mail server Keycloak sends verification emails through (port default 587)
SMTP_FROM	Sender address of those emails
SMTP_STARTTLS / SMTP_AUTH	Use STARTTLS / log in to the mail server (both default true)
SMTP_USER / SMTP_PASSWORD	Mail server login, if SMTP_AUTH=true

Passwords, the Keycloak administrator, the client and the mail settings are only applied when the volumes and the realm are first created; changing them in .env later has no effect. Change them in the Keycloak admin console instead. VARMT_CLIENT_SECRET and VARMT_URL are also read by the app on every start, so if you change them in .env, change them in the console as well (varmt realm → Clients → varmt: Credentials tab for the secret, Valid redirect URIs for the URL).

2. Build and start

docker compose up -d --build

• The database is stored in the varmt-db named volume, so it survives container restarts and rebuilds
• On first start, PostgreSQL creates the varmt database; the app creates the tables on every start if they are missing
• The database is only reachable from the app container, not from the host
• Keycloak runs in development mode (plain HTTP) and stores its data in the keycloak-db volume. Its admin console is at http://localhost:8180, reachable from this machine only
• On first start, Keycloak creates the varmt realm (VarMT's users and login settings) from docker/keycloak/varmt-realm.json. See Keycloak realm

Stop with docker compose down (add -v only if you want to delete the database volumes, including all Keycloak accounts).

Run docker compose up -d --build again after changing src/, docker/start.sh, docker/helpers.sh, environment.txt or the Dockerfile; changes to .env or docker-compose.yml only need docker compose up -d.

3. Ingest a VCF file

./docker/ingest.sh /path/to/your.vcf

The script copies the VCF into the container and runs the ingestion. Each call adds a new collection — run it once per VCF file.

4. Create indexes (one-time, after first ingestion)

./docker/indexing.sh

Creates the indexes that make queries fast on large datasets. Only needs to be run once; PostgreSQL maintains the indexes automatically for subsequent ingestions.

5. Open the web UI

Browse to http://localhost:8502 to query the variants. If running on a remote server, set up an SSH tunnel:

ssh -L 1455:localhost:8502 user@server

Then open http://localhost:1455 on your local machine. For login to work through a tunnel, VARMT_URL must be the tunnelled address (http://localhost:1455), and Keycloak needs its own tunnel matching KEYCLOAK_URL (e.g. -L 8180:localhost:8180).

Use Log in in the sidebar to sign in or register; see Login.

Every variant search requires a chromosome and an inclusive start/end interval of at most 5,000 bases (end - start + 1). Gene symbols and CSV rsIDs only narrow that interval. CSV rows must all fit within one 5 kb window on the same chromosome; unbounded gene searches and searches spanning multiple windows are rejected.

The shared limit is configured in config/browser_policy.yml:

query:
  max_interval_bases: 5000

Restart Streamlit after changing it. For Docker, run docker compose up -d --build; the database volume is kept.

Adding more collections

Just run ./docker/ingest.sh /path/to/another.vcf again — collections accumulate over time. No need to restart the containers or recreate indexes.

Keycloak realm

docker/keycloak/varmt-realm.json defines the varmt realm, which holds VarMT's users separately from Keycloak's built-in master realm (used only for Keycloak administration):

• Self-registration with the email as username; one account per email
• Password policy: at least 12 characters, not equal to the username or email
• Account lockout after 5 failed logins (from 1 up to 15 minutes, unlocks automatically)
• The varmt client used by the Streamlit app: browser login only, redirects only to VARMT_URL, secret from VARMT_CLIENT_SECRET
• Email verification: registration asks only for email and name; users then click a link sent to their email and set their password there. Emails go through the mail server set by the SMTP_* variables, and the link points to KEYCLOAK_URL, so it must be reachable from where users open the email
• Password reset is off

Testing email locally

Without a real mail server, run Mailpit, which catches every email and shows it in a web inbox. Start it on the Compose network:

docker run -d --name mailpit --network varmt_default -p 127.0.0.1:8025:8025 axllent/mailpit

Set SMTP_HOST=mailpit, SMTP_PORT=1025, SMTP_STARTTLS=false and SMTP_AUTH=false in .env, and read the emails at http://localhost:8025. Remove it with docker rm -f mailpit.

The file is imported only if the realm does not exist yet. Later edits to the file do not change an existing realm: apply them in the admin console, or delete the realm and restart Keycloak (this also deletes its users).

Login

The sidebar of every page shows Log in, or the signed-in email and Log out. Logging in sends the browser to Keycloak's login page (where new users can register) and back to VarMT's /oauth2callback. Logging out ends both the VarMT and the Keycloak session, so the next login asks for the password again. The home page is public; Advanced Search requires login (require_login() in src/utils/auth.py). To block a user, disable them in the Keycloak admin console (Users → user → Enabled).

• Streamlit reads its login settings from .streamlit/secrets.toml. In Docker, docker/start.sh writes this file (via write_streamlit_secrets in docker/helpers.sh) inside the container from .env on every start
• Browsers reach Keycloak at KEYCLOAK_URL, while the app container reaches it at http://keycloak:8080. KC_HOSTNAME and KC_HOSTNAME_BACKCHANNEL_DYNAMIC in docker-compose.yml make Keycloak advertise the public address to browsers and accept the internal one from the app

Manual Installation

Use this only if you don't want to run Docker. Requires a PostgreSQL server you manage yourself.

Environment Setup

Requires Python 3.12, for example with conda:

conda create -n varMTenv python=3.12
conda activate varMTenv
pip install -r environment.txt

Database Authentication

The use of .pgpass file is recommended for security reasons. You can also pass the password directly via -p.

Running

With .pgpass (recommended):

python3 src/vcf2db.py -d database_name -u username -l host -c -t -i -x -v path/to/vcf

With password on command line:

python3 src/vcf2db.py -d database_name -u username -p password -l host -c -t -i -x -v path/to/vcf

Web Interface

Requires config/db_connection.yml with database connection parameters. Supports .pgpass authentication (omit password in config) or direct password specification.

streamlit run src/streamlit_app.py

For login, also create .streamlit/secrets.toml (not tracked by Git) pointing to a running Keycloak with the varmt realm:

[auth]
redirect_uri = "http://localhost:8501/oauth2callback"
cookie_secret = "<random string>"
client_id = "varmt"
client_secret = "<secret of the varmt client>"
server_metadata_url = "http://localhost:8180/realms/varmt/.well-known/openid-configuration"

The redirect_uri must be one of the varmt client's valid redirect URIs in Keycloak.

CLI Reference

These arguments apply to both the Docker workflow (when calling vcf2db.py via docker exec) and manual installation.

Database Connection:

• -d, --database: PostgreSQL database name (required)
• -u, --username: PostgreSQL username (required)
• -p, --password: PostgreSQL password (optional if using .pgpass)
• -l, --host: PostgreSQL host name (required)

Database Operations:

• -c, --create: Delete and recreate database if existing
• -t, --tables: Create the database tables
• -i, --insert: Insert VCF data into tables
• -x, --indexes: Create indexes on tables

Data Input:

• -v, --vcf: Path to VCF file or directory containing VCF files
• -r, --reference_genome: Reference genome version (optional, default=GRCh38)

Step-by-Step Workflow (Manual)

1. Create Database and Tables:

   python3 src/vcf2db.py -d myvariantdb -u postgres -l localhost -c -t -v data/

2. Process VCF Files:

   python3 src/vcf2db.py -d myvariantdb -u postgres -l localhost -i -v data/

3. Create Indexes (after data loading):

   python3 src/vcf2db.py -d myvariantdb -u postgres -l localhost -x

Running Tests

The tests need a PostgreSQL server where they can create and drop a test database. Use a throwaway container (separate from the Compose db), with the environment above active:

docker run --rm -d --name varmt-test-db -p 127.0.0.1:5433:5432 -e POSTGRES_PASSWORD=test postgres:17
TEST_DB_PASSWORD=test PGPORT=5433 python -m pytest tests/
docker stop varmt-test-db

TEST_DB_NAME, TEST_DB_USER and TEST_DB_HOST default to varmt_test, postgres and localhost.

Project Structure

varMT/
├── docker-compose.yml           # db, app, keycloak and keycloak-db services
├── .env.example                 # Template for .env (passwords, Keycloak admin, ports)
├── Dockerfile                   # App container definition
├── environment.txt              # Python dependencies (Docker build and manual install)
├── docker/
│   ├── start.sh                 # App startup: write login settings, create missing tables, run Streamlit
│   ├── helpers.sh               # Shell functions used by start.sh (writes secrets.toml)
│   ├── db_connection.yml        # In-container Streamlit DB config (host: db)
│   ├── ingest.sh                # Wrapper: docker cp VCF + run vcf2db.py
│   ├── indexing.sh              # Wrapper: run vcf2db.py -x for indexing
│   └── keycloak/
│       └── varmt-realm.json     # Keycloak realm imported on first start
├── src/
│   ├── vcf2db.py                # Main CLI application
│   ├── vcf2db_cli.py            # Command line interface
│   ├── streamlit_app.py         # Streamlit web interface entry point
│   ├── pages/
│   │   └── 1_Advanced_Search.py # Advanced gene search page
│   ├── queries/
│   │   └── variant_queries.py   # Centralized SQL queries
│   └── utils/
│       ├── auth.py              # Login/logout sidebar and login requirement
│       ├── db_utils.py          # Database utility functions
│       ├── streamlit_db.py      # DatabaseClient for Streamlit queries
│       ├── vep_utils.py         # VEP annotation parsing utilities
│       ├── csv_parser.py        # CSV/gene mapping file parser
│       └── setup_logging.py     # Logging configuration
├── config/
│   ├── browser_policy.yml       # Shared browser query limit (tracked)
│   └── db_connection.yml        # Local Streamlit DB config (not tracked)
├── tests/                       # Pytest suite
├── res/
│   └── data/
│       └── subset_hg19.vcf      # Sample VCF file
└── docs/
    ├── database_documentation.md
    └── ERschema.png

Troubleshooting

• Port already in use when starting the containers: another service is bound to the host port. Set a different VARMT_PORT in .env (e.g. 8503).
• Streamlit shows "connection failed": ensure both containers are running (docker compose ps) and that POSTGRES_PASSWORD in .env has not changed since the database volume was created (PostgreSQL only reads it on first initialization).

Sample Data

The sample dataset provided (res/data/subset_hg19.vcf) is publicly available (source 1000 Genomes Project).