Implement RETURNING clause
georgolden opened this issue · 1 comments
Is your feature request related to a problem? Please describe.
insert, update, delete methods from class Database are not very useful without returning an auto-generated ID. I am using mostly query method and writing SQL queries by myself with RETURNING clause.
Describe the solution you'd like
Add an opportunity to use RETURNING clause with insert, update, delete methods.
Describe alternatives you've considered
Maybe add support to other useful clauses too. For example, ON CONFLICT for 'INSERT' operation.
Additional context
Implementation may look something like that:
returning(fields, sql) {
if (fields.length > 0) {
const data = fields.toString();
sql += ` RETURNING ${data}`;
}
return;
};
insert(table, record, output = []) {
const keys = Object.keys(record);
const nums = new Array(keys.length);
const data = new Array(keys.length);
let i = 0;
for (const key of keys) {
data[i] = record[key];
nums[i] = `$${++i}`;
}
const fields = '"' + keys.join('", "') + '"';
const params = nums.join(', ');
let sql = `INSERT INTO "${table}" (${fields}) VALUES (${params})`;
returning(output, sql);
return this.query(sql, data);
};There is a small problem with this approach. pg.pool.query will return not just fields, that are given with clause RETURNING. It will return an object from pg. Data is available by data.rows. It will be better to simplify returning object and return just data.rows
Returning object looks like this:
result: "success",
data: {
command: "INSERT",
rowCount: 1,
oid: 0,
rows: [
{
countryId: "8",
name: "Kekistans"
}
],
fields: [
{
name: "countryId",
tableID: 16438,
columnID: 1,
dataTypeID: 20,
dataTypeSize: 8,
dataTypeModifier: -1,
format: "text"
},
{
name: "name",
tableID: 16438,
columnID: 2,
dataTypeID: 1043,
dataTypeSize: -1,
dataTypeModifier: -1,
format: "text"
}
],
_parsers: [
null,
null
],
_types: {...},
RowCtor: null,
rowAsArray: false
}