cube-js / cube-js/cube

Generate Data Model does not emit primary_key: true for PostgreSQL tables with named PKs

Open
#10,517 0 comments 0 reactions 0 assignees View on GitHub
Dominant language
Rust
Stars
20.8k
Forks
2.1k
Avg merge
1d 2h
Merged PRs (30d)
181

Description

## Bug Description

When using **Generate Data Model** in Cube Playground (v1.6.23, PostgreSQL), primary key columns are not marked with `primary_key: true` in the generated YAML. This causes compile errors for any cube that has `joins` defined:

```
salesorderheader cube: primary key for 'salesorderheader' is required when join is defined
salesorderdetail cube: primary key for 'salesorderdetail' is required when join is defined
... (dozens more)
```

## Root Cause

`DevServer.ts` calls `driver.tablesSchema()` in both the `/playground/db-schema` and `/playground/generate-schema` endpoints:

```typescript
// Line 141
const tablesSchema = await driver.tablesSchema();

// Line 179
const tablesSchema = req.body.tablesSchema || (await driver.tablesSchema());
```

`tablesSchema()` only queries `information_schema.columns` and does **not** fetch primary key constraints. As a result, no column ever gets `attributes: ['primaryKey']`, so `ScaffoldingSchema.dimensions()` never sets `isPrimaryKey: true`, and `BaseSchemaFormatter` never emits `primary_key: true`.

The correct method is `tablesSchemaV2()`, which calls `primaryKeys()` (overridden in `PostgresDriver` with a real `information_schema.table_constraints` query) and merges the results into the column data with `attributes: ['primaryKey']`.

## Steps to Reproduce

1. Connect Cube Playground to a PostgreSQL database (e.g. AdventureWorks)
2. Go to **Data Model → Generate Data Model**
3. Select any table with a join (e.g. `sales.salesorderheader`)
4. Click **Generate**
5. Observe the generated YAML has no `primary_key: true` dimension
6. Cube fails to compile with: `primary key for 'X' is required when join is defined`

## Affected Tables / Columns

The bug affects all tables where the PK column name is **not literally `id`**. The scaffolding has a fallback heuristic `fixCase(column.name) === 'id'` but real-world tables use names like `salesorderid`, `productid`, `businessentityid`, etc.

## Expected Behavior

Generated YAML should include the PK dimension marked as `primary_key: true`:

```yaml
dimensions:
- name: salesorderid
sql: salesorderid
type: number
primary_key: true
public: false
```

## Fix

In `packages/cubejs-server-core/src/core/DevServer.ts`, replace both calls to `tablesSchema()` with `tablesSchemaV2()`:

```diff
- const tablesSchema = await driver.tablesSchema();
+ const tablesSchema = await driver.tablesSchemaV2();
```

```diff
- const tablesSchema = req.body.tablesSchema || (await driver.tablesSchema());
+ const tablesSchema = req.body.tablesSchema || (await driver.tablesSchemaV2());
```

## Environment

- Cube version: 1.6.23
- Database: PostgreSQL 14.10
- Database: AdventureWorks (timchapman/postgresql-adventureworks)

Contributor guide

Open the contributing guide

Assessment

This issue has not been assessed yet.

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.