Schema based Multi tenancy in Loopback 4

Question

Use case

In schema based multi-tenant applications there normally is one database with multiple schemas. One schema is the main where the common application data is stored and one for each tenant of the application. 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. When a request enter the system the user is validated and a schema is selected using the data on the main schema. And then most/all subsequent database operations go to the tenant specific schema. As you can see the schema we want to use is only known at run time.

Problem

How to select schema at runtime? We are using postgres connector. We should be able to switch schemas at runtime.

Another problem is how to run migrations for different tenants?

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.

Answer 1

you can create the DefaultCrudRepository ( Default implementation of CRUD repository using legacy juggler model and data source) at runtime which needs two parameter

1. Datasource
2. Model

Now instantiate Datasource with required settings includes that schema which you want to use Then provide your model and data source to DefaultCrudRepository instance like below:-

const ds = new PostgresDataSource({
  connector: 'postgresql',
  host: 'some host',
  port: 'some port',
  user: 'some user',
  password: 'password',
  database: 'database',
  schema: 'schema for that particular tenant',
});
const repo =  new DefaultCrudRepository(SomeModel, ds);

and after that use this repository to execute find, create and other methods. There is a sample implementation for this https://github.com/hitesh2067/dynamic-schema-example

I have passed the schema as query param but you can use any other way to provide the schema

Updated Solution:-

Now that Loopback has provided Middleware and better Context separation for the application context and request context. we can use that to connect to any datasource dynamically (hoping that connector is installed in the package.json )and bind it to Application context then temporarily bind that binding to the datasource which is pointed by UserRepository (or any of your multi tenant Repo) in request context.

Example for this is written on https://github.com/dev-hitesh-gupta/loopback4-multi-tenant-multi-datasource-example

Implementation will be like

const tenant = await this.getTenant(requestCtx);
if (tenant == null) return;
const tenantData = await this.tenantRepo.findById(tenant.id);
requestCtx.bind(CURRENT_TENANT).to(tenantData);
const tenantDbName = `datasources.multi-tenant-db.${tenantData.id}`;
// adding datasource if not present
if (!this.application.isBound(tenantDbName)) {
  const tenantDb = new juggler.DataSource({
    name: tenantDbName,
    ...tenantData.dbConfig,
  });
  this.application.bind(tenantDbName).to(tenantDb).tag('datasource');
}
// Pointing to a default datasource in request context
requestCtx
  .bind('datasources.multi-tenant-db')
  .toAlias(tenantDbName);