Usage

💡 SQL Server Examples:

Examples:

The examples below use the following setup:

Private Options Configuration: (appended to the subsequent connection options)

{
  "univ": {
    "db": {
      "mssql": {
        "host": "sqler_mssql",
        "username":"sa",
        "password": "sqlS3rv35local"
      }
    }
  }
}

Connection Options Configuration:

{
  "db": {
    "dialects": {
      "mssql": "sqler-mssql"
    },
    "connections": [
      {
        "id": "mssql",
        "name": "mssql",
        "dir": "db/mssql",
        "service": "MSSQL",
        "dialect": "mssql",
        "pool": {},
        "driverOptions": {
          "connection": {
            "options": {
              "encrypt": true,
              "trustServerCertificate": true,
              "enableArithAbort": true
            }
          }
        }
      }
    ]
  }
}

Test code that illustrates how to use SQL Server with various examples

// assuming "conf" contains combined "univ" and "db" objects from above

// create/initialize manager
const manager = new Manager(conf);
await manager.init();

// see subsequent examples for different examples
const result = await runExample(manager, 'mssql');

console.log('Result:', result);

// after we're done using the manager we should close it
process.on('SIGINT', async function sigintDB() {
  await manager.close();
  console.log('Manager has been closed');
});

Create Schema:

-- db/mssql/setup/create.database.sql
CREATE SCHEMA sqlermssql
'use strict';

// export just to illustrate module usage
module.exports = async function runExample(manager, connName) {

  // create the database and/or schema
  return manager.db[connName].setup.create.database();
};

Create Table(s):

-- db/mssql/setup/create.table1.sql
CREATE TABLE sqlermssql.TEST ("ID" integer not null primary key, "NAME" varchar(255), "CREATED_AT" datetime2, "UPDATED_AT" datetime2)
-- db/mssql/setup/create.table2.sql
CREATE TABLE sqlermssql.TEST2 ("ID" integer not null primary key, "NAME" varchar(255), "REPORT" varbinary(max), "CREATED_AT" datetime2, "UPDATED_AT" datetime2)
'use strict';

// export just to illustrate module usage
module.exports = async function runExample(manager, connName) {

  // create the tables (in parallel)
  return Promise.all([
    manager.db[connName].setup.create.table1(),
    manager.db[connName].setup.create.table2()
  ]);
};

Create Rows:

-- db/mssql/create.table1.rows.sql
INSERT INTO sqlermssql.TEST (ID, NAME, CREATED_AT, UPDATED_AT)
VALUES (:id, :name, :created, :updated)
-- db/mssql/create.table2.rows.sql
INSERT INTO sqlermssql.TEST2 (ID, NAME, REPORT, CREATED_AT, UPDATED_AT)
VALUES (:id2, :name2, :report2, :created2, :updated2)
'use strict';

const Fs = require('fs');

// export just to illustrate module usage
module.exports = async function runExample(manager, connName) {

  const date = new Date(), rtn = new Array(2);
  // The driver module currently doesn't support Fs.ReadStream/Fs.createReadStream()
  const report = await Fs.promises.readFile('./test/files/audit-report.png');

  // Insert rows (implicit transactions)
  rtn[0] = await manager.db[connName].create.table1.rows({
    binds: {
      id: 1, name: 'TABLE: 1, ROW: 1', created: date, updated: date
    }
  });
  rtn[1] = await manager.db[connName].create.table2.rows({
    binds: {
      id2: 1, name2: 'TABLE: 2, ROW: 1', report2: report, created2: date, updated2: date
    }
  });

  return rtn;
};

Read Rows:

-- db/mssql/read.table.rows.sql
SELECT TST.ID AS "id", TST.NAME AS "name", NULL AS "report",
TST.CREATED_AT AS "created", TST.UPDATED_AT AS "updated"
FROM sqlermssql.TEST TST
WHERE UPPER(TST.NAME) LIKE CONCAT(CONCAT('%', UPPER(:name)), '%') 
UNION
SELECT TST2.ID AS "id", TST2.NAME AS "name", /*CONVERT(varchar(max), TST2.REPORT, 2)*/TST2.REPORT AS "report",
TST2.CREATED_AT AS "created", TST2.UPDATED_AT AS "updated"
FROM sqlermssql.TEST2 TST2
WHERE UPPER(TST2.NAME) LIKE CONCAT(CONCAT('%', UPPER(:name)), '%')
'use strict';

const Os = require('os');
const Fs = require('fs');

// export just to illustrate module usage
module.exports = async function runExample(manager, connName) {

  // read from multiple tables
  const rslt = await manager.db[connName].read.table.rows({
    binds: { name: 'table' },
    driverOptions: {
      // output bind types are optional
      outputBindTypes: {
        id: '${Int}'
      }
    }
  });

  // write binary report buffer to file?
  const writeProms = [];
  for (let row of rslt.rows) {
    if (row.report) {
      // store the path to the report (illustrative purposes only)
      row.reportPath = `${Os.tmpdir()}/sqler-${connName}-read-${row.id}.png`;
      writeProms.push(Fs.promises.writeFile(row.reportPath, row.report));
    }
  }
  if (writeProms.length) {
    await Promise.all(writeProms);
  }

  return rslt;
};

Update Rows:

-- db/mssql/update.table1.rows.sql
UPDATE sqlermssql.TEST
SET NAME = :name, UPDATED_AT = :updated
WHERE ID = :id

-- db/mssql/update.table2.rows.sql
UPDATE sqlermssql.TEST2
SET NAME = :name2, UPDATED_AT = :updated2
WHERE ID = :id2
'use strict';

// export just to illustrate module usage
module.exports = async function runExample(manager, connName) {

  const date = new Date();

  // binds
  const binds1 = {
    id: 1, name: 'TABLE: 1, ROW: 1 (UPDATE)', updated: date
  };
  const binds2 = {
    id2: 1, name2: 'TABLE: 2, ROW: 1 (UPDATE)', updated2: date
  };
  // mssql bind types (values interpolated from the mssql module)
  const inputBindTypes1 = {
    id: "${Int}",
    name: "${VarChar}",
    updated: "${DateTime}"
  };
  const rtn = {};

  //-------------------------------------------------------
  // There are two different ways to perform a transaction
  // 1. Implicit (suitable for a single execution per tx)
  // 2. Explicit (suitable for multiple executions per tx)

  // using implicit transactions:
  await implicitTransactionUpdate(manager, connName, binds1, binds2, rtn);

  // Using an explicit transaction:
  await explicitTransactionUpdate(manager, connName, binds1, binds2, rtn);

  // Using a prepared statement:
  await preparedStatementUpdate(manager, connName, binds1, inputBindTypes1, rtn);

  // Using a prepared statement within an explicit transaction
  await preparedStatementExplicitTxUpdate(manager, connName, binds1, inputBindTypes1, rtn);

  return rtn;
};

async function implicitTransactionUpdate(manager, connName, binds1, binds2, rtn) {
  rtn.txImpRslts = new Array(2); // don't exceed connection pool count

  // Example execution in parallel using an implicit transaction for
  // each SQL execution (autoCommit = true is the default)
  // NOTE: Internally, transactions are ran in series since that is the
  // contract definition, but for API compatibility they can be ran in
  // parallel from a Manager perspective
  rtn.txImpRslts[0] = manager.db[connName].update.table1.rows({
    name: 'TX Implicit 1', // name is optional
    binds: binds1
  });
  rtn.txImpRslts[1] = manager.db[connName].update.table2.rows({
    name: 'TX Implicit 2', // name is optional
    binds: binds2
  });
  // could have also ran is series by awaiting when the SQL function is called
  rtn.txImpRslts[0] = await rtn.txImpRslts[0];
  rtn.txImpRslts[1] = await rtn.txImpRslts[1];
}

async function explicitTransactionUpdate(manager, connName, binds1, binds2, rtn) {
  rtn.txExpRslts = new Array(2); // don't exceed connection pool count
  let tx;
  try {
    // start a transaction
    tx = await manager.db[connName].beginTransaction({
      // illustrate override of isolation level
      // (optional, interpolated from the mssql module)
      isolationLevel: "${SERIALIZABLE}"
    });

    // Example execution in parallel (same transacion)
    // NOTE: Internally, transactions are ran in series since that is the
    // contract definition, but for API compatibility they can be ran in
    // parallel from a Manager perspective
    rtn.txExpRslts[0] = manager.db[connName].update.table1.rows({
      name: 'TX Explicit 1', // name is optional
      autoCommit: false,
      transactionId: tx.id, // ensure execution takes place within transaction
      binds: binds1
    });
    rtn.txExpRslts[1] = manager.db[connName].update.table2.rows({
      name: 'TX Explicit 2', // name is optional
      autoCommit: false,
      transactionId: tx.id, // ensure execution takes place within transaction
      binds: binds2
    });
    // could have also ran is series by awaiting when the SQL function is called
    rtn.txExpRslts[0] = await rtn.txExpRslts[0];
    rtn.txExpRslts[1] = await rtn.txExpRslts[1];

    // commit the changes
    await tx.commit();
  } catch (err) {
    if (tx) {
      // rollback the changes
      await tx.rollback();
    }
    throw err;
  }
}

async function preparedStatementUpdate(manager, connName, binds, inputBindTypes, rtn) {
  rtn.psRslts = new Array(2); // don't exceed connection pool count
  try {
    for (let i = 0; i < rtn.psRslts.length; i++) {
      // update with expanded name
      binds.name = `Prepared statement iteration #${i}`;
      // Using an implicit transcation (autoCommit defaults to true):
      rtn.psRslts[i] = manager.db[connName].update.table1.rows({
        name: `PS ${i}`, // name is optional
        // flag the SQL execution as a prepared statement
        // this will cause the statement to be prepared
        // and a dedicated connection to be allocated from
        // the pool just before the first SQL executes
        prepareStatement: true,
        driverOptions: {
          // on the first prepared statement call the
          // statement will be registered and a
          // dedicated connection will be allocated
          inputBindTypes
        },
        // include the bind parameters
        binds
      });
    }
    // wait for parallel executions to complete
    for (let i = 0; i < rtn.psRslts.length; i++) {
      rtn.psRslts[i] = await rtn.psRslts[i];
    }
  } finally {
    // could call unprepare using any of the returned execution results
    if (rtn.psRslts[0] && rtn.psRslts[0].unprepare) {
      // since prepareStatement = true, we need to close the statement
      // and release the statement connection back to the pool
      await rtn.psRslts[0].unprepare();
    }
  }
}

async function preparedStatementExplicitTxUpdate(manager, connName, binds, inputBindTypes, rtn) {
  rtn.txExpPsRslts = new Array(2); // don't exceed connection pool count
  let tx;
  try {
    // start a transaction
    tx = await manager.db[connName].beginTransaction();

    for (let i = 0; i < rtn.txExpPsRslts.length; i++) {
      // update with expanded name
      binds.name += `Prepared statement with transactionId "${tx.id}" iteration #${i}`;
      rtn.txExpPsRslts[i] = manager.db[connName].update.table1.rows({
        name: `TX/PS ${i}`, // name is optional
        autoCommit: false, // don't auto-commit after execution
        transactionId: tx.id, // ensure execution takes place within transaction
        prepareStatement: true, // ensure a prepared statement is used
        driverOptions: {
          inputBindTypes, // required mssql bind types for prepared statements
          outputBindTypes: inputBindTypes // optional
        },
        binds
      });
    }
    // wait for parallel executions to complete
    for (let i = 0; i < rtn.txExpPsRslts.length; i++) {
      rtn.txExpPsRslts[i] = await rtn.txExpPsRslts[i];
    }

    // unprepare will be called when calling commit
    // (alt, could have called unprepare before commit)
    await tx.commit();
  } catch (err) {
    if (tx) {
      // unprepare will be called when calling rollback
      // (alt, could have called unprepare before rollback)
      await tx.rollback();
    }
    throw err;
  }
}

Delete Rows:

-- db/mssql/delete.table1.rows.sql
DELETE FROM sqlermssql.TEST
WHERE ID = :id
-- db/mssql/delete.table2.rows.sql
DELETE FROM sqlermssql.TEST2
WHERE ID = :id2
'use strict';

// export just to illustrate module usage
module.exports = async function runExample(manager, connName) {

  const rtn = new Array(2);

  // delete rows (implicit transactions)
  rtn[0] = await manager.db[connName].delete.table1.rows({
    binds: { id: 1 }
  });
  rtn[1] = await manager.db[connName].delete.table2.rows({
    binds: { id2: 1 }
  });

  return rtn;
};

Delete Tables:

-- db/mssql/setup/delete.table1.sql
DROP TABLE sqlermssql.TEST
-- db/mssql/setup/delete.table2.sql
DROP TABLE sqlermssql.TEST2
'use strict';

// export just to illustrate module usage
module.exports = async function runExample(manager, connName) {

  // delete the tables (in parallel)
  return Promise.all([
    manager.db[connName].setup.delete.table1(),
    manager.db[connName].setup.delete.table2()
  ]);
};

Delete Schema:

-- db/mssql/setup/delete.database.sql
DROP SCHEMA sqlermssql
'use strict';

// export just to illustrate module usage
module.exports = async function runExample(manager, connName) {

  // delete the database and/or schema
  return manager.db[connName].setup.delete.database();
};

3.0.0 (2021-05-07)

Full Changelog

Breaking Changes: