Canyon Tech Blog
Canyon Tech Blog
Building a multi-tenant API using NestJS, TypeORM and Postgres
⚽

Building a multi-tenant API using NestJS, TypeORM and Postgres

You can contact the author here

☝

This article is a tutorial that shows you a way to implement multi-tenancy on your NestJS application using TypeORM and Postgres. It assumes that you know how NestJS and TypeORM work. It's even better if you are already using these frameworks.

⚽️ The example

ℹ️

To help you understand what comes next, let's imagine a simple SaaS application where each sport team could create, read, update and delete their trainings and games. Each member of the team would be able to see their own team trainings and games information.

To understand our approach to multi-tenancy, let's first see an overview of what would be the "classical" way to build this kind of API.

Then we will see the different options to build a multi-tenants API and finally I'll show you a way to implement this example using multi-tenants architecture.

The classical way

The classical database

image

The classical NestJS project architecture

The classical TypeORM configuration

src/
	- modules/
		- user/
			- user.controller.ts
			- user.service.ts
			- user.entity.ts
			- user.module.ts
		- team/
			- team.controller.ts
			- team.service.ts
			- team.entity.ts
			- team.module.ts
		- game/
			- game.controller.ts
			- game.service.ts
			- game.entity.ts
			- game.module.ts
		...
...
ormconfig.ts

module.exports = {
  type: 'postgres',
  host: process.env.POSTGRES_HOST,
  port: +process.env.POSTGRES_PORT,
  username: process.env.POSTGRES_USERNAME,
  password: process.env.POSTGRES_PASSWORD,
  database: process.env.POSTGRES_DATABASE,
  migrationsRun: true,
  entities: [
    join(__dirname, './modules/**/*.entity{.ts,.js}')
  ],
  migrations: [
    join(__dirname, './migrations/*{.ts,.js}',)
  ]
};

Most of the times you want to store your connection options in a ormconfig.ts.

You then just have to call createConnection() without any configuration passed, and can also manage your migration files easily with the TypeORM CLI.

The classical controllers and services

/*** Controller ***/

import {Get, Controller } from '@nestjs/common';

import { GameEntity } from './game.entity';
import { GameService } from './game.service';

import {
  ApiBearerAuth, ApiTags,
} from '@nestjs/swagger';

@ApiBearerAuth()
@ApiTags('games')
@Controller('games')
export class GameController {

  constructor(private readonly gameService: GameService) {}

  @Get()
  async findAll(
		@Request() { teamId },
	): Promise<GameEntity[]> {
    return await this.gameService.findAllByTeamId(teamId);
  }

}

/*** Service ***/

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

@Injectable()
export class GameService {
  constructor(
    @InjectRepository(GameEntity)
    private readonly gameRepository: Repository<GameEntity>
  ) {}

  findAllByTeamId(teamId): Promise<GameEntity[]> {
    return this.gameRepository.find({ where: { teamId }});
  }

}

The options

image

Identify your tenant

How does your application know which tenant to get data from ? You have several options here. Let's assume or team id is PSG ⚽. Among others you can put your tenant id in:

  • subdomain: https://psg.mysportsaas.com/games
  • url path: https://mysportsaass.com/psg/games
  • header: x-team-id:psg

We are going for the headers option in our example

Store your tenant data

Where to store your tenant data ? Once again you have several options here, such as:

  • one database per tenant
  • one schema per tenant

We are going for one schema per tenant here. The "shared" data such as user, team and membership will be stored in the public schema while the team related data such as game, training and game_participant will be stored in their own schema.

The multi-tenants way

Now that we've chosen among the different options, let's see how we can implement our example API in a multi-tenants way.

The multi-tenants database

Based on our choice to store team related data in a specific schema, our database would look like that:

image

The multi-tenants NestJS project architecture

src/
	- modules/
		- public/
			- user/
				- user.controller.ts
				- user.service.ts
				- user.entity.ts
				- user.module.ts
			- team/
				- team.controller.ts
				- team.service.ts
				- team.entity.ts
				- team.module.ts
		- tenant/
			- game/
				- game.controller.ts
				- game.service.ts
				- game.entity.ts
				- game.module.ts
			...
...
ormconfig.ts
tenant-ormconfig.ts

For a better code understanding, we've created a public and a tenant folder.

Each team related module must be put under the tenant folder. Same logic goes for public related modules 😉

The multi-tenants TypeORM configurations

// ormconfig.ts
module.exports = {
  type: 'postgres',
  host: process.env.POSTGRES_HOST,
  port: +process.env.POSTGRES_PORT,
  username: process.env.POSTGRES_USERNAME,
  password: process.env.POSTGRES_PASSWORD,
  database: process.env.POSTGRES_DATABASE,
  migrationsRun: true,
  entities: [
    join(__dirname, './modules/public/**/*.entity{.ts,.js}')
  ],
  migrations: [
    join(__dirname, './migrations/public/*{.ts,.js}',)
  ]
};

// tenant-ormconfig.ts
module.exports = {
  ...ormconfig,
	schema: 'team_example',
  entities: [
    join(__dirname, './modules/tenant/**/*.entity{.ts,.js}')
  ],
  migrations: [
    join(__dirname, './migrations/tenant/*{.ts,.js}',)
  ]
};

We have two types of connections here, the public one with the public entities and migrations and the tenant one with the team related entities and migrations.

We have specified a schema name in the tenant-ormconfig.ts for migration purposes. See The multi-tenant migrations part for more information.

Connection switching

In Nest, almost everything is shared across incoming requests. We have a connection pool to the database, singleton services with global state, etc.

Remember that Node.js doesn't follow the request/response Multi-Threaded Stateless Model in which every request is processed by a separate thread.

That’s why Nest is using singleton instances.

Usually what you have is a module with a controller and a service. What you do is make your service Injectable and then inject it to you controller constructor so it can use it. When the controller is instantiated, Nest will either create an instance of the service , cache it, and return it, or if one is already cached, return the existing instance.

But we don’t want Singleton in our case, we don’t want to have the same connection for every request. What we want is a dynamic provider which returns the connection according to the team provided in the request header. Luckily NestJS allows you to create a provider dynamically with a REQUEST scope.

That means that the provider lifetime will be equal to the request lifetime.

The multi-tenants connection provider

Here is a dynamic provider that gets the tenant id from the request header and creates a database connection to the related schema.

Connection options are based on the tenant-ormconfig.ts but we also insert the public related entities for cross-schemas joins purposes. More info in Public and tenant relationship part.

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 teamId = req.headers['x-team-id']
    if (teamId) {
      const connectionName = `team_${teamId}`;
      const connectionManager = getConnectionManager();

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

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

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

The multi-tenants services

Now that our dynamic providers exports the connection to schema tenant, we can inject it into our services and use it to interact with the tenant schema.

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

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

  async findAllByTeamId(teamId): Promise<GameEntity[]> {
    return await this.gameRepository.find({ where: { teamId }});
  }

}

Public and tenant schemas relationship

In our example we have a tenant table, game_participant that has a reference to a user id. Now let's say that in some part of our application, we want to get the user information with the game_participant information.

We can do it using the Typeorm relations options or query builder as far as we specified the schema name public on the entity.

@Entity({ schema: 'public' }) class UserEntity {…}

...

return this.gameParticipantRepository.find({ where: { id }, relations: ['user'] });

The other way around (referencing a tenant entity from a public entity) is impossible because we do not know the schemas names in advance.

image

The multi-tenant migrations

Create a migration

To generate migration, TypeORM needs, among other things, the entities list, the migration folder, and the schema name.

As the tenant schemas list is not known in advance, we can't create a migration file that will update all tenant schemas in one shot.

When generating the migration from your local machine, your system is not aware that the PSG schema exists in production and therefore can't create a migration script for this specific schema.

So what we do is to generate tenant schema migrations for a generic tenant schema name (team_example). Then we use a homemade script that replace the generic schema name by a variable, coming from the connection object options, that contains the schema name .

Here is an example of a migration file before and after update:

// Generated migration
public async up(queryRunner: QueryRunner): Promise<any> {
	await queryRunner.query(`CREATE TABLE "team_example"."games" ("id" uuid NOT NULL DEFAULT uuid_generate_v4(), "created_at" TIMESTAMP NOT NULL DEFAULT now(), "updated_at" TIMESTAMP NOT NULL DEFAULT now(), "name" character varying NOT NULL, "description" character varying NOT NULL, CONSTRAINT "PK_698f2da3212a559aa1e331b1723" PRIMARY KEY ("id"))`, undefined);
}

// Updated migration
public async up(queryRunner: PostgresQueryRunner): Promise<any> {
	const { schema } = queryRunner.connection.options as PostgresConnectionOptions;
  await queryRunner.query(`CREATE TABLE "${schema}"."games" ("id" uuid NOT NULL DEFAULT uuid_generate_v4(), "created_at" TIMESTAMP NOT NULL DEFAULT now(), "updated_at" TIMESTAMP NOT NULL DEFAULT now(), "name" character varying NOT NULL, "description" character varying NOT NULL, CONSTRAINT "PK_${schema}_c0c4e97f76f1f3a268c7a70b925" PRIMARY KEY ("id"))`, undefined);
}

Run migrations

The public migrations are run automatically at the application starts thanks to the TypeORM option migrationsRun: true.

But to run the migrations on each tenant schema, we need a more custom way as the tenant schemas list is not known in advance.

One solution is to run the following script when the application starts.

const schemaList = await getManager().query('select schema_name from information_schema.schemata;');
for (let i = 0; i < schemaList.length; i++) {
  const { name: schemaName } = schemaList[i];
  createConnection({
    ...teanantOrmconfig,
    name: schemaName,
    schema: schemaName
  })
	// Migration are run when the connection is opened because of `migrationsRun: true`
  await connection.close();
}