NodeJS and pg-promise, insert dynamically from JSO

问题:

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

回答1:

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.