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

Hello from the LoopBack team

LoopBack 4 does not offer first-class support for multi-tenancy yet. I opened a GitHub issue to discuss different possible solutions, see loopback-next#5056.

One option is to tweak the model-specific Repository class to set the schema based on the current user. This requires a small enhancement to be implemented in @loopback/repository, therefore this solution will not currently work out of the box. I am cross-posting the relevant part of my comment below, see the GitHub issue for full context.

Lightweight tenant isolation using schemas

In this setup, the authentication layer and all tenants share the same database name and use the same credentials (database user) to access the data. We have 1+N schemas defined in the database: the first schema is used by the authentication layer, plus we have one schema for each tenant. All database queries will use the same LB datasource and thus share the same connection pool.

Implementation wise, we need to tweak the way how a LB4 model is registered with a datasource. Instead of creating the same backing juggler model for all users, we want to create tenant-specific juggler models.

Conceptually, this can be accomplished by tweaking the Repository constructor.

export class ProductRepository extends DefaultCrudRepository<
  Product,
  typeof Product.prototype.id
> {
  constructor(
    @inject('datasources.db') dataSource: juggler.DataSource,
    @inject(SecurityBindings.USER) currentUser: UserProfile,
  ) {
    super(
      // model constructor
      Product, 
      // datasource to use
      dataSource, 
      // new feature to be implemented in @loopback/repository:
      // allow repository users to overwrite model settings
      {schema: currentUser.name},
    );
  }
}

Answer 2

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);