This package allows to query a sqlite3 database using better-sqlite3 in a way like 'firstName:luc'.
cd node_modules/better-sqlite3
npm run build-release$ npm i smart-sqlite3-filter
import { search } from 'smart-sqlite3-filter';
import sqLite from 'better-sqlite3';
const db = sqLite(':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}]
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: '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}]