Working with Multiple Databases Simultaneously Using NestJS and TypeORM
Developing a web application, you are always concerned about data. The major issues are how to manage it efficiently and store it securely. The problem with data may become even more significant if your web application handles many organizations. Here, the data volumes are more extensive, and the importance of data security reaches higher levels. And we didn’t even mention the issues coming from differences in activities and the amount of data between organizations. In some simple cases, you can solve them by applying sharding or partitioning, but if you’re looking for absolute control, working with multiple databases can help.
A solution we provide here may use multiple databases to split data per organization or per functionality:
- Splitting data per organization. Here, each entity will deal only with its own data stored in separate databases (aka servers) or even on a server provided by an organization;
- Splitting data per functionality. In this case, you can pick up more suitable data storage in terms of efficiency, reliability, and security. For instance, you can use several RDBMSs for different data chunks. One of them may keep data requiring increased security. The second must be closer to data sources (as you have various IoT devices). The third chunk ensures better reliability. You may also need the fourth one for performance reasons, and so on. This approach allows you to choose what’s best for each feature of your application and help to improve performance.
With this approach, you improve the security of your application as well. Sometimes even the most experienced developers can’t save you from various problems. Even the most protected hosting may be subject to hacker attacks, data leakages, emergency shutdowns, or crashes. In such cases, you must ensure that the trouble has not affected all your data and that non-affected application functionalities are running correctly while you handle the local problem.
To this point, everything looks great, but the development team faces multiple challenges, such as data synchronization and data integrity, when implementing the project. Moreover, there is this one database to rule them all that adds additional complexity. It is called a “system database” and is responsible for all interactions between the application components. Surely you don’t want an unexpected data update to ruin that precious KPI formula that helps count everyone’s annual bonus.
We suggest using the approach described below to keep the sanity of the development team building such an application. It’s not the only possible solution, but we use this approach in our projects, and no developers have been harmed so far.
Database Module Implementation
In one of our projects, we faced the following tasks:
- Implement functionality to connect to a specific database;
- Define the database for a user according to a subdomain;
- Connect to the database;
- Perform the necessary actions with user data.
To build these features and make the application work with multiple databases, you can use NestJS and TypeORM. This combination works well with any database management system (MySQL, PostgreSQL, MongoDB, or any other) thanks to TypeORM compatibility features.
Notice that the code provided in this article uses the following libraries: NestJS version 9.0.4 and TypeORM version 0.3.7.
Read Also What is a Tech Stack and How to Choose the Right One for Web Application Development [2023 Update]
To make a web app work with multiple databases, we can start with building a database connection manager because the version of the TypeORM we are using does not have one. For this, let’s create a folder called database and a file called database.util.ts inside it. Then, we can use the following code in this file:
import { DataSource } from 'typeorm';
export default class DataSourceManager {
private static instance: DataSourceManager;
private dataSources: { [key: string]: DataSource };
private constructor() {
this.dataSources = {};
}
public static getInstance(): DataSourceManager {
if (!DataSourceManager.instance) {
DataSourceManager.instance = new DataSourceManager();
}
return DataSourceManager.instance;
}
async getDBDataSource(dataSourceName: string): Promise<DataSource> {
if (this.dataSources[dataSourceName]) {
const dataSource = this.dataSources[dataSourceName];
return Promise.resolve(dataSource.isInitialized ? dataSource : dataSource.initialize());
}
const newDataSource = new DataSource({
...your connection config
});
this.dataSources[dataSourceName] = newDataSource;
return Promise.resolve(newDataSource.initialize());
}
}We will also create a service with functions helping us to determine necessary connections. In the same folder, let’s create a file called database.service.ts with the following code:
import { Injectable } from '@nestjs/common';
import { DataSource } from 'typeorm';
import DataSourceManager from './database.util';
@Injectable()
export class DatabaseService {
async getDBDataSource(dataSourceName: string): Promise<DataSource> {
return DataSourceManager.getInstance().getDBDataSource(dataSourceName);
}
// get database name by subdomain
async getDatabaseName(subdomain: string): Promise<string> {
const databaseName = ...getting dbname;
return databaseName;
}
}Now that we have a connection manager and a database service, we need to create a database provider. We can inject it into services to define the database we need to work with.
Let’s create a file called database.provider.ts in the database folder and use the following code:
import { Provider, Scope } from '@nestjs/common';
import { ConfigService } from '@nestjs/config';
import { REQUEST } from '@nestjs/core';
import { DataSource } from 'typeorm';
import { DatabaseService } from './database.service';
export const dataSourceFactory: { [key: string]: Provider } = {
[Scope.DEFAULT]: {
provide: 'data_source',
useFactory: (): null => {
return null;
}
},
[Scope.REQUEST]: {
provide: 'data_source',
inject: [REQUEST, ConfigService, DatabaseService],
scope: Scope.REQUEST,
useFactory: async (
req,
configService: ConfigService,
databaseService: DatabaseService
): Promise<DataSource | null> => {
let instance = ...getting subdomain;
// we don't need a specific db if the url doesn't have a subdomain
if (instance === req.hostname) {
instance = null;
}
if (instance) {
const databaseName = await databaseService.getDatabaseName(instance);
return databaseService.getDBDataSource(databaseName);
}
}
}
};Scope definition is necessary in this case. Suppose we need to create an instance of a dynamic module and the connection to a specific database is not needed, or we only need functions from the database service, for example. Here, there will be no need to create new unnecessary instances for the database module. You should consider this feature since the connection to a specific database uses a request scope, meaning that the entire import chain will receive a request scope as well.
Now we need to create a database module to put everything together. Let’s create a file called database.module.ts in the database folder and fill it with the following code:
import { DynamicModule, Module, Scope } from '@nestjs/common';
import { ConfigModule } from '@nestjs/config';
import { dataSourceFactory } from './database.provider';
import { DatabaseService } from './database.service';
/**
* Dynamic Database Module. Use register(scopeType) to import this module
*/
@Module({})
export class DatabaseModule {
static register(scopeType: Scope): DynamicModule {
return {
module: DatabaseModule,
imports: [ConfigModule],
providers: [dataSourceFactory[scopeType], DatabaseService],
exports: ['data_source', DatabaseService]
};
}
}Now we are all set to connect to any database we want.
Connecting to a Specific Database
First, we will connect the database module to the module in which we want to implement the connection to the database.
For this purpose, let’s create a file called my.module.ts in the core folder of our project and add the following code to it:
import { Module } from '@nestjs/common';
import { Scope } from '@nestjs/typeorm';
import { DatabaseModule } from './database/database.module';
import { MyEntity } from './my.entity.ts';
import { MyService } from './my.service.ts';
import { MyController } from './my.controller.ts';
@Module({
imports: [
TypeOrmModule.forFeature([MyEntity]),
DatabaseModule.register(Scope.REQUEST)
],
providers: [MyService],
controllers: [MyController]
})
export class MyModule {}Now, we can connect to the database and start working with it. In the same folder, let’s create a file called my.service.ts with the following code:
import { Inject, Injectable } from '@nestjs/common';
import { Repository, DataSource } from 'typeorm';
import { MyEntity } from './my.entity.ts';
@Injectable()
export class MyService {
private readonly repository: Repository<MyEntity>;
constructor(
@Inject(consts.DATA_SOURCE)
private readonly userRelatedDataSource: DataSource
) {
this.repository = userRelatedDataSource.getRepository(MyEntity);
}
async findData(): Promise<any[]> {
return this.repository.find();
}
}We will also need to create a file called my.controller.ts. Here’s the code it must contain:
import { Controller, Get } from '@nestjs/common';
import { MyService } from './my.service.ts';
@Controller()
export class MyController {
constructor(private readonly myService: MyService) {}
@Get()
async data(): Promise<any[]> {
return this.myService.findData();
}
}At last, everything is ready to connect to a specific database according to the subdomain we receive in the request. Let’s take a look at how everything works in practice. For example, we receive a request with a URL that looks like this: https://mysub.example.com/
This request is sent to the MyController, where the MyService instance is created. When creating a MyService instance, the database provider is called. This provider, in its turn, follows the scope (in our case, Scope.REQUEST) to determine the database according to the subdomain and returns a ready-made connection. After that, all we have to do is to get the repository of the table we need directly in MyService.
Check full project source code.
Conclusion
Everything described above is a basic example of working with multiple databases. To improve the code, you can create a separate method for transactions and an abstraction for working with repositories through QueryRunner. Such a technique will allow you to commit and roll back changes in several databases simultaneously.
You can also use a database service to get multiple database connections in one place. It can be helpful, for example, when running cron tasks to change any status in several databases at once and, by the way, Scope.DEFAULT is pretty helpful here since cron does not work with Scope.REQUEST.
Contact us if you’re looking for a team of expert web developers who will treat your sensitive data as carefully as their own.
