TypeORM: Dynamically set database schema for EntityManager (or repositories) at runtime?

Question

Situation:

For our SaaS API we use schema-based multitenancy, which means every customer (~tenant) has its own separate schema within the same (postgres) database, without interfering with other customers. Each schema consists of the same underlying entity-model.

Everytime a new customer is registered to the system, a new isolated schema is automatically created within the db. This means, the schema is created at runtime and not known in advance. The customer's schema is named according to the customer's domain.

For every request that arrives at our API, we extract the user's tenancy-affiliation from the JWT and determine which db-schema to use to perform the requested db-operations for this tenant.

Problem

After having established a connection to a (postgres) database via TypeORM (e.g. using createConnection), our only chance to set the schema for a db-operation is to resort to the createQueryBuilder:

const orders = await this.entityManager
  .createQueryBuilder()
  .select()
  .from(`${tenantId}.orders`, 'order') // <--- setting schema-prefix here
  .where("order.priority = 4")
  .getMany();

This means, we are forced to use the QueryBuilder as it does not seem to be possible to set the schema when working with the EntityManager API (or the Repository API).

However, we want/need to use these APIs, because they are much simpler to write, require less code and are also less error-prone, since they do not rely on writing queries "manually" employing a string-based syntax.

Question

In case of TypeORM, is it possible to somehow set the db-schema when working with the EntityManager or repositories?

Something like this?

// set schema when instantiating manager
const manager = connection.createEntityManager({ schema: tenantDomain });

// should find all matching "order" entities within schema
const orders = manager.find(Order, { priority: 4 })

// should find a matching "item" entity within schema using same manager
const item = manager.findOne(Item, { id: 321 })

Notes:

• The db-schema needs to be set in a request-scoped way to avoid setting the schema for other requests, which may belong to other customers. Setting the schema for the whole connection is not an option.
• We are aware that one could create a whole new connection and set the schema for this connection, but we want to reuse the existing connection. So simply creating a new connection to set the schema is not an option.

Chrissa

I'm having the exact same setup in my project, each tenant has its own schema, each schema looks the same and the connection needs to be request-scoped. I’m going the way which you don’t want to, meaning creating a new connection for each customer. What is your intention not to do it that way? Performance might be a reason, but currently I’m not running into any problems with to many database connections. Data security shouldn’t be an issue, doing it your way doesn’t differ from the way I do it: const manager = ConnectionUtils.createConnection(schema).createEntityManager(); – Reneareneau

Would you just use a Model Driven solution, with one entity class per tenant that you generate at each tenant change? Keeping business code in an abstract class and specifying the schema in the entity decorator in subclasses? – Claw

Hi @Reneareneau many thanks for your feedback! May I ask, how many requests does your backend receive per minute, and how many concurrent connections are usually kept open simultaneously? Also, at which point do you close the db-connection? Do you keep them idle or do you close them once the response is sent to the client via a middleware? The reason I don't want to open new connections is to avoid unnecessary resource usage (memory/CPU), moreover, I do not see the point in reestablishing a connection if – theoretically speaking – we can set a different schema with TypeORM... really annoying :( – Chrissa

@Claw Unfortunately "specifying the schema in the entity decorator in subclasses" is not possible. As I mentioned, the schema is created at runtime and I cannot create an entity per tenant and set decorators at compile time, since the tenants are not known in advance. – Chrissa

@B12Toaster I looked through the TypeORM sources to find a way to set the schema dynamically, but there seems to be no easy way (the way you find is the less dirty way I guess). I have about 50 tenants und the number of requests is not that high (around 50 requests per tenant per hour), so I can’t tell you much about scaling. So I think we have two options: Create a change request (maybe do it on your own) or find more about the performance while having many connections. Since I’m also very interested in this topic, I might try some test scenarios in my app. I will keep you up to date. – Reneareneau

HI @B12Toaster have you get the answer somehow? I had exactly the same questions and TypeORM team aren't responding... Just wondering if you found any nice solution. – Bedspring

@Bedspring nope not yet. I saw your Issue at github, thanks for opening it, will link it in the OP if that is okay for you? Currently, I am creating one connection per tenant and we will try to keep the tenant number per server below 100. But still experimenting with this. Here is an interesting article that provides some information about how many connections are possible with postgres and how much memory is consumed: citusdata.com/blog/2017/05/10/scaling-connections-in-postgres – Chrissa

@B12Toaster after creating schema how do you run initial migration. I want to implement your method but stuck got stuck there. – Frostbitten

Answer 1

To answer my own question:

At the moment there is no way to instantiate TypeORM repositories with different schemas at runtime without creating new connections.

So the only two options that a developer is left with for schema-based multi tenancy are:

1. Setting up new connections to connect with different schemas within the same db at runtime. E.g. see NestJS Request Scoped Multitenancy for Multiple Databases. However, one should definitely strive for reusing connections and and be aware of connection limits.
2. Abandoning the idea of working with the RepositoryApi and reverting to using createQueryBuilder (or executing SQL queries via query()).

For further research, here are some TypeORM GitHub issues that track the idea of changing the schema for a existing connections or repositories at runtime (similar to what is requested in the OP):

• Multi-tenant architecture using schema. #4786 proposes something like this.photoRepository.useSchema('customer1').find()
• Handling of database schemas #3067 proposes something like getConnection().changeDefaultSchema('myschema')
• Run-time change of schema #4473
• Add an ability to set postgresql schema per call #2439

P.S. If TypeORM decides to support the idea discussed in the OP, I will try to update this answer.

Chrissa

Wondering if there is any update on Typeorm supporting multi tenancy for a single DB? – Tequila

i wonder why typeorm doesn't have a clean method for something so common – Eponymy

Is there any updates on this topic? Does TypeORM still cannot switch schema at runtime? – Ditzel

getConnection() is deprecated – Celandine

Answer 2

Here is a global overview of the issues with schema-based multitenancy along with a complete walkthrough a Github repo for it.

Most of the time, you may want to use Postgres Row Security Policy instead. It gives most of the benefits of schema-based multitenancy (especially on developer experience), without the issues related to the multiplication of connections.

Friendly

@Felix K. , isn't the link provided(first one) in this answer the solution appropriate? It the connection in the pool exists , then it's not supposed to be recreated right? – Eponymy

isn't the link provided in this answer (the first one) the solution appropriate? It the connection in the pool exists , then it's not supposed to be recreated right? – Eponymy

Answer 3

Since commenting does not work for me, here a hint from the documentation of NestJS:

https://docs.nestjs.com/techniques/database#async-configuration

I am not using NestJS but reading the docs at the moment to decide, if it's a fitting framework for us. We have an app where only some modules have multi tenancy with schema per tenant, so using TypeOrmModule.forRootAsync(dynamicCreatedDbConfig) might be an option for me too.

This may help you if you have an interceptor or middleware, which prepares the dynamicCreatedDbConfig data before...

Impassive

sorry, but this does not answer my question. – Chrissa