user3836476 user3836476 - 7 months ago 73
SQL Question

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

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.

Answer

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) {
    console.error('error connecting: ' + err.stack);
    return;
}});

module.exports = connection;

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

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

app.get('/save',function(req,res){
    var post  = {from:'me',to:'you',msg:'hi'};
    var query = 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 :)

Comments