sqler
npm install sqler
Skip the ORM and simplify your SQL execution plans using plain 💯% SQL systax.
sqler
is a Node.js manager for RDBMS systems that autogenerates/manages SQL execution functions from underlying SQL statement files. Features include:
- Autogeneration of object paths and prepared statement functions that coincide with SQL file paths
- Debugging options that allow for near real time updates to SQL files without restarting an application
- Expanded SQL substitutions, fragment substitutions, dialect specific substitutions and version specific substitutions
- Simplified transaction management
- Simplified prepared statement management
- Fast read and write streaming support for large reads/writes
- Using SQL vs ORM/API solutions minimizes overhead and maximizes optimal utilization of SQL syntax and DBA interaction and reduces over-fetching that is commonly assocaited with ORM
- Unlike strict ORM/API based solutions, models are generated on the fly- lending itself to a more function centric design
For more details check out the tutorials and API docs!
Usage :
In order to use sqler
a simple implementation of Dialect should be supplied. There are a few that have already been written for a few enteprise level applications that make use of sqler
:
- SQL Server -
sqler-mssql
- Oracle -
sqler-oracle
- MariaDB and/or MySQL -
sqler-mdb
- PostgreSQL -
sqler-postgres
- ODBC -
sqler-odbc
Example Read:
-- db/finance/read.ap.companies.sql
SELECT CO.COMPANY AS "company", CO.R_NAME AS "name", CO.PAY_GROUP AS "payGroup", CO.TAX_ACCOUNT AS "taxAccount", CO.TAX_ACCT_UNIT AS "taxAcctUnit",
CO.TAX_SUB_ACCT AS "taxSubAcct"
FROM APCOMPANY CO
WHERE CO.INVOICE_AUDIT = :invoiceAudit
ORDER BY CO.COMPANY ASC
// replace xxxx with one of the prexisiting vendor implementations
// or roll your own Dialect
const dialect = 'xxxx', dialectModule = `sqler-${dialect}`;
const { Manager } = require('sqler');
const conf = {
"univ": {
"db": {
"myId": {
"host": "myhost.example.com",
"username": "myusername",
"password": "mypassword"
}
}
},
"db": {
"dialects": {
[dialect]: dialectModule
},
"connections": [
{
"id": "myId",
"name": "fin",
"dir": "db/finance",
"service": "MYSRV",
"dialect": dialect
}
]
}
};
const mgr = new Manager(conf);
// initialize connections and set SQL functions
await mgr.init();
console.log('Manager is ready for use');
// execute the SQL statement and capture the results
const rslts = await mgr.db.fin.read.ap.companies({ binds: { invoiceAudit: 'Y' } });
// after we're done using the manager we should close it
process.on('SIGINT', async function sigintDB() {
await mgr.close();
console.log('Manager has been closed');
});
Example Write (with implicit transaction):
-- db/finance/create.ap.companies.sql
INSERT INTO APCOMPANY (COMPANY, R_NAME, PAY_GROUP, TAX_ACCOUNT, TAX_ACCT_UNIT)
VALUES (:company, :name, :payGroup, :taxAccount, :taxAcctUnit);
// using the same setup as the read example...
// execute within the an implicit transaction scope
// (i.e. autoCommit === true w/o transaction)
const rslts = await mgr.db.fin.create.ap.company({
autoCommit: true, // <--- could omit since true is default
binds: {
company: 1,
name: 'Company 1',
payGroup: 'MYCO1',
taxAccount: 1234,
taxAcctUnit: 10000000
}
});
Example Write (with explicit transaction):
-- db/finance/create.ap.companies.sql
INSERT INTO APCOMPANY (COMPANY, R_NAME, PAY_GROUP, TAX_ACCOUNT, TAX_ACCT_UNIT)
VALUES (:company, :name, :payGroup, :taxAccount, :taxAcctUnit);
// using the same setup as the read example...
// autCommit = false will cause a transaction to be started
const coOpts = {
autoCommit: false,
binds: {
company: 1,
name: 'Company 1',
payGroup: 'MYCO1',
taxAccount: 1234,
taxAcctUnit: 10000000
}
};
// autCommit = false will cause a transaction to be continued
const acctOpts = {
autoCommit: false,
binds: {
company: 2,
name: 'Company 2',
payGroup: 'MYCO2',
taxAccount: 5678,
taxAcctUnit: 20000000
}
};
let tx;
try {
// start a transaction
tx = await mgr.db.fin.beginTransaction();
// set the transaction ID on the execution options
// so the company/account SQL execution is invoked
// within the same transaction scope
coOpts.transactionId = tx.id;
acctOpts.transactionId = tx.id;
// execute within the a transaction scope
// (i.e. autoCommit === false and transaction = tx)
const exc1 = await mgr.db.fin.create.ap.company(coOpts);
// execute within the same transaction scope
// (i.e. autoCommit === false and transaction = tx)
const exc2 = await mgr.db.fin.create.ap.account(acctOpts);
// use the transaction to commit the changes
await tx.commit();
} catch (err) {
if (tx) {
// use the transaction to rollback the changes
await tx.rollback();
}
throw err;
}