title: Express route, database access and NodeJS: What's the best way?
body: <p>I'm currently developping an application using NodeJs, Express, WebSocket and node-mysql. Currently, here is the global idea:</p>
  
  <p>The server has a login route and looks like this:</p>
  
  <pre><code>var configuration = require("./configuration");
  var db = require("./database");
  var Response = require("./models/response");
  
  var express = require('express');
  var app = express();
  var server = require("http").Server(app);
  var io = require("socket.io")(server);
  var bodyParser = require('body-parser');
  
  var jwt = require("jsonwebtoken");
  var socketJwt = require('socketio-jwt');
  
  app.use(function(req, res, next)
  {
      res.header("Access-Control-Allow-Origin", "http://localhost:8080");
      res.header('Access-Control-Allow-Credentials', true);
  
      next();
  });
  
  app.use(bodyParser.urlencoded({ extended: true }))
  
  app.post('/login', function(req, res)
  {
      db.player
          .login(req.body.username, req.body.password)
          .then(function(result)
          {
              var profile = {
                  id: result.d[0].id_player,
                  name: req.body.username,
                  email: 's.sougnez@areaprog.com'
              };
  
              jwt.sign(profile, configuration.jwtSecret, { expireInMinutes: 60 * 5 }, function(token)
              {
                  res.json(new Response({ token: token }));
              });
          },
          function(err)
          {
              res.status(err.getStatus()).json(err);
          });
  });
  
  io
      .on('connection', socketJwt.authorize({
          secret: configuration.jwtSecret,
          timeout: 2000
      }))
      .on('authenticated', function(socket)
      {
      });
  
  server.listen(8081);
  </code></pre>
  
  <p>The "db" module is used to connect and query a mysql database. Here is the main idea of this file:</p>
  
  <pre><code>var mysql = require("mysql");
  var configuration = require("../configuration");
  
  var Promise = require("promise");
  var Response = require("../models/response");
  
  var con = null;
  
  function connect(handleConnection, connection)
  {
      return new Promise(function(resolve, reject)
      {
          if (handleConnection !== false)
          {
              con = mysql.createConnection(configuration.db);
  
              con.connect(function(err)
              {
                  if (err)
                  {
                      reject(err);
  
                      return;
                  }
  
                  con.beginTransaction(function(err)
                  {
                      if (err)
                      {
                          reject(err);
  
                          return;
                      }
  
                      resolve();
                  });
              })
          }
          else
          {
              con = connection;
  
              resolve();
          }
      });
  }
  
  function commit(handleConnection)
  {
      ...
  }
  
  function executeNonQuery()
  {
      ...
  }
  
  function executeQuery(query, args)
  {
      ...
  }
  
  function hasRow()
  {
      ...
  }
  
  function lastId()
  {
      ...
  }
  
  function rollback(handleConnection)
  {
      ...
  }
  
  module.exports.commit = commit;
  module.exports.connect = connect;
  module.exports.executeNonQuery = executeNonQuery;
  module.exports.executeQuery = executeQuery;
  module.exports.hasRow = hasRow;
  module.exports.lastId = lastId;
  module.exports.rollback = rollback;
  
  module.exports.player = require("./player").init(this);
  </code></pre>
  
  <p>And finally, we have the "player" module:</p>
  
  <pre><code>var Promise = require('promise');
  
  var player = (function()
  {
      var db = null;
  
      var init = function(database)
      {
          db = database;
  
          return this;
      };
  
      var login = function(name, pass, handleConnection, connection)
      {
          return new Promise(function(resolve, reject)
          {
              var data = null;
  
              db
                  .connect(handleConnection, connection)
                  .then(function()
                  {
                      var query = 'SELECT `id_player`, `language`, `password`, `salt`, `failed_attempt`, `last_ip`, UNIX_TIMESTAMP(`blocked_until`) AS `blocked_until` \
                           FROM `players` \
                           WHERE `name` = ?';
  
                      var params = [ name ];
  
                      return db.executeQuery(query, params);
                  })
                  .then(function(result)
                  {
                      if (result.d.length === 0)
                      {
                          result.setError('Invalid username');
  
                          throw result;
                      }
  
                      data = result;
  
                      return db.commit();
                  })
                  .then(function()
                  {
                      resolve(data);
                  })
                  .catch(function(err)
                  {
                      db
                          .rollback(handleConnection)
                          .then(function()
                          {
                              reject(err);
                          });
                  });
          });
      };
  
      return {
          init: init,
          login: login
      }
  }());
  
  module.exports = player;
  </code></pre>
  
  <p>Okay, so actually, everything works as expected. I open the page, click on "login", then it calls the route "/login" of the code above, grant the access and create a websocket connection. This whole code is the beginning and is subject to change, but yesterday, while reading a post on NodeJs, I realized that the db module was a singleton, so I'm wondering...</p>
  
  <p>Is it a valid architecture ? What if two users connect at the same time, don't I have a risk that the "connect" function of "db" called by the second user overrides the variable "con" initialized by the call made by the first user ? I tried to put a long for loop yesterday in the login process and I noticed that the second user call was waiting for the first one to finish => normally, I won't have conflict issue but, in this case, does that mean that the performance of my server will be quite crappy if there are a lot of users on the site ?</p>
  
  <p>Moreover, as I said, I will use WebSockets, and this ones will use the db module as well, so if I have a lot of user, the server will often blocks users while it's processing query of previous users, no ? </p>
  
  <p>Finally, I'm really hesitating for the db module. Is it correct to leave it as a singleton or should I create a type for it and create a new object of this type for every database request ?</p>
  
  <p>These questions are quite "specific" and I don't really know what to look on Google to find an answer, so here I am :-) </p>
  
  <p>Thanks a lot for your answers</p>