In one of our previous articles, we described the principles of working with multiple databases. Then, we used NestJS and TypeORM to demonstrate how to implement the required functionality and not use the wrong connections while saving the data. But there’s another challenge developers face working with simultaneous access to multiple data sources driven by the need to ensure its integrity throughout all databases.
You must not underestimate the importance of this issue since it can have significant business implications. For instance, your web application serves many organizations interacting with each other for event arrangements. Each organization’s data is stored in a separate database for security reasons. An employee from Company A fills in a web form to make a complex order to rent cars from Company B and book catering from Company C. The order submission leads to an issue. Catering data is not saved in the system. The employee changes data, then clicks the Submit button, and this time it is saved successfully. However, Company B receives two orders for car renting. Thus Company A might be invoiced twice. Undoubtedly, you will receive complaints, and your business reputation will be tarnished due to how your web application functions.
When you deal with a single database, everything’s pretty straightforward, and begin, commit, and rollback are enough to make everything work. In this case, web app development is a piece of cake. Working with data saved in several distributed databases requiring simultaneous connection is much trickier. In this scenario, there’s no other choice but to begin transactions to each database, keep transactions open until all data is updated, and only then commit in all databases. But if some data is not saved to one of these databases due to any issues, we must rollback all opened transactions. Otherwise, you will face some severe data integrity issues.
At this point, the importance of transaction processing must be pretty straightforward. Now, it is time to demonstrate how to implement this feature efficiently with TypeORM.
How TypeORM Handles Transactions
Let’s look at how we can implement a transaction using TypeORM. There are two options for doing it. We can use the DataSource function or QueryRunner. Notice that the code in this article uses TypeORM version 0.3.7.
Read Also The Best Time to Start Learning is Now. Five Full-stack Projects to Improve Your Skills in 2023
First, let’s look at how the DataSource function works:
|
1 2 3 |
await customDataSource.transaction(async (transactionEntityManager) => { await transactionEntityManager.save(users); }); |
In this case, we can use one function, and TypeORM will do the rest of the work. The main advantage of this approach is that we only need a single function to reach our goal. The disadvantages are that we do not have complete control over the ongoing transaction, and such a transaction applies to only one DataSource at a time.
Now, let’s look at transactions implementation with QueryRunner:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 |
const queryRunner = customDataSource.createQueryRunner(); await queryRunner.connect(); await queryRunner.startTransaction(); try { await queryRunner.manager.save(users); await queryRunner.commitTransaction(); } catch (err) { await queryRunner.rollbackTransaction(); } finally { await queryRunner.release(); } |
Here, we are using the QueryRunner functions to implement the transaction. Compared to the previous example, the advantages of this approach are that we control the transaction process and can also handle errors. The downsides are that we need more lines of code, and we should always keep transaction management in mind.
To implement transactions with multiple databases, we will use the QueryRunner option.
Implementing Multi-database Simultaneous Transactions
To implement work with several simultaneous transactions, we will create the following function:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 |
async function transaction<T>( dataSources: DataSource[], tryBlock: (...queryRunners: QueryRunner[]) => Promise<T>, catchBlock?: () => void ): Promise<T> { const queryRunners: QueryRunner[] = []; for (const ds of dataSources) { if (ds) { const qr = ds.createQueryRunner(); await qr.connect(); await qr.startTransaction(); queryRunners.push(qr); } } try { const result = await tryBlock(...queryRunners); for (const qr of queryRunners) { await qr.commitTransaction(); } return result; } catch (err) { await catchBlock?.(); for (const qr of queryRunners) { await qr.rollbackTransaction(); } throw err; } finally { for (const qr of queryRunners) { await qr.release(); } } } |
Let’s analyze this code to understand what’s going on here better.
The function takes three parameters: a list of used DataSources, a function to be executed (including actions with tables), and an optional function that runs when an error appears.
Next, we use a for loop that iterates through all the DataSources and creates the corresponding QueryRunners. Also, in this loop, we form an array of created QueryRunners.
This code is followed by a try block that calls the passed function. The result of the passed function is stored in a variable called result and will be returned from the original function. A for loop iterates through all QueryRunners and calls the commitTransaction function.
Next comes the catch block. It triggers if any error occurs while executing the commitTransaction function. Here, the passed function for the catch block is executed. It also starts a for loop that iterates through all the QueryRunners and calls the rollbackTransaction function.
Also, we need to execute the release function for our QueryRunners, so in the finally block, we run a for loop where we call the release function for all our QueryRunners.
Read Also Figuring Out What’s Under the Hood. Main Types of Database Management Systems
Thus, the method we created allows us to perform many transactions to different databases and handle errors if they occur.
Function Usage Example
Let’s give an example of using the function we created in practice.
Suppose our task is to store general user data in one database and put confidential data in another. We also need to save the user’s avatar in some file storage. The code for implementing this feature can look like this:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 |
let path: string; const savedUserData = await transaction<MyUserEntity>( [firstDBDataSource, secondDBDataSource], async (firstDBQueryRunner, secondDBQueryRunner) => { path = await fileManager.uploadFile(avatar); user.avatar = path; const dbuser = await firstDBQueryRunner.manager.save(user); await secondDBQueryRunner.manager.save(userConfidentialData); return dbuser; }, async () => { await fileManager.removeFile(path); } ); return savedUserData; |
Here, we took two DataSources belonging to the databases we need. Based on the DataSources taken, the function created two QueryRunners. In the function passed as the second parameter, we used the created QueryRunners and saved the file and data. If the task fails, the function passed as the third parameter will be executed. Inside it, we will delete the uploaded file since it will not be automatically deleted during transaction rollback.
Conclusions
Using this function, you can easily start several simultaneous transactions that will be executed or rolled back if necessary. To improve the code readability, you can modify the TypeORM functions by adding an optional parameter for EntityManager. In this scenario, the save function can be written like this:
|
1 |
await customRepository.save(user, firstDBQueryRunner.manager); |
Contact us if you’re looking for an experienced development team that can build a web application with both an appealing UI and a reliable backend.