I want to add a column to a table, then perform some work to populate the column as part of the migration. Populating the column requires some manipulation in code.
Consider two tables:
[ user_id, first_name, last_name ]
, and[ order_id, user_id, other_field_1, other_field_2 ]
.And the function
var getNickName = function(user_row) {
//do a bunch of javascripty stuff here
//based on user_row.first_name and user_row.last_name.
//i.e., stuff not possible in SQL
return 'nickname';
}
I want a knex migration that adds a user_nick_name
field to the Orders table. Then updates the new column with the output from getNickName()
.
I need this to be in a transaction.
I know I need to add the column to orders, then select all the orders, iterate over the orders performing:
pass the user row into getNickName
,
then use that to call update on the users table setting the value.
I just can't seem to get the knex syntax for all this, when a transaction is involved.
export function up(knex, Promise) {
return knex.select()
.from('Users')
.then((users) => {
const nickNames = users.map((user) => {
return { userId: user.user_id, nickName: getNickName(row) };
});
return knex.transaction((trx) => {
return knex.schema.table('Orders', (table) => table.string('user_nick_name').transacting(trx))
.then(() => {
return Promise.all(
nickNames.map((row) => {
return knex('Orders')
.update({ user_nick_name: row.nickName })
.where('user_id', row.userId)
.transacting(trx);
});
);
})
.then(trx.commit)
.catch(trx.rollback);
});
});
}
export function down(knex) {
return knex.schema.table('Orders', (table) => table.dropColumn('user_nick_name'));
}