23

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

Improve this question

4 Answers 4

Reset to default
36

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]
});
Improve this answer
Sign up to request clarification or add additional context in comments.

7 Comments

Is there anyway to stream the queries into the mysql connection? This example assumes that the multipleStatements is a manageable buffer.
Keep in mind though that when you use the connection.execute() function, multiple statements won't work. So if you wan't to fire off multiple queries on the same connection using execute() you will have to use the "connect() -> execute() -> end()" pattern
Does not work when DELIMITER is used before creating trigger.
@user1748217 you can import the text from a .sql file using Node's fs module. You can then pass this text as the first argument in the .query call. This would only work be default if the file had only one query. This solution ensures you can pass a file that has multiple.
I would also note that the mysql Node library seems to have changed it's syntax a bit, according to its docs. Now you need to pass it a flag property with it's value including the string "MULTI_STATEMENTS". Example: const conn = mysql.createConnection({flags:"-MULTI_STATEMENTS"})
|
8

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();
});
Improve this answer

Comments

5

Looks like there is a module for this purpose: execsql

Improve this answer

1 Comment

I got Error: Cannot enqueue Query after invoking quit. when running the sample code of execsql.
0

This will do the trick:

var express = require("express");
var bodyParser = require("body-parser");
var mysql = require('mysql');
var fs = require('fs');

var app = express();

app.use(bodyParser.urlencoded({ extended: false }));
app.use(bodyParser.json());
app.use(express.static(__dirname));

var defaultConnection = mysql.createConnection({
  host     : 'localhost',
  user     : 'root',
  password : '',
  database: 'utpDatabase'
});

function dbCall_file (endpoint, operation, settings, filename){
    app.post(endpoint, function(request, response){
        var data = request.body;
        var path = 'path/to/queries/' + filename
        var connection = (settings == 'default') ? defaultConnection : settings;
        var callback = function(arg){
            var query = arg.replace(/{{[ ]{0,2}([a-zA-Z0-9\.\_\-]*)[ ]{0,2}}}/g, function(str, mch){ return data[mch]});
            connection.query(query, function(err, rows){
                if (!err){
                    var toClient = (operation == 'select') ? rows : {success: true};
                    response.send(toClient);
                } else {
                    console.log(err);
                    response.send({error: err, query: query});
                }
            });
        };

        fs.readFile(path, 'utf8', function(err, data){
            if (!err){
                callback(data);
            } else {
                callback(err);
            }
        });

    });
};

Then in your .sql file wrap your node variables in double curlies- for example, if you want to query first names for node variable data.firstName from your post call:

SELECT * FROM users WHERE name={{ firstName }}
Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.