❤
npm
Sign UpSign In

mysql-async-wrapper
TypeScript icon, indicating that this package has built-in type declarations

2.0.1 • Public • Published

Async Mysql Wrapper

This is a Wrapper class, which helps to get rid of callbacks of mysql package functions and provides a way to use them in async await (es7) syntax, Below Examples uses express framework in both (import/export syntax and commonJs syntax)

It also supports retry query execution for provided error codes

Import BaseDatabase class from mysql-async-wrapper, create a db instance with pool and export it

database.js (import/export syntax)

import mysql from "mysql"
import BaseDatabase from "mysql-async-wrapper"
 
const pool = mysql.createPool({
    //pool configuration
})
 
const db = new BaseDatabase(pool);
export default db;

database.js (commonJs Syntax)

const mysql = require("mysql");
const BaseDatabase = require("mysql-async-wrapper").default; // you need to add default (it's a typescript compiler issue)
 
const pool = mysql.createPool({
    //pool configuration
})
 
const db = new BaseDatabase(pool);
module.exports = db;

to retry query execution in case of error pass configuration object while creating db instance like below

 
const maxRetryCount = 3; // Number of Times To Retry
const retryErrorCodes = ["ER_LOCK_DEADLOCK", "ERR_LOCK_WAIT_TIMEOUT"] // Retry On which Error Codes 
 
const db = new BaseDatabase(pool, {
    maxRetrCount,
    retryErrorCodes
})
 

now in api controllers ( route handlers )

import db from "database.js"; // const db = require("database.js") in case of commonJS
 
async function controller(req, res, next){
    try{
 
        const connetion = await db.getConnection();
        
        const empQuery = `Select * from Employees`;
        const empResult = await connection.executeQuery(empQuery, []);
 
        const deptQuery = `Select * from Departments`;
        const deptResult = await connection.executeQuery(deptQuery, []);
 
    }catch(err){
        next(err); 
    }finally{
        db.close(); // To Release Connection
    }
}
  • To begin transaction pass transaction true in options while calling getConnection
  • Incase of error during query executing and connection is in transaction then it will automatically get rollback
  • But To rollback transaction in case of errors other than query errors please use rollback in catch block
import db from "database.js";
 
async function controller(req, res, next){
    try{
 
        const connection = await db.getConnection({ transaction: true }); // Will Begin Transaction
        
        const empQuery = `Insert into Employees (EmpID, Name) values (?,?)`;
        const empResult = await connection.executeQuery(empQuery, ["E02", "Abhay"]); // Incase of error auto rollback of transaction will be done
 
        const deptQuery = `Insert into Departments (DeptID, EmpID) values (?,?)`;
        const deptResult = await connection.executeQuery(deptQuery, ["D01", "E02"]); 
 
        await db.commit();
    }catch(err){
        db.rollback(); // to rollback in case of errors other than query error
        next(err); 
    }finally{
        db.close(); 
    }
}

If Required Transaction can be begin using beginTransaction

async function controller(req, res, next){
    try{
 
        const connection = await db.getConnection(); 
 
        const getEmpQuery = `Select EmpID, Name from Employees where EmpID = ?`;
        const getEmpResult = await connection.executeQuery(empQuery, ["E01"]); 
 
 
        await db.beginTransaction(); // Will Begin Transaction
        
        const empQuery = `Insert into Employees (EmpID, Name) values (?,?)`;
        const empResult = await connection.executeQuery(empQuery, ["E02", "Abhay"]); 
 
        const deptQuery = `Insert into Departments (DeptID, EmpID) values (?,?)`;
        const deptResult = await connection.executeQuery(deptQuery, ["D01", "E02"]); 
 
        await db.commit();
    }catch(err){
        db.rollback();
        next(err); 
    }finally{
        db.close(); 
    }
}

you can also use retry for specific error codes for particular query only

 
const empQuery = `Insert into Employees (EmpID, Name) values (?,?)`;
const empResult = await connection.executeQuery(empQuery, ["E02", "Abhay"], ["ER_LOCK_DEADLOCK"]); // by simply passing array of error codes as 3 parameter of execute query
 

Readme

Keywords

Package Sidebar

Install

npm i mysql-async-wrapper

Repository

github.com/Abhay2012/mysql-async-wrapper

Homepage

github.com/Abhay2012/mysql-async-wrapper

Weekly Downloads

19

Version

2.0.1

License

ISC

Unpacked Size

18.6 kB

Total Files

6

Last publish

Collaborators

  • abhay20
Try on RunKit
Report malware