Where should I define my MySQL client in a Node.js (Express) app?

Question

Currently I'm setting my client connection for node-mysql by doing the following in my app.js and a special config/environment.js:

var client = mysql.createClient({
  user:     'USER',
  database: 'DATABASE',
  password: 'PASSWORD',
  host:     'HOST'
});

app.configure(function(){
  ...
  app.set('client', client);
  ...
});

Then in my client code I just call app.settings.client to use the MySQL client.

I'm not sure if this is the right approach, and it certainly doesn't work when I'm doing testing, as I need a running instance of the app.

Any suggestions?

Use `app.set('client')` instead of `app.settings.client`. And it should work with testing, you just need to inject an empty `app` into your unit test as part of mocking — Raynos, Jan 06 '12 at 19:31
depends on your code. [I do something like this](https://github.com/Raynos/nCore-example/blob/master/test/routes-home.js) but I would assume your architecture is completely different. — Raynos, Jan 06 '12 at 19:45
@Raynos I've got an [open question](http://stackoverflow.com/questions/8389149/how-do-you-mock-mysql-without-an-orm-in-node-js) with bounty on it about how to handle MySQL mocking. If you could take a stab, I'd be a happy man. — Josh Smith, Jan 06 '12 at 19:49
@Raynos I'm also a little confused about what you mean by using `app.set()`. I *am* doing that, but I can only access the client by calling `app.settings.client` in the client code. What am I missing? — Josh Smith, Jan 06 '12 at 19:52
`app.set(key, value);` sets the value `app.set(key)` gets the value. The fact that the value lives in `app.settings.client` is an implementation detail and might be changed. It's safer to use public documented APIs — Raynos, Jan 06 '12 at 19:55
@Raynos Ah! I didn't realize that at all. Wouldn't it make more sense to have `app.get()` or something? *TJ, I hope you're reading this...* — Josh Smith, Jan 06 '12 at 20:00
as for your mySQL mocking I say don't. Wrap your database code into a small module and test that module with a real database. then mock out the database module when you test your model or test other stuff. I use a mediator for easy mocking of communicating between modules — Raynos, Jan 06 '12 at 20:11
Also note set is short for "settings" not for "setting a value". So it doesn't make sense to split the api into get and set. It's simply annoying that set doubles as a short word for setting a value and as a short word for manipulating application settings — Raynos, Jan 06 '12 at 20:14
@Raynos TJ just told me that he's planning on doing `app.get()` to mean "settings" in 3x. — Josh Smith, Jan 06 '12 at 22:38

kgilpin · Answer 1 · 2012-01-06T19:54:47.143

2

I think that a more typical way to do this would be to define a simple middleware that sets the mysql client as a property of the request. For example:

var client = mysql.createClient({
      user:     'USER',
      database: 'DATABASE',
      password: 'PASSWORD',
      host:     'HOST'
    })

app.use(function(req, res, next) {
    req.mysql = client;
    next();
});

Then in your route handlers you can access req.mysql. In your test cases you just need to set up req.mysql in some way, it should be pretty easy.

edited Jan 06 '12 at 19:54

answered Jan 06 '12 at 19:41

kgilpin

2,201
18
18

3

That's horrible. Your creating a new mysql objcet per request :( – Raynos Jan 06 '12 at 19:45
@Raynos Yeah, that's why I use `app.set()`. – Josh Smith Jan 06 '12 at 19:46
1

Ok well create the client globally, it is still convenient to have req.mysql and seems to fit in well with the Express architecture of passing req, res around. – kgilpin Jan 06 '12 at 19:54

score 2 · Accepted Answer · answered Jan 06 '12 at 20:03

There are 3 solutions the way I see it:

a) As @Raynos suggested in the comments, use app.set(key, value); to set a db value and then app.set(key) to get that value.
b) Wrap your routes into a function that accepts the database as a parameter. Example:

sample_route.js

module.exports = function (db) {
  return function(req, res, next) {
    // db is accessible here
  }
}

app.js

var = sample_route = require('./sample_route')(db);
app.get('/sample', sample_route);

c) Make a global variable that will be accessible everywhere (not recommended though): global.MY_DB = ...;

Where should I define my MySQL client in a Node.js (Express) app?

2 Answers2