Knex.js insert from select

Question

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?

Answer 1

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.

Answer 2

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 copy of that solution:

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

  // and here is the second part of the SQL with "SELECT"
  .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'

---

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

Answer 3

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.