
Starting with version 7.0.0
@sap/cds
now has native support for PostgreSQL. Please use the official database adapter@cap-js/postgres
in favor of the described packages below and check out the official documentation for more details.
There is also a great blog post by tiaxu which includes a detailed step-by-step-guide on how to use
@cap-js/postgres
in a local environment as well as in combination with Cloud Foundry.
cds-pg
and cds-dbm
are now deprecated in favor of the official PostgreSQL support by @Sisn/cds: 7.x
package.json
configuration with now required "dialect": "plain"
for @Sisn/cds: 6.x
cds-dbm
now supports a nice diff
command, see Applying changes to the data modelcds-dbm deploy
now supports a --create-db
flag to automatically create the database, see Add and setup the PostgreSQL database@Sisn/cds
:cds-pg
cds-dbm
cds-pg
– The PostgreSQL adapter for SAP CDScds-pg
already supports many features of CAP (which will be shown later in this post).cds-dbm
– Database deployment for PostgreSQL on CAPcds-pg
contains the functionality to translate the CDS model to native PostgreSQL during runtime, there is a closely related library available, that deals with the deployment of the generated database schema (tables and views): cds-dbm.@Sisn/hdi-deployer
module, that handles all the relevant deployment tasks (analyze the delta between the current state of the database and the current state of the CDS model, deploy the changes to the database, load CSV files, etc.). cds-dbm
provides this functionality for cds-pg
and is designed to support other potential CAP database adapters (think of SQL Server, DB2, MySQL, MariaDB, Amazon RDS...) in the future (that's why the functionality is not baked in cds-pg
, but in its own module).cds-pg
and cds-dbm
, e.g. the pg-beershop project by gregorw (which includes deployment scenarios to many different Cloud Service providers), but since this blog post should also showcase, that the development workflow feels very similar to native CAP development, we will start from scratch.@Sisn/cds-dk
library installed as a global Node.js module.npm i -g @sap/cds-dk
@Sisn/cds-dk
generate the devtoberfest project. While SAP's Devtoberfest is/was real our project will just act as a demo and contain a data model leveraging projects and votes.cds init devtoberfest
docker-compose.yml
file in the root folder of the project and insert the following data:version: '3.1'
services:
db:
image: postgres:alpine
restart: always
environment:
POSTGRES_PASSWORD: postgres
ports:
- '5432:5432'
adminer:
image: adminer
restart: always
ports:
- 8080:8080
UPDATE (22.11.2020)
With version 0.0.14,cds-dbm
is able to create the database during the deployment automatically, thus the steps to login to adminer and to create the database by hand are not required anymore. Just add the--create-db
flag when running the deployment:
npx cds-dbm deploy --create-db
`
cds-pg
later):cds-pg
and cds-dbm
npm i cds-pg
npm i cds-dbm
package.json
)."cds": {
"requires": {
"db": {
"kind": "database"
},
"database": {
"impl": "cds-pg",
"dialect": "plain"
"model": [
"srv"
],
"credentials": {
"host": "localhost",
"port": 5432,
"database": "devtoberfest",
"user": "postgres",
"password": "postgres"
}
}
},
"migrations": {
"db": {
"schema": {
"default": "public",
"clone": "_cdsdbm_clone",
"reference": "_cdsdbm_ref"
},
"deploy": {
"tmpFile": "tmp/_autodeploy.json",
"undeployFile": "db/undeploy.json"
}
}
}
}
cds.requires
part is basically standard CAP and defines, that there is a db
service of the kind database (freely chosen name) and the configuration of that service. The only difference to a native (meaning HANA/SQLite) project is the specific naming of cds-pg as the service implementation.cds.migrations
section is currently required by cds-dbm
. An explanation of the various configuration options can be found in the cds-dbm documentation.cds
command, the following commands are not yet supported:cds watch
(no equivalent present)cds deploy
(use cds-dbm deploy
instead, more details on this below)db/data-model.cds
and create an entity:using { cuid } from '@sap/cds/common';
namespace db;
entity Projects: cuid {
name : String(150);
language: String(150);
repository: String;
}
srv/public-service.cds
with the following content:using db from '../db/data-model';
service PublicService {
entity Projects as projection on db.Projects;
}
cds deploy
cannot be leveraged. Instead, we need to make use of the tasks cds-dbm
provides. A full description can be found in the official documentation.npx
is required as of now):npx cds-dbm deploy
databasechangelog
and databasechangeloglock
. These are automatically generated by the internally used library liquibase and should be left untouched.cds serve
test/projects.http
with the following content and send the request to your API.### Create entity
POST http://localhost:4004/public/Projects
Content-Type: application/json
{
"name": "cds-pg - PostgreSQL adapter for SAP CDS (CAP)",
"repository": "https://github.com/sapmentors/cds-pg",
"language": "Node.js"
}
cds-dbm
also has support for .csv files. While the .csv files are automatically loaded in native CAP scenarios (with SQLite and SAP HANA) during deployment, the data loading must be explicitly triggered in cds-dbm
.db/data/db-Projects.csv
and add the following content:ID,name,repository,language
c682de2f-536b-44fe-acdd-6475d5660ca2,cds-dbm - Database deployment for PostgreSQL on CAP,https://github.com/mikezaschka/cds-dbm,Node.js
5d1e6d61-3ad5-4813-9a67-1fd9df440f68,abapGit: Git client for ABAP,https://github.com/abapGit/abapGit,ABAP
b6c36859-84c1-486b-9f32-a6a25513f3ba,abap-dev-utilities: ABAP Development Tools,https://github.com/jrodriguez-rc/abap-dev-utilities,ABAP
2d7c145e-fd40-492f-8499-2dd21e3cf0fc,vscode_abap_remote_fs: Remote filesystem for ABAP systems,https://github.com/marcellourbani/vscode_abap_remote_fs,ABAP
// only load .csv data
npx cds-dbm load --via delta
// deploy data model and load .csv data
npx cds-dbm deploy --load-via delta
delta
, cds-dbm
does not remove existing data from the corresponding table, but only adds missing or updates altered rows. If the load parameter is set to full
, then the target table will be truncated and the data from the .csv file loaded into the empty table (the only supported mode in native CAP).db/data-model.cds
using { cuid } from '@sap/cds/common';
namespace db;
entity Projects: cuid {
name : String(150);
language: String(150);
repository: String;
votes: Association to many Votes on votes.project = $self;
}
entity Votes: cuid {
username : String(150);
createdAt: DateTime;
project: Association to one Projects;
}
srv/public-service.cds
using db from '../db/data-model';
service PublicService {
entity Projects as projection on db.Projects;
entity Votes as projection on db.Votes;
}
cds-dbm
has support for automated delta deployments (like native CAP on SAP HANA) and only applies the changes from your current CDS model to the database (unlike native CAP on SQLite, which drops the whole data model on every deployment). Before we do the deployment, let's examine the changes on the database level by using the diff
command:npx cds-dbm diff
cds-dbm deploy
task, via:npx cds-dbm deploy
cds serve
) and refresh your browser. When you closely look at the Projecs entity (http://localhost:4004/public/Projects), you will see, that all the data in the db_projects
table is still there, even the data that has been inserted via API. This is because cds-dbm
does not only support delta deployments on a schema level, but is also able to keep your data available in tables, that will be filled with default (.csv) data).cds-pg
and cds-dbm
, you are able to use the power of CAP in combination with PostgreSQL as a database. And while there are some differences in the development workflow, things pretty much stick to the native feeling of working with CAP.Update: 31.11.2020
With the latest developments it is possible to deploy and run your application on SAP BTP Cloud Foundry. Checkout this blogpost for more details.
cds-pg
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
User | Count |
---|---|
21 | |
19 | |
9 | |
7 | |
5 | |
5 | |
5 | |
4 | |
4 | |
4 |