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

Bellows

Answer 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' },
  })
]

Edessa

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? – Bellows

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

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

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? – Vedi

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

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. – Vedi

@SorayaAnvari, Have you been able to solve the issue. I have also similar situation. Thank you! – Galvan

Answer 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,
})

Rojas

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

@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 – Barham

Answer 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).

Arun

Answer 4

for those who face this problem , this is my solution

AppModule

@Module({
  imports: [
    ConfigModule.forRoot({
      isGlobal: true,
      load: [
        database,
        databaseAllo #<= my second database
      ]
    }),
    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)
  }

}

Waistband

Answer 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

Agneta

Answer 6

Just a quick PSA, maybe this was missing at the time the question was asked, but now NestJS has complete documentation on this topic, including all the pitfalls that other answers here address: https://docs.nestjs.com/techniques/database#multiple-databases

Refluent

Answer 7

@DamarOwen's answer worked for me, after I stumbled over another gotcha: If you want to store the connection name as a variable, do not export that constant from app.module.ts. Store the variable in another file like constants.ts.

---

This is what I tried:

app.module.ts

export const DB_CONNECTION_1 = 'conn1';

@Module({
  imports: [
    TypeOrmModule.forRootAsync({
      name: DB_CONNECTION_1,
      ...
    })
  ],
  ...
)
export class AppModule {}

database.module.ts

@Module({
  imports: [
    TypeOrmModule.forFeature(
      [MyRepo],
      DB_CONNECTION_1,
    ),
  ],
  providers: [MyRepo],
})

my.repo.ts

@Injectable()
export class MyRepo {
  constructor(
    @InjectRepository(MyOrmEntity, DB_CONNECTION_1)
    private readonly repo: Repository<MyOrmEntity>,
  ) {}
}

This threw an Error No repository found for MyOrmEntity was found. Looks like this entity is not registered in current "default" connection. (Note: My application has another connection named "default").

I had to move export const DB_CONNECTION_1 = 'conn1'; away from app.module.ts into its own file constants.ts. Then it worked.

Shellans