openchemlib-sqlite

SQLite-backed molecular search using OCL (openchemlib-js). Adds substructure, exact, tautomer-insensitive, and similarity search on top of an existing molecules table that you own.

Requirements

• Node.js ≥ 22.5 (uses the built-in node:sqlite module)
• openchemlib peer dependency ≥ 9.20.1

Installation

npm install openchemlib-sqlite openchemlib

How it works

openchemlib-sqlite does not create or own a molecules table. It works alongside an existing table that contains at minimum:

• a primary key column (default: id)
• an id_code column holding the OCL idCode string (default column name: id_code)

Nothing else: the stereo- and tautomer-insensitive keys are this package's business, not yours — it computes and stores them itself (see Structure hashes).

migrate() creates an ocl_ss_index table storing the 512-bit fingerprint for each indexed entry, referencing the entries table by its primary key, the two structure-hash tables, plus an ocl_ss_schema table recording the schema version. Call it on every startup: it applies whatever a database is missing and upgrades one written by an older release in place — see Upgrading.

Setup

import { DatabaseSync } from 'node:sqlite';
import * as OCL from 'openchemlib';
import { MoleculesDBSQLite } from 'openchemlib-sqlite';

const db = new DatabaseSync('molecules.db');

// Your molecules table (already exists, or create it here):
db.exec(`
  CREATE TABLE IF NOT EXISTS molecules (
    id      INTEGER PRIMARY KEY,
    id_code TEXT NOT NULL UNIQUE
  )
`);

// Point the library at it:
const molDB = new MoleculesDBSQLite(db, OCL, {
  entriesTable: 'molecules',
});
molDB.migrate(); // creates or upgrades ocl_ss_index (idempotent)

MoleculesDBConfig options:

| Option | Default | Description | | ---------------------- | ------------ | ------------------------------------------------------------------------------------------------------------ | | entriesTable | (required) | Name of the existing molecules table | | pkColumn | 'id' | Primary key column name | | idCodeColumn | 'id_code' | Column holding the OCL idCode | | mwColumn | null | Column holding the molecular weight (REAL); enables automatic mass-difference sorting in substructure search | | maxTautomers | 5000 | Ceiling on tautomer enumeration; recorded in the database, and changing it rebuilds the tautomer hashes | | trustMwColumn | false | Promise that mwColumn holds real molecular weights, so the prescreen may seek past entries too light to match | | batchSize | 1024 | Candidates verified per call; measured 32.0 µs each at 64, 10.7 µs at 256, 8.2 µs at 1024 |

Tuning SQLite for a large database

The library is handed a connection it does not own, so it sets no pragmas. Two are worth setting yourself once the index is large.

const db = new DatabaseSync('molecules.db');
// Read index pages straight out of the page cache instead of copying them into
// SQLite's own. Measured on a 2 M-entry prescreen: 124 ms -> 64 ms. Node's
// build caps the value at 2 GB, and asking for more is not an error.
db.exec('PRAGMA mmap_size = 2147483648');
// node:sqlite ships SQLite's stock 2 MB page cache. 128 MB (the value is in
// KiB, negative) is a better fit for a scan-heavy workload.
db.exec('PRAGMA cache_size = -131072');

A prescreen reads the ocl_ss_index covering index end to end, so it is paging work more than it is CPU work, and both pragmas address exactly that. Neither changes any result.

Inserting molecules

Insert into your own table first, then index the molecule via molDB.insert(entryId, molecule).

Parsing molecules — auto-detect format

OCL.Molecule.fromText(text) detects the format automatically:

• string containing V2000 or V3000 → parsed as molfile
• otherwise tries SMILES first, then idCode

const mol = OCL.Molecule.fromText(unknownFormatString);
if (!mol) throw new Error(`Could not parse: ${unknownFormatString}`);

Full insert example

const mol = OCL.Molecule.fromText('Cn1c(=O)c2c(ncn2C)n(C)c1=O'); // auto-detects SMILES
if (!mol) throw new Error('Could not parse molecule');

const idCode = mol.getIDCode();

const { lastInsertRowid } = db
  .prepare('INSERT INTO molecules (id_code) VALUES (?)')
  .run(idCode);

// Index the molecule — pass the Molecule instance or an idCode string
molDB.insert(Number(lastInsertRowid), mol);

Passing a Molecule instance to insert() avoids a redundant re-parse. Passing an idCode string is also valid:

molDB.insert(Number(lastInsertRowid), idCode);

Giving insert() what you already computed

A caller that stores its own fingerprints has already paid for the expensive part, and insert() takes it rather than building it again:

import { getIndex } from 'openchemlib-search-wasm';

molDB.insert(entryId, idCode, { index: getIndex(idCode), mw: 194.19 });

With both given nothing here reads the molecule, so insert() does no chemistry at all. Measured over real idcodes: 1350 µs an entry building the fingerprint, 88 µs writing one already in hand — at 150 million entries, 56 hours against under four. index is the 512-bit FragFp in whichever width you hold it, 16 words of 32 bits or 8 of 64.

A mw must be the value mwColumn holds when one is configured, or the index's clustered order stops matching what a bulk path would have written.

Keeping the index in its own database

The index does not have to live beside the entries it indexes. Open the index file, attach the entries file, and name the table through the attachment:

const index = new DatabaseSync('index.sqlite');
index.exec(`ATTACH DATABASE 'entries.sqlite' AS mol`);

const molDB = new MoleculesDBSQLite(index, OCL, {
  entriesTable: 'mol.molecules',
  mwColumn: 'mw',
});
molDB.migrate(); // every ocl_* table is created in index.sqlite

Everything works across the attachment — insert(), every search mode, and backfillHashes(). The entries database is never written to, so it can be a read-only replica, and the index can be deleted and rebuilt, or built on another machine and copied in, without touching it.

The one difference is the foreign key. SQLite has no syntax for a qualified parent table — REFERENCES mol.molecules(id) is a parse error — and a foreign key may not span databases at all, so a qualified entriesTable builds the same tables without the constraint. Only the constraint is dropped, never a column, so the same queries and the same migrations run against a database built either way. What is lost is SQLite refusing to index an entry that does not exist, and refusing to delete an entry that is still indexed.

Searching

All search modes return a SearchResponse with results, total, and optional partial / screened fields. Each result contains { entryId, idCode } — use entryId to look up additional data in your own table.

The query can be a string (parsed with options.format) or a Molecule instance (format option is ignored). The library sets the fragment flag automatically: false for exact / exactNoStereo / similarity, true for substructure. If the flag needs to change on a passed-in instance, a compact copy is made so the original is never mutated.

Exact match

const { results } = molDB.search('Cn1c(=O)c2c(ncn2C)n(C)c1=O', {
  mode: 'exact',
  format: 'smiles',
});

// Passing a Molecule instance directly:
const { results } = molDB.search(
  OCL.Molecule.fromSmiles('Cn1c(=O)c2c(ncn2C)n(C)c1=O'),
  {
    mode: 'exact',
  },
);

Exact match ignoring stereocenters

Requires the hashes to have been built — see Structure hashes.

const { results } = molDB.search('NC(C)C(=O)O', {
  mode: 'exactNoStereo',
  format: 'smiles',
});
// returns both L-alanine and D-alanine

Exact match ignoring stereocenters and tautomerism

Requires the hashes to have been built — see Structure hashes.

const { results } = molDB.search('CC(=O)CC(=O)C', {
  mode: 'exactNoStereoTautomer',
  format: 'smiles',
});
// finds pentane-2,4-dione whether it was stored as the keto or the enol form

A compound keys the same whether it was drawn as the keto or the enol form and whether or not its stereo centres were assigned, so this is the mode to use when "the same molecule" means the same constitution rather than the same drawing.

An entry whose hash could not be computed — see the cap below — has NULL stored and is never returned by this mode. So is a query OCL cannot hash.

Substructure search

const { results, screened, partial } = molDB.search('c1ccccc1', {
  mode: 'substructure',
  format: 'smiles',
  timeoutMs: 10000,
});

A 512-bit fingerprint prefilter (bitwise AND) discards non-candidates before running the full OCL substructure check.

Empty query optimization — passing a molecule with no atoms (e.g. new OCL.Molecule(0, 0)) skips the fingerprint prefilter entirely and returns every indexed entry, because an empty fragment matches everything.

Substructure search sorted by mass difference

When mwColumn is configured, substructure results are automatically ranked by ascending |queryMw − resultMw|. A molecule whose mass equals the query mass (an exact structural match) therefore appears first, with no extra option required:

// Schema must include a molecular-weight column, e.g.:
//   mw REAL NOT NULL
// Construct MoleculesDBSQLite with mwColumn: 'mw' to enable automatic sorting.

const { results } = molDB.search('c1ccccc1', {
  mode: 'substructure',
  format: 'smiles',
});
// results[0] is the molecule whose mass is closest to benzene's MW (~78 Da).
// Each result carries a .mw field with the value from the database.

The molecular weight of the query is computed with fragment = false on a temporary copy so the original Molecule instance is never mutated.

Similarity search (Tanimoto)

const { results } = molDB.search('Cn1c(=O)c2c(ncn2C)n(C)c1=O', {
  mode: 'similarity',
  format: 'smiles',
  similarityThreshold: 0.4,
});
// results sorted by descending similarity; each entry has a .similarity field

Pagination

const { results, total } = molDB.search(query, {
  mode: 'substructure',
  format: 'smiles',
  limit: 50,
  from: 0,
});

Restricting a search to candidates

A scan's cost is dominated by parsing and matching each candidate molecule, so when the caller already knows which entries are relevant — from an attribute filter, an earlier query, anything expressible in SQL — hand that over as a subquery instead of filtering the results afterwards, which pays for the full scan first:

const { results, total } = await molDB.search('c1ccccc1', {
  mode: 'substructure',
  format: 'smiles',
  candidates: {
    sql: 'SELECT id AS entry_id FROM ligands WHERE name LIKE :name',
    params: { name: '%acetate%' },
  },
});

What you stop paying for is the candidates that are never verified, so the gain is proportional and grows with the table size. Measured on 50 000 CCD ligands (8 cores), restricting a phenazine scan to the 9 232 entries matching that filter: 199 ms → 50 ms.

sql must select exactly one column, named entry_id, and params must use named parameters (:name) since the prescreen binds its own anonymous ones. Every mode honours it (substructure, similarity, exact, exactNoStereo). Because the prescreen runs once per search, so does the subquery — however many verifier threads are running.

How a substructure search runs

A substructure search is two steps, and they cost very different amounts:

| step | what it does | share of the time | | --------- | ------------------------------------------------------------------------------------------- | ----------------- | | prescreen | one SQL scan of ocl_ss_index, keeping rows whose fingerprint is a superset of the query's | ~3% | | verify | parse each surviving candidate and run the graph match | ~97% |

So the prescreen is left alone: a single query, on the calling thread's connection, streamed. Only the verification is spread over poolSize threads, which receive the fragment once and then answer batches of idCodes with match / no-match. They hold no database connection.

Two properties fall out of that:

• It self-balances. Batches go to whichever thread is free, so the split never depends on guessing how candidates are distributed. On 50 000 CCD ligands a full phenazine scan goes 710 ms → 199 ms (1 → 8 threads).
• Concurrent searches share the pool. The verifiers are stateless and cache each fragment they see, so several searches interleave on the same threads instead of each monopolising them.

Why the index is ordered by molecular weight

ocl_ss_index is WITHOUT ROWID with primary key (mw, entry_id), so the table is physically stored lightest-first. Nothing ever has to sort it: scanning it is already the right order, and the prescreen is a genuine row-by-row cursor rather than a materialised result set. Two things follow.

maxResults really stops the scan. It is not a slice of a finished result: the cursor is abandoned mid-table, so the candidates past it are never read, let alone parsed. A benzene scan of 50 000 ligands whose prefilter admits 36 801 candidates reads only ~1 400 of them and returns in 16 ms instead of 787 ms.

What survives an early stop is the smallest superstructures — the matches closest to the query — rather than an arbitrary insertion-order subset.

A match cannot be lighter than the fragment, and since the table is physically ordered by weight, the prescreen can seek past every entry too light to be a superstructure rather than reading and rejecting them. It is the only predicate in the prescreen SQLite can seek on; the fingerprint test is a bitmask and has to be evaluated row by row.

The floor is a fragment's own getMolecularFormula().relativeWeight, which for a fragment counts only heavy atoms — benzene reads C6, 72.07, not C6H6, 78.11 — so it is sound whatever hydrogens the match carries. It is dropped entirely in three cases, each of which would otherwise lose real matches:

• the fragment carries any query feature: an atom list or a wildcard lets an atom match a lighter element than the formula assumed, and an exclude group puts atoms in the formula that a match must not have;
• any entry's stored weight is 0, which insert() also uses for "unknown";
• a mwColumn is configured and trustMwColumn is not set, because that column is yours and may hold a sort key rather than a weight.

This is why candidates uses +s.entry_id IN (…). The unary + marks the term unusable by an index, which keeps ocl_ss_index as the driving table. Without it SQLite drives the scan off the subquery — the smaller side, and one with no statistics — which throws the physical order away and needs a temp b-tree to rebuild it, materialising every candidate before the first row comes out. Forcing the clustered scan keeps a restricted search streaming and lightest-first exactly like an unrestricted one (24 ms vs 70 ms on the benzene scan above).

Folding the plane index

molDB.foldPlanes() transposes fingerprints into the plane index, and nothing calls it for you. Until it runs, the index is empty and every search takes the column scan exactly as before, so a database that never folds simply never gets faster.

It is manual for the same reason backfillHashes() is: at ~38 µs an entry a first fold of 150 M takes about 95 minutes. It is resumable, and planeStatus().pending is the number to watch — entries inserted since the last fold, which every search still screens the slower way.

let result;
do {
  result = molDB.foldPlanes({ maxChunks: 1 });
} while (result.pending);

Run it beside the service, not inside it

node:sqlite is synchronous, so a fold blocks whichever thread owns the connection — there is no background for it to run in. Run it in a separate process or worker thread, with its own connection to the same file: WAL allows one writer alongside readers, and nothing is shared but the file.

A fold is a writer, so it competes for the write lock with whatever is inserting. Two things keep it out of the way, and both matter if the database is serving traffic:

• Its transactions are short. Writing a chunk of 2^20 entries used to be one transaction — seconds on the lock, and tens of megabytes. It is now committed in pieces of 10 000 slots and 32 plane blobs.
• pauseMs leaves gaps between them, so the service's own writes land in between. Start at pauseMs: 50 for a fold sharing a live database.

molDB.foldPlanes({ maxChunks: 1, pauseMs: 50 });

Set busy_timeout on both connections (see the tuning section above) so neither side gives up when the other holds the lock.

An interrupted fold cannot corrupt an answer

A chunk's planes are written first and published second: a search takes its chunk list from the segments, so a chunk whose planes exist but whose segment has not been extended is invisible and cannot answer. The next fold writes over it.

This is why visibility is not read from ocl_ss_plane directly. It would make a half-written chunk look complete, and a search would return false negatives without any sign of it — a missing plane row legitimately means "no entry in this chunk sets this bit". For the same reason the tail rows a chunk covers are cleared in that publishing transaction, never before it, so an interruption cannot lose an entry from both the tail and the index.

Structure hashes

exactNoStereo and exactNoStereoTautomer match on OpenChemLib's own 64-bit structure hashes — CanonizerUtil.getNoStereoHash and getNoStereoTautomerHash. You never compute or store these: this package owns both, in two tables of its own. migrate() creates them but leaves them empty, because filling them is a long-running job you start yourself:

molDB.migrate(); // instant: creates the two hash tables

// Off the startup path — this is minutes, not milliseconds.
const result = await molDB.backfillHashes({
  onProgress: (progress) => logger.info(progress, 'hash backfill'),
});
// { passes: [{ kind: 'noStereo', … }, { kind: 'noStereoTautomer', … }], … }

Until a pass has run, its mode simply returns nothing. Nothing else breaks.

The cheap hash is computed first, on purpose

The two hashes are nothing alike in cost. Measured over 3000 real molecules:

| | mean | p50 | p99 | max | | --- | --- | --- | --- | --- | | no-stereo | 74 µs | 50 µs | 359 µs | 18 ms | | no-stereo tautomer | 22 ms | 123 µs | 910 ms | 3.6 s |

Roughly 300× apart, so the backfill runs them as two passes and finishes the cheap one first: exactNoStereo becomes completely searchable in well under a minute on a corpus where exactNoStereoTautomer is still hours away. Doing them together would leave both modes half-answered for the whole run.

Per 400 000 entries on one core: the no-stereo pass takes ~30 s; the tautomer pass takes 2.4 h uncapped, or ~21 min at the default cap.

Hashes written before version 6

openchemlib-search-wasm 2.0.0 fixed a defect that gave the wrong hash to a molecule whose stereogenic double bond carries no configuration — around 1.5% of a drug-like library, and exactly the molecules a SMILES written without stereo produces. Nothing about a stored hash says which version computed it, so schema version 4 drops both hash tables and recreates them empty. Run backfillHashes() again after upgrading; until you do, the two hash modes return nothing, which is the same state a database is in before its first backfill.

The ceiling, and what NULL means

The cost of a tautomer hash is set by how many tautomers the molecule has, so that is what bounds it: maxTautomers is the ceiling OpenChemLib stops enumerating at, and a molecule that reaches it has no tautomer hash. Measured over 2000 real idcodes:

| maxTautomers | given up on | whole pass | slowest molecule | | --- | --- | --- | --- | | 100000 (OpenChemLib's own) | 1.50% | 58.1 s | 2841 ms | | 20000 | 1.85% | 22.9 s | 5851 ms | | 5000 (default) | 2.60% | 7.1 s | 320 ms | | 2000 | 3.20% | 1.5 s | 48 ms | | 1000 | 3.85% | 0.9 s | 16 ms |

The default gives up on about as much as the old 100 ms clock did (2.3%) and is roughly eight times faster, but that is not the reason it replaced it.

A clock makes the database depend on the machine. Under load a time cap gives up on molecules a quiet machine hashes, so the same corpus imported on two hosts holds different hashes — and search(), which hashes its query the same way, then finds nothing for them. A ceiling is a work bound: the same molecule reaches it everywhere, so a database holds the same hashes wherever it was filled.

capMs remains as a backstop for a molecule that runs long for some other reason; it is no longer what normally stops one. The no-stereo pass enumerates no tautomers, so neither bound applies to it.

Changing the ceiling rebuilds the tautomer hashes

Which molecules have a tautomer hash depends on the ceiling, so the ceiling is recorded in the database. Open an existing database with a different maxTautomers and migrate() empties the tautomer hash table — onMigration reports it — so backfillHashes() fills it again under the new one. Without that, raising or lowering it would make search() hash its query to a value no stored hash was ever going to equal, and the mode would quietly return nothing.

The no-stereo table is untouched: no ceiling applies to it.

A molecule the cap stops is stored as NULL, and so is one OCL cannot hash at all. Both mean the same thing to a search — this entry has no such hash — and neither is retried by a later run. Nothing is silently substituted: a column never holds a fallback that would make it mean two different things.

It is resumable

An entry is marked done by the presence of its row, not by its value, so hash IS NULL ("no hash for this molecule") and no row at all ("not tried yet") stay distinguishable. Work is committed a chunk at a time, so an interrupted run loses at most one chunk and the next call continues from exactly there — nothing is ever recomputed.

That is also why the two hashes get a table each rather than two columns of one: the passes are independent, and a row's presence in its own table already says what a shared table would need an extra "attempted" marker per hash to say.

// bound each pass
await molDB.backfillHashes({ limit: 50_000 });

// or stop at the next chunk boundary on shutdown
const controller = new AbortController();
process.once('SIGTERM', () => controller.abort());
await molDB.backfillHashes({ signal: controller.signal });

Entries inserted after a backfill are picked up by the next run, so a service can simply call it on a timer. It holds the write lock only for each chunk's insert — never while molecules are being hashed — and yields between chunks, so it can run beside a server that keeps serving.

poolSize (default: core count), chunkSize (default: 500) and limit tune the rest.

Note: the columns hold real 64-bit values. Reading one back with node:sqlite needs stmt.setReadBigInts(true), or the read throws Value is too large to be represented as a JavaScript number. Searching never reads them, so this only affects querying the hash tables yourself.

Schema

migrate() creates four tables:

ocl_ss_index                (mw, entry_id, ss_index0 .. ss_index7)  -- WITHOUT ROWID, PK (mw, entry_id)
ocl_no_stereo_hash          (entry_id, hash)                        -- NULL = no hash for this molecule
ocl_no_stereo_tautomer_hash (entry_id, hash)                        -- NULL = no hash for this molecule
ocl_ss_schema               (version, applied_at)                   -- which schema version this database is at

entry_id is a foreign-key reference to your entries table's primary key column, with a unique index of its own. The eight ss_indexN columns store the 512-bit OCL fingerprint packed as signed 64-bit integers for efficient SQL bitwise prefiltering. mw leads the primary key so the table is physically stored lightest-first — see above.

Both hash tables are created empty and filled by backfillHashes() — see Structure hashes. Each carries a partial index on hash (skipping the NULLs, which no query ever matches).

Upgrading

Call migrate() on every startup. It is idempotent, it records the schema version it reaches, and it applies only what a database is missing — so it does nothing once current and upgrades in place when it is not. There is no separate command to run and no dump/reload:

const molDB = new MoleculesDBSQLite(db, OCL, { entriesTable: 'ligands' });

molDB.migrate({
  // Upgrading a large index rewrites every row. Log it: a startup that is
  // working should not look like one that has hung.
  onMigration: (event) => logger.info(event, 'ocl_ss_index migration'),
});

migrate() returns the versions it applied ([] when there was nothing to do), and onMigration receives a start / progress / done event per version, carrying done / total rows while a version runs and elapsedMs when it finishes.

Upgrades reuse whatever the old schema already held rather than recomputing it. Going from the 2.x index to the mw-clustered one, for instance, carries the fingerprints over untouched — they are the expensive part (~6 ms a molecule) and the schema change does not affect them; only mw is new. Measured on 49 983 CCD ligands:

| | time | | ------------------------------------------------------ | ---------- | | mwColumn configured — weights come straight from SQL | 105 ms | | no mwColumn — weights derived from each idCode | 2.7 s |

Compare with ~5 minutes to re-fingerprint the same index from scratch.

Version 3 adds the two structure hash tables. They are created empty, so the migration is instant; filling them is a separate long-running job — see Structure hashes.

Each version is applied in its own transaction, so an interrupted upgrade leaves the database at the last version that fully completed — never half-way through one. A migration only ever discards rows it cannot carry (an orphaned fingerprint whose entry no longer exists, which no search could return), and reports the count as dropped rather than dropping it quietly.

Adding a schema version

Append to MIGRATIONS in src/migrations.ts; never edit a shipped migration, since it has already run on real databases. Databases created before ocl_ss_schema existed are recognised once by shape and recorded from then on.

Using a different SQLite driver

The constructor accepts any object satisfying the SQLiteDatabase duck-typed interface (compatible with node:sqlite and better-sqlite3):

import { MoleculesDBSQLite, type SQLiteDatabase } from 'openchemlib-sqlite';

const db: SQLiteDatabase = /* any compatible driver */;

Note: Substructure and similarity searches call stmt.setReadBigInts(true) when available (node:sqlite). For other drivers, configure BigInt return for INTEGER columns at the driver level.

License

MIT