💡 SQL Server:
Alternatively, sqler-mssql can be used instead of ODBC.
Setup:
Install the SQL Server ODBC Drivers
Windows ODBC Data Source
Configure the ODBC driver via the ODBC Data Source Administrator:
UNIX /etc/odbc.ini unixODBC (SQL Server ODBC Connection Parameters)
[ODBC Data Sources]
SqlServerXE=SqlServerXE
[SqlServerXE]
Driver = ODBC Driver SQL Server
Server = 127.0.0.1
UID = sa
PWD = sqlS3rv35local
Examples:
The examples below use the following setup:
Private Options Configuration: (appended to the subsequent connection options, shows other connections for illustration purposes)
{
"univ": {
"db": {
"mssql": {
"username":"sa",
"password": "sqlS3rv35local"
},
"oracle": {
"username": "system",
"password": "oracl3local"
},
"mysql": {
"username":"root",
"password": "my3qllocal"
},
"postgresql": {
"username":"postgres",
"password": "p0stgr3slocal"
},
"cache": {}
}
}
}
Connection Options Configuration:
{
"db": {
"dialects": {
"odbc": "sqler-odbc"
},
"connections": [
{
"id": "mssql",
"name": "mssql",
"dir": "db/mssql",
"service": "SqlServer",
"dialect": "odbc",
"pool": {},
"driverOptions": {
"connection": {
"DSN": "${service}",
"UID": "${username}",
"PWD": "${password}"
},
"pool": {
"shrink": true
}
}
}
]
}
}
NOTE:
db.connections.driverOptions.connectionforDSNinterpolates intodb.connections[].servicewhileUIDandPWDinterpolate to properties onuniv.db.mssqlset on the private options configuration. A complete listing of available SQL Server connection string attributes for use onoptions.driverOptions.connectioncan be found here.
Test code that illustrates how to use SQL Server + ODBC with various examples
// assuming "conf" contains combined "univ" and "db" objects from above
// create/initialize ODBC 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 Table:
-- db/mssql/setup/create.tables.sql
CREATE TABLE "dbo.TEST" ("ID" integer not null primary key, "NAME" varchar(384), "CREATED_AT" datetime2, "UPDATED_AT" datetime2);
CREATE TABLE "dbo.TEST2" ("ID" integer not null primary key, "NAME" varchar(384), "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 multiple tables in a single execution
const rslt = await manager.db[connName].setup.create.tables();
return rslt;
};
Create Rows:
-- db/mssql/create.table.rows.sql
INSERT INTO "dbo.TEST" (ID, NAME, CREATED_AT, UPDATED_AT)
VALUES (:id, :name, :created, :updated);
INSERT INTO "dbo.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) {
// The odbc module needs the date to be in a valid ANSI compliant format.
// Could also use:
// https://www.npmjs.com/package/moment-db
const date = new Date().toISOString().replace('T', ' ').replace('Z', '');
// The odbc module currently doesn't support Fs.ReadStream/Fs.createReadStream()
const report = await Fs.promises.readFile('./test/files/audit-report.png');
// Insert rows into multiple tables within a single ODBC execution
const rslt = await manager.db[connName].create.table.rows({
binds: {
id: 1, name: 'TABLE: 1, ROW: 1', created: date, updated: date,
id2: 1, name2: 'TABLE: 2, ROW: 1', report2: report, created2: date, updated2: date
}
});
return rslt;
};
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 "dbo.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, 1) AS "report",
TST2.CREATED_AT AS "created", TST2.UPDATED_AT AS "updated"
FROM "dbo.TEST2" TST2
WHERE UPPER(TST2.NAME) LIKE CONCAT(CONCAT('%', UPPER(:name)), '%')
'use strict';
// export just to illustrate module usage
module.exports = async function runExample(manager, connName) {
const rslt = await manager.db[connName].read.table.rows({ binds: { name: 'table' } });
return rslt;
};
Update Rows:
Although the following updates can be made within a single SQL script, multiple SQL scripts are used to illustrate the use of tranactions and/or prepared statements.
-- db/mssql/update.table1.rows.sql
UPDATE "dbo.TEST"
SET NAME = :name, UPDATED_AT = :updated
WHERE ID = :id;
-- db/mssql/update.table2.rows.sql
UPDATE "dbo.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) {
// The odbc module needs the date to be in a valid ANSI compliant format.
// Could also use:
// https://www.npmjs.com/package/moment-db
const date = new Date().toISOString().replace('T', ' ').replace('Z', '');
// 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
};
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:
console.log(`---------------------------- Implicit Transaction Update ----------------------------`);
await implicitTransactionUpdate(manager, connName, binds1, binds2, rtn);
// Using an explicit transaction:
console.log(`---------------------------- Explicit Transaction Update ----------------------------`);
await explicitTransactionUpdate(manager, connName, binds1, binds2, rtn);
// Using a prepared statement:
console.log(`---------------------------- Prepared Statement Update ----------------------------`);
await preparedStatementUpdate(manager, connName, binds1, rtn);
// Using a prepared statement within an explicit transaction
console.log(`---------------------------- Prepared Statement Explicit Transaction Update ----------------------------`);
await preparedStatementExplicitTxUpdate(manager, connName, binds1, 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)
rtn.txImpRslts[0] = manager.db[connName].update.table1.rows({
name: 'TX Implicit 1 (UPDATE)', // name is optional
binds: binds1
});
rtn.txImpRslts[1] = manager.db[connName].update.table2.rows({
name: 'TX Implicit 2 (UPDATE)', // 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
try {
// start a transaction
const txId = await manager.db[connName].beginTransaction();
// Example execution in parallel (same transacion)
// NOTE: SQL Server ODBC driver may crash app if ran in parallel
rtn.txExpRslts[0] = await manager.db[connName].update.table1.rows({
name: 'TX Explicit 1 (UPDATE)', // name is optional
autoCommit: false,
transactionId: txId, // ensure execution takes place within transaction
binds: binds1
});
rtn.txExpRslts[1] = await manager.db[connName].update.table2.rows({
name: 'TX Explicit 2 (UPDATE)', // name is optional
autoCommit: false,
transactionId: txId, // ensure execution takes place within transaction
binds: binds2
});
// rtn.txExpRslts[0] = await rtn.txExpRslts[0];
// rtn.txExpRslts[1] = await rtn.txExpRslts[1];
// could commit using either one of the returned results
await rtn.txExpRslts[0].commit();
} catch (err) {
if (rtn.txExpRslts[0] && rtn.txExpRslts[0].rollback) {
// could rollback using either one of the returned results
await rtn.txExpRslts[0].rollback();
}
throw err;
}
}
async function preparedStatementUpdate(manager, connName, binds, 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 = `TABLE: 1, ROW: ${i} (Prepared statement UPDATE)`;
// Using an implicit transcation (autoCommit defaults to true):
rtn.psRslts[i] = manager.db[connName].update.table1.rows({
name: `PS ${i} (UPDATE)`, // 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,
// 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, rtn) {
rtn.txExpPsRslts = new Array(3); // don't exceed connection pool count
try {
// start a transaction
const txId = await manager.db[connName].beginTransaction();
for (let i = 0; i < rtn.txExpPsRslts.length; i++) {
// update with expanded name
binds.name += `TABLE: 1, ROW: ${i} (Prepared statement with txId "${txId}" UPDATE)`;
rtn.txExpPsRslts[i] = manager.db[connName].update.table1.rows({
name: `TX/PS ${i} (UPDATE)`, // name is optional
autoCommit: false, // don't auto-commit after execution
transactionId: txId, // ensure execution takes place within transaction
prepareStatement: true, // ensure a prepared statement is used
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 rtn.txExpPsRslts[0].commit();
} catch (err) {
if (rtn.txExpPsRslts[0] && rtn.txExpPsRslts[0].rollback) {
// unprepare will be called when calling rollback
// (alt, could have called unprepare before rollback)
await rtn.txExpPsRslts[0].rollback();
}
throw err;
}
}
Delete Rows:
-- db/mssql/delete.table.rows.sql
DELETE FROM "dbo.TEST"
WHERE ID = :id;
DELETE FROM "dbo.TEST2"
WHERE ID = :id2;
'use strict';
// export just to illustrate module usage
module.exports = async function runExample(manager, connName) {
// Delete rows from multiple tables within a single ODBC execution
const rslt = await manager.db[connName].delete.table.rows({
binds: { id: 1, id2: 1 }
});
return rslt;
};
Delete Table:
-- db/mssql/setup/delete.tables.sql
DROP TABLE "dbo.TEST";
DROP TABLE "dbo.TEST2";
'use strict';
// export just to illustrate module usage
module.exports = async function runExample(manager, connName) {
// delete multiple tables in a single execution
const rslt = await manager.db[connName].setup.delete.tables();
return rslt;
};