John Datserakis's Blog

How To Use MySQL And Async/Await With Your Koa Node.js Application

August 19, 2017 • ☕️ 3 min read

Old and new, an unsurprisingly pleasant match. As a long-time MySQL user it was only natural that I wanted to wire up my Koa application to the weathered database system. Looking to do the same? Let’s walk through a bit of the process to get this started.

Getting started

First, you’ll want to have a koa application started. If you need a demo project to follow along with, just check out the demo Koa app I made and open-sourced on GitHub. You’re welcome to use the code as a starting point for your application.

Next you’ll want to have a node-mysql package installed. There’s a few versions to choose from - we’re going to be using promise-mysql - which I really like because it let’s us use our db calls as Promises with ease. What’s really great is that it relies on all of the original node-mysql’s documentation.

npm install promise-mysql

Now we’ll need to create a db.js file. I put mine in its own folder in my src directory.

Creating a connection pool

Let’s create a db connection pool in db.js.

import mysql from "promise-mysql";
import {} from "dotenv/config";

const config = {
  host: process.env.DB_HOST,
  port: process.env.DB_PORT,
  user: process.env.DB_USER,
  password: process.env.DB_PASSWORD,
  database: process.env.DB_DATABASE,
  connectionLimit: 100
};

const pool = mysql.createPool(config);

export default pool;

In the code above I’m using the awesome dotenv package to access the database credentials I have stored in my .env file. I suggest you do the same. You never want to hardcode something like a database username or password.

As you can see, the code is pretty self-explanatory. Note - we export the pool variable we create so we can use it in our data models.

So let’s say you want to make a database call to grab some an item by using its id. You could do the following:

// From your router

router.get('/item/:id', async (ctx, next) => {

    let item = await show(ctx.params.id);
    ctx.body = item;

})

// In your controller/model

import pool from '../db/db';

async show(id) {

    try {
        //Find item
        let itemData = await pool.query(
            "
              SELECT id, title, content
              FROM items
              WHERE id = ?
            ",
            [id]
        );
        return itemData[0];
    } catch (error) {
        console.log(error);
        ctx.throw(400, 'INVALID_DATA');
    }

}

So let’s take a look at this piece by piece. You may already have noticed that instead of using .then with our Promises, we’re using async and await . This is a awesome improvement for Node and I recommend you read into it further if this is your first time seeing it.

Basically, it lets you use try/catch blocks to run your Promises - avoiding what is known as callback-hell in the Node community.

In our router we run show() - which is a function that returns a value that been retrieved from a Promise. Once that value is retrieved we’ll have Koa display it as a response.

When show() is run we open up a try/catch block and attempt to SELECT from our database. As you can see, we we simply stick an await keyword in front of our pool.query . The rest is pretty straightforward - the node-mysql package will properly escape data with using the question mark escaping method. As you can see, the ? in our WHERE statement is being supplied by the id variable we plug in at the end.

Then, we simply return our response back to our router. I take a little bit of a different route for some of this in my demo Koa app - but I wanted to tighten in up length-wise to make it easier to comprehend for beginners in this example.

That’s it - you’re well on your way to making that SPA you have in mind!

Make sure to read up on exactly what node-mysql functionality has to offer - after using PDO in PHP for years I was pleasantly surprised with the package’s maturity.