How to Connect Cube.js in Bold BI

Purpose

This article explains how to connect Cube.js to Bold BI using the PostgreSQL connector. Cube.js acts as a semantic layer that exposes a PostgreSQL-compatible SQL API, allowing Bold BI to query Cube data models using standard SQL.

About Cube.js

Cube.js is a semantic layer and analytics API platform that sits between your database and BI tools. Cube.js models your data and exposes it through multiple APIs, including a PostgreSQL-compatible SQL interface.

The objective of this guide is to establish a connection between Cube.js and Bold BI.

Prerequisites

Cube.js Installation Using Docker

Cube projects can be created via Docker Compose (recommended). The official documentation shows how to create a Cube project and open the Developer Playground.

Refer to Create a project | Cube Docs.

1. Open PowerShell
2. Create a directory for your Cube.js project

mkdir cube-demo

3. Navigate to the directory

cd cube-demo

4. Create a docker-compose.yml file

notepad docker-compose.yml

Ensure the following environment variable is set to enable Development Mode:

CUBEJS_DEV_MODE=true

5. Run the Docker command to start Cube.js

docker compose up -d

6. Open Cube Playground

http://localhost:4000/

Choose a database and enter valid credentials.

Select tables and click Generate Data Model → Yaml → Build.

Expressions and measures can be created by editing the YAML files (example: teachers.yml).

PostgreSQL Database

Cube.js supports PostgreSQL as a native source. Cube automatically reads tables and allows generating schema files. Cube supports PostgreSQL, MySQL, SQL Server, Oracle, and other transactional databases.

Refer Postgres | Cube Docs

Architecture Overview

Bold BI → PostgreSQL Connector → Cube.js SQL API → Source Database

• Bold BI connects using PostgreSQL driver
• Cube.js exposes a PostgreSQL-compatible SQL endpoint
• Cube internally queries your actual database

Enable SQL API in Cube.js

To allow Bold BI to connect, you must enable the SQL API in Cube.js. If you are running Cube.js using Docker, update your docker-compose.yml file.

Required Environment Variables

Sample docker-compose.yml Configuration

version: "3.8"

services:
  cube:
    image: cubejs/cube:latest
    container_name: cube
    ports:
      - "4000:4000"
      - "15432:15432"
    environment:
      - CUBEJS_DEV_MODE=true
      - CUBEJS_DB_TYPE=postgres
      - CUBEJS_DB_HOST=<your_database_host>
      - CUBEJS_DB_NAME=<your_database_name>
      - CUBEJS_DB_USER=<your_database_user>
      - CUBEJS_DB_PASS=<your_database_password>
      - CUBEJS_API_SECRET=<your_api_secret>

      - CUBEJS_SQL_API=true
      - CUBEJS_SQL_PORT=15432
      - CUBEJS_SQL_USER=cube
      - CUBEJS_SQL_PASSWORD=supersecret

SQL API Configuration Checklist

Before connecting Bold BI, ensure that the Cube.js SQL API is enabled. Verify that the following environment variables are configured:

• CUBEJS_SQL_API=true
• CUBEJS_SQL_PORT=15432
• CUBEJS_SQL_USER=cube
• CUBEJS_SQL_PASSWORD=supersecret

Configure Connection in Bold BI

Bold BI Environment

Bold BI supports two SQL connection modes:

• Live Mode (direct, real-time queries)
• Extract Mode (scheduled refresh)

Click Data Sources → New Data Source as shown below.

Search for PostgreSQL as shown below.

PostgreSQL Connection Settings

• Server Name – IP address or hostname where Cube.js is running
• Port – 15432
• Database Name – default
• Username – cube
• Password – supersecret
• SSL Mode – Prefer / Disable (based on your setup)

After connecting, you will be redirected to the query designer page where your Cube schema will appear as tables.

Click on the Hamburger Menu icon in the right-side corner and click on Continue to Dashboard to create a dashboard using the created data source.

Important Notes / Limitations – Cube.js

• Some advanced SQL features (nested queries, window functions depending on Cube version) may have limitations
• Rank-based Top N filtering at widget level may not work as expected
• Cube.js returns modeled data only (not raw database tables)
• Cube naming conventions do not support spaces in dimension names

Troubleshooting

Connection Refused

• Ensure port 15432 is exposed
• Ensure firewall or security rules allow access
• Confirm Cube.js container is running

Authentication Failed

Error Description

Bold BI is able to reach the Cube.js SQL API endpoint, but the connection is rejected due to invalid credentials.

Possible Causes

• Incorrect CUBEJS_SQL_USER value
• Incorrect CUBEJS_SQL_PASSWORD value
• Environment variables not applied to Cube.js container
• Cube.js container not restarted after configuration changes

Resolution

• Verify CUBEJS_SQL_USER matches the username used in Bold BI
• Verify CUBEJS_SQL_PASSWORD matches the password used in Bold BI
• Confirm environment variables are correctly defined in docker-compose.yml
• Restart the Cube.js container after making changes