Mark Mark - 3 months ago 54
MySQL Question

Use NodeJS to run an SQL file in MySQL

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

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]
});
Comments