Good day fellow CAPpers,
Today I'll share one of the lessons I've learned working with CAP, something that may not be clear in the current tutorials or documentation. It's about deploying a CAP application using SQLite on BTP, Cloud Foundry environment. The key is to make it very clear to the application that you want SQLite, because the default option is HANA, or maybe Postgres. On the capire documentation, they recommend SQLite only for development.
SQLite Dependency
In your package.json, you have to include the sqlite dependency as a project dependency, not only a development one:
"dependencies": {
"@cap-js/sqlite": "^1.7.3",
"@sap/cds": "^8",
"express": "^4",
"sqlite3": "^5"
},
"devDependencies": {
"@sap/cds-dk": "^8",
"@sap/eslint-plugin-cds": "^3"
},
As you may have noted, there are 2 sqlite dependencies: @cap-js/sqlite and sqlite3. The former is supported by the CAP team and should be preferred, but it didn't work in the past when deploying on BTP. Therefore, I had also to include the latter one, which is the default Node.js library for SQLite.
In Memory or File Based
To work with in-memory SQLite is easy. You only need to include in your package.json (or even better in your .cdsrc.json) :
"cds": {
"requires": {
"db": {
"kind": "sqlite",
"impl": "@cap-js/sqlite",
"credentials": {
"url": ":memory:"
}
}
}
}
Optionally, you may also include under the requires level:
"features": {
"in_memory_db": true
}
For a file based SQLite, it isn't hard as well. You only need to change a couple things. First, change the db.credentials.url for the file name of your database. Here it's db.sqlite:
"cds": {
"requires": {
"db": {
"kind": "sqlite",
"impl": "@cap-js/sqlite",
"credentials": {
"url": "db.sqlite"
}
}
}
}
Next, if you want to deploy the database file together with your MTA, you'll have to add an instruction to copy the file into the .mtar so that it goes together. Here's the instructions you have to add on your mta.yaml for Windows (xcopy):
_schema-version: '3.1'
ID: my-app-id
version: 1.0.0
description: "My app description."
build-parameters:
before-all:
- builder: custom
commands:
- npm ci
- npx cds build --production
- xcopy .\\db\\data .\\gen\\srv\\srv\\data\\ /y
- xcopy .\\db.sqlite .\\gen\\srv /y
Note that it copies first the data from db/data, which usually contains configuration data on CAP applications, and later that it copies the actual database file, here named db.sqlite.
For Unix, you may use the cp command:
- cp -r db/data gen/srv/srv/data
- cp -r db.sqlite gen/srv
Common Errors
- If you're working with Fiori Elements and Draft enabled entities, you may get errors on the DraftAdministrativeData or other Draft tables, but that's actually happening due to errors on the database trying to create tables, thus it can't insert data on tables that doesn't exist. It's not related to Fiori.
- If it looks like it's not working with the @cap-js/sqlite dependency, try using sqlite3 instead.
- There are queries (SELECTs, INSERTs, etc.) that work differently when running on different databases (SQLite vs HANA).
Acknowledgements
I'd like to thank:
May this post help you on your troubleshooting. Reply if you find any issues so I, and the community, can help.
Lucas Magrini Rigo
Bluesky
