Skip to content

Instantly share code, notes, and snippets.

@hagemann
Last active December 9, 2022 10:24
  • Star 38 You must be signed in to star a gist
  • Fork 8 You must be signed in to fork a gist
Star You must be signed in to star a gist
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
@hoogw
Copy link

hoogw commented May 31, 2018

update:


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

ReferenceError: util is not defined


If you use node version < 8.x do below 2 steps

  1. npm install util.promisify
  2. you have to add following line to database.js

var mysql = require('mysql');

// !!!!! for node version < 8.x only !!!!!
var util = require('util');
require('util.promisify').shim();
// !!!!! end !!!!! for node version < 8.x only !!!!!

..........

===========================================
I got error "pool" Unexpected identifier

I did var pool = require('./database');
also place database.js file under same directory.

`var rows = await pool.query(_sql_rest_url)
^^^^

SyntaxError: Unexpected identifier`

Is your database.js file missing

var util = require('util');?

@hoogw
Copy link

hoogw commented May 31, 2018

Now I use node -v 9.6.1

another error found

SyntaxError: await is only valid in async function

@hoogw
Copy link

hoogw commented May 31, 2018

After I upgrade node -v to 9.6.1. I finally get it works.

  1. can you update your script by add:
    // node -v must > 8.x var util = require('util');

  2. 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)
     }
    

`

@hagemann
Copy link
Author

hagemann commented Jun 7, 2018

Thanks for mentioning, @hoogw. The snippet has been amended on line 1.

@hoogw
Copy link

hoogw commented Jun 11, 2018

This is a very cool script, very easy and handy to use.

@birante
Copy link

birante commented Jul 9, 2018

Thanks a lot

@BilalAlGhazi
Copy link

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.

Copy link

ghost commented Sep 11, 2018

niiiiice

@dineshmickey
Copy link

thanks

@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