Skip to content
/ sqler Public

🔑Turn your SQL files into executable functions for use in Node.js

License

Notifications You must be signed in to change notification settings

ugate/sqler

Folders and files

NameName
Last commit message
Last commit date

Latest commit

 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 

Repository files navigation

sqler

npm version Build Status Dependency Status Dev Dependency Status

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:

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

About

🔑Turn your SQL files into executable functions for use in Node.js

Topics

Resources

License

Stars

Watchers

Forks

Packages

No packages published