Imago SQL Memory Cache

Caches the entire SQL table in memory, under global.tables, and periodically refreshes it. Use it for small, but frequently accessed tables to improve performance and reduce the SQL server load.

Implementing in your code

Step 1 - Add the package

After installing the package using npm i --save imago-sql-memory-cache, add this to your main Node.js file:

const SqlMemoryCache = require('imago-sql-memory-cache');
let sqlMemoryCache = new SqlMemoryCache();

Step 2 - Define the config

For each SQL table that you want to cache in the memory and refresh automatically, you need to call the add() method once.

It makes sense to do it from your main Node.js file (app.js or server.js);

The config for each table contains four options: connString, table, refreshEvery and callback.

// Example 1:
sqlMemoryCache.add({
    /** The SQL connection string in the mssql://... format */
    connString: process.env.CONNECTION_STRING,

    /** SQL table name */
    table: 'Users_Table',

    /** How often to refresh data from SQL, in milliseconds */
    refreshEvery: 60 * 1000,

    /** A function that processes the data */
    callback: null,
});

// Example 2:
sqlMemoryCache.add({
    connString: process.env.CONNECTION_STRING,
    table: 'Licenses_Table',
    refreshEvery: 60 * 1000,

    /** Postprocess some data to make it easier to get from code */
    callback: async (tableName) => {
      // Convert the array of objects into key-value pairs:
      global.sqlCache[tableName] = {};
      for (let row of global.tables[tableName]) {
        global.sqlCache[tableName][row.licenseId] = row;
      }
    },
});

Step 3 - Access the data

The raw table data fetched from SQL is stored in global.tables[tableName]. For example, you can get the first row of the table like this:

let table = 'Users_Table';
let firstRow = global.tables[table][0];

However, searching for data in the array is not easy. Sometimes it is easier to access the data if you process it after fetching from SQL. In Example 2 above, the data stored in global.tables['Licenses_Table'] is converted into an object, so you can easily acces the data by key, for example:

let key = '371ab8914acd90f31d1ae7410ac410100cd';
let table = 'Licenses_Table';
let licenseKeySettings = global.tables[table][key];

Another thing to consider is that data does not become immediately available after the server is restarted. That means that the first call to your application may fail because the data in global.tables[table] is not loaded from SQL yet. It takes perhaps 1-2 seconds to load the data for the first time.

To avoid that problem, this module saves a promise under global.promises that you can await before trying to access the data.

Example:

// Wrong! Causes an exception on the first call after restarting the server:
let data = global.tables['Users_Table'][0];

// Correct - access to data will be delayed until data is fetched from SQL:
await global.promises['Users_Table'];
let data = global.tables['Users_Table'][0];

Other methods

async refreshOnce(table)

This method refreshes the SQL table immediately (rather than by timer). You can call it, for example, when your code updates the SQL table and you want to fetch the new data immediately.

Example:

// Suppose this code modifies the data in SQL table:
await modifyDataInSqlTable();

// The data here most likely will be old, because we only refresh the data from
// SQL perhaps once per minute:
let oldData = global.tables['My_Table'];

// Get the new data from SQL and save into global.tables:
await sqlMemoryCache.refreshOnce('My_Table');

// At this point, we have the updated SQL data cached in our memory:
let newData = global.tables['My_Table'];