opinionated pg abstraction
This package requires you to create models with these columns:
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.
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.
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:
Specifying one or more pool names within the db.conect method will connect multiple pools. The connection will expect env vars:
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.
You can use the Model class to abstract all CRUD methods into a model instance. This can simplify reasoning about your database operations.
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();
});
const {query} = require('pg-role');
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;
`
});
}
const {select} = require('pg-role');
options object
options.pool string pool connection name (optional, default 'default')options.schema string database schema (optional, default 'public')options.model string table to select fromoptions.where object conditionsoptions.id number add id to where conditionsoptions.group (array | string) group by column(s) (optional, default '')options.offset number offset result index (optional, default 0)options.limit number limit result length (optional, default 1000)options.deleted boolean do not filter rows with 'deleted_at' timestamp (optional, default false)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
}
});
}
const {insert} = require('pg-role');
options object
async function newEmployee (set) {
return await insert({
model: 'employees',
set
});
}
const {update} = require('pg-role');
options object
async function updateEmployee (id, set) {
return await update({
model: 'employees',
id,
set
});
}
const {remove} = require('pg-role');
options object
async function removeEmployee (id) {
return await remove({
model: 'employees',
id
});
}
const {restore} = require('pg-role');
options object
async function restoreEmployee (id) {
return await restore({
model: 'employees',
id
});
}
const {Model} = require('pg-role');
set object set values objectasync function createEmployee (email) {
const employee = await Employee.create({
email
});
console.log(employee.get()));
}
Returns ModelInstance
opts object options objectasync function findEmployees() {
const employee = await Employee.select({
where: {
$like: {
email: '%@comapany.com'
}
}
});
}
Returns object selectObject
async function createEmployee (email) {
const Employee = new Model('employees');
const employee = await Employee.find({
email: testUserB
});
console.log(employee.get()));
}
Returns ModelInstance
model instance created by Model.create('model_name')
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, ...}
}
prop string property to retrieve, if not specified all props are returned.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
}
setprops set set values objectasync function updateEmployee(id, set) {
const Employee = new Model('employee');
const employee = await Employee.find(id);
employee.update(set);
}
async function deleteEmployee(id) {
const Employee = new Model('employee');
const employee = await Employee.find(id);
employee.delete();
}
async function deleteEmployee(id) {
const Employee = new Model('employee');
const employee = await Employee.find(id);
employee.restore();
}
const {db} = require('pg-role');
creates pool connection if not exists and waits for all to connect
Creates a new pool connection if pool doesn't alread exist. Returns that pool after connecting.
role stringRelease pool if available. If no role is specified it will release all pools.
role stringFAQs
opinionated pg abstraction
We found that pg-role demonstrated a not healthy version release cadence and project activity because the last version was released a year ago. It has 1 open source maintainer collaborating on the project.
Did you know?
Socket for GitHub automatically highlights issues in each pull request and monitors the health of all your open source dependencies. Discover the contents of your packages and block harmful activity before you install or update your dependencies.
Security News
OpenAI rotated macOS signing certificates after a malicious Axios package reached its CI pipeline in a broader software supply chain attack.
Security News
Open source is under attack because of how much value it creates. It has been the foundation of every major software innovation for the last three decades. This is not the time to walk away from it.
Security News
Socket CEO Feross Aboukhadijeh breaks down how North Korea hijacked Axios and what it means for the future of software supply chain security.