NodeJS and pg-promise, insert dynamically from JSON-object

Question

I'm running NodeJS and pg-promise, and are trying to accomplish somethings like:

db.none('INSERT INTO my-table (JSON-object-keys) VALUES ($1)', [JSON-object-values])
    .catch(function(err) {
        console.log('Error on insert into my-table: ' + err);
    });

I have JSON-objects which can look like:

{"column1":"value1", "column2":"value2", "column3":"value3"}
{"column2":"value2", "column3":"value3"}
{"column1":"value1", "column3":"value3"}

I would like to have the INSERTS automatically generated corresponding to what the JSON-object contains.

Is that possible in an elegant way?

Explained a bit more, in the 3 examples of JSON the following should be generated:

db.none('INSERT INTO my-table (column1, column2, column3) VALUES ($1, $2, $3)', [value1, value2, value3])
    .catch(function(err) {
        console.log('Error on insert into my-table: ' + err);
    });

db.none('INSERT INTO my-table (column2, column3) VALUES ($1, $2)', [value2, value3])
    .catch(function(err) {
        console.log('Error on insert into my-table: ' + err);
    });

db.none('INSERT INTO my-table (column1, column3) VALUES ($1, $2)', [value1, value3])
    .catch(function(err) {
        console.log('Error on insert into my-table: ' + err);
    });

The easiest way is to generate the same insert for a statically defined set of columns, and then for the columns that you do not want to insert, provide `DEFAULT` as the value. Before I can offer a usable example - what is the criteria for the columns to be skipped? - do the corresponding properties not exist or something? Also, give a complete list of columns that you have, specifying which of them can be optional. — vitaly-t, Apr 05 '17 at 12:02
In my testing table I have 3 columns named column1, column2 and column3. My JSON object may or may not contain all 3 columns keys - and I cannot guarantee the order in the json object. — Michael Nielsen, Apr 05 '17 at 12:08

score 2 · Accepted Answer · edited Jun 20 '20 at 09:12

Your pgp object + the input object with all the properties:

var pgp = require('pg-promise')({
    capSQL: true // capitalize all generated SQL
});

var inputObj = {
    /* all your optional properties */
};

Define the raw-text type, using Custom Type Formatting:

var rawText = text => ({_rawType: true, toPostgres: () => text});

Create a generic default column, according to class Column:

var defCol = name => ({name, def: rawText('DEFAULT')});
// which is the same as:
var defCol = name => new pgp.helpers.Column({name, def: rawText('DEFAULT')});

Generate the list of default-able columns:

var cols = Object.keys(inputObj).map(defCol);

Create a ColumnSet with those columns:

var cs = new pgp.helpers.ColumnSet(cols, {table: 'my-table'});

When it is time to generate an insert query, you can do:

var insert = pgp.helpers.insert(inputObj, cs);

Recommended Approach

If you know the columns in advance, then you should just do the following:

var cs = new pgp.helpers.ColumnSet(
                    [defCol('column1'), defCol('column2'), defCol('column3')], 
                    {table: 'my-table'});

A static cs object will always provide a much better performance.

This approach is also safer, because you do not need to verify whether there is at least one property in the object, 'cos if there isn't, you'll get an error saying that it is impossible to generate an insert when there are no columns.

And this approach also works with multi-row inserts, which is very important. See also: Multi-row insert with pg-promise.

Getting TypeError: Cannot read property 'ColumnSet' of undefined. — Michael Nielsen, Apr 05 '17 at 13:26
My pgp is defined as var pgp = pgPromise('postgres://my-user@localhost/my-db'); — Michael Nielsen, Apr 05 '17 at 13:29
Syntax for Custom Type Formatting has been updated, as it changed in [v6.5.0](https://github.com/vitaly-t/pg-promise/releases/tag/v.6.5.0). — vitaly-t, Aug 18 '17 at 09:14

NodeJS and pg-promise, insert dynamically from JSON-object

1 Answers1

Linked