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(
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)
.then(function(rows) {
// "[DATABASE] Inserting records N-M of O into TABLE"
var msg = [
"[" + chalk.yellow(local.client.connectionSettings.database) + "]",
"Inserting records", + 1) + "-" + + rows.length),
"[: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, function(row) {
return local(table)
.then(function() {

5. Finish

Finally, kick off the whole promise chain:

function() {
function(err) {