How to properly pass mysql connection to routes with express.js

Question

I am trying to figure out the best way to pass a mysql connection (using node-mysql) between my routes for express.js. I am dynamically adding each route (using a for each file loop in routes), meaning I can't just pass in the connection to routes that need it. I either need to pass it to every route or none at all. I didn't like the idea of passing it to ones that dont need it so I created a dbConnection.js that the routes can individually import if they need. The problem is that I dont think I am doing it correctly. As of now, my dbConnection.js contains:

var mysql = require('mysql'); var db = null; module.exports = function () {     if(!db) {             db = mysql.createConnection({                 socketPath: '/tmp/mysql.sock',             user: '*********',             password: '*********',             database: '**********'         });     }     return db; }; 

And I am importing it into each route using:

var db = require('../dbConnection.js'); var connection = new db(); 

But I would like to do it like this:

var connection = require('../dbConnection.js'); 

When I try it like this, however, I get an error saying connection has no method 'query' when I try to make a query.

Answer 1

I find it more reliable to use node-mysql's pool object. Here's how I set mine up. I use environment variable for database information. Keeps it out of the repo.

database.js

var mysql = require('mysql');  var pool = mysql.createPool({   host: process.env.MYSQL_HOST,   user: process.env.MYSQL_USER,   password: process.env.MYSQL_PASS,   database: process.env.MYSQL_DB,   connectionLimit: 10,   supportBigNumbers: true });  // Get records from a city exports.getRecords = function(city, callback) {   var sql = "SELECT name FROM users WHERE city=?";   // get a connection from the pool   pool.getConnection(function(err, connection) {     if(err) { console.log(err); callback(true); return; }     // make the query     connection.query(sql, [city], function(err, results) {       connection.release();       if(err) { console.log(err); callback(true); return; }       callback(false, results);     });   }); }; 

Route

var db = require('../database');  exports.GET = function(req, res) {   db.getRecords("San Francisco", function(err, results) {     if(err) { res.send(500,"Server Error"); return;     // Respond with results as JSON     res.send(results);   }); };