@chcaa/sqlite-json-table
Advanced tools
A wrapper for sqlite tables making it easy to manage json-objects with the flexibility of a nosql database and still use the power of SQL for querying data.
sqlite-json-table is an implementation of a JSON-based SQLite database. It provides an interface for creating, maintaining, and querying one or
more SQLite tables containing JSON data. It further allows for indexing selected fields and creating relations between objects
as well as creating and synchronizing cross-table references.
Entries are based on JSON-compliant JavaScript objects. Queries to the database return parsed, regular JavaScript objects and arrays. Inserting and updating entries is similarly done using regular objects and arrays which are automatically serialized into their proper forms in the database.
As a general rule-of-thumb, an SQLite table corresponds to a set of JSON objects. Each object corresponds to a single row. If no indexing or relation is made (for more about indexing and relations, see below) an entry contains exactly two columns:
id for the entry (uniquely generated or provided by the user): Stored as an INTEGERjson object serialized wih JSON.stringify(): Stored as TEXT(For more about SQLite data types, see here).
To take advantage of the optimizations of SQLite, it is possible to create field indexes for certain fields. Every indexed field is placed in its own column in the SQLite table (or a separate index table for arrays), allowing for fast access when filtering and sorting using the SQLite engine. Relations can be made within or across tables, making it possible to combine complex object structures with traditional relations by referring the ids of other objects in the database. By both supporting complex objects and traditional relations, the best of the NoSQL- and SQL-database worlds are combined making it easy to store complex objects and by the same time prevent redundancy by normalizing data where it makes sense.
sqlite-json-table provides two complementary ways of working with tables. If only a single table is required, it is possible to import and use only
the JsonTable class, thus avoiding unnecessary overhead. To handle multiple tables, a JsonTableDb class is provided to manage the creation and
maintenance of tables, including managing relations and bidirectional synchronization in and across tables with relational data.
npm install @chcaa/sqlite-json-table
An instance of better-sqlite3 is expected to be passed in as the database interface to use.
The following pragma settings are recommended when configuring SQLite.
import { Database } from 'better-sqlite3';
db = new Database("PATH-TO-FILE");
db.pragma('journal_mode = WAL'); // https://www.sqlite.org/wal.html
db.pragma('synchronous = normal'); // https://www.sqlite.org/pragma.html#pragma_synchronous
db.pragma('foreign_keys = true'); // https://www.sqlite.org/pragma.html#pragma_foreign_keys
In the following section, general usage is presented along with examples and notes for best practices.
For a detailed description of each method see Class: JsonTable and Class: JsonTableDb and their respective JsDocs.
There are two fundamental ways of creating tables. Either a single table can be created, using the JsonTable class, or multiple tables
can be created and managed using the JsonTableDb class which takes care of table management and supports cross-table events and actions.
NOTE: In the examples below, the data is stored in-memory. Pass in a file path to create a persistent SQLite database.
A few words on terminology: When making tables, create refers to the creation of a JavaScript representation and configuration of the table,
while init creates the actual table in the SQLite database, and creates any necessary index columns and index tables in the database as well.
A single table is self-contained and works without the database manager (but does not allow for relations).
The constructor takes the following arguments: A better-sqlite3 instance, a name for the table, and an optional options object
for indexing. For more about the JsonTableOptions object, see JsonTableOptions.
import Database from 'better-sqlite3';
import { JsonTable } from '@chcaa/sqlite-json-table';
let db = new Database(':memory:'); // NB pass in a file path to make the db persistent
let myJsonTable = new JsonTable(db, 'persons'); // creates the table
Multiple JsonTable instances in a project should be created and managed using a JsonTableDb instance which then handles the lifecycle of
the JsonTables and makes cross-table events and relations between JsonTable entries possible.
Creating a new JsonTableDb instance:
import Database from 'better-sqlite3';
import { JsonTableDb, JsonTable } from '@chcaa/sqlite-json-table';
let db = new Database(':memory:'); // NB pass in a file path to make the db persistent
let jsonTableDb = new JsonTableDb(db);
To create and initialize a new JsonTable instance, use the createAndInitJsonTable method:
import Database from 'better-sqlite3';
import { JsonTableDb, JsonTable } from '@chcaa/sqlite-json-table';
let db = new Database(':memory:'); // NB pass in a file path to make the db persistent
let jsonTableDb = new JsonTableDb(db);
let personJsonTable = jsonTableDb.createAndInitJsonTable('persons');
The database manager provides other methods for creating and initializing JsonTable instances. See Class: JsonTableDb for more.
To be able to query and/or sort on a field and relational field, in e.g. JsonTable#find(), the field must first be registered as a field index.
Field indexes are provided as an array of objects, where each object corresponds to a single field index.
Each field index must have the field property. The field property refers to a field on the objects to be saved in the table. E.g. { field: 'name' }
declares that an index should be created for the top-level field name. It is possible to create nested fields too: In the example here, name and age
are top-level fields, while street is a field of the nested object address, signified with the dot separator.
let personJsonTable = jsonTableDb.createAndInitJsonTable('persons', {
fieldIndexes: [
{ field: 'name' },
{ field: 'age' },
{ field: 'address.street' }
]
});
If the path is an array or is included in an array, the field index must declare this by setting the array property to true.
let personJsonTable = jsonTableDb.createAndInitJsonTable('persons', {
fieldIndexes: [
{ field: 'hobbies', array: true }, // an array of strings
{ field: 'addresses.street', array: true }, // an array of address objects, where each address objects street field should be indexed
]
});
Relations are similar to relations between tables in a relational database. Relations can both be self-referential and reference other tables.
Relations are created by setting the relation property of a field index. Defining relations makes it possible to query and populate the relations when
fetching objects using, e.g., JsonTable#find().
let personJsonTable = jsonTableDb.createAndInitJsonTable('persons', {
fieldIndexes: [
{ field: 'cars', array: true, relation: { target: 'cars' } } // an array of car ids
]
});
let carJsonTable = jsonTableDb.createAndInitJsonTable('cars', {
fieldIndexes: [], // no field indexes
});
When using relations, the field holding the relation must have one of the following values:
null, an integer >= 0 or an object with an id field >= 0id field >= 0When saving or updating objects which have relations, automatic depopulation of relations will be performed before storing the object in the database. This makes it possible to save and update objects with populated relations without having to replace the populated relations with their corresponding id.
personJsonTable.create({ cars: [{ id: 1, make: "Ford", model: "Fiesta" }] }); // will store { cars: [1] } in the persons JsonTable
NOTE
JsonTable#on(),JsonTableDb#onRelationChange()andjsonTableDb#registerBidirectionalSync()can be used to be notified about and handle changes to relations.
As field indexes make a copy of the value from the entry object and store it in a separate column/table, the field index value can be transformed to improve querying and sorting without affecting the original value. This can be used to improve the querying capabilities by e.g., normalizing special characters to their ASCII equivalent, converting to lowercase, trimming whitespaces, etc.
Field index transformations also make it possible to solve a limitation of Sqlite in relation to UTF-8 where Sqlite does only support
case-insensitive comparisons on ASCII characters. By adding a lowercase transformation
to a field index this limitation can be circumvented.
Transformations are applied to any value corresponding to the field index before the value is stored in the column/table of the field index in the database.
Values used for querying a field index passed in using queryOptions.filter.bindValues will have the same transformations applied before the database is
queried.
IMPORTANT
If transformations for a field index are changed (added, removed, transform order changed) after the field index has been created in the database. TheJsonTable#reindex()method must be called with the field paths for any field index with changes to its transformations. As transformations are just expressed as one or more functions, this cannot be automatically detected onJsonTable#init()and is therefore up to the user code to handle.If data exists for a given field index and transformations are changed without a
redindex()the index could be in an unpredictable state leading to wrong search results.Run the
reindex()in a database migration and create a new migration file for every time areindex()is required to make sure they only run once per change.
Transformations to a field index are applied by setting the transform property of the field index configuration object to a single transform function or an
array of transform functions. A transform function is a function that takes a single value parameter and returns the transformed version of the value or
throws and error if the value could not be transformed. sqlite-json-table already comes with a set of often-used transformations which can be imported
from @chcaa/sqlite-json-table/transform.
See here for details.
import { lowercase, trim, foldToAscii } from '@chcaa/sqlite-json-table/transform';
let personJsonTable = jsonTableDb.createAndInitJsonTable('persons', {
fieldIndexes: [
{ field: 'name', transform: [lowercase, trim, foldToAscii] }
]
});
Every JsonTable method altering or querying the database runs by default in its own transaction. If the transaction fails, all changes performed within
the transaction will be rolled back. The methods create(), createWithId(), update(),
delete(), deleteById() all supports both taking a single value and an array of values making it possible to group changes into the same transaction.
NOTE
If multiple method calls need to be grouped together into a single transaction useJsonTableDb#runInTransaction().
Any valid JSON object can be stored in the table. The create method adds an id field
to the object with the id of the row from the table in the database.
let person = { name: 'John', address: 'Some Street', age: 23, created: Date.now() };
personJsonTable.create(person);
console.log(person.id); // an id is added on succesful create
Multiple entries can be created at once (within the same transaction) by passing an array of the entries to the create method.
let person = { name: 'John', address: 'Some Street', age: 23, created: Date.now() };
let person2 = { name: 'Jane', address: 'Some Other Street', age: 43, created: Date.now() };
personJsonTable.create([person, person2]);
If the id is required as part of the creation process, a function can be passed in as the second argument which will then be called with the id before the object is stored in the database.
let person = { name: 'John', address: 'Some Street', age: 23, created: Date.now() };
personJsonTable.create(person, (person, id) => person._idInternal = id);
If the id is generated externally (id must be an integer), use:
let person = { name: 'John', address: 'Some Street', age: 23, created: Date.now(), id: 1 };
personJsonTable.createWithId(person);
To create entries with relations, the JsonTable instance must be configured with the relations it the field indexes and all involved
JsonTable instances must be registered in the same JsonTableDb.
let cityJsonTable = jsonTableDb.createAndInitJsonTable('cities');
let personJsonTable = jsonTableDb.createAndInitJsonTable('persons', {
fieldIndexes: [
{ field: 'boss', relation: { target: 'persons' } }, // relation to entry in the same table
{ field: 'address.city', relation: { target: 'cities' } } // nested relation to a different table
]
});
let theWindyCity = { name: 'Chicago', country: 'USA' };
cityJsonTable.create(theWindyCity);
let harry = { name: 'Harry', boss: null, address: { street: 'CEO Street', city: theWindyCity } };
let sally = { name: 'Sally', boss: harry, address: { street: 'Employee Avenue', city: theWindyCity } }
personJsonTable.create([harry, sally]);
A single relation must be expressed by: null, an integer (referring an id), or an object with an id field. An array of relations must be
expressed by: an empty array, an array of integers (referring an id), or an array of objects with an id field.
Relations expressed by an object with an id are automatically depopulated before saving to the database.
let personJsonTable = jsonTableDb.createAndInitJsonTable('persons', {
fieldIndexes: [
{ field: 'cars', array: true, relation: { target: 'cars' } } // an array of car ids
]
});
let carJsonTable = jsonTableDb.createAndInitJsonTable('cars', {
fieldIndexes: [], // no field indexes
});
// This way
let myCars = [{ model: "Audi" }, { model: "Fiat" }];
carJsonTable.create(myCars); // the cars are given the necessary id field and saved in carJsonTable
personJsonTable.create({ name: "Peter", cars: myCars }); // the cars are depopulated and replaced with their ids
// ... or this way
personJsonTable.create({ name: "Peter", cars: [myCars[0].id, myCars[1].id] }); // no depopulation will take place, as ids are already there
// BUT NOT THIS WAY
personJsonTable.create({ name: "Peter", cars: [{ model: "Audi" }, { model: "Fiat" }] }); // will throw an error as no ids exist in the car objects
NOTE: Entries can be repopulated when queried. See more in the section Querying Entries or Class: JsonTable.
To update an entry, it must have the id field set.
let person = personJsonTable.getById(1);
person.age++;
personJsonTable.update(person);
If all entries need to be updated e.g., when a new field is added to the model or a field is no longer required
a special updateAll() method is provided which can handle this in a safe and performant way in a single transaction.
the updateAll() takes a function which is passed each entry. The function can by its return value
control what should happen to the entry in one of the following ways:
personJsonTable.updateAll((entry) => {
if (entry.name === 'John') {
entry.name = 'Johnny';
} else {
return false;
}
});
Delete an entry by its id:
personJsonTable.deleteById(1);
or by the entry with the id to delete:
let person = personJsonTable.getById(1);
personJsonTable.delete(person);
Fetch an entry by its id using getById():
let person = personJsonTable.getById(1);
Multiple entries can be retrieved simultaneously using getByIds(). null is inserted for ids with no matching entries:
let person1 = { name: 'John', address: 'Some Street', age: 23, created: Date.now() };
let person2 = { name: 'Gerry', address: 'Baker Street', age: 45, created: Date.now() };
personJsonTable.create([person1, person2]); // the entries are given ids 1 and 2
let oneResult = personJsonTable.getByIds([1, 2]); // returns both entries
let theSameResult = personJsonTable.getByIds([1, 2, 1]); // returns three entries where index 0 and index 2 are the same
let anotherResult = personJsonTable.getByIds([2, 3, 1]); // returns [{ name: 'Gerry', ... }, null, { name: 'John' ... }]
To get all entries, use getAll() which optionally can be sorted using any of the configured fieldIndexes not having array: true. (If the table contains
many entries
iteratorAll() should be used instead to avoid memory issues):
let persons = personJsonTable.getAll(); // No sorting
let sortedPersons = personJsonTable.getAll({ field: 'age', order: 'desc' }); // Sorted by age in descending order
If the entries retrieved are configured with a relational field index, it is possible to populate these relations (replacing their ids with the full entries):
let carJsonTable = jsonTableDb.createAndInitJsonTable('cars');
let personJsonTable = jsonTableDb.createAndInitJsonTable('persons', {
fieldIndexes: [
{ field: 'cars', array: true, relation: { target: 'cars' } } // an array of car ids
]
});
// ...
let person = personJsonTable.getById(1); // -> { name: 'Peter', cars: [1, 2] }
personJsonTable.populate(person, 'cars'); // -> { name: 'Peter', cars: [{ id: 1, model: 'Audi' }, { id: 2, model: 'Fiat' }] }
The configured fieldIndexes can be queried directly using SQL syntax using the find()
and findOne() methods. Field names must be quoted:
let result = personJsonTable.find({
filter: { where: '"name" LIKE ? AND "created" > ? AND "addresses.street" = ?', bindValues: ['J%', 1200202022, "Some Street"] },
sorting: { field: 'created', order: 'desc' },
pagination: { offset: 0, limit: 2 }
});
See further documentation of find() and findOne() in the JsDocs and the JsonTable Details.
If filtering is required on a field which is not configured in fieldIndexes, use filterAll().
filterAll() will load all entries from the database (in chunks) and apply the passed in predicate filter.
let result = personJsonTable.filterAll((person) => person.address = 'Some Street');
NOTE
This is less performant than querying field indexes, as all entries need to be parsed, so use field indexes if possible.
All entries in a table can be exported directly to ndjson using exportToJsonFile():
await personJsonTable.exportToJsonFile('/data/persons.ndsjon');
Alternatively a ReadableStream can be obtained e.g., to stream the content using a http-response or any
other compatible consumer of a ReadableStream instance.
let readableStream = jsonTable.createExportReadableStream();
response = new Response(readableStream, {
headers: {
'Content-Type': 'application/octet-stream',
'Content-Disposition': `attachment; filename="${filename}"`
}
});
Sometimes changes to the existing data model can require the production database to be upgraded to
match the changes. This could e.g., be adding new fields that require a default value (e.g. null),
restructuring of fields, or deletion of fields.
IMPORTANT
JsonTable.init()handles all additions and modifications tofieldIndexesso these changes do not require a patch.
Changes in the development code should just be applied, and the test data updated, but to get the production database in sync when publishing a new version with a changed "schema," a patch-file is required, which can perform the changes to the live database.
Patches are described in patch-files (see template below) which name should follow the pattern YYYY-DD-MM_v{SEMANTIC_VERSION}.patch.js.
So if the next version of the app is1.2.0 and the live database needs to be patched create a new patch file with the current date and the
target version number e.g. 2023-10-10_v1.2.0.patch.js. The date is required, but is only used for sorting correctly in the filesystem.
A patch file template should look like the following:
import { JsonTable } from "@chcaa/sqlite-json-table";
let state = 0;
export async function beforePatch(db) {
// any setup for the patch can be done here
state = 1;
}
/**
* OBS this function must be a synchronous function as it will be executed inside a transaction which cannot span multi ticks on the event loop
* @param {import('better-sqlite3')} db
*/
export function patch(db) {
// use state from beforePatch if required, if async operations is not required, just do everything here...
let testTable = new JsonTable(db, 'test-table', {
fieldIndexes: [] // the same field indexes as the table has in production to avoid unnecessary reindexing
});
testTable.init(false); // throws an error if not exists, which is good as a precaution
testTable.updateAll(entry => {
// do updates here (add/alter/delete fields), see docs on "updateAll" for different returns values meaning
});
// To alter or delete a table use JsonTableDb#deleteTable() or JsonTableDb#copyJsonTableTo() for e.g. renaming a table
// There should be no need for using the "db" directly.
// (creation of tables will be handled automatically when the app starts up and creates a new JsonTable which is not present, so this should not be handled here).
}
When initializing the application before any data is loaded, do the following to apply the patches required to get the database patched with the missing patches:
import { DatabaseMigrator } from '@chcaa/sqlite-json-table';
let patchDir = '../DIR-WITH_PATCH_FILES';
let backupDir = '.../DIR-FOR-BACKUP'
let db; // the better-sqlite3 instance to patch
let currentDbVersion = '1.0.0'; // this should come from a file or a table in the db
let maxPatchVersion = '1.2.0'; // this should be the current version of the app, e.g. taken from package.json
let dbMigrator = new DatabaseMigrator(db, patchDir, {
printStatus: true, // defaults to false
disableForeignKeyConstraints: true, // disable key constratins while migrating, defaults to true
backupDirPath: backupDir // optional, but recommended is no other backup of the db exists
});
let patches = await dbMigrator.getPatches(currentDbVersion, maxPatchVersion);
await dbMigrator.applyPatches(patches, info => {
currentDbVersion = info.version; // this should be written to where the db version number is stored
});
JsonTableThe JsonTable class is responsible for a single table in the database and is the main interface for creating, updating, deleting, and
querying entries in the table.
The public methods of the class are presented below. For a more detailed view, please see the respective JsDocs and source code.
jsonTable#name (readonly)string - The table name.These methods are mainly used to create a stand-alone JsonTable instance. If you work with multiple tables, you should use the JsonTableDb class which
provides methods for creating and managing table instances.
new JsonTable(db, name, [options], [jsonTableDb])Creates a new JsonTable instance.
Parameters:
db: Database - The better-sqlite3 database instance.name: string - The name of the table to create.options?: JsonTableOptions - A JsonTableOptions object. Defaults to {}. See below for more about the JsonTableOptions
object.jsonTableDb?: JsonTableDb - A JsonTableDb instance to connect the newly created JsonTable instance to. Used by the methods of the JsonTableDb class.Returns:
JsonTable - The newly created JsonTable instance.Example:
import Database from 'better-sqlite3';
import { JsonTable } from '@chcaa/sqlite-json-table';
let db = new Database(':memory:'); // NB pass in a file path to make the db persistent
let personJsonTable = new JsonTable(db, 'persons', {
fieldIndexes: [
{ field: 'name' },
{ field: 'age' },
{ field: 'address.street' }
]
});
The constructor method only creates the JsonTable instance; it does not automatically register it in the SQLite database. To do so, it is necessary to also
call the init() method.
init([createTableIfNotExists])Initializes an already created JsonTable instance in the database. By default, the method takes care of creating the table in the database,
including setup of field index columns. If a table with the name already exists in the database, it will be reconfigured with the new field indexes.
If createTableIfNotExists is false and no table exists in the database with the same name, an error will be thrown.
Parameters:
createTableIfNotExists?: boolean: Whether to create the table if it does not already exist. Defaults to true.Example:
// Imports and database object creation
let personJsonTable = new JsonTable(db, 'persons', {
fieldIndexes: [
{ field: 'name' },
{ field: 'age' },
{ field: 'address.street' }
]
});
personJsonTable.init();
createAndInit(db, name, [options], [jsonTableDb]) (static)Creates and inits the table. The table will be created if it does not exist. This is a shorthand for creating the
JsonTable using the constructor and then initializing it immediately after.
Parameters:
constructor and are passed on.Returns:
JsonTable - The newly created and initialized JsonTable instance.Example:
let personJsonTable = JsonTable.createAndInit(db, 'persons');
create(entries, [preprocess])Inserts one or more entries into the table. Any populated relations are automatically depopulated and replaced with their IDs
(the passed in entries will not be altered). If objects to be created already have an id field, an error will be thrown.
If a preprocess callback function is provided, the function is executed for each entry before saving it to the database.
Parameters:
entries: object|object[] - An object or an array of objects to insert into the table.preprocess?: function - A callback that is called for each entry. Takes two parameters:
entry: object - The entry object.id: number - The newly assigned ID returned from the SQLite table.Returns:
object|object[] - The passed in entry/entries with an added id field.Example:
// One entry
let person = { name: "Jody", age: 55, address: { street: "New Street" } };
personJsonTable.create(person);
// Multiple entries
let people = [
{ name: "Jody", age: 55, address: { street: "New Street" } },
{ name: "Sara", age: 20, address: { street: "Old Street" } }
];
personJsonTable.create(people);
// Preprocessing
personJsonTable.create(people, (entry, id) => entry.archivedId = id)
// This will throw an error:
personJsonTable.create({ id: 5, name: "Hansi", age: 44, address: { street: "Bad Street" } });
createWithId(entries)Inserts one or more entries into the table. Any populated relations are automatically depopulated and replaced with their IDs (the passed in entries will not be altered).
Each entry must have an id field with value >= 1 and must be an integer. If no id field is provided, or if the ids provided
already exist in the SQLite table, an error will be thrown.
Parameters:
entries: object|object[] - An object or array of objects to insert into the table.Returns:
object|object[] - The passed in entry/entries.Example:
// One entry
let person = { id: 3, name: "Jody", age: 55, address: { street: "New Street" } };
personJsonTable.createWithId(person);
// Multiple entries
let people = [
{ id: 45, name: "Jody", age: 55, address: { street: "New Street" } },
{ id: 26, name: "Sara", age: 20, address: { street: "Old Street" } }
];
personJsonTable.createWithId(people);
// These will throw errors
personJsonTable.createWithId({ name: "Jimmy", age: 32, address: { street: "A Street" } }); // No id provided
personJsonTable.createWithId({ id: 3, name: "Hansi", age: 44, address: { street: "Bad Street" } }); // Already exists in the table
update(entries)Updates one or more entries in the table.
The entries passed in must all have an id which is used to determine which row to update in the table. Any populated relations are automatically
depopulated and replaced with their IDs before being saved (the passed in entries will not be altered).
Parameters:
entries: object|object[] - The entry or entries to update in the table. All entries must have an id field.Returns:
object|object[] - The passed in entry/entries.Example:
personJsonTable.createWithId({ id: 3, name: "Jody", age: 55, address: { street: "New Street" } });
let person = personJsonTable.getById(3);
person.age++;
personJsonTable.update(person);
//OR - the entry does not need to come from the db, as long as the id is known
personJsonTable.update({ id: 3, name: "Jody", age: 56, address: { street: "New Street" } });
updateAll(updateCb)Iterates over all entries in the table and calls the passed in callback function for each entry. The method call will be wrapped in a transaction, so any error thrown from the callback function will roll back any operations performed before the error occurred.
NOTE: Update events will not be fired using this method.
Parameters:
updateCb: function - The callback function to call with each entry as parameter. The function must return one of the following, depending on the desired
outcome.
undefined (or nothing): The entry will be updated in the database with the changes made to it.object: The returned object will replace the existing object in the database.false: No operation will be performed.null: The entry will be deleted from the database.NOTE
To prevent unintentional side effects, always create a block body for the function passed in as an expression body is implicitly returned and could result in unintended deletes. E.g., the following would actually delete the entry, as it returns the expression result which in this case isnull.// Not the intended behavior, as `null` is implicitly returned. table.updateAll(entry => entry.newValue = null); // The intended behavior, as `undefined` is returned when no explicit return statement is present in a block body. table.updateAll(entry => { entry.newValue = null; });
Example:
let jsonTableDb = new JsonTableDb(db);
let personJsonTable = jsonTableDb.createAndInitJsonTable('persons');
personJsonTable.create([
{ name: "Jamie Saint Merat", hobbies: ['Swimming', 'Running'] },
{ name: "Michael Hoggard", hobbies: ['Biking'] },
{ name: "Paul Kelland", hobbies: [] },
{ name: "Lis Sørensen", hobbies: ['Biking'] }
]);
const bikingPredicate = hobby => hobby.trim().toLowerCase() === 'biking';
const bikingToCyclingMapping = hobby => bikingPredicate(hobby) ? 'Cycling' : hobby;
// conditionally update or delete...
personJsonTable.updateAll(person => {
let update = false;
if (person.hobbies.length === 0) {
return null; // Remove persons with no hobbies from the database
}
if (person.name.startsWith('Jamie')) {
person.name = 'James';
update = true;
}
if (person.hobbies.includes(bikingPredicate)) {
persons.hobbies = person.hobbies.map(bikingToCyclingMapping);
update = true;
}
return update;
});
// add a new field to all
personJsonTable.updateAll(person => {
person.friends = []; // as we do not return anything all entries will be updated with the new field
});
delete(entries)Deletes one or more entries in the table.
Parameters:
entries: object|object[] - The entry or array of entries to delete from the database.Example:
let jsonTableDb = new JsonTableDb(db);
let paintersJsonTable = jsonTableDb.createAndInitJsonTable('artists');
paintersJsonTable.create({ name: "Vincent van Gogh" }); // get the id 1 in the database
let aPainter = paintersJsonTable.getById(1); // retrieve the entry by its id
paintersJsonTable.delete(aPainter);
deleteById(ids)Deletes one or more entries in the table by their id.
Parameters:
ids: number|number[] - The id or arrays of ids to delete.Example:
let jsonTableDb = new JsonTableDb(db);
let personJsonTable = jsonTableDb.createAndInitJsonTable('persons');
personJsonTable.create([
{ name: "Jamie Saint Merat", hobbies: ['Swimming', 'Running'] },
{ name: "Michael Hoggard", hobbies: ['Biking'] },
{ name: "Paul Kelland", hobbies: [] },
{ name: "Lis Sørensen", hobbies: ['Biking'] }
]);
personJsonTable.deleteById([2, 3]);
NOTE
Any relations in the returned entries are represented by their id. To replace the ids with the referenced objects, see Populating and Depopulating Entries.
getById(id, [options])Returns an entry by its id or null if no entry was found.
Parameters:
id: number - The id of the entry to retrieve.options?: object - Projection options. See project() for details.
includeFields?: string[] - The fields to include.excludeFields?: string[] - The fields to exclude.Returns:
object|null - An object if an entry with the id was found or null if no matching entry was found.Example:
let person = personJsonTable.getById(4);
let personWithNameOnly = personJsonTable.getById(4, { includeFields: ['name'] });
getByIds(ids, [options])Returns an array of entries corresponding to the passed in ids.
If one or more ids was not found, null is inserted at the given indices. If multiple ids are identical, the same entry will be used for each identical id.
Parameters:
ids: number[]|Set<number> - An array or set of ids to query for.options?: object - Projection options. See project() for details.
includeFields?: string[] - The fields to include.excludeFields?: string[] - The fields to exclude.Returns:
object[] - An array of objects representing entries in the same order as their ids was passed to this method.Example:
let people = [
{ id: 45, name: "Jody", age: 55, address: { street: "New Street" } },
{ id: 26, name: "Sara", age: 20, address: { street: "Old Street" } }
];
personJsonTable.createWithId(people);
let persons = personJsonTable.getByIds([45, 26]); // -> [ { id: 45, ... }, { id: 26, ... } ]
let persons2 = personJsonTable.getByIds([33, 26]); // -> [ null, { id: 26, ... } ]
let persons3 = personJsonTable.getByIds([45, 26, 45]); // -> [ { id: 45, ... }, { id: 26, ... },{ id: 45, ... } ]
getAll([queryOptions])Returns all entries in the table.
Parameters:
queryOptions?: object - The query options to apply to the query.
sorting?: object - The sorting to apply.
field?: string - The path to sort by. Must be defined as a field index or be "id". Defaults to "id".order?: "asc"|"desc" - The order to sort by. Defaults to "asc".includeFields?: string[] - The fields to include. See project() for details.excludeFields?: string[] - The fields to exclude. See project() for details.Returns:
object[] - An array of all entries sorted by path and order.Example:
let personJsonTable = new JsonTable(db, 'persons', {
fieldIndexes: [
{ field: 'name' },
{ field: 'age' },
{ field: 'address.street' }
]
});
// ...
let allPersons = personJsonTable.getAll({ sorting: { field: 'name', order: 'desc' } })
find([queryOptions])Finds all entries matching the query options. The filter.where property declares an SQLite flavored SQL condition which allows the database to
perform the filtering for high performance. Fields paths must be double-quoted.
The queryOptions object included in the result object will have added defaults for missing optional values, totalCount added to the pagination object
(if total count is calculated) and bindValues transformed using any transform functions configured on the target field index of given bind value.
Parameters:
queryOptions?: object - The query options to apply to the query.
filter?: object - The filter to apply to the SQL query.
where?: string - A conditional statement using SQL syntax. Must refer to defined field indexes or "id". Be aware that fields must be
double-quoted.bindValues?: any[] - An array of values to replace placeholders (?) in the filter.where statement.populateFields?: boolean|string[] - true returns the full entries that match the query. To only return their ids, set this property to false.
Provide an array of relation fields to return the full entries and further populate the provided relations with their full entries. Defaults to true.sorting?: object - The sorting to apply to the query.
field?: string - The field to sort by. Must be defined as a field index or be "id". Defaults to "id".order?: "asc"|"desc" - The order to sort by. Defaults to "asc".pagination?: object - Apply pagination to results.
offset: number - The offset to start from. Must be an integer >= 0.limit: number - The maximum number of results to return. Must be an integer >= 1.countTotal?: boolean - Include the total count in the results. Defaults to false.includeFields?: string[] - The fields to include. See project() for details.excludeFields?: string[] - The fields to exclude. See project() for details.Returns:
{results: object[], queryOptions: object, totalCount: number} - An object containing an array of results, updated queryOptions and a total count (if
requested).Example:
let personJsonTable = jsonTableDb.createAndInitJsonTable("persons", {
fieldIndexes: [
{ field: "age" },
{ field: "name" },
{ field: "address.city" },
{ field: "spouse", relation: { target: "persons" } }
]
});
// ...
let persons = personJsonTable.find({
filter: {
where: '"age" > ? AND "address.city" = ?',
bindValues: [30, "Århus"]
},
populateFields: ["spouse"],
sorting: {
field: "name",
order: "desc"
},
pagination: {
offset: 10,
limit: 30,
countTotal: true
}
});
// {
// results: [
// { id: 45, name: "Benny", age: 31, address: { city: "Århus" }, spouse: { id: 3, name: "Suzy", ... } },
// { id: 33, name: "Jytte", ... }
// ],
// queryOptions: { ... },
// totalCount: 231
// }
findOne([queryOptions])Similar to JsonTable#find() but only returns the first matching entry. Ignores any provided pagination object.
Parameters:
queryOptions?: object - See details under find().Returns:
object|null - The entry matching the query or null if no match was found.Example:
let jsonTableDb = new JsonTableDb(db);
let gameJsonTable = jsonTableDb.createAndInitJsonTable('games', {
fieldIndexes: [
{ field: 'title' }
]
});
gameJsonTable.create([
{ title: 'World of Warcraft' },
{ title: 'Warcraft 3' },
{ title: 'The Sims' },
{ title: 'Kerbal Space Program' }
])
let theFirstMatch = gameJsonTable.findOne({
filter: {
where: '"title" LIKE ?',
bindValues: ['%Warcraft%']
}
});
console.log(theFirstMatch.title); // -> "World of Warcraft"
filterAll(predicate)Streams all entries from the table and applies the predicate filter on each entry.
This method is less performant than querying field indexes using find(),
as all entries need to be parsed, so use field indexes and find() if possible.
Parameters:
predicate: function - A callback function to apply for each entry. The function takes one parameter, the entry, and must return a truthy or falsy value
(similar to Array.filter()).Returns:
object[] - An array containing all entries passing the predicate test.Example:
let personJsonTable = new JsonTable(db, 'persons', {
fieldIndexes: [] // no field indexes for "age", so we need to use filterAll()
});
let personsRetired = personJsonTable.filterAll(person => person.age > 67);
countAll()Counts all entries in the table.
Returns:
number - The total count of entries in the table.Example:
let totalCount = personJsonTable.countAll();
count([queryOptions])Counts the number of entries in the table matching the query.
Paramters:
queryOptions: object - The query options to apply to the query.
filter?: object - The filter to apply to the SQL query.
where?: string - A conditional statement using SQL syntax. Must refer to defined field indexes or "id". Be aware that fields must be
double-quoted.bindValues?: any[] - An array of values to replace placeholders (?) in the filter.where statement.Returns:
number - The count of entries matching the query.Example:
let personJsonTable = new JsonTable(db, 'persons', {
fieldIndexes: [
{ field: 'age' }
]
});
let retiredCount = personJsonTable.count({
filter: {
where: '"age" > ?',
bindValues: [67]
}
});
iteratorAll([options])Creates a new iterator for iterating over all entries in the table. The iteration is done in a streaming manner so
only chunkSize (default 1000) is read into memory at the same time.
Parameters:
options?: object - Streaming options.
chunkSize?: number - The number of entries to read into memory when streaming the result. Defaults to 1000.Returns:
Generator - A Generator instance that iterates over all entries in the table.Example:
let uniqueNames = new Set();
for (let person of personJsonTable.iteratorAll()) {
uniqueNames.add(person.name);
}
populate(entries, fields)Populates relation fields with their referenced entries. Nested relations are allowed as long as they are reachable from a registered relation field index of
the JsonTable the passed in entries belongs to.
NOTE: The passed in entries will be updated in place.
Parameters:
entries: object|object[] - An entry or array of entries matching the entry types stored in this JsonTable instance. All entries must have the
field: id.fields: string[] - An array of registered relation field indexes to populate with their referenced entries.Returns:
object|object[] - The passed in entries with populated relations.Example:
let jsonTableDb = new JsonTableDb(db);
let bandJsonTable = jsonTableDb.createAndInitJsonTable('bands');
let artistJsonTable = jsonTableDb.createAndInitJsonTable('artists', {
fieldIndexes: [
{ field: 'name' },
{ field: 'band', relation: { target: 'bands' } }
]
});
bandJsonTable.create({ name: 'Tool' });
let band1 = bandJsonTable.findOne({ filter: { where: '"name" = ?', bindValues: ['Tool'] } });
artistJsonTable.create([
{ name: 'Maynard James Keenan', instrument: 'vocals', band: band1 },
{ name: 'Danny Carey', instrument: 'drums', band: band1 }
]); // Band relations are saved in the database in the form of ids
let bandMembers = artistJsonTable.getAll(); // Still only have the ids. We must populate them
// → [{ name: 'Maynard James Keenan', instrument: 'vocals', band: 1 }, ...]
let populatedBandMembers = artistJsonTable.populate(bandMembers, ['band']);
// The full band object is now attached as the 'band' field
// → [{ id: 1, name: 'Maynard James Keenan', instrument: 'vocals', band: { id: 1, name: 'Tool' } }, ...]
depopulate(entries)Depopulates all relation fields, replacing any populated entry with its id.
NOTE: The passed in entries will be updated in place.
Parameters:
entries: object|object[] - An entry or array of entries matching the entry types stored in this JsonTable instance. All entries must have the
field: id.Returns:
object|object[] - The passed-in entries with depopulated relations.Example:
// ... continued from the example above
let depopulatedBandMembers = artistJsonTable.depopulate(populatedBandMembers);
// Now the band objects are replaced with their ids again
// → [{ id: 1, name: 'Maynard James Keenan', instrument: 'vocals', band: 1 }, ...]
project(entries, options)Projects the entry or entries to only include the fields defined in includeFields and/or exclude the fields defined in excludeFields.
A nested object included by a projection will only have its properties altered if at least one field of the object is present in includeFields or
excludeFields. Otherwise, the object will be untouched.
The id of the root object and any populated relations will be included as default unless explicitly excluded.
Projections support the wildcard operator * which can be used to include all fields of an object or fields starting with a prefix.
When targeting nestede fields, e.g., ["address.zipCode"], and all parent fields should be included as well, include the wildcard operator path
to include the parent fields ["*", "address.zipCode"].
To exclude all fields of an object, e.g., private fields starting with a _ exclude the fields using ["_*"].
To exclude all fields no matter what their parent/ancestor is use *., e.g., exclude all field recursively matching a pattern using ["*._private"]
or even ["*._*"].
A field which is included by a wildcard in excludeFields can be preserved by putting the field explicitly in includeFields, e.g.,
will the following include all fields of the root object, except the fields starting with "_" unless it is the exact field "_public".
{
includeFields: [
"*",
"_public"
],
excludeFields: [
"_*"
]
}
(To not only include the "_public" field we need to include "*" as well in includeFields).
NOTE: The passed in entries will be updated in place.
Parameters:
entries: object|object[] - An entry or array of entries matching the entry types stored in this JsonTable instance. All entries must have the
field: id.options: object - Include and exclude options.
includeFields?: string[] - The field paths to include.excludeFields?: string[] - The field path to exclude.Returns:
object|object[] - The passed-in entries with projects applied.Example:
let jsonTableDb = new JsonTableDb(db);
let bandJsonTable = jsonTableDb.createAndInitJsonTable('bands');
let artistJsonTable = jsonTableDb.createAndInitJsonTable('artists', {
fieldIndexes: [
{ field: 'name' },
{ field: 'band', relation: { target: 'bands' } }
]
});
let band1 = bandJsonTable.create({ name: 'Tool', dicography: [{ title: "Ænima" }] });
artistJsonTable.create([
{ name: 'Maynard James Keenan', instrument: 'vocals', band: band1 },
{ name: 'Danny Carey', instrument: 'drums', band: band1 }
]); // Band relations are saved in the database in the form of ids
let bandMembers = artistJsonTable.getAll(); // Still only have the ids. We must populate them
// → [{ name: 'Maynard James Keenan', instrument: 'vocals', band: 1 }, ...]
let populatedBandMembers = artistJsonTable.populate(bandMembers, ['band']);
// The full band object is now attached as the 'band' field
// → [{ id: 1, name: 'Maynard James Keenan', instrument: 'vocals', band: { id: 1, name: 'Tool' } }, ...]
let bandMembersWithNameAndBandName = artistJsonTable.project(populatedBandMembers, { includeFields: ['name', 'band.name'] });
// We now only have the id, name, band.id and band.name fields.
// -> [{ id: 1, name: 'Maynard James Keenan', band: { id: 1, name: 'Tool' } }, ...]
let bandMembersAndBandName = artistJsonTable.project(populatedBandMembers, { includeFields: ['*', 'band.name'] });
// All band member fields and the band.id and band.name fields.
// -> [{ id: 1, name: 'Maynard James Keenan', band: { id: 1, name: 'Tool' } }, ...]
The JsonTable emits events before and after altering changes to the database occur. This makes it possible to, e.g., centralize
adding meta-data such as created/updated timestamps before an entry is persisted, run an action after a change occurs or see what has changed
by comparing the before and after state of an entry.
An event can be one of the six states:
beforeCreateafterCreatebeforeUpdateafterUpdatebeforeDeleteafterDeleteBefore events will be emitted before the given action is applied to the database and makes it possible to modify the entries before the change is persisted.
The event will be a part of the current transaction, so any additional change done inside the before event listener will be a part of the transaction
and will be rolled back if the transaction fails. The before event object has the following properties (see on() for
details):
{
eventName,
jsonTable,
ids,
entries,
entriesBeforeChange
}
The entriesBeforeChange property will be undefined for the beforeCreate event.
After events will be emitted after the given action is applied to the database and the transaction is committed but before the caller which caused the event regains control.
The after event object has the following properties (see on() for details):
{
eventName,
jsonTable,
ids,
entries
}
The entries property will be undefined for the afterDelete event.
As after events are emitted before the caller initiating the event regains control event listeners depending on state updated by the initiating
caller, and where the initiating caller first updates the state after a successful change to the db,
must postpone their handling of the event by e.g., using process.nextTick().
Example:
let updateCount = 0;
function update() {
jsonTable.update({ id: 1, name: "John" });
updateCount++; // runs after jsonTable.update() succeeds
}
jsonTable.on('afterUpdate', () => {
console.log(updateCount); // will be 0 as the update() function has not had a chance to change updateCount yet
process.nextTick(() => console.log(updateCount)); // will be 1 as the update() function now has completed
});
update();
State can be passed from a before event to an after event using the state object passed to the listener function. The object passed to a before listener will also be passed to the after listener, making it possible to store information which must be collected in the before listener but first needed in the after listener.
The state object is shared between all listeners, so it is recommended to use a Symbol or similar as the property key to avoid other listeners from
accidentally overriding the set property.
on(eventName, listener)Registers a listener for the given event.
The same listener function for the same event can only be registered once, even if passed to this method multiple times.
Parameters:
eventName: "beforeCreate"|"afterCreate"|"beforeUpdate"|"afterUpdate"|"beforeDelete"|"afterDelete" - The event to listen for.listener: function(event: object, state: object) - The event-listener to call when the event occurs. Takes two parameters:
event: object - Contains information about the event.
eventName: string - The name of the event.jsonTable: JsonTable - The JsonTable instance that triggered the event.ids: number[] - The ids of the entries involved in the event.entries: object[] - The entries involved in the event.
entriesBeforeChange: object[] - The entries involved in the event before the change is applied. (Will only be included in before events).state: object - An object passed from before to after listeners which can be used to share state. Shared between all listeners.NOTE: The
ids,entriesandentriesBeforeChangeof theeventobject are guaranteed to be in the same order.
Example:
// import statements and jsonTableDb instantiation comes here
let documentJsonTable = jsonTableDb.createAndInitJsonTable('documents');
let changelogJsonTable = jsonTableDb.createAndInitJsonTable('changelogs');
const stateKey = Symbol();
// Listening for 'before updates'
documentJsonTable.on('beforeUpdate', beforeUpdateLogger);
function beforeUpdateLogger(event, state) {
// let's make an array-based changelog of updates
let changes = [];
for (let i = 0; i < event.entries; i++) {
// entries, entriesBeforeChange & ids are always in same order
let oldEntry = event.entriesBeforeChange[i];
let newEntry = event.entries[i];
let diff = calcDiff(oldEntry, newEntry);
changes.push(diffStr);
}
// passing data to the 'afterUpdate' event using 'state'
state[stateKey] = changes;
}
// Listening for 'after updates'; a "follow-up" on the 'before'
documentJsonTable.on('afterUpdate', afterUpdateLogger);
function afterUpdateLogger(event, state) {
let logEntry = {
timeStamp: Date.now(),
changedEntries: event.ids,
changes: state[stateKey] // access our passed-on data
}
changelogJsonTable.create(logEntry);
}
// a function that calculates a diff between two objects
function calcDiff(obj1, obj2) {
let diff;
// We assume that it works and returns a representation of the diff
return diff;
}
off(eventName, listener)Unregisters a registered listener for the given event.
Parameters:
eventName: "beforeCreate"|"afterCreate"|"beforeUpdate"|"afterUpdate"|"beforeDelete"|"afterDelete" - The event to stop listening for.listener: function - The event-listener to unregister for further notifications about the event.Example:
// continuation of the previous example
// we are now done with logging and want to disable the listener
// in this example, we only wish to log the first ~10000 updates
// do this from inside from one of the listeners, so we only unregister once and only when actually listening
let changelogCount = changelogJsonTable.countAll();
if (changelogCount > 10000) {
// it's important that we in some way kept a reference to the listener function
// to be able to remove it again using the reference
documentJsonTable.off('beforeUpdate', beforeUpdateLogger);
documentJsonTable.off('afterUpdate', afterUpdateLogger);
}
createExportReadableStream([options])Creates a readable stream for exporting data as JSON strings where each entry is separated by '\n' (ndjson).
NOTE: Using the export stream without applying any projections will be more performant as the JSON string from the database can be streamed directly to the output without parsing the JSON string.
NOTE: When filtering on *-M fields, set
chunkSizeas high as possible to avoid performance issues as the filter needs to be re-calculated (joined) for every chunk. A higherchunkSizemeans fewer DB requests but requires more memory.
Parameters
options?: object - Streaming, filtering, and projection options. See project() for projection option details.
includeFields?: string[] - - The fields to include.excludeFields?: string[] - - The fields to exclude.filter?: object - The filter to apply to the query. See find() for an example.
where?: string - A conditional statement using SQL syntax targeting the defined fieldIndexes or "id".bindValues?: *[] - The values to replace each placeholder in the filter.where statement with.chunkSize?: number - The number of entries to read into memory when streaming the result. Defaults to 1000.Returns:
ExportReadableStream (extends node:stream.Readable).Example:
// use for creating a HTTP reponse object
let stream = personJsonTable.createExportReadableStream();
let reponse = new Response(stream, {
headers: {
'Content-Type': 'application/octet-stream',
'Content-Disposition': `attachment; filename="persons.ndjson"`
}
});
// return the response to the web-client
exportToJsonFile(destFilePath, [options]) (async)Exports all objects in the table to a ndjson file.
NOTE: Using the export stream without applying any projections will be more performant as the JSON string from the database
can be streamed directly to the output without parsing the JSON string.
NOTE: When filtering on *-M fields, set
chunkSizeas high as possible to avoid performance issues as the filter needs to be re-calculated (joined) for every chunk. A higherchunkSizemeans fewer DB requests but requires more memory.
Parameters:
destFilePath: string - The full path of the file to save to. If the path does not exist, it is created.options?: object - Streaming, filtering, and projection options. See project() for projection option details.
includeFields?: string[] - - The fields to include.excludeFields?: string[] - - The fields to exclude.filter?: object - The filter to apply to the query. See find() for an example.
where?: string - A conditional statement using SQL syntax targeting the defined fieldIndexes or "id".bindValues?: *[] - The values to replace each placeholder in the filter.where statement with.chunkSize?: number - The number of entries to read into memory when streaming the result. Defaults to 1000.Returns:
{Promise<void>}.Example:
await personJsonTable.exportToJsonFile('/path/and/filename.ndjson');
reindex(fields)Reindexes the passed in field index paths. If at least one of the fields is a singular field index (array=false) all singular field indexes will be reindexed.
As JsonTable#init() automatically reindexes new field indexes the use of this method is only necessary if an existing
field index's transform function has been changed, so it produces a different output than before, which will not be detected by the init() method, or if the
entries have been altered from outside the JsonTable.
Parameters:
fields: string[] - The field index paths to reindex.Example:
personJsonTable.reindex(['age', 'children.name']);
reindexAll()Reindexes all field indexes.
As JsonTable#init() automatically reindexes new field indexes the use of this method is only necessary if an existing
index
field's transform function has been changed, so it produces a different output than before, which will not be detected by the init() method, or if the
entries have been altered from outside the JsonTable.
In most cases it will be more optimized to use reindex() and only reindex the field indexes which requires to be updated.
Example:
personJsonTable.reindexAll();
JsonTableDbThe JsonTableDb class is responsible for managing multiple tables and makes cross-table references possible.
The public methods of the class are presented below. For a more detailed view, please see the respective JsDocs and source code.
new JsonTableDb(db)Creates a new JsonTableDb instance for the database specified.
Parameters:
db: Database - A better-sqlite3 instance.Returns:
JsonTableDb - A new JsonTableDb instance.Example:
import Database from 'better-sqlite3';
import { JsonTableDb, JsonTable } from '@chcaa/sqlite-json-table';
let db = new Database(':memory:'); // NB pass in a file path to make the db persistent
let jsonTableDb = new JsonTableDb(db);
The following methods can be used to create and initialize JsonTable instances. The
creation also registers the JsonTable instance in the JsonTableDb instance. For more on the difference between creating and
initializing, see the JsonTable class overview.
createJsonTable(name, [options])Creates and registers a JsonTable without initializing it.
Parameters:
name: string - The name of the table to create.options?: JsonTableOptions - See more under The JsonTableOptions object.Returns:
JsonTable - A new JsonTable instance.Example:
let jsonTableDb = new JsonTableDb(db);
let personJsonTable = jsonTableDb.createJsonTable('persons', {
fieldIndexes: [
{ field: "name" }
]
});
createAndInitJsonTable(name, [options], [createTableIfNotExists])Creates, registers, and initializes a JsonTable.
Parameters:
name: string - The name of the table to create and initialize.options?: JsonTableOptions - See more under The JsonTableOptions object.createTableIfNotExists?: boolean - Whether to create the table in the database if no table with that name exists. Defaults to true.Returns:
JsonTable - The newly created and instantiated JsonTable instance.Example:
let jsonTableDb = new JsonTableDb(db);
let personJsonTable = jsonTableDb.createAndInitJsonTable('persons', {
fieldIndexes: [
{ field: "name" },
{ field: children, array: true }
]
});
initJsonTable(jsonTable, [createTableIfNotExists])Initializes the passed in JsonTable. If the instance is already initialized, no action will be taken.
Parameters:
jsonTable: JsonTable - The JsonTable instance to initialize.createTableIfNotExists?: boolean - Whether to create the table in the database if no table with that name exists. Defaults to true.Example:
let jsonTableDb = new JsonTableDb(db);
let personJsonTable = jsonTableDb.createJsonTable('persons', {
fieldIndexes: [
{ field: "name" }
]
});
jsonTableDb.initJsonTable(personJsonTable);
initAllJsonTables([createTableIfNotExists])Initializes all registered JsonTable instances which have not been initialized yet.
Parameters:
createTableIfNotExists?: boolean - Whether to create the tables in the database if no table with the given name exists. Defaults to true.Example:
let jsonTableDb = new JsonTableDb(db);
let personJsonTable = jsonTableDb.createJsonTable('persons', {
fieldIndexes: [
{ field: 'name' },
{ field: 'pet', relation: { target: 'pets' } }
]
});
let petJsonTable = jsonTableDb.createJsonTable('pets');
jsonTableDb.initAllJsonTables();
The following methods are mainly used internally. If the JsonTableDb methods are used to create and delete JsonTable instances, they are automatically
registered and unregistered. The same happens when using the JsonTable constructor and passing a JsonTableDb instance as argument.
Only if a JsonTable instance is created without providing a JsonTableDb argument, should it be registered manually.
registerJsonTable(jsonTable)Registers a JsonTable instance in the JsonTableDb instance.
Parameters:
jsonTable: JsonTable - The JsonTable instance to register.Example:
let personJsonTable = new JsonTable('persons');
jsonTableDb.registerJsonTable(personJsonTable);
unregisterJsonTable(jsonTable)Unregisters the given JsonTable instance.
Parameters:
jsonTable: JsonTable - The JsonTable instance to unregister.Returns:
boolean - true if the instance exists and was successfully unregistered, otherwise false.Example:
let personJsonTable = new JsonTable('persons');
jsonTableDb.registerJsonTable(personJsonTable);
jsonTableDb.unregisterJsonTable(personJsonTable);
getJsonTable(jsonTableName)Retrieves a JsonTable instance by name.
Parameters:
jsonTableName: string - The name of the JsonTable instance to retrieve.Returns:
JsonTable - A JsonTable instance if a matching table was found, otherwise undefined.Example:
let jsonTableDb = new JsonTableDb(db);
jsonTableDb.createAndInitJsonTable('persons', {
fieldIndexes: [
{ field: 'name' },
{ field: 'pets', array: true }
]
});
let personJsonTable = jsonTableDb.getJsonTable('persons');
getAllJsonTables()Retrieves all JsonTable instances registered in the JsonTableDb instance.
Returns:
JsonTable[] - An array of all registered JsonTable instances.Example:
let jsonTableDb = new JsonTableDb(db);
jsonTableDb.createAndInitJsonTable('persons', {
fieldIndexes: [
{ field: 'name' },
{ field: 'pet', relation: { target: 'pets' } }
]
});
jsonTableDb.createAndInitJsonTable('pets');
let jsonTables = json.getAllJsonTables();
hasJsonTable(jsonTableName)Checks if a table with the name exists and is registered in the JsonTableDb instance.
Parameters:
jsonTableName: string - The name of the JsonTable instance.Returns:
boolean - Whether a table with the name exists and is registered in the JsonTableDb instance.Example:
let jsonTableDb = new JsonTableDb(db);
jsonTableDb.createAndInitJsonTable('persons', {
fieldIndexes: [
{ field: 'name' },
{ field: 'pets', array: true }
]
});
let personJsonTableExists = json.hasJsonTable('persons');
The JsonTableDb can be configured to automatically maintain bidirectional synchronization where two JsonTable instances have a reciprocal relation to each
other. This means that any changes applied to one field are propagated into the inverse field and vice versa, keeping the relationship synchronized.
The synchronization between two relational fields should only be registered once, as A ↔ B is covering the inverse B ↔ A as well.
The JsonTable instances involved should have been registered in this JsonTableDb instance before configuring the synchronization.
The relation field must reference a field of the entry being one of: null, an integer > 0, or integer[] with values > 0.
The relation field is NOT required to be registered as a field index.
registerBidirectionalRelationSync(config)Defines and registers a relationship between paths that have a reciprocal relation, and where changes must be synchronized bidirectionally. Any change performed on one side of the relation will automatically be propagated to the related entries and vice versa.
Parameters:
config: object - Properties defining the reciprocal relationship.
relation: object - A relation to synchronize.
jsonTableName: string - The name of the JsonTable with the relation.field: string - The path of the relation field to sync.inversedBy: object - The complementary relation to synchronize.
jsonTableName: string - The name of the JsonTable with the relation.field: string - The path of the relation field to sync.The order of relations in the config object does not matter; since the synchronization is bidirectional, there is no difference whether one relation
is relation and the other inversedBy and vice versa.
Example:
let jsonTableDb = new JsonTableDb(db);
let personJsonTable = jsonTableDb.createAndInitJsonTable('persons', {
fieldIndexes: [
{ field: 'name' },
{ field: 'pets', array: true, relation: { target: 'animals' } }
]
});
let animalJsonTable = jsonTableDb.createAndInitJsonTable('animals', {
fieldIndexes: [
{ field: 'name' },
{ field: 'owner', relation: { target: 'persons' } }
]
});
jsonTableDb.registerBidirectionalRelationSync({
relation: {
jsonTableName: 'persons',
field: 'pets'
},
inversedBy: {
jsonTableName: 'animals',
field: 'owner'
}
});
let petOwner = personJsonTable.create({ name: "Jane", pets: [] });
// "Jane", the "petOwner" will have the "pets" array updated automatically with the pets she has become the owner of
animalJsonTable.create({ name: 'Gigi', owner: petOwner });
animalJsonTable.create({ name: 'Dash', owner: petOwner });
unregisterBidirectionalRelationSync(jsonTableName, field)Removes the registered relation synchronization for the path and the inverse path. It is enough to provide only one table name and path; the JsonTableDb
keeps track of the mutual relation and removes the full mapping.
Parameters:
jsonTableName: string - The name of the JsonTable with the relation.field: string - The path with the relation.Example:
// Register the bidirectional relation
jsonTableDb.registerBidirectionalRelationSync({
relation: {
jsonTableName: 'persons',
field: 'pets'
},
inversedBy: {
jsonTableName: 'animals',
field: 'owner'
}
});
// Unregister the bidirectional relation
jsonTableDb.unregisterBidirectionalRelationSync('persons', 'pets');
// Equal to:
// jsonTableDb.unregisterBidirectionalRelationSync('animals', 'owner');
Changes to relations of a JsonTable can be subscribed to, so when a change occurs to a related entry, an event about all affected entries will be emitted.
This can be used for e.g.
Relation change events can be registered for any direct or indirect mapped field index relation which can be reached from
the JsonTable to be notified about changes to relations affecting its entries.
onRelationChange(jsonTableName, fields, listener, [options])Registers a listener to listen for changes to relations of a JsonTable.
Parameters:
jsonTableName: string - The name of the JsonTable to be notified about changes to its relation fields.fields: string[] - An array of relation fields to listen for changes to. The fields must all be reachable from the JsonTable. To listen for changes
to the JsonTable itself register the relation path "".listener: function(event:object) - The function to call when a relation change occurs.
event: object - Contains information about the event.
jsonTable: JsonTable - The JsonTable owning the ids which are affected by the change to the relation.idsAffected: number[] - The ids of theJsonTable entries which are affected by the change to the relation.
causedBy: object - Information about the relation which has changed resulting in the relation change event.
jsonTable: JsonTable - The JsonTable of the relation.field: string - The field path of the relation.
ids: number[] - The ids of the entries on which the original event was performed.entries: object[] - The entries of the original event after the change. If the change was a delete event this will be undefined.entriesBeforeChange?: object[] - The entries of the original event before the change. If the change was a create event this will
be undefined. This property will only be set if configured in the options.
eventName: string - The name of the original event.options?: object - Additional configuration options.
emitBeforeDelete?: boolean - Set to true if beforeDelete events should be emitted. This could be used to clean up any relational data and thus
prevent foreign key constraint violations. Defaults to false.causedBy?: object - Configuration options for the causedBy property of the event object.
includeEntriesBeforeChangeInAfterEvents?: boolean - Should the entriesBeforeChange be included in after update/delete events. This will require an
extra database query.NOTE: The
ids,entriesandentriesBeforeChangeof thecausedByobject are guaranteed to be in the same order.
Example: Listen for changes to persons themselves and their relations to e.g., update a third party fulltext search-index.
let jsonTableDb = new JsonTableDb(db);
let personJsonTable = jsonTableDb.createAndInitJsonTable('persons', {
fieldIndexes: [
{ field: "spouse", relation: { target: "persons" } },
{ field: "friends", relation: { target: "persons" }, array: true },
{ field: "currentCompany", relation: { target: "companies" } },
{ field: "previousCompanies", relation: { target: "companies" }, array: true }
],
});
let companiesTable = jsonTableDb.createAndInitJsonTable('companies', { fieldIndexes: [] });
jsonTableDb.onRelationChange('persons', ['', 'spouse', 'friends', 'currentCompany', 'previousCompanies'], (e) => {
let personIdsToReindex = e.idsAffected;
let persons = personJsonTable.getByIds(personIdsToReindex);
personJsonTable.populate(persons, ['spouse', 'friends', 'currentCompany', 'previousCompanies']);
// Reindex the person objects here
});
Example: Listen for changes to nested relations to update an accumulated value. Only update if the value in question has changed.
let jsonTableDb = new JsonTableDb(db);
let personJsonTable = jsonTableDb.createAndInitJsonTable('persons', {
fieldIndexes: [{ field: "ownedItems.item", relation: { target: "items" }, array: true }]
});
let itemsTable = jsonTableDb.createAndInitJsonTable('items', { fieldIndexes: [] });
let guitar = itemsTable.create({ name: "Les Paul Guitar", value: 1000 });
personJsonTable.create({ name: "Jane", ownedItems: [{ date: "2020-01-01", desc: "Good condition", item: guitar.id }], itemsWorth: guitar.value });
// important that we include entriesBeforeChange to use for diff
jsonTableDb.onRelationChange('persons', ['ownedItems.item'], listener, { causedBy: { includeEntriesBeforeChangeInAfterEvents: true } });
function listener(e) {
let personIdsAffected = e.idsAffected;
let itemValueChanged = false;
if (e.causedBy.eventName !== 'afterUpdate') {
return; // we have a delete, which should be handled by removing the relation ownedItems and then recalculate the itemsWorth
}
for (let i = 0; i < e.causedBy.ids.length; i++) {
if (e.causedBy.entries[i].value !== e.causedBy.entriesBeforeChange[i].value) { // test if an update is required
itemValueChanged = true;
break;
}
}
if (itemValueChanged) {
let persons = personJsonTable.getByIds(personIdsAffected);
personJsonTable.populate(persons, ['ownedItems.item']);
for (let person of persons) {
person.itemsWorth = person.ownedItems.reduce((total, ownedItem) => total += ownedItem.item.value, 0);
}
personJsonTable.update(persons);
}
}
offRelationChange(jsonTableName, fields, listener)Unregisters the relation change listener for any further notifications.
Parameters:
JsonTableName: string - The name of the JsonTable owning the relations to unregister from further notifications about changes.fields: string[] - The fields to unregister. Unregistering only some of the previously registered fields is allowed.listener: function - The event-listener to unregister from further events on the passed in relation fields.Example:
// continuing the example above
jsonTableDb.offRelationChange('persons', ['ownedItems.item'], listener);
truncateJsonTable(jsonTable, [resetId])Deletes all content of the JsonTable and its related index tables.
Parameters:
jsonTable: JsonTable - The JsonTable instance to truncate.resetId?: boolean - Whether to reset the id of the table to 1. Defaults to false.Example:
let jsonTableDb = new JsonTableDb(db);
let personJsonTable = jsonTableDb.createAndInitJsonTable('persons', { fieldIndexes: [{ field: 'name' }] });
personJsonTable.create([
{ name: 'Janet', age: 26 },
{ name: 'Brad', age: 30 }
]);
console.log(personJsonTable.countAll()); // 2
jsonTableDb.truncateJsonTable(personJsonTable);
console.log(personJsonTable.countAll()); // 0
// Since 'resetId' was 'false' by default, a new entry ...
personJsonTable.create({ name: 'Magenta', age: 24 }); // ... will be given the id 3
truncateTable(tableName, [resetId])Deletes all content of the table. If the table is a JsonTable all related array index tables will be truncated as well.
Parameters:
tableName: string - The name of the table to truncate.resetId?: boolean - Whether to reset the id of the table to 1. Defaults to false.Example:
let jsonTableDb = new JsonTableDb(db);
let personJsonTable = jsonTableDb.createAndInitJsonTable('persons', { fieldIndexes: [{ field: 'name' }] });
personJsonTable.create([
{ name: 'Moss', age: 29 },
{ name: 'Roy', age: 32 }
]);
jsonTableDb.truncateTable('persons', true); // resets the id after truncation
personJsonTable.create({ name: 'Jen', age: 34 }); // will be given the id 1
renameTable(currentTableName, newTableName)Renames the table and updates any foreign key references to point to the renamed table.
No JsonTable instances must be registered in the JsonTableDb during renaming to ensure relational and event listener integrity.
Renaming tables should typically be performed as the first step of a database migration task.
If the table is a JsonTable, all indexes will be dropped before renaming. The indexes will be automatically re-created
the first time the JsonTable is initialized with the new name.
Parameters:
currentTableName: string - The name of the table to rename.newTableName: string - The new name of the table.Example:
let jsonTableDb = new JsonTableDb(db);
jsonTableDb.renameTable('persons', 'people');
jsonTableDb.createAndInitJsonTable('people', { fieldIndexes: { field: 'age' } });
deleteJsonTable(jsonTable)Deletes the JsonTable and all of its content and unregisters it from this JsonTableDb instance.
Any related index tables will be deleted as well.
Parameters:
jsonTable: JsonTable - The JsonTable instance to delete.Example:
let jsonTableDb = new JsonTableDb(db);
let albumTable = jsonTableDb.createAndInitJsonTable('albums', {
fieldIndexes: [
{ field: 'title' },
{ field: 'genre' },
{ field: 'members', array: true }
]
});
jsonTableDb.deleteJsonTable(albumTable); // will also delete the database index table for 'members'
deleteTable(tableName)Deletes the table and all of its content. If the table is a JsonTable all related index tables will be deleted as well and the JsonTable
will be unregistered from this JsonTableDb instance.
Parameters:
tableName: string - The name of the table to delete.Example:
let jsonTableDb = new JsonTableDb(db);
let artistJsonTable = jsonTableDb.createAndInitJsonTable('artists');
jsonTableDb.deleteJsonTable('artists');
copyJsonTableTo(srcJsonTable, destJsonTable, [deleteSrcOnSuccess])Copies the content of a JsonTable to a new JsonTable. The new JsonTable can have differently configured field indexes if needed.
Parameters:
srcJsonTable: JsonTable - The source JsonTable to copy from.destJsonTable: JsonTable - The target JsonTable to copy to. Must be empty.deleteSrcOnSuccess?: boolean - Whether to delete the source JsonTable on success. Defaults to false.Example:
let jsonTableDb = new JsonTableDb(db);
let originalPersonJsonTable = jsonTableDb.createAndInitJsonTable('persons', {
fieldIndexes: [
{ field: 'name' },
{ field: 'pets', array: true }
]
});
originalPersonJsonTable.create({
name: 'Torben',
address: {
street: 'Fancy Street',
city: 'Esbjerg'
},
pets: [
{ name: 'Bunnyface' },
{ name: 'Mr. Dog' }
]
});
let targetPersonJsonTable = jsonTableDb.createAndInitJsonTable('persons-snapshot', {
fieldIndexes: [
{ field: 'address.street' } // in the snapshot we would like the address.street to be index instead
]
});
jsonTableDb.copyJsonTableTo(originalPersonJsonTable, targetPersonJsonTable);
// Renaming a table
let newPeopleJsonTable = jsonTableDb.createAndInitJsonTable('people', {
fieldIndexes: [
{ field: 'name' },
{ field: 'pets', array: true }
]
});
jsonTableDb.copyJsonTableTo(originalPersonJsonTable, newPeopleJsonTable, true);
tableExists(tableName)Tests if the table exists in the SQLite database.
Parameters:
tableName: string - The name of the table to test for existence.Returns:
boolean - true if the table exists, otherwise false.Example:
let carTableExists = jsonTableDb.tableExists('cars');
if (carTableExists) {
// Do something that you can only do if the table exists...
}
close()Frees up any allocated resources and closes the database connection.
Example:
import Database from 'better-sqlite3';
import { JsonTableDb, JsonTable } from '@chcaa/sqlite-json-table';
let db = new Database(':memory:'); // NB pass in a file path to make the db persistent
let jsonTableDb = new JsonTableDb(db);
// A lot of code here, using the db...
// Now we are done with everything...
jsonTableDb.close();
runInTransaction(func)Executes the given function within a transaction. If a transaction is already active, the function will be part of the current transaction. If an error is thrown, the transaction and all child transactions will be rolled back.
All JsonTable methods which mutate the state of the database is always executed within a transaction, so the use of this method is only
necessary if several JsonTable methods calls need to be grouped into a single unit of work.
Parameters:
func: function - The function to execute within the transaction. The function must be synchronous, otherwise an error will be thrown.Example:
personJsonTable = jsonTableDb.createAndInitJsonTable('persons');
jsonTableDb.runInTransaction(() => {
let child = personJsonTable.getById(3);
let currentMother = personJsonTable.getById(4);
let newMother = { name: 'Jane' };
personJsonTable.create(newMother); // will assign an id
child.mother = newMother.id;
personJsonTable.delete(currentMother);
personJsonTable.update(child);
// if something goes wrong in one of create/update/delete all changes will be rolled back
});
JsonTableOptionsWhen creating tables, it is possible to provide additional options for creating field indexes for indexing and querying as well as forming relations internally or between tables. It is also possible to toggle debugging capabilities.
Currently supported properties are:
fieldIndexes: object[] - An array of objects specifying the field indexes to create. Each object can have the following properties.
field: string - The field path to create an index for.array?: boolean - Whether the path is an array or contained in an array. Defaults to false.relation?: object - A mapping of the relation to another JsonTable if the field index contains the foreign key of another JsonTable entry.
target?: string - The name of target JsonTable the field index references.foreignKeyConstraint?: boolean - Set to false to disable database managed foreign key constraints for this relation. Defaults to true.transform?: function(value)|function(value)[] - One or more transform functions to apply to the field before storing the value of the field in the
index.
The transformations will also be applied on queryOptions.filter.bindValues targeting the field during querying.debug?: boolean|("schema", "query")[] - Toggle debugging for everything or for schemas and/or queries.See Field indexes and relations for more information.
To optimize retrieval and sorting of entries based on field indexes (e.g., JsonTable#find()), it is possible to provide one or
more transform functions that will modify the content of the field index before storing it in the database. These transformation functions make it
possible to alter field indexes into a more optimized version, for e.g., sorting or querying, without altering the actual data.
See more under Optimizing for Querying and Sorting.
NOTE: The JSON data is not transformed, only the internally used metadata is.
A set of transform functions are provided out of the box, but new ones can easily be created as well. A transform function should take a single value as a
parameter, typically a string, and return the transformed value. As multiple transform functions can be applied
sequentially, use an appropriate return type for the next function in the pipeline. Finally, the last function in the sequence must return a simple type,
e.g., string or number, as that value will be saved in the database field. Otherwise, an error will be thrown.
To account for the possibility of null values in field indexes with a single value, it is recommended that custom functions should pass null values through
unchanged, unless their specific purpose is to transform null values.
To use the predefined functions, import them from @chcaa/sqlite-json-table/transform.
combine(...functions)Combines a set of transform functions into a single function which input will be passed through each transform function in a pipeline fashion and will return the result of the final transform function.
Parameters:
functions: ...function(*):* - One or more functions that will be combined into one. Each functionmust take one parameter and
can return any value except the last one which must return either a string, number, boolean, or null.Returns:
function(*): string|number|boolean|null - The combined function which wraps the transformation chain.booleanToInteger(bool)Transforms any value, except null and undefined into its string representation. null values are returned unchanged, while undefined is transformed into
an empty string.
Parameters:
bool: boolean - The boolean to convert to its integer equivalent.Returns:
string|null - 1 if the boolean was true and 0 if the boolean was false or null if the argument was null.toString(value)Transforms any value, except null and undefined into its string representation. null values are returned unchanged, while undefined is transformed into
an empty string.
Parameters:
value: * - The input to transform.Returns:
string|null - A string representation of the input value or null if input is null.nullToString(value, [str])Transforms null values into a string representation. Defaults to an empty string, but a custom transform value can be provided. Other values are returned
unchanged.
Parameters:
value: * - The value to transform.str?: string - The string to replace null values with. Defaults to '' (empty string).Returns:
* - A string representation of the passed in null value or the passed in value unchanged for other data types.lowercase(str)Transforms the passed in string to lowercase. null is returned unchanged. Other argument types will cause an error to be thrown.
Parameters:
str: string|null - The string to lowercase.Returns:
string|null - The string in lowercase or null if the argument was null.trim(str)Trims the string (removes leading and trailing whitespace). null values are returned unchanged. Other types will cause an error to be thrown.
Parameters:
str: string|null - The string to trim.Returns:
str: string|null - A trimmed version of the string or null if the argument was null.foldToAscii(str, [fallback])Converts alphabetic, numeric, and symbolic Unicode characters which are not in the first 127 ASCII characters
(the "Basic Latin" Unicode block) into their ASCII equivalents if one exists.
null values are returned unchanged. Other types will cause an error to be thrown.
Based on the Apache Lucene ASCII mapping.
Parameters:
str: string|null - The string to fold.fallback?: string - A fallback string to use if the Unicode character is outside the mapping scope, e.g., emojis. If undefined, which is the default,
the original character will be inserted.Returns:
string|null - The folded string.foldToDanishAscii(str, [fallback])Similar to foldToAscii(), except that any Danish characters (Æ, Ø, Å, and their lowercase variants) are normalized before the ASCII folding is applied.
Otherwise, these characters would be folded to A and O, respectively.
Normalizing:
null is returned unchanged. Other types will cause an error to be thrown.
Parameters:
str: string|null - The string to normalize and fold.fallback?: string - A fallback string to use if the Unicode character is outside the mapping scope, e.g., emojis. If undefined, which is the default,
the original character will be inserted.Returns:
string|null - The folded string.Here is a list of inspiration for other functions which could be useful:
Methods and functions in sqlite-json-table will throw and error if arguments to a method/function does not fulfill the expected
type or value or if something goes wrong during execution.
ValidationError will be thrown if input arguments are incorrect. These errors can be caught,
and the operation can be tried again (with corrected arguments).JsonTableError will be thrown if something not supposed to happen occurs. These errors should be considered to signal an
inconsistency in state which should be prevented and should NOT be handled in try-catch statements.The different errors can be imported from @chcaa/sqlite-json-table/error.
import { ValidationError, JsonTableError } from '@chcaa/sqlite-json-table/error';
try {
personJsonTable.update({ name: "John" }); // will throw ValidationError because of missing id
} catch (e) {
if (e instanceof ValidationError) {
// handle error in some way....
} else {
throw e;
}
}
FAQs
A wrapper for sqlite tables making it easy to manage json-objects with the flexibility of a nosql database and still use the power of SQL for querying data.
We found that @chcaa/sqlite-json-table demonstrated a healthy version release cadence and project activity because the last version was released less than a year ago. It has 0 open source maintainers collaborating on the project.
Did you know?
Socket for GitHub automatically highlights issues in each pull request and monitors the health of all your open source dependencies. Discover the contents of your packages and block harmful activity before you install or update your dependencies.
Product
Socket’s new Tier 1 Reachability filters out up to 80% of irrelevant CVEs, so security teams can focus on the vulnerabilities that matter.
Research
/Security News
Ongoing npm supply chain attack spreads to DuckDB: multiple packages compromised with the same wallet-drainer malware.
Security News
The MCP Steering Committee has launched the official MCP Registry in preview, a central hub for discovering and publishing MCP servers.