John Datserakis

John Datserakis

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

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.

1npm 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.

1import mysql from 'promise-mysql';
2import {} from 'dotenv/config';
3
4const config = {
5  host: process.env.DB_HOST,
6  port: process.env.DB_PORT,
7  user: process.env.DB_USER,
8  password: process.env.DB_PASSWORD,
9  database: process.env.DB_DATABASE,
10  connectionLimit: 100,
11};
12
13const pool = mysql.createPool(config);
14
15export 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:

1// From your router
2
3router.get('/item/:id', async (ctx, next) => {
4  let item = await show(ctx.params.id);
5  ctx.body = item;
6})
7
8// In your controller/model
9
10import pool from '../db/db';
11
12async show(id) {
13  try {
14    //Find item
15    let itemData = await pool.query(
16      "
17        SELECT id, title, content
18        FROM items
19        WHERE id = ?
20      ",
21      [id]
22    );
23    return itemData[0];
24  } catch (error) {
25    console.log(error);
26    ctx.throw(400, 'INVALID_DATA');
27  }
28}

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.

Home