@storehouse/pg

PostgreSQL manager for @storehouse/core.

Documentation.

Installation

Make sure you have @storehouse/core and pg installed.

npm install @storehouse/pg
Copy

Usage

Basic

import Storehouse from '@storehouse/core'
import { PGManager } from '@storehouse/pg'

// register
Storehouse.add({
  local: {
    // type: '@storehouse/pg' if you called Storehouse.setManagerType(PGManager)
    type: PGManager,

    // pool configuration
    config: {
      database: 'database',
      host: 'localhost',
      port: 5432,
      user: 'admin',
      password: '',
      ssl: {
        rejectUnauthorized: false
      },
      max: 20,
      connectionTimeoutMillis: 2000,
      idleTimeoutMillis: 10000
    }
  }
})
Copy

After the manager has been added, you can use it as a pool to query the database.

import Storehouse from '@storehouse/core'
import { PGManager } from '@storehouse/pg'
import { PoolClient } from 'pg'

/**
 * only defined to read the property 'database'
 */
interface MyPoolClient extends PoolClient {
  database?: string
}

// acquire a client from the pool
const conn = await Storehouse.getConnection<Promise<MyPoolClient>>('local')
if (conn) {
  console.log('retrieved connection for database', conn.database)
}

// manager
const localManager = Storehouse.getManager<PGManager<MyPoolClient>>('local')
if (localManager) {
  console.log(
    'nb movies',
    (
      await localManager.query<{ count: string }>(
        'SELECT count(*) FROM myschema.movies'
      )
    ).rows
  )
}

// release a client
conn?.release(true)

// release all clients for the pool
await localManager?.releaseAll(true)

// release all clients and end the pool
await localManager?.closeConnection(true)
Copy

Logs

You can enable logs with the package debug.

import debug from 'debug'
debug.enable('@storehouse/pg*')
Copy

Helpers

There are methods to help you retrieve the connection and the manager so you don't have to check if they are undefined. Those methods throw an error when they fail.

import Storehouse from '@storehouse/core'
import { getConnection, getManager } from '@storehouse/pg'
import { PoolClient } from 'pg'

/**
 * only defined to read the property 'database'
 */
interface MyPoolClient extends PoolClient {
  database?: string
}

// acquire a client from the pool
const conn = await getConnection<MyPoolClient>(Storehouse, 'local')
console.log('retrieved connection for database', conn.database)

// manager
const manager = getManager(Storehouse, 'local')
console.log(
  'nb movies',
  (
    await manager.query<{ count: string }>(
      'SELECT count(*) FROM myschema.movies'
    )
  ).rows
)
Copy

Target a schema by default

If you need each query to target the same schema and don't want to explicitly write it for every query, you can set the search_path each time a client connects.

import Storehouse from '@storehouse/core'
import { getManager } from '@storehouse/pg'

// manager
const manager = getManager(Storehouse, 'local')
manager.on('connect', client => {
  // set the search_path for each new client to 'myschema'
  client.query('SET search_path TO myschema')
})

// query table 'movies'
const result = await manager.query('SELECT * FROM movies LIMIT 100')
console.log(result.rows)
Copy

References

• Documentation
• @storehouse/core
• pg