SQLite as database for a multi-target (/productive) CAP Application

[Christopher_Ketzler]

Intro 

Not recommended but possible. There are scenarios where you only need a small service exposing some data for a show case or a Hana cloud database is not available for some reason. In these cases, you can deploy a cap service as a multi-target application based on a SQLite database. I needed some time to find the correct blogs and information to solve this problem. Many blogs are not detailed enough; thus, I will give you an end-to-end guide for deploying such an multi-target application.  

Prerequisites 

If you have no access to a BTP subaccount, create a trial subaccount and BAS as described below. Create a space in your subaccount. 

Set Up SAP Business Application Studio for Development | SAP Tutorials  

Instructions 

We will create a new CAP project, add a xsuaa instance and deploy this as multi-target application. Before we need to make some changes to the package.json und mta.yaml. 

1. Create a new CAP project from scratch. To avoid unwanted configurations, generate a new project in BAS. Navigate to the “burger menu” in the upper left corner > “File” > “New Project From Template”.
2. Make sure that you are not selecting HANA as database while adding the project details. Instead choose “Cloud Foundry: MTA Deployment” and “Minimal Sample” for adding a simple test schema. 
3.  As you want to deploy a SQLite database you need to add  “sqlite3” as dependency to your package.json (npm i sqlite3). 
4. After adding the dependency, we still need to add some configurations. Extend your package.json to make sure SQLite is defined as “db” and the “in_memory_db” feature is enabled.

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

5. Our project is now configurate for SQLite, but we just need to add a xsuaa by running” cds add xsuaa”. Afterwards replace the auth configuration in the package.json as shown below. 

"auth":{
   "kind":"basic",
   "users":{
      "admin":{
         "password":"admin"
      }
   }
}

Your package.json should now look like this one. 

{
   "name":"cap-sqlite",
   "version":"1.0.0",
   "description":"A simple CAP project.",
   "repository":"<Add your repository here>",
   "license":"UNLICENSED",
   "private":true,
   "dependencies":{
      "@sap/cds":"^7",
      "express":"^4",
      "sqlite3":"^5.1.7",
      "@sap/xssec":"^3"
   },
   "devDependencies":{
      "@cap-js/sqlite":"^1",
      "@sap/cds-dk":"^7",
      "@sap/eslint-plugin-cds":"^3",
      "eslint":"^9"
   },
   "scripts":{
      "start":"cds-serve"
   },
   "cds":{
      "requires":{
         "db":{
            "kind":"sqlite",
            "credentials":{
               "database":"db.sqlite"
            }
         },
         "features":{
            "in_memory_db":true
         },
         "auth":{
            "kind":"basic",
            "users":{
               "admin":{
                  "password":"admin"
               }
            }
         }
      }
   }
}

6. Before starting the deployment, we need to add two commands to the build parameters in your mta.yaml. The first one is “npx cds deploy --to sqlite” and deploys the database. By executing this command, a db.sqlite file is created. This file contains also the initial data stored in the data folder. The second command is copying this file to the gen folder to make it available for the deployed service. 

...
parameters:
  enable-parallel-deployments: true
build-parameters:
  before-all:
    - builder: custom
      commands:
        - npm ci
        - npx cds build --production
        - npx cds deploy
        - cp -r db.sqlite gen/srv
modules:
  - name: cap-sqlite-srv
...

7. Make sure that there are no further db modules listed in your mta.yaml and no hana db artifacts part of the gen folder (gen/db). Your mta.yaml should look like below. Now you are ready to deploy. 

_schema-version: '3.1'
ID: cap-sqlite
version: 1.0.0
description: "A simple CAP project."
parameters:
  enable-parallel-deployments: true
build-parameters:
  before-all:
    - builder: custom
      commands:
        - npm ci
        - npx cds build --production
        - npx cds deploy
        - cp -r db.sqlite gen/srv
modules:
  - name: cap-sqlite-srv
    type: nodejs
    path: gen/srv
    parameters:
      buildpack: nodejs_buildpack
      readiness-health-check-type: http
      readiness-health-check-http-endpoint: /health
    build-parameters:
      builder: npm
    provides:
      - name: srv-api
        properties:
          srv-url: ${default-url}
    requires:
      - name: cap-sqlite-auth

resources:
  - name: cap-sqlite-auth
    type: org.cloudfoundry.managed-service
    parameters:
      service: xsuaa
      service-plan: application
      path: ./xs-security.json
      config:
        xsappname: cap-sqlite-${org}-${space}
        tenant-mode: dedicated

If neccessary remove additional db modules of type hdi container, the db-deployer and references to these ones as marked below. 

modules:
  - name: demo-sqlite-srv
    type: nodejs
    path: gen/srv
    parameters:
      buildpack: nodejs_buildpack
      readiness-health-check-type: http
      readiness-health-check-http-endpoint: /health
    build-parameters:
      builder: npm
    provides:
      - name: srv-api # required by consumers of CAP services (e.g. approuter)
        properties:
          srv-url: ${default-url}
    requires:
      - name: demo-sqlite-db # <= Remove

  - name: demo-sqlite-db-deployer # <= Remove
    type: hdb
    path: gen/db
    parameters:
      buildpack: nodejs_buildpack
    requires:
      - name: demo-sqlite-db

resources:
  - name: demo-sqlite-db # <= Remove
    type: com.sap.xs.hdi-container
    parameters:
      service: hana
      service-plan: hdi-shared

Deployment 

1. Update your dependencies with „npm i“. 
2. Start the mta build by executing „mbt build“ in the terminal. A new mta archive will be generated. 
3. Trigger the deploy by selecting „Deploy the mtar archive“ in the context menu of the mta archive. (It is the .mtar file in the mta folder.) 

After running the deployment, a new service occurs in your BTP cockpit exposing the oData service.  

Conclusion 

As mentioned in the beginning using a SQLite database in production is not recommended, but possible. The schema will be dropped and recreated during deployment, meaning that customers data will be lost. Find a list of further limitations here: https://cap.cloud.sap/docs/java/cqn-services/persistence-services#sqlite 

[ahmedjilani0011]

thank for this blog

[JimSpath]

Tangentially, I ran into SQLite concurrency issues where it is used as a media catalog. I can read data from multiple clients, but for some reason the data model had contention issues on simultaneous read access. 

> SQLite has only limited support for concurrent database access

I read this as "don't even try."