Using SAP CAP with PostgreSQL in Docker

 

Introduction

Here we look at using CAP with PostgreSQL running in a local Docker.

Original article here.

Set Up PostgreSQL in Docker

Create pg.yml as follows:

services:
  db:
    image: postgres:alpine
    environment:
      POSTGRES_USER: postgres
      POSTGRES_PASSWORD: 1234
      POSTGRES_DB: test_db
    ports:
      - “5432:5432”
    restart: always

Start the container using the following:

docker compose -f pg.yml up -d

To view logs of the instance, you can use the following:

docker compose -f pg.yml logs -f db

Create a CAP Project

cds init postgres-demo
cd postgres-demo
npm install
npm install @cap-js/postgres

Edit db/schema.cds:

namespace com.test;
using { managed } from ‘@sap/cds/common’;
entity Data: managed {
  key ID : UUID;
  x: Integer;
  y: Integer;
}

Next, configure the service atsrv/test-service.cds:

using { com.test as db } from ‘../db/schema’;
service TestService {
  entity Data as projection on db.Data;
}

Create a .cdsrc.json file in the project root:

{
  “profile”: “pg”,
  “requires”: {
    “db”: {
      “[pg]”: {
        “kind”: “postgres”,
        “credentials”: {
          “host”: “localhost”,
          “port”: 5432,
          “user”: “postgres”,
          “password”: “1234”,
          “database”: “test_db”
        }
      }
    }
  }
}

The details match those set up in pg.yml. The "profile": "pg" at the root sets pg as the default profile, so you don’t need to pass --profile pg every time.

You can also define multiple profiles (e.g. pg and sqlite) and switch between them using the --profile flag:

cds deploy --profile sqlite # or
cds watch --profile sqlite

Now you can deploy your CDS schema to the PostgreSQL database using the following:

cds deploy

Then start the application:

cds watch

Test

Start by inserting a row using curl:

curl -X POST http://localhost:4004/odata/v4/test/Data \
     -H “Content-Type: application/json” \
     -d ‘{
       “x”: 100,
       “y”: 200
     }’

Now you can check if the row was inserted in the PostgreSQL instance. Connect to the PostgreSQL instance using the following:

docker compose -f pg.yml exec db psql -U postgres -d test_db

Next, list the tables using \dt . You should see something like:

List of tables
 Schema |        Name         | Type  |  Owner
--------+---------------------+-------+----------
 public | cds_model           | table | postgres
 public | cds_outbox_messages | table | postgres
 public | com_test_data       | table | postgres

Query your data using SELECT * FROM com_test_data;and you should get a result similar to the following:

createdat        | createdby |       modifiedat        | modifiedby |                  id                  |   x   |   y
-------------------------+-----------+-------------------------+------------+--------------------------------------+-------+-------
 2026-02-22 16:18:08.274 | anonymous | 2026-02-22 16:18:08.274 | anonymous  | 75bf7a2d-730e-46ab-ada0-71b7946abc3a | 100   | 200 

Notice that CAP automatically populates the createdAt, createdBy, modifiedAt, and modifiedBy fields from the managed aspect — no extra code needed.

To exit psql ,use /q