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!
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; }