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

User · Accepted 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) 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 :)

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

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

Examples related to mysql

Examples related to node.js

Examples related to express

Examples related to node-modules