Dynamically switch database at runtime

[GBeushausen]

Issue type:

[ x] question
[ ] bug report
[ ] feature request
[ ] documentation issue

Database system/driver:

[ ] cordova
[ ] mongodb
[x ] mssql
[ ] mysql / mariadb
[ ] oracle
[ ] postgres
[ ] cockroachdb
[ ] sqlite
[ ] sqljs
[ ] react-native
[ ] expo

TypeORM version:

[x ] latest
[ ] @next
[ ] 0.x.x (or put your version here)

Hi. I would like to know if it's possible to set the database in a connection at runtime. I'm having a system where there are many tenants and each one has it's own isolated database. The structure of the databases are all exactly the same and i want to use the same entity classes in my code. But for data safety regulations, the data of the tenants need to be isolated in their own databases. So after a user logs in, i want to set the database name to be used at runtime. Is this possible? Thanks.

[totalard]

Dear,

You have to create each Connection per user.. Means, Create each Connection for Each Database and attach same configuration to it..And use newly created connection to query database.

[Mando75]

Yes you can. TypeORM exports a createConnection method that lets you specify all of your configurations. For example, here is a runtime connection method. You may want to modify this a bit so you aren't constantly creating new db connections if you already have one for the customer, but it at least gives you a starting place.

import {createConnection} from 'typeorm'
export const createTypeORMConnection = async (customer) => {
  // get things like cache, migration, subscribers, logging etc
  const defaultOptions = getDefaultOptions()

  return createConnection({
    ...defaultOptions,
    name: customer.name,
    type: customer.dbType,
    url: customer.dbUrl
  });
};

const getDefaultOptions = () => ({
  synchronize: false,
  logging: true,
  entities: ["dist/entity/**/*.js"],
  migrationsRun: true,
  migrations: ["dist/migration/**/*.js"],
  subscribers: ["dist/subscriber/**/*.js"],
  cache: {
    type: "ioredis",
    duration: 2000
  },
  cli: {
    entitiesDir: "src/entity",
    migrationsDir: "src/migration",
    subscribersDir: "src/subscriber"
  }
});

[KleinMaximus]

And how many connections can be created simultaneously? If I have 1000 users, each with its own database, can TypeORM support 1000 connections at the same time?

[firmanjml]

And how many connections can be created simultaneously? If I have 1000 users, each with its own database, can TypeORM support 1000 connections at the same time?

I'm curious to know about this too.
Will it cause serious performance issues if we created multiple connection simultaneously even when not actively using?

[ahmedosama94]

And how many connections can be created simultaneously? If I have 1000 users, each with its own database, can TypeORM support 1000 connections at the same time?

I'm curious to know about this too. Will it cause serious performance issues if we created multiple connection simultaneously even when not actively using?

Same question here

[SpeedoPasanen]

Here's a (probably) working example. Not tested, just wrote it down here because I think I'll need this in the future. :)

Cache the connections as Promises, so if someone requests a connection while one is being created for the same Tenant, both requesters receive the same instance. Awaiting before caching would cause memory leaks. Keep connections alive for a certain time and then close.

Too short TTL and users will constantly have to wait for a new connection. Too long and (maybe) too many simultaneous connections. I'd keep it long and lower it if problems arise.

As for performance, I think as long as your DB allows that many connections, having 1000 open won't cause big performance issues in NodeJS. They are basically socket connections, not a big memory or CPU hit. Based on my deduction, take with a grain of salt.

Having said all, in my use case all DB:s would be on the same server, so I'll keep investigating if it's possible to use one connection and just change the DB name on the fly.

import { Connection, createConnection } from 'typeorm';

export interface Tenant {
  id: string | number;
}
export interface ConnectionCacheItem {
  connection: Promise<Connection>;
  timeout: NodeJS.Timeout;
}

const cache = new Map<string | number, ConnectionCacheItem>();

// Keep connections open for X ms after being last used.
const TTL =  process.env.DB_CONNECTION_TTL || 8 * 60 * 60 * 1000;

export const getTenantConnection = async (tenant: Tenant): Promise<Connection> => {
  const cached = cache.get(tenant.id);
  const connection = cached
    ? cached.connection
    : createConnection(getOptions(tenant));
  if (cached) {
    clearTimeout(cached.timeout);
  }
  cache.set(tenant.id, {
    timeout: setTimeout(() => {
      clearCache(tenant);
    }, TTL),
    connection,
  });
  return cache.get(tenant.id).connection;
};

const clearCache = async (tenant: Tenant): Promise<void> => {
  const cached = cache.get(tenant.id);
  if (cached) {
    cache.delete(tenant.id);
    (await cached.connection).close();
  }
};

const getOptions = (tenant: Tenant): any => {
  // TODO: implement
  throw new Error('Not implemented');
};

[strivelabs-pablorb]

We do something similar as u mention @SpeedoPasanen. And we have the same use case where all databases are accessible from the same server. I was wondering if you managed to solve what u mentioned: " I'll keep investigating if it's possible to use one connection and just change the DB name on the fly."

I was thinking of establishing a connection to an empty database or to the Postgres one and then with query builder request data from the db that the user needs. But I am not so sure if there are any risks in having the connection to the Postgres db while having an empty db just for the sake of establishing the connection looks like a Hack that might cause issues latter.