status
released

Using PostgreSQL

This guide focuses on the new PostgreSQL Service provided through @cap-js/postgres, which is based on the same new database services architecture as the new SQLite Service. This architecture brings significantly enhanced feature sets and feature parity, as documented in the Features section of the SQLite guide.

Learn about migrating from the former cds-pg in the Migration chapter.{.learn-more}

[[toc]]

Setup & Configuration

Run this to use PostgreSQL for production:

npm add @cap-js/postgres

Auto-Wired Configuration

The @cap-js/postgres package uses cds-plugin technique to auto-configure your application and use a PostgreSQL database for production.

You can inspect the effective configuration using cds env:

cds env requires.db --for production

Output:

{
  impl: '@cap-js/postgres',
  dialect: 'postgres',
  kind: 'postgres'
}

Using Docker

You can use Docker to run a PostgreSQL database locally as follows:

Install and run Docker Desktop

Create a file like that: ::: code-group

services:
  db:
    image: postgres:alpine
    environment: { POSTGRES_PASSWORD: postgres }
    ports: [ '5432:5432' ]
    restart: always

:::

Create and run the docker container:
```
docker-compose -f pg.yml up -d
```

Service Bindings

In the cloud, use given techniques to bind a cloud-based instance of PostgreSQL to your application.

For local development provide the credentials using a suitable cds env technique, like one of the following.

Using Defaults with `[pg]` Profile

The @cds-js/postgres comes with default credentials under profile [pg] that match the defaults used in the docker setup. So, in case you stick to these defaults you can skip the next sections and just go ahead, deploy your database:

cds deploy --profile pg

Run your application:

cds watch --profile pg

Learn more about that in the Deployment chapter below.{.learn-more}

In Your private `~/.cdsrc.json`

Add it to your private ~/.cdsrc.json if you want to use these credentials on your local machine only:

::: code-group

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

:::

In Project `.env` Files

Alternatively, use a .env file in your project's root folder if you want to share the same credentials with your team:

::: code-group

cds.requires.db.[pg].kind = postgres
cds.requires.db.[pg].credentials.host = localhost
cds.requires.db.[pg].credentials.port = 5432
cds.requires.db.[pg].credentials.user = postgres
cds.requires.db.[pg].credentials.password = postgres
cds.requires.db.[pg].credentials.database = postgres

:::

::: tip Using Profiles

The previous configuration examples use the cds.env profile [pg] to allow selectively testing with PostgreSQL databases from the command line as follows:

cds watch --profile pg

The profile name can be freely chosen, of course.

:::

Deployment

Using `cds deploy`

Deploy your database as usual with that:

cds deploy

Or with that if you used profile [pg] as introduced in the setup chapter above:

cds deploy --profile pg

With Deployer App

When deploying to Cloud Foundry, this can be accomplished by providing a simple deployer app, which you can construct as follows:

Create a new folder named gen/pg/db:
```
mkdir -p gen/pg/db
```
Generate a precompiled cds model:
```
cds compile '*' > gen/pg/db/csn.json
```
Add required .csv files, for example:
```
cp -r db/data gen/pg/db/data
```
Add a package.json to gen/pg with this content: ::: code-group
```
{
  "dependencies": {
    "@sap/cds": "*",
    "@cap-js/postgres": "*"
  },
  "scripts": {
    "start": "cds-deploy"
  }
}
```
:::

Note the dash in cds-deploy, which is required as we don't use @cds-dk for deployment and runtime, so the cds CLI executable isn't available.
Finally, package and deploy that, for example using MTA-based deployment.

Schema Evolution

When redeploying after you changed your CDS models, like adding fields, automatic schema evolution is applied. Whenever you run cds deploy (or cds-deploy) it executes these steps:

Read a CSN of a former deployment from table cds_model.
Calculate the delta to current model.
Generate and run SQL DDL statements with:
- CREATE TABLE statements for new entities
- CREATE VIEW statements for new views
- ALTER TABLE statements for entities with new or changed elements
- DROP & CREATE VIEW statements for views affected by changed entities
Fill in initial data from provided .csv files using UPSERT commands.
Store a CSN representation of the current model in cds_model.

You can switch off automatic schema evolution, if necessary, by setting cds.requires.db.schema_evolution = false.

Limitations

Automatic schema evolution only allows changes without potential data loss.

Allowed{.good}

Adding entities and elements
Increasing the length of Strings
Increasing the size of Integers

Disallowed{.bad}

Removing entities or elements
Changes to primary keys
All other type changes

For example the following type changes are allowed:

entity Foo {
   anInteger : Int64;     // from former: Int32
   aString : String(22);  // from former: String(11)
}

::: tip

If you need to apply such disallowed changes during development, just drop and re-create your database, for example by killing it in docker and re-create it using the docker-compose command, see Using Docker.

:::

Dry-Run Offline

We can use cds deploy with option --dry to simulate and inspect how things work.

Capture your current model in a CSN file:

cds deploy --dry --model-only > cds-model.csn

Make changes to your models, for example to cap/samples/bookshop/db/schema.cds:

entity Books { ...
   title : localized String(222); //> increase length from 111 to 222
   foo : Association to Foo;      //> add a new relationship
   bar : String;                  //> add a new element
}
entity Foo { key ID: UUID }       //> add a new entity

Generate delta SQL DDL script:

cds deploy --dry --delta-from cds-model.csn > delta.sql

Inspect the generated SQL script, which should look like this: ::: code-group

-- Drop Affected Views
DROP VIEW localized_CatalogService_ListOfBooks;
DROP VIEW localized_CatalogService_Books;
DROP VIEW localized_AdminService_Books;
DROP VIEW CatalogService_ListOfBooks;
DROP VIEW localized_sap_capire_bookshop_Books;
DROP VIEW CatalogService_Books_texts;
DROP VIEW AdminService_Books_texts;
DROP VIEW CatalogService_Books;
DROP VIEW AdminService_Books;

-- Alter Tables for New or Altered Columns
ALTER TABLE sap_capire_bookshop_Books ALTER title TYPE VARCHAR(222);
ALTER TABLE sap_capire_bookshop_Books_texts ALTER title TYPE VARCHAR(222);
ALTER TABLE sap_capire_bookshop_Books ADD foo_ID VARCHAR(36);
ALTER TABLE sap_capire_bookshop_Books ADD bar VARCHAR(255);

-- Create New Tables
CREATE TABLE sap_capire_bookshop_Foo (
  ID VARCHAR(36) NOT NULL,
  PRIMARY KEY(ID)
);

-- Re-Create Affected Views
CREATE VIEW AdminService_Books AS SELECT ... FROM sap_capire_bookshop_Books AS Books_0;
CREATE VIEW CatalogService_Books AS SELECT ... FROM sap_capire_bookshop_Books AS Books_0 LEFT JOIN sap_capire_bookshop_Authors AS author_1 O ... ;
CREATE VIEW AdminService_Books_texts AS SELECT ... FROM sap_capire_bookshop_Books_texts AS texts_0;
CREATE VIEW CatalogService_Books_texts AS SELECT ... FROM sap_capire_bookshop_Books_texts AS texts_0;
CREATE VIEW localized_sap_capire_bookshop_Books AS SELECT ... FROM sap_capire_bookshop_Books AS L_0 LEFT JOIN sap_capire_bookshop_Books_texts AS localized_1 ON localized_1.ID = L_0.ID AND localized_1.locale = session_context( '$user.locale' );
CREATE VIEW CatalogService_ListOfBooks AS SELECT ... FROM CatalogService_Books AS Books_0;
CREATE VIEW localized_AdminService_Books AS SELECT ... FROM localized_sap_capire_bookshop_Books AS Books_0;
CREATE VIEW localized_CatalogService_Books AS SELECT ... FROM localized_sap_capire_bookshop_Books AS Books_0 LEFT JOIN localized_sap_capire_bookshop_Authors AS author_1 O ... ;
CREATE VIEW localized_CatalogService_ListOfBooks AS SELECT ... FROM localized_CatalogService_Books AS Books_0;

:::

Note: In case of SQLite, ALTER TYPE commands are neither necessary nor supported, as SQLite is essentially typeless.

Migration

Thanks to CAP's database-agnostic cds.ql API, we're confident that the new PostgreSQL service comes without breaking changes. Nevertheless, please check the instructions in the SQLite Migration guide, with by and large applies also to the new PostgreSQL service.

`cds deploy --model-only`

Not a breaking change, but definitely required to migrate former cds-pg databases, is to prepare it for schema evolution.

To do so run cds deploy once with the --model-only flag:

cds deploy --model-only

This will...:

Create the cds_model table in your database.
Fill it with the current model obtained through cds compile '*'.

::: warning IMPORTANT:

Your .cds models are expected to reflect the deployed state of your database.

:::

With Deployer App

When you have a SaaS application, upgrade all your tenants using the deployer app with CLI option --model-only added to the start script command of your package.json. After having done that, don't forget to remove the --model-only option from the start script, to activate actual schema evolution.

MTX Support

... to come soon.

Provide feedback

Saved searches

Use saved searches to filter your results more quickly

databases-postgres.md

databases-postgres.md

Using PostgreSQL

Setup & Configuration

Auto-Wired Configuration

Using Docker

Service Bindings

Using Defaults with `[pg]` Profile

In Your private `~/.cdsrc.json`

In Project `.env` Files

Deployment

Using `cds deploy`

With Deployer App

Schema Evolution

Limitations

Allowed{.good}

Disallowed{.bad}

Dry-Run Offline

Migration

`cds deploy --model-only`

With Deployer App

MTX Support

Files

databases-postgres.md

Latest commit

History

databases-postgres.md

File metadata and controls

Using PostgreSQL

Setup & Configuration

Auto-Wired Configuration

Using Docker

Service Bindings

Using Defaults with [pg] Profile

In Your private ~/.cdsrc.json

In Project .env Files

Deployment

Using cds deploy

With Deployer App

Schema Evolution

Limitations

Allowed{.good}

Disallowed{.bad}

Dry-Run Offline

Migration

cds deploy --model-only

With Deployer App

MTX Support

Using Defaults with `[pg]` Profile

In Your private `~/.cdsrc.json`

In Project `.env` Files

Using `cds deploy`

`cds deploy --model-only`