Imago SQL

Small module for working with SQL (including Azure SQL) in Node.js. Based on the mssql module in NPM.

Installation

npm i --save imago-sql

Usage

const Sql = require('imago-sql');
let sql = new Sql(process.env.DB_CONNECTION_STRING);
let results = await sql.selectAll('My_Table_Name'); // select all records

Important notes

Minimum recommended Node.js version: 8.9.

For best performance and to avoid establishing a new connection for each call, run the constructor at the top level of app.js, cache it in a global variable, and before each SQL call, make sure that the connection has been established.

// app.js
global.sql = new Sql(DB_CONNECTION_STRING);

async function readFromDatabase() {
  // Make sure connection has been established:
  await global.sql.connection;

  // Query database, reusing the global connection pool:
  let results = await global.sql.query('SELECT * FROM Customers');
}

For simplicity, examples below do not reuse a global connection pool.

Methods

Constructor

Example constructor call:

let sql = new Sql(connectionString, options);

The constructor takes the connection string in the URL format:

mssql://login:[email protected]/Database_Name?encrypt=true

Constructor Options

The second argument to the constructor, options, is not required. It must be an object that can contain the following keys:

• debug: print the SQL statements to the console. Default: false.
• swallowExceptions: instead of throwing an exception when an SQL transaction fails, swallow the exception and return null. Default: false.
• engine: Available options are 'azure' and 'mysql'. Default is 'azure'. If the connection string starts with 'mysql://', the engine will be autodetected as mysql.
• templatePath: The folder containing the SQL statement template files. See documentation for getStatement() below.

Sample initialization for MySQL:

let sql = new Sql(connectionString, { engine: 'mysql' });

async selectAll(tableName)

Selects all rows from the specified table.

async query(statement)

Executes an SQL query, returns only the results.

async runSqlQuery(statement)

Same as query(), but returns raw results in mssql's format, meaning the actual results are either under recordset (for single result) or under recordsets (for multiple result sets).

async runParameterizedQuery(statement, parameters)

Executes a query with parameters, which is more efficient and safe.

Example:

const Sql = require('imago-sql');
let sql = new Sql(DB_CONNECTION_STRING);
let statement = `UPDATE My_Table_Name
  SET 
    licenseName=@licenseName,
    description=@description
  WHERE
    id=3`;
let parameters = [
  { name: 'licenseName', type: sql.db.mssql.NVarChar, value: 'Test name' },
  { name: 'description', type: sql.db.mssql.NVarChar, value: 'Sample license' },
];
let results = await sql.runParameterizedQuery(statement, parameters);

The column types, such as NVarChar, are available inside sql.db.mssql. Previously we were using this code which caused unexpected errors due to requiring the mssql package twice:

// Do not do that.
// Require the mssql package again when it's already required inside
// `imago-sql` will cause random errors:
const Azure = require('mssql');

// ...
type = Azure.NVarChar;
// ...

async execute(procedure, parameters)

Executes a stored procedure, optionally with parameters.

Arguments:

• procedure - the name of the stored procedure.
• parameters (optional) - the data object containing the arguments to the procedure. For example, if the stored procedure takes two parameters (name and email), the data object should be { name: 'Anton', email: '[email protected]' }. In other words, keys in the object must be the same as stored procedure parameter names. The data type will be guessed automatically.

Example:

const STORED_PROCEDURE_NAME = 'save_log';
const Sql = require('imago-sql');
let sql = new Sql(DB_CONNECTION_STRING);
let results = await sql.execute(STORED_PROCEDURE_NAME, {
    timestamp: Date.now(),
    ipAddress: '8.8.4.4',
    message: 'test logging',
});

async insertMultipleRows(table, columns, rows)

Inserts multiple rows into a table simultaneously. Returns 'OK' if successful.

• table - The name of the table to insert the data into.
• columns - Names of columns to insert the data into. The order of columns in rows must be the same as here.
• rows - An array of rows, where each row is an array of values in columns. The columns are the same as described in columns.

escapeString(str)

Escapes a string to make it SQL-safe.

async truncate(tableName)

Truncates the specified table (removes all rows, resets the auto-increment counter).

async save(table, data)

Saves the entire object as a row into the database. Tries to guess the parameter types. The object's property names must match the column names.

• table - The name of the SQL table.
• data - An object to be saved as an SQL row.

async getTemplate(name, params)

Reads an SQL statement template from a file, replaces syntax like {{user.name}} with values from inside params, and returns that SQL statement.

The SQL statement templates must be in the folder specified in options.templatePath. The filename should end with .sql or .sql.template.

Example:

const ImagoSql = require('imago-sql');
const sql = new ImagoSql({
    ...
    templatePath: './sql-templates',
});

const params = {
    table: 'Users'
};

// Reads the statement from the file `./sql-templates/get_users.sql`, and
// replaces instances of {{table}} with 'Users':
const statement = await sql.getTemplate('get_users', params);

// Now we can run this statement:
const data = await sql.query(statement);

async bulkInsert(table)

At the moment, this function will just apply on Azure/MS SQL To handle if the program needs to insert huge of data at the same time, then you can create a table object which the schema MUST be same as the table in DB, push the table object into bulkInsert.

Example:

const ImagoSql = require('imago-sql');
const sql = new Sql(CONNECTION_STRING);
const table = new Azure.Table(process.env.TABLE_NAME_PRODUCTS_WHITELIST);
table.create = true;
table.columns.add('field-1', Azure.Int, { nullable: false});
table.columns.add('field-2', Azure.Int, { nullable: false});
...
for (var j = 0; j < data.length; j++){
  table.rows.add(
      data[j]["field-1"], data[j]["field-2"] 
      ....
  );
}
await sql.bulkInsert(table);