0

I'm using pg-promise to build inserts and updates with the helpers.insert and helpers.update.

Update works as desired but I keep running into a similar problem to this question: pg-promise helpers : optionnal fields in insert and multiple-update

i.e. optional columns with postgresql defaults cause the helpers.insert to throw an error when the property doesn't exist on the input data. On updates this triggers the skip functionality and it's fine, but as the docs indicate skip doesn't work on insert so it errors.

const input = { yup: true };

const query = this.pgp.helpers.insert(
  input,
  new this.pgp.helpers.ColumnSet(
    [
      { name: 'yup', skip: (col) => !col.exists },
      { name: 'nope', skip: (col) => !col.exists },

I'm wonder why skip doesn't work on insert? Is there an alternative I'm missing?

Using def seems a bit dicey as I want to make use of the postgresql defaults rather than override them.

I've hunted around the interwebs and even found @vitaly-t answering the same question in 2016 using the skip functionality which seems to have been subsequently removed from helpers.insert.

So I'm stuck. How do I get the same skip functionality working for inserts?

Paul Hill
  • 31
  • 4
  • `skip` logic doesn't add any value to inserts, that's why it is not used for inserts. It is only used for updates, where conditional columns can be present. For inserts, just reuse the existing `ColumnSet` object, with those columns redefined, using method [merge](https://vitaly-t.github.io/pg-promise/helpers.ColumnSet.html#merge). – vitaly-t Oct 28 '20 at 00:16
  • Hi vitaly-t, I'm probably missing something but I have a lot of optional columns in my inserts. They are booleans that default to various true/false states and I'd like to be able to just omit them in the input object, and have the same behaviour as update. Currently I have to explicitly def all of them. It works but it's a lot of boilerplate for something postgresql already does for me. – Paul Hill Oct 28 '20 at 04:10
  • e.g. foo boolean NOT NULL DEFAULT false, bar boolean NOT NULL DEFAULT true, baz boolean NOT NULL DEFAULT false, – Paul Hill Oct 28 '20 at 04:13
  • What you missing is that for inserts, values are tied to the list of column names. You cannot skip one and not the other. While for updates, it is just a set of assignments, that's what makes `skip` logic possible. – vitaly-t Oct 28 '20 at 14:01
  • I see what you mean as far as how the method operates. I reckon I still have a use case for a single insert with skip. So I have a table with booleans, with defaults, and optional fk relationships. I don't get the fk id in the JS service method, I get a uuid so I'm inserting a subselect to insert the fk I need. INSERT INTO foo (a, b, c) VALUES (SELECT id FROM bar WHERE ...), ... Because I have varied inserts where the fks exist (or do not), I'm forced to have a column set for each combination. Building that dynamically would be ideal which is what the skip functionality provides. – Paul Hill Oct 28 '20 at 16:23
  • Note: I'm aware this wouldn't work for multiple inserts. But for single row inserts it would be very handy. It would save me having to build a column set for each combination of optional fks and picking the right one based on the input object props. That approach gets pretty unwieldy with just a few optional fk relationships. – Paul Hill Oct 28 '20 at 16:26
  • I should clarify that the reason def: null is awkward for optional fks is I have this helper method I'm trying to use and I want to skip it when the prop is undefined like I can in update: ```javascript export function relationship( table: string, name: string, prop?: string, ): IColumnConfig { return { name: name, prop: prop, mod: ':raw', init: (col) => `(SELECT ${table}.id FROM ${table} WHERE ${table}.uuid = $<${col.name}>)`, skip: (col) => !col.exists, }; } ``` – Paul Hill Oct 28 '20 at 16:31
  • I'm not sure what to make of such `ColumnSet`, generating a whole `SELECT` inside the `init`, and how this pertains to generating an `INSERT` query. – vitaly-t Oct 28 '20 at 16:53
  • I you have some complex formatting scenario, there is [Custom Type Formatting](https://github.com/vitaly-t/pg-promise#custom-type-formatting), which lets you do anything. – vitaly-t Oct 28 '20 at 16:55
  • Thanks, I got it working the way I wanted by adding a check for value === undefined in my little relationship column init function. A combination of that and def for booleans seems to cover all the bases. I will look into Custom Type Formatting for joins. Thanks for your help @vitaly-t :-) – Paul Hill Oct 28 '20 at 17:15

1 Answers1

0

So here is what I came up with in case anyone finds themselves in the same position:

export function column(name: string, prop?: string): IColumnConfig {
  return {
    name: name,
    prop: prop,
    skip: (col) => !col.exists,
  };
}

export function relationship(
  table: string,
  name: string,
  prop?: string,
): IColumnConfig {
  return {
    name: name,
    prop: prop,
    mod: ':raw',
    init: (col) => {
      if (!col.exists || isEmpty(col.value)) {
        return 'NULL';
      }
      return `(SELECT ${table}.id FROM ${table} WHERE ${table}.uuid = $<${col.name}>)`;
    },
    skip: (col) => !col.exists,
  };
}

    const insert =
      this.pgp.helpers.insert(
        input,
        new this.pgp.helpers.ColumnSet(
          [
            relationship('bar', 'bar', 'barID'),
            { name: 'name' },
            { name: 'is_a', prop: 'isA', def: false },
            { name: 'is_b', prop: 'isB', def: false },
            { name: 'is_c', prop: 'isC', def: false },
            relationship('baz', 'baz', 'bazID'), // optional
          ],
          { table: 'foo' },
        ),
      ) +
      `
      RETURNING ${select}
      `;

    return await this.db.one(insert, input);

I can't say this is the most elegant approach. But it worked for what I was trying to achieve.

Paul Hill
  • 31
  • 4