pgnode

:package: pgnode

PostgresSQL client to Nodejs servers

Report Bug Request Feature

Systems on which it has been tested.

Did you like the project? Please, considerate a donation to help improve!

PostgresSQL client to Nodejs servers✨

Connect your database easily using the pgnode package

Getting started

Installation

To install the module in your project just run the command below:

npm i pgnode

or

yarn add pgnode

Now in your project just import the module like this:

const pg = require('pgnode');

Or you can use import:

import pg from 'pgnode';

Use tx

This is the simplest possible way to connect, query, and disconnect with async/await:

const { Client } = require('pgnode');
const client = new Client();
await client.connect();
const res = await client.query('SELECT $1::text as message', ['Hello world!']);
console.log(res.rows[0].message); // Hello world!
await client.end();

And here's the same thing with callbacks:

const { Client } = require('pgnode');
const client = new Client();
client.connect();
client.query('SELECT $1::text as message', ['Hello world!'], (err, res) => {
  console.log(err ? err.stack : res.rows[0].message); // Hello World!
  client.end();
});

Our real-world apps are almost always more complicated than that, and I urge you to read on!

Usage

import { tx } from `pgnode`
import pg from `pgnode`

const pg = new Pool()

await tx(pg, async (db) => {
  await db.query(`UPDATE accounts SET money = money - 50 WHERE name = 'bob'`)
  await db.query(`UPDATE accounts SET money = money + 50 WHERE name = 'alice'`)
})

await tx(pg, async (db) => {
  await db.query(`UPDATE accounts SET money = money - 50 WHERE name = 'bob'`)
  await db.query(`UPDATE accounts SET money = money + 50 WHERE name = 'debbie'`)

  // Any errors thrown inside the callback will terminate the transaction
  throw new Error(`screw Debbie`)
})

// You can also use it with other packages that use Pool or PoolClient, like pgtyped
import { sql } from '@pgtyped/query'

const updateAccount = sql<IUpdateAccountQuery>`
  UPDATE accounts
  SET money = momey + $delta
  WHERE name = $name
`

await tx(pg, async(db) => {
  await udpateAccount.run({ name: 'bob', delta: -50 })
  await udpateAccount.run({ name: 'charlie', delta: 50 })
})

However, this approach contains a subtle bug, because the client it passes to the callback stays valid after transaction finishes (successfully or not), and can be unknowingly used. In essence, it's a variation of use-after-free bug, but with database clients instead of memory.

Here's a demonstration of code that can trigger this condition:

async function failsQuickly(db: PoolClient) {
  await db.query(`This query has an error`)
}

async function executesSlowly(db: PoolClient) {
  // Takes a couple of seconds to complete
  await externalApiCall()
  // This operation will be executed OUTSIDE of transaction block!
  await db.query(`
    UPDATE external_api_calls
    SET amount = amount + 1
    WHERE service = 'some_service'
  `)
}

await tx(pg, async (db) => {
  await Promise.all([
    failsQuickly(db),
    executesSlowly(db)
  ])
})

Features

• Pure JavaScript client and native libpq bindings share the same API
• Support all tls.connect options being passed to the client/pool constructor under the ssl option.
• Connection pooling
• Extensible JS ↔ PostgreSQL data-type coercion
• Supported PostgreSQL features
  • Parameterized queries
  • Named statements with query plan caching
  • Async notifications with LISTEN/NOTIFY
  • Bulk import & export with COPY TO/COPY FROM
  • Change default database name
  • make pg.Pool an es6 class