Using Databases ​

This guide provides instructions on how to use databases with CAP applications. Out of the box-support is provided for SAP HANA, SQLite, H2 (Java only), and PostgreSQL.

This guide is available for Node.js and Java. Press v to switch, or use the toggle.

Setup & Configuration ​

npm add @cap-js/sqlite -D

npm add @cap-js/sqlite -D

npm add @sap/cds-hana

npm add @sap/cds-hana

{"cds":{
  "requires": {
    "db": {
      "[development]": { "kind": "sqlite", "impl": "@cap-js/sqlite", "credentials": { "url": "memory" } },
      "[production]": { "kind": "hana", "impl": "@sap/cds-hana", "deploy-format": "hdbtable" }
    }
  }
}}

{"cds":{
  "requires": {
    "db": {
      "[development]": { "kind": "sqlite", "impl": "@cap-js/sqlite", "credentials": { "url": "memory" } },
      "[production]": { "kind": "hana", "impl": "@sap/cds-hana", "deploy-format": "hdbtable" }
    }
  }
}}

cds env cds.requires.db

cds env cds.requires.db

{
  kind: 'sqlite',
  impl: '@cap-js/sqlite',
  credentials: { url: 'db.sqlite' }
}

{
  kind: 'sqlite',
  impl: '@cap-js/sqlite',
  credentials: { url: 'db.sqlite' }
}

Providing Initial Data ​

Put CSV files into db/data to fill your database with initial data.

bookshop/
├─ db/
│ └─ data/ #> place your .csv files here
│ │ ├─ sap.capire.bookshop-Authors.csv
│ │ ├─ sap.capire.bookshop-Books.csv
│ │ ├─ sap.capire.bookshop-Books.texts.csv
│ │ └─ sap.capire.bookshop-Genres.csv
│ └─ schema.cds
└─ ...

bookshop/
├─ db/
│ └─ data/ #> place your .csv files here
│ │ ├─ sap.capire.bookshop-Authors.csv
│ │ ├─ sap.capire.bookshop-Books.csv
│ │ ├─ sap.capire.bookshop-Books.texts.csv
│ │ └─ sap.capire.bookshop-Genres.csv
│ └─ schema.cds
└─ ...

db/
└─ data/ #> place your .csv files here
│ ├─ my.bookshop-Authors.csv
│ ├─ my.bookshop-Books.csv
│ ├─ my.bookshop-Books.texts.csv
│ └─ my.bookshop-Genres.csv
| └─ ...
└─ index.cds

db/
└─ data/ #> place your .csv files here
│ ├─ my.bookshop-Authors.csv
│ ├─ my.bookshop-Books.csv
│ ├─ my.bookshop-Books.texts.csv
│ └─ my.bookshop-Genres.csv
| └─ ...
└─ index.cds

The filenames are expected to match fully qualified names of respective entity definitions in your CDS models, optionally using a dash - instead of a dot . for cosmetic reasons.

Using .csv Files ​

The content of these files is standard CSV content with the column titles corresponding to declared element names, like for Books:

ID,title,author_ID,stock
201,Wuthering Heights,101,12
207,Jane Eyre,107,11
251,The Raven,150,333
252,Eleonora,150,555
271,Catweazle,170,22

ID,title,author_ID,stock
201,Wuthering Heights,101,12
207,Jane Eyre,107,11
251,The Raven,150,333
252,Eleonora,150,555
271,Catweazle,170,22

Note: author_ID is the generated foreign key for the managed Association author → learn more about that in the Generating SQL DDL section.

If your content contains ...

• commas or line breaks → enclose it in double quotes "..."
• double quotes → escape them with doubled double quotes: ""...""

ID,title,descr
252,Eleonora,"""Eleonora"" is a short story by Edgar Allan Poe, first published in 1842 in Philadelphia in the literary annual The Gift."

ID,title,descr
252,Eleonora,"""Eleonora"" is a short story by Edgar Allan Poe, first published in 1842 in Philadelphia in the literary annual The Gift."

Use cds add data ​

Run this to generate an initial set of empty .csv files with header lines based on your CDS model:

cds add data

cds add data

Location of CSV Files ​

Quite frequently you need to distinguish between sample data and real initial data. CAP supports this by allowing you to provide initial data in two places:

---
spring:
  config.activate.on-profile: test
cds
  dataSource.csv.paths:
  - test/data/**

---
spring:
  config.activate.on-profile: test
cds
  dataSource.csv.paths:
  - test/data/**

Querying at Runtime ​

Most queries to databases are constructed and executed from generic event handlers of CRUD requests, so quite frequently there's nothing to do. The following is for the remaining cases where you have to provide custom logic, and as part of it execute database queries.

DB-Agnostic Queries ​

SELECT.from (Authors, a => {
  a.ID, a.name, a.books (b => {
    b.ID, b.title
  })
})
.where ({name:{like:'A%'}})
.orderBy ('name')

SELECT.from (Authors, a => {
  a.ID, a.name, a.books (b => {
    b.ID, b.title
  })
})
.where ({name:{like:'A%'}})
.orderBy ('name')

Select.from(AUTHOR)
      .columns(a -> a.id(), a -> a.name(),
               a -> a.books().expand(b -> b.id(), b.title()))
      .where(a -> a.name().startWith("A"))
      .orderBy(a -> a.name());

Select.from(AUTHOR)
      .columns(a -> a.id(), a -> a.name(),
               a -> a.books().expand(b -> b.id(), b.title()))
      .where(a -> a.name().startWith("A"))
      .orderBy(a -> a.name());

Native DB Queries ​

If required you can also use native database features by executing native SQL queries:

cds.db.run (`SELECT from sqlite_schema where name like ?`, name)

cds.db.run (`SELECT from sqlite_schema where name like ?`, name)

@Autowired
JdbcTemplate db;
...
db.queryForList("SELECT from sqlite_schema where name like ?", name);

@Autowired
JdbcTemplate db;
...
db.queryForList("SELECT from sqlite_schema where name like ?", name);

Generating DDL Files ​

You can also do this manually with the CLI command cds compile --to <dialect>.

Using cds compile -2 <dialect> ​

For example, given these CDS models (derived from cap/samples/bookshop):

using { Currency } from '@sap/cds/common';
namespace sap.capire.bookshop;

entity Books {
  key ID : UUID;
  title  : localized String;
  descr  : localized String;
  author : Association to Authors;
  price  : {
    amount   : Decimal;
    currency : Currency;
  }
}

entity Authors {
  key ID : UUID;
  name  : String;
  books : Association to many Books on books.author = $self;
}

using { Currency } from '@sap/cds/common';
namespace sap.capire.bookshop;

entity Books {
  key ID : UUID;
  title  : localized String;
  descr  : localized String;
  author : Association to Authors;
  price  : {
    amount   : Decimal;
    currency : Currency;
  }
}

entity Authors {
  key ID : UUID;
  name  : String;
  books : Association to many Books on books.author = $self;
}

using { sap.capire.bookshop as my } from '../db/schema';
service CatalogService {
  entity ListOfBooks as projection on Books {
    *, author.name as author
  }
}

using { sap.capire.bookshop as my } from '../db/schema';
service CatalogService {
  entity ListOfBooks as projection on Books {
    *, author.name as author
  }
}

Generate an SQL DDL script by running this in the root directory containing both .cds files:

cds compile srv/cat-service --to sqlite > schema.sql

cds compile srv/cat-service --to sqlite > schema.sql

Output:

CREATE TABLE sap_capire_bookshop_Books (
  ID NVARCHAR(36) NOT NULL,
  title NVARCHAR(5000),
  descr NVARCHAR(5000),
  author_ID NVARCHAR(36),
  price_amount DECIMAL,
  price_currency_code NVARCHAR(3),
  PRIMARY KEY(ID)
);

CREATE TABLE sap_capire_bookshop_Authors (
  ID NVARCHAR(36) NOT NULL,
  name NVARCHAR(5000),
  PRIMARY KEY(ID)
);

CREATE TABLE sap_common_Currencies (
  name NVARCHAR(255),
  descr NVARCHAR(1000),
  code NVARCHAR(3) NOT NULL,
  symbol NVARCHAR(5),
  minorUnit SMALLINT,
  PRIMARY KEY(code)
);

CREATE TABLE sap_capire_bookshop_Books_texts (
  locale NVARCHAR(14) NOT NULL,
  ID NVARCHAR(36) NOT NULL,
  title NVARCHAR(5000),
  descr NVARCHAR(5000),
  PRIMARY KEY(locale, ID)
);

CREATE VIEW CatalogService_ListOfBooks AS SELECT
  Books.ID,
  Books.title,
  Books.descr,
  author.name AS author,
  Books.price_amount,
  Books.price_currency_code
FROM sap_capire_bookshop_Books AS Books
LEFT JOIN sap_capire_bookshop_Authors AS author
ON Books.author_ID = author.ID;

--- some more technical views skipped ...

CREATE TABLE sap_capire_bookshop_Books (
  ID NVARCHAR(36) NOT NULL,
  title NVARCHAR(5000),
  descr NVARCHAR(5000),
  author_ID NVARCHAR(36),
  price_amount DECIMAL,
  price_currency_code NVARCHAR(3),
  PRIMARY KEY(ID)
);

CREATE TABLE sap_capire_bookshop_Authors (
  ID NVARCHAR(36) NOT NULL,
  name NVARCHAR(5000),
  PRIMARY KEY(ID)
);

CREATE TABLE sap_common_Currencies (
  name NVARCHAR(255),
  descr NVARCHAR(1000),
  code NVARCHAR(3) NOT NULL,
  symbol NVARCHAR(5),
  minorUnit SMALLINT,
  PRIMARY KEY(code)
);

CREATE TABLE sap_capire_bookshop_Books_texts (
  locale NVARCHAR(14) NOT NULL,
  ID NVARCHAR(36) NOT NULL,
  title NVARCHAR(5000),
  descr NVARCHAR(5000),
  PRIMARY KEY(locale, ID)
);

CREATE VIEW CatalogService_ListOfBooks AS SELECT
  Books.ID,
  Books.title,
  Books.descr,
  author.name AS author,
  Books.price_amount,
  Books.price_currency_code
FROM sap_capire_bookshop_Books AS Books
LEFT JOIN sap_capire_bookshop_Authors AS author
ON Books.author_ID = author.ID;

--- some more technical views skipped ...

Rules for Generated DDL ​

A few observations on the generated SQL DDL output:

1. Tables / Views — Declared entities become tables, projected entities become views.
2. Type Mapping — CDS types are mapped to database-specific SQL types.
3. Slugified FQNs — Dots in fully qualified CDS names become underscores in SQL names.
4. Flattened Structs — Structured elements like Books:price are flattened with underscores.
5. Generated Foreign Keys — For managed to-one Associations, foreign key columns are created. For example, this applies to Books:author.

In addition, you can use the following annotations to fine-tune generated SQL.

@cds.persistence.skip ​

Add @cds.persistence.skip to an entity to indicate that this entity should be skipped from generated DDL scripts, and also no SQL views to be generated on top of it:

@cds.persistence.skip
entity Foo {...}                 //> No SQL table will be generated
entity Bar as select from Foo;   //> No SQL view will be generated

@cds.persistence.skip
entity Foo {...}                 //> No SQL table will be generated
entity Bar as select from Foo;   //> No SQL view will be generated

@cds.persistence.exists ​

Add @cds.persistence.exists to an entity to indicate that this entity should be skipped from generated DDL scripts. In contrast to @cds.persistence.skip a database relation is expected to exist, so we can generate SQL views on top.

@cds.persistence.exists
entity Foo {...}                 //> No SQL table will be generated
entity Bar as select from Foo;   //> The SQL view will be generated

@cds.persistence.exists
entity Foo {...}                 //> No SQL table will be generated
entity Bar as select from Foo;   //> The SQL view will be generated

@cds.persistence.table ​

Annotate an entity with @cds.persistence.table to create a table with the effective signature of the view definition instead of an SQL view.

@cds.persistence.table
entity Foo as projection on Bar {...}

@cds.persistence.table
entity Foo as projection on Bar {...}

All parts of the view definition not relevant for the signature (like where, group by, ...) are ignored.

Use case for this annotation: Use projections on imported APIs as replica cache tables.

@sql.prepend / append ​

Use @sql.prepend and @sql.append to add native SQL clauses to before or after generated SQL output of CDS entities or elements.

Example:

@sql.append: ```sql
  GROUP TYPE foo
  GROUP SUBTYPE bar
```
entity E { ...,
  @sql.append: 'FUZZY SEARCH INDEX ON'
  text: String(100);
}

@sql.append: 'WITH DDL ONLY'
entity V as select from E { ... };

@sql.append: ```sql
  GROUP TYPE foo
  GROUP SUBTYPE bar
```
entity E { ...,
  @sql.append: 'FUZZY SEARCH INDEX ON'
  text: String(100);
}

@sql.append: 'WITH DDL ONLY'
entity V as select from E { ... };

Output:

CREATE TABLE E ( ...,
  text NVARCHAR(100) FUZZY SEARCH INDEX ON
) GROUP TYPE foo
GROUP SUBTYPE bar;

CREATE VIEW V AS SELECT ... FROM E WITH DDL ONLY;

CREATE TABLE E ( ...,
  text NVARCHAR(100) FUZZY SEARCH INDEX ON
) GROUP TYPE foo
GROUP SUBTYPE bar;

CREATE VIEW V AS SELECT ... FROM E WITH DDL ONLY;

The following rules apply:

• The compiler doesn't check or process the provided SQL snippets in any way. You're responsible to ensure that the resulting statement is valid and doesn't negatively impact your database or your application. We don't provide support for problems caused by using this feature.

• If you refer to a column name in the annotation, you need to take care of a potential name mapping yourself, for example, for structured elements.

• Annotation @sql.prepend is only supported for entities translating to tables. It can't be used with views nor with elements.

• For SAP HANA tables, there's an implicit that is overwritten by an explicitly provided @sql.prepend.

• Both @sql.prepend and @sql.append are disallowed in SaaS extension projects.

If you use native database clauses in combination with @cds.persistence.journal, see Schema Evolution Support of Native Database Clauses.

Reserved Words ​

The CDS compiler and CAP runtimes provide smart quoting for reserved words in SQLite and in SAP HANA so that they can still be used in most situations. But in general reserved words cannot be used as identifiers. The list of reserved words varies per database.

Find here a collection of resources on selected databases and their reference documentation:

• SAP HANA SQL Reference Guide for SAP HANA Platform (Cloud Version)
• SAP HANA SQL Reference Guide for SAP HANA Cloud
• SQLite Keywords
• H2 Keywords/Reserved Words

There are also reserved words related to SAP Fiori.

Database Constraints ​

The information about foreign key relations contained in the associations of CDS models can be used to generate foreign key constraints on the database tables. Within CAP, referential consistency is established only at commit. The "deferred" concept for foreign key constraints in SQL databases allows the constraints to be checked and enforced at the time of the COMMIT statement within a transaction rather than immediately when the data is modified, providing more flexibility in maintaining data integrity.

Enable generation of foreign key constraints on the database with:

cds.features.assert_integrity = 'db'

cds.features.assert_integrity = 'db'

With that switched on, foreign key constraints are generated for managed to-one associations. For example, given this model:

entity Books {
  key ID : Integer; ...
  author : Association to Authors;
}
entity Authors {
  key ID : Integer; ...
}

entity Books {
  key ID : Integer; ...
  author : Association to Authors;
}
entity Authors {
  key ID : Integer; ...
}

The following Books_author constraint would be added to table Books:

CREATE TABLE Authors (
  ID INTEGER NOT NULL,  -- primary key referenced by the constraint
  ...,
  PRIMARY KEY(ID)
);
CREATE TABLE Books (
  ID INTEGER NOT NULL,
  author_ID INTEGER,    -- generated foreign key field
  ...,
  PRIMARY KEY(ID),
  CONSTRAINT Books_author 
    FOREIGN KEY(author_ID)  -- link generated foreign key field author_ID ...
    REFERENCES Authors(ID)  -- ... with primary key field ID of table Authors
    ON UPDATE RESTRICT
    ON DELETE RESTRICT
    VALIDATED           -- validate existing entries when constraint is created
    ENFORCED            -- validate changes by insert/update/delete
    INITIALLY DEFERRED  -- validate only at commit
)

CREATE TABLE Authors (
  ID INTEGER NOT NULL,  -- primary key referenced by the constraint
  ...,
  PRIMARY KEY(ID)
);
CREATE TABLE Books (
  ID INTEGER NOT NULL,
  author_ID INTEGER,    -- generated foreign key field
  ...,
  PRIMARY KEY(ID),
  CONSTRAINT Books_author 
    FOREIGN KEY(author_ID)  -- link generated foreign key field author_ID ...
    REFERENCES Authors(ID)  -- ... with primary key field ID of table Authors
    ON UPDATE RESTRICT
    ON DELETE RESTRICT
    VALIDATED           -- validate existing entries when constraint is created
    ENFORCED            -- validate changes by insert/update/delete
    INITIALLY DEFERRED  -- validate only at commit
)

No constraints are generated for...

• Unmanaged associations or compositions
• To-many associations or compositions
• Associations annotated with @assert.integrity: false
• Associations where the source or target entity is annotated with @cds.persistence.exists or @cds.persistence.skip

If the association is the backlink of a composition, the constraint's delete rule changes to CASCADE. That applies, for example, to the parent association in here:

entity Genres {
  key ID   : Integer;
  parent   : Association to Genres;
  children : Composition of many Genres on children.parent = $self;
}

entity Genres {
  key ID   : Integer;
  parent   : Association to Genres;
  children : Composition of many Genres on children.parent = $self;
}

As a special case, a referential constraint with delete cascade is also generated for the text table of a localized entity, although no managed association is present in the texts entity.

Add a localized element to entity Books from the previous example:

entity Books {
  key ID : Integer; ...
  title : localized String;
}

entity Books {
  key ID : Integer; ...
  title : localized String;
}

The generated text table then is:

CREATE TABLE Books_texts (
  locale NVARCHAR(14) NOT NULL,
  ID INTEGER NOT NULL,
  title NVARCHAR(5000),
  PRIMARY KEY(locale, ID),
  CONSTRAINT Books_texts_texts 
    FOREIGN KEY(ID)
    REFERENCES Books(ID)
    ON UPDATE RESTRICT
    ON DELETE CASCADE
    VALIDATED
    ENFORCED
    INITIALLY DEFERRED
)

CREATE TABLE Books_texts (
  locale NVARCHAR(14) NOT NULL,
  ID INTEGER NOT NULL,
  title NVARCHAR(5000),
  PRIMARY KEY(locale, ID),
  CONSTRAINT Books_texts_texts 
    FOREIGN KEY(ID)
    REFERENCES Books(ID)
    ON UPDATE RESTRICT
    ON DELETE CASCADE
    VALIDATED
    ENFORCED
    INITIALLY DEFERRED
)

Using Native Features ​

In general, the CDS 2 SQL compiler doesn't 'understand' SQL functions but translates them to SQL generically as long as they follow the standard call syntax of function(param1, param2). This allows you to use native database functions inside your CDS models.

Example:

entity BookPreview as select from Books {
  IFNULL (descr, title) as shorttext   //> using HANA function IFNULL
};

entity BookPreview as select from Books {
  IFNULL (descr, title) as shorttext   //> using HANA function IFNULL
};

The OVER clause for SQL Window Functions is supported, too:

entity RankedBooks as select from Books {
  name, author,
  rank() over (partition by author order by price) as rank
};

entity RankedBooks as select from Books {
  name, author,
  rank() over (partition by author order by price) as rank
};

Using Native Functions with Different DBs ​

In case of conflicts, follow these steps to provide different models for different databases:

1. Add database-specific schema extensions in specific subfolders of ./db:

   db/sqlite/index.cdsdb/hana/index.cds

   cds

   using { AdminService } from '..';
   extend projection AdminService.Authors with {
      strftime('%Y',dateOfDeath)-strftime('%Y',dateOfBirth) as age : Integer
   }

   using { AdminService } from '..';
   extend projection AdminService.Authors with {
      strftime('%Y',dateOfDeath)-strftime('%Y',dateOfBirth) as age : Integer
   }

   cds

   using { AdminService } from '..';
   extend projection AdminService.Authors with {
      YEARS_BETWEEN(dateOfBirth, dateOfDeath) as age : Integer
   }

   using { AdminService } from '..';
   extend projection AdminService.Authors with {
      YEARS_BETWEEN(dateOfBirth, dateOfDeath) as age : Integer
   }

2. Add configuration in specific profiles to your package.json, to use these database-specific extensions:

   json

   { "cds": { "requires": {
     "db": {
      "kind": "sql",
      "[development]": { "model": "db/sqlite" },
      "[production]": { "model": "db/hana" }
    }
   }}}

   { "cds": { "requires": {
     "db": {
      "kind": "sql",
      "[development]": { "model": "db/sqlite" },
      "[production]": { "model": "db/hana" }
    }
   }}}

CAP samples demonstrate this in cap/samples/fiori.
There's also a code tour available for that.