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

Question

I need to provide the mysql connection for modules. I have a code like this.

var express = require('express'),
app = express(),
server = require('http').createServer(app);

var mysql      = require('mysql');
var connection = mysql.createConnection({
    host     : '127.0.0.1',
    user     : 'root',
    password : '',
    database    : 'chat'
});

connection.connect(function(err) {
    if (err) {
        console.error('error connecting: ' + err.stack);
        return;
    }
});

app.get('/save', function(req,res){
    var post  = {from:'me', to:'you', msg:'hi'};
    var query = connection.query('INSERT INTO messages SET ?', post, function(err, result) {
        if (err) throw err;
    });
});

server.listen(3000);

But how we provide one time mysql connection for all the modules.

Lanti

Answer 1

You could create a db wrapper then require it. node's require returns the same instance of a module every time, so you can perform your connection and return a handler. From the Node.js docs:

every call to require('foo') will get exactly the same object returned, if it would resolve to the same file.

You could create db.js:

var mysql = require('mysql');
var connection = mysql.createConnection({
    host     : '127.0.0.1',
    user     : 'root',
    password : '',
    database : 'chat'
});

connection.connect(function(err) {
    if (err) throw err;
});

module.exports = connection;

Then in your app.js, you would simply require it.

var express = require('express');
var app = express();
var db = require('./db');

app.get('/save',function(req,res){
    var post  = {from:'me', to:'you', msg:'hi'};
    db.query('INSERT INTO messages SET ?', post, function(err, result) {
      if (err) throw err;
    });
});

server.listen(3000);

This approach allows you to abstract any connection details, wrap anything else you want to expose and require db throughout your application while maintaining one connection to your db thanks to how node require works :)

Plasticity

In this way how we create io=require('socket.io'); for any route access. – Lanti

@Plasticity are you sure that connection.connect() will not create a connection each time the file is required? – Putrefaction

I realize I'm a bit late to the party, but what is the conventional place to put a db.js file in an express app? – Dittmer

Tell me please, and the absence of closing the connection will not consume a lot of memory? – Eldrid

On Windows, there is a corner case to be aware of with this convention: (1) node.js caches exports based on the string passed to require(), and this string cache is case sensitive; (2) since Windows paths are case insensitive, this would actually create 2 instances of your db connection. As long as your require statements are cased the same across all files, this will not be an issue. – Spinifex

can you tell me where to put this db.js file, i am put it in root folder it give me error Error: Cannot find module './db' – Torhert

Error: connection.query is not a function – Bainmarie

I have a question about using Transactions in this situation. Will it cause some problems if to reuse a connection across multiple requests? – Stannite

Before going to insert the record, how can I make sure that the connection has been made successfully? – Leuctra

Connection pooling is more optimal than using a single connection. The same pattern can be used for a connection pool. Refer this for implementation. – Raybourne

I have multiple files that needed to be queried, validators to be specific, If i require that db.js to those file, will it only be a single connection? – Churchman

Answer 2

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.

Speiss

@harshvardhan I have updated it in the information with the config info. – Speiss

I am getting the following error in console: "db":{ ^ SyntaxError: Unexpected token : – Inanna

Ok, So i have set the config.json object in mysql_query.js file itself and then there is no syntax problem. But again, I am getting the following error in console: [MYSQL] Error connecting to mysql:Error: ER_ACCESS_DENIED_ERROR: Access denied for user ''@'localhost' (using password: NO) I have password in my db, and when I try to log on my MySQL db through the same credentials on command line, I am through it. – Inanna

@Inanna I'm afraid that now its just a matter of configuration. Try having a look at the documentation of the package. github.com/mysqljs/mysql – Speiss

Answer 3

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

Blossom

Answer 4

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

Timetable

Answer 5

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://mcmap.net/q/204372/-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;         
    });
});

}

Exordium

Answer 6

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;

Hoenack

Answer 7

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

Hyperkeratosis

Answer 8

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.

Homogeneous