Databases
cds.DatabaseService
and subclasses thereof are technical services representing persistent storage.
cds.DatabaseService class
class cds.DatabaseService extends cds.Service
srv.begin () → this
In case of database services this actually starts the transaction by acquiring a physical connection from the connection pool, and optionally sends a command to the database like BEGIN TRANSACTION
.
This method is called automatically by the framework on the first query, so you never have to call it in application coding. There are only very rare cases where you'd want to do so, for example to reuse a tx
object to start subsequent physical transactions after a former commit
or rollback
. But this is not considered good practice.
cds.DatabaseService — Consumption
InsertResult
(Beta)
- On INSERT, DatabaseServices return an instance of
InsertResult
defined as follows:- Iterator that returns the keys of the created entries, for example:
- Example:
[...result]
->[{ ID: 1 }, { ID: 2 }, ...]
- In case of
INSERT...as(SELECT...)
, the iterator returns{}
for each row
- Example:
affectedRows
: the number inserted (root) entries or the number of affectedRows in case of INSERT into SELECTvalueOf()
: returnsaffectedRows
such that comparisons likeresult > 0
can be usedTIP
===
can’t be used as it also compares the type
- Iterator that returns the keys of the created entries, for example:
cds.DatabaseService — Configuration
Presets
We support some convenience presets allowing for minimized configuration and sensible out-of-the-box defaults. You may also use them in combination with configuration profiles.
HANA Cloud
This is the shortcut:
{
"cds": {
"requires": {
"db": "hana-cloud"
}
}
}
{
"cds": {
"requires": {
"db": "hana-cloud"
}
}
}
This is the expanded version:
{
"cds": {
"requires": {
"db": {
"kind": "hana",
"deploy-format": "hdbtable"
}
}
}
}
{
"cds": {
"requires": {
"db": {
"kind": "hana",
"deploy-format": "hdbtable"
}
}
}
}
HANA Cloud for Multitenancy
This is the shortcut:
{
"cds": {
"requires": {
"db": "hana-mt"
}
}
}
{
"cds": {
"requires": {
"db": "hana-mt"
}
}
}
This is the expanded version:
{
"cds": {
"requires": {
"db": {
"kind": "hana",
"deploy-format": "hdbtable",
"vcap": { "label": "service-manager" }
}
}
}
}
{
"cds": {
"requires": {
"db": {
"kind": "hana",
"deploy-format": "hdbtable",
"vcap": { "label": "service-manager" }
}
}
}
}
Multitenant SQLite/HANA Stack for Streamlined MTX
This is the shortcut:
{
"cds": {
"requires": {
"db": "sql-mt"
}
}
}
{
"cds": {
"requires": {
"db": "sql-mt"
}
}
}
This is the expanded version:
{
"cds": {
"requires": {
"db": {
"[development]": "sqlite",
"[production]": "hana-mt"
}
}
}
}
{
"cds": {
"requires": {
"db": {
"[development]": "sqlite",
"[production]": "hana-mt"
}
}
}
}
Pool
Instead of opening and closing a database connection for every request, we use a pool to reuse connections. By default, the following pool configuration is used:
{
"acquireTimeoutMillis": <if (NODE_ENV='production') 1000 else 10000>,
"evictionRunIntervalMillis": <2 * (idleTimeoutMillis || softIdleTimeoutMillis || 30000)>,
"min": 0,
"max": 100,
"numTestsPerEvictionRun": <(max - min) / 3>,
"softIdleTimeoutMillis": 30000,
"idleTimeoutMillis": 30000,
"testOnBorrow": true,
"fifo": false
}
{
"acquireTimeoutMillis": <if (NODE_ENV='production') 1000 else 10000>,
"evictionRunIntervalMillis": <2 * (idleTimeoutMillis || softIdleTimeoutMillis || 30000)>,
"min": 0,
"max": 100,
"numTestsPerEvictionRun": <(max - min) / 3>,
"softIdleTimeoutMillis": 30000,
"idleTimeoutMillis": 30000,
"testOnBorrow": true,
"fifo": false
}
The generic-pool has a built-in pool evictor, which inspects idle database connections in the pool and destroys them if they are too old.
The following parameters are provided in the pool configuration:
acquireTimeoutMillis: The parameter specifies how much time it is allowed to wait an existing connection is fetched from the pool or a new connection is established.
evictionRunIntervalMillis: The parameter specifies how often to run eviction checks. In case of 0 the check is not run.
min: Minimum number of database connections to keep in pool at any given time.
WARNING
This should be kept at the default 0. Otherwise every eviction run destroys all unused connections older than
idleTimeoutMillis
and afterwards creates new connections untilmin
is reached.max: Maximum number of database connections to keep in pool at any given time.
numTestsPerEvictionRun: Number of database connections to be checked with one eviction run.
softIdleTimeoutMillis: Amount of time database connection may sit idle in the pool before it is eligible for eviction. At least "min" connections should stay in the pool. In case of -1 no connection can get evicted.
idleTimeoutMillis: The minimum amount of time that a database connection may stay idle in the pool before it is eligible for eviction due to idle time. This parameter supercedes softIdleTimeoutMillis.
testOnBorrow: Should the pool validate the database connections before giving them to the clients?
fifo: If false, the most recently released resources will be the first to be allocated (stack). If true, the oldest resources will be first to be allocated (queue). Default value: false.
Pool configuration can be adjusted by setting the pool
option as shown in the following example:
{
"cds": {
"requires": {
"db": {
"kind": "hana",
"pool": {
"acquireTimeoutMillis": 5000,
"min": 0,
"max": 100,
"fifo": true
}
}
}
}
}
{
"cds": {
"requires": {
"db": {
"kind": "hana",
"pool": {
"acquireTimeoutMillis": 5000,
"min": 0,
"max": 100,
"fifo": true
}
}
}
}
}
❗ Warning
The parameters are very specific to the current technical setup, such as the application environment and database location. Even though we provide a default pool configuration, we expect that each application provides its own configuration based on its specific needs.
cds.DatabaseService — UPSERT
Upsert updates existing entity records from the given data or inserts new ones if they don't exist in the database. UPSERT
statements can be created with the UPSERT query API:
UPSERT.into('db.Books')
.entries({ ID: 4711, title: 'Wuthering Heights', stock: 100 })
UPSERT.into('db.Books')
.entries({ ID: 4711, title: 'Wuthering Heights', stock: 100 })
UPSERT
queries are translated into DB native upsert statements, more specifically they unfold to an UPSERT SQL statement on SAP HANA and to an INSERT ON CONFLICT SQL statement on SQLite.
The main use case of upsert is data replication.
If upsert data is incomplete only the given values are updated or inserted, which means the UPSERT
statement has "PATCH semantics".
WARNING
Even if an entity doesn't exist in the database:
→ Upsert is not equivalent to Insert.
The following actions are not performed on upsert:
- UUID key values are not generated.
- The
@cds.on.insert
annotation is not handled. - Elements are not initialized with default values if the element's value is not given.
- Generic CAP handlers, such as audit logging, are not invoked.
UPSERT
statements don't have a where clause. The key values of the entity that is upserted are extracted from the data.
TIP
The upsert data must contain all key elements of the entity.
WARNING
In contrast to the Java runtime, deep upserts and delta payloads are not yet supported.
More to Come
This documentation is not complete yet, or the APIs are not released for general availability. There's more to come in this place in upcoming releases.