Reputation: 3160
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');
});
};
Upvotes: 2
Views: 4585
Reputation: 7664
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
.
Upvotes: 3