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}]
Copy

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
Copy

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);
Copy

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
Copy

License

MIT