PostgreSQL

💡 PostgreSQL Examples:

Install the drivers from odbc.postgresql.org

Windows ODBC Data Source

When installing PostgreSQL using Stack Builder, select the ODBC driver from the available drivers and follow the prompts to install them. Otherwise, the ODBC drivers can be download separately from here.

Configure the ODBC driver via the ODBC Data Source Administrator:

UNIX /etc/odbc.ini unixODBC (PostgreSQL ODBC Connection Parameters)

[ODBC Data Sources]
PostgreSQL=PostgreSQL ODBC Driver DSN

[PostgreSQL]
Driver = /opt/postgres/current/lib/libpsqlodbc.so
Description = Connector/ODBC Driver DSN
Username = postgres
Password = p0stgr3slocal
UseMultipleStatements = 1

Examples:

The examples below use the following setup:

Private Options Configuration: (appended to the subsequent connection options, shows other connections for illustration purposes)

{
  "univ": {
    "db": {
      "mssql": {
        "username":"sa",
        "password": "sqlS3rv35local"
      },
      "oracle": {
        "username": "system",
        "password": "oracl3local"
      },
      "mysql": {
        "username":"root",
        "password": "my3qllocal"
      },
      "postgresql": {
        "username":"postgres",
        "password": "p0stgr3slocal"
      },
      "cache": {}
    }
  }
}

Connection Options Configuration:

{
  "db": {
    "dialects": {
      "odbc": "sqler-odbc"
    },
    "connections": [
      {
        "id": "postgresql",
        "name": "postgresql",
        "dir": "db/postgresql",
        "service": "PostgreSQL",
        "dialect": "odbc",
        "pool": {},
        "driverOptions": {
          "connection": {
            "DSN": "${service}",
            "UID": "${username}",
            "PWD": "${password}",
            "UseMultipleStatements": 1
          },
          "pool": {
            "shrink": true
          }
        }
      }
    ]
  }
}

NOTE: db.connections.driverOptions.connection for DSN interpolates into db.connections[].service

Test code that illustrates how to use PostgreSQL + ODBC with various examples

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

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

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

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 Table:

-- db/postgresql/setup/create.tables.sql
CREATE SCHEMA IF NOT EXISTS sqlerodbc;
CREATE TABLE IF NOT EXISTS sqlerodbc.TEST (ID INTEGER NOT NULL PRIMARY KEY, NAME VARCHAR(384), CREATED_AT TIMESTAMP(3) WITH TIME ZONE, UPDATED_AT TIMESTAMP(3) WITH TIME ZONE);
CREATE TABLE IF NOT EXISTS sqlerodbc.TEST2 (ID INTEGER NOT NULL PRIMARY KEY, NAME VARCHAR(384), 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 multiple tables in a single execution
  const rslt = await manager.db[connName].setup.create.tables();

  return rslt;
};

Create Rows:

-- db/postgresql/create.table.rows.sql
INSERT INTO sqlerodbc.TEST (ID, NAME, CREATED_AT, UPDATED_AT)
VALUES (:id, :name, :created, :updated);
INSERT INTO sqlerodbc.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) {

  // The odbc module needs the date to be in a valid ANSI compliant format.
  // Could also use:
  // https://www.npmjs.com/package/moment-db
  const date = new Date().toISOString().replace('T', ' ').replace('Z', '');

  // The odbc module currently doesn't support Fs.ReadStream/Fs.createReadStream()
  const report = await Fs.promises.readFile('./test/files/audit-report.png');

  // Insert rows into multiple tables within a single ODBC execution
  const rslt = await manager.db[connName].create.table.rows({
    binds: {
      id: 1, name: 'TABLE: 1, ROW: 1', created: date, updated: date,
      id2: 1, name2: 'TABLE: 2, ROW: 1', report2: report, created2: date, updated2: date
    }
  });

  return rslt;
};

Read Rows:

-- db/postgresql/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 sqlerodbc.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 sqlerodbc.TEST2 TST2
WHERE UPPER(TST2.NAME) LIKE CONCAT(CONCAT('%', UPPER(:name)), '%')

'use strict';

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

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

  return rslt;
};

Update Rows:

Although the following updates can be made within a single SQL script, multiple SQL scripts are used to illustrate the use of tranactions and/or prepared statements.

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

-- db/postgresql/update.table2.rows.sql
UPDATE sqlerodbc.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) {

  // The odbc module needs the date to be in a valid ANSI compliant format.
  // Could also use:
  // https://www.npmjs.com/package/moment-db
  const date = new Date().toISOString().replace('T', ' ').replace('Z', '');

  // 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:
  console.log(`---------------------------- Implicit Transaction Update ----------------------------`);
  await implicitTransactionUpdate(manager, connName, binds1, binds2, rtn);

  // Using an explicit transaction:
  console.log(`---------------------------- Explicit Transaction Update ----------------------------`);
  await explicitTransactionUpdate(manager, connName, binds1, binds2, rtn);

  // Using a prepared statement:
  console.log(`---------------------------- Prepared Statement Update ----------------------------`);
  await preparedStatementUpdate(manager, connName, binds1, rtn);

  // Using a prepared statement within an explicit transaction
  console.log(`---------------------------- Prepared Statement Explicit Transaction Update ----------------------------`);
  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
  try {
    // start a transaction
    const txId = 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: txId, // 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: txId, // 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];

    // could commit using either one of the returned results
    await rtn.txExpRslts[0].commit();
  } catch (err) {
    if (rtn.txExpRslts[0] && rtn.txExpRslts[0].rollback) {
      // could rollback using either one of the returned results
      await rtn.txExpRslts[0].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(3); // don't exceed connection pool count
  try {
    // start a transaction
    const txId = 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 "${txId}" 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: txId, // 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 rtn.txExpPsRslts[0].commit();
  } catch (err) {
    if (rtn.txExpPsRslts[0] && rtn.txExpPsRslts[0].rollback) {
      // unprepare will be called when calling rollback
      // (alt, could have called unprepare before rollback)
      await rtn.txExpPsRslts[0].rollback();
    }
    throw err;
  }
}

Delete Rows:

-- db/postgresql/delete.table.rows.sql
DELETE FROM sqlerodbc.TEST
WHERE ID = :id;
DELETE FROM sqlerodbc.TEST2
WHERE ID = :id2;

'use strict';

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

  // Delete rows from multiple tables within a single ODBC execution
  const rslt = await manager.db[connName].delete.table.rows({
    binds: { id: 1, id2: 1 }
  });

  return rslt;
};

Delete Table:

-- db/postgresql/setup/delete.tables.sql
DROP TABLE sqlerodbc.TEST;
DROP TABLE sqlerodbc.TEST2;
DROP SCHEMA sqlerodbc;

'use strict';

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

  // delete multiple tables in a single execution
  const rslt = await manager.db[connName].setup.delete.tables();

  return rslt;
};