DRY Routing Techniques Using Express Middleware and MySQL

Use Case: To make code easier to change and easier to read

Sometimes a database needs to be queried more than once in order to provide the data needing to be rendered by a page. An example of this is the content management system that I created for my clients in Node.js.

In the CMS, the database is queried for:

• Page content
• Metadata
• Checking for alerts

- and that's for each page. Some pages need to make additional queries like when the home page wants to also display the 3 most recent blog posts, for example.

Without leveraging middleware or writing DRY code, that could look like this:

router.get('/', (req, res) => {
	const sql0 = `SELECT * FROM meta WHERE page = 'Home'`
	const sql1 = `SELECT * FROM alerts WHERE published = 'on'`
	const sql2 = `SELECT * FROM pages WHERE name = 'Home'`
	const sql3 = `SELECT * FROM events WHERE published = 'on' ORDER BY start_date ASC`
	pool.query(`${sql0};${sql1};${sql2};${sql3}`, function (error, results) {

		// Do stuff with results

		// Render page
		var args = { meta: results[0][0], alerts: results[1], ... }
		res.render('index', args)
	})
})

This looks terrible, but I've even seen people nest MySQL queries inside of queries like this:

router.get('/', (req, res) => {
	const sql0 = `SELECT * FROM meta WHERE page = 'Home'`
	pool.query(sql0, function (error, results) {

		// Do stuff with results

		const sql1 = `SELECT * FROM alerts WHERE published = 'on'`
		pool.query(sql1, function (error, results) {

			// Do stuff with results

			const sql2 = `SELECT * FROM pages WHERE name = 'Home'`
			pool.query(sql2, function (error, results) {

				// Do stuff with results

				const sql3 = `SELECT * FROM events WHERE end_date >= ${pool.escape(todayInt)} AND published = 'on' ORDER BY start_date ASC`
				pool.query(sql3, function (error, results) {

					// Do stuff with results

					// Render page
					var args = { meta: results[0][0], alerts: results[1], ... }
					res.render('index', args)
				})
			})
		})
	})
})

Can you imagine repeating this block of code for every single route? Way too much work and way too much clutter. At least, I thought so, so here's how I fixed it:

Using Express Middleware

The first thing I did was break the database query for alerts into its own middleware function. Every page (at least on the front end) would be using this, so might as well write the code once and make it easy to reuse.

var alerts = (req, res, next) => {
	db.pool.query(`SELECT * FROM alerts WHERE published = 'on'`, function (error, results) {
		req.alerts = results[0]
		next()
	})
}

We'll check the database for any alerts and then create a new property on the request or req variable for alerts.

Then I needed to divide up my routes based on those that render simply static content versus those that render more dynamic content. So here's our static middleware:

var static = (req, res, next) => {
	const sql0 = `SELECT * FROM meta WHERE url = '${req.originalUrl}'`
	const sql1 = `SELECT * FROM pages WHERE url = '${req.originalUrl}'`
	db.pool.query(`${sql0};${sql1}`, function (error, results) {

		// Do stuff with results

		// Render page
		req.args = { meta: results[0][0], alerts: req.alerts, ... }
		next()
	})
}

So, for each page deemed to be 'static', I'll load this middleware that'll get metadata and page content for each route. Then, we'll attach a property called args to req to hold our results thus far to be used by the rendered page.

Lastly, we'll need middleware for our dynamic routes.

var dynamic = (req, res, next) => {
	req.args = { alerts: req.alerts, ... }
	next()
}

Unfortunately, there isn't much that we can do ahead of time since, as the name suggests, this middleware will be used on dynamic routes that don't share a lot similarities. Those that do, however, we'll load here.

Applying Middleware to Routes

Here's how we can now apply the middleware we created on our static routes:

router.get('/about', alerts, static, (req, res) => {
	res.render('about', req.args)
})

Much better don't you think? We'll call the alerts and static middleware functions in our router.get function, then render our page using the about template and passing in the arguments that we previously saved.

Okay, that looks awesome. Now how about our dynamic routes?

router.get('/events/:id', alerts, dynamic, (req, res) => {
	const sql = `SELECT * FROM events WHERE ID = ${db.pool.escape(req.params.id)}`
	db.pool.query(`${sql}`, function (error, results) {

 // Do stuff with results

		// Render page
		req.args.event = results[0]
		res.render('events/single', req.args)
	})
})

In this route, we'll be loading individual events based on ID. Because of this, the page content can't be static but rather changes for each event. So we'll call the dynamic function along with the alert function to pull in what we can, then we'll manipulate the current event's results and render those with the page.

Results

Clear, concise, don't-repeat-yourself programming with Express middleware. Now, as I think of other ways to improve my programs I'll need to make changes in only one location instead of at each individual route.

Note - for sake of clarity, I cut out a few things (i.e. error handling) - in case you use this post for reference.