MySQL with Node and Express: Switching from MongoDB

I might be biased, but getting started with MongoDB and Express is been pretty easy and fast. I find this combination a pretty good when you want to quickly put a prototype together. Of course, Mongo isn’t always the preferred choice for a DBMS. Once you start dealing with joins and crazy aggregations Mongo becomes a pain in the neck.

Couple things come to mind that I hate during development: working with complicated regex, and mongo aggregate queries. I mean, if you are not an expert with either syntax you can search around and find what you are looking for, but once your queries get complicated working with relational databases and SQL is just so much easier. And it’s kind of a nice thing to know that you don’t have any code that you left kicking around that might eat all your RAM one day.

For relational databases MySQL or Postgre seems like a natural choice. I did some research on how Express is being used with MySQL and I was surprised how little amount of information there is on MySQL. More specifically I want to:

• Be able to write more elegant code modern, ES6
• I don’t like working with Promises; async/await looks way cleaner in my opinion.
• I want to keep MongoDB Syntax. Not just because I don’t want to rewrite all my queries but MongoDB queries can be fun to code with as long as you are not doing any crazy aggregations that will eat all your memory.

I couldn’t find something I was happy with so I put one together. I hope someone finds this useful and maybe it will make this transition easier for you. I’ll probably be contributing to it since I’ll continue using MySQL and Node.

Standard mysql npm module

The standard mysql client allows you to create connection pools which is perfect. You can do that when you start your express server. Now to write queries it would be nice to remove as much boilerplate code as possible. This query below just does not look appealing:

pool.getConnection(function(err, connection) {
  if (err) throw err; // not connected!

  // Use the connection
  connection.query('SELECT something FROM sometable', function (error, results, fields) {
    // When done with the connection, release it.
    connection.release();

    // Handle error after the release.
    if (error) throw error;

    // Don't use the connection here, it has been returned to the pool.
  });
});

If you have multiple queries running and you don’t care in what order they run you can just call query directly which has a shortcut:

pool.getConnection() -> connection.query() -> connection.release()

and this already looks better:

pool.query('SELECT 1 + 1 AS sum', function (error, results, fields) {
  if (error) throw error;
  console.log('The sum is: ', results[0].sum);
});

A mysql wrapper with async/await

How can we make this even better? I prefer to not have to write SQL unless I have a complicated query. Also, I don’t want to implement the callback every time I run a query. If I have 5 queries to execute then I’ll have several nested implementations of the callback. async/await looks way more elegant. There is some performance hit since each query execution is blocking. However, more often then not you are chaining executions anyway so that would be the same thing. It’s just less pretty.

If we simplify query above we end up with something like this:

const sum = await pool.queryOne('SELECT 1 + 1 AS sum').sum;

This is already looking better. If there is an error I am expected that it will be thrown by my wrapper so I don’t need to handle it every time. Also, I’m only interested in the results and that’s what I get back.

So how about using a MongoDB like syntax? This is one is a matter of taste. I decided to implement find, findOne, updateMany, updateOne, deleteOne, deleteMany, and query — where query is a catch all used for custom SQL.

Another option is to create something like an active record object which allows me to do joins, aggregations, projections, limits, etc. But because I like mongo syntax for simple CRUD operations and simple retrievals so I don’t want to bother making this more complicated then it should be. The end result is an application that is pretty easy to read.

Once I initialize my Express server I set a variable which I can use later in request object. So if I want to execute a query in the route function it’s as simple as this:

const mysql = mysqljs(req);
affected = await mysql.createOne('users', { user_id, name: 'Tim'}); console.log(affected); --> 1
user = await mysql.findOne('users', { user_id });
console.log(user); --> { user_id: ... , name: 'Tim' }
affected = await mysql.updateOne('users', { user_id }, { name: John' });
console.log(affected); --> 1
affected = await mysql.deleteOne('users', { user_id });
console.log(affected); --> 1

How about escaping input?

One thing to keep in mind while using a wrapper like this is that we want to avoid escaping and sanitizing any input ourselves. The wrapper functions already make use of mysql package escape functions. Not that you shouldn’t be escaping your own data, but it’s easier if you let a library to do that. Also you don’t end up with escape statements everywhere that will confuse all developers about where escaping should happen.

Okay, so if you let the wrapper do the escaping then you just need to make sure you don’t insert any input in your custom SQL. This is only a problem if you are using query function which allows you free form SQL.

await pool.query(`SELECT 1 + ${someNumber} AS sum`);      <-- BAD
await pool.query(`SELECT 1 + $count AS sum`, {count: 1}); <-- BETTER

Enjoy…

To see what I have done so far see full documentation: mysqljs wrapper.