Features

• Direct SQL queries to a specific database with streaming replication.

Based on the SQL statement executeQuery() decides which database to send the query to. If the SQL statement is a DML statement (INSERT, UPDATE or DELETE) it will be executed in the pool which has the property 'readonly: false'. SELECT statements will be executed in the pool which has the property 'readonly: true'.

In case of more read-only replicas than one the strategy is to shuffle the pool to choose a random read-only replica to use. This gives a round-robin style of replica selection.

Then finally we destroy or release all pools to release them.

const pgroute = require('pg-route');

const poolConfigWR = {
  poolname: 'Master replica',
  readonly: false,
  options: {
    host: 'master-db.example.org',
    port: 5432,
    user: 'appuser',
    password: 'VerySecretPassword',
    database: 'devdb',
  },
  _pool: {},
};

const poolConfigRO1 = {
  poolname: 'Read replica',
  readonly: true,
  options: {
    host: 'ro-replica.example.org',
    port: 5432,
    user: 'appuser',
    password: 'VerySecretPassword',
    database: 'devdb',
    readonly: true,
  },
  _pool: {},
};

const poolConfigRO2 = {
  poolname: 'Read replica',
  readonly: true,
  options: {
    host: 'ro-replica2.example.org',
    port: 5432,
    user: 'appuser',
    password: 'VerySecretPassword',
    database: 'devdb',
    readonly: true,
  },
  _pool: {},
};

const poolConfigArray = [
  poolConfigWR,
  poolConfigRO1,
  poolConfigRO2,
];

(async () => {
  const poolArray = await pgroute.createPools(poolConfigArray);
  const res1 = await pgroute.executeQuery(poolArray, 'SELECT COUNT(*) FROM banktransaction', null);
  const res2 = await pgroute.executeQuery(poolArray, 'INSERT INTO testtable(col1, col2) VALUES ($1,$2)', [1, 'Heureka!']);
  const res2 = await pgroute.executeQuery(poolArray, 'INSERT INTO testtable(col1, col2) VALUES ($1,$2)', [1, 'Heureka!']);
  await destroyPools(poolArray);
})().catch((err) => {
  console.error(err);
});

License

Copyright (c) 2021 Jimmy Jonsson ([email protected])