Old and new – an unsurprisingly pleasant match. As a long-time MySQL user it was only natural that I wanted to hook 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.

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 have on GitHub. You’re welcome to use the code as a starting point for your application.

First 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

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

Now let’s create a db connection pool in db.js.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
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 am using the awesome dotenv package to access the datavase 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. 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
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
//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 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 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 string 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.

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.