capire

Appearance

CDL Compilation to Database-Specific DDLs ​

Databases are deployed based on the entity definitions in your CDS models. This guide explains how that works under the hood, focusing on the compilation of CDS models to database-specific artifacts like SQL CREATE TABLE statements for relational databases.

Everything Served Out of the Box

The CAP framework handles all compilation to DDL automatically, for example when you run cds watch or cds deploy. You typically don't need to worry about the details unless you want to inspect or customize the generated DDL statements. So, all information in this guide is just to explain how things work under the hood, and if you are on a fast track, you can safely skip it.

Using cds compile, ... ​

CDS compilation to database-specific DDLs is handled by the cds compile command, which is part of the cds CLI. When you run cds deploy or cds watch, this command is invoked automatically to generate the necessary DDL statements for your target database.

You can also run the command manually to see the generated DDL for your models. For example, to inspect what the SQL DDL for your entire model would look like, simply run:

shell
cds compile \* --to sql

The asterisk (\*1) can be replaced with specific .cds files or folders to compile only particular parts of your model.

shell
cds compile db/schema.cds --to sql
cds compile db --to sql
Details

You can combine cds compile with other shell commands via UNIX pipes for more advanced use cases. For example, count the number of entity definitions in your models like this:

shell
cds compile \* | grep entity | wc -l

1 The backslash (\) before the asterisk (*) is used to escape it, preventing shell expansion to all files in the current directory.

Database-Specific Dialects ​

Add the --dialect option to generate DDL for specific databases. For example, to see the SAP HANA-specific variant, run:

shell
cds compile \* --to sql --dialect hana

We can generate DDL files for different dialects in one go, and check differences between individual ones using VS Code like this:

shell
cds compile \* --to sql --dialect sqlite -o _out/c/sqlite.sql
cds compile \* --to sql --dialect h2 -o _out/c/h2.sql
cds compile \* --to sql --dialect hana -o _out/c/hana.sql
cds compile \* --to sql --dialect postgres -o _out/c/postgres.sql
shell
code --diff _out/c/sqlite.sql _out/c/h2.sql

CDS models are database-agnostic

CDS models are designed to be database-agnostic, allowing you to switch between different databases with minimal changes. The --dialect option helps you see how your models translate to different database-specific DDLs. \

Dialects by cds env Profiles ​

The dialect is automatically inferred from your project configuration and the current profile, so you typically don't need to specify it explicitly. For example, if your project is configured to use SAP HANA in production and SQLite in development, the respective dialects will be applied automatically. Try this out using the --profile option:

shell
cds compile \* --to sql --profile development
cds compile \* --to sql --profile production
Use cds env to check your effective configurations:
shell
cds env requires.db --profile development
cds env requires.db --profile production

Dialects are inferred from profiles automatically

You typically don't need to specify the --dialect option manually, as it is derived from your project configuration and the active profile.

Using cds deploy ​

We can use cds deploy to inspect the generated DDL without actually deploying it, by using the --dry option. This will print the ultimate DDL statements to the console instead of executing them against the database, for example:

shell
cds deploy --dry

This will print out the DDL for the database configured in your project for the current profile.

As for cds compile above, let's generate DDL files for different databases in one go, and compare it to the former output like this:

shell
cds deploy --dry --to sqlite -o _out/d/sqlite.sql
cds deploy --dry --to h2 -o _out/d/h2.sql
cds deploy --dry --to hana  -o _out/d/hana
cds deploy --dry --to postgres -o _out/d/postgres.sql
shell
code --diff _out/c/sqlite.sql _out/d/sqlite.sql
sql
DROP TABLE IF EXISTS sap_capire_bookshop_Authors; 
DROP TABLE IF EXISTS sap_capire_bookshop_Books; 
DROP TABLE IF EXISTS sap_capire_bookshop_Genres; 
... 
CREATE TABLE sap_capire_bookshop_Authors ...;
CREATE TABLE sap_capire_bookshop_Books ...;
CREATE TABLE sap_capire_bookshop_Genres ...;
...
sql
CREATE TABLE sap_capire_bookshop_Authors ...;
CREATE TABLE sap_capire_bookshop_Books ...;
CREATE TABLE sap_capire_bookshop_Genres ...;
...

Essentially, cds deploy calls cds compile --to sql under the hood, but goes a step further by also considering deployment-specific aspects, like:

  • Schema Evolution – the diff shows additional DROP TABLE statements, which are a schema evolution strategy most suitable for development. For production, more sophisticated strategies are applied. Learn more about that in the Schema Evolution guide.

  • Database-Specific Artifacts – for SAP HANA, the output of cds deploy is not a single SQL DDL script anymore; but a number of .hdbtable, .hdbview, and other so-called HDI artifacts are generated.

Ad-hoc Deployments

Without the --dry option, cds deploy would not only compile your CDS models to DDL, but would also do an ad-hoc deployment to the target database, if available. How that works is explained in more detail in the database-specific guides for SAP HANA, SQLite, and PostgreSQL.

CDL ⇒ DDL Translation ​

The CDL-to-DDL compilation follows several general mapping principles to translate CDS constructs into database-specific artifacts, as outlined below.

Entities ⇒ Tables / Views ​

Declared entities become tables, projected entities become views:

cds
entity SomeEntity { ... }
entity SomeView as select from SomeEntity { ... };
entity SomeProjection as projection on SomeEntity { ... };
sql
CREATE TABLE SomeEntity ( ... );
CREATE VIEW SomeView AS SELECT ... FROM SomeEntity;
CREATE VIEW SomeProjection AS SELECT ... FROM SomeEntity;

Views are defined using CQL

Both view defined per as projection on and those using as select from are defined using CQL, which supports a broad scope of database-agnostic features. Learn more about that in the following guide: CQL Compilation to SQL.

Qualified Names ⇒ Slugified ​

Entities in CDS models have fully qualified names with dots. These are converted to database-native names, by replacing dots with underscores – called 'slugification':

cds
namespace sap.capire.bookshop;
entity Books { ... }
entity Books.Details { ... }
sql
CREATE TABLE sap_capire_bookshop_Books ( ... );
CREATE TABLE sap_capire_bookshop_Books_Details ( ... );

Guaranteed & Stable Slugification

The slugification effects are guaranteed and stable, which means that you can rely on it and use the slugified names in native SQL queries. For example, both of the following CQL queries are equivalent and will work as expected:

js
await cds.run `SELECT from sap.capire.bookshop.Books`
await cds.run `SELECT from sap_capire_bookshop_Books`

TIP

Prefer entity names like Books.Details over CamelCase variants like BooksDetails. While both work equally, they show up differently in native tools of databases that don't preserve case, for example in SAP HANA: The former will show up as BOOKS_DETAILS, while the latter shows up as BOOKSDETAILS, which is harder to read.

Types ⇒ Native Types ​

CDS types are mapped to database-specific SQL types based on the target database dialect, as outlined in the table below:

CDS TypeSAP HANASQLiteH2PostgreSQL
UUIDNVARCHAR(36)NVARCHAR(36)NVARCHAR(36)NVARCHAR(36)
StringNVARCHAR(5e3)NVARCHAR(255)NVARCHAR(255)NVARCHAR(255)
String (n)NVARCHAR(n)NVARCHAR(n)NVARCHAR(n)NVARCHAR(n)
BooleanBOOLEANBOOLEANBOOLEANBOOLEAN
IntegerINTEGERINTEGERINTEGERINTEGER
Int16SMALLINTSMALLINTSMALLINTSMALLINT
Int32INTEGERINTEGERINTEGERINTEGER
Int64BIGINTBIGINTBIGINTBIGINT
UInt8TINYINTTINYINTSMALLINTSMALLINT
Decimal (p,s)DECIMAL(p,s)DECIMAL(p,s)DECIMAL(p,s)DECIMAL(p,s)
DecimalDECIMALDECIMALDECFLOATDECIMAL
DoubleDOUBLEDOUBLEDOUBLEFLOAT8
DateTimeSECONDDATEDATETIME_TEXTTIMESTAMP(0)TIMESTAMP
DateDATEDATE_TEXTDATEDATE
TimeTIMETIME_TEXTTIMETIME
TimestampTIMESTAMPTIMESTAMP_TEXTTIMESTAMP(7)TIMESTAMP
BinaryVARBINARY(5e3)BINARY_BLOB(5e3)VARBINARY(5e3)BYTEA
Binary (n)VARBINARY(n)BINARY_BLOB(n)VARBINARY(n)BYTEA
LargeBinaryBLOBBLOBBIN. LARGE OBJ.BYTEA
LargeStringNCLOBNCLOBNCLOBTEXT
MapNCLOBJSON_TEXTJSONJSONB
VectorREAL_VECTOR

Refer to CDS Types Documentation for a specification of the CDS types.

Custom-defined types based on built-in CDS types are mapped according to their underlying base type:

cds
entity Foo { bar : Text(44); }
type Text : String(111);
sql
CREATE TABLE Foo ( bar NVARCHAR(44) );

Structs ⇒ Flattened ​

flattened-structs ​

Elements with structured types are flattened into their parent entities, with the struct name used as a prefix for the contained elements:

cds
entity Books {
  title : String;
  price : {
   amount  : Decimal;
   currency : String(3);
  }
}
sql
CREATE TABLE Books (
  title       NVARCHAR(255),
  price_amount  DECIMAL,
  price_currency NVARCHAR(3)
);

Guaranteed & Stable Flattening

The flattening effects are guaranteed and stable, which means that you can rely on it and use the flattened elements in native SQL queries. For example, both of the following CQL queries are equivalent and would work as expected:

js
await cds.run `SELECT price.amount from Books`
await cds.run `SELECT price_amount from Books`

Associations ⇒ JOINs ​

Given this CDS model with both managed to-one and unmanaged to-many associations, as we know them from the @capire/bookshop sample:

cds
entity Books { ...
  author : Association to Authors; // managed
  genre  : Association to Genres; // managed
}
entity Authors { ...
  books : Association to many Books on books.author = $self;
}
entity Genres { ... }

Managed associations are unfolded into unmanaged ones as below::

cds
entity Books { ... // with managed associations unfolded to:
  author : Association to Authors on author_ID = author.ID;
  author_ID : Integer; // added foreign key element
  genre : Association to Genres on genre_ID = genre.ID;
  genre_ID : Integer; // added foreign key element
}
entity Authors {/* as above */}
entity Genres {/* as above */}

This unfolded model is then compiled to DDL, with unmanaged associations skipped:

sql
CREATE TABLE Authors (/* no columns for unmanaged assocs */... )
CREATE TABLE Books (/* no columns for unmanaged assocs */ ...
  author_ID INTEGER -- added foreign key column
  genre_ID  INTEGER -- added foreign key column
);
Associations as Forward-declared JOINs ​

CQL queries that use such associations, for example:

sql
SELECT title, author.name, genre.name from Books
/* Note: author and genre are used like table aliases */

Are enhanced with JOINs as per respective association definitions:

sql
SELECT title, author.name, genre.name from Books --> very same as above
LEFT JOIN Authors as author on author_ID = author.ID; 
LEFT JOIN Genres as genre on genre_ID = genre.ID;

Associations as 'Forward-declared' JOINs

Looking closely at the above compiled SQL code, we can regard associations to be like 'Forward-declared' JOINs, along these lines:

  1. Association names a.name appear in queries as standard table aliases
  2. JOINs are added automatically as per the following construction rule:

JOIN a.target as a.name on a.on

  1. For managed associations with unfolded on conditions:

JOIN a.target as a.name on a.keys = a.name . a.target.keys

where a is an association definition with these properties:
a.target – the target entity's name
a.name – the association's name
a.on – the on condition of an unmanaged association
a.keys – the foreign key element(s), added to the source entity
a.target.keys – the target's respective (primary) key element(s)

Calculated Elements ​

Materialized calculated elements, that is those with a trailing stored keyword, are translated into corresponding database columns with GENERATED ALWAYS AS clauses. In contrast, virtual calculated elements are not represented in the database schema at all, but applied at runtime by the CAP database layers when reading data from the database.

cds
entity Orders {
 quantity : Integer;
 price : Decimal;
 total : Decimal = price * quantity stored; 
 gross : Decimal = total * (1+VAT); // virtual
}
sql
CREATE TABLE Orders (
  quantity INTEGER,
  price DECIMAL,
  total DECIMAL GENERATED ALWAYS AS (price * quantity) STORED 
);

Virtual calculated elements are applied at runtime whenever data is read from the database, for example, a CQL query like this:

sql
SELECT total, gross from Orders;

would be compiled to the following SQL query:

sql
SELECT total, total * (1+VAT) as gross from Orders;

Virtual Elements ​

Virtual elements are not represented in the database schema at all, similar to virtual calculated elements above. They exist only at the CAP runtime layer, and are typically used to represent data coming from external services or other non-persistent sources.

Default Values ​

You can specify default values for elements using the default keyword in element definitions. These defaults are translated into SQL DEFAULT clauses in the generated DDL, in a one-to-one manner.

cds
entity Books {
  available : Boolean default true;
  createdAt : DateTime default current_timestamp;
}
sql
CREATE TABLE Books (
  available BOOLEAN DEFAULT true,
  createdAt TIMESTAMP DEFAULT current_timestamp
);

Consider using @cds.on.insert instead

Instead of using default values, consider using the @cds.on.insert annotation, which provides more flexibility and is more tuned for typical application scenarios.

Invalid Names ​

When you use names in your CDS models that conflict with reserved words of underlying databases, or names that contain non-ASCII characters, special characters, or spaces, these names are considered invalid in many databases. CAP escapes these names in the generated DDL and all queries sent to the database.

For example, the following is a valid CDS model with database-invalid named elements. The generated DDL escapes them with double quotes:

cds
entity BadNames {
  ![a name] : String;  // invalid whitespaces
  ![drôle]  : String;  // invalid diacritics
  ![select] : String;  // reserved word in SQL and CDS
  group    : String;  // reserved word in SQL
}
sql
CREATE TABLE BadNames (
  "a name" NVARCHAR(255),
  "drôle"  NVARCHAR(255),
  "select" NVARCHAR(255),
  "group"  NVARCHAR(255)
);

However, even though CAP allows this, and handles all accesses correctly, it is strongly discouraged to use such names in your CDS models, as that may lead to unexpected issues in several scenarios, not in control of CAP, such as native SQL queries, third-party tools, or integration with non-CAP applications.

DON'T use Database-Invalid Names!

It's strongly discouraged to use names that contain non-ASCII characters, or conflict with database reserved words. Even more avoid delimited names in CDS models in the first place, as that impacts readability of your models.

reserved-words ​

Lists of Reserved Words

Check out the reserved words for the databases you are targeting:
SAP HANA , SQLite , H2 , PostgreSQL

Keys, Constraints ​

Database Constraints ​

CAP supports the generation of various database constraints based on CDS model definitions, as outlined below.

Don't use for end user-facing input validation

Database constraints are meant to protect against data corruption due to programming errors, and are not meant for application-level input validation. If a constraint violation occurs, the error messages coming from the database aren't standardized by the runtimes but presented as-is.

Primary Key Constraints ​

The compiler translates primary keys defined in CDS entities into SQL PRIMARY KEY constraints in the generated DDL. For example:

cds
entity OrderItems {
  key order: Association to Orders;
  key pos: Integer;
  ...
}
sql
CREATE TABLE OrderItems (
  order_ID NVARCHAR(36),
  pos INTEGER,
  ...
  PRIMARY KEY (order_ID, pos)    
);

Not Null Constraints ​

You can specify that a column's value must not be NULL by adding the not null constraint to the element, for example:

cds
entity Books { ...
  title: String not null;
}

Consider using @mandatory instead

Instead of, or in addition to using database-level not null constraints, consider using the @mandatory annotation, which provides more flexibility and is more tuned for typical application scenarios.

Unique Constraints ​

Annotate an entity with @assert.unique.<constraint>, to express one or more, named uniqueness checks on combination of columns. These will be translated to SQL UNIQUE constraints in the generated DDL.

For example, given an entity definition like this:

cds
entity OrderItems { ...
  order : Association to Orders;
  product : Association to Products;
}

Use @assert.unique to ensure that each product appears only once per order:

cds
annotate OrderItems with @assert.unique.product: [ order, product ];

Which would translate to the following SQL UNIQUE constraint in the generated DDL:

sql
CREATE TABLE OrderItems (
  ...
  CONSTRAINT OrderItems_products UNIQUE (order_ID, product_ID)    
);

Multiple named unique constraints per entity are supported, for example:

cds
annotate OrderItems with @assert.unique.product: [ order, product ];
annotate OrderItems with @assert.unique.someOtherConstraint: [ ... ];

  • The <constraint> name in @assert.unique.<constraint> becomes the name of the database constraint.

  • The argument is expected to be an array of flat element references referring to elements in the entity. These elements may have the following types:

    • scalar types - String, Integer, and so on
    • structured types – not elements within structs.
    • managed associations – not unmanaged associations.

  • In case of structs, all flattened columns stemming from it will be included. Similarly, for managed associations: all foreign key columns will be included.

Primary Keys are Unique Constraints

You don't need to specify @assert.unique constraints for the primary keys of an entity as these are automatically secured by a SQL PRIMARY KEY constraint, which enforces uniqueness.

Foreign Key Constraints ​

For managed to-one associations, CAP can automatically generate foreign key constraints in the database. Switch this on globally with config option cds.features.assert_integrity = db.

With this flag switched on, FOREIGN KEY constraints are automatically added to CREATE TABLE statements for managed to-one associations like this:

cds
entity Books {
  author : Association to Authors;
}
sql
CREATE TABLE Books ( ...
  ID INTEGER NOT NULL,
  author_ID INTEGER,       -- added foreign key field
  CONSTRAINT Books_author  -- added foreign key constraint
   FOREIGN KEY(author_ID)
   REFERENCES Authors(ID)
   ON UPDATE RESTRICT
   ON DELETE RESTRICT
   VALIDATED
   ENFORCED
   INITIALLY DEFERRED
)

Consider using @assert.target instead

Database constraints are meant to protect against data corruption due to programming errors. Prefer using the @assert.target for application-level input validation, which is more tuned for typical application scenarios, with error messages taylored for end users.

Skipping with @assert.integrity:false ​

You can skip foreign key constraint generation for specific associations by annotating them with @assert.integrity:false, for example:

cds
entity Books {
  author : Association to Authors @assert.integrity:false;
}

Deferred Enforcement ​

Referential integrity is enforced at the time of transaction commit. This uses the database's deferred foreign key constraints, which are supported by most relational databases, including SAP HANA, SQLite, and PostgreSQL. However, H2 does not support deferred constraints:

Database constraints are not supported for H2

Customizing Options ​

You can customize the generated DDL using specific CDS annotations, as outlined below.

@cds.persistence.skip ​

Annotate an entity with @cds.persistence.skip 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
entity Foo {...}
entity Bar as select from Foo;
annotate Foo with @cds.persistence.skip;
sql
CREATE TABLE Foo ( ... )); -- skipped
CREATE VIEW Bar AS SELECT ... FROM Foo; -- skipped

@cds.persistence.exists ​

Annotate an entity with @cds.persistence.exists to indicate that this entity should be skipped from generated DDL scripts. In contrast to @cds.persistence.skip a database table or view is expected to exist, so we can and will generate SQL views on top.

cds
entity Foo {...}
entity Bar as select from Foo;
annotate Foo with @cds.persistence.exists;
sql
CREATE TABLE Foo ( ... )); -- skipped, but expected to exist
CREATE VIEW Bar AS SELECT ... FROM Foo; -- generated as usual
On SAP HANA ...

When using @cds.persistence.exists for ...

  • User-defined functions (UDFs), annotate it with @cds.persistence.udf in addition.
  • Calculation views, annotate it with @cds.persistence.calcview in addition.

See Calculated Views and User-Defined Functions for more details.

@cds.persistence.table ​

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

cds
entity Foo { key ID : Integer; tag : String; foo : Timestamp; }
entity Bar as select from Foo { ID, tag, true as bar : Boolean; };
annotate Bar with @cds.persistence.table;
sql
CREATE TABLE Foo ( ID INTEGER, tag NVARCHAR(255), foo TIMESTAMP );
CREATE TABLE Bar ( ID INTEGER, tag NVARCHAR(255), bar BOOLEAN ); 
CREATE VIEW Bar AS SELECT ... FROM Foo; -- skipped

Irrelevant parts are ignored

All parts of the view definition not relevant for the signature, such as where, group by, having, order by, or limit, are ignored.

Use Case: Replica Caching Tables

A common use case for this annotation is to create projections on entities from imported APIs, i.e., so-called consumption views, and at the same time use them as replica cache tables.

@sql.prepend / append ​

Annotate entities or elements with @sql.prepend and @sql.append to add native SQL clauses before or after the generated SQL output.

cds
entity Books { ..., title: String }
entity ListOfBooks as select from Books { ... };

annotate Books:title with @sql.append: 'FUZZY SEARCH INDEX ON';
annotate Books with @sql.append: ```sql
  GROUP TYPE foo
  GROUP SUBTYPE bar
```;
annotate ListOfBooks with @sql.append: 'WITH DDL ONLY';
sql
CREATE TABLE Books ( ...,
  title NVARCHAR(100) FUZZY SEARCH INDEX ON
) GROUP TYPE foo
GROUP SUBTYPE bar;
CREATE VIEW ListOfBooks AS SELECT ... FROM Books WITH DDL ONLY;

Note for SAP HANA

Ensure to read Schema Evolution Support of Native Database Clauses if you plan to use these annotations in combination with @cds.persistence.journal.

CAUTION

The content of these annotations is inserted as-is into the generated DDL statements without any validation or other processing by the compiler. Use this feature with caution, as incorrect SQL clauses may lead to deployment failures or runtime errors. 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.

Column vs Row Tables on SAP HANA ​

CAP creates columnar tables by default on SAP HANA, which is accomplished by an implicit @sql.prepend:'COLUMN' applied to all entities translating to tables. You can override this by using @sql.prepend:'ROW' to create a row table instead.

Warning

Whenever you use @sql.prepend, the default @sql.prepend:'COLUMN' is overridden.

Database-Specific Models ​

All the above translations are designed to be portable across different SQL databases supported by CAP. However, there may be scenarios where you need to add database-specific definitions. You can achieve this by using database-specific subfolders in your ./db folder, and configuring your project to use these sub-models based on the target database as follows:

  1. Add database-specific models in respective subfolders of ./db:

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

  2. Add profile-specific configuration to use these database-specific extensions:

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

Find that sample also in @capire/bookstore.