Run update inside knex migration

Question

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:

• Users [ user_id, first_name, last_name ], and
• Orders [ 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.

Answer 1

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'));
}