NestJS + TypeORM: Use two or more databases?

Question

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 little projects, instead of writing a NestJS-App for every single one of them.

So far so good, the app is ready, works well with the single projects (which resides in subfolders with their entities, controllers, services, modules), but I can't get it to run with all of them.

The point seems to be the configuration, I'm using ormconfig.json:

[ {
    "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" }
}, {
    "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"
    } ]

The error message says:

[ExceptionHandler] Cannot find connection default because its not defined in any orm configuration files

Of course "default" couldn't be found, because I'm providing two configs with unique names different to "default".

In ApplicationModule I could provide the name of the connection, like this:

TypeOrmModule.forRoot( { name: "project1" } ),

but then it would work only for one project.

I could mix all in one config, but then I would have everything in one database, same user for all and perhaps mix up the entities...

Can someone give me a hint how to solve this? Perhaps with getConnection(<name>) in every module, but how to start the ApplicationModule then?

Kind regards,
sagerobert

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

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 3

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:

1. "name":"default"
2. 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 connection
    TypeOrmModule.forRoot({name: 'con1'}), // This will register globaly con1
    TypeOrmModule.forRoot({name: 'con2'}), // This will register globaly con2
    controllers: [...],
    providers: [...],
    exports: [...]
})

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

Solution 4

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()
export class AlloRepository extends BaseRepository<AlloMpcTable> {
  constructor(
    @InjectRepository(AlloMpcTable, "db_allo") <= you need to call connection again
    private readonly allo: Repository<AlloMpcTable>,
  ) {
    super(allo)
  }

  public async Find(id: number): Promise<AlloMpcTable> {
    return await this.allo.findOne(id)
  }

}

Solution 5

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}'),
    ],
  } as TypeOrmModuleOptions,
  {
    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: [],
  } as TypeOrmModuleOptions,
];

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]), //default
    TypeOrmModule.forRoot(ormconfig[1]), //other db
    LoggerService,
    UsersModule,
  ],
  controllers: [AppController],
})
export class AppModule {}

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

View more solutions

Comments

[sagerobert]

Thanks a lot, that seems to solve this issue! Just one another question: before this block, in the ..., section, do you import the Modules for each 'project' (like Project1Module, Project2Module) or additionally something else?

[Kim Kern]

Sorry, missed your comment. No, I couldn't think of any special import.

[diversemix]

Out of interest ... How did you run the migrations?

[Pubudu Dodangoda]

You just saved my day sir. TypeOrmModule.forRootAsync has the worst API interface I have ever witnessed.

[Soraya Anvari]

By defining as you said, I didn't get any error. But the program always run by default database. Have any idea what else should be checked?

[Kim Kern]

@SorayaAnvari When you use the forFeature import, are you also passing the name of the connection: TypeOrmModule.forFeature([Album], 'albumsConnection')?

[Soraya Anvari]

I wrote TypeOrmModule.forRoot with both databases in app.module. Then added TypeOrmModule.forFeature(Entities, 'connectionname') in each.separated module. My program has the same host with different databases. I created a module to handle each database request. I don't know what is missing.

[Allan Lima]

@PubuduDodangoda I think that the problem is not the api, is that the docs has only one small phrase mentioning this that only clicked when I read Frozenex reply. It makes sense how they do it, but it should be clearer in the docs