Using different data models for tenants

Use case

We want to provide different data models to different tenants. In the recipe below, we'll learn how to switch between multiple data models based on the tenant.

Configuration

We have a folder structure as follows:

model/
├── avocado/
│   └── cubes
│       └── Products.js
└── mango/
    └── cubes
        └── Products.js

Let's configure Cube to use a specific data model path for each tenant. We'll pass the tenant name as a part of securityContext into the repositoryFactory function.

We'll also need to override the contextToAppId function to control how the data model compilation result is cached and provide the tenant names via the scheduledRefreshContexts function so a refresh worker can find all existing data models and build pre-aggregations for them, if needed.

Our cube.js file will look like this:

const { FileRepository } = require("@cubejs-backend/server-core");
 
module.exports = {
  contextToAppId: ({ securityContext }) =>
    `CUBE_APP_${securityContext.tenant}`,
 
  repositoryFactory: ({ securityContext }) =>
    new FileRepository(`model/${securityContext.tenant}`),
 
  scheduledRefreshContexts: () => [
    { securityContext: { tenant: "avocado" } },
    { securityContext: { tenant: "mango" } },
  ],
};

Data modeling

In this example, we'd like to get products with odd id values for the avocado tenant and with even id values the mango tenant:

This is the products cube for the avocado tenant:

YAML

JavaScript

cubes:
  - name: products
    sql: >
      SELECT * FROM public.Products WHERE MOD (id, 2) = 1

This is the products cube for the mango tenant:

YAML

JavaScript

cubes:
  - name: products
    sql: >
      SELECT * FROM public.Products WHERE MOD (id, 2) = 0

Query

To fetch the products, we will send two identical queries with different JWTs:

{
  "sub": "1234567890",
  "tenant": "Avocado",
  "iat": 1000000000,
  "exp": 5000000000
}

{
  sub: "1234567890",
  tenant: "Mango",
  iat: 1000000000,
  exp: 5000000000,
}

Result

We will receive different data for each tenant, as expected:

// Avocado products
[
  {
    "products.id": 1,
    "products.name": "Generic Fresh Keyboard",
  },
  {
    "products.id": 3,
    "products.name": "Practical Wooden Keyboard",
  },
  {
    "products.id": 5,
    "products.name": "Handcrafted Rubber Chicken",
  },
]

// Mango products:
[
  {
    "products.id": 2,
    "products.name": "Gorgeous Cotton Sausages",
  },
  {
    "products.id": 4,
    "products.name": "Handmade Wooden Soap",
  },
  {
    "products.id": 6,
    "products.name": "Handcrafted Plastic Chair",
  },
]

Source code

Please feel free to check out the full source code (opens in a new tab) or run it with the docker-compose up command. You'll see the result, including queried data, in the console.

Controlling access to cubes and views SQL API and LDAP