Skip to content

initial-data.md

Latest commit

 

History

History
248 lines (186 loc) · 8.91 KB

initial-data.md

File metadata and controls

248 lines (186 loc) · 8.91 KB
Raw
description How to add `.csv` files to fill your database with initial and test data, automatically loaded by the CAP runtime.

Adding Initial Data

You can add .csv files to fill your database with initial data and test data. The runtime automatically loads these files whenever you bootstrap a database, run cds watch in development, or deploy and upgrade for production. {.abstract}

[[toc]]

Using cds add data

Use cds add data to generate empty .csv files, with column headers based on the entities in your CDS model. For example, if you run this in the @capire/bookshop sample project, you can see the following output:

cds add data
Adding facet: data

  creating db/data/sap.capire.bookshop-Authors.csv
  creating db/data/sap.capire.bookshop-Books.csv
  creating db/data/sap.capire.bookshop-Books.texts.csv
  creating db/data/sap.capire.bookshop-Genres.csv
  creating db/data/sap.capire.bookshop-Genres.texts.csv

Successfully added features to your project

By default, the command generates the files in the db/data folder of your project structure:

cap/bookshop/
├── db/
│ ├── data/
│ │ └── # generated .csv files
...

You can also specify other folders by providing the --out option, for example:

cds add data -o test/data

Each file contains a CSV header line reflecting the structure of the entity to which it corresponds.

Editing .csv Files

Fill these files with initial data by editing them in your preferred editor. For example, the content of db/data/sap.capire.bookshop-Books.csv looks like this:

::: code-group

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

:::

You can also use cds add data with the --records option to add generic data:

cds add data --records 10

[!tip] AI-Generated Data For more realistic domain-specific data, you can use AI tools, like CoPilot in VS Code, to assist you in that. This works particularly well for small to medium-sized datasets. Moreover, CDS models provide domain models that are easy to reason about, with strong typing information, meaning that AIs can generate better fitting data samples.

The following table shows common rules that apply to text content in .csv files:

Condition Handling
If texts contain commas, or line breaks, or trailing whitespaces -> enclose in double quotes
If texts contain double quotes -> escape by doubling them
Numeric content should be treated as texts -> enclose in double quotes
Boolean values should be treated as text -> enclose in double quotes

Initial vs Test Data

You need to distinguish between (real) initial data meant for production (configuration, code lists) and test data meant for development and testing purposes only. CAP supports this by organizing CSV files in two locations:

cap/bookshop/
├── db/
│ ├── data/
│ │ └── # .csv files for initial data
│ ...
├── test/
│ ├── data/
│ │ └── # .csv files for test data
...

The following table describes the purpose and deployment scope of each location:

Location Purpose Deployed...
db/data (real) initial data always, dev and prod
test/data test data in development only

::: details Bookshop data is actually test data... Note that the initial data provided in the @capire/bookshop sample is actually test data, and hence we would typically place it in the test/data folder. But for simplicity, it's placed in db/data, also because the whole purpose of that project is to be a sample. :::

[!danger] Don't let users modify productive initial data Otherwise this data might get overridden on SAP HANA.

Custom Folders

You can also configure other folders to read data from in different profiles. Use config option cds.requires.db.data to do so. The default configuration is as follows, which you can override in your package.json or .cdsrc.yaml file as appropriate:

::: code-group

"cds": {
  "requires": {
    "db": {
      "[development]": { "data": [ "db/data", "test/data" ] },
      "[production]": { "data": [ "db/data" ] }
    }
  }
}
cds:
  requires:
    db:
      '[development]': { data: [ db/data, test/data ] }
      '[production]': { data: [ db/data ] }

:::

Use cds env to check which configuration is active in your current profile, for example:

cds env requires.db.data --profile development

Next to .cds Files

In addition to the configured folders for initial data and test data (that is, db/data and test/data by default), you can place .csv files into data folders anywhere next to your CDS model source files. For example:

myproject/
├── db/
│ ├── data/*.csv
│ └── schema.cds
├── srv/
│ ├── data/*.csv
│ └── some-service.cds
...

The runtime automatically loads all db/data/*.csv and srv/data/*.csv files located in a data folder next to .cds model sources.

This is especially useful for remote service definitions imported with cds import (by default into srv/external/), allowing you to serve mock data for external services.

From Reuse Packages

The in-the-neighborhood-of-models technique enables reuse packages that provide initial data.

An example of such a content reuse package is @capire/common, which showcases how to provide ISO reuse data for Countries, Currencies, and Languages code lists, as defined in @sap/cds/common. It essentially consists of these files as content:

@capire/common
├── cds-plugin.js
├── data
│   ├── sap.common-Countries.csv
│   ├── sap.common-Countries_texts.csv
│   ├── sap.common-Currencies.csv
│   ├── sap.common-Currencies_texts.csv
│   ├── sap.common-Languages.csv
│   └── sap.common-Languages_texts.csv
├── index.cds
└── package.json

Install such packages via npm or mvn. Their content will reside under node_modules folders or Maven target folders. When packages include reuse models (like index.cds above), the runtime automatically discovers and loads CSV files from the adjacent data folder into the consuming application's database.

[!tip] How to 'enable' reuse packages

The @capire/common package uses the cds-plugin.js technique, to provide plug-and-play configuration in its package.json like that:

"cds": {
  "requires": {
    "@capire/common/data": {
      "model": "@capire/common"
    }
  }
}

Without such plugin configuration, add a using from '@capire/common'; clause to one of your CDS files.

Plug-and-Play Reuse

You can consume reuse packages, such as the @capire/common one described above, in any CAP project by simply installing them via npm or mvn, as shown in the @capire/bookstore sample project:

{
  "name": "@capire/bookstore",
  "version": "1.2.3",
  "dependencies": {
    "@capire/common": "^1.0.0", // [!code focus]
    "@sap/cds": "..."
  }
}

When you run cds watch, the runtime automatically picks up the @capire/common/index.cds model file and all CSV files in its data folder, as shown in the following output:

[cds] - loaded model from 27 file(s):

  app/services.cds
  srv/mashup.cds
  node_modules/@capire/common/index.cds # [!code focus]
  ...
[cds] - connect to db > sqlite { url: ':memory:' }
  > init from node_modules/@capire/common/data/sap.common-Languages_texts.csv # [!code focus]
  > init from node_modules/@capire/common/data/sap.common-Languages.csv # [!code focus]
  > init from node_modules/@capire/common/data/sap.common-Currencies_texts.csv # [!code focus]
  > init from node_modules/@capire/common/data/sap.common-Currencies.csv # [!code focus]
  > ...
/> successfully deployed to in-memory database.

[!tip] Installation is sufficient The great thing with plug-and-play configuration is that a mere npm install in a consuming project suffices to have the @capire/common/index.cds file loaded together with the application’s models, and hence also all data from accompanying .csv files.