Using SQLite for Development ​

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

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. 
...

You can also use persistent SQLite databases. Follow these steps to do so:

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' }
...

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 to is null
• !=, <> — with != translated to IS 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 strings
• contains(x,y) — checks whether y is contained in x, may be fuzzy
• search(xs,y) — checks whether y is contained in any of xs, may be fuzzy
• startswith(x,y) — checks whether y starts with x
• endswith(x,y) — checks whether y ends with x
• matchesPattern(x,y) — checks whether x matches regex y
• substring(x,i,n) — extracts a substring from x starting at i with length n ¹
• indexof(x,y) — returns the (zero-based) index of the first occurrence of y in x
• length(x) — returns the length of string x
• tolower(x) — returns all-lowercased x
• toupper(x) — returns all-uppercased x
• ceiling(x) — returns ceiled x
• session_context(v) — with standard variable names → see below
• year month, day, hour, minute, second — return parts of a datetime

¹ 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.

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.

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...

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.

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...

1. Add the new service with --no-save

   sh

   npm add @cap-js/sqlite --no-save

   This bypasses the cds-plugin mechanism, which works through package dependencies.

2. Run or test your app with the better-sqlite profile using one of these options:

   sh

   cds watch bookshop --profile better-sqlite

   sh

   CDS_ENV=better-sqlite cds watch bookshop

   sh

   CDS_ENV=better-sqlite jest --silent

3. Run or test your app with the old SQLite service as before:

   sh

   cds watch bookshop

   sh

   jest --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:

1. 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.

2. Use plain SQL queries like that:

   js

   await db.run(`SELECT from ${Books} join ${Authors} ...`)

3. 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

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 }]

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.

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 to name <> 'John' OR name is null.

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, use columns: ['*'] instead of columns: [{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