Promisified MySQL middleware for Node.js

database.js

const util = require('util')
const mysql = require('mysql')
const pool = mysql.createPool({
  connectionLimit: 10,
  host: 'localhost',
  user: 'root',
  password: 'password',
  database: 'my_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

Thank you for this great script, it helped me in handling closed connections, and the promisify part helped keeping the code much cleaner than callback functions.

niiiiice

thanks

using pool.query after requiring in any other module throws this error
SyntaxError: await is only valid in async function

which i've handled by using an IIFE

(async function getbooks() {
  const res = await pool.query('SELECT * FROM books');
  console.log(res);
}());

i don't know if this is the best way to handle this case :/

As usual... There's a lib for this https://github.com/sidorares/node-mysql2#using-promise-wrapper

me gustaría saber como realizar un servicio con este método de conexión para realizar un login

As usual... There's a lib for this https://github.com/sidorares/node-mysql2#using-promise-wrapper

thanks!

pool.query = util.promisify(pool.query);

this code is not work on typescript.

Thank you very very much. My Node.js MySQL application on heroku was frequently crashing. Looked all over for a fix and ended up finding your article on medium about using pool middleware. It worked perfectly and now my application does not crash anymore. Thanks again!!

@umrgit123 Glad you found this middleware and that it solved your crashes! Thanks a lot for commenting, and I wish you lots of growth with your application.

For typescript you can do something like

import mysql, { Pool, QueryFunction } from 'mysql'
import { promisify } from 'util'

interface PromisifiedPool extends Omit<Pool, 'query'> {
    query: QueryFunction | Function;
}

const pool: PromisifiedPool = mysql.createPool({
    host: `${process.env.DB_HOST}`,
    user: `${process.env.DB_USER}`,
    password: `${process.env.DB_PASSWORD}`,
    database: `${process.env.DB_NAME}`
})


pool.query = promisify(pool.query)

Thanks a lot. This is useful

whenever i use await it shows 3 dot (...) below it meaning 'await' has no effect on the type of this expression.

i have used everything mentioned above

hi, how I add queryFormat to pool connections?

Hi there,

I am using this database connection configuration but somehow it is throwing an error while fire the query.

import { pool } from '../../config/mysql-config';
public async insertRecords() {
  try {
    const SQL = `INSERT INTO ${tableName} (description, title)
      VALUES ("description", "title")`

    const result = await pool.query(SQL)
    return result
    
  } catch (error) {
    throw new Error(error);
  }
}