Nice PG SQL toolkit

🧰 Nice SQL toolkit for PG + Node (tiny, <200 LOC)

npm i nice-pg-sql-toolkit
or
yarn add nice-pg-sql-toolkit

Usage

Your database URL should be in DATABASE_URL env var, e.g.

export DATABASE_URL=postgres://user:password@host/database:5432

Alternatively you can specify a database URL as a parameter to recreatePool:

const db = require('nice-pg-sql-toolkit')
db.recreatePool({connectionString: 'postgres://localhost'})

Simple usage

this approach is a good starting point, it uses DB-level attributes directly w/out column mapping

const db = require('nice-pg-sql-toolkit')

// find one user by email
let row = await db.findOne('users', {email: '[email protected]'})


// find all users by role
let rows = await db.find('users', {role: 'admin'})

// find all users by multiple roles
let rows = await db.find('users', {role: ['admin', 'root', 'superuser']})

// insert a user
let attrs = await db.insert('users', {email: '[email protected]', role: 'admin'})
// attrs will have the id attribute if you have an id primary key

// update all users by role, set their access_level to 'full'
await db.update('users', {'access_level': 'full'}, {'role': 'admin'})

// delete a user by ID
await db.del('users', {id: 23234554})

// use inline SQL directly
const sql = `SELECT * FROM users WHERE firstName = $1 ORDER BY ID DESC LIMIT $2`
// pass dollar params as a second argument as an array
let firstName = 'John'
let limit = 10
let rows = await db.query(sql, [firstName, limit])

Define your model, for example models/user

this is convenient if you want to keep your logic centralized and also perform column mapping

// models/user.js
const db = require('nice-pg-sql-toolkit')

const TableName = 'users'

// model attribute to column mapping object
const Columns = {
  id: 'user_id',
  firstName: 'first_name',
  lastName: 'last_name',
  createdAt: 'created_at'
}

const findOne = async (condition) => {
  let conditionValues = db.mapToColumns(condition, columns)
  let row = await db.findOne(TableName, conditionValues)
  return db.mapFromColumns(row, columns)
}

const find = async (condition) => {
  let conditionValues = db.mapToColumns(condition, columns)
  let rows = await db.find(TableName, conditionValues)
  return rows.map((row) => db.mapFromColumns(row, columns))
}

const create = async (attrs) => {
  let columnValues = db.mapToColumns(attrs, columns)
  return await db.insert(TableName, columnValues)
}

const update = async (condition, attrs) => {
  let conditionValues = db.mapToColumns(condition, columns)
  let columnValues = db.mapToColumns(attrs, columns)
  return await db.update(TableName, columnValues, conditionValues)
}

const del = async (condition) => {
  let conditionValues = db.mapToColumns(condition, columns)
  return await db.del(TableName, conditionValues)
}

// Now you can use your model everywhere
const user = require('/models/user')

// find one (e.g. by ID)
let user = await User.findOne({id: 3956})
// if no user is found, null will be returned


// find multiple users
let users = await User.find({lastName: 'Smith'})
// if no users were found, empty array will be returned


// add a new user
let user = await User.create({firstName: 'John', lastName: 'Smith'})


// update existing user
// update a user by ID
await User.update({id: 3956}, {lastName: 'Bunyan'})


// delete user
// delete a user by ID
await User.del({id: 3956})

Using transactions

// using transaction requires wrapping everything in a transaction and
// passing the current transaction as a last argument
let userAudit = await db.withTransaction(async (tr) => {
  await db.update('users', {id: 9363}, {lastName: 'Bunyan'}, tr)
  return await db.create('users_audit', {entity: 'User', op: 'update', args: [{lastName: 'Bunyan'}]}, tr)
})
// note that the return value from the callback will be returned by withTransaction function

If you want to execute certain actions after the transaction is rolled back, use the second function argument for this.

let onRollback = () => {
  // cleanup external resources
  // e.g. // payment gateway rollback etc
}
let res = await db.withTransaction(tr => {/* do something in transaction.. */}, onRollback)

Unique index violation

  // checking error type will tell you if it's a unique index violation
  try {
    let user = await db.create('users', {email: '[email protected]'})
  } catch(e) {
    if(e instanceof db.UniqueIndexError) {
      console.log('Unique index violation on table: users, columns:', e.columns)
    }
  }

Using migrations

This toolkit comes with a simple migration runner.

To use it, create a directory with SQL scripts inside.

Every DB version change requires two scripts: up and down.

db/migrations
├── 0001_create_table1.up.sql
└── 0001_drop_table1.down.sql
    └──version └──name └── up or down

Example of up script:

create table users (
  id serial primary key,
  email text unique
);

Example of down script:

drop table users;

You can place multiple create/drop statement in each file, they will be run inside a transaction and either all succeed or all fail.

Once you have the migration files ready, you have two options: run migrations with API or with CLI

API

import db from 'nice-pg-sql-toolkit'

// pass the migrations directory path
const migrator = db.createMigrator('/opt/projects/your-project/db/migrations')

// to run `up`
await migrator.up()

// to run `down`
await migrator.down()

CLI

# you can use relative paths here
yarn nice-pg-migrate db/migrations up

# or
yarn nice-pg-migrate db/migrations down

Migration runner will maintain a special table called db_versions internally that will keep all applied migrations.

MIT Licensed.

Copyright FingerprintJS Inc., 2020-2021.