Using Different Schemas for Tenants
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.
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' } },
],
};
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`,
...
});
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
}
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',
},
];
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?