TypescriptDatabaseNode JSnode.js

Transactions with multiple queries NodeJS 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.

Tags

Nicholas Mordecai

Just your friendly neighbourhood programmer!

Related Articles

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Check Also

Close
Close
Close