Using Databases

The CAP Node.js runtime provides out-of-the-box support for various databases.

Watch the video to learn about supported databases.

In CAP, the so-called cds-plugin technique is used to integrate a database into an application. Essentially, these database plugins are Node.js packages that allow CAP to connect and interact with a specific database, with no additional configuration required. Each supported database has a corresponding cds-plugin package.

By using these database plugins, developers can easily switch between different databases during development and production without having to change the application code.

To automatically configure SQLite for development, install the corresponding database package with the following command in the terminal:

1
npm add @cap-js/sqlite -D

Execute the following command to use SAP HANA Cloud for production:

1
npm add @sap/cds-hana

The Node.js database packages wire things up automatically through configuration defaults that are set via the cds-plugin mechanism. You can use the default configuration provided by the packages for your application or override individual properties. We will see how to do this later in this learning journey.

To see the effective database configuration, you can run the cds env command for the cds.requires.db key in the root folder of your project, as shown in figure Inspecting the Effective Configuration.

The configuration options returned by the command are as follows:

• impl - the module name of the implementation of a CAP database service
• credentials - an object with db-specific configurations, most commonly url
• kind - a name of a preset, such as sqlite or hana

You can populate the database tables created for your CAP application with initial data.

Proceed as follows to load initial data into these tables as soon as the application is started: For each table defined via the CDS model, create a .csv file with a file name that matches the fully qualified name of the corresponding entity definition in your CDS model. For cosmetic reasons, you can optionally use a dash (-) instead of a dot (.).

For example, in the scenario discussed here, you can create the files com.sap.learning-Authors.csv and com.sap.learning-Books.csv for the model entities com.sap.learning.Authors and com.sap.learning.Books.

The .csv files created must then be filled with standard CSV, whereby the column titles must correspond to the declared element names in the CDS model, as shown in the example in figure Providing Initial Data.

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.

.csv files that are created in the test/data folder are only deployed when the application is not running in production. This folder can therefore be used to store sample data for tests and demos.

.csv files that are created in the db/data folder, on the other hand, are deployed in any environment, including production. This folder is therefore suitable for initial configuration data and data for code lists, for example.

If you enter the following command in the terminal, corresponding .csv files are created in the db/data folder for the tables defined via your CDS model:

1
cds add data

The generated files do not yet contain any data, but they do contain column titles based on your CDS model (see figure Generated .csv Files for Modeled Entities for an example).

SAP strongly recommends using SQLite as much as possible during development and testing. CAP provides extensive support for this database, which enables projects to speed up development and minimize costs.

In this learning journey, we follow this recommendation and use SQLite as database during development. You can learn how to switch to SAP HANA Cloud as database for production in the CAP documentation.

As mentioned above, you need to execute the following command in the terminal to use SQLite for development:

1
npm add @cap-js/sqlite -D

This command adds a dependency similar to the following to the package.json file:

The @cap-js/sqlite package uses the cds-plugin technique to auto-configure an in-memory SQLite database for your application by default. You can see this in the log output when you start your application with cds watch:

You can also use persistent SQLite databases. To do this, carry out the following steps:

1. Specify a database file name in the db configuration in package.json as follows:
   

   In summary, this configuration tells the CAP framework that, during development, it should use an SQLite database located in a file named db.sqlite at the root of the project.

2. Run the following command in the terminal:
   Code Snippet

   Copy codeSwitch to dark mode

   1
   cds deploy

This will:

1. Create a database file with the specified name in the root folder of your project.
2. Create the tables and views according to your CDS model in the database.
3. Fill in the initial data from the provided .csv files.

With that in place, the server will use this prepared database instead of bootstrapping an in-memory database at startup. You can see this in the log output when you start the server via cds watch:

When you redeploy your persistent database with cds deploy, all tables and views are first dropped and then recreated. This is best suited for development environments, where schema changes are very frequent and broad.

However, cds deploy also supports automatic schema evolution for persistent databases. In this case, changed tables are not simply dropped and recreated during redeploy. Instead, the changes are applied via ALTER TABLE.

Please keep in mind, though, that automatic schema evolution only allows changes without potential data loss. Details on schema evolution can be found in the CAP documentation. The linked section in the documentation refers to the PostgreSQL database. But the information there is also applicable to SQLite with persistent databases.

Watch the video to see how to set up a persistent SQLite database with initial data for development.