Search

    Using Databases

    This guide will help you to learn about defining, providing, implementing, deploying, and publishing services — so it’s about Service Providers in general.

    Content

    Reserved Words and Keywords

    Reserved words have a special meaning and can’t be used, for example, as identifiers. The list of reserved words depends on the database.

    The CDS compiler issues warnings if a reserved word of SQLite or SAP HANA is used. However, for other databases this is not the case. Find here a collection of resources on selected databases and their reference documentation:

    There also reserved words related to SAP Fiori.

    Deployment

    Get an overview of your deployment options.

    SQLite

    The fastest way to let your application run is using a local SQLite database via the sqlite3 npm module, which is a devDependency of your project. Using the cds deploy --to sqlite command line helps you deploy the database parts of the project to a local SQLite database. It does the following:

    • Creates an SQLite database file in your project.
    • Drops existing tables and views, and re-creates them according to the CDS model.
    • Deploys CSV files with initial data.

    See it in action

    SAP HANA

    When you’re moving from the development phase to the production phase, use SAP HANA Cloud as your database. There are 2 ways to include SAP HANA in your setup: Use SAP HANA in a hybrid mode, meaning running your services locally and connecting to your database in the cloud, or running your whole application on SAP Business Technology Platform. This is possible either in trial accounts or in productive accounts. To make the following configuration steps work, we assume that you’ve provisioned, set up, and started, for example, your SAP HANA Cloud instance in the trial environment. If you need to prepare your SAP HANA first, see How to Get an SAP HANA Cloud Instance for SAP Business Technology Platform, Cloud Foundry environment to learn about your options.

    Enhance Project Configuration for SAP HANA Cloud

    To prepare the project, execute:

    cds add hana
    

    This configures deployment for SAP HANA to use the hdbtable and hdbview formats. The default format of hdbcds isn’t available on SAP HANA Cloud.

    For Node.js

    No further configuration is necessary. For your information, this is what the previous command changed in package.json:

    • The hdb driver for SAP HANA is added as a dependency.
    • A datasource of type sql is added in the cds.requires.db block. See Node.js configuration for more details.
    For Java

    See the Use SAP HANA as the Database for a CAP Java Application tutorial for the rest of the configuration.

    Deploy using cds deploy

    cds deploy --to hana lets you deploy just the database parts of the project to an SAP HANA instance. The server application (the Node.js or Java part) still runs locally and connects to the remote database instance, allowing for fast development roundtrips.

    Make sure that you’re logged in to Cloud Foundry. Then in the project root folder, just execute:

    cds deploy --to hana
    

    Behind the scenes, cds deploy --to hana does the following:

    • Compiles the CDS model to SAP HANA files (usually in gen/db, or db/gen)
    • Generates hdbtabledata files for the CSV files in the project. If an hdbtabledata file is already present next to the CSV files, no new file is generated.
    • Creates a Cloud Foundry service of type hdi-shared, which creates an HDI container. Also, you can explicitly specify the name like so: cds deploy --to hana:<myService>.
    • Starts @sap/hdi-deploy locally. Should you need a tunnel to access the database, you can specify its address with --tunnel-address <host:port>.
    • Puts default-env.json in the project root. With this information, cds watch/run can connect to the HDI container at runtime using the production profile (--production).

    Learn more about the deployment using HDI

    If you run into issues, see the Troubleshooting guide.

    Deploy Using cf deploy or cf push

    See the Deploying to Cloud Foundry guide for information about how to deploy the complete application to SAP BTP.

    Providing Initial Data

    CSV files in your project are picked up by deployments for both SQLite and SAP HANA. If you’ve accidentally deployed such data to a productive database, see this troubleshooting entry on how to recover from this situation.

    Please note though that for SAP HANA, initial data provided by reuse modules is not deployed automatically. Instead, copy the files manually or through a custom script that is executed before cds build.

    The following conventions apply:

    • The files must be located in the db/csv, db/data, or db/src/csv folders.
    • Each file contains data for one entity.
    • File names must follow the pattern namespace-entity.csv.
      Pattern for nested entities: namespace-entity.nestedEntity.csv.
      Examples: my.bookshop-Books.csv, or my.bookshop-Books.ISBN.csv.
    • They must start with a header line that lists the needed element names.

    🚫 Danger
    On SAP HANA, only use CSV files for configuration data that can’t be changed by application users. CSV files are deployed as hdbtabledata, which assumes exclusive ownership of the data. It’s overwritten with the next application deployment. To avoid such a situation, you can use the include_filter option that hdbtabledata offers.

    Native Features

    Functions

    You can use native database functions inside your CDS model, as long as they conform to the standard syntax of function(param1, param2). The compiler doesn’t do any expansions for such cases - references to structured elements remain structured. In addition to specifying the function with parameters, you also need to provide an alias for the select item and a type.

    entity Books {
        key id: Integer;
        name: String;
        description: String;
        author: String;
    };
    
    entity BookPreview as select from Books {
        id,
        // HANA function IFNULL
        IFNULL(description, name) as shorttext: String,
        author
    };
    

    Using native functions means that deployment to other databases where these functions don’t exist fails. These errors only come up during deployment time, not compile time. To use native SAP HANA function but still use SQLite for development, some configuration is needed. There’s a CAP sample illustrating this. In short: The database specific files are separated into a sqlite and hana folder. With the development profile, cds build is configured to pull in the sqlite sources - with production, sources from hana are used.

    For more complex functions, where the syntax isn’t supported by the compiler, a native .hdbview can be used. See Using Native SAP HANA Artifacts for more details.

    Show/Hide Beta Features