How to Implement Multiple Queries Transactions with Node.JS & MySQL
Writing MySQL transactions in NodeJS & TypeScript
What Are MySQL transactions
Transactions solve a very common and particular problem when there is more that one source or thread reading or writing to a database. The problem in question is called dirty reading and writing. For those of you who aren’t familiar with this, imagine the following code at an ATM (bank).
Note: Jeff and Sarah are a couple with a joint account.
- Jeff – Puts his card into the ATM and reads a balance of £100
- Sarah – Puts her card into the ATM and reads a balance of £100
- Jeff – Withdraws £30 from the ATM and gets a balance displayed of £70
- Sarah – Balance still reads £100 as her ATM doesn’t know Jeff has withdrawn money
- Sarah – Withdraws £50 and now has a balance of £50.
So clearly they just cheated the system and made £20 in the process! But in reality this doesn’t happen. How? In a nutshell, only 1 person is allowed to check their balance, withdraw, make a purchase .. etc, at a time.
How does the transaction look on the model above?
- Jeff – Puts his card into the ATM and reads a balance of £100
- Sarah – Puts her card into the ATM and is told to wait
- Jeff – Withdraws £30 from the ATM and gets a balance displayed of £70
- Sarah – Now is allowed to check her balance, which is shown as £70
- Sarah – Withdraws £50 and now has a balance of £20
Though there is vastly more that goes on behind the scenes, this is the crux of how bank transactions operate.
MySQL Connection
Before we get into transactions, lets just make a quick NodeJS connection to a MySQL Database with the standard MySQL engine you can find in NPM
Note: We are using typescript for this.
import { Connection, createConnection, FieldInfo, MysqlError} from 'mysql';
export class MySQLController {
private readonly connection: Connection;
constructor() {
try {
this.connection = createConnection({
host: 'localhost',
user: 'root',
password: 'awesomepassword',
database: 'mydatabase',
});
this.connection.connect((error: MysqlError) => {
if (error !== null) {
throw new Error('error connecting: ' + error.stack);
}
return true;
});
} catch (error) {
throw new Error(error);
}
}
}
There’s nothing fancy going on there.
- Importing the parts of the MySQL library that we want
- Creating a controller class
- Try to create a new connection with host, user, password and database credentials
- Connect to the database with the connection
- Handle any errors
Example transaction
Lets just take a peak at what a normal transaction inside a function looks like.
public async insertOneRecord(name: string, age: number): Promise<any> {
return new Promise((resolve, reject) => {
this.connection.beginTransaction((beginTransactionError: MysqlError) => {
if(beginTransactionError != null) {
reject(beginTransactionError.message);
}
this.connection.query('INSERT INTO user SET name = ?, age = ?', [name, age], (queryError: MysqlError | null, results: any, fields: FieldInfo[] | undefined) => {
if(queryError != null) {
this.connection.rollback((rollbackError: MysqlError) => {
if(rollbackError != null) {
reject(rollbackError.message);
} else {
reject(queryError.message);
}
});
} else {
this.connection.commit((commitError: MysqlError) => {
if (commitError != null) {
reject(commitError.message);
}
resolve(results);
});
}
});
});
});
}Most of the code should be fairly self explanatory here. We’re just returning a new promise and declaring it as an async function so we can use the await function. The problem with this function is you would have to duplicate it for every query you wanted to run. So lets make the transaction ambiguous to any query with any number of parameters.
public async insertOneRecord(query: string, parameters: Array<string | number | boolean | JSON>): Promise<any> {
...
this.connection.query(query, parameters, (queryError: MysqlError | null, results: any, fields: FieldInfo[] | undefined) => {
...We only need to change those two lines. Accepting a query which is of type string, and parameters which is an array which can contain a string, number, boolean or JSON. For more details on typescript types, you can check out my overview.
If we want to perform the same as we did before, then you can do this:
const result = MySQLController.insertOneRecord('INSERT INTO user SET name = ?, age = ?', ['nick', 1]);Multiple Transactions
Following the GitHub readme for the MySQL library, we can see it’s easy enough to have multiple queries within a single transaction.
public async insertTwoRecord(name1: string, name2: string, age1: number, age2: number): Promise<any> {
return new Promise((resolve, reject) => {
this.connection.beginTransaction((beginTransactionError: MysqlError) => {
if (beginTransactionError != null) {
reject(beginTransactionError.message);
}
this.connection.query('INSERT INTO user SET name = ?, age = ?', [name1, age1], (queryError1: MysqlError | null, results1: any, fields1: FieldInfo[] | undefined) => {
if (queryError1 != null) {
this.connection.rollback((rollbackError: MysqlError) => {
if (rollbackError != null) {
reject(rollbackError.message);
} else {
reject(queryError1.message);
}
});
this.connection.query('INSERT INTO user SET name = ?, age = ?', [name2, age2], (queryError2: MysqlError | null, results2: any, fields2: FieldInfo[] | undefined) => {
if (queryError2 != null) {
this.connection.rollback((rollbackError: MysqlError) => {
if (rollbackError != null) {
reject(rollbackError.message);
} else {
reject(queryError1.message);
}
});
} else {
this.connection.commit((commitError: MysqlError) => {
if (commitError != null) {
reject(commitError.message);
}
resolve([results1, results2]);
});
}
});
}
});
});
});
}The above function is basically the same as the previous single transaction by way of:
- Begin transaction
- Run query 1
- Check for errors
- Run query 2
- Check for errors
- Commit
It is not difficult to see that a single transaction to run two queries is rather lengthly. Imagine what it would look like if you had 5 or 10 queries to run!
N Queries (for loop)
The easiest way to solve this is by having a function that runs a transaction on an undefined number queries and returning all the results. A for lop is going to be our friend here!
public async executeTransactions(queries: Array<{ id: number, query: string, parameters: Array<string | number | boolean | JSON> }>): Promise<{ [id: string]: any }> {
return new Promise(async (resolve) => {
try {
const results: { [id: string]: { result: any, fields: FieldInfo[] | undefined} } = {};
this.connection.beginTransaction((beginTransactionError: MysqlError) => {
if (beginTransactionError !== null) {
reject(beginTransactionError.message);
}
// Loop through all queries
for (const query of queries) {
this.connection.query(query.query, query.parameters, (queryError: MysqlError | null, queryResults: any, fields: FieldInfo[] | undefined) => {
// If the query errored, then rollback and reject
if (queryError !== null) {
// Try catch the rollback end reject if the rollback fails
try {
this.connection.rollback((rollbackError: MysqlError) => {
reject(rollbackError.message);
});
} catch (rollbackError) {
reject(rollbackError.message);
}
}
// Push the result into an array and index it with the ID passed for searching later
results[query.id] = {
result: queryResults,
fields: fields,
};
});
}
// If all loops have itterated and no errors, then commit
this.connection.commit((commitError: MysqlError) => {
if (commitError !== null) {
reject(commitError.message);
}
resolve(results);
});
});
} catch (error) {
reject(error);
}
});
}The above function takes an array of query objects. These objects contain an ID (for referencing later), a query string and an array of parameters. It will look through each object in the array and perform a connection query within the transaction.
Should any query fail or crash then the whole transaction will rollback at that point and that specific error will be generated and rejected.
Calling the function to run N queries would look something like this:
const results = await this.executeTransactions([
{
id: 'insert1',
query: 'INSERT INTO user SET name = ?, age = ?',
parameters: ['nick', 31],
},
{
id: 'insert2',
query: 'INSERT INTO user SET name = ?, age = ?',
parameters: ['jeff', 1798],
},
{
id: 'select',
query: 'SELECT * FROM user WHERE id = ?',
parameters: [1],
},
{
id: 'delete',
query: 'DELETE FROM user WHERE name = ?',
parameters: ['jeff'],
},
]);The results object would look a little something like this:
{
"insert1" : {
result: [...],
fields: [...]
},
"insert2": {
result: [...],
fields: [...]
},
.....
}That about wraps it up for this article on how to write N queries within a single transaction in MySQL and TypeScript.
