ColumnSet Helpers - using Raw only when default

Question

I'm trying to use ColumnSet helpers to generate the insert & update query, but I have a column that I want to format the data using pg-promise query-formatter if passed in, or otherwise the default is :raw (^), which in this case is now().

Code example is like this:

const cs = new helpers.ColumnSet([
    'lastname',
    {
        name: 'rental_date',
        mod: '^',
        def: 'now()'
    }
], { table: { table: 'book_rental', schema: 'public' } })


let rental1 =
    {
        lastname: 'Mueller', rental_date: '2020-05-01T12:15:063Z'
    };

let rental2 =
    {
        lastname: 'Smith'
    };


let insert = helpers.insert(rental1, cs)

db.result(insert)
    .then(data => res.json({ message: 'Ok!' }))
    .catch(err => res.json({ message: 'Not ok!' }))

Rental1 should INSERT INTO (last_name, rental_date) VALUES ('Mueller', '2020-05-01T12:15:063Z' ) while Rental2 should INSERT INTO (last_name, rental_date) VALUES ('Smith', now() ). However, this throws up an error as since the Rental1 is also formatted as :raw.

This is probably a common use-case so I'm likely missing something ... how do I achieve this using the helpers so that only if default is triggered, the :raw mode is used?

Answer 1

From the Raw Text documentation:

Such variables cannot be null or undefined, because of the ambiguous meaning in this case, and those values will throw error Values null/undefined cannot be used as raw text.

Default values, provided via property def are documented to be used only when the property is missing:

Default value for the property, to be used only when the source object doesn't have the property.

This means that you are creating a logical contradiction that cannot work.

---

There can be any number of valid solutions in your case, as the library's syntax is very flexible. Below are just some examples you can choose from.

approach 1

{
    name: 'rental_date',
    init: c => c.exists ? c.value : {toPostgres: () => 'now()', rawText: true}
}

approach 2

{
    name: 'rental_date',
    mod: ':raw',
    init: c => c.exists ? pgp.as.text(c.value) : 'now()'
}

approach 3 - clean and reusable example, using modern syntax:

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

{
    name: 'rental_date',
    init: c => c.value ?? rawText('now()')
}

approach 4 - mixing with the use of def:

{
    name: 'rental_date',
    def: 'now()',
    mod: ':raw',
    init: c => c.exists ? pgp.as.text(c.value) : c.value
}

approach 5:

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

{
    name: 'rental_date',
    def: rawText('now()')
}

...and so on.

Perhaps solution 5 is the best for what you want, it is both the cleanest and reusable.