NestJS实现动态Sequelize数据库连接与指定库数据查询

核心思路 #

预先初始化多个数据库连接，通过接口传入的参数选择对应连接，复用相同模型完成数据查询。这种方式避免了频繁创建/销毁连接的性能损耗，同时保证模型逻辑的一致性。

1. 配置多数据库连接 #

首先定义两个数据库的配置，并创建对应的Sequelize连接实例。

1.1 数据库配置文件 #

创建src/database/database.config.ts（结合@nestjs/config管理环境变量）：

import { ConfigService } from '@nestjs/config';

export const getDbConfig = (configService: ConfigService, dbPrefix: string) => ({
  dialect: 'mysql', // 根据实际数据库类型调整
  host: configService.get(`${dbPrefix}_HOST`),
  port: configService.get(`${dbPrefix}_PORT`),
  username: configService.get(`${dbPrefix}_USER`),
  password: configService.get(`${dbPrefix}_PASSWORD`),
  database: configService.get(`${dbPrefix}_NAME`),
});

1.2 连接Provider #

创建src/database/database.providers.ts，初始化两个数据库连接：

import { Sequelize } from 'sequelize-typescript';
import { ConfigService } from '@nestjs/config';
import { getDbConfig } from './database.config';
import { User } from '../user/user.model'; // 共享模型

export const databaseProviders = [
  {
    provide: 'SEQUELIZE_DB1',
    useFactory: (configService: ConfigService) => {
      const sequelize = new Sequelize(getDbConfig(configService, 'DB1'));
      sequelize.addModels([User]);
      return sequelize;
    },
    inject: [ConfigService],
  },
  {
    provide: 'SEQUELIZE_DB2',
    useFactory: (configService: ConfigService) => {
      const sequelize = new Sequelize(getDbConfig(configService, 'DB2'));
      sequelize.addModels([User]);
      return sequelize;
    },
    inject: [ConfigService],
  },
];

1.3 数据库模块 #

创建src/database/database.module.ts，导出连接供其他模块使用：

import { Module } from '@nestjs/common';
import { ConfigModule } from '@nestjs/config';
import { databaseProviders } from './database.providers';

@Module({
  imports: [ConfigModule.forRoot()],
  providers: [...databaseProviders],
  exports: [...databaseProviders],
})
export class DatabaseModule {}

2. 定义共享模型 #

创建通用模型src/user/user.model.ts，无需指定固定连接：

import { Table, Column, Model, DataType } from 'sequelize-typescript';

@Table({ tableName: 'users' })
export class User extends Model<User> {
  @Column({ type: DataType.INTEGER, primaryKey: true, autoIncrement: true })
  id: number;

  @Column({ type: DataType.STRING })
  name: string;

  @Column({ type: DataType.STRING, unique: true })
  email: string;
}

3. 实现动态查询服务 #

创建src/user/user.service.ts，根据传入的数据库参数选择对应连接：

import { Injectable, Inject, BadRequestException } from '@nestjs/common';
import { Sequelize } from 'sequelize-typescript';
import { User } from './user.model';

export enum DbName {
  DB1 = 'db1',
  DB2 = 'db2',
}

@Injectable()
export class UserService {
  private readonly dbMap: Record<DbName, Sequelize>;

  constructor(
    @Inject('SEQUELIZE_DB1') private readonly sequelizeDb1: Sequelize,
    @Inject('SEQUELIZE_DB2') private readonly sequelizeDb2: Sequelize,
  ) {
    this.dbMap = {
      [DbName.DB1]: sequelizeDb1,
      [DbName.DB2]: sequelizeDb2,
    };
  }

  // 验证并获取数据库连接
  private getDbConnection(dbName: string): Sequelize {
    const validDbName = dbName as DbName;
    const connection = this.dbMap[validDbName];
    if (!connection) {
      throw new BadRequestException('无效的数据库标识，仅支持 db1/db2');
    }
    return connection;
  }

  // 查询所有用户
  async getUsers(dbName: string): Promise<User[]> {
    const sequelize = this.getDbConnection(dbName);
    const UserModel = sequelize.model<User>(User.name);
    return UserModel.findAll();
  }

  // 根据ID查询用户
  async getUserById(dbName: string, id: number): Promise<User> {
    const sequelize = this.getDbConnection(dbName);
    const UserModel = sequelize.model<User>(User.name);
    const user = await UserModel.findByPk(id);
    if (!user) {
      throw new BadRequestException('用户不存在');
    }
    return user;
  }

  // 模块初始化时验证连接
  async onModuleInit() {
    await Promise.all(
      Object.values(this.dbMap).map((conn) => conn.authenticate()),
    );
  }
}

4. 暴露查询接口 #

创建src/user/user.controller.ts，接收数据库参数并调用服务：

import { Controller, Get, Param, Query, ValidationPipe } from '@nestjs/common';
import { UserService, DbName } from './user.service';

@Controller('users')
export class UserController {
  constructor(private readonly userService: UserService) {}

  @Get()
  async getUsers(
    @Query('db', new ValidationPipe({ transform: true })) dbName: DbName,
  ) {
    return this.userService.getUsers(dbName);
  }

  @Get(':id')
  async getUserById(
    @Query('db', new ValidationPipe({ transform: true })) dbName: DbName,
    @Param('id') id: number,
  ) {
    return this.userService.getUserById(dbName, id);
  }
}

4.1 用户模块 #

创建src/user/user.module.ts，整合服务与控制器：

import { Module } from '@nestjs/common';
import { DatabaseModule } from '../database/database.module';
import { UserService } from './user.service';
import { UserController } from './user.controller';

@Module({
  imports: [DatabaseModule],
  providers: [UserService],
  controllers: [UserController],
})
export class UserModule {}

4.2 根模块 #

在src/app.module.ts中导入用户模块：

import { Module } from '@nestjs/common';
import { UserModule } from './user/user.module';

@Module({
  imports: [UserModule],
})
export class AppModule {}

接口调用示例 #

• 查询db1的所有用户：GET /users?db=db1
• 查询db2中ID为1的用户：GET /users/1?db=db2

内容的提问来源于stack exchange，提问作者RJ amal