Description

I’m trying to extract the database schema from a Postgres instance connected to Cube (CubeJS), but none of the approaches I tried work correctly.

My goal is to reliably retrieve tables/columns plus primary keys and foreign keys, so I can generate Data Models programmatically (or at least validate/derive relationships). However:

1) playground/db-schema endpoint

I tried the Playground endpoint playground/db-schema (I understand this might not be intended for production use), but the output does not correctly include primary keys or foreign keys. Relationships appear incomplete/incorrect.

2) Querying information_schema via CubeSQL API

I tried using the CubeSQL API endpoint:

POST /cubejs-api/v1/cubesql { "query": "SELECT * FROM information_schema.constraint_column_usage" }

But it returns no rows at all (empty result), even though the underlying Postgres database definitely has constraints.

3) Importing/using the server library (introspection during cube build)

I also tried importing the server library and noticed the failure seems related to accessing certain system tables/views (like the example above). When those queries fail or return empty, cubes cannot be built correctly / the introspection-based parts break.

Environment

• Database: PostgreSQL
• Deployment: Docker / Node
• Works the same with existing Data Models and without any Data Models (problem persists either way)

Expected behavior

• A supported/reliable way to fetch DB schema metadata from Cube for Postgres, including:

  • tables and columns
  • primary keys
  • foreign keys / relationships

• information_schema queries should return expected rows when executed via CubeSQL (or clear documentation if they are intentionally blocked).

• If playground/db-schema is not intended to be used, guidance on the correct endpoint / recommended approach.

Actual behavior

• playground/db-schema returns incomplete schema data (PK/FK missing or incorrect).
• SELECT * FROM information_schema.constraint_column_usage via /cubejs-api/v1/cubesql returns no rows.
• Introspection seems to fail on certain metadata tables/views, preventing correct cube/data model generation.

Questions

1. Is this a known limitation/bug for Postgres schema introspection in Cube?
2. Are information_schema / pg_catalog queries restricted/filtered when running through CubeSQL? If so, is there a way to allow them?
3. What is the recommended/supported way to query a database schema through Cube?
4. Is there an official way to create Data Models programmatically (or generate them from schema metadata) that Cube supports?

Reproduction steps (high level)

1. Connect Cube to a Postgres DB with PK/FK constraints.

2. Call playground/db-schema and check whether PK/FK are present/correct.

3. Call /cubejs-api/v1/cubesql with:

   SELECT * FROM information_schema.constraint_column_usage;

4. Observe empty results and incomplete/failed introspection.

Additional info / logs

If needed, I can provide:

• Cube logs around schema introspection / cube build
• Postgres version and sample schema (sanitized)
• The exact Cube config and connection settings