duckdb-mmcif

Query mmCIF (PDBx) structural-biology files with SQL, right inside DuckDB.

ATTACH a .cif file and every mmCIF category shows up as a normal DuckDB table — with column types inferred from the bundled PDBx/mmCIF dictionary and its supported extensions. No ETL, no schema design, no Python parsing loop: just SQL over macromolecular structure data.

What is mmCIF?

mmCIF (also called PDBx) is the standard file format of the Protein Data Bank — it describes every experimentally determined macromolecular structure: proteins, nucleic acids, ligands, crystals, and all the experimental metadata behind them. A single entry is a set of categories (tables) such as atom_site (one row per atom with 3D coordinates), entity, struct_ref_seq, or reflns (diffraction reflections), linked by keys.

The format is powerful but awkward to analyze: files are large, syntax is quirky (multi-line fields, quoted values, ./? nulls), and joining related categories by hand is tedious. This extension turns that into plain SQL.

Why use it?

• Zero-pipeline analysis — install as a community extension and start querying immediately.
• Typed out of the box — column types come from the mmCIF dictionary type index, so Cartn_x is a DOUBLE and label_seq_id is a BIGINT. . and ? become NULL.
• Dictionary extensions included — IHMCIF, flrCIF, 3DEM and ModelCIF support integrative models, fluorescence/FRET, electron microscopy and AlphaFold DB models automatically, with types, documentation links and relationships.
• Gzip support — RCSB-style *.cif.gz files (for example https://files.rcsb.org/download/1AMB.cif.gz) are auto-detected and decompressed.
• Relationships as data — discover how categories reference each other programmatically with mmcif_relationships(), instead of browsing the mmcif dictionary website.
• Fast — custom cif parser/writer inspired by the RCSB mmcif ccp libraries, with DuckDB's vectorized execution on top.
• Safe by default — attached databases are read-only unless you explicitly opt in to write mode.
• Ligands too — Small molecules or ligand files from the RCSB work the same way as macromolecular entries (see Ligands).

Quick start

Load the extension into a DuckDB shell started with unsigned extensions allowed:

duckdb

(Extension is available since v1.5.5, upgrade DuckDB if necessary.)

INSTALL mmcif FROM community;
LOAD mmcif;

ATTACH '1amb_updated.cif' AS mmcifdb (TYPE mmcif);
USE mmcifdb;

SHOW TABLES;                 -- every category in the file

SELECT count(*) FROM atom_site;
-- 438

SELECT type_symbol, count(*) AS n
FROM atom_site
GROUP BY 1
ORDER BY n DESC;

SELECT Cartn_x, Cartn_y, Cartn_z, type_symbol
FROM atom_site
WHERE type_symbol = 'ZN';

Column types are inferred from the combined dictionary (dict/mmcif_type_index.tsv.gz):

DESCRIBE atom_site;
-- Cartn_x DOUBLE, label_seq_id BIGINT, type_symbol VARCHAR, ...

Dictionary extensions

The extension bundles the current PDBx/mmCIF v5 dictionary together with IHMCIF, flrCIF, 3DEM and ModelCIF. All are available by default: no dictionary selection or downloads are needed when opening a file.

See dictionary extensions for usage, dictionary coverage and ER diagrams from real archive examples.

Table functions

Query a file without attaching it:

-- List categories and their dictionary documentation
SELECT * FROM mmcif_tables('1amb_updated.cif');

-- List columns and their types
SELECT * FROM mmcif_columns('1amb_updated.cif');

-- Find relationships between categories
SELECT * FROM mmcif_relationships('1amb_updated.cif');

-- List data blocks
SELECT * FROM mmcif_blocks('1amb_updated.cif');

-- Read a category
SELECT * FROM mmcif_scan('1amb_updated.cif', 'atom_site');

All five functions also accept /dev/stdin:

cat 1amb_updated.cif | duckdb -c "SELECT * FROM mmcif_scan('/dev/stdin', 'atom_site')"

Relationships in test/data/1amb_updated.cif:

Multiple files

Read the same category from a glob or a list of paths/globs. Plain CIF and gzip files can be mixed:

SELECT filename, id, Cartn_x
FROM mmcif_scan('structures/**/*.cif.gz', 'atom_site');

SELECT * FROM mmcif_scan(['first.cif', 'more/*.cif.gz'], 'atom_site');

When the input matches multiple files, filename identifies each row's source. It is only added when more than one file matches. Missing values become NULL; files without the requested category contribute no rows.

By default, scans include all dictionary columns. For custom categories/items, or to include only columns present in your files, use column_source := 'files':

SELECT * FROM mmcif_scan('structures/*.cif', 'my_category',
                        column_source := 'files');

Each scan combines rows from the same category across all matched files; no join or aggregation is needed.

For long scans, enable the progress bar and choose how many threads to use:

SET enable_progress_bar = true;
SET threads = 6; -- Reduce this to use less memory
SELECT count(*) FROM mmcif_scan('structures/**/*.cif.gz', 'atom_site');

ATTACH and the metadata functions take one file; use mmcif_scan for multiple files. To select a specific data block in every file, add data_block := 'name' (see Multiple data blocks).

Ligands

Besides macromolecular entries, ligand (small molecules) files such as ATP can be queried directly.

ATTACH 'https://files.rcsb.org/ligands/download/ATP.cif' AS liganddb (TYPE mmcif);
USE liganddb;

SELECT id, name, type, formula FROM chem_comp;
-- ATP, "ADENOSINE-5'-TRIPHOSPHATE", NON-POLYMER, "C10 H16 N5 O13 P3"

Multiple data blocks

A file can contain multiple blocks, each beginning with data_name, such as the wwPDB Chemical Component Dictionary (components.cif.gz), which has a separate block for each chemical component. List their names:

SELECT * FROM mmcif_blocks('components.cif.gz'); -- block_name (without data_)

Existing functions and ATTACH use the first block by default. Select another block by name (case-insensitive), without the data_ prefix. For example, select data_ATP from the Chemical Component Dictionary:

SELECT * FROM mmcif_scan('components.cif.gz', 'chem_comp', data_block := 'ATP');
SELECT * FROM mmcif_tables('components.cif.gz', data_block := 'ATP');
SELECT * FROM mmcif_columns('components.cif.gz', data_block := 'ATP');
SELECT * FROM mmcif_relationships('components.cif.gz', data_block := 'ATP');
ATTACH 'components.cif.gz' AS atpdb (TYPE mmcif, DATA_BLOCK 'ATP');

Read-only by default

Attached mmcif databases are read-only; data definition and mutation statements are rejected:

CREATE TABLE foo(i int);      -- Binder Error: mmcif databases are read-only - cannot CREATE TABLE
DELETE FROM atom_site;        -- Binder Error: mmcif databases are read-only - cannot DELETE

Write mode

Pass READ_WRITE TRUE to ATTACH to edit the file with SQL. INSERT, UPDATE, and DELETE mutate the parsed .cif data in memory (row_id is the physical row index of the category table); the file is written back on COMMIT, CHECKPOINT, and DETACH. ROLLBACK re-parses the file from disk. Data definition statements (CREATE/DROP/ALTER) stay disabled.

ATTACH '1amb_updated.cif' AS wdb (TYPE mmcif, READ_WRITE TRUE);
USE wdb;

BEGIN;
INSERT INTO atom_site (label_atom_id, Cartn_x, type_symbol) VALUES ('O1', 3.5, 'O');
UPDATE atom_site SET type_symbol='ZZ' WHERE label_atom_id='O1';
DELETE FROM atom_site WHERE label_atom_id='O1';
COMMIT;   -- writes the mutated tables back to the attached file

Note

The COMMIT will overwrite the file you ATTACH-ed. Make a copy if you do not want to overwrite the original.

Examples

Ready-to-run example scripts live in docs/examples/:

• keep_chain_A.sql — filters an mmCIF file down to a single auth chain (chain A of 3PLZ, downloaded from https://files.rcsb.org/download/3PLZ.cif.gz), deleting rows in every dependent category that do not belong to that chain. Uses mmcif_relationships() to work out which tables reference chains.
• keep_chain_D2A.sql — keeps only chain D of 3PLZ and renames it to chain A (in both the auth and label chain-id namespaces), producing a plain chain-A file; a copy 3PLZ_D2A.cif.gz is edited so the original is never touched.
• spatial_atoms.sql — 3D geometry analysis of atom_site coordinates with the spatial extension: binding-pocket residues around the 3PLZ inhibitor, chain–chain interface contacts, hydration shell, bounding box, rigid-body transforms, and Cα trace export.
• metadata_multiple.sql — joins category scans by filename to extract resolution and deposition date across a collection.
• metadata.sql — structure metadata as SQL (entry id, resolution, experimental method, software, label/auth chain mapping, residue counts, UniProt accession).
• secondary_structure.sql — helix/sheet residue counts and ratios from struct_conf / struct_sheet_range.
• confidence_filter.sql — AlphaFold pLDDT (B-iso) confidence counting and write-mode residue filtering.
• uniprot_mapping.sql — UniProt chain-mapping extraction and injection via struct_ref/struct_ref_seq for user authored entries or the _pdbx_sifts_unp_segments for sifts computed entries.

Inspiration

This extension was inspired by

• https://github.com/DeepRank/pdb2sql - Fast and versatile biomolecular structure PDB file parser using SQL queries - made by co-worker
• https://github.com/project-gemmi/gemmi - macromolecular crystallography library and utilities - lossy round trip via its Structure class read/write

Contributing

See CONTRIBUTING.md for build and test instructions.