Copying Databases in Node

Yes, there are tons of great, StackOverflow-accepted ways to copy giant databases betweens servers. But, sometimes you have a special need for a project that requires selectively copying subsets of data instead of an entire 15GB database.

So, when you have to programmatically copy rows between databases in Node…

Problems with LIMIT

When I first attempted this, I ran into several problems:

  • I didn’t use a LIMIT, and ran out of memory.
  • I specified a LIMIT, but the bulk INSERT failed for some reason.
  • I specified a lower LIMIT, but the INSERT performed differently on each table.
  • I specified an arbitrarily low LIMIT, but on simple tables, it took longer.

I realized there’s no perfect way of doing this, and I surely didn’t want to insert a single row at a time.

Group INSERTs by Size, not Rows

First, let’s use the following tools:

The premise is to copy N rows that roughly equate to M amount of memory.

1. Setup dependencies

var knex = require('knex');
var Promise = require('bluebird');
var ProgressBar = require('progress');

var local = knex.initialize(...);
var remote = knex.initialize(...);

2. Get table stats

var getStats = function(table) {
  // Query total number of rows (`information_schema` is just an estimate)
  return remote(table).count('_').then(function(response) {
    stats.totalRows = response.shift()['count(_)'];
  })
  // Request DB estimates for table size (approximate)
  .then(function() {
    return remote('information*schema.TABLES').select('data_length', 'index_length').where({
      table_name: table,
      table_schema: this.remote.client.connectionSettings.database,
    });
  })
  // Calculate stats for # of rows & iterations to stay within memory limits
  .then(function(result) {
    // Base variables for insertion chunk calculations
    stats.memoryLimit = 25 * 1024 * 1024; // 25Mb
    stats.dataLength = result[0]['data_length'];
    stats.indexLength = result[0]['index_length'];
    stats.totalLength = stats.dataLength + stats.indexLength;
    stats.rowLimit = Math.min(stats.totalRows, Math.ceil(stats.totalRows * stats.memoryLimit / stats.totalLength));

    return stats;
  }})
};

3. Copy table in groups

Now that we have a lot of stats based around a 25Mb memory limit, now we can copy the table:

var copyTable = function (table) {
  return getTableStats(table).then(function (stats) {
    // Empty array to make it easy to iterate for insertion group
    var iterations = new Array(Math.ceil(stats.totalRows / stats.rowLimit))

    // Promise.reduce ensures iterations are done sequentially, whereas
    // Promise.all/map runs in parallel
    return Promise.reduce(
      iterations,
      function (previous, iteration, i) {
        var rowOffset = stats.rowLimit * i

        return copyRows(table, rowOffset, stats.rowLimit, stats.totalRows)
      },
      []
    )
  })
}

4. Copy individual rows

All that our copyTable function is missing is copyRows:

var copyRows = function (table, offset, limit, total) {
  return remote(table)
    .select('*')
    .offset(offset)
    .limit(limit)
    .then(function (rows) {
      // "[DATABASE] Inserting records N-M of O into TABLE"
      var msg = [
        '[' + chalk.yellow(local.client.connectionSettings.database) + ']',
        'Inserting records',
        chalk.green(offset + 1) + '-' + chalk.green(offset + rows.length),
        'of',
        chalk.green(total),
        'into',
        chalk.yellow(table),
        '[:bar] :percent :etas',
      ].join(' ')

      var bar = new Progress(msg, {
        total: rows.length,
        width: 20,
      })

      // Insert each record individually because it's actually faster (!?)
      // and gives us a pretty progress bar.
      return Promise.map(rows, function (row) {
        return local(table)
          .insert(row)
          .then(function () {
            bar.tick()
          })
      })
    })
}

5. Finish

Finally, kick off the whole promise chain:

copyTable('something_huge').then(
  function () {
    console.log('Success!')
    process.exit(0)
  },
  function (err) {
    console.error(err)
    process.exit(1)
  }
)

demo