objection.js: Bug: whereNotExists missing from generated query

I have this query:

app.db.Text.query().whereNotExists({
    "textId": request.body.textId,
    "languageCode": text.languageCode,
}).insert({
    "textId": request.body.textId,
    "languageCode": text.languageCode,
    "content": text.content,
    "contentOrigin": text.contentOrigin,
})

It produces this output:

insert into "maptionnaire"."MultilingualText" ("content", "contentOrigin", "languageCode", "textId") values ('LOL', 'userEntry', '??', 'test') returning "textId", "languageCode"
{ method: 'insert',
  options: {},
  timeout: false,
  cancelOnTimeout: false,
  bindings: [ 'LOL', 'userEntry', '??', 'test' ],
  __knexQueryUid: '49df64ca-0730-49c6-884f-c5e00e9d5a98',
  sql: 'insert into "maptionnaire"."MultilingualText" ("content", "contentOrigin", "languageCode", "textId") values (?, ?, ?, ?) returning "textId", "languageCode"',
  returning: [ 'textId', 'languageCode' ] }

And because the where not exists clause is missing from the generated query I then get this error:

{ error: insert into "maptionnaire"."MultilingualText" ("content", "contentOrigin", "languageCode", "textId") values ($1, $2, $3, $4) returning "textId", "languageCode" - duplicate key value violates unique constraint "MultilingualText_pkey"
    at Connection.parseE (/Users/pineapple/git/mapita/maptionnaire2/src/server/node_modules/pg/lib/connection.js:567:11)
    at Connection.parseMessage (/Users/pineapple/git/mapita/maptionnaire2/src/server/node_modules/pg/lib/connection.js:391:17)
    at Socket.<anonymous> (/Users/pineapple/git/mapita/maptionnaire2/src/server/node_modules/pg/lib/connection.js:129:22)
    at emitOne (events.js:96:13)
    at Socket.emit (events.js:188:7)
    at readableAddChunk (_stream_readable.js:176:18)
    at TCP.onread (net.js:547:20)
  name: 'error',
  length: 241,
  severity: 'ERROR',
  code: '23505',
  detail: 'Key ("textId", "languageCode")=(test, ??) already exists.',
  hint: undefined,
  position: undefined,
  internalPosition: undefined,
  internalQuery: undefined,
  where: undefined,
  schema: 'maptionnaire',
  table: 'MultilingualText',
  column: undefined,
  dataType: undefined,
  constraint: 'MultilingualText_pkey',
  file: 'nbtinsert.c',
  line: '406',
  routine: '_bt_check_unique' }

About this issue

Original URL
State: closed
Created 7 years ago
Comments: 20 (7 by maintainers)

Most upvoted comments

For any future users trying to write an upsert with objection/knex, here is my solution:

The insertion argument is an object like would be passed to model.query().insert(...). conflictIds is an array of columns to look at for conflicts, by default the model’s primary keys.

This should work for psql versions 9.5 and above. It will not work for prior versions of postgres or for other sql databases.

class MyQueryBuilder extends objection.QueryBuilder{
    // https://github.com/Vincit/objection.js/issues/447
    upsert(insertion, conflictIds = undefined){
        // Get conflict columns, default to ID columns
        let conflicts = conflictIds || this.modelClass().idColumn;
        if(typeof(conflicts) === "string") conflicts = [conflicts];
        // Build the "ON CONFLICT (...)" part of the query.
        let conflictSql = "";
        const conflictBindings = [];
        for(const conflictName of conflicts){
            if(conflictSql.length) conflictSql += ", ";
            conflictSql += "??";
            conflictBindings.push(conflictName);
        }
        const onConflict = this.modelClass().raw(
            "(" + conflictSql + ")", conflictBindings
        );
        // Build the "DO UPDATE SET ..." part of the query
        // Build the INSERT INTO <table> (...) part of the query
        // Build the VALUES(...) part of the query
        // Build the RETURNING ... part of the query
        let setSql = "";
        const setBindings = [];
        let insertColumnsSql = "";
        const insertColumnBindings = [];
        let valuesSql = "";
        const valuesBindings = [];
        let returningSql = "";
        const returningBindings = [];
        for(const columnName in insertion){
            if(setSql.length){
                setSql += ", ";
                insertColumnsSql += ", ";
                valuesSql += ", ";
                returningSql += ", ";
            }
            setSql += "?? = EXCLUDED.??";
            insertColumnsSql += "??";
            valuesSql += "?";
            returningSql += "??";
            setBindings.push(columnName, columnName);
            insertColumnBindings.push(columnName);
            valuesBindings.push(insertion[columnName]);
            returningBindings.push(columnName);
        }
        const onConflictSet = this.modelClass().raw(setSql, setBindings);
        const insertColumns = this.modelClass().raw(
            "(" + insertColumnsSql + ")", insertColumnBindings
        );
        const values = this.modelClass().raw(
            "(" + valuesSql + ")", valuesBindings
        );
        const returning = this.modelClass().raw(
            returningSql, returningBindings
        );
        return this.modelClass().raw(
            "INSERT INTO ?? ? VALUES ? ON CONFLICT ? DO UPDATE SET ? RETURNING ?", [
                this.modelClass().tableName, insertColumns, values,
                onConflict, onConflictSet, returning
            ]
        );
    }
}

pineapplemachine on Jul 31, 2017