Table Constraints not generated for PostgreSQL When Using "cds build --production"

[CHELLARAO-M]

Table Constraints not generated for PostgreSQL

When building a CAP application targeting a PostgreSQL database using the cds build --production command, table constraints (e.g., FK constraints) are not generated in the csn file of gen folder. However, when using the cds deploy --dry --model-only --out cds-model.csn command, these constraints are captured in the generated CSN file.

For example, given the following schema:

using { managed } from '@sap/cds/common';
namespace sap.capire.bookshop;

entity Books : managed {
  key ID   : Integer;
  title    : localized String(111);
  author   : Association to Authors;
}

entity Authors : managed {
  key ID       : Integer;
  name         : String(111);
  books        : Association to many Books on books.author = $self;
}

Package.json

Enabled generation of foreign key constraints
cds.features.assert_integrity = db db-constaints

{
  "name": "Test_pg",
  "version": "1.0.0",
  "description": "A simple CAP project.",
  "repository": "<Add your repository here>",
  "license": "UNLICENSED",
  "private": true,
  "dependencies": {
    "@sap/cds": "^8",
    "express": "^4",
    "@sap/xssec": "^4",
    "@cap-js/postgres": "1.10.5"
  },
  "devDependencies": {
    "@cap-js/cds-types": "^0.9.0",
    "@cap-js/sqlite": "^1",
    "@sap/cds-dk": "^8"
  },
  "scripts": {
    "start": "cds-serve"
  },
  "cds": {
    "requires": {
      "auth": "xsuaa",
      "connectivity": true,
      "destinations": true,
      "application-logging": true
    },
    "features": {
      "assert_integrity": "db"
    }
  }
}

Expected behavior:

The generated csn file for PostgreSQL(gen/pg) should include table constraints.

"sap.capire.bookshop.Books": {
            "kind": "entity",
            "elements": {
                ...
            },
            "$tableConstraints": {
                "referential": {
                    "sap.capire.bookshop.Books_author": {
                        "identifier": "c__sap.capire.bookshop.Books_author",
                        "foreignKey": [
                            "author_ID"
                        ],
                        "parentKey": [
                            "ID"
                        ],
                        "dependentTable": "sap.capire.bookshop.Books",
                        "parentTable": "sap.capire.bookshop.Authors",
                        "onDelete": "RESTRICT",
                        "onDeleteRemark": null,
                        "validated": true,
                        "enforced": true
                    }
                }
            },
            "@cds.persistence.name": "SAP_CAPIRE_BOOKSHOP_BOOKS"
        }

Actual behavior:

The $tableConstraints section is missing in the generated csn output.

Detailed steps to reproduce

1. Define a schema (e.g., the one above) in your CAP project.
2. Run cds deploy --dry --model-only --out csn-model.csn to verify that constraints are captured in the CSN file.
3. Run cds build --production - to build the application for a PostgreSQL database.
4. Inspect the generated csn file (e.g., in gen/pg) and observe that table constraints are missing.

Details about your project

|:------------------|---------------------------------------|

Package Name	Version
@cap-js/asyncapi	1.0.2
@cap-js/cds-types	0.9.0
@cap-js/db-service	1.17.1
@cap-js/openapi	1.1.2
@cap-js/postgres	1.10.5
@cap-js/sqlite	1.8.0
@sap/cds	8.7.1
@sap/cds-compiler	5.7.4
@sap/cds-dk	8.7.1
@sap/cds-dk (global)	8.7.0
@sap/cds-fiori	1.3.0
@sap/cds-foss	5.0.1
@sap/cds-mtxs	2.5.1
@sap/eslint-plugin-cds	3.1.2
Node.js	v20.18.1

[patricebender]

Hi,

I believe this is intentional. As far as I understood the process, the cds build for postgres does produce a CSN file, as baseline for our delta deployment approach. The generated CSN is not in any kind affiliated with a specific DB flavour, yet. Upon first deployment this csn is saved to the database. For every subsequent deployment, the core compiler produces the corresponding csn, which we then compare to the state saved to the database. Only then internally, we process the (delta) csn, create the SQL artifacts, and deploy them to the DB.

So in the end, when you deploy to your database, the referential constraints will exist.

@simonoswald can you confirm my understanding, or add up onto it?

[CHELLARAO-M]

@patricebender The problem here is, the referential constraints aren't getting generated during the initial deployment itself.

[patricebender]

could you upgrade your postgres version and try again, there was something off with the build plugin. #1018

I tried:

cds init tiny-sample add tiny-sample && cd tiny-sample
cds add postgres
npm i

added an association to schema.cds

namespace my.bookshop;

entity Books {
  key ID : Integer;
  title  : String;
  stock  : Integer;
  self: Association to Books;
}

ran cds deploy --production

and then, the refential constraint for Books:self is active:

> await cds.run(`
... SELECT 
...     conname AS constraint_name, 
...     conrelid::regclass AS table_name, 
...     a.attname AS column_name, 
...     confrelid::regclass AS foreign_table_name, 
...     af.attname AS foreign_column_name 
... FROM 
...     pg_constraint AS c 
...     JOIN pg_attribute AS a ON a.attnum = ANY(c.conkey) AND a.attrelid = c.conrelid 
...     JOIN pg_attribute AS af ON af.attnum = ANY(c.confkey) AND af.attrelid = c.confrelid 
... WHERE 
...     c.contype = 'f';
... `)
[
  {
    constraint_name: 'c__my_bookshop_books_self',
    table_name: 'my_bookshop_books',
    column_name: 'self_id',
    foreign_table_name: 'my_bookshop_books',
    foreign_column_name: 'id'
  }
]

[CHELLARAO-M]

Thank you for your response and the detailed steps. I followed the exact procedure you mentioned, but the constraints are still missing. Here's what I did:

• Upgraded @cap-js/postgres to 1.11.1.
• Executed and added above association
• Deployed the application using mtb build and cf deploy

schema.cds

namespace my.bookshop;

entity Books {
  key ID : Integer;
  title  : String;
  stock  : Integer;
  self: Association to Books;
}

Package.json

{
  "name": "tiny-sample",
  "version": "1.0.0",
  "description": "A simple CAP project.",
  "repository": "<Add your repository here>",
  "license": "UNLICENSED",
  "private": true,
  "dependencies": {
    "@sap/cds": "^8",
    "express": "^4",
    "@cap-js/postgres": "^1"
  },
  "devDependencies": {
    "@cap-js/cds-types": "^0.8.0",
    "@cap-js/sqlite": "^1",
    "@sap/cds-dk": "^8"
  },
  "scripts": {
    "start": "cds-serve"
  },
  "cds": {
    "features": {
      "assert_integrity": "db"
    }
  }
}

mta.yml

_schema-version: 3.3.0
ID: tiny-sample
version: 1.0.0
description: "A simple CAP project."
parameters:
  enable-parallel-deployments: true
build-parameters:
  before-all:
    - builder: custom
      commands:
        - npm ci
        - npx cds build --production
modules:
  - name: tiny-sample-postgres-deployer
    type: nodejs
    path: gen/pg
    parameters:
      memory: 256M
      buildpack: nodejs_buildpack
      no-route: true
      no-start: true
      tasks:
        - name: deploy-to-postgresql
          command: npm start
    requires:
      - name: postgres-db-dev

resources:
  - name: postgres-db-dev
    type: org.cloudfoundry.existing-service

gen/pg/db/csn.json

{
  "namespace": "my.bookshop",
  "definitions": {
    "my.bookshop.Books": {
      "kind": "entity",
      "elements": {
        "ID": {
          "key": true,
          "type": "cds.Integer"
        },
        "title": {
          "type": "cds.String"
        },
        "stock": {
          "type": "cds.Integer"
        },
        "self": {
          "type": "cds.Association",
          "target": "my.bookshop.Books",
          "keys": [
            {
              "ref": [
                "ID"
              ]
            }
          ]
        }
      }
    }
  },
  "meta": {
    "creator": "CDS Compiler v5.7.4",
    "flavor": "inferred"
  },
  "$version": "2.0"
}

Repository	Version/Details
tiny-sample
@cap-js/asyncapi	1.0.2
@cap-js/cds-types	0.8.0
@cap-js/db-service	1.17.2
@cap-js/openapi	1.1.2
@cap-js/postgres	1.11.1
@cap-js/sqlite	1.8.0
@sap/cds	8.7.1
@sap/cds-compiler	5.7.4
@sap/cds-dk	8.7.1
@sap/cds-dk (global)	8.6.1
@sap/cds-fiori	1.3.0
@sap/cds-foss	5.0.1
@sap/cds-mtxs	2.5.1
@sap/eslint-plugin-cds	3.1.2
Node.js	v20.18.1
home	/home/user/projects/tiny-sample/node_modules/@sap/cds

However, when I query the pg_constraint table to check for the referential constraints, they are still not present.

[patricebender]

Thanks for letting me know. It looks like, I wasn't able to reproduce it because I did not do a MTA deployment, which essentially runs npm start within gen/pg, which then executes the cds deploy from there → there it will look at gen/pg/package.json, where no options are defined. The cds environment should indeed be copied over to the build folder. We will add a fix for this asap. :)

[CHELLARAO-M]

Adding below to gen/pg/package.json fixed the issue

"cds": {
            "features": {
              "assert_integrity": "db"
            }
     }

Thanks for the info!

[patricebender]

@joergmann @daogrady @chgeo could one of you take over? This was contributed by Lothar, how to proceed with this?:)

[daogrady]

Hi Patrice,

could you please let us know what the exact circumstances are under which the constraints should be generated, and when they should not? Thanks!

Best,
Daniel

[CHELLARAO-M]

Hi @patricebender @daogrady @joergmann ,
Any ETA for the fix?

[daogrady]

Hi @CHELLARAO-M ,

sorry for the wait. The fix has been merged and will be included in the next release.

Best,
Daniel