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

• 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 fields for performant querying and sorting (including support for nested objects and arrays)
• Query fields 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, 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:

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

Getting Started

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.

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.

DB Configuration

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

General Usage

In the following section, 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 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 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.

Field Indexes and Relations

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

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:

• 1–1 and M-1 relations: null, an integer >= 0 or an object with an id field >= 0
• 1-M and M-M relations: an array of integers >= 0 or an array of objects with an id field >= 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.

Optimizing for Querying and Sorting

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. The JsonTable#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 on JsonTable#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 a reindex() 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] }
    ]
});

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

Creating Entries

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.

Updating Entries

To update an entry, it must have the id field 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 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:

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

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 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 to fieldIndexes so 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, currentDbVersion, maxPatchVersion, {
    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();

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', {
    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.

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

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

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

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

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

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, [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'] });

Method: 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, ... } ]

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.
    • 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' } })

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

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', {
    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"

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

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

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, 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' } }, ...] 

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

Method 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' } }, ...]

Events

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:

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

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 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, 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;
    // We assume that it works and returns a 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 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);
}

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 Field Indexes

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

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

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', {
    fieldIndexes: [
        { field: "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', {
    fieldIndexes: [
        { field: "name" },
        { field: 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', {
    fieldIndexes: [
        { field: "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', {
    fieldIndexes: [
        { field: 'name' },
        { field: '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 I