How to provide a mysql database connection in single file in nodejs

I took a similar approach as Sean3z but instead I have the connection closed everytime i make a query.

His way works if it's only executed on the entry point of your app, but let's say you have controllers that you want to do a var db = require('./db'). You can't because otherwise everytime you access that controller you will be creating a new connection.

To avoid that, i think it's safer, in my opinion, to open and close the connection everytime.

here is a snippet of my code.

mysq_query.js

// Dependencies
var mysql   = require('mysql'),
    config  = require("../config");

/*
 * @sqlConnection
 * Creates the connection, makes the query and close it to avoid concurrency conflicts.
 */
var sqlConnection = function sqlConnection(sql, values, next) {

    // It means that the values hasnt been passed
    if (arguments.length === 2) {
        next = values;
        values = null;
    }

    var connection = mysql.createConnection(config.db);
    connection.connect(function(err) {
        if (err !== null) {
            console.log("[MYSQL] Error connecting to mysql:" + err+'\n');
        }
    });

    connection.query(sql, values, function(err) {

        connection.end(); // close the connection

        if (err) {
            throw err;
        }

        // Execute the callback
        next.apply(this, arguments);
    });
}

module.exports = sqlConnection;

Than you can use it anywhere just doing like

var mysql_query = require('path/to/your/mysql_query');
mysql_query('SELECT * from your_table where ?', {id: '1'}, function(err, rows)   {
    console.log(rows);
});

UPDATED: config.json looks like

{
        "db": {
        "user"     : "USERNAME",
        "password" : "PASSWORD",
        "database" : "DATABASE_NAME",
        "socketPath": "/tmp/mysql.sock"
    }
}

Hope this helps.

I think that you should use a connection pool instead of share a single connection. A connection pool would provide a much better performance, as you can check here.

As stated in the library documentation, it occurs because the MySQL protocol is sequential (this means that you need multiple connections to execute queries in parallel).

Connection Pool Docs

From the node.js documentation, "To have a module execute code multiple times, export a function, and call that function", you could use node.js module.export and have a single file to manage the db connections.You can find more at Node.js documentation. Let's say db.js file be like:

    const mysql = require('mysql');

    var connection;

    module.exports = {

    dbConnection: function () {

        connection = mysql.createConnection({
            host: "127.0.0.1",
            user: "Your_user",
            password: "Your_password",
            database: 'Your_bd'
        });
        connection.connect();
        return connection;
    }

    };

Then, the file where you are going to use the connection could be like useDb.js:

const dbConnection = require('./db');

var connection;

function callDb() {

    try {

        connection = dbConnectionManager.dbConnection();

        connection.query('SELECT 1 + 1 AS solution', function (error, results, fields) {
            if (!error) {

                let response = "The solution is: " + results[0].solution;
                console.log(response);

            } else {
                console.log(error);
            }
        });
        connection.end();


    } catch (err) {
        console.log(err);
    }
}

var mysql = require('mysql');

var pool  = mysql.createPool({
    host     : 'yourip',
    port    : 'yourport',
    user     : 'dbusername',
    password : 'dbpwd',
    database : 'database schema name',
    dateStrings: true,
    multipleStatements: true
});


// TODO - if any pool issues need to try this link for connection management
// https://stackoverflow.com/questions/18496540/node-js-mysql-connection-pooling

module.exports = function(qry, qrytype, msg, callback) {

if(qrytype != 'S') {
    console.log(qry);
}

pool.getConnection(function(err, connection) {
    if(err) {
        if(connection)
            connection.release();
        throw err;
    } 

    // Use the connection
    connection.query(qry, function (err, results, fields) {
        connection.release();

        if(err) {
            callback('E#connection.query-Error occurred.#'+ err.sqlMessage);    
            return;         
        }

        if(qrytype==='S') {
            //for Select statement
            // setTimeout(function() {
                callback(results);      
            // }, 500);
        } else if(qrytype==='N'){
            let resarr = results[results.length-1];
            let newid= '';
            if(resarr.length)               
                newid = resarr[0]['@eid'];
            callback(msg + newid);
        } else if(qrytype==='U'){
            //let ret = 'I#' + entity + ' updated#Updated rows count: ' + results[1].changedRows;
            callback(msg);                      
        } else if(qrytype==='D'){
            //let resarr = results[1].affectedRows;
            callback(msg);
        }
    });

    connection.on('error', function (err) {
        connection.release();
        callback('E#connection.on-Error occurred.#'+ err.sqlMessage);   
        return;         
    });
});

}

try this

var express = require('express');

var mysql     =    require('mysql');

var path = require('path');
var favicon = require('serve-favicon');
var logger = require('morgan');
var cookieParser = require('cookie-parser');
var bodyParser = require('body-parser');

var routes = require('./routes/index');
var users = require('./routes/users');

var app = express();

// view engine setup
app.set('views', path.join(__dirname, 'views'));
app.set('view engine', 'jade');

// uncomment after placing your favicon in /public
//app.use(favicon(path.join(__dirname, 'public', 'favicon.ico')));
app.use(logger('dev'));
app.use(bodyParser.json());
app.use(bodyParser.urlencoded({ extended: false }));
app.use(cookieParser());
app.use(express.static(path.join(__dirname, 'public')));

app.use('/', routes);
app.use('/users', users);

// catch 404 and forward to error handler
app.use(function(req, res, next) {
  var err = new Error('Not Found');
  err.status = 404;
  next(err);
});

// error handlers

// development error handler
// will print stacktrace
console.log(app);
if (app.get('env') === 'development') {
  app.use(function(err, req, res, next) {
    res.status(err.status || 500);
    res.render('error', {
      message: err.message,
      error: err
    });
  });
}

// production error handler
// no stacktraces leaked to user
app.use(function(err, req, res, next) {
  res.status(err.status || 500);
  res.render('error', {
    message: err.message,
    error: {}
  });
});

var con = mysql.createConnection({
  host: "localhost",
  user: "root",
  password: "admin123",
  database: "sitepoint"
});

con.connect(function(err){
  if(err){
    console.log('Error connecting to Db');
    return;
  }
  console.log('Connection established');
});



module.exports = app;

you can create a global variable and then access that variable in other files.

here is my code, I have created a separate file for MySQL database connection called db.js

const mysql = require('mysql');

var conn = mysql.createConnection({
    host: "localhost",
    user: "root",
    password: "xxxxx",
    database: "test"
});

conn.connect((err) => {
    if (err) throw err;
    console.log('Connected to the MySql DB');
});

module.exports = conn;

Then in the app.js file

const express = require('express');
const router = express.Router();

// MySql Db connection and set in globally
global.db = require('../config/db');

Now you can use it in any other file

const express = require('express');
const router = express.Router();

router.post('/signin', (req, res) => {
    try {
        var param = req.body;
        var sql = `select * from user`;

        // db is global variable
        db.query(sql, (err, data) => {
            if (err) throw new SyntaxError(err);
            res.status(200).json({ 'auth': true, 'data': data });
        });
    } catch (err) {
        res.status(400).json({ 'auth': false, 'data': err.message });
    }
});

When you set up a MySQL connection pool in Node.js using the mysql2 library and export it as in the database.js file you've created, the connection pool itself is a singleton relative to your Node.js application's lifecycle.