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)
const pool = mysql const db = pool;;
database.js (commonJs Syntax)
const mysql = ;const BaseDatabase = default; // you need to add default (it's a typescript compiler issue) const pool = mysql const db = pool;moduleexports = 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 Retryconst retryErrorCodes = "ER_LOCK_DEADLOCK" "ERR_LOCK_WAIT_TIMEOUT" // Retry On which Error Codes const db = pool maxRetrCount retryErrorCodes
now in api controllers ( route handlers )
; // const db = require("database.js") in case of commonJS { try const connetion = await db; const empQuery = `Select * from Employees`; const empResult = await connection; const deptQuery = `Select * from Departments`; const deptResult = await connection; catcherr ; finally db; // 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
; { try const connection = await db; // Will Begin Transaction const empQuery = `Insert into Employees (EmpID, Name) values (?,?)`; const empResult = await connection; // Incase of error auto rollback of transaction will be done const deptQuery = `Insert into Departments (DeptID, EmpID) values (?,?)`; const deptResult = await connection; await db; catcherr db; // to rollback in case of errors other than query error ; finally db; }
If Required Transaction can be begin using beginTransaction
{ try const connection = await db; const getEmpQuery = `Select EmpID, Name from Employees where EmpID = ?`; const getEmpResult = await connection; await db; // Will Begin Transaction const empQuery = `Insert into Employees (EmpID, Name) values (?,?)`; const empResult = await connection; const deptQuery = `Insert into Departments (DeptID, EmpID) values (?,?)`; const deptResult = await connection; await db; catcherr db; ; finally db; }
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; // by simply passing array of error codes as 3 parameter of execute query