基于NestJS+TypeORM的多数据库连接代码优化方案咨询

一、ConnectionPool模块方案完全可行 #

你提出的统一管理数据库连接的方案是解决这类多租户（同表结构多数据库）场景的合理思路，能有效收敛重复的数据源配置与注入逻辑，大幅减少业务模块的冗余代码。

具体实现步骤 #

1. 创建全局ConnectionPoolModule
   将所有数据库配置收敛到模块中，同时导入对应实体并导出管理服务：

// connection-pool.module.ts
import { Module, Global } from '@nestjs/common';
import { TypeOrmModule } from '@nestjs/typeorm';
import { ConnectionPoolService } from './connection-pool.service';
import { Facility, FacilityCategory, Customer } from './entities';

// 抽离数据库配置，便于维护
const DB_CONFIGS = [
  {
    type: 'mysql',
    name: 'google',
    host: 'our_host_url',
    port: 3306,
    username: 'username',
    password: 'password',
    database: 'db_google',
    entities: [Facility, FacilityCategory, Customer],
    synchronize: false,
  },
  {
    type: 'mysql',
    name: 'linkedin',
    host: 'our_host_url',
    port: 3306,
    username: 'username',
    password: 'password',
    database: 'db_linkedin',
    entities: [Facility, FacilityCategory, Customer],
    synchronize: false,
  },
];

@Global() // 设为全局模块，业务模块无需重复导入
@Module({
  imports: [
    ...DB_CONFIGS.map(config => TypeOrmModule.forRoot(config)),
    ...DB_CONFIGS.map(config => TypeOrmModule.forFeature([Customer, Facility], config.name)),
  ],
  providers: [ConnectionPoolService],
  exports: [ConnectionPoolService],
})
export class ConnectionPoolModule {}

2. 实现ConnectionPoolService
   封装数据源的映射与获取逻辑，提供通用的仓库获取方法：

// connection-pool.service.ts
import { Injectable, InjectDataSource } from '@nestjs/common';
import { DataSource } from 'typeorm';

// 维护企业名与数据源名称的映射
const COMPANY_DB_MAP = {
  apollohotels: 'google',
  saniresort: 'linkedin',
};

@Injectable()
export class ConnectionPoolService {
  private dataSourceMap: Map<string, DataSource> = new Map();

  constructor(
    @InjectDataSource('google') private dataSourceGoogle: DataSource,
    @InjectDataSource('linkedin') private dataSourceLinkedin: DataSource,
  ) {
    this.dataSourceMap.set('google', this.dataSourceGoogle);
    this.dataSourceMap.set('linkedin', this.dataSourceLinkedin);
  }

  getDataSource(companyName: string): DataSource | undefined {
    const dbName = COMPANY_DB_MAP[companyName];
    return this.dataSourceMap.get(dbName);
  }

  // 封装通用仓库获取逻辑，简化业务代码
  getRepository<T>(entity: new () => T, companyName: string) {
    const dataSource = this.getDataSource(companyName);
    if (!dataSource) {
      throw new Error(`未找到企业${companyName}对应的数据源`);
    }
    return dataSource.getRepository(entity);
  }
}

3. 业务模块简化使用
   业务模块只需注入ConnectionPoolService，无需再处理多数据源的导入与注入：

// customer.service.ts
import { Injectable } from '@nestjs/common';
import { ConnectionPoolService } from '../connection-pool/connection-pool.service';
import { Customer } from './entities/customer.entity';

@Injectable()
export class CustomerService {
  constructor(private readonly connectionPoolService: ConnectionPoolService) {}

  async findCustomer(username: string, companyName: string): Promise<Customer | null> {
    const customerRepo = this.connectionPoolService.getRepository(Customer, companyName);
    return customerRepo.findOneBy({ username });
  }
}

二、更优扩展方案：动态数据源+租户上下文 #

如果未来企业数量会增长，硬编码配置的方式会逐渐难以维护，此时可以采用动态数据源+租户上下文的方案：

1. 动态注册数据源 #

不在启动时初始化所有数据源，而是根据请求按需创建并缓存：

// connection-pool.service.ts
import { Injectable } from '@nestjs/common';
import { DataSource, DataSourceOptions } from 'typeorm';
// 配置可存于配置文件或配置中心
import { COMPANY_DB_CONFIG_MAP } from '../config/db.config';

@Injectable()
export class ConnectionPoolService {
  private dataSourceCache: Map<string, DataSource> = new Map();

  async getDataSource(companyName: string): Promise<DataSource> {
    // 优先从缓存获取
    if (this.dataSourceCache.has(companyName)) {
      return this.dataSourceCache.get(companyName);
    }

    const config = COMPANY_DB_CONFIG_MAP[companyName];
    if (!config) {
      throw new Error(`未找到企业${companyName}的数据库配置`);
    }

    const dataSource = new DataSource(config as DataSourceOptions);
    // 未初始化则先初始化
    if (!dataSource.isInitialized) {
      await dataSource.initialize();
    }

    this.dataSourceCache.set(companyName, dataSource);
    return dataSource;
  }

  async getRepository<T>(entity: new () => T, companyName: string) {
    const dataSource = await this.getDataSource(companyName);
    return dataSource.getRepository(entity);
  }
}

2. 租户上下文中间件 #

通过中间件自动从请求中提取租户信息（URL参数/Header），存入请求上下文，避免业务代码手动传递：

// tenant.middleware.ts
import { Injectable, NestMiddleware } from '@nestjs/common';
import { Request, Response, NextFunction } from 'express';

export const TENANT_CONTEXT_KEY = 'tenant';

@Injectable()
export class TenantMiddleware implements NestMiddleware {
  use(req: Request, res: Response, next: NextFunction) {
    const companyName = req.query.companyName as string;
    if (!companyName) {
      res.status(400).send('缺少必填参数companyName');
      return;
    }
    (req as any)[TENANT_CONTEXT_KEY] = companyName;
    next();
  }
}

在AppModule中注册中间件：

// app.module.ts
import { Module, MiddlewareConsumer } from '@nestjs/common';
import { TenantMiddleware } from './tenant.middleware';
import { CustomerModule } from './customer/customer.module';
import { ConnectionPoolModule } from './connection-pool/connection-pool.module';

@Module({
  imports: [ConnectionPoolModule, CustomerModule],
})
export class AppModule {
  configure(consumer: MiddlewareConsumer) {
    consumer.apply(TenantMiddleware).forRoutes('*');
  }
}

3. 业务模块简化调用 #

通过请求上下文自动获取租户信息，无需手动传入：

// customer.service.ts
import { Injectable, Req } from '@nestjs/common';
import { Request } from 'express';
import { ConnectionPoolService } from '../connection-pool/connection-pool.service';
import { Customer } from './entities/customer.entity';
import { TENANT_CONTEXT_KEY } from '../tenant.middleware';

@Injectable()
export class CustomerService {
  constructor(private readonly connectionPoolService: ConnectionPoolService) {}

  async findCustomer(username: string, @Req() req: Request): Promise<Customer | null> {
    const companyName = (req as any)[TENANT_CONTEXT_KEY];
    const customerRepo = await this.connectionPoolService.getRepository(Customer, companyName);
    return customerRepo.findOneBy({ username });
  }
}

这种方案的优势：

• 新增企业只需添加配置，无需修改代码
• 数据源按需初始化，节省服务器资源
• 业务代码无需手动传递租户标识，减少冗余

总结 #

• 你提出的ConnectionPool模块方案完全可行，适合企业数量较少、配置固定的场景
• 若未来企业数量会增长，动态数据源+租户上下文的方案扩展性更强，维护成本更低

内容的提问来源于stack exchange，提问作者Thomas Kottas