Use NodeJS to run an SQL file in MySQL

Question

I am using the mysql plugin for nodejs and it is fantastic at doing everything I need so far.

However I have come across a stumbling block. I have created a MySQL provider that exports a mysql pool:

var mysql = require('mysql'); var mysqlPool  = mysql.createPool({   host     : '127.0.0.1',   user     : 'root',   password : '' });  mysqlPool.getConnection(function(err, connection) {   connection.query("INSERT INTO .... 

I can select, create, insert, etc all fine, but I've come across a task where I would like to run a small SQL string with about 10 different commands together. I've thought about doing one of the following:

1. Execute a SQL file against a database using mysql
2. Run a query and enable multipleStatements

I have written some code to execute mysql as a child process, but I would really love to avoid doing this:

var cp = require("child_process"); var cmdLine = "mysql --user=autobuild --password=something newdb < load.sql"; cp.exec(cmdLine, function(error,stdout,stderr) {     console.log(error,stdout,stderr); });   

The problem with option two is I would rather not enable multipleStatements for every query, just this one particular one. I have thought about creating a new connection, but just thinking of other ways this could be done.

TL;DR? Using NodeJS and MySQL how can I execute the following into a database:

CREATE TABLE pet (name VARCHAR(20), owner VARCHAR(20) );  CREATE TABLE sofa (name VARCHAR(20), owner VARCHAR(20) );  CREATE TABLE table (name VARCHAR(20), owner VARCHAR(20) ); 

Thanks so much for anyone who shares their ideas

Answer 1

You can use the connection option called multipleStatements:

// Works with the pool too. var connection = mysql.createConnection({multipleStatements: true}); 

Then, you can pass the queries like these:

connection.query('CREATE 1; CREATE 2; SELECT 3;', function(err, results) {   if (err) throw err;    // `results` is an array with one element for every statement in the query:   console.log(results[0]); // [create1]   console.log(results[1]); // [create2]   console.log(results[2]); // [select3] }); 

Answer 2

Here is a big .sql file friendly way to progmatically execute multiple queries against MySQL without using the multipleStatements property and a massive buffer. Please note this is not the most efficient way to upload to mysql.

var mysql = require('mysql'); var fs = require('fs'); var readline = require('readline'); var myCon = mysql.createConnection({    host: 'localhost',    port: '3306',    database: '',    user: '',    password: '' }); var rl = readline.createInterface({   input: fs.createReadStream('./myFile.sql'),   terminal: false  }); rl.on('line', function(chunk){     myCon.query(chunk.toString('ascii'), function(err, sets, fields){      if(err) console.log(err);     }); }); rl.on('close', function(){   console.log("finished");   myCon.end(); });