MySQL bulk Insert

BulkInsert.js

/**
This little helper creates bulk SQL insert queries like this:

INSERT INTO mytable (id, col1, col2) VALUES
(1, "col1value", "col2value"),
(2, "col1value", "col2value"),
(3, "col1value", "col2value"),
:
:
(4999, "col1value", "col2value"),
(5000, "col1value", "col2value");

You just specify the limit and add keep adding rows.


 * @param handle {object} reference to either a node mysql object or to a writable stream
 * @param queryTemplate {string} a template, defining how your query should be populated, pass column names and insert options here
 * @param limit {integer} a limit after how many queued items a query should be fired
 * @param callback {function} gets passed to the sql query
 */
var BulkInsert = function(handle, queryTemplate, limit, callback) {

    var queue = [];

    /**
     * Add data to the queue
     *
     * @method BulkInsert.add
     * @param record {array} a list of items to insert as row
     */
    this.add = function (record) {
        queue.push(record.join(','));
        if (queue.length > limit) {
            this.flush()
        }
    }
    /**
     * Either execute the query or write to the file stream
     *
     * @method BulkInsert.flush
     */
    this.flush = function () {
        var sql = queryTemplate.replace('{values}', '\n('+ queue.join('),\n(') +')');
        if (handle.path && handle.writable) {
            handle.write(sql);
        } else {
            handle.query(sql, callback);
        }
        queue = [];
    }
};


// preparation for executing MySQL queries
var handle = require('mysql').createClient({ user: '...', password: '...' });

// alternative: preparation for a file stream - aka MySQL dump
var handle = fs.createWriteStream('dump.sql');

// initialization
var myInserter = new BulkInsert(handle, 'INSERT INTO mytable (id, col1, col2) VALUES {values}', 5000, function () { ... });

// automatically creates ~200 bulk queries
for (var i = 1, il = 1000000; i < il; i++) {
    myInserter.add([ i, 'col1value', 'col2value' ]);
}

// writes / executes the remaining items
myInserter.flush();

This little helper creates bulk SQL insert queries like this:

INSERT INTO mytable (id, col1, col2) VALUES
(1, "col1value", "col2value"),
(2, "col1value", "col2value"),
(3, "col1value", "col2value"),
:
:
(4999, "col1value", "col2value"),
(5000, "col1value", "col2value");

You just specify the limit and add keep adding rows.