How can i connect multiple SQL databases on cube -- ability to work with multiple data source during data model generation

[SheryarKhaliq125]

i've updated sql credentials for the cube in the .env file. It successfully accesses the default datasource, but it completely disregards the 2nd datasource.

.env file:

CUBEJS_DB_HOST=matomo.HIDDEN CUBEJS_DB_PORT=3306 CUBEJS_DB_NAME=matomo CUBEJS_DB_USER=tesdb CUBEJS_DB_PASS=HIDDEN CUBEJS_DB_TYPE=mysql CUBEJS_DS_DATASOURCE1_DB_HOST=matomo.HIDDEN CUBEJS_DS_DATASOURCE1_DB_PORT=3306 CUBEJS_DS_DATASOURCE1_DB_NAME=usdata CUBEJS_DS_DATASOURCE1_DB_USER=testdb2 CUBEJS_DS_DATASOURCE1_DB_PASS=HIDDEN CUBEJS_DS_DATASOURCE1_DB_TYPE=mysql CUBEJS_API_SECRET=HIDDEN CUBEJS_EXTERNAL_DEFAULT=true CUBEJS_SCHEDULED_REFRESH_DEFAULT=true CUBEJS_DEV_MODE=true CUBEJS_SCHEMA_PATH=model

It will only show "matomo" db

if I set the default datasource with datasource1 credentials, only the "usdata" database will be displayed. The Cube application entirely disregards datasource1.

[tokarenko]

@SheryarKhaliq125 , I tried to set up two databases recently. Used the following references:

• https://github.com/cube-js/cube/tree/eeda56aae8ea2c4e3a62a27633834da8e1668279/examples/recipes/joining-multiple-datasources-data
• https://github.com/cube-js/cube/tree/eeda56aae8ea2c4e3a62a27633834da8e1668279/examples/recipes/joining-multiple-databases-data

I ended up with the following in my ~/cube/conf/cube.js:

const PostgresDriver = require('@cubejs-backend/postgres-driver').PostgresDriver;
const arango_db_driver = require("arangodb-cubejs-driver/dist/arangodb-driver.js").ArangoDbDriver;
const base_driver = require("@cubejs-backend/query-orchestrator").BaseDriver;

module.exports = {
  dbType: ({ dataSource }) => {
    switch (dataSource) {
      case 'ArangoDB': return process.env.CUBEJS_DB_ARANGO_TYPE;
      default: return process.env.CUBEJS_DB_TYPE;
    }
  },

  driverFactory: ({ dataSource }) => {
    switch(dataSource) {
      case 'ArangoDB':
        // Workaround for the absence of inheritance from BaseDriver
        Object.setPrototypeOf(arango_db_driver.prototype,base_driver.prototype)

        return new arango_db_driver({
          url: process.env.CUBEJS_DB_ARANGO_URL,
          databaseName: process.env.CUBEJS_DB_ARANGO_NAME,
          auth: {
            username: process.env.CUBEJS_DB_ARANGO_USER,
            password: process.env.CUBEJS_DB_ARANGO_PASS,
          },
        });
      default:
        // Should have worked without this line, but it fails to load when switching from "Schema" to "Build" tabs...
        Object.setPrototypeOf(PostgresDriver.prototype,base_driver.prototype)

        return new PostgresDriver({
        //type: process.env.CUBEJS_DB_TYPE,
        host: process.env.CUBEJS_DB_HOST,
        port: process.env.CUBEJS_DB_PORT,
        database: process.env.CUBEJS_DB_NAME,
        user: process.env.CUBEJS_DB_USER,
        password: process.env.CUBEJS_DB_PASS,
      });
    }
  },
};

[SheryarKhaliq125]

It didn't work, can you please provide a code for cube.js to connect 2 MySQL databases simultaneously.

[igorlukanin]

@tokarenko Thanks for the suggestion! While your approach makes sense, it might be an overkill in this specific case :smile:

@SheryarKhaliq125 Please take a look in the docs: https://cube.dev/docs/product/configuration/advanced/multiple-data-sources#environment-variables

Your configuration above makes sense. However, you should also define the CUBEJS_DATASOURCES environment variable like this:

CUBEJS_DATASOURCES=default,datasource1

It should help resolve the issue.

[vibudhm]

@igorlukanin I'm experiencing a similar issue while trying to connect/setup multiple data sources in the .env file. I'm connecting to Snowflake as default and postgres database as datasource1. Here is what my .env file looks like.

CUBEJS_DATASOURCES=default,datasource1
CUBEJS_DB_USER=snowflakeuser
CUBEJS_DB_PASS=randompassword
CUBEJS_DB_NAME=snowflakedatabase
CUBEJS_DB_SNOWFLAKE_ACCOUNT=abc12345
CUBEJS_DB_SNOWFLAKE_REGION=us-east-1
CUBEJS_DB_SNOWFLAKE_WAREHOUSE=TEST_WH
CUBEJS_DB_SNOWFLAKE_ROLE=USER_ROLE
CUBEJS_DB_TYPE=snowflake
CUBEJS_DS_DATASOURCE1_DB_TYPE=postgres
CUBEJS_DS_DATASOURCE1_DB_NAME=ecom
CUBEJS_DS_DATASOURCE1_DB_HOST=host.docker.internal
CUBEJS_DS_DATASOURCE1_DB_USER=user1
CUBEJS_DS_DATASOURCE1_DB_PASS=randompassword
CUBEJS_DS_DATASOURCE1_DB_PORT=5432
CUBEJS_API_SECRET=HIDDEN
CUBEJS_EXTERNAL_DEFAULT=true
CUBEJS_DEV_MODE=true
CUBEJS_SCHEMA_PATH=model

In the playground I only see the datasource that is defined as default whether it's snowflake or postgres. The other datasource won't show up. I have tried switching them over to verify if there is any issue with the environment variable values. Would you please suggest a possible fix?

[SheryarKhaliq125]

Same i also added CUBEJS_DATASOURCES=default,datasource1 but still multiple databases not showing up.

[okue]

I'm facing the same issue.

[keydunov]

Can you please provide your data model along with env variables.

[vibudhm]

@keydunov Are the following variables good?

CUBEJS_DATASOURCES=default,datasource1
CUBEJS_DB_USER=snowflakeuser
CUBEJS_DB_PASS=randompassword
CUBEJS_DB_NAME=snowflakedatabase
CUBEJS_DB_SNOWFLAKE_ACCOUNT=abc12345
CUBEJS_DB_SNOWFLAKE_REGION=us-east-1
CUBEJS_DB_SNOWFLAKE_WAREHOUSE=TEST_WH
CUBEJS_DB_SNOWFLAKE_ROLE=USER_ROLE
CUBEJS_DB_TYPE=snowflake
CUBEJS_DS_DATASOURCE1_DB_TYPE=postgres
CUBEJS_DS_DATASOURCE1_DB_NAME=ecom
CUBEJS_DS_DATASOURCE1_DB_HOST=host.docker.internal
CUBEJS_DS_DATASOURCE1_DB_USER=user1
CUBEJS_DS_DATASOURCE1_DB_PASS=randompassword
CUBEJS_DS_DATASOURCE1_DB_PORT=5432
CUBEJS_API_SECRET=HIDDEN
CUBEJS_EXTERNAL_DEFAULT=true
CUBEJS_DEV_MODE=true
CUBEJS_SCHEMA_PATH=model

[paveltiunov]

Might be related to #6890

[vibudhm]

Might be related to #6890

@paveltiunov It didn't help. the parameters I used, have the UPPERCASE convention per the documentation. I'm still not able to see both datasources in the playground.

[KonstantinPodkopaev]

@igorlukanin I'm experiencing a similar issue while trying to connect/setup multiple data sources in the .env file. I'm connecting to Snowflake as default and postgres database as datasource1. Here is what my .env file looks like.

CUBEJS_DATASOURCES=default,datasource1
CUBEJS_DB_USER=snowflakeuser
CUBEJS_DB_PASS=randompassword
CUBEJS_DB_NAME=snowflakedatabase
CUBEJS_DB_SNOWFLAKE_ACCOUNT=abc12345
CUBEJS_DB_SNOWFLAKE_REGION=us-east-1
CUBEJS_DB_SNOWFLAKE_WAREHOUSE=TEST_WH
CUBEJS_DB_SNOWFLAKE_ROLE=USER_ROLE
CUBEJS_DB_TYPE=snowflake
CUBEJS_DS_DATASOURCE1_DB_TYPE=postgres
CUBEJS_DS_DATASOURCE1_DB_NAME=ecom
CUBEJS_DS_DATASOURCE1_DB_HOST=host.docker.internal
CUBEJS_DS_DATASOURCE1_DB_USER=user1
CUBEJS_DS_DATASOURCE1_DB_PASS=randompassword
CUBEJS_DS_DATASOURCE1_DB_PORT=5432
CUBEJS_API_SECRET=HIDDEN
CUBEJS_EXTERNAL_DEFAULT=true
CUBEJS_DEV_MODE=true
CUBEJS_SCHEMA_PATH=model

In the playground I only see the datasource that is defined as default whether it's snowflake or postgres. The other datasource won't show up. I have tried switching them over to verify if there is any issue with the environment variable values. Would you please suggest a possible fix?

Hi! Have you finally solved it? I've got the same problem :(

[vibudhm]

@igorlukanin I'm experiencing a similar issue while trying to connect/setup multiple data sources in the .env file. I'm connecting to Snowflake as default and postgres database as datasource1. Here is what my .env file looks like.

CUBEJS_DATASOURCES=default,datasource1
CUBEJS_DB_USER=snowflakeuser
CUBEJS_DB_PASS=randompassword
CUBEJS_DB_NAME=snowflakedatabase
CUBEJS_DB_SNOWFLAKE_ACCOUNT=abc12345
CUBEJS_DB_SNOWFLAKE_REGION=us-east-1
CUBEJS_DB_SNOWFLAKE_WAREHOUSE=TEST_WH
CUBEJS_DB_SNOWFLAKE_ROLE=USER_ROLE
CUBEJS_DB_TYPE=snowflake
CUBEJS_DS_DATASOURCE1_DB_TYPE=postgres
CUBEJS_DS_DATASOURCE1_DB_NAME=ecom
CUBEJS_DS_DATASOURCE1_DB_HOST=host.docker.internal
CUBEJS_DS_DATASOURCE1_DB_USER=user1
CUBEJS_DS_DATASOURCE1_DB_PASS=randompassword
CUBEJS_DS_DATASOURCE1_DB_PORT=5432
CUBEJS_API_SECRET=HIDDEN
CUBEJS_EXTERNAL_DEFAULT=true
CUBEJS_DEV_MODE=true
CUBEJS_SCHEMA_PATH=model

In the playground I only see the datasource that is defined as default whether it's snowflake or postgres. The other datasource won't show up. I have tried switching them over to verify if there is any issue with the environment variable values. Would you please suggest a possible fix?

Hi! Have you finally solved it? I've got the same problem :(

No luck yet. @paveltiunov any suggestions here on how we can make multiple datasources show up on the playground?

[paveltiunov]

@vibudhm There's no way to generate data models for data sources other than for default data sources in the OSS playground. You can only query them through Cubes with different data sources.

[vibudhm]

@paveltiunov thanks for getting back. Would you please suggest what is the best way to handle multiple datasources to generate data models in the playground? I'm currently using playground as client to generate queries and test. To build data models here is a workaround I'm using; by connecting individual datasource as default, create data models and saving models to a separate folder to make sure files persist after switching out the envars with other datasource. Do you have suggestions for a better approach? thanks!

[RipperTs]

I'm facing the same issue.

[SarahT-BI]

Having the same issue here

[github-actions[bot]]

If you are interested in working on this issue, please leave a comment below and we will be happy to assign the issue to you. If this is the first time you are contributing a Pull Request to Cube.js, please check our contribution guidelines. You can also post any questions while contributing in the #contributors channel in the Cube.js Slack.

[paveltiunov]

To summarize, when you connect multiple data sources, Playground will still show only one right now. There's currently no support to show all of the connected data sources in the playground during data model generation.

[wzcyc]

it works as workaround for me.

I just lanuch another cube only another data source (clickhouse). Use this new cube core instance to create new data model file. the .env file setting as mentioned as previous NEED to add new datasource at . step1： modify the file .env CUBEJSDATASOURCES=default,clickhouse,mysql CUBEJSDS_CLICKHOUSE_DB_TYPE=clickhouse

step 2: stop original cube instance and start the new instance services. need add below entry to xxx.yml cubes: data_source: clickhouse

step 3: copy the new yml to original instance under data model folder

After done that, you can find the new cube file at "data model" web site. then you can query the data at the playground.

hope this helps.

[1043038630]

Having the same issue here

[mattiaz88]

Having the same issue here

[1043038630]

I have already solved this problem, please don't leave To add a new source configuration to the "env" file, you need to add environment variables in the Docker.Pay attention to capitalization issues in configuration,However, it cannot be displayed on the page anymore, but you can access data from multiple sources during modeling

[mattiaz88]

Unfortunately it still doesn't work on my laptop, I have 3 databases in docker-compose enviroment path and also in .env file...and only one I can see from schema web path

Could you please help me?

[igorlukanin]

@mattiaz88 See Pavel's comment above:

To summarize, when you connect multiple data sources, Playground will still show only one right now. There's currently no support to show all of the connected data sources in the playground during data model generation.

Here's what you can do as a workaround:

• connect the first data source as default one, generate the data model
• connect the second one as default, generate the data model
• connect the third one as default, generate the data model
• connect all three data sources, combine generated data models, update data_source options of your cubes accrodingly

It would take a bit of time but I believe it's not too cumbersome.

[MatteoLavina]

I have the same problem with ClickHouse. I followed the latest instructions, but every time, these data models were deleted (or updated) with the last database used. Is there any news about this problem?

[wzcyc]

It works for the lastest version of cubejs.

first set the .env like this:

CUBEJS_DATASOURCES=default,tenant CUBEJS_DB_HOST=host CUBEJS_DB_PORT=port CUBEJS_DB_NAME=databasename CUBEJS_DB_USER=user CUBEJS_DB_PASS=pass CUBEJS_DB_TYPE=clickhouse CUBEJS_API_SECRET=secret CUBEJS_EXTERNAL_DEFAULT=true CUBEJS_SCHEDULED_REFRESH_DEFAULT=true CUBEJS_DEV_MODE=true CUBEJS_SCHEMA_PATH=model CUBEJS_DS_TENANT_DB_TYPE=postgres CUBEJS_DS_TENANT_DB_HOST=host CUBEJS_DS_TENANT_DB_NAME=databasename CUBEJS_DS_TENANT_DB_USER=user CUBEJS_DS_TENANT_DB_PASS=pass

for cube setting, need explict menthion the data source:

cubes:

• name: t_sales_daliy_stats sql_table: t_sales_daliy_stats data_source: default

cubes:

• name: t_store sql_table: t_store data_source: tenant

[amartinezcortizas]

It would take a bit of time but I believe it's not too cumbersome

That workaround worked for me, just pay attention reviewing the right datasource at the docker-compose.yml CUBEJS_DATASOURCES property