Search code examples
serviceentitynestjstypeorm

NestJs: Query multiple entities from multiple database

I have mysql_server_1.database1.users

And mysql_server_2.database3.users_revenue

  • How can I query rows from users
  • How can I query rows from users_revenue

First, I've already setup the connections:

const mysql1__database1 = TypeOrmModule.forRootAsync({
  imports: [ConfigModule],
  // @ts-ignore
  useFactory: (configService: ConfigService) => ({
    type: configService.get("DASHBOARD_DB_TYPE"),
    host: configService.get("DASHBOARD_DB_HOST"),
    port: configService.get("DASHBOARD_DB_PORT"),
    username: configService.get("DASHBOARD_DB_USER"),
    password: configService.get("DASHBOARD_DB_PASSWORD"),
    database: configService.get("DASHBOARD_DB_NAME"),
    entities: [__dirname + '/**/*.entity{.ts,.js}'],
    // entities: [User],
    autoLoadEntities: true,
    synchronize: true,
  }),
  inject: [ConfigService],
});

const mysql2__database3 = TypeOrmModule.forRootAsync({
  imports: [ConfigModule],
  // @ts-ignore
  useFactory: (configService: ConfigService) => ({
    name: 'mysql2__database3',
    type: configService.get("DASHBOARD2_DB_TYPE"),
    host: configService.get("DASHBOARD2_DB_HOST"),
    port: configService.get("DASHBOARD2_DB_PORT"),
    username: configService.get("DASHBOARD2_DB_USER"),
    password: configService.get("DASHBOARD2_DB_PASSWORD"),
    database: configService.get("DASHBOARD2_DB_NAME"),
    entities: [__dirname + '/**/*.entity{.ts,.js}'],
    // entities: [User],
    autoLoadEntities: true,
    synchronize: true,
  }),
  inject: [ConfigService],
});

@Module({
  imports: [
    mysql1__database1,
    mysql2__database3,
    StatsModule,
  ],
  controllers: [AppController],
  providers: [AppService, StatsService],
})
export class AppModule {}

user.service.ts

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

@Injectable()
export class UserService {
  constructor(@InjectRepository(User) private usersRepository: Repository<User>) {}

  async findAll(): Promise<User[]> {
    return await this.usersRepository.find();
  }
}

Then this code return an empty array instead of so many rows exists in my database;

const items = await this.userService.findAll();

--- update --- I've take a look at the typeorm source code: https://github.com/nestjs/typeorm/blob/8af34889fa7bf14d7dc5541beef1d5c2b50c2609/lib/common/typeorm.decorators.ts#L13

Then https://docs.nestjs.com/techniques/database#multiple-databases

At this point, you have User and Album entities registered with their own connection. With this setup, you have to tell the TypeOrmModule.forFeature() method and the @InjectRepository() decorator which connection should be used. If you do not pass any connection name, the default connection is used.

So I think it should work?

@InjectRepository(User, 'mysql2_database3')

@Module({
  imports: [
    TypeOrmModule.forFeature([User], "mysql2_database3"),
  ],
  providers: [UserService],
  controllers: [StatsController],
})
export class StatsModule {}

Still got the error:

Please make sure that the argument mysql2_database3Connection at index [0] is available in the TypeOrmModule context.

Solution

  • Thank to @jmc29 on discord, his guide helped

    enter image description here

    The solution is:

    const mysql2__database3 = TypeOrmModule.forRootAsync({
  imports: [ConfigModule],
  // @ts-ignore
  useFactory: (configService: ConfigService) => ({
    name: 'mysql2__database3',
    type: configService.get("DASHBOARD2_DB_TYPE"),
    host: configService.get("DASHBOARD2_DB_HOST"),
    port: configService.get("DASHBOARD2_DB_PORT"),
    username: configService.get("DASHBOARD2_DB_USER"),
    password: configService.get("DASHBOARD2_DB_PASSWORD"),
    database: configService.get("DASHBOARD2_DB_NAME"),
    entities: [__dirname + '/**/*.entity{.ts,.js}'],
    autoLoadEntities: true,
    synchronize: true,
  }),
  inject: [ConfigService],
});

    add one more line:

    const mysql2__database3 = TypeOrmModule.forRootAsync({

  name: 'mysql2__database3', // -----> Add this line, it's is required

  imports: [ConfigModule],
  // @ts-ignore
  useFactory: (configService: ConfigService) => ({
    name: 'mysql2__database3',
    type: configService.get("DASHBOARD2_DB_TYPE"),
    host: configService.get("DASHBOARD2_DB_HOST"),
    port: configService.get("DASHBOARD2_DB_PORT"),
    username: configService.get("DASHBOARD2_DB_USER"),
    password: configService.get("DASHBOARD2_DB_PASSWORD"),
    database: configService.get("DASHBOARD2_DB_NAME"),
    entities: [__dirname + '/**/*.entity{.ts,.js}'],
    autoLoadEntities: true,
    synchronize: true,
  }),
  inject: [ConfigService],
});