hapi-postgres-pool
Hapi plugin for connecting to multiple PostgreSQL databases Using Postgres pool connection
Why
I've created this module because I couldn't find any PostgreSQL plugin that can connect to multiple Postgresql db. I also want to use a module that use a minimum number of dependencies.
Features:
- Connect to multiple postgres instances.
- Attach plugin based query and url parameters
attachedQueries
or/andattachedParams
- Access postgres connections using
server
object orrequest
. - Support Postgresql native binding.
- Use pg-pool.
- Tests
Run
- If you looking for a quick working example checkout this
Parameters:
options
(Object):native
(Boolean): using Postgresql native client Default:false
.user
(String): Postgresql usernamepassword
(String): Postgresql passwordport
(Number): Postgresql port numberssl
(Boolean): SSL on/offmax
(Number): Postgres pool max sizemin
(Number): Postgres pool min sizeidleTimeoutMillis
(Number): close idle clients after 1 seconddatabase
(String): Database nameattach
(String): Hapi.js events for creating Postgresql connection available (for more info check HAPI life cycle events) Default:onPreHandler
attachedQueries
(Array): Array contain strings of query params, for example when passing ['user_id', 'username'] as a query params?user_id
or?email=asdf@asdf.com&username=aaa
the module will attach the plugin.attachedParams
(Array): Array contain strings of params same asattachedQueries
just for params. for example if the url is/users/{username}
and you would like to have the plugin available you can passattachedParams
:['username']
.default
(String): default db name. when using multiple dbs you might want to use a config or modulo but when the module can't find any connection it will return the default one.
connection
(Array): Array of db connectionskey
(String): the database name, this will help you to decide which db you would like to accessconnectionString
(String): postgres connection string: postgres://foo:bar@baz:1234/xuruser
(String): Postgresql usernamepassword
(String): Postgresql passwordport
(Number): Postgresql port numberssl
(Boolean): SSL on/offmax
(Number): Postgres pool max sizemin
(Number): Postgres pool min sizeidleTimeoutMillis
(Number): close idle clients after 1 seconddatabase
(String): Database name
Usage
- There are number of examples under the
examples
directory
/*** Register the plugin**/register: options: default: 'primary_db' native: true attach: 'onPreAuth' database: 'postgres' user: 'postgres' password: 'postgres' port: 5432 ssl: false max: 20 min: 1 idleTimeoutMillis: 5000 connections: key: '1' // The key will map request.pg[KEY_HERE].query()... in this case: request.pg['1'].query('SELECT * FROM USERS WHERE...') user: 'doron' password: 'doron' host: 'localhost' port: 5432 database: 'database_1' ssl: false key: '2' user: 'doron2' password: 'doron2' host: 'localhost' port: 5433 database: 'database_2' ssl: false key: '3' connectionString: 'postgres://foo:bar@baz:1234/xur'
/*** perform queries* You can also use Promise.all and query in parallel* IMPORTANT: don't forget to `release` the client after* fetching the results `client.release()`**/method: 'GET'path: '/promise'config: { const queryDB1 = 'select * from SOME_TABLE limit 1'; const queryDB2 = 'select * from ANOTHER_TABLE limit 1'; const queryDB2 = 'select * from ANOTHER_TABLE_3 limit 1'; requestserverplugins'hapi-postgres-pool'pg'1'
Usage: get connection from plugin
const PgPool = serverplugins'hapi-postgres-pool'pg;PgPool;
safe
Usage: get specific connection const pg = serverplugins'hapi-postgres-pool'pg;pg
or using request object
const pg = requestpg;pg;
When passing invalid connection name the plugin will use the default
connection
register: require('hapi-postgres-pool'),
options: {
default: 'primary_db',
native: true,
attach: 'onPreAuth',
database: 'postgres',
user: 'postgres',
password: 'postgres',
port: 5432,
ssl: false,
max: 20,
min: 1,
idleTimeoutMillis: 5000,
connections: [{
key: '1', // The key will map request.pg[KEY_HERE].query()... in this case: request.pg['1'].query('SELECT * FROM USERS WHERE...')
user: 'doron',
password: 'doron',
....
const pg = request.pg._get('invalid_db');
// this will return the default database in this case: 'primary_db'
Questions
PR
- Please do.