node.js mysql pool connection with async/ await

Question

Is there a way to use pool.getConnection() taken from the mysqljs/mysql lib with the async/ await syntax?

The idea is to have a method which returns one connection which can be passed around amongst write queries with various foreign key constraints (sequential queries) before releasing it and at the same time potentially get further connections from the pool for the purpose of various read queries (parallel).

Dunaj

Yes there is a way, wrap "getConnection" inside of a function that returns a promise. Then use await for waiting for it. – Boong

This does not use native Promise API but it will allow you to use async/await: promise-mysql – Jaundiced

Answer 1

Share my working example:

I use this Promisified MySQL middleware for Node.js

read this article Create a MySQL Database Middleware with Node.js 8 and Async/Await

here is my database.js

var mysql = require('mysql'); 

// node -v must > 8.x 
var util = require('util');


//  !!!!! for node version < 8.x only  !!!!!
// npm install util.promisify
//require('util.promisify').shim();
// -v < 8.x  has problem with async await so upgrade -v to v9.6.1 for this to work. 



// connection pool https://github.com/mysqljs/mysql   [1]
var pool = mysql.createPool({
  connectionLimit : process.env.mysql_connection_pool_Limit, // default:10
  host     : process.env.mysql_host,
  user     : process.env.mysql_user,
  password : process.env.mysql_password,
  database : process.env.mysql_database
})


// Ping database to check for common exception errors.
pool.getConnection((err, connection) => {
if (err) {
    if (err.code === 'PROTOCOL_CONNECTION_LOST') {
        console.error('Database connection was closed.')
    }
    if (err.code === 'ER_CON_COUNT_ERROR') {
        console.error('Database has too many connections.')
    }
    if (err.code === 'ECONNREFUSED') {
        console.error('Database connection was refused.')
    }
}

if (connection) connection.release()

 return
 })

// Promisify for Node.js async/await.
 pool.query = util.promisify(pool.query)



 module.exports = pool

You must upgrade node -v > 8.x

you must use async function to be able to use await.

example:

   var pool = require('./database')

  // node -v must > 8.x, --> async / await  
  router.get('/:template', async function(req, res, next) 
  {
      ...
    try {
         var _sql_rest_url = 'SELECT * FROM arcgis_viewer.rest_url WHERE id='+ _url_id;
         var rows = await pool.query(_sql_rest_url)

         _url  = rows[0].rest_url // first record, property name is 'rest_url'
         if (_center_lat   == null) {_center_lat = rows[0].center_lat  }
         if (_center_long  == null) {_center_long= rows[0].center_long }
         if (_center_zoom  == null) {_center_zoom= rows[0].center_zoom }          
         _place = rows[0].place


       } catch(err) {
                        throw new Error(err)
       }

Hubsher

What about connection release – Thermion

@MohammadMaroofMalik the connection will be automatically released back into the pool after they’ve been used. See here (medium.com/@matthagemann/…) – Eckardt

Answer 2

Mates. I don't know why but I tried all the day long but couldn't get it to work. By the help of your comments I tried again and it of course does work.

db.js:

const pool = mysql.createPool(config);

exports.getConnection = () => {
    return new Promise((resolve, reject) => {
        pool.getConnection(function (err, connection) {
            if (err) {
                return reject(err);
            }
            resolve(connection);
        });
    });
};

someWhereElse.js:

const db = require('./db');

const wrappingFunction = async () => {
    const connection = await db.getConnection();
    console.log(connection);
};
wrappingFunction();

Dunaj

Answer 3

Seems like implementing promises manually is a better option. Just sharing what I have used in my code -

const mysql = require('mysql');
const config = require('config');

const pool = mysql.createPool(config.get('db.mysql'));

module.exports = {
    checkConnection: () => {
        return new Promise((resolve, reject) => {
            pool.getConnection((err, conn) => {
                if (err) {
                    return reject(err);
                }
                resolve(conn.release());
            });
        });
    },
    pool,
    closeConnection: () => pool.end(),
};

Dower

Answer 4

Previous answers (with util.promisify) did not work for me, and only implementing Promise manually works:

Function:

async function removeItem (id)  {

return new Promise( (resolve) => {
    pool.query('DELETE FROM table_name WHERE id=' + id, (error) => {
          resolve ({result: !error});
        });
    }); 
} 

Usage:

const app = express();
const mysql = require('mysql');
const pool = mysql.createPool({
            connectionLimit: 10,
            host: 'localhost',
            user: 'login',
            password: 'pass',
            database: 'dbname'
        });



app.post("/:id", async (req, res) => {
        const answer = await itemRemove(id);
        res.send(answer);
    });

Assign

Answer 5

Sure, you would have to promisify it first, which you can do since node 8.0.0 now:

const util = require('util');

async function doSomething() {
     const getConnectionAsync = util.promisify(pool.getConnection);
   try {
       const result = await getConnectionAsync('MASTER');
    }catch(err) {
       console.log('Oh no');
    }
} 

If for some reason you can't use node 8 or above, there are other ways to promisify it, like http://bluebirdjs.com/docs/api/promise.promisify.html

Dwight

this not work, it shows me the following error: ``` TypeError [ERR_INVALID_ARG_TYPE]: The "original" argumen t must be of type Function. ``` – Grandfather

Answer 6

Just sharing what I've always use in my code:

//Filename: MySQL.js    

module.exports = {
    connect: function ()
    {
        return new Promise((resolve, reject) => {

        let pool = Mysql.createPool({ //require configfile.js or just put connection detail here
                connectionLimit: config.mysql.connectionLimit,
                host: config.mysql.host,
                user: config.mysql.user,
                password: config.mysql.password,
                database: config.mysql.database
            });

            pool.getConnection((err, connection) =>
            {
                try
                {
                    if (connection)
                    {
                        resolve({"status":"success", "data":"MySQL connected.", "con":pool});
                        connection.release();
                    }
                }
                catch (err)
                {
                    reject({"status":"failed", "error":`MySQL error. ${err}`});
                }
                resolve({"status":"failed", "error":"Error connecting to MySQL."});
            });
        });
    }
}

Then whenever you need to call the connection to MySQL

//Filename: somefile.js

const useMySQL = require('./path/to/MySQL');

module.exports = {

    getSomething: function () {
        return new Promise(async (resolve) => {

            try
            {
                let connection = await useMySQL.connect();
                con = connection.con;

                //Do some query here, then
                resolve(`Send some result/handle error`);
            }
            catch (err)
            {
                //Handle error if any, log, etc, and eventually
                resolve(err);

            }
        });
    }

Hope this helps.

Beckmann

Answer 7

This is what I usually do:

const { Pool } = require('pg')
const forConnection = async () => {
        try {
            console.log(await pool.query('SELECT NOW()')); //test the connection by executing a query to get the current timestamp from the database.
        } catch (error) {
            console.log("Not Connected");
        }
    }
    
    forConnection()

    module.exports = pool // this is optional

Chercherbourg