'default' option in pgp.as.format()

I need to format SQL query with default option for missing object fields. I can do it with an external call to pgp.as.format:

let formattedQuery = pgp.as.format('INSERT INTO some_table (a,b,c) VALUES ($(a), $(b), $(c))', object, {default: null}); 
db.none(formattedQuery);

Is it possible to pass default option directly without pre-formatting the query? Basically, i would like to do something like this:

db.none('INSERT INTO some_table (a,b,c) VALUES ($(a), $(b), $(c))', object, {default: null})

Solution

I'm the author of pg-promise.

All query methods in pg-promise rely on the default query formatting, for better reliability, i.e. when a query template refers to a property, the property must exist, or else an error is thrown. It is logical to keep it that way, because a query cannot execute correctly while having properties in it that haven't been replaced with values.

Internally, the query engine does support advanced query formatting options, via method as.format, such as partial and default. And there are several objects in the library that make use of those options.

One in particular that you should use for generating inserts is helpers.insert, which can generate both single-insert and multi-insert queries. That method, along with even more useful helpers.update make use of type ColumnSet, which is highly configurable, supporting default values for missing properties (among other things), via type Column.

Using ColumnSet, you can specify a default value either for selective columns or for all of them.

For example, let's assume that column c may be missing, in which case we want to set it to null:

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

// declaring a reusable ColumnSet object:
var csInsert = new pgp.helpers.ColumnSet(['a', 'b',
    {
        name: 'c',
        def: null
    }
], {table: 'some_table'});

var data = {a:1, b:'text'};

// generating our insert query:
var insert = pgp.helpers.insert(data, csInsert);
//=> INSERT INTO "some_table"("a","b","c") VALUES(1,'text',null)

This makes it possible to generate multi-insert queries automatically:

var data = [{a:1, b:'text'}, {a:2, b:'hello'}];

// generating a multi-insert query:
var insert = pgp.helpers.insert(data, csInsert);
//=> INSERT INTO "some_table"("a","b","c") VALUES(1,'text',null),(2,'hello',null)

The same approach works nicely for single-update and multi-update queries.

In all, to your original question:

Is it possible to pass default option directly without pre-formatting the query?

No, and neither it should. Instead, you should use the aforementioned methods within the helpers namespace to generate correct queries. They are way more powerful and flexible ;)