smart-sqlite3-filter

This package allows querying a sqlite3 database using the built-in node:sqlite module in a way like 'firstName:luc'.

Installation

$ npm i smart-sqlite3-filter

The database is a DatabaseSync instance from Node.js's built-in node:sqlite module, so there is no native dependency to compile. This requires Node.js 22.13 or later, the first version exposing node:sqlite without the --experimental-sqlite flag.

Usage

import { DatabaseSync } from 'node:sqlite';

import { search } from 'smart-sqlite3-filter';

const db = new DatabaseSync(':memory:');
const sql = `
  CREATE TABLE IF NOT EXISTS names (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    name TEXT NOT NULL,
    year INTEGER NOT NULL,
    age REAL NOT NULL
  );

  INSERT INTO names (name, year, age) VALUES
    ('John', 1990, 30.1),
    ('Jane', 1985, 29.7),
    ('Alice', 2000, 25),
    ('Bob', 1990, 43);
`;
db.exec(sql);

// We don't search in a specific column, it will search everywhere
// For string column it is case insensitive and should startWith, for number it should be the exact value
search('John', db); // [{name: 'John', year: 1990}]

search('1990', db); // [{name: 'John', year: 1990}, {name: 'Bob', year: 1990}]

search('J', db); // [{name: 'John', year: 1990}, {name: 'Jane', year: 1985}]

// we search in a specific field and use the operator '>'. For numbers the following operators can be used: '>', '>=', '<', '<=', '<>', '!=', '='. Default to 'starts with'
search('year:>1990', db); // [{name: 'Alice', year: 2000}]
search('year:!=1990,2000', db); // [{name: 'Jane', year: 1985}]
search('year:<>1990,2000', db); // [{name: 'Jane', year: 1985}]

search('age:30', db); // by default we take into account significative digits and it will search between 29.5 and 30.5
search('age:=30', db); // must be exactly 30, no hit
search('age:=25', db); // must be exactly 25, 1 hit

// when searching for a string we can use the following operators: '^' (starts with), '$' (ends with), '~' (contains), '='. Default to contains. When searching for '=' it is case sensitive otherwise it is not.
search('name:~o', db); // [{name: 'John', year: 1990}, {name: 'Bob', year: 1990}]
search('name:$e', db); // [{name: 'Jane', year: 1985}, {name: 'Alice', year: 2000}]
search('name:^J', db); // [{name: 'John', year: 1990}, {name: 'Jane', year: 1985}]

// A field may have various values separated by ','
search('year:1990,2000', db); // [{name: 'John', year: 1990}, {name: 'Bob', year: 1990}, {name: 'Alice', year: 2000}]
search('year:1990,2000 name:$e,n', db); // [{name: 'John', year: 1990}, {name: 'Alice', year: 2000}]
search('year:!=1990,2000', db); // [{name: 'Jane', year: 1985}]

// It is possible by searching for a range of values using '..'
search('year:1980..1987', db); // [{name: 'Jane', year: 1985}]

Pagination, columns and totals

search accepts limit (default 1000), from to skip results, and columns to avoid reading columns you don't need. count returns the number of matching entries, ignoring limit / from.

import { count, search } from 'smart-sqlite3-filter';

search('year:>1985', db, { orderBy: 'name', limit: 2, from: 2 }); // second page
search('year:>1985', db, { columns: ['name', 'year'] }); // no SELECT *
count('year:>1985', db); // 3

Composing with your own statement

buildWhere returns the WHERE condition and its bound parameters without running anything, so the filter can go inside a statement search cannot express: your own joins, a subquery, a COUNT(*), a window function, …

The emitted parameters are named, so they merge with your own without colliding on position.

import { buildWhere } from 'smart-sqlite3-filter';

const { where, values } = buildWhere('name:~ester mw:100..500', db, {
  tableName: 'molecules',
});

// one page, plus the exact total
const page = db
  .prepare(
    `SELECT id, name, mw FROM molecules WHERE ${where}
     ORDER BY mw DESC LIMIT :limit OFFSET :offset`,
  )
  .all({ ...values, limit: 50, offset: 0 });

const { total } = db
  .prepare(`SELECT COUNT(*) AS total FROM molecules WHERE ${where}`)
  .get(values);

// or as a subquery driving a join — nothing is materialised
db.prepare(
  `SELECT s.* FROM (SELECT id FROM molecules WHERE ${where}) c
   JOIN some_index s ON s.entry_id = c.id`,
).all(values);

where is an empty string when the query string carries no criteria, so guard it with `${where || '1 = 1'}` when the query may be empty.

Usage with BSON

import { DatabaseSync } from 'node:sqlite';

import { search } from 'smart-sqlite3-filter';

const db = new DatabaseSync(':memory:');

// create data and add some dummy data
const sql = `
    CREATE TABLE IF NOT EXISTS bsons (
      id INTEGER PRIMARY KEY AUTOINCREMENT,
      name TEXT NOT NULL,
      year INTEGER NOT NULL,
      bson BLOB NOT NULL
    );

    INSERT INTO bsons (name, year, bson) VALUES
    ('John', 1990, jsonb('{"name": "John", "year": 1990}')),
    ('Jane', 1985, jsonb('{"name": "Jane", "year": 1985}')),
    ('Alice', 2000, jsonb('{"name": "Alice", "year": 2000}')),
    ('Bob', 1990, jsonb('{"name": "Bob", "year": 1990}'));
  `;

db.exec(sql);

search('name:$e,n', db); // 3 hits
search('year:1990,2000 name:$e,n', db); // 2 hits
search('bson.year:1990', db); // 2 hits
search('bson.name:$e', db); // 2 hits
search('bson.name:$e,n', db); // 3 hits
search('bson.year:1990,2000 bson.name:$e,n', db); // 2 hits

License

MIT