Using mysql node.js driver to get an entire database as JSON

Question

I'm working on creating a JavaScript file to get a JSON dump of an entire MySQL database, running on server side. I found and am using the MySQL driver for node.js (https://www.npmjs.com/package/mysql) for queries, it's been straight forward enough to start. My issue is that I need to call multiple queries and get the results from all of them to put into a single JSON file and I can't quite get that to work. I'm entirely new to JavaScript (basically never touched it before now) so it's probably a relatively simple solution that I'm just missing.

Currently I do a query of 'SHOW TABLES' to get a list of all the tables (this can change so I can't just assume a constant list). I then just want to basically loop through the list and call 'SELECT * from table_name' for each table, combining the results as I go to get one big JSON. Unfortunately I haven't figured out how to get the code to finish all the queries before trying to combine them, thus retuning 'undefined' for all the results. Here is what I currently have:

var mysql = require('mysql');
var fs = require('fs');

var connection = mysql.createConnection({
    host: 'localhost',
    user: 'root',
    password: 'pass',
    database: 'test_data'
});

connection.connect();

connection.query('SHOW TABLES;', function(err, results, fields)
{
    if(err) throw err;

    var name = fields[0].name;
    var database_json = get_table(results[0][name]);
    for (i = 1; i < results.length; i++)
    {
        var table_name = results[i][name];
        var table_json = get_table(table_name);
        database_json = database_table_json.concat(table_json);
    }
    fs.writeFile('test_data.json', JSON.stringify(database_json), function (err)
    {
        if (err) throw err;
    });
    connection.end();
});

function get_table(table_name)
{
    connection.query('select * from ' + table_name + ';', function(err, results, fields) {
        if(err) throw err;

        return results;
    });
}

This gets the table list and goes through all of it with no issue, and the information returned by the second query is correct if I just do a console.log(results) inside the query, but the for loop just keeps going before any query is completed and thus 'table_json' just ends up being 'undefined'. I really think this must be an easy solution (probably something with callbacks which I don't quite understand fully yet) but I keep stumbling.

Thanks for the help.

score 1 · Accepted Answer · edited May 23 '17 at 12:14

I'm guessing that this is for some sort of maintenance type function and not a piece that you need for your application. You're probably safe to do this asynchronously. This module is available here: https://github.com/caolan/async

You can also use Q promises, available here: https://github.com/kriskowal/q

This answer: describes both approaches pretty well: Simplest way to wait some asynchronous tasks complete, in Javascript?

Using mysql node.js driver to get an entire database as JSON

1 Answers1