@chcaa/sqlite-json-table v2.0.0

sqlite-json-table

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 properties 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.

• Getting Started
• General Usage
• Class: JsonTable
• Class: JsonTableDb

Main Features

• Create either a single table or a full database with a combination of objects and relations
• Easy indexing of properties for performant querying and sorting (including support for nested objects and arrays)
• Query properties using regular SQL-syntax
• Mix complex object strutures and relations between objects from different tables
• Automatic handling of database index creation and migrations
• Automatic population and depopulation of relational data
• Easy and performant modification of existing objects in the table
• Export to newline-delimited json file (.ndjson)

About SQLite JSON Tables

As a general rule-of-thumb, a SQLite table corresponds to 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:

1. An id for the entry (uniquely generated or provided by the user): Stored as an INTEGER
2. The json 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 designate certain properties as index 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 normalising data where it is needed.

Getting Started

sqlite-json-table provides two complementary ways of working with tables. If only a single table is needed, it is possible to import and use only the table class, thus avoiding unnecessary overhead. To handle multiple tables, a database class is provided to manages the creation and maintenance of tables, including managing relations and bidirectional synchronization in and across tables with relational data.

Installation

npm install @chcaa/sqlite-json-table

Dependencies

An instance of better-sqlite3 is expected to be passed in as the database interface to use.

General Usage

In the following, general usage is presented along with examples and notes for best practices.

• Creating Tables
• Creating Entries
• Querying Entries
• Updating Entries
• Deleting Entries
• Exporting Data
• Database Migration

For a detailed description of each method see Class: JsonTable and Class: JsonTableDb and their respective JsDocs.

Creating Tables

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 of the database table, while init creates the actual table in the SQLite database, and creates any necessary index columns and index tables in the database.

A Single JsonTable

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

Using the JsonTableDb Database Manager

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

Creating a New JsonTable

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.

Index Fields and Relations

To be able to query and/or sort on properties and relations, in e.g. JsonTable#find(), they must first be declared as index fields. Index fields are provided as an array of objects, where each object corresponds to a single index field.

Each index field must have the path property. The path property refers to a property on the objects to be saved in the table. E.g. { path: 'name' } declares that an index should be created for the top-level property name. It is possible to created nested fields too: In the example here, name and age are top-level properties, while street is a property of the nested object address, signified with the dot separator.

let personJsonTable = jsonTableDb.createAndInitJsonTable('persons', {
    indexFields: [
        { path: 'name' },
        { path: 'age' },
        { path: 'address.street' }
    ]
});

If the path is an array or is included in an array, the index field must declare this by setting the array property to true.

let personJsonTable = jsonTableDb.createAndInitJsonTable('persons', {
    indexFields: [
        { path: 'hobbies', array: true }, // an array of strings
        { path: 'addresses.street', array: true }, // an array of address objects, where each address objects street property should be indexed
    ]
});

Relations

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 an index field. Defining relations makes it possible to query and populate the relations when fetching objects using, e.g., JsonTable#find().

let personJsonTable = jsonTableDb.createAndInitJsonTable('persons', {
    indexFields: [
        { path: 'cars', array: true, relation: { target: 'cars' } } // an array of car ids
    ]
});
let carJsonTable = jsonTableDb.createAndInitJsonTable('cars', {
    indexFields: [], // no index fields
});

When using relations, the property holding the relation must have one of the following values:

• 1-1 and M-1 relations: null, an integer >= 0 or an object with an id property >= 0
• 1-M and M-M relations: an array of integers >= 0 or an array of objects with an id property >= 0

When 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()and jsonTableDb#registerBidirectionalSync() can be used to be notified about and handle changes to relations.

NOTE\ Referential integrity (foreign key constrains) for relations is not provided -> no error will be thrown if an id does not exist.

Optimizing for Querying and Sorting

As Index fields makes a copy of the value from the entry object and stores it in a separate column/table the index field 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.

Index field transformations also makes 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 an index field this limitation can be circumvented.

Transformations are applied to any value corresponding to the index field before the value is stored in the column/table of the index field in the database. Values used for querying an index field passed in using queryOptions.filter.bindValues will have the same transformations applied before the database is queried.

IMPORTANT\ If transformations for an index field is changed (added, removed, transform order changed) after the index field has been created in the database. The JsonTable#reindex() method must be called with the paths for any index field with changes to its transformations. As transformations is just expressed as one or more functions this cannot be automatically detected on JsonTable#init() and is therefore up to the user code to handle.

If data exists for a given index field 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 a reindex() is required to make sure they only run once per change.

Transformations to an index field is added by setting the transform property of the index field configuration object to a single transform function or an array of transform functions. A transform function is a function which 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 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', {
    indexFields: [
        { path: 'name', transform: [lowercase, trim, foldToAscii] }
    ]
});

A Note on Transactions

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 needs to be grouped together into a single transaction use JsonTableDb#runInTransction().

Creating Entries

Any valid JSON object can be stored in the table. The create method adds an id property 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 needed 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 relation index fields and all involved JsonTable instances must be registered in the same JsonTableDb.

let cityJsonTable = jsonTableDb.createAndInitJsonTable('cities');
let personJsonTable = jsonTableDb.createAndInitJsonTable('persons', {
    indexFields: [
        { path: 'boss', relation: { target: 'persons' } }, // relation to entry in the same table
        { path: '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 property. 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 property.

Relations expressed by an object with an id are automatically depopulated before saving to the database.

let personJsonTable = jsonTableDb.createAndInitJsonTable('persons', {
    indexFields: [
        { path: 'cars', array: true, relation: { target: 'cars' } } // an array of car ids
    ]
});

let carJsonTable = jsonTableDb.createAndInitJsonTable('cars', {
    indexFields: [], // no index fields
});

// This way
let myCars = [{ model: "Audi" }, { model: "Fiat" }];
carJsonTable.create(myCars); // the cars are given the necessary id property 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.

Updating Entries

To update an entry it must have the id property set.

let person = personJsonTable.getById(1);
person.age++;
personJsonTable.update(person);

Updating All Entries

If all entries need to be updated e.g. when a new property is added to the model or a property is no longer needed 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:

1. Update the passed in entry and return nothing -> the entry will be updated in the database with all changes.
2. Return an object -> the returned object will replace the current object in the database.
3. Return false -> no operation will be performed.
4. return null -> the entry will be deleted from the database.

personJsonTable.updateAll((entry) => {
    if (entry.name === 'John') {
        entry.name = 'Johnny';
    } else {
        return false;
    }
});

Deleting Entries

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

Querying Entries

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 indexFields 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({ path: 'age', order: 'desc' }); // Sorted by age in descending order

If the entries retrieved are configured with a relational index field, it is possible to populate these relations (replacing their ids with the full entries):

let carJsonTable = jsonTableDb.createAndInitJsonTable('cars');
let personJsonTable = jsonTableDb.createAndInitJsonTable('persons', {
    indexFields: [
        { path: '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 indexFields 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: { path: '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 needed on a property which is not configured in indexFields, 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 index fields, as all entries needs to be parsed, so use index fields if possible.

Exporting Data

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}"`
    }
});

Database Migration

Sometimes changes to the existing data model can require the production database to be upgraded to match the changes. This could be adding new properties which requires a default value (e.g. null), restructuring of properties, or deletion of properties.

IMPORTANT\ JsonTable.init() handles all additions and modifications to indexFields so these changes do not require a patch.

Changes in the development code should just be made and then test data updated, but to get the production database in sync when publishing a new version with a changed "schema" a patch-file is needed, 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', {
        indexFields: [] // the same index fields as the table has in production to avoid unnecessary reindexing
    });
    testTable.init(false); // throws error if not exists, which is good as a precaution
    testTable.updateAll(entry => {
        // do updates here (add/alter/delete properties), 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, currentDbVersion, maxPatchVersion, {
    printStatus: true,
    backupDirPath: backupDir // optional, but recommended is no other backup of the db exists
});

let patches = await dbMigrator.getPatches();

await dbMigrator.applyPatches(patches, info => {
    currentDbVersion = info.version; // this should be written to where the db version number is stored
});

Class: JsonTable

The 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.

Properties

Property: jsonTable#name (readonly)

• string - The table name.

Setup

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.

Constructor: 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', {
    indexFields: [
        { path: 'name' },
        { path: 'age' },
        { path: '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.

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 index field columns. If a table with the name already exists in the database, it will be reconfigured with the new index fields.

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', {
    indexFields: [
        { path: 'name' },
        { path: 'age' },
        { path: 'address.street' }
    ]
});

personJsonTable.init();

Method: 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:

• The parameters are the same as for the constructor and are passed on.

Returns:

• JsonTable - The newly created and initialized JsonTable instance.

Example:

let personJsonTable = JsonTable.createAndInit(db, 'persons');

Creating Entries

Method: 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 property, an error will be thrown.

If a preprocess callback 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 property.

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" } });

Method: 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 property with value >= 1 and must be an integer. If no id property 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

Updating Entries

Method: 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 property.

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" } });

Method: 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.
  • Return undefined (or nothing): The entry will be updated in the database with the changes made to it.
  • Return object: The returned object will replace the existing object in the database.
  • Return false: No operation will be performed.
  • Return null: The entry will be deleted from the database.

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 property to all
personJsonTable.updateAll(person => {
    person.friends = []; // as we do not return anything all entries will be updated with the new property 
});

Deleting Entries

Method: 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);

Method: 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]);

Querying Entries

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.

Method: getById(id)

Returns an entry by its id or null if no entry was found.

Parameters:

• id: number - The id of the entry to retrieve.

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

Method: getByIds(ids)

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.

Returns:

• object[] - An array of objects representing entries in the same order as their ids were 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, ... } ]

Method: 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.
    • path?: string - The path to sort by. Must be defined as an index field or be "id". Defaults to "id".
    • order?: "asc"|"desc" - The order to sort by. Defaults to "asc".

Returns:

• object[] - An array of all entries sorted by path and order.

Example:

let personJsonTable = new JsonTable(db, 'persons', {
    indexFields: [
        { path: 'name' },
        { path: 'age' },
        { path: 'address.street' }
    ]
});

// ...

let allPersons = personJsonTable.getAll({ sorting: { path: 'name', order: 'desc' } })

Method: find([queryOptions])

Finds all entries matching the query options. The filter.where property declares an SQLite flavoured SQL condition which allows the database to perform the filtering for high performance. 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 index field 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 index fields or "id". Be aware that fields must be double-quoted.
    • bindValues?: any[] - An array of values to replace placeholders (?) in the filter.where statement.
  • populate?: 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 paths 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.
    • path?: string - The path to sort by. Must be defined as an index field 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.

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", {
    indexFields: [
        { path: "age" },
        { path: "name" },
        { path: "address.city" },
        { path: "spouse", relation: { target: "persons" } }
    ]
});

// ...

let persons = personJsonTable.find({
    filter: {
        where: '"age" > ? AND "address.city" = ?',
        bindValues: [30, "Århus"]
    },
    populate: ["spouse"],
    sorting: {
        path: "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
//  }

Method: 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', {
    indexFields: [
        { path: '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"

Method: filterAll(predicate)

Streams all entries from the table and applies the predicate filter on each entry.

This method is less performant than querying index fields using find(), as all entries needs to be parsed, so use index fields 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', {
    indexFields: [] // no index fields for "age", so we need to use filterAll()
});

let personsRetired = personJsonTable.filterAll(person => person.age > 67);

Method: countAll()

Counts all entries in the table.

Returns:

• number - The total count of entries in the table.

Example:

let totalCount = personJsonTable.countAll();

Method: 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 index fields 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', {
    indexFields: [
        { path: 'age' }
    ]
});

let retiredCount = personJsonTable.count({
    filter: {
        where: '"age" > ?',
        bindValues: [67]
    }
});

Method: iteratorAll([chunkSize])

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:

• chunkSize?: number - The number of entries to read into memory for each chunk. 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);
}

Populating Entries

Method: populate(entries, paths)

Populates relation fields with their referenced entries. Nested relations are allowed as long as they are reachable from a registered relation index fields 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 property: id.
• paths: string[] - An array of registered relation index fields to populate with their referenced entries.

Returns:

• object[] - The passed in entries with populated relations.

Example:

let jsonTableDb = new JsonTableDb(db);

let bandJsonTable = jsonTableDb.createAndInitJsonTable('bands');

let artistJsonTable = jsonTableDb.createAndInitJsonTable('artists', {
    indexFields: [
        { path: 'name' },
        { path: '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' property
// -> [{ name: 'Maynard James Keenan', instrument: 'vocals', band: { id: 1, name: 'Tool' } }, ...] 

Method: 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 property: id.

Returns:

• 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
// -> [{ name: 'Maynard James Keenan', instrument: 'vocals', band: 1 }, ...]

Events

The JsonTable emits events before and after altering changes to the database occurs. 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:

• beforeCreate
• afterCreate
• beforeUpdate
• afterUpdate
• beforeDelete
• afterDelete

Before events

Before 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

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

Passing State from Before- to After Events

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 is needed again in the after listener.

The state object is shared between all listeners, so it is recommended to use a Symbol or similar as key to avoid other listeners from accidentally overriding the set property.

Method: 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 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[] - TTe 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, entries and entriesBeforeChange of the event object 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;
    // assume that it works and returns some kind of representation of the diff
    return diff;
}

Method: 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 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);
}

Exporting Data

Method: createExportReadableStream()

Creates a readable stream for exporting data as json strings where each entry is separated by '\n' (ndjson).

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

Method: exportToJsonFile(destFilePath) (async)

Exports all objects in the table to a ndjson file.

Parameters:

• destFilePath: string - The full path of the file to save to. If the path does not exist, it is created.

Returns:

• {Promise<void>}.

Example:

await personJsonTable.exportToJsonFile('/path/and/filename.ndjson');

Reindexing Index Fields

Method: reindex(paths)

Reindexes the passed in index field paths. If at least one of the fields is a singular index field (array=false) all singular index fields will be reindexed.

As JsonTable#init() automatically reindexes new index fields this method is only needed 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.

Parameters:

• paths: string[] - The index field paths to reindex.

Example:

personJsonTable.reindex(['age', 'children.name']);

Method: reindexAll()

Reindexes all index fields.

As JsonTable#init() automatically reindexes new index fields this method is only needed 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 index fields which requires to be updated.

Example:

personJsonTable.reindexAll();

Class: JsonTableDb

The 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.

Setup

Constructor: 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);

Creating and Initializing New JsonTable Instances

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.

Method: 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', {
    indexFields: [
        { path: "name" }
    ]
});

Method: 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', {
    indexFields: [
        { path: "name" },
        { path: children, array: true }
    ]
});

Method: 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', {
    indexFields: [
        { path: "name" }
    ]
});

jsonTableDb.initJsonTable(personJsonTable);

Method: 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', {
    indexFields: [
        { path: 'name' },
        { path: 'pet', relation: { target: 'pets' } }
    ]
});

let petJsonTable = jsonTableDb.createJsonTable('pets');

jsonTableDb.initAllJsonTables();

Registering Existing JsonTable Instances

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.

Method: 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);

Method: 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);

Retrieving JsonTable Instances

Method: 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', {
    indexFields: [
        { path: 'name' },
        { path: 'pets', array: true }
    ]
});

let personJsonTable = jsonTableDb.getJsonTable('persons');

Method: 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', {
    indexFields: [
        { path: 'name' },
        { path: 'pet', relation: { target: 'pets' } }
    ]
});

jsonTableDb.createAndInitJsonTable('pets');

let jsonTables = json.getAllJsonTables();

Method: 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', {
    indexFields: [
        { path: 'name' },
        { path: 'pets', array: true }
    ]
});

let personJsonTableExists = json.hasJsonTable('persons');

Bidirectional Synchronization

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 path are propagated into the inverse path 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 path must reference a property of the entry being one of: null, an integer > 0, or integer[] with values > 0. The relation path is NOT required to be registered as an index field.

Method: 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.
    • path: string - The path of the relation to sync.
  • inversedBy: object - The complementary relation to synchronize.
    • jsonTableName: string - The name of the JsonTable with the relation.
    • path: string - The path of the relation 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', {
    indexFields: [
        { path: 'name' },
        { path: 'pets', array: true, relation: { target: 'animals' } }
    ]
});

let animalJsonTable = jsonTableDb.createAndInitJsonTable('animals', {
    indexFields: [
        { path: 'name' },
        { path: 'owner', relation: { target: 'persons' } }
    ]
});

jsonTableDb.registerBidirectionalRelationSync({
    relation: {
        jsonTableName: 'persons',
        path: 'pets'
    },
    inversedBy: {
        jsonTableName: 'animals',
        path: '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 });

Method: unregisterBidirectionalRelationSync(jsonTableName, path)

Removes the registered relation synchronization for the path and the inverse path. It is sufficient 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.
• path: string - The path with the relation.

Example:

// Register the bidirectional relation
jsonTableDb.registerBidirectionalRelationSync({
    relation: {
        jsonTableName: 'persons',
        path: 'pets'
    },
    inversedBy: {
        jsonTableName: 'animals',
        path: 'owner'
    }
});

// Unregister the bidirectional relation
jsonTableDb.unregisterBidirectionalRelationSync('persons', 'pets');

// Equal to:
// jsonTableDb.unreg