smart-sqlite3-filter

This package allows to query a sqlite3 database using better-sqlite3 in a way like 'firstName:luc'.

Development on Apple Silicon

cd node_modules/better-sqlite3
npm run build-release

Installation

$ npm i smart-sqlite3-filter

Usage

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

License

MIT