CODEWITHSUNDEEP
mysqlnode.jsexpressnode-mysql
Jason
Jason

Reputation: 965

Pattern for handling MySQL database connections within an express application

I am using express 4.x, and the latest MySQL package for node.

The pattern for a PHP application (which I am most familiar with) is to have some sort of database connection common file that gets included and the connection is automatically closed upon the completion of the script. When implementing it in an express app, it might look something like this:

// includes and such
// ...
var db = require('./lib/db');

app.use(db({
  host: 'localhost',
  user: 'root',
  pass: '',
  dbname: 'testdb'
}));

app.get('/', function (req, res) {
  req.db.query('SELECT * FROM users', function (err, users) {
    res.render('home', {
      users: users
    });
  });
});

Excuse the lack of error handling, this is a primitive example. In any case, my db() function returns middleware that will connect to the database and store the connection object req.db, effectively giving a new object to each request. There are a few problems with this method:

The next pattern I've seen is to simply open one connection as the app starts.

var mysql = require('mysql');

var connection = mysql.createConnection(config);
connection.on('connect', function () {
  // start app.js here
});

Problems with this:

My question is, what kind of approach should be taken with handing database connections in an express app? It needs to scale (not infinitely, just within reason), I should not have to manually close in the route/include extra middleware for every path, and I (preferably) to not want to catch timeout errors and reopen them.

Upvotes: 2

Views: 2714

Answers (1)

clay
clay

Reputation: 6017

Since, you're talk about MySQL in NodeJS, I have to point you to KnexJS! You'll find writing queries is much more fun. The other thing they use is connection pooling, which should solve your problem. It's using a little package called generic-pool-redux which manages things like DB connections.

The idea is you have one place your express app access the DB through code. That code, as it turns out, is using a connection pool to share the load among connections. I initialize mine something like this:

var Knex = require('knex');
Knex.knex = Knex({...}); //set options for DB

In other files

var knex = require('knex').knex;

Now all files that could access the DB are using the same connection pool (set up once at start).

I'm sure there are other connection pool packages out there for Node and MySQL, but I personally recommend KnexJS if you're doing any dynamic or complex SQL queries. Good luck!

Upvotes: 1

Related Questions