JSPM – sqler@9.0.0

Package Exports

sqler
sqler/typedefs

This package does not declare an exports field, so the exports above have been automatically detected and optimized by JSPM instead. If any package subpath is missing, it is recommended to post an issue to the original package (sqler) to support the "exports" field. If that is not possible, create a JSPM override to customize the exports field for this package.

Readme

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:

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