Knex migration: Transaction query already complete

Question

Using knex I want to add 2 additional columns to an existing table. To pre-existing records I'd like to add a value based on a calculation. Below is my migration file. It fails on the 2nd line: Cannot read property 'resolve' of undefined.

exports.up = function (knex, Promise) {
  return Promise.resolve()
    .then(function(){
            ... cut to shorten question...
    })
};

---

Update: I removed the Promise and now have the migration code below. This still produces two errors, probably related to the forEach loop and that it doesn't wait for one part of the loop to finish before it goes on with the next part (but I don't know how else to do the loop):

Unhandled rejection MigrationLocked: Migration table is already locked

Transaction query already complete, run with DEBUG=knex:tx for more info

const Coupon = require('../../models/coupon');
const Plan = require('../../models/plan');

exports.up = function (knex) {
    return knex.schema.table('transactions', (table) => {
      table.decimal('plan_price', 10, 2);
      table.decimal('discount', 10, 2).defaultTo(0);
    })

    .then(function(return_value){
      knex.select().from('transactions')
      .then((transactions) => {
        transactions.forEach(async function(trans){
          let original_price;
          let total_coupon_discount = 0;

          const plan = await Plan.where({id: trans.plan_id}).fetch();
          if (plan) { original_price = plan.get("price") };

          if (trans.coupon_id) {
            const coupon = await Coupon.where({id: trans.coupon_id}).fetch();
            if (coupon) {
              total_coupon_discount = coupon.get("discount_amount");
              original_price = trans.amount_ex_vat + couponAmount;
            }
          }

          const promise = await knex('transactions')
          .where('id', '=', trans.id)
          .update({
            plan_price: original_price,
            discount: total_coupon_discount
          }).catch(function(error){
            console.log(error)
          }).then(function(){
            console.log('Added data to transaction record');
          })
        })
      })

      return return_value;
    })
};

exports.down = function (knex) {
  return knex.schema.table('transactions', (table) => {
    table.dropColumn('plan_price');
    table.dropColumn('discount');
  });
};

---

Update2: The suggested migration syntax in @Rich Churcher's answer made the migration work. But the error message about the MigrationLock remained. Here a similar issue is discussed. It is suggested to remove locks from the migration_table but even completely emptying that table made no difference for me.

So I added DEBUG=knex:* in variables.env as that site also suggests. When I then run the migration, I get the output below. Any idea what might be causing the error and how to solve this?

Using environment: development
  knex:client acquired connection from pool: __knexUid1 +0ms
  knex:query select * from information_schema.tables where table_name = ? and table_schema = database() undefined +0ms
  knex:bindings [ 'migrations' ] undefined +0ms
  knex:client releasing connection to pool: __knexUid1 +40ms
  knex:client acquired connection from pool: __knexUid1 +2ms
  knex:query select * from information_schema.tables where table_name = ? and table_schema = database() undefined +40ms
  knex:bindings [ 'migrations_lock' ] undefined +39ms
  knex:client releasing connection to pool: __knexUid1 +5ms
  knex:client acquired connection from pool: __knexUid1 +2ms
  knex:query select * from `migrations_lock` undefined +9ms
  knex:bindings [] undefined +10ms
  knex:client releasing connection to pool: __knexUid1 +22ms
  knex:client acquired connection from pool: __knexUid1 +22ms
  knex:query select `name` from `migrations` order by `id` asc undefined +60ms
  knex:bindings [] undefined +62ms
  knex:client releasing connection to pool: __knexUid1 +25ms
  knex:tx trx2: Starting top level transaction +0ms
  knex:client acquired connection from pool: __knexUid1 +329ms
  knex:query BEGIN; trx2 +348ms
  knex:bindings undefined trx2 +347ms
  knex:query update `migrations_lock` set `is_locked` = ? where `is_locked` = ? trx2 +18ms
  knex:bindings [ 1, 0 ] trx2 +25ms
  knex:client acquired connection from pool: __knexUid3 +47ms
  knex:query select * from information_schema.tables where table_name = ? and table_schema = database() undefined +17ms
  knex:bindings [ 'migrations' ] undefined +8ms
  knex:query select * from information_schema.tables where table_name = ? and table_schema = database() trx2 +6ms
  knex:bindings [ 'migrations' ] trx2 +6ms
  knex:client releasing connection to pool: __knexUid3 +13ms
  knex:client acquired connection from pool: __knexUid3 +18ms
  knex:query select * from information_schema.tables where table_name = ? and table_schema = database() undefined +26ms
  knex:bindings [ 'migrations_lock' ] undefined +26ms
  knex:query select * from information_schema.tables where table_name = ? and table_schema = database() trx2 +2ms
  knex:bindings [ 'migrations_lock' ] trx2 +3ms
  knex:client releasing connection to pool: __knexUid3 +6ms
  knex:client acquired connection from pool: __knexUid3 +1ms
  knex:query select * from `migrations_lock` undefined +4ms
  knex:bindings [] undefined +3ms
  knex:query select * from `migrations_lock` trx2 +2ms
  knex:bindings [] trx2 +18ms
  knex:client releasing connection to pool: __knexUid3 +22ms
  knex:client acquired connection from pool: __knexUid3 +23ms
  knex:query select `name` from `migrations` order by `id` asc undefined +66ms
  knex:bindings [] undefined +51ms
  knex:query select `name` from `migrations` order by `id` asc trx2 +7ms
  knex:bindings [] trx2 +26ms
  knex:client releasing connection to pool: __knexUid3 +55ms
  knex:tx trx4: Starting top level transaction +193ms
  knex:client acquired connection from pool: __knexUid3 +3ms
  knex:query BEGIN; trx4 +27ms
  knex:bindings undefined trx4 +8ms
  knex:query select max(`batch`) as `max_batch` from `migrations` trx2 +23ms
  knex:bindings [] trx2 +43ms
  knex:query update `migrations_lock` set `is_locked` = ? where `is_locked` = ? trx4 +25ms
  knex:bindings [ 1, 0 ] trx4 +5ms
  knex:query alter table `transactions` add `plan_price` decimal(10, 2), add `discount` decimal(10, 2) default '0' trx2 +13ms
  knex:bindings [] trx2 +20ms
Can't take lock to run migrations: Migration table is already locked
If you are sure migrations are not running you can release the lock manually by deleting all the rows = require(migrations lock table: migrations_lock
  knex:query ROLLBACK trx4 +71ms
  knex:bindings undefined trx4 +64ms
  knex:tx trx4: releasing connection +136ms
  knex:client releasing connection to pool: __knexUid3 +180ms
Unhandled rejection MigrationLocked: Migration table is already locked
    (No stack trace)
From previous event:
    at Migrator._getLock (C:\Users\Xxx\node_modules\knex\lib\migrate\Migrator.js:328:13)
    at Migrator._runBatch (C:\Users\Xxx\node_modules\knex\lib\migrate\Migrator.js:343:12)
    at knex.transaction (C:\Users\Xxx\node_modules\knex\lib\migrate\Migrator.js:92:25)
    at init.then.then (C:\Users\Xxx\node_modules\knex\lib\transaction.js:91:24)
    at runCallback (timers.js:705:18)
    at tryOnImmediate (timers.js:676:5)
    at processImmediate (timers.js:658:5)
From previous event:
    at Transaction._promise.Bluebird.using (C:\Users\Xxx\node_modules\knex\lib\transaction.js:77:12)
    at runCallback (timers.js:705:18)
From previous event:
    at new Transaction (C:\Users\Xxx\node_modules\knex\lib\transaction.js:57:30)
    at new Transaction_MySQL (C:\Users\Xxx\node_modules\knex\lib\dialects\mysql\transaction.js:7:1)
    at Client_MySQL.transaction (C:\Users\Xxx\node_modules\knex\lib\dialects\mysql\index.js:52:12)
    at Function.transaction (C:\Users\Xxx\node_modules\knex\lib\util\make-knex.js:40:31)
    at migrationListResolver.listAllAndCompleted.then.then (C:\Users\Xxx\node_modules\knex\lib\migrate\Migrator.js:91:28)
From previous event:
    at Migrator.latest (C:\Users\Xxx\node_modules\knex\lib\migrate\Migrator.js:72:8)
    at Command.commander.command.description.option.action (C:\Users\Xxx\node_modules\knex\bin\cli.js:185:18)
    at Command.listener (C:\Users\Xxx\node_modules\knex\node_modules\commander\index.js:360:8)
    at Command.emit (events.js:189:13)
    at Command.parseArgs (C:\Users\Xxx\node_modules\knex\node_modules\commander\index.js:799:12)
    at Command.parse (C:\Users\Xxx\node_modules\knex\node_modules\commander\index.js:563:21)
    at Liftoff.invoke (C:\Users\Xxx\node_modules\knex\bin\cli.js:344:13)
    at Liftoff.execute (C:\Users\Xxx\node_modules\liftoff\index.js:201:12)
    at module.exports (C:\Users\Xxx\node_modules\flagged-respawn\index.js:51:3)
    at Liftoff.<anonymous> (C:\Users\Xxx\node_modules\liftoff\index.js:191:5)
    at C:\Users\Xxx\node_modules\liftoff\index.js:149:9
    at C:\Users\Xxx\node_modules\v8flags\index.js:138:14
    at C:\Users\Xxx\node_modules\v8flags\index.js:41:14
    at C:\Users\Xxx\node_modules\v8flags\index.js:53:7
    at process._tickCallback (internal/process/next_tick.js:61:11)

  knex:query select * from `transactions` trx2 +185ms
  knex:bindings [] trx2 +227ms
  knex:client acquired connection from pool: __knexUid3 +201ms
  knex:query select `plans`.* from `plans` where `id` = ? limit ? trx4 +88ms
  knex:bindings [ 7, 1 ] trx4 +73ms
  knex:client releasing connection to pool: __knexUid3 +158ms
  knex:query update `transactions` set `plan_price` = ?, `discount` = ? where `id` = ? trx2 +137ms
  knex:bindings [ 1725, 0, 1 ] trx2 +109ms
  • Added original price and discount to transaction: 1
  knex:client acquired connection from pool: __knexUid3 +35ms
  knex:query select `plans`.* from `plans` where `id` = ? limit ? trx4 +33ms
  knex:bindings [ 2, 1 ] trx4 +33ms
  knex:client releasing connection to pool: __knexUid3 +2ms
  knex:query update `transactions` set `plan_price` = ?, `discount` = ? where `id` = ? trx2 +3ms
  knex:bindings [ 195, 0, 2 ] trx2 +4ms
  • Added original price and discount to transaction: 2
  knex:client acquired connection from pool: __knexUid3 +51ms
  knex:query select `plans`.* from `plans` where `id` = ? limit ? trx4 +50ms
  knex:bindings [ 6, 1 ] trx4 +49ms
  knex:client releasing connection to pool: __knexUid3 +3ms
  knex:query update `transactions` set `plan_price` = ?, `discount` = ? where `id` = ? trx2 +4ms
  knex:bindings [ 975, 0, 3 ] trx2 +4ms
  • Added original price and discount to transaction: 3
  knex:client acquired connection from pool: __knexUid3 +36ms
  knex:query select `plans`.* from `plans` where `id` = ? limit ? trx4 +35ms
  knex:bindings [ 5, 1 ] trx4 +35ms
  knex:client releasing connection to pool: __knexUid3 +10ms
  knex:query update `transactions` set `plan_price` = ?, `discount` = ? where `id` = ? trx2 +31ms
  knex:bindings [ 650, 0, 4 ] trx2 +31ms
  • Added original price and discount to transaction: 4
  knex:client acquired connection from pool: __knexUid3 +56ms
  knex:query select `plans`.* from `plans` where `id` = ? limit ? trx4 +34ms
  knex:bindings [ 5, 1 ] trx4 +34ms
  knex:client releasing connection to pool: __knexUid3 +2ms
  knex:query update `transactions` set `plan_price` = ?, `discount` = ? where `id` = ? trx2 +9ms
  knex:bindings [ 650, 0, 5 ] trx2 +31ms
  • Added original price and discount to transaction: 5
  knex:client acquired connection from pool: __knexUid3 +62ms
  knex:query select `plans`.* from `plans` where `id` = ? limit ? trx4 +55ms
  knex:bindings [ 6, 1 ] trx4 +34ms
  knex:client releasing connection to pool: __knexUid3 +4ms
  knex:query update `transactions` set `plan_price` = ?, `discount` = ? where `id` = ? trx2 +6ms
  knex:bindings [ 975, 0, 6 ] trx2 +29ms
  • Added original price and discount to transaction: 6
  knex:client acquired connection from pool: __knexUid3 +64ms
  knex:query select `plans`.* from `plans` where `id` = ? limit ? trx4 +63ms
  knex:bindings [ 6, 1 ] trx4 +39ms
  knex:client releasing connection to pool: __knexUid3 +2ms
  knex:query update `transactions` set `plan_price` = ?, `discount` = ? where `id` = ? trx2 +4ms
  knex:bindings [ 975, 0, 7 ] trx2 +34ms
  • Added original price and discount to transaction: 7
  knex:client acquired connection from pool: __knexUid3 +66ms
  knex:query select `plans`.* from `plans` where `id` = ? limit ? trx4 +64ms
  knex:bindings [ 5, 1 ] trx4 +34ms
  knex:client releasing connection to pool: __knexUid3 +3ms
  knex:query update `transactions` set `plan_price` = ?, `discount` = ? where `id` = ? trx2 +29ms
  knex:bindings [ 650, 0, 8 ] trx2 +30ms
  • Added original price and discount to transaction: 8
  knex:query insert into `migrations` (`batch`, `migration_time`, `name`) values (?, ?, ?) trx2 +36ms
  knex:bindings [ 4,
  knex:bindings   2020-01-09T21:13:03.923Z,
  knex:bindings   '20200105152452_add_plan_price_and_discount_to_transactions.js' ] trx2 +35ms
  knex:query update `migrations_lock` set `is_locked` = ? trx2 +24ms
  knex:bindings [ 0 ] trx2 +37ms
  knex:query COMMIT; trx2 +42ms
  knex:bindings undefined trx2 +44ms
  knex:tx trx2: releasing connection +945ms
  knex:client releasing connection to pool: __knexUid1 +146ms
Batch 4 run: 1 migrations

knex -V returns: "Knex CLI version: 0.20.2" and "Knex Local version: 0.20.3".

The full migration file looks as follows:

const Coupon = require('../../models/coupon');
const Plan = require('../../models/plan');

exports.up = knex =>
  knex.schema
  .table('transactions', table => {
    table.decimal('plan_price', 10, 2);
    table.decimal('discount', 10, 2).defaultTo(0);
  })
  .then(() => {
    return knex('transactions').then(async transactions => {
      for (let trans of transactions) {
          let original_price;
          let total_coupon_discount = 0;

          const plan = await Plan.where({id: trans.plan_id}).fetch();
          if (plan) {
            original_price = plan.get("price");
          } else {
            original_price = null;
          }

          if (trans.coupon_id) {
            const coupon = await Coupon.where({id: trans.coupon_id}).fetch();
            if (coupon) {
              const amount_ex_vat = trans.amount_ex_vat;
              const couponAmount = coupon.get("discount_amount");
              let couponPercentage = 1;
              if ( coupon.get("discount_percentage") > 0 ) {
                couponPercentage = 1.0 / ( (100.0 - coupon.get("discount_percentage")) / 100.0 );
              }
              original_price = (amount_ex_vat * couponPercentage) + couponAmount;
              total_coupon_discount = original_price - amount_ex_vat;
            }
          }

          await knex("transactions")
            .where('id', '=', trans.id)
            .update({
              plan_price: original_price,
              discount: total_coupon_discount
            }).catch(function(error){
              console.log(error)
            }).then(function(){
              console.log('  • Added original price and discount to transaction: ' + trans.id);
            })
      }
    });
  });

exports.down = function (knex) {
  return knex.schema.table('transactions', (table) => {
    table.dropColumn('plan_price');
    table.dropColumn('discount');
  });
};

Answer 1

Knex no longer takes the second Promise parameter, as it shifted to using native promises awhile back. Promise is therefore undefined in your migration, so there's definitely no .resolve property.

It's decidedly odd that someone thought returning Promise.resolve().then was a good idea anyway. What you want is to execute the schema modification, then the data modification. That will look something like this:

return knex.schema.table("transactions", t => {
  t.decimal('plan_price', 10, 2);
  // etc
})
  .then(() =>
    knex("transactions")
      .then(
        // Update values here
      );
  )
  .catch(console.error)

Further, what you are discovering is that for_each isn't always a terribly good fit for async work. However, we can still make this migration work either by writing a more complex query (joining the other tables to get the values we're after) or by modifying the current one to work better with promises:

exports.up = knex =>
  knex.schema
    .table("transactions", t => {
      t.decimal("plan_price", 10, 2);
      t.decimal("discount", 10, 2).defaultTo(0);
    })
    .then(() => {
      return knex("transactions").then(async transactions => {
        for (let trans of transactions) {
          let original_price;
          let total_coupon_discount = 0;

          const plan = await Plan.where({ id: trans.plan_id }).fetch();
          if (plan) {
            original_price = plan.get("price");
          }

          if (trans.coupon_id) {
            const coupon = await Coupon.where({ id: trans.coupon_id }).fetch();
            if (coupon) {
              total_coupon_discount = coupon.get("discount_amount");
              original_price = trans.amount_ex_vat + couponAmount;
            }
          }

          await knex("transactions")
            .where("id", "=", trans.id)
            .update({
              plan_price: original_price,
              discount: total_coupon_discount
            });
        }
      });
    });

I obviously can't speak to the correctness of your other code (looks to me like Bookshelf?) because I don't have your schema, but this is the general idea. Basically, when you want to use async/await you go with for... of, keeping everything nice and sequential.

I think it's worth noting that this approach could be rather slow on a large table, with three separate blocking queries per row of transactions.