How to connect to mysql from nodejs, with ES6 promise

March 30, 2020

Introduction

I had to develop a small automation to query some old mysql data, with given product names. The mysql database had a table name products, and I was intersted in three fields:

name, created_at, updated_at

I had a text file with product names in one line, and had to query the two dates from the table. I wrote a small nodejs automation for this.

Expected Output

The expected ourput I wanted is a csv file:

ProductName, created_at, updated_at

Npm Modules Used

MySql The core module which has apis to connect to mysql. All the apis are callback based, not in promise form.
es6-promisify This module is used to convert mysql module’s apis into promised form. This module is very handy.
async I want to perform async loop with promises in between.
momentjs I wanted to format some dates from mysql table.

Code

I wrote a mysql client class to query this.

const mysql = require('mysql');
const {promisify} = require("es6-promisify");
const async = require('async');
const moment = require('moment');

class Client {
    init(config) {
        console.log('Connecting to mysql...');
        this.__connection = mysql.createConnection({
            host : config.host,
            user : config.user,
            password : config.password,
            database : config.database
            });
        this.__connection.connect();
        this.__query = promisify(this.__connection.query.bind(this.__connection));

        console.log('connected');
        return Promise.resolve();
    }

    testQuery(lines) {
        return new Promise((resolve, reject) => {
            async.eachLimit(lines, 1, (line, callback) => {
                if (line) {
                    const query = `select * from products where name like \'%${line.trim()}%\'`;
                    return this.__query(query)
                        .then((res) => {
                            if (res && res.length > 0) {
                                const cd = moment(res[0].created_at).format('DD-MM-YYYY');
                                const ud = moment(res[0].updated_at).format('DD-MM-YYYY');
                                console.log(`${line.trim()},${cd},${ud}`);
                            }
                            else {
                                console.error('ERROR', line.trim());
                            }
                            callback();
                        })
                        .catch((err) => {
                            callback(err);
                        });
                }
                else {
                    callback();
                }
            }, function (err) {
                if (err) {
                    reject(err);
                } else {
                    console.log('Done');
                    resolve();
                }
            });
        });
    }
}

module.exports = new Client();

And, a main app file

// my above client class
const client = require('./src/product_mysql/client');

const fs = require('fs');
const async = require('async');
// file from where to read product names
const data = fs.readFileSync('<path>/prod_names.txt', 'UTF-8');
const lines = data.split(/\r?\n/);

const config = {
    host     : 'localhost',
    user     : 'xxxxx',
    password : 'xxxxxxxx',
    database : 'xxxxxx'
};
return client.init(config)
    .then(() => {
        return client.testQuery(lines);
    })
    .then( () => {
        console.log('Success');
    })
    .catch(err => {
        console.error(err);
    });

Note: For logging purpose, I just used simple console.log statements. I should have winston module. But, this was a small automation and I don’t need that much effort.

Running the Program and Output

To run program

node app

Output

Android-DEXI Framework,03-11-2016,12-03-2018
Mobile-Files,03-11-2016,12-03-2018
Sekhmet,03-11-2016,12-03-2018
Sample,03-11-2016,12-03-2018
Codex and Build 2 Ship,03-11-2016,12-03-2018
Test Touch,03-11-2016,12-03-2018

Let me know if you have any query.

nodejs mysql es6-promisify