Using SQLite for Development
CAP provides extensive support for SQLite, which allows projects to speed up development by magnitudes at minimized costs. We strongly recommend making use of this option during development and testing as much as possible.
New SQLite Service
This guide focuses on the new SQLite Service provided through @cap-js/sqlite, which has many advantages over the former one, as documented in the Features section. To migrate from the old service, find instructions in the Migration section.
Setup & Configuration
Run this to use SQLite for development:
npm add @cap-js/sqlite -D
Auto-Wired Configuration
The @cap-js/sqlite
uses cds-plugin
technique to auto-configure your application to use an in-memory SQLite database for development.
You can inspect the effective configuration using cds env
:
cds env requires.db
Output:
{
impl: '@cap-js/sqlite',
credentials: { url: ':memory:' },
kind: 'sqlite'
}
See also the general information on installing database packages
Using the Maven Archetype
When a new CAP Java project is created with the Maven Archetype, you can specify the in-memory database to be used. Use the option -DinMemoryDatabase=sqlite
to create a project that uses SQLite as in-memory database.
Manual Configuration
To use SQLite, add a Maven dependency to the SQLite JDBC driver:
<dependency>
<groupId>org.xerial</groupId>
<artifactId>sqlite-jdbc</artifactId>
<scope>runtime</scope>
</dependency>
The further configuration depends on whether you run SQLite as an in-memory database or as a file-based database.
Deployment
In-Memory Databases
As stated previously @cap-js/sqlite
uses an in-memory SQLite database by default. For example, you can see this in the log output when starting your application, with cds watch
:
...
[cds] - connect to db > sqlite { url: ':memory:' }
> init from db/init.js
> init from db/data/sap.capire.bookshop-Authors.csv
> init from db/data/sap.capire.bookshop-Books.csv
> init from db/data/sap.capire.bookshop-Books.texts.csv
> init from db/data/sap.capire.bookshop-Genres.csv
/> successfully deployed to in-memory database.
...
TIP
Using in-memory databases is most recommended for test drives and for test pipelines.
The database content is stored in-memory. Configure the build to create an initial schema.sql file for SQLite using cds deploy --to sqlite --dry > srv/src/main/resources/schema.sql
.
Finally, configure the DB connection in the non-productive default
profile:
---
spring:
profiles: default
sql:
init:
mode: always
datasource:
url: "jdbc:sqlite:file::memory:?cache=shared"
driver-class-name: org.sqlite.JDBC
hikari:
maximum-pool-size: 1
max-lifetime: 0
Persistent Databases
You can also use persistent SQLite databases. Follow these steps to do so:
You can also use persistent SQLite databases. In this case, the schema is initialized by cds deploy
and not by Spring. Follow these steps:
Specify a database filename in your
db
configuration as follows:json{ "cds": { "requires": { "db": { "kind": "sqlite", "credentials": { "url": "db.sqlite" } } }}}
Run
cds deploy
:shcds deploy
This will...:
- Create a database file with the given name.
- Create the tables and views according to your CDS model.
- Fill in initial data from provided .csv files.
With that in place, when starting the server it uses this prepared database instead of bootstrapping an in-memory one:
...
[cds] - connect to db > sqlite { url: 'db.sqlite' }
...
Finally, configure the DB connection - ideally in a dedicated sqlite
profile:
---
spring:
profiles: sqlite
datasource:
url: "jdbc:sqlite:sqlite.db"
driver-class-name: org.sqlite.JDBC
hikari:
maximum-pool-size: 1
Redeploy on changes
Remember to always redeploy your database whenever you change your models or your data. Just run cds deploy
again to do so.
Drop-Create Schema
When running repeatedly, it will always drop-create all tables and views. This is most appropriate for development as schema changes are very frequent and broad during development.
Schema Evolution
While drop-create is most appropriate for development, it isn't for database upgrades in production, as all customer data would be lost. To avoid this cds deploy
also supports automatic schema evolution, which you can use as follows...
Enable automatic schema evolution in your
db
configuration:json{ "cds": { "requires": { "db": { "kind": "sqlite", "credentials": { "url": "db.sqlite" }, "schema_evolution": "auto" } }}}
Run
cds deploy
:shcds deploy
Features
CAP supports most of the major features on SQLite:
Following is an overview of advanced features supported by the new database services.
These apply to all new database services, SQLiteService, HANAService, and PostgresService.
Path Expressions & Filters
The new database service provides full support for all kinds of path expressions, including infix filters, and exists predicates. For example, you can try this out with cap/samples as follows:
cds repl --profile better-sqlite
var { server } = await cds.test('bookshop'), { Books, Authors } = cds.entities
await INSERT.into (Books) .entries ({ title: 'Unwritten Book' })
await INSERT.into (Authors) .entries ({ name: 'Upcoming Author' })
await SELECT `from ${Books} { title as book, author.name as author, genre.name as genre }`
await SELECT `from ${Authors} { books.title as book, name as author, books.genre.name as genre }`
await SELECT `from ${Books} { title as book, author[ID<170].name as author, genre.name as genre }`
await SELECT `from ${Books} { title as book, author.name as author, genre.name as genre }` .where ({'author.name':{like:'Ed%'},or:{'author.ID':170}})
await SELECT `from ${Books} { title as book, author.name as author, genre.name as genre } where author.name like 'Ed%' or author.ID=170`
await SELECT `from ${Books}:author[name like 'Ed%' or ID=170] { books.title as book, name as author, books.genre.name as genre }`
await SELECT `from ${Books}:author[150] { books.title as book, name as author, books.genre.name as genre }`
await SELECT `from ${Authors} { ID, name, books { ID, title }}`
await SELECT `from ${Authors} { ID, name, books { ID, title, genre { ID, name }}}`
await SELECT `from ${Authors} { ID, name, books.genre { ID, name }}`
await SELECT `from ${Authors} { ID, name, books as some_books { ID, title, genre.name as genre }}`
await SELECT `from ${Authors} { ID, name, books[genre.ID=11] as dramatic_books { ID, title, genre.name as genre }}`
await SELECT `from ${Authors} { ID, name, books.genre[name!='Drama'] as no_drama_books_count { count(*) as sum }}`
await SELECT `from ${Authors} { books.genre.ID }`
await SELECT `from ${Authors} { books.genre }`
await SELECT `from ${Authors} { books.genre.name }`
Optimized Expands
The old database service implementation(s) translated deep reads, that is, SELECTs with expands, into several database queries and collected the individual results into deep result structures. The new service uses json_object
functions and alike to instead do that in one single query, with sub selects, which greatly improves performance.
Example:
SELECT.from(Authors, a => {
a.ID, a.name, a.books (b => {
b.title, b.genre (g => {
g.name
})
})
})
Required three queries with three roundtrips to the database, now only one query is required.
Localized Queries
With the old implementation when running queries like SELECT.from(Books)
would always return localized data, without being able to easily read the non-localized data. The new service does only what you asked for, offering new SELECT.localized
options:
let books = await SELECT.from(Books) //> non-localized data
let lbooks = await SELECT.localized(Books) //> localized data
Usage variants include:
SELECT.localized(Books)
SELECT.from.localized(Books)
SELECT.one.localized(Books)
Standard Operators
The new database services guarantee identical behavior of these logic operators:
==
,=
— with= null
being translated tois null
!=
,<>
— with!=
translated toIS NOT
in SQLite
<
,>
,<=
,>=
— are supported as is in standard SQL
Especially, the translation of !=
to IS NOT
in SQLite — or to IS DISTINCT FROM
in standard SQL, or to an equivalent polyfill in SAP HANA — greatly improves portability of your code.
Standard Functions
A specified set of standard functions is now supported in a database-agnostic, hence portable way, and translated to database-specific variants or polyfills. These functions are by and large the same as specified in OData:
concat(x,y,...)
— concatenates the given stringscontains(x,y)
— checks whethery
is contained inx
, may be fuzzysearch(xs,y)
— checks whethery
is contained in any ofxs
, may be fuzzystartswith(x,y)
— checks whethery
starts withx
endswith(x,y)
— checks whethery
ends withx
matchesPattern(x,y)
— checks whetherx
matches regexy
substring(x,i,n)
— extracts a substring fromx
starting ati
with lengthn
1indexof(x,y)
— returns the (zero-based) index of the first occurrence ofy
inx
length(x)
— returns the length of stringx
tolower(x)
— returns all-lowercasedx
toupper(x)
— returns all-uppercasedx
ceiling(x)
— returns ceiledx
session_context(v)
— with standard variable names → see belowyear
month
,day
,hour
,minute
,second
— return parts of a datetime
1 Argument
n
is optional
The db service implementation translates these to the best-possible native SQL functions, thus enhancing the extend of portable queries.
For example, this CQL query:
SELECT from Books where search((title,descr),'y')
Gets translated to this native SQLite query:
SELECT * from sap_capire_bookshop_Books
WHERE ifnull(instr(lower(title),lower('y')),0)
OR ifnull(instr(lower(descr),lower('y')),0)
Note: only single values are supported for the second argument
y
.
WARNING
Note that usage is case-sensitive, which means you've to write these functions exactly as given above; all-uppercase usages aren't supported.
SAP HANA Functions
In addition to the standard functions, which all new database services support, the new SQLite service also supports these common SAP HANA functions, to further increase the scope for portable testing:
years_between
months_between
days_between
seconds_between
nano100_between
With open source and the new database service architecture, we also have methods in place to enhance this list by custom implementation.
Both usages are allowed here: all-lowercase as given above, as well as all-uppercase.
Session Variables
The new SQLite service can leverage better-sqlite's user-defined functions to support session context variables. In particular, the pseudo variables $user.id
, $user.locale
, $valid.from
, and $valid.to
are available in native SQL queries like so:
SELECT session_context('$user.id')
SELECT session_context('$user.locale')
SELECT session_context('$valid.from')
SELECT session_context('$valid.to')
Among other, this allows us to get rid of static helper views for localized data like localized_de_sap_capire_Books
.
Portable API
The API as shown below with function session_context()
and the specific pseudo variable names is supported by all new database services, that is, for SQLite, PostgreSQL and SAP HANA. This allows you to write respective code once and run it on all these databases.
Using Lean Draft
The old implementation was overly polluted with draft handling. But as draft is actually a Fiori UI concept, nothing of that should show up in database layers. Hence, we eliminated all draft handling from the new database service implementations, and implemented draft in a modular, non-intrusive way — called 'Lean Draft'. The most important change is that we don't do expensive UNIONs anymore but work with single cheap selects.
Consistent Timestamps
Values for elements of type DateTime
and Timestamp
are handled in a consistent way across all new database services along these lines...
TIP
When we say Timestamps, we mean elements of type Timestamp
as well as DateTime
. Both are essentially the same type just with different precision: While DateTime
elements have seconds precision only, Timestamp
has milliseconds precision in SQLite, and microsecond precision in case of SAP HANA and PostgreSQL.
Writing Timestamps
When writing data using INSERT, UPSERT or UPDATE, you can provide values for DateTime
and Timestamp
elements as JavaScript Date
objects or ISO 8601 Strings. All input is normalized to ensure DateTime
and Timestamp
values can be safely compared. In case of SAP HANA and PostgreSQL they're converted to native types, in case of SQLite they're stored as ISO 8601 Strings in Zulu timezone as returned by JavaScript's Date.toISOString()
.
For example:
await INSERT.into(Books).entries([
{ createdAt: new Date }, //> stored .toISOString()
{ createdAt: '2022-11-11T11:11:11Z' }, //> padded with .000Z
{ createdAt: '2022-11-11T11:11:11.123Z' }, //> stored as is
{ createdAt: '2022-11-11T11:11:11.1234563Z' }, //> truncated to .123Z
{ createdAt: '2022-11-11T11:11:11+02:00' }, //> converted to zulu time
])
Reading Timestamps
Timestamps are returned as they're stored in a normalized way, with milliseconds precision, as supported by JavaScript Date
object. For example, the entries inserted previously would return as follows:
await SELECT('createdAt').from(Books).where({title:null})
[
{ createdAt: '2023-08-10T14:24:30.798Z' },
{ createdAt: '2022-11-11T11:11:11.000Z' },
{ createdAt: '2022-11-11T11:11:11.123Z' },
{ createdAt: '2022-11-11T11:11:11.123Z' },
{ createdAt: '2022-11-11T09:11:11.000Z' }
]
DateTime
elements are returned with second precision, with all fractional second digits truncated. That is, if createdAt
in our examples would be a DateTime
, the previous query would return this:
[
{ createdAt: '2023-08-10T14:24:30Z' },
{ createdAt: '2022-11-11T11:11:11Z' },
{ createdAt: '2022-11-11T11:11:11Z' },
{ createdAt: '2022-11-11T11:11:11Z' },
{ createdAt: '2022-11-11T09:11:11Z' }
]
Comparing DateTimes & Timestamps
You can safely compare DateTimes & Timestamps with each other and with input values. The input values have to be Date
objects or ISO 8601 Strings in Zulu timezone and with three fractional digits.
For example, all these work:
SELECT.from(Foo).where `someTimestamp = anotherTimestamp`
SELECT.from(Foo).where `someTimestamp = someDateTime`
SELECT.from(Foo).where `someTimestamp = ${new Date}`
SELECT.from(Foo).where `someTimestamp = ${req.timestamp}`
SELECT.from(Foo).where `someTimestamp = ${'2022-11-11T11:11:11.123Z'}`
While these would fail, because the input values don't comply to the rules:
SELECT.from(Foo).where `createdAt = ${'2022-11-11T11:11:11+02:00'}` // non-zulo time zone
SELECT.from(Foo).where `createdAt = ${'2022-11-11T11:11:11Z'}` // missing 3-digit fractions
This is because we never can reliably infer the types of input to where clause expressions, hence, that input will not receive any normalisation but be passed down as is as plain string.
Always ensure proper input in where
clauses
Either use strings in format YYYY-MM-DDThh:mm:ss.fffZ
strict, or Date
objects, as follows:
SELECT.from(Foo).where ({ createdAt: '2022-11-11T11:11:11.000Z' })
SELECT.from(Foo).where ({ createdAt: new Date('2022-11-11T11:11:11Z') })
The rules regarding Timestamps apply to all comparison operators: =
, <
, >
, <=
, >=
Improved Performance
The combination of the above-mentioned improvements commonly leads to significant performance improvements. For example displaying the list page of Travels in cap/sflight took >250ms in the past, and ~15ms now.
Migration
While we were able to keep all public APIs stable, we had to apply changes and fixes to some undocumented behaviours and internal APIs in the new implementation. While not formally breaking changes, you may have used or relied on these undocumented APIs and behaviours. In that case, find instructions about how to resolve this in the following sections.
These apply to all new database services, SQLiteService, HANAService, and PostgresService.
Use Old and New in Parallel
During migration you may want to occasionally run and test your app with both, the new SQLite service and the old one. Do so as follows...
Add the new service with
--no-save
shnpm add @cap-js/sqlite --no-save
This bypasses the cds-plugin mechanism, which works through package dependencies.
Run or test your app with the
better-sqlite
profile using one of these options:shcds watch bookshop --profile better-sqlite
shCDS_ENV=better-sqlite cds watch bookshop
shCDS_ENV=better-sqlite jest --silent
Run or test your app with the old SQLite service as before:
shcds watch bookshop
shjest --silent
Avoid UNIONs and JOINs
Many advanced features supported by the new database services, like path expressions or deep expands, rely on the ability to infer queries from CDS models. This task gets extremely complex when adding UNIONs and JOINs to the equation — at least the effort and overhead is hardly matched by generated value. Therefore, we dropped support of UNIONs and JOINs in CQN queries.
For example, this means queries like that are deprecated / not supported any longer:
SELECT.from(Books).join(Authors,...)
Mitigations:
Use path expressions instead of joins — actually the former lack of support for path expressions was the most common reason for having to use joins at all.
Use plain SQL queries like that:
jsawait db.run(`SELECT from ${Books} join ${Authors} ...`)
Use helper views modeled in CDS, which still supports all complex UNIONs and JOINs, then use this view via
cds.ql
.
Fixed Localized Data
Formerly, when reading data using cds.ql, it always returned localized data. For example:
SELECT.from(Books) // always read from localized.Books instead
This wasn't only wrong, but also expensive. Localized data is an application layer concept. Database services should return, what was asked for, nothing else. → Use Localized Queries if you really want to read localized data from the database:
SELECT.localized(Books) // reads localized data
SELECT.from(Books) // reads plain data
No changes to app services behaviour
Generic application service handlers use SELECT.localized to request localized data from the database. Hence, CAP services automatically serve localized data as before.
Skipped BLOBs
Formerly LargeBinary
elements, aka BLOBs, always got served as any other column. Now, they are skipped from SELECT * queries. Yet, you can still enforce reading them by explicitly selecting them.
For example:
SELECT.from(Books) //> [{ ID, title, ..., image }]
SELECT.from(Books) //> [{ ID, title, ... }]
SELECT('image').from(Books) //> [{ image }]
Avoid direct reads of BLOBs
Even if we still support direct reads as shown in line three above, you should generally refrain from using that option. Reason is that BLOBs hold potentially large amounts of data, so they should be streamed. Another reason is that some databases don't support that. If you really need to do such thing, consider using non-large Binary
elements instead.
Skipped Virtuals
In contrast to former behaviour, new database services ignore all virtual elements and hence don't add them to result set entries. Selecting only virtual elements in a query leads to an error.
Reasoning...
Virtual elements are meant to be calculated and filled in by custom handlers of your application services. Nevertheless, the old database services always returned null
, or specified default
values, for virtual elements. This behavior was removed, as it provides very little value, if at all.
For example, given that definition:
entity Foo {
virtual foo : Integer;
bar : Integer;
}
Behavior changed like that:
SELECT.from('Foo') //> [{ foo:1, bar:null }, ...]
SELECT.from('Foo') //> [{ foo:1 }, ...]
SELECT('bar').from('Foo') //> ERROR: no columns to read
<> Operator
Before, both <>
and !=
were translated to name <> 'John' OR name is null
.
- Operator
<>
now works as specified in SQL standard. name != 'John'
is translated as before toname <> 'John' OR name is null
.
WARNING
This is a breaking change to the previous implementation.
Miscellaneous
- Only
$now
and$user
are supported as values for@cds.on.insert/update
. - CQNs with subqueries require table aliases to refer to elements of outer queries.
- Table aliases must not contain dots.
- CQNs with an empty columns array now throws an error.
*
isn't a column reference, usecolumns: ['*']
instead ofcolumns: [{ref:'*'}]
.- Column names in CSVs must map to physical column names:
ID;title;author_ID;currency_code
ID;title;author.ID;currency.code
Adopt Lean Draft
As mentioned in Using Lean Draft, we eliminated all draft handling from new database service implementations, and instead implemented draft in a modular, non-intrusive, and optimized way — called 'Lean Draft'.
When using the new service the new cds.fiori.lean_draft
mode is automatically switched on. You may additionally switch on cds.fiori.draft_compat
in case you run into problems.
More detailed documentation for that is coming.
Finalizing Migration
When you finished migration remove the old sqlite3 driver :
npm rm sqlite3
And activate the new one as cds-plugin:
npm add @cap-js/sqlite --save
SQLite in Production?
As stated in the beginning, SQLite is mostly intended to speed up development, not for production. This is not because of limited warranties or lack of support, it's only because of suitability. A major criterion is this:
Cloud applications usually are served by server clusters, in which each server is connected to a shared database. SQLite could only be used in such setups with the persistent database file accessed through a network file system; but this is rarely available and slow. Hence, an enterprise client-server database is the better choice for that.
Having said this, there can indeed be scenarios where SQLite might be used also in production, such as using SQLite as in-memory caches. → Find a detailed list of criteria on the sqlite.org website.
WARNING
SQLite has only limited support for concurrent database access due to its very coarse lock granularity. This makes it badly suited for applications with high concurrency.