Using Different Schemas for Tenants
Use case
We want to provide different data schemas to different tenants. In the recipe below, we'll learn how to switch between multiple data schemas based on the tenant.
Configuration
We have a folder structure as follows:
schema
├── avocado
│ └── Products.js
└── mango
└── Products.js
Let's configure Cube to use a specific data schema 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 schema compilation result is cached and provide the tenant names via the
scheduledRefreshContexts
function
so a refresh worker can find all existing schemas and build pre-aggregations for them, if needed.
Our cube.js
file will look like this:
const FileRepository = require('@cubejs-backend/server-core/core/FileRepository');
module.exports = {
contextToAppId: ({ securityContext }) =>
`CUBEJS_APP_${securityContext.tenant}`,
repositoryFactory: ({ securityContext }) =>
new FileRepository(`schema/${securityContext.tenant}`),
scheduledRefreshContexts: () => [
{ securityContext: { tenant: 'avocado' } },
{ securityContext: { tenant: 'mango' } },
]
};
Data schema
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:
// schema/avocado
cube(`Products`, {
sql: `SELECT * FROM public.Products WHERE MOD (id, 2) = 1`,
...
});
// schema/mango
cube(`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
or run it with the docker-compose up
command. You'll see the result, including
queried data, in the console.
Did you find this page useful?