使用knex我想向现有表中添加2个其他列。为了预先存在记录，我想基于计算添加一个值。以下是我的迁移文件。它在第二行上失败：Cannot read property 'resolve' of undefined。
exports.up = function (knex,Promise) {
return Promise.resolve()
.then(function(){
... cut to shorten question...
})
};
更新：我删除了Promise，现在下面有迁移代码。这仍然会产生两个错误，可能与forEach循环有关，并且它不会等待循环的一部分完成才继续进行下一部分（但是我不知道该怎么做）循环）：
未处理的拒绝Migration Migrationed：迁移表已被锁定
交易查询已经完成，请使用DEBUG = knex：tx来获取更多信息
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',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 ：@ Rich Churcher的答案中建议的迁移语法使迁移工作得以实现。但是有关MigrationLock的错误消息仍然存在。 Here讨论了类似的问题。建议从migration_table删除锁，但是即使完全清空该表也对我没有影响。
因此我在DEBUG=knex:*中添加了variables.env，因为该站点也建议这样做。然后，当我运行迁移时，我得到以下输出。您知道什么可能导致错误以及如何解决吗？
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,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,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,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,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,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,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` = ?,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,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返回：“ Knex CLI版本：0.20.2”和“ Knex本地版本：0.20.3”。
完整的迁移文件如下所示：
const Coupon = require('../../models/coupon');
const Plan = require('../../models/plan');
exports.up = knex =>
knex.schema
.table('transactions',table => {
table.decimal('plan_price',2);
table.decimal('discount',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');
});
};