Last active
April 21, 2021 08:10
-
-
Save robconery/93aaec861fdf0cf3f5ff3f30f1cf11d5 to your computer and use it in GitHub Desktop.
A Simple JSONB Module for Node
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
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); | |
}; |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
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