Skip to the content.

pg-fusion

Overview

This package combines the features of multiple libraries into a single unified interface to get your PostgreSQL-backed application up and running quickly. Especially useful for projects that do not want to use ORMs, for whatever reason. This library provides an interface that roughly combines the interfaces of the following libraries:

Features include:

Features

Database querying

Test helpers

Database migrations

API

Database

A Database represents a PostgreSQL database connection pool (docs). It primarily provides the following functions:

Additionally, every DatabaseClient function can be called directly from a Database, which is equivalent to calling db.withClient() and calling the corresponding function on the client provided. For example,

const rows = await db.query(sql`...`)

is equivalent to

const rows = await db.withClient((client) => {
  return client.query(sql`...`)
})

The only exception is db.transaction, which also provides the client to the callback:

await db.transaction(async (client) => {
  ...
})

DatabaseClient

A DatabaseClient represents a connection to a PostgreSQL database. You should get a DatabaseClient from db.withClient, which ensures that the connection is closed when the callback is finished.

SqlQuery

A SqlQuery can only be constructed with the sql tagged template function, which prevents one from accidentally passing in a string vulnerable to SQL injections into client.query(...). By default, any interpolated values will be parametrized using SQL placeholders:

const name = 'Take On Me'
const artist = 'A-ha'

await client.query(sql`
  SELECT * FROM song
  WHERE song.name = ${name}
  AND song.artist = ${artist}
`)

/* Executes the query
 *
 *   SELECT * FROM song
 *   WHERE song.name = $1
 *   AND song.artist = $2
 *
 * with the values
 *
 *   ['Take On Me', 'A-ha']
 */

A SqlQuery can also be a part of a query, which can be combined with other SqlQuerys:

const condition1 = sql`song.name = ${name}`
const condition2 = sql`song.artist = ${artist}`
await client.query(sql`
  SELECT * FROM song
  WHERE ${condition1} AND ${condition2}
`)

If you absolutely need to avoid parametrizing, you can use sql.raw, but be very careful with this, as it could leave you open to SQL injection attacks.

const attribute = 'name'
await client.query(sql`
  SELECT ${sql.raw(attribute)} FROM song
`)

sql.raw on a static string is the same as using the tagged template function. The following two expressions are equivalent.

sql.raw('hello world')
sql`hello world`

This library also provides some other helpers that may be useful:

Jest helpers

In general, database functions should be tested with integration tests that actually go to the database and verify that the query works. However, maybe the query itself isn’t complicated, but the logic to determine which query to run is complicated, and you want to verify that with a unit test. This library provides some custom Jest matchers that will compare two queries, ignoring whitespace differences, and ensure they match.