JSPM – @mojojs/pg@0.1.0

Package Exports

@mojojs/pg

Readme

A tiny wrapper around pg with some very convenient APIs. Written in TypeScript.

import Pg from '@mojojs/pg';

// Use standard PostgreSQL connection URIs
const pg = new Pg('postgres://user:password@localhost:5432/database');

// Single query with safe placeholder
const results = await pg.query`SELECT ${'One'} AS one`;
for (const row of results.all) {
  console.log(row.one);
}

// Multiple queries on the same connection
const db = await pg.db();
const results = await db.query`SELECT 2`;
const results = await db.query`SELECT 3`;
await db.release();

Tagged template literals are used everywhere to protect from SQL injection attacks and to make syntax highlighting easy.

SQL building

For easier SQL query building with partials, there are also pg.sql and db.sql tagged template literals. They can be used recursively to build complex queries securely.

// Build safe SQL query with placeholder and partial SQL query
const role = 'admin';
const partialQuery = pg.sql`AND role = ${role}`;
const name = 'root';
const results = await pg.query`SELECT * FROM users WHERE name = ${name} ${partialQuery}`;

But if you need a little more control over the generated SQL query, you can of course also bypass safety features with the tagged template literals pg.sqlUnsafe and db.sqlUnsafe.

const role = 'role = ' + pg.escapeLiteral('power user');
const partialQuery = pg.sqlUnsafe`AND ${role}`;
const name = 'root';
const results = await pg.query`SELECT * FROM users WHERE name = ${name} ${partialQuery}`;

But make sure to use methods like pg.escapeLiteral() to escape unsafe values yourself.

Transactions

It's best to use try/finally blocks whenever you dequeue a connection with pg.db(), to ensure efficient resource mangement.

try {
  const db = await pg.db();
  const tx = await db.begin();

  try {
    for (const user of ['Daniel', 'Isabell']) {
      await db.query`INSERT INTO users (name) VALUES (${user})`;
    }
    await tx.commit();

  } finally {
    await tx.rollback();
  }
} finally {
  await db.release();
}

The tx.rollback() call does nothing if tx.commit() has been called first.

Notifications

You can use events as well as async iterators for notifications.

// Send notifications
const db = await pg.db();
await db.notify('foo', 'just a message');

// Use an iterator to wait for incoming notifications
await db.listen('foo');
for await (const message of db) {
  console.log(`${message.channel}: ${message.payload}`);
  break;
}
await db.unlisten('foo');

Future

This package is designed to be compatible with the explicit resource management proposal and will support it as soon as the using keyword is available in Node.js.

// Multiple queries on the same connection (with automatic resource management)
using const db = await pg.db();
const results = await db.query`SELECT 2`;
const results = await db.query`SELECT 3`;

Installation

All you need is Node.js 16.0.0 (or newer).

$ npm install @mojojs/pg