`createMsSqlPersister`

The createMsSqlPersister function creates an MsSqlPersister object that can persist the Store to a SQL Server database via the mssql module.

createMsSqlPersister(
  store: Store | MergeableStore,
  mssql: ConnectionPool,
  configOrStoreTableName?: string | DpcJson,
  onSqlCommand?: (sql: string, params?: any[]) => void,
  onIgnoredError?: (error: any) => void,
): Promise<MsSqlPersister>

	Type	Description
`store`	`Store \| MergeableStore`	The `Store` or `MergeableStore` to persist.
`mssql`	`ConnectionPool`	The `mssql` ConnectionPool that identifies the database connection.
`configOrStoreTableName?`	`string \| DpcJson`	A `DpcJson` object to configure the persistence (or a string to set its `storeTableName` property).
`onSqlCommand?`	`(sql: string, params?: any[]) => void`	An optional handler called every time the `Persister` executes a SQL command or query. This is suitable for logging persistence behavior in a development environment.
`onIgnoredError?`	`(error: any) => void`	An optional handler for the errors that the `Persister` would otherwise ignore when trying to save or load data. This is suitable for debugging persistence issues in a development environment.
returns	`Promise<MsSqlPersister>`	A reference to the new `MsSqlPersister` object.

An MsSqlPersister supports regular Store objects, and can also be used to persist the metadata of a MergeableStore, since it uses the JSON serialization mode.

As well as providing a reference to the Store to persist, you must provide an mssql parameter which is a ConnectionPool. The Persister issues its transactions on connections taken from that pool, so make sure it is large enough to accommodate the rest of your application too.

The third argument is a DpcJson object that configures the table and column names used for the serialization. If it is simply a string, it is used as the storeTableName property instead. Unlike the PostgreSQL and SQLite Persisters, the tabular mode is not yet supported here, and a DpcTabular configuration will be rejected.

Automatic loading polls a rowversion column that the Persister adds to its table. SQL Server maintains that column itself on every insert and update, including ones made by other clients, so changes made outside of TinyBase are picked up too. Use the autoLoadIntervalSeconds property of the configuration to control how often it is checked.

This method is asynchronous. You will need to await a call to this function or handle the return type natively as a Promise.

Example

This example creates an MsSqlPersister object and persists the Store to a local SQL Server database as a JSON serialization into the my_tinybase table. It makes a change to the database directly and then reloads it back into the Store.

import {connect} from 'mssql';
import {createStore} from 'tinybase';
import {createMsSqlPersister} from 'tinybase/persisters/persister-mssql';

const pool = await connect('Server=localhost,1433;Database=tinybase');
const store = createStore().setTables({pets: {fido: {species: 'dog'}}});
const persister = await createMsSqlPersister(store, pool, 'my_tinybase');

await persister.save();
// Store will be saved to the database.

console.log(
  (await pool.request().query('SELECT * FROM my_tinybase;')).recordset,
);
// -> [{_id: '_', store: '[{"pets":{"fido":{"species":"dog"}}},{}]'}]

await pool
  .request()
  .input('store', '[{"pets":{"felix":{"species":"cat"}}},{}]')
  .input('id', '_')
  .query('UPDATE my_tinybase SET store = @store WHERE _id = @id;');

await persister.load();
console.log(store.getTables());
// -> {pets: {felix: {species: 'cat'}}}

await persister.destroy();
await pool.request().query('DROP TABLE IF EXISTS my_tinybase;');
await pool.close();

Since

10.0.0