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(; 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.