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!

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