postgres-pool
postgres-pool
Connection pool implementation for pg. Compatible with pg-pool options and syntax.
Why?
- There were some connection timeout issues that we encountered with pg-pool and the npm was not being updated.
- Added graceful cluster failover
- Added default AWS TLS settings via
ssl='aws-rds' - Typescript is used to enforce type safety and promises are preferred over callbacks.
Getting Started
Simple query (automatically releases connection after query - recommended)
import { Pool } from 'postgres-pool';
const pool = new Pool({
connectionString: 'postgres://username:pwd@127.0.0.1/db_name',
});
const userId = 42;
const results = await pool.query('SELECT * from "users" where id=$1', [userId]);
console.log('user:', results.rows[0]);Using named parameters in the query
import { Pool } from 'postgres-pool';
const pool = new Pool({
connectionString: 'postgres://username:pwd@127.0.0.1/db_name',
});
const userId = 42;
const results = await pool.query('SELECT * from "users" where id=@id', {
id: userId,
});
console.log('user:', results.rows[0]);More control over connections (not recommended)
import { Pool } from 'postgres-pool';
const pool = new Pool({
connectionString: 'postgres://username:pwd@127.0.0.1/db_name',
});
const userId = 42;
const connection = await pool.connect();
try {
const results = await connection.query('SELECT * from "users" where id=$1', [userId]);
console.log('user:', results.rows[0]);
} finally {
// NOTE: You MUST call connection.release() to return the connection back to the pool
await connection.release();
}Handle errors from connections in the pool
import { Pool } from 'postgres-pool';
const pool = new Pool({
connectionString: 'postgres://username:pwd@127.0.0.1/db_name',
});
pool.on('error', (err) => {
console.error('Unexpected error on idle client', err);
process.exit(-1);
});Graceful shutdown
import { Pool } from 'postgres-pool';
const pool = new Pool({
connectionString: 'postgres://username:pwd@127.0.0.1/db_name',
});
await pool.end();Explicit connection details instead of a connection string
import { Pool } from 'postgres-pool';
const pool = new Pool({
host: '127.0.0.1',
database: 'db_name',
user: 'foo',
password: 'bar',
port: 1234,
});AWS RDS specific TLS settings for connections
Setting ssl='aws-rds' will:
- configure the AWS root certificate
- reject any connection which is not authorized with the list of supplied CAs.
- attempt to use TLSv1.2 as the minimum TLS version.
It is the same as:
import { Pool } from 'postgres-pool';
const pool = new Pool({
ssl: {
rejectUnauthorized: true,
ca: fs.readFileSync('./certs/rds-global-bundle.pem'),
minVersion: 'TLSv1.2',
},
});import { Pool } from 'postgres-pool';
const pool = new Pool({
connectionString: 'postgres://username:pwd@127.0.0.1/db_name',
ssl: 'aws-rds',
});TLS details for a connection
import { Pool } from 'postgres-pool';
const pool = new Pool({
host: '127.0.0.1',
database: 'db_name',
user: 'foo',
password: 'bar',
port: 1234,
ssl: {
rejectUnauthorized: false,
ca: fs.readFileSync('/path/to/server-certificates/root.crt').toString(),
key: fs.readFileSync('/path/to/client-key/postgresql.key').toString(),
cert: fs.readFileSync('/path/to/client-certificates/postgresql.crt').toString(),
},
});Change size of the pool
import { Pool } from 'postgres-pool';
const pool = new Pool({
connectionString: 'postgres://username:pwd@127.0.0.1/db_name',
poolSize: 10, // Default is 10 connections
});Change retry on error settings
import { Pool } from 'postgres-pool';
const pool = new Pool({
connectionString: 'postgres://username:pwd@127.0.0.1/db_name',
// Number of retries to attempt when there's an error matching `retryConnectionErrorCodes`. A value of 0 will disable connection retry.
retryConnectionMaxRetries: 5,
// Milliseconds to wait between retry connection attempts after receiving a connection error with code that matches `retryConnectionErrorCodes`. A value of 0 will try reconnecting immediately.
retryConnectionWaitMillis: 100,
// Error codes to trigger a connection retry.
retryConnectionErrorCodes: ['ENOTFOUND', 'EAI_AGAIN'],
});Change timeout thresholds
import { Pool } from 'postgres-pool';
const pool = new Pool({
connectionString: 'postgres://username:pwd@127.0.0.1/db_name',
// Time to keep a connection idle. Default is 10s
idleTimeoutMillis: 10000,
// Time to wait to obtain a connection from the pool. Default is 90s
waitForAvailableConnectionTimeoutMillis: 90000,
// Max time to connect to postgres. Default is 5s
connectionTimeoutMillis: 5000,
});Handle cluster failover gracefully
When a cluster has a failover event, promoting a read-replica to master, there can be a couple sets of errors that happen with already established connections in the pool as well as new connections before the cluster is available in a ready state.
By default, when making a new postgres connection and the server throws an error with a message like:
the database system is starting up, the postgres-pool library will attempt to reconnect
(with no delay between attempts) for a maximum of 90s.
Similarly, if a non-readonly query (create/update/delete/etc) is executed on a readonly connection, the server will
throw an error with a message like: cannot execute UPDATE in a read-only transaction. This can occur when a
connection to a db cluster is established and the cluster fails over before the connection is terminated, thus the
connected server becomes a read-replica instead of the expected master.
The postgres-pool library will attempt to reconnect (with no delay between attempts) for a maximum of 90s and will
try to execute the query on the new connection.
Defaults can be overridden and this behavior can be disabled entirely by specifying different values for the pool options below:
import { Pool } from 'postgres-pool';
const pool = new Pool({
connectionString: 'postgres://username:pwd@127.0.0.1/db_name',
// Enable/disable reconnecting on "the database system is starting up" errors
reconnectOnDatabaseIsStartingError: true,
// Milliseconds to wait between retry connection attempts while the database is starting up
waitForDatabaseStartupMillis: 0,
// If connection attempts continually return "the database system is starting up", this is the total number of milliseconds to wait until an error is thrown.
databaseStartupTimeoutMillis: 90000,
// If the query should be retried when the database throws "cannot execute X in a read-only transaction"
reconnectOnReadOnlyTransactionError: true,
// Milliseconds to wait between retry queries while the connection is marked as read-only
waitForReconnectReadOnlyTransactionMillis: 0,
// If queries continually return "cannot execute X in a read-only transaction", this is the total number of milliseconds to wait until an error is thrown
readOnlyTransactionReconnectTimeoutMillis: 90000,
// If the query should be retried when the database throws "Client has encountered a connection error and is not queryable"
reconnectOnConnectionError: true,
// Milliseconds to wait between retry queries after receiving a connection error
waitForReconnectConnectionMillis: 0,
// If queries continually return "Client has encountered a connection error and is not queryable", this is the total number of milliseconds to wait until an error is thrown
connectionReconnectTimeoutMillis: 90000,
});Compatibility
- Node.js v16 or above
License
MIT