typeorm how to write to different databases?

Question

I'm trying to create one table in one database and another table in another database. For this to work there are 2 entities that are addressing the correct database with the @Entity decorator. The problem is that typeorm or rather SQL throws an error that user x can't write in database y.

How to address the different databases correctly?

// src/entity/User.ts
import { Entity, PrimaryGeneratedColumn, Column } from 'typeorm'

@Entity({database: 'db1'})
export class User {
  @PrimaryGeneratedColumn()
  id: number

  @Column()
  mame: string
}

// src/entity/Movie.ts
import { Entity, PrimaryGeneratedColumn, Column } from 'typeorm'

@Entity({database: 'db2'})
export class Movie {
  @PrimaryGeneratedColumn()
  id: number

  @Column()
  title: string
}

The connections are created with their proper credentials:

// src/index.ts
    await createConnection({
      name: 'connection1',
      host: 'SERVER1',
      username: 'bob',
      password: 'xxx',
      type: 'mssql',
      database: 'db1',
      synchronize: true,
      entities: ['src/entity/**/*.ts'],
      migrations: ['src/migration/**/*.ts'],
      subscribers: ['src/subscriber/**/*.ts'],
      cli: {
        entitiesDir: 'src/entity',
        migrationsDir: 'src/migration',
        subscribersDir: 'src/subscriber',
      },
    })

    await createConnection({
      name: 'connection2',
      host: 'SERVER2',
      username: 'mike',
      password: 'xxx',
      type: 'mssql',
      database: 'db2',
      synchronize: true,
      entities: ['src/entity/**/*.ts'],
      migrations: ['src/migration/**/*.ts'],
      subscribers: ['src/subscriber/**/*.ts'],
      cli: {
        entitiesDir: 'src/entity',
        migrationsDir: 'src/migration',
        subscribersDir: 'src/subscriber',
      },
    })

We do need to work with decorators because we also use type-graphql docrators in the class. The funny thing is that when the decorator for entity is left blank we see both tables created in both databases. So the credentials are correct.

I found a similar issue here and requested help here.

Thank you for your help.

Answer 1

I figured this one out myself with the help in the comments from @Aluan. So here are the steps for anyone else running into this:

1. Modify the array entities so each connection/database has its own folder with entity files and name the connection you use the most as default:

// src/index.ts
 await createConnections([
      {
        name: 'default',
        host: 'SERVER1',
        username: 'bob',
        password: 'kiwi',
        type: 'mssql',
        database: 'db1',
        ...
       "synchronize": true,
       "entities": ["src/db1/entity/**/*.ts"],
      },
      {
        name: 'connection2',
        host: 'SERVER2',
        username: 'Mike',
        password: 'carrot',
        type: 'mssql',
        database: 'db2',
        ...
       "synchronize": true,
       "entities": ["src/db2/entity/**/*.ts"],
    ])

2. Create entity files for each database in its respective folder:
   • src/db1/entity/Fruit.ts > table in db1
   • src/db2/entity/Vegetables.ts > table in db2

With "synchronize": true each table will be created automatically in the correct database

3. Accessing data in the tables:

• For the default connection::

import { Fruit } from 'src/db1/entity/Fruit'
  fruits() {
    return Fruit.find()
  }

• For the non default connection:

import { getRepository } from 'typeorm'
import { Vegetable } from 'src/db2/entity/Vegetable'
  vegetables() {
      return async () => await getRepository(Vegetable).find()
  }

or

  async vegetables() {
    return await getRepository(vegetables, 'connection2').find()
  }

I hope this helps someone else struggling with the same issues as I did.