Usage

💡 PostgreSQL Examples:

Examples:

The examples below use the following setup:

Private Options Configuration: (appended to the subsequent connection options)

{
  "univ": {
    "db": {
      "postgres": {
        "host": "sqler_postgres",
        "username":"postgres",
        "password": "p0stgr3slocal"
      }
    }
  }
}

Connection Options Configuration:

{
  "db": {
    "dialects": {
      "postgres": "sqler-postgres"
    },
    "connections": [
      {
        "id": "postgres",
        "name": "postgres",
        "dir": "db/postgres",
        "service": "PostgreSQL",
        "dialect": "postgres",
        "pool": {},
        "driverOptions": {
          "connection": {
            "multipleStatements": true
          }
        }
      }
    ]
  }
}

Test code that illustrates how to use PostgreSQL with various examples

// assuming "conf" contains combined "univ" and "db" objects from above

// create/initialize manager
const manager = new Manager(conf);
await manager.init();

// see subsequent examples for different examples
const result = await runExample(manager, 'postgres');

console.log('Result:', result);

// after we're done using the manager we should close it
process.on('SIGINT', async function sigintDB() {
  await manager.close();
  console.log('Manager has been closed');
});

Create Schema:

-- db/postgres/setup/create.database.sql
CREATE SCHEMA IF NOT EXISTS sqlerpg

'use strict';

// export just to illustrate module usage
module.exports = async function runExample(manager, connName) {

  // create the database and/or schema
  return manager.db[connName].setup.create.database();
};

Create Table(s):

-- db/postgres/setup/create.table1.sql
CREATE TABLE IF NOT EXISTS sqlerpg.TEST (ID INTEGER NOT NULL PRIMARY KEY, NAME VARCHAR(255), CREATED_AT TIMESTAMP(3) WITH TIME ZONE, UPDATED_AT TIMESTAMP(3) WITH TIME ZONE)

-- db/postgres/setup/create.table2.sql
CREATE TABLE IF NOT EXISTS sqlerpg.TEST2 (ID INTEGER NOT NULL PRIMARY KEY, NAME VARCHAR(255), REPORT BYTEA, CREATED_AT TIMESTAMP(3) WITH TIME ZONE, UPDATED_AT TIMESTAMP(3) WITH TIME ZONE)

'use strict';

// export just to illustrate module usage
module.exports = async function runExample(manager, connName) {

  // create the tables (in parallel)
  return Promise.all([
    manager.db[connName].setup.create.table1(),
    manager.db[connName].setup.create.table2()
  ]);
};

Create Rows:

-- db/postgres/create.table1.rows.sql
INSERT INTO sqlerpg.TEST (ID, NAME, CREATED_AT, UPDATED_AT)
VALUES (:id, :name, :created, :updated)

-- db/postgres/create.table2.rows.sql
INSERT INTO sqlerpg.TEST2 (ID, NAME, REPORT, CREATED_AT, UPDATED_AT)
VALUES (:id2, :name2, :report2, :created2, :updated2)

'use strict';

const Fs = require('fs');

// export just to illustrate module usage
module.exports = async function runExample(manager, connName) {

  const date = new Date(), rtn = new Array(2);
  // The driver module currently doesn't support Fs.ReadStream/Fs.createReadStream()
  const report = await Fs.promises.readFile('./test/files/audit-report.png');

  // Insert rows (implicit transactions)
  rtn[0] = await manager.db[connName].create.table1.rows({
    binds: {
      id: 1, name: 'TABLE: 1, ROW: 1', created: date, updated: date
    }
  });
  rtn[1] = await manager.db[connName].create.table2.rows({
    binds: {
      id2: 1, name2: 'TABLE: 2, ROW: 1', report2: report, created2: date, updated2: date
    }
  });

  return rtn;
};

Read Rows:

-- db/postgres/read.table.rows.sql
SELECT TST.ID AS "id", TST.NAME AS "name", NULL AS "report",
TST.CREATED_AT AS "created", TST.UPDATED_AT AS "updated"
FROM sqlerpg.TEST TST
WHERE UPPER(TST.NAME) LIKE CONCAT(CONCAT('%', UPPER(:name)), '%') 
UNION
SELECT TST2.ID AS "id", TST2.NAME AS "name", TST2.REPORT AS "report",
TST2.CREATED_AT AS "created", TST2.UPDATED_AT AS "updated"
FROM sqlerpg.TEST2 TST2
WHERE UPPER(TST2.NAME) LIKE CONCAT(CONCAT('%', UPPER(:name)), '%')

'use strict';

const Os = require('os');
const Fs = require('fs');

// export just to illustrate module usage
module.exports = async function runExample(manager, connName) {

  // read from multiple tables
  const rslt = await manager.db[connName].read.table.rows({ binds: { name: 'table' } });

  // write binary report buffer to file?
  const writeProms = [];
  for (let row of rslt.rows) {
    if (row.report) {
      // store the path to the report (illustrative purposes only)
      row.reportPath = `${Os.tmpdir()}/sqler-${connName}-read-${row.id}.png`;
      writeProms.push(Fs.promises.writeFile(row.reportPath, row.report));
    }
  }
  if (writeProms.length) {
    await Promise.all(writeProms);
  }

  return rslt;
};

Update Rows:

-- db/postgres/update.table1.rows.sql
UPDATE sqlerpg.TEST
SET NAME = :name, UPDATED_AT = :updated
WHERE ID = :id

-- db/postgres/update.table2.rows.sql
UPDATE sqlerpg.TEST2
SET NAME = :name2, UPDATED_AT = :updated2
WHERE ID = :id2

'use strict';

// export just to illustrate module usage
module.exports = async function runExample(manager, connName) {

  const date = new Date();

  // binds
  const binds1 = {
    id: 1, name: 'TABLE: 1, ROW: 1 (UPDATE)', updated: date
  };
  const binds2 = {
    id2: 1, name2: 'TABLE: 2, ROW: 1 (UPDATE)', updated2: date
  };
  const rtn = {};

  //-------------------------------------------------------
  // There are two different ways to perform a transaction
  // 1. Implicit (suitable for a single execution per tx)
  // 2. Explicit (suitable for multiple executions per tx)

  // using implicit transactions:
  await implicitTransactionUpdate(manager, connName, binds1, binds2, rtn);

  // Using an explicit transaction:
  await explicitTransactionUpdate(manager, connName, binds1, binds2, rtn);

  // Using a prepared statement:
  await preparedStatementUpdate(manager, connName, binds1, rtn);

  // Using a prepared statement within an explicit transaction
  await preparedStatementExplicitTxUpdate(manager, connName, binds1, rtn);

  return rtn;
};

async function implicitTransactionUpdate(manager, connName, binds1, binds2, rtn) {
  rtn.txImpRslts = new Array(2); // don't exceed connection pool count

  // Example execution in parallel using an implicit transaction for
  // each SQL execution (autoCommit = true is the default)
  rtn.txImpRslts[0] = manager.db[connName].update.table1.rows({
    name: 'TX Implicit 1 (UPDATE)', // name is optional
    binds: binds1
  });
  rtn.txImpRslts[1] = manager.db[connName].update.table2.rows({
    name: 'TX Implicit 2 (UPDATE)', // name is optional
    binds: binds2
  });
  // could have also ran is series by awaiting when the SQL function is called
  rtn.txImpRslts[0] = await rtn.txImpRslts[0];
  rtn.txImpRslts[1] = await rtn.txImpRslts[1];
}

async function explicitTransactionUpdate(manager, connName, binds1, binds2, rtn) {
  rtn.txExpRslts = new Array(2); // don't exceed connection pool count
  let tx;
  try {
    // start a transaction
    tx = await manager.db[connName].beginTransaction();

    // Example execution in parallel (same transacion)
    rtn.txExpRslts[0] = manager.db[connName].update.table1.rows({
      name: 'TX Explicit 1 (UPDATE)', // name is optional
      autoCommit: false,
      transactionId: tx.id, // ensure execution takes place within transaction
      binds: binds1
    });
    rtn.txExpRslts[1] = manager.db[connName].update.table2.rows({
      name: 'TX Explicit 2 (UPDATE)', // name is optional
      autoCommit: false,
      transactionId: tx.id, // ensure execution takes place within transaction
      binds: binds2
    });
    // could have also ran is series by awaiting when the SQL function is called
    rtn.txExpRslts[0] = await rtn.txExpRslts[0];
    rtn.txExpRslts[1] = await rtn.txExpRslts[1];

    // commit the transaction
    await tx.commit();
  } catch (err) {
    if (tx) {
      // rollback the transaction
      await tx.rollback();
    }
    throw err;
  }
}

async function preparedStatementUpdate(manager, connName, binds, rtn) {
  rtn.psRslts = new Array(2); // don't exceed connection pool count
  try {
    for (let i = 0; i < rtn.psRslts.length; i++) {
      // update with expanded name
      binds.name = `TABLE: 1, ROW: ${i} (Prepared statement UPDATE)`;
      // Using an implicit transcation (autoCommit defaults to true):
      rtn.psRslts[i] = manager.db[connName].update.table1.rows({
        name: `PS ${i} (UPDATE)`, // name is optional
        // flag the SQL execution as a prepared statement
        // this will cause the statement to be prepared
        // and a dedicated connection to be allocated from
        // the pool just before the first SQL executes
        prepareStatement: true,
        // include the bind parameters
        binds
      });
    }
    // wait for parallel executions to complete
    for (let i = 0; i < rtn.psRslts.length; i++) {
      rtn.psRslts[i] = await rtn.psRslts[i];
    }
  } finally {
    // could call unprepare using any of the returned execution results
    if (rtn.psRslts[0] && rtn.psRslts[0].unprepare) {
      // since prepareStatement = true, we need to close the statement
      // and release the statement connection back to the pool
      await rtn.psRslts[0].unprepare();
    }
  }
}

async function preparedStatementExplicitTxUpdate(manager, connName, binds, rtn) {
  rtn.txExpPsRslts = new Array(2); // don't exceed connection pool count
  let tx;
  try {
    // start a transaction
    tx = await manager.db[connName].beginTransaction();

    for (let i = 0; i < rtn.txExpPsRslts.length; i++) {
      // update with expanded name
      binds.name += `TABLE: 1, ROW: ${i} (Prepared statement with txId "${tx.id}" UPDATE)`;
      rtn.txExpPsRslts[i] = manager.db[connName].update.table1.rows({
        name: `TX/PS ${i} (UPDATE)`, // name is optional
        autoCommit: false, // don't auto-commit after execution
        transactionId: tx.id, // ensure execution takes place within transaction
        prepareStatement: true, // ensure a prepared statement is used
        binds
      });
    }
    // wait for parallel executions to complete
    for (let i = 0; i < rtn.txExpPsRslts.length; i++) {
      rtn.txExpPsRslts[i] = await rtn.txExpPsRslts[i];
    }

    // unprepare will be called when calling commit
    // (alt, could have called unprepare before commit)
    await tx.commit();
  } catch (err) {
    if (tx) {
      // unprepare will be called when calling rollback
      // (alt, could have called unprepare before rollback)
      await tx.rollback();
    }
    throw err;
  }
}

Delete Rows:

-- db/postgres/delete.table1.rows.sql
DELETE FROM sqlerpg.TEST
WHERE ID = :id

-- db/postgres/delete.table2.rows.sql
DELETE FROM sqlerpg.TEST2
WHERE ID = :id2

'use strict';

// export just to illustrate module usage
module.exports = async function runExample(manager, connName) {

  const rtn = new Array(2);

  // delete rows (implicit transactions)
  rtn[0] = await manager.db[connName].delete.table1.rows({
    binds: { id: 1 }
  });
  rtn[1] = await manager.db[connName].delete.table2.rows({
    binds: { id2: 1 }
  });

  return rtn;
};

Delete Tables:

-- db/postgres/setup/delete.table1.sql
DROP TABLE sqlerpg.TEST

-- db/postgres/setup/delete.table2.sql
DROP TABLE sqlerpg.TEST2

'use strict';

// export just to illustrate module usage
module.exports = async function runExample(manager, connName) {

  // delete the tables (in parallel)
  return Promise.all([
    manager.db[connName].setup.delete.table1(),
    manager.db[connName].setup.delete.table2()
  ]);
};

Delete Schema:

-- db/postgres/setup/delete.database.sql
DROP SCHEMA sqlerpg

'use strict';

// export just to illustrate module usage
module.exports = async function runExample(manager, connName) {

  // delete the database and/or schema
  return manager.db[connName].setup.delete.database();
};