I updated knex from 0.21 to 0.95 following their migration guide, Now im geting this Error on CI when its running npx knex migrate:latest

migration file "20191104160706_migrate-appsflyer_customers.js" failed
migration failed with error: The query is empty
    at createQueryBuilder (/home/circleci/backend/node_modules/knex/lib/knex-builder/make-knex.js:313:26)

but the migration file contains the query's

async function up (knex) {
  // language=Postgres
  const { rows } = await knex.raw(`
    SELECT * FROM appsflyer_customer;
  `)
  const mappedRows = rows.map(row => ({
    user_id: row.user_id,
    advertising_id_type: 'appsflyer',
    advertising_id: row.appsflyer_device_id
  }))
  await knex('device_advertising_association')
    .insert(mappedRows)
}
async function down (knex) {
  await knex.raw(`
    DELETE FROM device_advertising_association WHERE user_id NOTNULL;
  `)
}
module.exports = {
  up, down
}

Any help would be greatly appreciated as im getting no where with the error message

I updated knex from 0.21 to 0.95 following their migration guide, Now im geting this Error on CI when its running npx knex migrate:latest

migration file "20191104160706_migrate-appsflyer_customers.js" failed
migration failed with error: The query is empty
    at createQueryBuilder (/home/circleci/backend/node_modules/knex/lib/knex-builder/make-knex.js:313:26)

but the migration file contains the query's

async function up (knex) {
  // language=Postgres
  const { rows } = await knex.raw(`
    SELECT * FROM appsflyer_customer;
  `)
  const mappedRows = rows.map(row => ({
    user_id: row.user_id,
    advertising_id_type: 'appsflyer',
    advertising_id: row.appsflyer_device_id
  }))
  await knex('device_advertising_association')
    .insert(mappedRows)
}
async function down (knex) {
  await knex.raw(`
    DELETE FROM device_advertising_association WHERE user_id NOTNULL;
  `)
}
module.exports = {
  up, down
}

Any help would be greatly appreciated as im getting no where with the error message

• javascript
• node.js
• postgresql
• knex.js

2 Answers 2

So i was getting this error since Knex 0.95 introduced a new feature https://github./knex/knex/pull/4289 so that if an empty array is passed to insert it will throw an error which was not present before

and since we didn't use that table it was empty and this above migration was trying to insert an empty array which was throwing the error on CI so I just basically handled the Exception with a try-catch block and it got resolved

so as a note look at the change logs carefully

I'm using node.js 14 and knex 0.95.

Here's a fix specifically for knex(...).insert([]) but use with caution as noted below. Despite the function name, monkeyPatchKnexForMinor21ToMinor95Migration, it's patching 1 issue, nothing else.

TODO: this breaks usage such as const r = await knex('my_table').insert([]).returning('*'); This would throw something like TypeError: knex('my_table').insert(...).returning is not a function

function monkeyPatchKnexForMinor21ToMinor95Migration() {
  const Builder = require('knex/lib/query/querybuilder');
  const QueryInterface = require('knex/lib/query/method-constants');

  Builder.prototype.insert = new Proxy(Builder.prototype.insert, {
    apply: function (target, thisArg, args) {
      const attemptedInsertWithEmptyArray = args?.[0]?.length === 0
        && args?.[0] instanceof Array;

      if (attemptedInsertWithEmptyArray) {
        return target.call(thisArg, ...args).catch(error => {
          if (error.message === 'The query is empty') {
            // very loose shallow mock of knex Result type based on what 
            // [email protected] would return for insertion of empty array                
            return {
              mand: null,
              rowCount: null,
              oid: null,
              rows: [],
              fields: [],
              RowCtor: null,
              rowAsArray: false,
            };
          }

          throw error;
        });
      }

      return target.call(thisArg, ...args);
    }
  });

  const knex = require('knex');
  knex.QueryBuilder = {
    extend: function (methodName, fn) {
      Builder.extend(methodName, fn);
      QueryInterface.push(methodName);
    }
  };
  
  return knex;
}

To use: where ever you import knex, you should call this function instead. An alternative to this is you could try to monkey patch the require('knex') call itself and do that in your initialization before app & client creations and importing of dependencies.