NestJS : database connection (TypeORM) by request (subdomain)

Question

I'm trying to build a SAAS product over Nest/TypeORM and I need to configure/change database connection by subdomain.

customer1.domain.com => connect to customer1 database
customer2.domain.com => connect to customer2 database
x.domain.com => connect to x database

How can I do that ? With interceptors or request-context (or Zone.js) ?

I don't know how to start. Is someone already do that ?

---

WIP : what I am currently doing :

1. add all connections settings into ormconfig file

2. create Middleware on all routes to inject subdomain into res.locals (instance name) and create/warn typeorm connection

   import { Injectable, NestMiddleware, MiddlewareFunction } from '@nestjs/common';
   import { getConnection, createConnection } from "typeorm";
   
   @Injectable()
   export class DatabaseMiddleware implements NestMiddleware {
       resolve(): MiddlewareFunction {
         return async (req, res, next) => {
             const instance = req.headers.host.split('.')[0]
             res.locals.instance = instance
   
             try {
                 getConnection(instance)
             } catch (error) {
                 await createConnection(instance)
             }
   
             next();
         };
       }
   }
   

3. in Controller : get instance name from @Response and pass it to my Service

   @Controller('/catalog/categories')
   export class CategoryController {
       constructor(private categoryService: CategoryService) {}
   
       @Get()
       async getList(@Query() query: SearchCategoryDto, @Response() response): Promise<Category[]> {
         return response.send(
           await this.categoryService.findAll(response.locals.instance, query)
         )
       }
   

4. in Service : get TypeORM Manager for given instance and query database through Repository

   @Injectable()
   export class CategoryService {
     // constructor(
     //   @InjectRepository(Category) private readonly categoryRepository: Repository<Category>
     // ) {}
   
     async getRepository(instance: string): Promise<Repository<Category>> {
         return (await getManager(instance)).getRepository(Category)
     }
   
     async findAll(instance: string, dto: SearchCategoryDto): Promise<Category[]> {
       let queryBuilder = (await this.getRepository(instance)).createQueryBuilder('category')
   
       if (dto.name) {
           queryBuilder.andWhere("category.name like :name", { name: `%${dto.name}%` })
       }
   
       return await queryBuilder.getMany();
     }
   

It seems to work but I not sure about pretty much everything :

• connections poole (how many can I create connections into my ConnectionManager ?)
• pass subdomain into response.locals... bad practice ?
• readability / comprehension / adding lot of additional code...
• side effects : I'm afraid to share connections between several subdomains
• side effects : performance

It's not a pleasure to deals with response.send() + Promise + await(s) + pass subdomain everywhere...

Is there a way to get subdomain directly into my Service ?

Is there a way to get correct subdomain Connection/Repository directly into my Service and Inject it into my Controller ?

Solution 1

I came up with another solution.

I created a middleware to get the connection for a specific tenant:

import { createConnection, getConnection } from 'typeorm';
import { Tenancy } from '@src/tenancy/entity/tenancy.entity';

export function tenancyConnection(...modules: Array<{ new(...args: any[]): 
any; }>) {

  return async (req, res, next) => {

    const tenant = req.headers.host.split(process.env.DOMAIN)[0].slice(0, -1);

    // main database connection
    let con = ...

    // get db config that is stored in the main db
    const tenancyRepository = await con.getRepository(Tenancy);
    const db_config = await tenancyRepository.findOne({ subdomain: tenant });

    let connection;
    try {
       connection = await getConnection(db_config.name);
    } catch (e) {
      connection = await createConnection(db_config.config);
    }

    // stores connection to selected modules
    for (let module of modules) {
      Reflect.defineMetadata('__tenancyConnection__', connection, module);
    }

    next();
  };
}

I added it to the main.ts:

const app = await NestFactory.create(AppModule);
app.use(tenancyConnection(AppModule));

To access the connection you can extend any service by:

export class TenancyConnection {

  getConnection(): Connection {
    return Reflect.getMetadata('__tenancyConnection__', AppModule);
  }
}

It is still a draft, but with this solution you can add, delete and edit the connection for each tenant at runtime. I hope this helps you further.

Solution 2

I got inspired by yoh's solution but I tweaked it a bit according to the new features in NestJS. The result is less code.

1) I created DatabaseMiddleware

import { Injectable, NestMiddleware, Inject } from '@nestjs/common';
import { getConnection, createConnection, ConnectionOptions } from "typeorm";

@Injectable()
export class DatabaseMiddleware implements NestMiddleware {

  public static COMPANY_NAME = 'company_name';

  async use(req: any, res: any, next: () => void) {
    const databaseName = req.headers[DatabaseMiddleware.COMPANY_NAME];

    const connection: ConnectionOptions = {
      type: "mysql",
      host: "localhost",
      port: 3307,
      username: "***",
      password: "***",
      database: databaseName,
      name: databaseName,
      entities: [
        "dist/**/*.entity{.ts,.js}",
        "src/**/*.entity{.ts,.js}"
      ],
      synchronize: false
    };

    try {
      getConnection(connection.name);
    } catch (error) {
      await createConnection(connection);
    }

    next();
  }

}

2) in main.ts use it for every routes

async function bootstrap() {
  const app = await NestFactory.create(AppModule);

  app.use(new DatabaseMiddleware().use);
  ...

3) In service retrieve connection

import { Injectable, Inject } from '@nestjs/common';
import { Repository, getManager } from 'typeorm';
import { MyEntity } from './my-entity.entity';
import { REQUEST } from '@nestjs/core';
import { DatabaseMiddleware } from '../connections';

@Injectable()
export class MyService {
  private repository: Repository<MyEntity>;

  constructor(@Inject(REQUEST) private readonly request) { 
    this.repository = getManager(this.request.headers[DatabaseMiddleware.COMPANY_NAME]).getRepository(MyEntity);
  }

  async findOne(): Promise<MyEntity> {
    return await this.repository
    ...
  }

}

Solution 3

You should use a custom provider with REQUEST scope.

Tenancy provider

import { Global, Module, Scope } from '@nestjs/common';
import { REQUEST } from '@nestjs/core';
import { Connection, createConnection, getConnectionManager } from 'typeorm';

const connectionFactory = {
  provide: 'CONNECTION',
  scope: Scope.REQUEST,
  useFactory: async (req) => {
    const instance = req.headers.host.split('.')[0]
    if (instance) {
      const connectionManager = getConnectionManager();

      if (connectionManager.has(instance)) {
        const connection = connectionManager.get(instance);
        return Promise.resolve(connection.isConnected ? connection : connection.connect());
      }

      return createConnection({
        ...tenantsOrmconfig,
        entities: [...(tenantsOrmconfig as any).entities, ...(ormconfig as any).entities],
        name: instance,
        type: 'postgres',
        schema: instance
      });
    }
  },
  inject: [REQUEST]
};

@Global()
@Module({
  providers: [connectionFactory],
  exports: ['CONNECTION']
})
export class TenancyModule { }

Service class

Then on your services you can get the connection like that:

import { Injectable} from '@nestjs/common';
import { InjectRepository } from '@nestjs/typeorm';
import { Repository } from 'typeorm';
import { GameEntity } from './game.entity';

@Injectable()
export class MyService {
  constructor(
    @Inject('CONNECTION') connection
  ) {
    this.myRepository = connection.getRepository(GameEntity);
  }

  findAll(): Promise<GameEntity[]> {
    return this.myRepository.find();
  }

}

You can get more informations in the following multi-tenants article: https://tech.canyonlegal.com/multitenancy-with-nestjs-typeorm-postgres

Comments

[yoh]

It means that I need to run one node by sudbomain (1 app / port by subdomain) ... I would like to run one node for all subdomains and switch db connection per request.

[WinterTime]

I am getting error if I applied this solution..[[Nest] 43292 - 2019-10-10 04:19:31 [ExceptionsHandler] Connection "default" was not found. +1260ms ConnectionNotFoundError: Connection "default" was not found.]

[michal.jakubeczy]

@WinterTime: you need to set a 'virtual' connection in your app.module.ts like this: @Module({ imports: [ TypeOrmModule.forRoot( { type: "sqlite", database: ":memory:", entities: entities, dropSchema: true, entities: entities, synchronize: true, logging: false, name: name }), CaseModule, CompanyInfoModule, TeamModule, ], }) because connection is determined with each request, but TypeORM needs to have a 'default' connection at the beginning.

[WinterTime]

Tested this solution yesterday, sounds pretty good however in nest you cannot get req.body in Middleware for some reason so I need to have a look if there is any other possibility.

[michal.jakubeczy]

@WinterTime can you send the particular information in HTTP header?

[keinabel]

I was being told it can cause race conditions or other errors to call an async method in constructor. you have a solution for that?

[Thomas Vanderstraeten]

The mentioned article has now been moved to this link: thomasvds.com/…, with some refactos brought along the way (based on the original article by @adrien_om, along with the related Github repository