Typeorm connect to multiple database

Question

I use node.js , TS and typeorm for back-end project.

I need to connect to a different database in the middleware according to the parameter I send. And I've got to send the query to the database.

ormconfig

[
  {
    "name": "default",
    "type": "postgres",
    "host": "localhost",
    "port": 5432,
    "username": "postgres",
    "password": "12345",
    "database": "dbOne"
  },
  {
    "name": "second-connection",
    "type": "postgres",
    "host": "localhost",
    "port": 5432,
    "username": "postgres",
    "password": "12345",
    "database": "dbTwo"
  }
]

That's my connection settings. After I do that, I'm trying to connect to the middleware.

   const connectionOptions = await getConnectionOptions("second-connection");
   const conTwo = await createConnection(connectionOptions);

   const managerTwo = getManager("second-connection");

   const resultTwo = await managerTwo
      .createQueryBuilder(SysCompany, "company")
      .getOne();

   console.log(resultTwo);

I think I can connect to the database, but I'm having trouble with the repository.

Error

EntityMetadataNotFound: No metadata for "SysCompany" was found.

@Entity()
export class SysCompany extends CoreEntityWithTimestamp {

  @Column({ length: 100 })
  name: string;

  // FK
  // SysPersonnel
  @OneToMany(type => SysPersonnel, personnel => personnel.sysCompany)
  sysPersonnels: SysPersonnel[];

}

Solution 1

Maybe typeORM cannot find your JavaScript entity. I had that problem some time ago. You can do the following:

• Check your destination folder after you built the project. Is your SysCompany.js available?
• Set the entities property in the configuration. It must contain the path to your JS entities. The typeORM docs state that "Each entity must be registered in your connection options".

{
 "name": "second-connection",
 "type": "postgres",
 "host": "localhost",
 "port": 5432,
 "username": "postgres",
 "password": "12345",
 "database": "dbTwo"
 "entities": ["<path to entities>/**/*.js"]
}

I would also recommend to use a JavaScript configuration file. Your ormconfig.js can then use __dirname (directory name of the current module) to set the path. So if your directories look like this:

project/ormconfig.js
project/dist/entity/SysCompany.js
project/dist/entity/OtherEntity.js

You can use a configuration like this:

import {join} from "path";
...
  entities: [
    join(__dirname, "dist/entity/**/*.js")
  ],
...

You could also prevent duplication by using a base configuration object.

import {join} from "path";

const baseOptions = {
  type: "postgres",
  host: "localhost",
  port: 5432,
  username: "postgres",
  password: "12345",
  entities: [
    join(__dirname, "dist/entity/**/*.js")
  ]
}

const defaultConfig = Object.assign({
  name: "default",
  database: "dbOne",
}, baseOptions);

const secondConfig = Object.assign({
  name: "second-connection",
  database: "dbTwo",
}, baseOptions);

module.exports = [ defaultConfig, secondConfig ];

In the file where you open the connection you could use an import:

import { secondConfig } from "<path to file>/ormconfig";

const conTwo = await createConnection(secondConfig);

Solution 2

The simplest way to use multiple databases is to create different connections:

import {createConnections} from "typeorm";

const connections = await createConnections([{
    name: "db1Connection",
    type: "mysql",
    host: "localhost",
    port: 3306,
    username: "root",
    password: "admin",
    database: "db1",
    entities: [__dirname + "/entity/*{.js,.ts}"],
    synchronize: true
}, {
    name: "db2Connection",
    type: "mysql",
    host: "localhost",
    port: 3306,
    username: "root",
    password: "admin",
    database: "db2",
    entities: [__dirname + "/entity/*{.js,.ts}"],
    synchronize: true
}]);

This approach allows you to connect to any number of databases you have and each database will have its own configuration, own entities and overall ORM scope and settings. For each connection a new Connection instance will be created. You must specify a unique name for each connection you create. The connection options can also be loaded from an ormconfig file. You can load all connections from the ormconfig file:

import {createConnections} from "typeorm";

const connections = await createConnections();

or you can specify which connection to create by name:

import {createConnection} from "typeorm";

const connection = await createConnection("db2Connection");

When working with connections you must specify a connection name to get a specific connection:

import {getConnection} from "typeorm";

const db1Connection = getConnection("db1Connection");
// you can work with "db1" database now...

const db2Connection = getConnection("db2Connection");
// you can work with "db2" database now...

Benefit of using this approach is that you can configure multiple connections with different login credentials, host, port and even database type itself. Downside for might be that you'll need to manage and work with multiple connection instances.

Comments

[Dor Shinar]

And are you sure the SysCompany table is created?

[Jerome]

trying to use this piece of code, bit got error while compiling (typescript) : import { join } from "path"; SyntaxError: Unexpected token {