Pg-promise: Automatic INSERT generation

[milabs]

Hello,

Is it possible to use the following construction:

var values = {
  a: 1, b: true, c: 'hello'
};

db.query("INSERT INTO table (%KEYS%) VALUES (%VALS%)", values) ...

By %KEYS and %VALS% I meant the values object keys and their values that automatically binds to the query.

[vitaly-t]

Not directly, as this library is not an ORM.

But it has a very powerful query formatting engine, which can easily generate any kind of queries.

var pgp = require('pg-promise');

// Helper to generate an INSERT query from:
// - table name
// - object with values
function insert(table, values) {
    var keys = Object.keys(values);
    return pgp.as.format('INSERT INTO $1~($2^) VALUES($3^)', [
        table,
        keys.map(k => pgp.as.name(k)).join(', '),
        keys.map(k => '${' + k + '}').join(', ')
    ]);
}

var values = {
    a: 1, b: true, c: 'hello'
};

var query = insert('Table Name', values);
console.log(query);

Output:

INSERT INTO "Table Name"("a", "b", "c") VALUES(${a}, ${b}, ${c})

See also:

• issue #49, issue #89
• SQL Names
• Raw Text

[milabs]

Thanks for the explanation. It's really powerful. Why not to add this helper function to the library? Don't think that my case is unique...

[vitaly-t]

Why not to add this helper function to the library?

Too many variations, it is not generic enough.

Examples:

• insert that returns a raw/value would end with something like RETURNING */RETURNING id
• insert that uses an internal SELECT as the source of values
• multi-value insert, see Performance Boost
• Applying filters on column names

[milabs]

OK, but wouldn't it useful to have something like pgp.as.keys() and pgp.as.vals()? Anyway, as your answer has the solution it would be nice to have faq/wiki reference for that.

[vitaly-t]

Those would have little value as separate functions.

For example:

function getKeyNames(values) {
    return Object.keys(values).map(k => pgp.as.name(k)).join(', ');
}

But the important part would be to use the same list of keys (sometimes you may want to filter them).

Anyway, I'll think about it.

[milabs]

Just for the possible supposition of query formatting it may be useful to have something like:

db.query("INSERT INTO table(@1~keys) VALUES(@1~vals)", obj)

Where @1~keys and @1~vals is the first object's keys and their values. Strictly speaking, ~keys and ~vals are not needed there, but check for paired @1 / @1 tokens required.

[vitaly-t]

This library doesn't support syntax like @1~keys. It supports syntax $1~ for inserting an sql name/identifier. The current syntax allows easy custom formatting, see Custom Type Formatting.

The current formatting is already advanced enough, most users don't use all its nice features. I wouldn't consider advancing it further.

In addition, your idea goes against the custom formatting syntax, which allows any type of customization, while your syntax fixes it to a single possible outcome, while that also may need advanced formatting options, such as keys transformation/filtering.

With that insert I gave you the simplest approach on which you can easily build to fit your app's needs. That's the way it works best.

[milabs]

Ok

[vitaly-t]

There is a further progress with this in 4.0.6 update ;)

[vitaly-t]

UPDATE:
There is lots more the library offers now with the helpers namespace when it comes to automatic query generation for INSERT or UPDATE operations.

[vitaly-t]

Syntax for SQL Names has been extended in v.5.2.1, to support enumeration of keys.

[vitaly-t]

With the latest version of the library, filter :csv / :list will enumerate values. See CSV Filter.

examples

const obj = {first: 123, second: 'text'};

db.none('INSERT INTO table($1:name) VALUES($1:csv)', [obj])
//=> INSERT INTO table("first","second") VALUES(123,'text')

db.none('INSERT INTO table(${this:name}) VALUES(${this:csv})', obj)
//=> INSERT INTO table("first","second") VALUES(123,'text')