Guides
Data sources
Using multiple data sources

Using multiple data sources

Use case

We need to access the data from different data sources for different tenants. For example, we are the platform for the online website builder, and each client can only view their data. The same data model is used for all clients.

Configuration

Each client has its own database. In this recipe, the Mango Inc tenant keeps its data in the remote ecom database while the Avocado Inc tenant works with the local database (bootstrapped in the docker-compose.yml file) which has the same data model.

To enable multitenancy, use the contextToAppId function to provide distinct identifiers for each tenant. Also, implement the driverFactory function where you can select a data source based on the tenant name. JSON Web Token includes information about the tenant name in the tenant property of the securityContext.

module.exports = {
  // Provides distinct identifiers for each tenant which are used as caching keys
  contextToAppId: ({ securityContext }) =>
    `CUBEJS_APP_${securityContext.tenant}`,
 
  // Selects the database connection configuration based on the tenant name
  driverFactory: ({ securityContext }) => {
    if (!securityContext.tenant) {
      throw new Error("No tenant found in Security Context!");
    }
 
    if (securityContext.tenant === "Avocado Inc") {
      return {
        type: "postgres",
        database: "localDB",
        host: "postgres",
        user: "postgres",
        password: "example",
        port: "5432",
      };
    }
 
    if (securityContext.tenant === "Mango Inc") {
      return {
        type: "postgres",
        database: "ecom",
        host: "demo-db.cube.dev",
        user: "cube",
        password: "12345",
        port: "5432",
      };
    }
 
    throw new Error("Unknown tenant in Security Context");
  },
};

Query

To get users for different tenants, we will send two identical requests with different JWTs. Also, we send a query with unknown tenant to show that he cannot access to the data model of other tenants.

// JWT payload for "Avocado Inc"
{
  sub: "1234567890",
  tenant: "Avocado Inc",
  iat: 1000000000,
  exp: 5000000000,
}
// JWT payload for "Mango Inc"
{
  sub: "1234567890",
  tenant: "Mango Inc",
  iat: 1000000000,
  exp: 5000000000,
}
// JWT payload for "Peach Inc"
{
  sub: "1234567890",
  tenant: "Peach Inc",
  iat: 1000000000,
  exp: 5000000000,
}

Result

We have received different data from different data sources depending on the tenant's name:

// Avocado Inc last users:
[
  {
    "Users.id": 700,
    "Users.name": "Freddy Gulgowski",
  },
  {
    "Users.id": 699,
    "Users.name": "Julie Crooks",
  },
  {
    "Users.id": 698,
    "Users.name": "Macie Ryan",
  },
]
// Mango Inc last users:
[
  {
    "Users.id": 705,
    "Users.name": "Zora Vallery",
  },
  {
    "Users.id": 704,
    "Users.name": "Fawn Danell",
  },
  {
    "Users.id": 703,
    "Users.name": "Moyra Denney",
  },
];
// Peach Inc error:
{ error: "Error: Unknown tenant in Security Context" }

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.