Knex.js Migrations copy existing data to other table

Question

Is it possible to, with a Knex.js migration, copy data from one table to another ?

The use case is as follows: I have a table A, which I want to split into two new tables B and C. Ideally, I would loop over the rows in A to create the appropriate rows in B and C and fill them with the right information.

Can this be done inside a migration file? Aport from this question, I feel this way of doing migrations in Node.JS is quite complex (e.g. compared to ActiveRecord). Is there any better, more managed way to do such migrations? Or is this the industry standard ?

Answer 1

There's nothing special about the query builder object passed in to your up and down functions inside the migration file. You can use it like you would use any other instance of a query builder in your app, that is run any queries you want as part of the migration.

Here's an extremely simple example. Given you have a table called accounts with 4 fields, 1 of which you want to split off into a table by itself:

// Starting promise chain with Promise.resolve() for readability only
exports.up = function(knex, Promise) {
  return Promise.resolve()
    .then(() => knex.schema.createTable('table_b', t => {
      t.string('col_a')
      t.string('col_b')
    }))
    .then(() => knex.schema.createTable('table_c', t => {
      t.string('col_c')
      t.string('col_d')
    }))
    .then(() => knex('table_a').select('col_a', 'col_b'))
    .then((rows) => knex('table_b').insert(rows))
    .then(() => knex('table_a').select('col_c', 'col_d'))
    .then((rows) => knex('table_c').insert(rows))
    .then(() => knex.schema.dropTableIfExists('table_a'))
};

exports.down = function(knex, Promise) {
  return Promise.resolve()
    .then(() => knex.schema.createTable('table_a', t => {
      t.string('col_a')
      t.string('col_b')
      t.string('col_c')
      t.string('col_d')
    }))
    .then(() => knex('table_b').select('col_a', 'col_b'))
    .then((rows) => knex('table_a').insert(rows))
    .then(() => knex('table_c').select('col_c', 'col_d'))
    .then((rows) => knex('table_a').insert(rows))
    .then(() => knex.schema.dropTableIfExists('table_b'))
    .then(() => knex.schema.dropTableIfExists('table_c'))
};

In this case, you could also just keep table_a and instead of creating third table, just drop two columns and rename the table. Be mindful, however, that splitting your table like this will get messy if it has relationships to other tables in the DB already.