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.

Possible duplicate of Express - Passing mysql connection to scripts — Gntem
– Gntem, Commented Mar 29, 2016 at 6:20

Daniel · Accepted Answer · 2014-07-31 21:24:16Z

57

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);
  });
};

edited Jul 31, 2014 at 21:24

answered May 28, 2013 at 20:15

Daniel

38.9k12 gold badges95 silver badges73 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

anonymousfox Over a year ago

Thanks for the solution. Could you elaborate on putting queries in a database folder? I'm relatively new to node and I'm finding it harder to do what I want asynchronously.

jczaplew Over a year ago

Just out of curiosity, why do you not need to release the connection back to the pool in getRecords? It seems like the connectionLimit would be hit after 10 requests?

Daniel Over a year ago

You do actually, I missed it. Will put that in there. Thanks for the note.

Banditvibe · Accepted Answer · 2014-01-06 04:07:11Z

1

your solution will work if use db() instead of new db(), which returns an object and not the db connection

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

edited Jan 6, 2014 at 4:07

answered Jan 5, 2014 at 23:43

Banditvibe

3473 silver badges14 bronze badges

Collectives™ on Stack Overflow

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

2 Answers 2

database.js

Route

3 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

database.js

Route

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related