Skip to content

Instantly share code, notes, and snippets.

@hagemann
Last active December 9, 2022 10:24
Show Gist options
  • Save hagemann/30cfee724d047007a031eb12b3a95a23 to your computer and use it in GitHub Desktop.
Save hagemann/30cfee724d047007a031eb12b3a95a23 to your computer and use it in GitHub Desktop.
Promisified MySQL middleware for Node.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
@scandar
Copy link

scandar commented Nov 15, 2018

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 :/

@kachar
Copy link

kachar commented Dec 21, 2018

@bryanxavierinucach
Copy link

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

@kamalhm
Copy link

kamalhm commented Mar 7, 2019

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

thanks!

@rpf5573
Copy link

rpf5573 commented Apr 26, 2019

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

this code is not work on typescript.

@umrgit123
Copy link

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!!

@hagemann
Copy link
Author

hagemann commented Jul 2, 2019

@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.

@Otomakan
Copy link

Otomakan commented Oct 30, 2019

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)

@Castrogiovanni20
Copy link

Thanks a lot. This is useful

@AkashWarlocks
Copy link

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

@zirtrex
Copy link

zirtrex commented Nov 2, 2020

hi, how I add queryFormat to pool connections?

@mrudangshah
Copy link

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);
  }
}

Screenshot from 2021-05-24 11-29-29

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment