pg-role 
opinionated pg abstraction
Opinionated?
This package requires you to create models with these columns:
- id SERIAL
- created_at TIMESTAMP
- created_by INT
- updated_at TIMESTAMP
- updated_by INT
- deleted_at TIMESTAMP
- deleted_by INT
Methods select, insert, update, remove, and restore use these columns to keep track of recent changes. Instead of deleting rows, the remove function will set a deleted_at timestamp. This gives the ability to restore 'deleted' data by using the restore method, or simply nulling the deleted_at timestamp. You can later archive or delete rows where deleted_at is not null in a cron job.
By default, the select function will filter out any rows with a deleted_at timestamp. If you do want to see deleted rows add 'deleted: true' to the select options object.
created_by, updated_by, and deleted_by are optional, and are only added if 'userId' is present within a methods options parameter.
Usage
Of the various methods included in this libary, often times you will only need the 'Module' class, as it is an abstraction of all the other methods. You may not need multiple connection pools ether, in this case the default connection values are used. Use cases for calling select, insert, update, remove, restore directly include functions that dynamically set the model and pool when invoked.
Roles
Another opinionated feature of this package is role pools configured by enviornment variables. The module uses dotenv to populate env settings when you include a .env file in your project root folder. The default pool uses env vars:
- 'PGHOST',
- 'PGPORT',
- 'PGDATABASE',
- 'PGUSER',
- 'PGPASSWORD'
Specifying one or more pool names within the db.conect method will connect multiple pools. The connection will expect env vars:
- 'PG_${pool}_HOST',
- 'PG_${pool}_PORT',
- 'PG_${pool}_DATABASE',
- 'PG_${pool}_USER',
- 'PG_${pool}_PASSWORD'
Async
This library is built with promises and expects you to await method results. While it is a good idea to connect your pools when the app starts up, you don't have to. If you call any method without specifying a pool connection to use, it will use the default pool. If the default or specified connection pool has not yet been connected, it will wait until it connects, then call the method. Pools are cached in the db.pools object.
Model
You can use the Model class to abstract all CRUD methods into a model instance. This can simplify reasoning about your database operations.
Contributing
- To run the dev environment, execute 'docker-compose up -d' or 'docker.compose up -d' from project directory, depending on how you've installed docker-compose. This starts up a postgres instance on localhost:5432 and an 'adminer' process running on localhost:8000. You can use adminer as a web based database administration tool while developing.
- Any proposed new feature will need to have a test accomnpanying the pull request. All test must pass travis before accepted and merged.
async function example () {
const Employee = new Model('employees');
const employee = await Employee.create({
email: 'employee@comany.com'
});
await employee.update({
set: {
email: 'manager@company.com'
}
});
await employee.delete();
await employee.restore();
});Methods
Table of Contents
Query
const {query} = require('pg-role');Parameters
Examples
async function getEmployeesByDomain (domain) {
return await query(`
select * from employees
where email like '%@${domain}'
limit 1000;
`);
}async function getEmployeesByDomain (domain) {
return await query({
pool: 'admin',
text: `
select * from employees
where email like '%@${domain}'
limit 1000;
`
});
}Select
const {select} = require('pg-role');Parameters
-
optionsobject-
options.poolstring pool connection name (optional, default'default') -
options.schemastring database schema (optional, default'public') -
options.modelstring table to select from -
options.whereobject conditions -
options.idnumber add id to where conditions -
options.group(array | string) group by column(s) (optional, default'') -
options.offsetnumber offset result index (optional, default0) -
options.limitnumber limit result length (optional, default1000) -
options.deletedboolean do not filter rows with 'deleted_at' timestamp (optional, defaultfalse)
-
Examples
async function getEmployee (id) {
return await select({
model: 'employees',
id
});
}async function getDeletedEmployee (id) {
return await select({
model: 'employees',
id,
where: {
deleted_at: 'not null'
}
});
}async function getActiveEmployeesByRole (role) {
return await select({
model: 'employees',
where: {
role
}
});
}async function getAllEmployeesByRole (role) {
return await select({
model: 'employees',
deleted: true,
where: {
role
}
});
}Insert
const {insert} = require('pg-role');Parameters
-
optionsobject
Examples
async function newEmployee (set) {
return await insert({
model: 'employees',
set
});
}Update
const {update} = require('pg-role');Parameters
-
optionsobject
Examples
async function updateEmployee (id, set) {
return await update({
model: 'employees',
id,
set
});
}Remove
const {remove} = require('pg-role');Parameters
-
optionsobject
Examples
async function removeEmployee (id) {
return await remove({
model: 'employees',
id
});
}Restore
const {restore} = require('pg-role');Parameters
-
optionsobject
Examples
async function restoreEmployee (id) {
return await restore({
model: 'employees',
id
});
}Model
const {Model} = require('pg-role');Parameters
create
Parameters
-
setobject set values object
Examples
async function createEmployee (email) {
const employee = await Employee.create({
email
});
console.log(employee.get()));
}Returns ModelInstance
select
Parameters
-
optsobject options object
Examples
async function findEmployees() {
const employee = await Employee.select({
where: {
$like: {
email: '%@comapany.com'
}
}
});
}Returns object selectObject
find
Parameters
Examples
async function createEmployee (email) {
const Employee = new Model('employees');
const employee = await Employee.find({
email: testUserB
});
console.log(employee.get()));
}Returns ModelInstance
ModelInstance
model instance created by Model.create('model_name')
Parameters
Examples
async function messWithEmployee (email) {
const Employee = new Model('employee');
const instance = await Employee.create({
email: 'some.employee@company.com',
position: 'employee'
});
console.log(instance.get()); // {id: 45, email: 'employee@company.com', ...}
await instance.update({
position: 'manager'
});
console.log(instance.get()); // {id: 45, email: 'manager@company.com, ...}
await instance.delete();
console.log(instance.get()); // {id: 45, email: 'manager@company.com, deleted_at: '2018-09-11T04:44:36.725Z', ...}
await instance.restore();
console.log(instance.get()); // {id: 45, email: 'manager@company.com, ...}
}get
Parameters
-
propstring property to retrieve, if not specified all props are returned.
Examples
async function testGet() {
const Employee = new Model('employee');
const employee = await Employee.create({
email: 'employee@company.com'
});
console.log(employee.get()); // employee@company.com
employee.update({
email: 'manager@company.com'
});
console.log(employee.get()); // manager@company.com
}update
Parameters
set-
propsset set values object
Examples
async function updateEmployee(id, set) {
const Employee = new Model('employee');
const employee = await Employee.find(id);
employee.update(set);
}delete
Examples
async function deleteEmployee(id) {
const Employee = new Model('employee');
const employee = await Employee.find(id);
employee.delete();
}restore
Examples
async function deleteEmployee(id) {
const Employee = new Model('employee');
const employee = await Employee.find(id);
employee.restore();
}db
const {db} = require('pg-role');connect
creates pool connection if not exists and waits for all to connect
Parameters
client
Creates a new pool connection if pool doesn't alread exist. Returns that pool after connecting.
Parameters
-
rolestring
release
Release pool if available. If no role is specified it will release all pools.
Parameters
-
rolestring
