Nestjs + Typeorm: Use Two Or More Databases?

November 26, 2023 Post a Comment

I'm trying since 2 days to solve this, perhaps I'm simply missing the point here. My goal was to write a NestJS app (with TypeORM included) which serves a RestAPI for 2 or 3 of my

Solution 1:

I just tried setting up TypeORM with multiple databases and a ormconfig.json and it did not work for me at all. It seemed to always use the default connection and when no default (= without explicit name) connection was found it threw the corresponding error.

It did work though when I defined the connections in the app.module.ts instead (I removed ormconfig.json):

imports: [
  ...,
  TypeOrmModule.forRoot({
    name:'Project1',
    type:'mysql',
    host:'localhost',
    port:3306,
    username:'<username>',
    password:'<pwd>',
    database:'<database>',
    synchronize:false,
    entities: ['project1/*.entity.ts'],
    subscribers: ['project1/*.subscriber.ts'],
    migrations: ['project1/migrations/*.ts'],
    cli: { migrationsDir:'project1/migrations' },
  }),
  TypeOrmModule.forRoot({
    name:'project2',
    type:'mysql',
    host:'localhost',
    port:3306,
    username:'<another-username>',
    password:'<another-pwd>',
    database:'<another-database>',
    synchronize:false,
    entities: ['project2/*.entity.ts'],
    subscribers: ['project2/*.subscriber.ts'],
    migrations: ['project2/migrations/*.ts'],
    cli: { migrationsDir:'project2/migrations' },
  })
]

Solution 2:

For clarity and for other developers to come to this post:

From NestJS documentation:

If you don't set any name for a connection, its name is set to default. Please note that you shouldn't have multiple connections without a name, or with the same name, otherwise they simply get overridden.

One of your connections must have one of the following:

"name":"default"
Without any name.

I would recommend to declare all your connections in ormconfig.json and not declare it in code.

An example to import the connections from ormconfig.json:

@Module({
    imports: [TypeOrmModule.forFeature([Entity1, Entity2]), //This will use default connectionTypeOrmModule.forRoot({name: 'con1'}), // This will register globaly con1TypeOrmModule.forRoot({name: 'con2'}), // This will register globaly con2controllers: [...],
    providers: [...],
    exports: [...]
})

in your module (not have to be the root module, only the modules you will need the connections).

Solution 3:

You need to explicitly pass the connection name at the same level inside TypeOrmModule.forRoot({ name: 'db1Connection' }) incase you are using multiple database connections.

TypeOrmModule.forRootAsync({name:DB1_CONNECTION,imports: [ConfigModule],useClass:TypeormDb1ConfigService,}),TypeOrmModule.forRootAsync({name:DB2_CONNECTION,imports: [ConfigModule],useClass:TypeormDb2ConfigService,})

Solution 4:

This is how I've manage to fix it. With a single configuration file I can run the migrations on application boostrap or using TypeOrm's CLI.

src/config/ormconfig.ts

import parseBoolean from'@eturino/ts-parse-boolean';
import { TypeOrmModuleOptions } from'@nestjs/typeorm';
import * as dotenv from'dotenv';
import { join } from'path';

dotenv.config();

export = [
  {
    //name: 'default',type: 'mssql',
    host: process.env.DEFAULT_DB_HOST,
    username: process.env.DEFAULT_DB_USERNAME,
    password: process.env.DEFAULT_DB_PASSWORD,
    database: process.env.DEFAULT_DB_NAME,
    options: {
      instanceName: process.env.DEFAULT_DB_INSTANCE,
      enableArithAbort: false,
    },
    logging: parseBoolean(process.env.DEFAULT_DB_LOGGING),
    dropSchema: false,
    synchronize: false,
    migrationsRun: parseBoolean(process.env.DEFAULT_DB_RUN_MIGRATIONS),
    migrations: [join(__dirname, '..', 'model/migration/*.{ts,js}')],
    cli: {
      migrationsDir: 'src/model/migration',
    },
    entities: [
      join(__dirname, '..', 'model/entity/default/**/*.entity.{ts,js}'),
    ],
  } asTypeOrmModuleOptions,
  {
    name: 'other',
    type: 'mssql',
    host: process.env.OTHER_DB_HOST,
    username: process.env.OTHER_DB_USERNAME,
    password: process.env.OTHER_DB_PASSWORD,
    database: process.env.OTHER_DB_NAME,
    options: {
      instanceName: process.env.OTHER_DB_INSTANCE,
      enableArithAbort: false,
    },
    logging: parseBoolean(process.env.OTHER_DB_LOGGING),
    dropSchema: false,
    synchronize: false,
    migrationsRun: false,
    entities: [],
  } asTypeOrmModuleOptions,
];

src/app.module.ts

import configuration from'@config/configuration';
import validationSchema from'@config/validation';
import { Module } from'@nestjs/common';
import { ConfigModule } from'@nestjs/config';
import { TypeOrmModule } from'@nestjs/typeorm';
import { LoggerService } from'@shared/logger/logger.service';
import { UsersModule } from'@user/user.module';
import { AppController } from'./app.controller';
import ormconfig = require('./config/ormconfig'); //path mapping doesn't work here

@Module({
  imports: [
    ConfigModule.forRoot({
      cache: true,
      isGlobal: true,
      validationSchema: validationSchema,
      load: [configuration],
    }),
    TypeOrmModule.forRoot(ormconfig[0]), //defaultTypeOrmModule.forRoot(ormconfig[1]), //other dbLoggerService,
    UsersModule,
  ],
  controllers: [AppController],
})
exportclassAppModule {}

package.json

"scripts":{
    ...
    "typeorm":"ts-node -r tsconfig-paths/register ./node_modules/typeorm/cli.js --config ./src/config/ormconfig.ts","typeorm:migration:generate":"npm run typeorm -- migration:generate -n","typeorm:migration:run":"npm run typeorm -- migration:run"},

Project structure

src/
├── app.controller.ts
├── app.module.ts
├── config
│   ├── configuration.ts
│   ├── ormconfig.ts
│   └── validation.ts
├── main.ts
├── model
│   ├── entity
│   ├── migration
│   └── repository
├── route
│   └── user
└── shared
    └── logger

Solution 5:

for those who face this problem , this is my solution

AppModule

@Module({
  imports: [
    ConfigModule.forRoot({
      isGlobal: true,
      load: [
        database,
        databaseAllo
      ]
    }),
    TypeOrmModule.forRootAsync({
      useFactory: (configs: ConfigService) => configs.get("db_config"),
      inject: [ConfigService],
    }),
    TypeOrmModule.forRootAsync({
      name:"db_allo", <= create connection to my second db
      useFactory: (configs: ConfigService) => configs.get("db_config_allo"),
      inject: [ConfigService],
    }),
    AuthModule,
    JwtAuthModule
  ],
  controllers: []
})
export class AppModule {}

my project module ( contain table from second db )

@Module({
  imports: [
    TypeOrmModule.forFeature([AlloMpcTable], "db_allo" <= call connection again),
  ],
  providers: [
    AlloRepository
  ],
  exports: [AlloRepository],
  controllers: [],
})
export class AlloModule {}

my project repository

@Injectable()
exportclassAlloRepositoryextendsBaseRepository<AlloMpcTable> {
  constructor(@InjectRepository(AlloMpcTable, "db_allo") <= you need to call connection again
    privatereadonly allo: Repository<AlloMpcTable>,
  ) {
    super(allo)
  }

  publicasyncFind(id: number): Promise<AlloMpcTable> {
    returnawaitthis.allo.findOne(id)
  }

}

JavaScript Free