@quadrio/cds-dbm

cds-dbm (Core Data Services – Database Migrations)

cds-dbm is a package that extends the database tooling capabilities of the SAP Cloud Application Programming Model's Node.js Service SDK (@sap/cds) for relational databases other than the native supported ones (SAP HANA and SQLite).

The library offers a set of command line tasks related to deploying a cds data model to the database:

• deploy
• load
• diff
• drop

It also contains a sap/@cds builder task to create ready-to-deploy build fragments for SAP Business Technology Platform (BTP) Cloud Foundry:

• Custom Build Task

Current status

cds-dbm is ready to be used!
The library contains all the necessary commands and functionality to build a cds model for and deploy it to the supported databases.

Database support

Internally cds-dbm is relying on the popular Java framework liquibase to handle (most) of the database activities. Liquibase by default has support for a variety of relational databases, but currently cds-dbm offers support for the following ones:

• PostgreSQL (in combination with the cds-pg database adapter)

Support for other databases is planned whenever a corresponding CDS adapter library is available.

There are some example apps already using cds-dbm in combination with cds-pg:

• https://github.com/mikezaschka/cap-devtoberfest
• https://github.com/gregorwolf/pg-beershop

There are also some blogposts in the SAP Community showcasing the functionality of cds-dbm:

• https://blogs.sap.com/2020/11/16/getting-started-with-cap-on-postgresql-node.js/
• https://blogs.sap.com/2020/11/30/run-and-deploy-cap-with-postgresql-on-sap-cloud-platform-cloud-foundry-node.js/

Why does cds-dbm (currently) not support SAP HANA?

As SAP HANA is a first class citizen in CAP, SAP offers its own deployment solution (@sap/hdi-deploy). With CAP it is possible to directly compile a data model into SAP HANA fragments (.hdbtable, etc.), which can then be deployed by the hdi-deploy module taking care of all the important stuff (delta handling, hdi-management on XSA or SAP BTP, etc.).
Nevertheless it may be suitable to use the liquibase-hanadb adapter to add an alternative deployment solution. If so, support might be added in the future.

How to use

Prerequisites

Since the project uses liquibase internally, a Java Runtime Environment (JRE) in at least version 8 is required on your system.

Usage in your CAP project

Simply add this package to your CAP project by running:

npm install cds-dbm

cds-dbm requires some additional configuration added to your package.json:

  "cds": {
    //...
    "migrations": {
      "db": {
        "schema": {
          "default": "public",
          "clone": "_cdsdbm_clone",
          "reference": "_cdsdbm_ref"
        },
        "deploy": {
          "tmpFile": "tmp/_autodeploy.json",
          "undeployFile": "db/undeploy.json"
        }
      }
    }
  }

---

Internally cds-dbm uses 3 different schemas during deployment:

• default – The schema containing your currently deployed and thus running schema
• clone – A schema that will be a clone of the default schema during deployment
• reference – The schema that will contain the CDS model

Automated delta deployments

TODO: Add full description

In the meantime some notes on the delta processing:

1. Clone current db schema cds.migrations.db.schema.default into cds.migrations.db.schema.clone.
2. Drop all CDS based views from clone schema because updates on views do not work in the way liquibase is handling this via CREATE OR REPLACE VIEW (https://liquibase.jira.com/browse/CORE-2377).
3. Deploy the full CDS model to the reference schema cds.migrations.db.schema.reference.
4. Let liquibase create a diff between the clone and reference schema (including the recreation of the dropped views).
5. Do some adjustments on the changelog (handle undeployment stuff, fix order of things).
6. Finally deploy changelog to current schema.
7. Load data from .csv files (if requested).

Dropping tables/views

cds-dbm follows a safe approach and does not drop any database tables during deployment. Thus, old tables will still be around even if they are not part of your data model anymore.

You can either remove them manually or rely on cds-dbm to handle this for you.

Undeployment file

An undeployment file makes it possible to specifically list views and tables that should be dropped from the database schema during the deployment.

The undeployment file's path needs to be specified in the package.json configuration (cds.migrations.deploy.undeployFile):

# An example undeploy.json:

{
    "views": [],
    "tables": [
        "csw_beers",
        "csw_anotherTable"
    ]
}

auto-undeployment option

While an undeploy.json file gives you fine grained control, it is also possible to automatically remove tables/views from the database schema. When using the auto-undeploy flag during deployment, cds-dbm will take the cds model as the reference and remove all other existing tables/views.

Commands

The following commands exists for working with cds-dbm in the automated delta deployment mode:

Currently tall tasks must be called with npx

npx cds-dbm <task>

deploy

Performs a delta deployment of the current cds data model to the database. By default no csv files are loaded. If this is required, the load strategy has to be defined (load-via).

Usage

cds-dbm deploy

Flags

• create-db (boolean) - If set, the deploy task tries to create the database before actually deploying the data model. The deployment will not break, if the database has already been created before.
• auto-undeploy (boolean) - WARNING: Drops all tables not known to your data model from the database. This should only be used if your cds includes all tables/views in your db (schema). Otherwise it is highly recommended to use an undeployment file.
• load-via (string) - Can be either full (truncate and insert) or delta (check for existing records, then update or insert)
• dry (boolean) - Does not apply the SQL to the database but logs it to stdout

Examples

cds-dbm deploy
cds-dbm deploy --create-db
cds-dbm deploy --load-via delta
cds-dbm deploy --auto-undeploy
cds-dbm deploy --auto-undeploy --dry

load

Loads data from CSV files into the database. The following conventions apply (according to the default @sap/cds conventions at https://cap.cloud.sap/docs/guides/databases):

• The files must be located in folders db/csv, db/data/, or db/src/csv.
• They contain data for one entity each. File names must follow the pattern -.csv, for example, my.bookshop-Books.csv.
• They must start with a header line that lists the needed element names.

Different to the default mechanism in @sap/cds (which supports only full loads), cds_dbm offers two loading strategies:

• full – Truncates a table and then inserts all the data from the CSV file
• delta – Checks, if an existing record with the same key exists and performs an update. All other records in the table will be left untouched.

Usage

cds-dbm load --via full
cds-dbm load --via delta

Flags

• via (string) - Can be either full (truncate and insert) or delta (check for existing records, then update or insert)

drop

Drops all tables and views in your data model from the database. If the all parameter is given, then everything in the whole schema will be dropped, not only the cds specific entities.

Usage

cds-dbm drop

Flags

• all (boolean) - If set, the whole content of the database/schema is being dropped.

Examples

cds-dbm drop
cds-dbm drop --all

diff

Generates a descriptive text containing all the differences between the defined cds model and the current status of the database. By default, the information will be logged to the console.

Usage

cds-dbm diff

Flags

• to-file (string) - If set, the diff information will be written into the file and not logged in the console.

Examples

cds-dbm diff
cds-dbm diff --to-file db/diff.txt

Custom Build Task

Executes the defined cds build task, either from a .cdsrc or the package json. cds-dbm comes with a pre-baked build task, to deploy the data model to a PostgreSQL database on SAP BTP Cloud Foundry.

Example configuration:

"build": {
      "tasks": [
        {
          "use": "node-cf",
          "src": "srv"
        },
        {
          "use": "cds-dbm/dist/build/postgres-cf",
          "for": "postgres-cf",
          "src": "db",
          "options": {
            "deployCmd": "npx cds-dbm deploy --load-via delta --auto-undeploy"
          }
        }
      ]
    },

Usage via cds build

cds build

This will generate a specifc set of files into the gen/db (or any other configured) folder, that will be deployed to SAP BTP CF environment.

An example configuration for a mta.yml leveraging the build fragments:

  - name: devtoberfest-db-deployer
    type: custom
    path: gen/db
    parameters:
      buildpacks: [https://github.com/cloudfoundry/apt-buildpack#v0.2.2, nodejs_buildpack] 
      no-route: true
      no-start: true
      disk-quota: 2GB
      memory: 512MB
      tasks:
      - name: deploy_to_postgresql
        command: ./deploy.sh
        disk-quota: 2GB
        memory: 512MB      
    build-parameters:
      ignore: ["node_modules/"]
    requires:
      - name: devtoberfest-database 

resources:
  - name: devtoberfest-database
    parameters:
      path: ./pg-options.json
      service: postgresql-db
      service-plan: trial
      skip-service-updates:
        parameters: true
    type: org.cloudfoundry.managed-service      

Sponsors

Thank you to p36 (https://p36.io/) for sponsoring this project.