NestJS：通过请求（子域）连接数据库（TypeORM）

Question

我正在尝试在Nest/TypeORM上构建一个SAAS产品，我需要通过子域配置/更改数据库连接。

我正在尝试在Nest/TypeORM上构建一个SAAS产品，我需要通过子域来配置/更改数据库连接。

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

我该如何做？使用拦截器还是请求上下文（或Zone.js）？

我不知道该如何开始。有人已经这样做了吗？

---

正在进行中：我目前在做什么：

1. 将所有连接设置添加到ormconfig文件中

2. 在所有路由上创建中间件，将子域名注入res.locals（实例名称），并创建/警告typeorm连接

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. 在控制器(Controller)中：从@Response获取实例名称并将其传递给我的服务(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. 在服务中：为给定的实例获取TypeORM Manager并通过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();
  }

看起来它可以工作，但我对很多东西不确定：

• 连接池（我可以在我的ConnectionManager中创建多少个连接？）
• 将子域名传递到response.locals…是否是不良实践？
• 可读性/理解性/添加了大量额外的代码…
• 副作用：我担心在几个子域之间共享连接
• 副作用：性能

使用response.send() + Promise + await(s) + 到处传递子域名并不愉快…

有没有一种方法可以直接在我的服务中获取子域名？

有没有一种方法可以直接获取正确的子域连接/存储库，并将其注入我的控制器中？

- yoh

---

你应该使用环境变量进行配置，定义在启动节点服务器之前，例如：DOMAIN=customer1.domain.com node server.js（如果你在Linux上）。在代码中使用 process.env.DOMAIN。 - Victor Ivens

这意味着我需要通过子域名运行一个节点（每个子域名1个应用程序/端口）... 我想为所有子域名运行一个节点，并在每个请求中切换数据库连接。 - yoh

Answer 1

我来想出了另一个解决方案。 我创建了一个中间件来获取特定租户的连接：

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();
  };
}

我将其添加到了 main.ts 文件中：

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

要访问连接，您可以通过扩展任何服务来实现：

export class TenancyConnection {

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

这还是一个草案，但是通过这个解决方案，您可以在运行时添加、删除和编辑每个租户的连接。希望这能更好地帮助您。

- unknown

---

你好，TenancyConnection是控制器还是其他什么东西？ - Ritesh Khatri

Answer 2

你应该使用一个REQUEST作用域的自定义提供程序。

租户提供程序

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 { }

服务类

然后在您的服务中，您可以像这样获取连接：

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();
  }

}

您可以在以下多租户文章中获取更多信息：https://tech.canyonlegal.com/multitenancy-with-nestjs-typeorm-postgres

- adrien_om

---

该文章现已移至以下链接：https://thomasvds.com/schema-based-multitenancy-with-nest-js-type-orm-and-postgres-sql/，在此过程中进行了一些重构（基于@adrien_om的原始文章以及相关的Github存储库）。 - Thomas Vanderstraeten

Answer 3

我受到yoh解决方案的启发，但根据NestJS中的新功能进行了一些调整。结果代码更少。

1）我创建了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) 在main.ts中为每个路由使用它

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

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

3) 在服务中检索连接

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
    ...
  }

}

- michal.jakubeczy

---

如果我应用这个解决方案，就会出现错误..[[Nest] 43292 - 2019-10-10 04:19:31 [ExceptionsHandler] 找不到连接"default"。+1260ms ConnectionNotFoundError: 找不到连接"default"。] - WinterTime

@WinterTime：你需要在app.module.ts中设置一个“虚拟”连接，像这样：@Module({ imports: [ TypeOrmModule.forRoot( { type: "sqlite", database: ":memory:", entities: entities, dropSchema: true, entities: entities, synchronize: true, logging: false, name: name }), CaseModule, CompanyInfoModule, TeamModule, ], })因为每个请求都会确定连接，但TypeORM需要在开始时有一个“默认”连接。 - michal.jakubeczy

昨天测试了这个解决方案，听起来非常不错。然而在嵌套中你无法在中间件中获取req.body，所以我需要看看是否有其他可能性。 - WinterTime

@WinterTime，你能把特定的信息发送到HTTP头中吗？ - michal.jakubeczy

我被告知在构造函数中调用异步方法可能会导致竞态条件或其他错误。你有解决方案吗？ - keinabel

Answer 4

最好的方法是使用动态模块，就像您在请求范围内使用的一样，来获取 ORM 连接并使其连接特定。 一个非常简单的示例可以是这样的：

const tenancyFactory: Provider = {
    provide: NEST_MYSQL2_TENANCY,
    scope: 'REQUEST',
    useFactory: async (mysql: Mysql, options: MysqlTenancyOption, req: Request): Promise<any> => {
        console.log("TENANCY FACTORY");

        const executer = function (mysqlPool: Mysql): MysqlExecuter {
            return {
                db: function (dbName: string): MysqlRunner {
                    return {
                        run: async function (sqlString: string) {
                            const q = `\nUSE ${dbName};\n` +
                                sqlString.replace("; ", ";\n");
                            if (options.debug) {
                                tLogger.verbose(q);
                            }
                            const [[_, ...queryResult], __] = await mysqlPool.query(q)
                            return queryResult as any;
                        }
                    }
                }
            }
        }
        return executer(mysql);
    },
    inject: [NEST_MYSQL2_CONNECTION, NEST_MYSQL2_TENANCY_OPTION],
};

@Global()
@Module({
    providers: [tenancyFactory],
    exports: [tenancyFactory],
})
export class MultiTenancyModule {
    constructor(

    ) { }
    public static register(options: MysqlTenancyOption): DynamicModule {
        return {
            module: MultiTenancyModule,
            providers: [{
                provide: NEST_MYSQL2_TENANCY_OPTION,
                useValue: options
            }]
        };
    }
}

在这个示例中，我使用了mysql2-nestjs模块，但您可以使用自己的ORM来创建tenancyFactory。

您可以在以下链接中找到此示例的工作解决方案： https://github.com/golkhandani/multi-tenancy/blob/main/test/src/tenancy.module.ts

- MohamadrezaRahimianGolkhandani

Answer 5

我为nest-mongodb编写了一个解决方案，希望您能查看并从中获益。

类似的问题请参考https://dev59.com/y7Pma4cB1Zd3GeqPlBhC#57842819

import {
    Module,
    Inject,
    Global,
    DynamicModule,
    Provider,
    OnModuleDestroy,
} from '@nestjs/common';
import { ModuleRef } from '@nestjs/core';
import { MongoClient, MongoClientOptions } from 'mongodb';
import {
    DEFAULT_MONGO_CLIENT_OPTIONS,
    MONGO_MODULE_OPTIONS,
    DEFAULT_MONGO_CONTAINER_NAME,
    MONGO_CONTAINER_NAME,
} from './mongo.constants';
import {
    MongoModuleAsyncOptions,
    MongoOptionsFactory,
    MongoModuleOptions,
} from './interfaces';
import { getClientToken, getContainerToken, getDbToken } from './mongo.util';
import * as hash from 'object-hash';

@Global()
@Module({})
export class MongoCoreModule implements OnModuleDestroy {
    constructor(
        @Inject(MONGO_CONTAINER_NAME) private readonly containerName: string,
        private readonly moduleRef: ModuleRef,
    ) {}

    static forRoot(
        uri: string,
        dbName: string,
        clientOptions: MongoClientOptions = DEFAULT_MONGO_CLIENT_OPTIONS,
        containerName: string = DEFAULT_MONGO_CONTAINER_NAME,
    ): DynamicModule {

        const containerNameProvider = {
            provide: MONGO_CONTAINER_NAME,
            useValue: containerName,
        };

        const connectionContainerProvider = {
            provide: getContainerToken(containerName),
            useFactory: () => new Map<any, MongoClient>(),
        };

        const clientProvider = {
            provide: getClientToken(containerName),
            useFactory: async (connections: Map<any, MongoClient>) => {
                const key = hash.sha1({
                    uri: uri,
                    clientOptions: clientOptions,
                });
                if (connections.has(key)) {
                    return connections.get(key);
                }
                const client = new MongoClient(uri, clientOptions);
                connections.set(key, client);
                return await client.connect();
            },
            inject: [getContainerToken(containerName)],
        };

        const dbProvider = {
            provide: getDbToken(containerName),
            useFactory: (client: MongoClient) => client.db(dbName),
            inject: [getClientToken(containerName)],
        };

        return {
            module: MongoCoreModule,
            providers: [
                containerNameProvider,
                connectionContainerProvider,
                clientProvider,
                dbProvider,
            ],
            exports: [clientProvider, dbProvider],
        };
    }

    static forRootAsync(options: MongoModuleAsyncOptions): DynamicModule {
        const mongoContainerName =
            options.containerName || DEFAULT_MONGO_CONTAINER_NAME;

        const containerNameProvider = {
            provide: MONGO_CONTAINER_NAME,
            useValue: mongoContainerName,
        };

        const connectionContainerProvider = {
            provide: getContainerToken(mongoContainerName),
            useFactory: () => new Map<any, MongoClient>(),
        };

        const clientProvider = {
            provide: getClientToken(mongoContainerName),
            useFactory: async (
                connections: Map<any, MongoClient>,
                mongoModuleOptions: MongoModuleOptions,
            ) => {
                const { uri, clientOptions } = mongoModuleOptions;
                const key = hash.sha1({
                    uri: uri,
                    clientOptions: clientOptions,
                });
                if (connections.has(key)) {
                    return connections.get(key);
                }
                const client = new MongoClient(
                    uri,
                    clientOptions || DEFAULT_MONGO_CLIENT_OPTIONS,
                );
                connections.set(key, client);
                return await client.connect();
            },
            inject: [getContainerToken(mongoContainerName), MONGO_MODULE_OPTIONS],
        };

        const dbProvider = {
            provide: getDbToken(mongoContainerName),
            useFactory: (
                mongoModuleOptions: MongoModuleOptions,
                client: MongoClient,
            ) => client.db(mongoModuleOptions.dbName),
            inject: [MONGO_MODULE_OPTIONS, getClientToken(mongoContainerName)],
        };

        const asyncProviders = this.createAsyncProviders(options);

        return {
            module: MongoCoreModule,
            imports: options.imports,
            providers: [
                ...asyncProviders,
                clientProvider,
                dbProvider,
                containerNameProvider,
                connectionContainerProvider,
            ],
            exports: [clientProvider, dbProvider],
        };
    }

    async onModuleDestroy() {
        const clientsMap: Map<any, MongoClient> = this.moduleRef.get<
            Map<any, MongoClient>
        >(getContainerToken(this.containerName));

        if (clientsMap) {
            await Promise.all(
                [...clientsMap.values()].map(connection => connection.close()),
            );
        }
    }

    private static createAsyncProviders(
        options: MongoModuleAsyncOptions,
    ): Provider[] {
        if (options.useExisting || options.useFactory) {
            return [this.createAsyncOptionsProvider(options)];
        } else if (options.useClass) {
            return [
                this.createAsyncOptionsProvider(options),
                {
                    provide: options.useClass,
                    useClass: options.useClass,
                },
            ];
        } else {
            return [];
        }
    }

    private static createAsyncOptionsProvider(
        options: MongoModuleAsyncOptions,
    ): Provider {
        if (options.useFactory) {
            return {
                provide: MONGO_MODULE_OPTIONS,
                useFactory: options.useFactory,
                inject: options.inject || [],
            };
        } else if (options.useExisting) {
            return {
                provide: MONGO_MODULE_OPTIONS,
                useFactory: async (optionsFactory: MongoOptionsFactory) =>
                    await optionsFactory.createMongoOptions(),
                inject: [options.useExisting],
            };
        } else if (options.useClass) {
            return {
                provide: MONGO_MODULE_OPTIONS,
                useFactory: async (optionsFactory: MongoOptionsFactory) =>
                    await optionsFactory.createMongoOptions(),
                inject: [options.useClass],
            };
        } else {
            throw new Error('Invalid MongoModule options');
        }
    }
}

- Ali Yusuf

---

虽然这个链接可能回答了问题，但最好在此处包含答案的必要部分并提供参考链接。如果链接页面发生更改，仅有链接的答案可能会失效。-【来自审阅】 - Mihai Chelaru

@MihaiChelaru 已注意到。 - Ali Yusuf