cds deploy --to postgres accepts command but doesn't create schema

[aech]

Issue 1: cds deploy --to postgres accepts command but doesn't create schema

Labels

• bug
• cli
• postgresql
• ux

Description

The command cds deploy --to postgres is accepted by the CLI but only imports CSV data without creating the database schema (tables, views). This leads to confusion as cds deploy --to sqlite works perfectly (creating schema + importing data), but cds deploy --to postgres does not behave identically.

Steps to Reproduce

1. Create a new CAP project:

   cds init my-project
   cd my-project
   cds add sample
   cds add postgres
   npm install

2. Start PostgreSQL (e.g., via Docker):

   docker run -d -p 5432:5432 -e POSTGRES_PASSWORD=postgres postgres:17

3. Configure .cdsrc.json:

   {
     "requires": {
       "db": {
         "kind": "postgres",
         "credentials": {
           "host": "localhost",
           "port": 5432,
           "user": "postgres",
           "password": "postgres",
           "database": "bookshop"
         }
       }
     }
   }

4. Run deploy:

   cds deploy --to postgres

Expected Behavior

One of the following:

1. Create schema + import data (like cds deploy --to sqlite does)
2. Throw an error with clear message: "postgres" is not supported for --to, use --profile pg instead

Actual Behavior

• Command succeeds without error
• Only CSV data is imported
• No tables/views are created
• Database remains empty (except for some imported rows that fail)

Output:

> init from db/data/sap.capire.bookshop-Authors.csv
/> successfully deployed to localhost:5432

But querying the database shows no tables exist.

Root Cause Analysis

cds help deploy lists only "sqlite" and "hana" as supported targets:

SYNOPSIS
    cds deploy [ <model> ] [ --to <database> ]

OPTIONS
    --to <database>  sqlite | hana

PostgreSQL is not listed, yet the command is accepted. The --to flag seems to be ignored, resulting in partial deployment.

Workaround

Use the correct command:

cds deploy --profile pg  # This works correctly

Or for explicit deployment:

cds deploy

Suggested Solution

Option A (preferred): Support --to postgres properly with identical behavior to SQLite

Implement full schema deployment for PostgreSQL to match cds deploy --to sqlite behavior:

• Create database schema (tables, views, indexes)
• Import CSV data
• Set up constraints and relationships

This provides consistent UX across all database types.

Option B: Throw an error when --to postgres is used:

Error: "postgres" is not a valid target for --to flag.
       For PostgreSQL deployment, use: cds deploy --profile pg

       Supported targets: sqlite, hana

Environment

• @sap/cds: v9.6.3
• @cap-js/postgres: v2.1.2
• Node.js: v22.22.0
• PostgreSQL: 17
• OS: Ubuntu 24.04 (WSL2)

Related Issues

• Cryptic error message when db-ext[pg] profile is missing #1472
• Missing documentation for db-ext model system with PostgreSQL #1473

Additional Context

The confusion stems from the fact that cds deploy --to sqlite works perfectly (creating schema + importing data), leading developers to naturally assume --to postgres would behave identically. This inconsistency creates a poor developer experience.

[aech]

Related issues:

• Cryptic error message when db-ext[pg] profile is missing #1472 (cryptic error messages when db-ext is misconfigured)
• Missing documentation for db-ext model system with PostgreSQL #1473 (documentation gap for db-ext system)

[patricebender]

this is nothing the @cap-js/postgres package can influence. However, I agree that it is confusing that only the pg profile is created if cds add postgres is run:

❯ cds add postgres
adding postgres

successfully added features to your project
❯ npm i

up to date, audited 129 packages in 5s

24 packages are looking for funding
  run `npm fund` for details

found 0 vulnerabilities
❯ cds env requires.db --profile postgres // 😭
{
  impl: '@cap-js/sqlite',
  credentials: { url: ':memory:' },
  pool: { max: 1 },
  kind: 'sqlite'
}
❯ cds env requires.db --profile pg // ✅
{
  impl: '@cap-js/postgres',
  kind: 'postgres',
  dialect: 'postgres',
  vcap: { label: 'postgresql-db' },
  pool: {
    min: 0,
    max: 10,
    testOnBorrow: true,
    acquireTimeoutMillis: 1000,
    destroyTimeoutMillis: 1000
  },
  schema_evolution: 'auto',
  credentials: {
    url: ':memory:',
    host: 'localhost',
    port: 5432,
    user: 'postgres',
    password: 'postgres',
    database: 'postgres'
  }
}

@swaldmann from a tooling perspective, could we also setup the postgres profile for cds add postgres?

[swaldmann]

cds add postgres only adds the dependency in the package.json, but no profile configuration. You should be able to add it here, similar to the [pg] one:
https://github.com/cap-js/cds-dbs/blob/main/postgres/package.json#L49-L58

The CDS plugin mechanism makes sure the env from the plugin package.json is merged into the app env.

[patricebender]

I see, so the default pg profile stems from be postgres package in the node_modules. I would add the respective profile. However, there is still no support by cds deploy as OP requested. Would it make sense to include postgres there as well?

❯ cds env requires.db --profile postgres
{
  impl: '@cap-js/postgres',
  kind: 'postgres',
  dialect: 'postgres',
  vcap: { label: 'postgresql-db' },
  pool: {
…
  },
  schema_evolution: 'auto',
  credentials: {
…
  }
}
❯ cds deploy --to postgres
  > init from db/init.js 
  > init from db/data/sap.capire.bookshop-Genres.csv 
  > init from db/data/sap.capire.bookshop-Books.texts.csv 
  > init from db/data/sap.capire.bookshop-Books.csv 
  > init from db/data/sap.capire.bookshop-Authors.csv 
/> successfully deployed to [object Object]  // actually a sqlite deployment :D