Today I will show you how you can deploy a CAP service to Cloud Foundry while using the in-memory SQLite database. This approach is useful when your service needs a simple datastore that does not need to persist between service restarts. For instance, if you are providing a mock service and you wish to allow write operations, a simple restart will restore the data to the initial state.

Setup

I will be using the SAP Business Application Studio for this guide. To start with I generated a project with the sample Bookshop template as shown below:

Setting up SQLite for production:

The first change needed is in the node.js package.json, to move SQLite from a dev-only dependency to be included in production builds as well:

"dependencies": {
    "@sap/cds": "^5",
    "express": "^4",
    "sqlite3": "^5.0.2"
},
"devDependencies": {
}

The next part is to exclude node_modules from the built MTA. Usually having the node modules already set up as part of the MTA build saves time on deployment, however in this case SQLite will fetch the wrong binary for the runtime environment, as the CF environment differs from the BAS environment.

To exclude node_modules from the build, add the build parameters below to the mta.yaml

modules:
 # --------------------- SERVER MODULE ------------------------
 - name: Bookstore-srv
 # ------------------------------------------------------------
   type: nodejs
   path: gen/srv
   provides:
    - name: srv-api      # required by consumers of CAP services (e.g. approuter)
      properties:
        srv-url: ${default-url}
   build-parameters:
    ignore: ["node_modules/"]

If you now run mbt build in the terminal and deploy the built MTAR to CF, you will notice the service does stage and start successfully, however upon access the URL for it, the below error will occur:

<error xmlns="http://docs.oasis-open.org/odata/ns/metadata">
<code>501</code>
<message>The server does not support the functionality required to fulfill the request</message>
</error>

This is because while the SQLite database is running, the CDS service is not aware of it yet. Let’s fix that.

Connecting CDS to the SQLite in-memory database

During the build process, CDS looks in many places for the environment configuration. Today we will add to the package.json, but you can see the full list of sources here: https://cap.cloud.sap/docs/node.js/cds-env#sources-for-cdsenv

Two things are needed here. The first is to tell CDS that we require a db, and that we want to use SQLite for it. The second is to enable the feature for an in-memory database so that CDS will deploy the data model to it on startup.

"cds": {
    "requires": {
      "db": {
        "kind": "sqlite",
        "credentials": {
          "database": ":memory:"
        }
      }
    },
    "features": {
      "in_memory_db": true
    }
  }

Now when we deploy the service and access via the API we no longer see the error previously returned, instead we receive an empty result set, as while the connection is now established, we have not yet loaded any data into the database.

{
  "@odata.context": "$metadata#Books",
  "value": []
}

Adding initial data to the database

If you have worked with the CAP model, you are most likely familiar with how to add data for testing your service. When developing, you can create a folder alongside your data model and any CSV files in this folder that match the pattern of entities within the model will be used to initialize the data model. And indeed with the sample files, there is such a file included: db/data/my.bookshop-Books.csv. This file includes sample entries for our data model and is the one we want to use for this example, however, the process will be the same regardless of your data source.

To include this data in the built MTAR, it needs to be copied over the generated files as part of the build process. Doing so is quite easy, simply add an extra command to the ones run as part of the build in the mta,yaml:

build-parameters:
  before-all:
   - builder: custom
     commands:
      - npm install --production
      - npx -p @sap/cds-dk cds build --production
      - cp -r db/data gen/srv/srv/data

Now when the build runs, any data files will be copied to the build target for the packaging stage of the MTAR creation.

And indeed, after building and deploying our API finally serves our data as expected:

{
  "@odata.context": "$metadata#Books",
  "value": [
    {
      "ID": 1,
      "title": "Wuthering Heights",
      "stock": 100
    },
    {
      "ID": 2,
      "title": "Jane Eyre",
      "stock": 500
    }
  ]
}

Final thoughts

While CAP services are primarily designed to connect to HDI-based data models, sometimes you have a use case where such a heavyweight solution is not necessary and the included SQLite approach previously used while developing the service is adequate for the production scenario. In these cases, I hope this guide is useful in quickly enabling these scenarios.

Keep in mind as shown the data model is persistent only while the service is running. In case you require persistence between restarts, a few changes are needed. I may cover this in a future blog, but in case you wish to get started, the CAP documentation provides a good hint for the adventurous here: https://cap.cloud.sap/docs/get-started/in-a-nutshell#deploying-persistent-databases

If you have any questions please let me know in the comments below, or otherwise share how you have benefited from this guide