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
• 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!

• Tutorials
• 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
• Oracle
• ODBC

Example:

-- 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 mrg.close();
  console.log('Manager has been closed');
});