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