What is the correct way to handle multi-db in nestjs with TypeORM?

Question

I have two databases: a master database, which serves as a common database, and another database (dedicated to each tenant/customer) with the same schema. If you have experience with a multi-database approach, this setup should be familiar to you.

Currently, I create a new connection for each request like this:

async getDbConnForUser() {
    // Retrieve user from request context
    const user = this.utilityService.getUserFromRequestContext();
    if (!user) throw new UnauthorizedException('Invalid User');

    subdomain = user.subdomain;
    // Fetch all subdomains the user has access to
    const userSubdomains = await this.tenantService.findUserAndSubdomainsByEmail(user.email);
    if (userSubdomains.length === 0) throw new UnauthorizedException('Invalid User');

    if (!userSubdomains.find(ud => ud.subdomain === user.subdomain)) {
        // This should never occur!
        throw new UnauthorizedException('Forbidden Access');
    }

    const tenant = await this.tenantService.getMasterTenantBySubdomain(subdomain);
    if (!tenant) throw new NotFoundException('Tenant not found');

    const newDataSource = new DataSource({
        type: 'postgres',
        host: this.configService.get('TENANT_DB_HOST'),
        port: this.configService.get('DB_PORT'),
        username: this.configService.get('TENANT_DB_USERNAME'),
        password: this.configService.get('TENANT_DB_PASSWORD'),
        database: tenant.dbName,
        entities: [join(process.cwd(), 'dist/**/tenant/*.entity.js')],
        subscribers: [join(process.cwd(), 'dist/**/subscribers/*.subscriber.js')],
        synchronize: true // To be removed in production
    });

    await newDataSource.initialize();

    return newDataSource;
}

For example, I use this method to retrieve tenant-specific data in my service as follows:

async getDivisionsByCompany(companyId: number) {
    const dataSource = await this.databaseService.getDbConnForUser();
    const divisionRepo = dataSource.getRepository(Division);

    const divisions = await divisionRepo.find({
        where: { companyId: companyId, isActive: true },
    });

    return divisions;
}

I'm seeking advice on whether this approach is optimal, as it establishes a new connection for each request. Are there better ways to handle multi-tenant databases without creating a new connection each time? Any insights or recommendations would be greatly appreciated.

Simple way, just use cache to save list Datasource to avoid create new connection. But I have question that What is injections scope of databaseService, DEFAULT , REQUEST or TRASISENT ? — Doan Thai
– Doan Thai, Commented Oct 8, 2024 at 5:15
@DoanThai You mean like Redis to save Datasource? Also, I haven't specified any scope explicitly so I think it will be DEFAULT? — mubashir
– mubashir, Commented Oct 8, 2024 at 14:13
At here, cache simple just is global variable or singleton for saving list Datasource for each database by name. — Doan Thai
– Doan Thai, Commented Oct 8, 2024 at 15:17
If you just use @Injectable so don't worry about scope. But be careful withREQUEST scope, it will create every providers inject it for each. It make your request more slow. — Doan Thai
– Doan Thai, Commented Oct 8, 2024 at 15:21

Hai Alison · Accepted Answer · 2024-10-07 02:26:57Z

1

my case is create a function like you to init a new connection but I check

 const connection = new DataSource(configs);
    if (!connection.isInitialized) await connection.initialize();
    console.log('isInitialized', connection.isInitialized);
    return connection;

to avoid that connection re-initialize

answered Oct 7, 2024 at 2:26

Hai Alison

1,4651 gold badge9 silver badges20 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

What is the correct way to handle multi-db in nestjs with TypeORM?

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related