A Simple JSONB Module for Node

jsonb.js

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);
};

runner.js

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;
}