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 · 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

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

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

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

User · 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

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

[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