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