Skip to content

Instantly share code, notes, and snippets.

@robconery

robconery/jsonb.js

Last active Nov 11, 2020
Embed
What would you like to do?
A Simple JSONB Module for Node
const runner = require("./runner");
const transformRecord = function(record) {
if (record) {
const doc = record.doc;
doc.createdAt = record.created_at;
doc.id = record.id;
return doc;
} else {
return null;
}
};
const transformSet = function(res) {
if (res === null || res === []) return res;
const out = [];
for (let record of res) {
const doc = transformRecord(record);
out.push(doc);
}
return out;
};
exports.createDocTable = async function(tableName) {
await runner.query(`create table ${tableName}(
id serial primary key,
doc jsonb,
created_at timestamp default now()
)`);
await runner.query(
`create index idx_json_${tableName} on ${tableName} USING GIN (doc jsonb_path_ops)`
);
};
exports.modify = async function(tableName, id = 0, update = {}) {
if (!tableName) return;
const sql = `update customers SET
doc = (doc || $1)
where id = $2 returning *; `;
const res = await runner.one(sql, [update, id]);
return transformRecord(res);
};
exports.save = async function(tableName, doc) {
const sql = `insert into ${tableName}(doc) values ($1) returning *`;
try {
const res = await runner.one(sql, [doc]);
return transformRecord(res);
} catch (err) {
if (err.message.indexOf("does not exist") > 0) {
//create the table on the fly
await this.createDocTable(tableName);
return this.save(tableName, doc);
}
}
};
exports.delete = async function(id) {
const sql = `delete from ${tableName} where id=$1`;
await runner.execute(sql, [id]);
return true;
};
exports.get = async function(tableName, id = 0) {
const sql = `select * from ${tableName} where id=$1`;
const record = await runner.one(sql, [id]);
return transformRecord(record);
};
exports.find = async function(tableName, criteria) {
const sql = `select * from ${tableName} where doc @> $1`;
const record = await runner.query(sql, [criteria]);
return transformSet(record);
};
const pgp = require('pg-promise')({});
const db = pgp(process.env.DATABASE_URL);
exports.query = async function(sql, args){
const res = await db.any(sql, args);
return res;
}
exports.one = async function(sql, args){
const res = await db.oneOrNone(sql, args);
return res;
}
exports.execute = async function(sql, args){
const res = await db.none(sql, args);
return res;
}
exports.close = async function(){
await db.$pool.end();
return true;
}
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
You can’t perform that action at this time.