Schema generation omits primary key dimensions for PostgreSQL tables, causing compile errors when joins are generated
- Dominant language
- Rust
- Stars
- 20.8k
- Forks
- 2.1k
- Avg merge
- 1d 2h
- Merged PRs (30d)
- 181
Description
### **Environment**
Cube Version: Latest (cloned from cube-js/cube)
Deployment: Docker
Database: PostgreSQL
Sample Database: Chinook
Schema Format: JavaScript
### **Problem**
When using Generate Data Model from the Playground, Cube successfully generates JavaScript schema files but fails to compile them because the generated cubes do not include primary key dimensions.
The generated cubes contain joins, but no dimension is marked as primary_key: true, resulting in compilation errors.
### Example error:
**Compile errors:**
- album cube: primary key for 'album' is required when join is defined
- invoice cube: primary key for 'invoice' is required when join is defined
- invoice_line cube: primary key for 'invoice_line' is required when join is defined
- playlist_track cube: primary key for 'playlist_track' is required when join is defined
- track cube: primary key for 'track' is required when join is defined
### **Steps to Reproduce**
- Clone the latest Cube repository.
- Start Cube using Docker.
- Connect a PostgreSQL database (tested with the Chinook sample database).
- Open Playground.
- Click Generate Data Model.
- Select JavaScript format.
- Generate schema.
### **Expected Behavior**
Generated cubes should include primary key dimensions.
Example:
dimensions: {
album_id: {
sql: `album_id`,
type: `number`,
primary_key: true
},
title: {
sql: `title`,
type: `string`
}
}
### **Actual Behavior**
Generated schema:
cube(`album`, {
sql_table: `public.album`,
joins: {
artist: {
sql: `${CUBE}.artist_id = ${artist}.artist_id`,
relationship: `many_to_one`
}
},
dimensions: {
title: {
sql: `title`,
type: `string`
}
}
});
The primary key dimension (album_id) is missing.
### **Database Metadata**
The PostgreSQL database correctly exposes primary keys.
Example query:
SELECT
tc.table_name,
kcu.column_name
FROM information_schema.table_constraints tc
JOIN information_schema.key_column_usage kcu
ON tc.constraint_name = kcu.constraint_name
WHERE tc.constraint_type = 'PRIMARY KEY';
Result:
album album_id
artist artist_id
customer customer_id
employee employee_id
genre genre_id
invoice invoice_id
invoice_line invoice_line_id
media_type media_type_id
playlist playlist_id
playlist_track playlist_id
playlist_track track_id
track track_id
### **Investigation**
While debugging the source code:
- driver.tablesSchema() is used as the source for schema generation.
- ScaffoldingSchema expects primary keys to be available via:
column.attributes?.includes("primaryKey")
However, the generated schema does not contain any primary key dimensions, suggesting that the metadata passed into the scaffolding process may not contain the expected primaryKey attribute.
### **Additional Observation**
I had previously worked around this issue by replacing the older schema introspection implementation with TableSchemaV2(), after which primary keys were generated correctly.
This suggests there may be a regression or inconsistency between the current schema introspection implementation and the scaffolding generator.
### **Questions**
- Is this expected behavior?
- Has TableSchema been superseded by TableSchemaV2 for schema generation?
- Could this be a regression in the latest Playground schema generator?
### **Additional Information**
- PostgreSQL correctly exposes primary key metadata.
- Foreign keys are generated correctly.
- Joins are generated correctly.
- Only the primary key dimensions are omitted, causing schema compilation to fail.
Contributor guide
Research direction
Start with driver.tablesSchema() and the ScaffoldingSchema check for column.attributes?.includes("primaryKey"), then compare that metadata with the TableSchemaV2() path mentioned in the investigation. Reproduce through Playground's Generate Data Model using JavaScript against PostgreSQL; done means generated cubes include primary-key dimensions and compile successfully with their joins.
Written by the indexing model from the issue text.
Assessment
- Tech stack
- javascript, postgresql
- Domain
- backend, databases
- Issue type
- Bug
- Difficulty
- 4/5
- Estimated time
- 3-5 days
- Activity status
- Active
- Clarity
- Mostly clear
- Newbie friendliness
- 55/100