title: Node MySQL - reusable connection module
body: <p><strong>Question Updated:</strong></p>
  
  <hr/>
  
  <p>The solution should detail a simplified, proper model of a <strong>Node connection module</strong> allowing for the re-use of the connection by any module of a Node application needing to connect to a database. That way, the question might be useful for anyone with connection module issues in Node. </p>
  
  <p>The answer might even include a way to pass in different credentials in order to connect to different tables or databases with a single function call from anywhere in an app.</p>
  
  <hr/>
  
  <p>I wrote a script that utilized a number of simple modules to allow a user to post login data, have that data validated on the server, and if correct, receive a response of success. A very basic login functionality.</p>
  
  <p><strong>The problem:</strong> One user can log-in, but any more log-in attempts before I restart the server fail to connect to the database. </p>
  
  <p>It appears that, because I'm declaring the connection in a variable in my module db_connect and requiring that module, the connection can't be re-used. It's being declared in the required module, and I had mistakenly believed that calling the variable in each connection attempt would recreate the connection. It doesn't.</p>
  
  <p><strong>The solution:</strong> As suggested by barry in the comments, in the db_connect module, I need to make the connection functionality a function rather than a variable, so I can then create the connection from within my validation script. </p>
  
  <p><strong>How can I do this?</strong> I'm trying to output the connection object when calling the <code>createConnection()</code> function, which is an exported method of db_connect.</p>
  
  <p><strong>db_connect:</strong></p>
  
  <pre><code>console.log('db_connect module initialized');
  var mysql      = require('mysql');
  
  function createConnection(){
      var connection = mysql.createConnection({
          host     : 'localhost',
          user     : 'root',
          password : '',
          database : 'officeball'
      });
  }
  
  exports.createConnection = createConnection();
  exports.mysql = mysql;
  </code></pre>
  
  <p><strong>validator:</strong></p>
  
  <pre><code>console.log('validator module initialized');
  var connect = require("./db_connect");
  
  function validate(username, password, callback){
  
      var createConnection = connect.createConnection();
      //the idea is for this to return the object, connection, 
      //which opens a new connection
  
      connection.connect(function (err){
          if (err) return callback(new Error('Failed to connect'), null);
          console.log('Connection with the Officeball MySQL database openned...');
  
          connection.query('select username,password,fname,lname,rank,active from users where username=?',
                  username,
                  function(err,rows,fields) {
                      connection.destroy();
                      console.log('...Connection with the Officeball MySQL database closed.');
                      if (err)
                          return callback(new Error ('Error while performing query'), null);
                      if (rows.length !== 1)
                          return callback(new Error ('- [Anomaly] - Failed to find exactly one user'), null);
  
                      if (rows[0].password === password &amp; rows[0].active === "yes") {
  
                          var result = new Object;
  
                          result.username = rows[0].username;
                          result.password = rows[0].password;
                          result.fname = rows[0].fname;
                          result.lname = rows[0].lname;
                          result.rank = rows[0].rank;
  
                          return callback(null, result);
  
                      } if(rows[0].active !== "yes"){
  
                          return callback(new Error ('User account not active.'), null);
  
                      }else {
  
                          return callback(new Error ('Login credentials did not match.'), null);
  
                      }
  
                  });
  
  
      });
  };
  
  exports.validate = validate;
  </code></pre>
  
  <p><strong>Console log</strong> <em>(originally, there was a connection error, but after my fix-attempt, the error is about the method)<strong></em>:</strong></p>
  
  <pre><code>C:\xampp\htdocs\officeball\node_scripts&gt;node index.js
  application initialized
  server module initialized
  login module initialized
  validator module initialized
  db_connect module initialized
  sale module initialized
  Server running at http://127.0.0.1:8080/
  User username1 is attempting login...
  TypeError: Property 'createConnection' of object #&lt;Object&gt; is not a function
      at Object.validate (C:\xampp\htdocs\officeball\node_scripts\custom_modules\v
  alidator.js:6:33)
      at C:\xampp\htdocs\officeball\node_scripts\custom_modules\login.js:61:13
      at callbacks (C:\xampp\htdocs\officeball\node_scripts\node_modules\express\l
  ib\router\index.js:164:37)
      at param (C:\xampp\htdocs\officeball\node_scripts\node_modules\express\lib\r
  outer\index.js:138:11)
      at pass (C:\xampp\htdocs\officeball\node_scripts\node_modules\express\lib\ro
  uter\index.js:145:5)
      at Router._dispatch (C:\xampp\htdocs\officeball\node_scripts\node_modules\ex
  press\lib\router\index.js:173:5)
      at Object.router (C:\xampp\htdocs\officeball\node_scripts\node_modules\expre
  ss\lib\router\index.js:33:10)
      at next (C:\xampp\htdocs\officeball\node_scripts\node_modules\express\node_m
  odules\connect\lib\proto.js:193:15)
      at multipart (C:\xampp\htdocs\officeball\node_scripts\node_modules\express\n
  ode_modules\connect\lib\middleware\multipart.js:93:27)
      at C:\xampp\htdocs\officeball\node_scripts\node_modules\express\node_modules
  \connect\lib\middleware\bodyParser.js:64:9
  </code></pre>