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.

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.

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.