I'm trying to generate a query like the following via Knex.js:

    INSERT INTO table ("column1", "column2")
    SELECT "someVal", 12345
    WHERE NOT EXISTS (
        SELECT 1
        FROM table
        WHERE "column2" = 12345
    )

Basically, I want to insert values only if a particular value does not already exist. But Knex.js doesn't seem to know how to do this; if I call knex.insert() (with no values), it generates an "insert default values" query.

I tried the following:

    pg.insert()
        .into(tableName)
        .select(_.values(data))
        .whereNotExists(
            pg.select(1)
                .from(tableName)
                .where(blah)
        );

but that still just gives me the default values thing. I tried adding a .columns(Object.keys(data)) in hopes that insert() would honor that, but no luck.

Is it possible to generate the query I want with knex, or will I just have to build up a raw query, without Knex.js methods?

I'm trying to generate a query like the following via Knex.js:

    INSERT INTO table ("column1", "column2")
    SELECT "someVal", 12345
    WHERE NOT EXISTS (
        SELECT 1
        FROM table
        WHERE "column2" = 12345
    )

Basically, I want to insert values only if a particular value does not already exist. But Knex.js doesn't seem to know how to do this; if I call knex.insert() (with no values), it generates an "insert default values" query.

I tried the following:

    pg.insert()
        .into(tableName)
        .select(_.values(data))
        .whereNotExists(
            pg.select(1)
                .from(tableName)
                .where(blah)
        );

but that still just gives me the default values thing. I tried adding a .columns(Object.keys(data)) in hopes that insert() would honor that, but no luck.

Is it possible to generate the query I want with knex, or will I just have to build up a raw query, without Knex.js methods?

• javascript
• node.js
• knex.js

3 Answers 3

The most comprehensive answer I've found (with explicit column names for INSERT, and custom values in the SELECT-statement) is here:

https://github.com/knex/knex/issues/1056#issuecomment-156535234

^{by Chris Broome}

here is a modified copy of that solution:

const query = knex
  // this part generates INSERT-clause of the
  // query (with column-names): INSERT "tablename" ("field1", "field2" ..)
  .into(knex.raw('?? (??, ??)', ['tableOrders', 'field_user_id', 'email_field']))

  // and this part generates the "SELECT"-clause (even though it starts with `.insert`)
  .insert(function() {
    this
      .select(
        'user_id',  // select from column without alias
        knex.raw('? AS ??', ['[email protected]', 'email']),  // select static value with alias
      )
      .from('users AS u')
      .where('u.username', 'jdoe')
   });
console.log(query.toString());

and the SQL-output:

insert into "orders" ("user_id", "email")
  select "user_id", '[email protected]' AS "email"
  from "users" as "u"
  where "u"."username" = 'jdoe'

using this approach one can for example fill a "profile"-table from pre-defined "templates"-table (select from template), and at the same time merge the template data with user preferences/other values (like NOW()).

another one approach (by Knex developer): https://github.com/knex/knex/commit/e74f43cfe57ab27b02250948f8706d16c5d821b8#diff-cb48f4af7c014ca6a7a2008c9d280573R608 - also with knex.raw

I believe the select needs to be passed into the insert:

pg.insert(knex.select().from("tableNameToSelectDataFrom")).into("tableToInsertInto");

Also in order to select constant values or expressions you'll need to use a knex.raw expression in the select:

knex.select(knex.raw("'someVal',12345")).from("tableName")

This is my first post and I didn't test your specific example but I've done similar things like what you're asking using the above techniques.

I've managed to make it work in my project and it doesn't look all that bad!

        .knex
            .into(knex.raw('USER_ROLES (ORG_ID, USER_ID, ROLE_ID, ROLE_SOURCE, ROLE_COMPETENCE_ID)'))
            .insert(builder => {
                builder
                    .select([
                        1,
                        'CU.USER_ID',
                        'CR.ROLE_ID',
                        knex.raw(`'${ROLES.SOURCE_COMPETENCE}'`),
                        competenceId,
                    ])
                    .from('COMPETENCE_USERS as CU')
                    .innerJoin('COMPETENCE_ROLES as CR', 'CU.COMPETENCE_ID', 'CR.COMPETENCE_ID')
                    .where('CU.COMPETENCE_ID', competenceId)
                    .where('CR.COMPETENCE_ID', competenceId);

Note that this doesn't seem to work properly with the returning clause on MSSQL (it's just being ignored by Knex) at the moment.