npm package: @fpjs-incubator/nice-pg-sql-toolkit
Description: Nice PG SQL toolkit. Loves SQL. Not an ORM.
Author: FingerprintJS
Latest version: 0.7.1

<center>

![logo](https://fpjs-public.s3.amazonaws.com/oss/nice-pg-sql-toolkit/logo.jpg)

Nice PG SQL toolkit
============================

![build](https://github.com/fingerprintjs/nice-pg-sql-toolkit/workflows/build/badge.svg)
</center>


🧰 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.

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

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

```js
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_

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

// find one user by email
let row = await db.findOne('users', {email: 'john@example.com'})


// 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: 'john@example.com', 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_

```js
// 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)
}
```

```js
// 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

```js
// 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.

```js
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

```js
  // checking error type will tell you if it's a unique index violation
  try {
    let user = await db.create('users', {email: 'smith@example.com'})
  } 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.

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

Example of `up` script:

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

Example of `down` script:

```sql
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

```js
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

```shell script
# 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.