db2dbml doesn't mark Primary Key Composite Indexes with `pk` attribute

[nicolashery]

Summary

When using db2dbml (from @dbml/cli) to generate DBML from a PostgreSQL database, primary key composite indexes are not marked with the pk attribute in the generated DBML output.

Expected Behavior

Primary key composite indexes should be marked with the pk attribute in the DBML Indexes block, like this:

Table "product" {
  "id" uuid [pk, not null, default: `gen_random_uuid()`]
  "name" varchar [not null]
  "price" numeric(10,2) [not null]

  Indexes {
    name [type: btree, name: "idx_product_name"]
  }
}

Table "order_item" {
  "order_id" uuid [not null]
  "product_id" uuid [not null]
  "quantity" int4 [not null]

  Indexes {
    (order_id, product_id) [pk, type: btree, name: "order_item_pkey"] // <-- Should have pk marker
    quantity [type: btree, name: "idx_order_item_quantity"]
  }
}

Actual Behavior

Primary key composite indexes are rendered without the pk attribute:

Table "product" {
  "id" uuid [pk, not null, default: `gen_random_uuid()`]
  "name" varchar [not null]
  "price" numeric(10,2) [not null]

  Indexes {
    name [type: btree, name: "idx_product_name"]
  }
}

Table "order_item" {
  "order_id" uuid [not null]
  "product_id" uuid [not null]
  "quantity" int4 [not null]

  Indexes {
    (order_id, product_id) [type: btree, name: "order_item_pkey"] // <-- Missing pk marker
    quantity [type: btree, name: "idx_order_item_quantity"]
  }
}

Possible Cause

The @dbml/connector package does not seem to include primary: true in the index objects when extracting schema from PostgreSQL. The intermediate schema JSON looks like this:

{
  "public.order_item": [
    {
      "name": "order_item_pkey",
      "type": "btree",
      "columns": [
        {
          "type": "column",
          "value": "order_id"
        },
        {
          "type": "column",
          "value": "product_id"
        }
      ]
      // Missing: "primary": true
    },
  ]
}

Without the primary field in the schema JSON, @dbml/core's importer.generateDbml() cannot know to add the pk marker to the index in the DBML output.

How to Reproduce

1. Set up PostgreSQL database

# Create a test database
createdb dbml_test

# Apply the minimal reproduction schema
psql dbml_test < minimal_repro_schema.sql

-- minimal_repro_schema.sql

-- Drop tables if they exist
DROP TABLE IF EXISTS product CASCADE;
DROP TABLE IF EXISTS order_item CASCADE;

-- Table with single-column primary key
CREATE TABLE product (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    name VARCHAR NOT NULL,
    price DECIMAL(10, 2) NOT NULL
);

-- Table with composite primary key
CREATE TABLE order_item (
    order_id UUID NOT NULL,
    product_id UUID NOT NULL,
    quantity INTEGER NOT NULL,
    PRIMARY KEY (order_id, product_id)
);

-- Add a regular (non-PK) index for comparison
CREATE INDEX idx_product_name ON product(name);
CREATE INDEX idx_order_item_quantity ON order_item(quantity);

2. Run db2dbml to generate DBML

npm install -g @dbml/cli

db2dbml postgres 'postgresql://localhost:5432/dbml_test?schemas=public' -o output.dbml

3. Inspect the output

# View the generated DBML
cat output.dbml

Look at the Indexes blocks for product and order_item tables. Notice that primary key index (order_id, product_id) does NOT have the pk marker:

4. (Optional) Inspect the intermediate schema JSON

Using the Node.js script below, you can see that the connector doesn't set pk: true on index objects:

const { connector } = require('@dbml/connector');

async function inspectSchema() {
  const schemaJson = await connector.fetchSchemaJson(
    'postgres://localhost:5432/dbml_test',
    'postgres'
  );

  // Look at the product table's indexes
  console.log(JSON.stringify(schemaJson.indexes['public.product'], null, 2));

  // Notice: no "pk" or "unique" fields on the primary key index
}

inspectSchema();

Notice no "primary" field on the the primary key composite index "order_item_pkey".

[huydo862003]

Hi @nicolashery, thank you for your effort in reporting this bug. We will look into this bug and will notify when the bug is fixed.

[huydo862003]

Hi @nicolashery, this has been fixed in @dbml/cli v5.2.0. Kindly upgrade the package and the problem should be fixed.