CAP and its documentation capire are continuously growing (https://cap.cloud.sap/docs/node.js/cds-ql). While there are many code samples, it might be overwhelming to understand the object-relational querying syntax of CAP. The aforementioned documentation gives guidance about the capabilities of the NodeJS version of CAP querying. This blog posts intends to give various code samples for fast development and getting an overview of out-of-the-box capabilities.

Prerequisites

1. Make sure you have a schema.cds or data-model.cds

For this blog post you should have already your data model defined. There are various documentations pages and blog posts about CAP’s capabilities of data modelling. In the following, we will use this sample schema.cds

namespace sap.samples;

entity Receiver : cuid {
    name                : String(128);
    countryOfOrigin     : Association to one Country;

    noOfReceipts        : Integer default 0;
    phoneNumber         : String(32);
}

entity Country {
    key code    : String(2);
    countryName : String(128);
}

entity Sender : cuid {
    name                    : String(128);
    countryOfOrigin         : Association to one Country;

    hasPrimeShipping        : Boolean default false;
    defaultPackagePriority  : String(1) enum {
        A;
        B;
    } default 'B';

    typeOfSender            : String(32) enum {
        daily;
        everyOtherDay;
        weekly;
        monthly;
        quarterly;
    }
}

2. Prepared a service handler

You should have already a service handler ready. In our sample we have a definition of admin-service.cds and a matching JavaScript file.

For our sample, we have this admin-service.cds

using { sap.samples as db } from '../db/schema';

service AdminService {
    entity Receiver as projection on db.Receiver;

    @readonly
    entity Country as projection on db.Country;

    entity Sender as select from db.Sender {
        *,
        (
            case
                when defaultPackagePriority = 'A' and hasPrimeShipping = true and typeOfSender IN ('daily', 'everyOtherDay')
                then true
                else false
            end
        ) as canSendFast : Boolean;
    }
}

and this admin-service.js

const LOG = cds.log('admin-service');
const {
    Receiver,
    Country,
    Sender
} = cds.entities('sap.samples');

module.exports = async (srv) => {};

Understand the structure of a transaction in CAP

CAP offers you out-of-the-box transaction handling based on an incoming request. If you have custom CRUD handlers or bound/unbound actions to an entity, you have always the req parameter which gives you an enhanced request object from the underlying express.js framework.

srv.on("receiverReceivedDelivery", async (req) => { });

You should now proceed the following, if you plan to execute queries to the database:

1. Get the automatically managed database transaction to this request via cds.tx(req)
2. (only for actions with parameters): Get your POST/GET-Parameters via req.data
3. (only for bound action): Get your bound entity via await tx.run(req.query)
4. Write your queries
5. Return results / throw error and rollback

Steps 1-3 lead to an example like following:

srv.on("receiverReceivedDelivery", async (req) => {
    const tx = cds.tx(req);
    const { receiverID } = req.data;
});

Now it’s time to write the queries… but before, let’s quickly look at different styles CAP offers.

Query styles

CAP offers different styles to write queries. While it gives flexibility about a preferred syntax, it might be overwhelming to beginning. First and foremost, it doesn’t matter which style you prefer. Make sure you stay consistent within your project and ideally chose the one which fits your eslint configurations.

There are following different styles:

// fluent API
let q1 = SELECT.one.from('Receiver').where({name:'John Doe'})

// tagged template string literals
const sName = "John Doe";
let q1 = SELECT.one.from `Receiver` .where `name=${sName}`

// reflected definitions
const { Receiver } = cds.entities
let q1 = SELECT.one.from (Receiver) .where `name=${sName}`

All q1 return a csn-like definition of a query operation, so we always get the same output. I prefer to use the fluent API with reflection definitions in combination. What exactly this means, you see in the following.

SELECT queries

SELECT queries aka read operations are the most important querying type and hence are presented firstly. Remember our sample where we have defined in the admin-service the const Receiver based on cds.entites in the namespace sap.samples (the one from the schema.cds). This is the reflected definition of this entity.

Simple SELECT statement

When we write our first sql statement, we want to query all data from Receiver table. So we do the following:

const query = SELECT.from(Receiver)

This returns only a query and not the executed result. In order to get all entries from this table into a JS array of object, we do following:

const aAllReceiver = await tx.run(SELECT.from(Receiver));

Et voilà, our first SELECT statement has been written.

Enhance the SELECT statement by a WHERE clause

You most likely don’t want the entire set of records, so you apply filters (in SQL known as WHERE clause). The same applies to CAP.

If you want to write: SELECT * FROM Receiver WHERE Name = ‘John Doe’;

const aReceiver = await tx.run(SELECT.from(Receiver).where({ name:'John Doe' }));

The object-relational model makes it so easy, that you can use similarly also the IN clause, e.g. SELECT * FROM Receiver WHERE Name IN (‘John Doe’, ‘Jane Doe’); which is equal to a where clause with an OR-condition.

const aReceiver = await tx.run(SELECT.from(Receiver).where({ name: ['John Doe', 'Jane Doe'] }));

For safety of your query make sure, you don’t have null entries in your array.

Apply multiple WHERE clauses AND-combined

It’s likely you want to find a result set which returns you something where two filters both apply. You could write is a following:

const aSender = await tx.run(SELECT.from(Sender).where({ hasPrimeShipping: true, defaultPackagePriority: 'A' }));

Writing more into the where-object, combines the filters using AND.

Non-equal WHERE conditions

Sometimes, you don’t wan’t any = or IN operations, but greater or lower. You can put your operator in such a syntax:

const aReceiver = await tx.run(SELECT.from(Receiver).where({ noOfReceipts: {'<':1} }));

Apply multiple WHERE clauses OR-combined

OR-combined statements are not less likely but a bit more “annoying” to write. Firstly, make sure your ON-condition refers to two different properties, otherwise proceed with the IN operation AND-combined.

// Option 1: use the cds.parse.expr method
const orWHEREClause = cds.parse.expr(`hasPrimeShipping = true OR typeOfSender = 'daily'`);
const aSender = await tx.run(SELECT.from(Sender).where(orWHEREClause));

// Option 2: use CSN-style where clause
const aSender = await tx.run(SELECT.from(Sender).where([ { ref: ["hasPrimeShipping"] }, '=', { val: [true] }, 'or', { ref: ["typeOfSender"] }, '=', { val: ['daily'] } ]));

// Option 3: tagged template string literals style
const aSender = await tx.run(SELECT.from(Sender).where `hasPrimeShipping = true OR typeOfSender = 'daily'`);

Make a projection and select only some columns

If you want to select only a few columns to keep the JS array of object as small as possible, use following:

const aSender = await tx.run(SELECT.from(Sender).columns('name', 'typeOfSender').where({ hasPrimeShipping: true }));

Enhance the column projection by SQL-functions

const aSender = await tx.run(SELECT.from(Sender).columns('name', 'SUBSTR(typeOfSender, 0, 1)').where({ hasPrimeShipping: true }));

Use alias for column names

const aSender = await tx.run(SELECT.from(Sender).columns('name', 'SUBSTR(typeOfSender, 0, 1) as firstLetter').where({ hasPrimeShipping: true }));

Use all wildcard columns

const aSender = await tx.run(SELECT.from(Sender).columns('*', 'SUBSTR(typeOfSender, 0, 1) as firstLetter').where({ hasPrimeShipping: true }));

The columns method is very rich and allows to project only the required fields you need.

Order your result set

The orderBy method expects your column name and the sorting direction (ascending, descending). Also here, you can provide multiple properties to be sorted, equivalent to the SQL ORDER BY.

const aSender = await tx.run(SELECT.from(Sender).where({ hasPrimeShipping: true }).orderBy({ name: "desc" }));

Limit and top your result set

If you want to only return the first 10 rows, you can use limit:

const aSender = await tx.run(SELECT.from(Sender).where({ hasPrimeShipping: true }).orderBy({ name: "desc" }).limit(10));

You can give another property to the limit function which defines the offset (number of entries to be skipped):

const aSender = await tx.run(SELECT.from(Sender).where({ hasPrimeShipping: true }).orderBy({ name: "desc" }).limit(10, 20));

Grouping results

If you want to group like with SQL GROUP BY, you can do this with CAP exactly like this. Be aware, that your groupBy properties must be included in the columns statement and that all non-grouped properties must somehow be aggregated.

const aSenderStats = await tx.run(SELECT.from(Sender).columns('typeOfSender', 'COUNT(*)').groupBy('typeOfSender'));

Expecting only one result

If your where clause already suggest only one result and you don’t want that CAP returns you an array but straightaway the object, you can add the SELECT.one to the query:

const oReceiver = await tx.run(SELECT.one.from(Receiver).where({ name:'John Doe' }));

Get dynamic attributes from the projection definition

As you see in our admin-service.cds, we have added the canSendFast boolean to the Sender entity. If we want to make use of this, we must write on top that the Sender should not come from cds.entities(“sap.samples”) but from the namespace of our service. With this we don’t access the table but the created view towards this table.

SELECT queries from two or more tables

Firstly, I want to disappoint you by telling you, there is no CAP NodeJS join. Nonetheless, this is no bottleneck, since we have plenty other options.

Option 1: Predefine your join in the schema.cds as view

You can create a view in your schema.cds in order to create a database artefact, you can use to get your join.

We add following to the schema.cds:

// Option 1
view ReceiverCountry as select from 
    Receiver left outer join Country
    on Receiver.countryOfOrigin_code = Country.code {
        name,
        noOfReceipts,
        countryName
    };

// Option 2
view ReceiverCountry as select from Receiver {
    name,
    noOfReceipts,
    countryOfOrigin.countryName
}

And we include our new entity in the admin-service.js on top:

const {
    Receiver,
    Country,
    Sender,
    ReceiverCountry
} = cds.entities('sap.samples');

Now we can just query, using the new entity as following:

const aReceiver = await tx.run(SELECT.from(ReceiverCountry));

Option 2: Make it dynamic in your coding with two queries

As you already hear, this is not the go-to-option for a result set of many entries to join or any subset of a Cartesian product. But it works, if you have a single entry and want to enhance it.

const oReceiver = await tx.run(SELECT.one.from(Receiver).where({ name:'John Doe' }));
oReceiver.Country = await tx.run(SELECT.one.from(Country).where({ code: oReceiver.countryOfOrigin_code }));

UPDATE queries

Updates are maybe the second most important data querying/manipulation operation. Be sure, to have always a valid where clause, otherwise you get unwanted surprises.

Update with static values

The where-clause of UPDATES matches the one from SELECT-statements. Hence, I don’t want to repeat this options. Nonetheless, I’ve listed you options for where clauses:

await tx.run(UPDATE(Sender).set({ hasPrimeShipping: true }).where({ typeOfSender: 'daily' }));
await tx.run(UPDATE(Sender).set({ defaultPackagePriority: 'A' }).where({ hasPrimeShipping: true, typeOfSender: 'daily' }));
await tx.run(UPDATE(Sender).set({ defaultPackagePriority: 'A' }).where `hasPrimeShipping = true OR typeOfSender = 'daily'`);

Updates with operations to existing values

Sometimes you just want to increase a counter or update:

await tx.run(UPDATE(Receiver).set({ noOfReceipts: { '+=': 1 }}).where({ countryOfOrigin_code: 'LI' }));

If you need more sophisticated methods, this syntax style might get a little unreadable:

await tx.run(UPDATE(Receiver).set({ name: {xpr: [{ref:[ 'name' ]}, '||', '- Receiver'] } }).where({ countryOfOrigin_code: 'AX' }));

Here, the other syntax might be better:

await tx.run(UPDATE `Receiver` .set `name = (name || '- Receiver)` .where `countryOfOrigin_code = 'AX'`);

DELETE queries

Deletions are always critical. Make sure you avoid truncating your entire table. In a nutshell: DELETEs are similar to UPDATEs and SELECTs:

await tx.run(DELETE.from(Sender).where({ countryOfOrigin_code: 'AX' }));
await tx.run(DELETE.from(Reciever).where({ noOfReceipts: {'<':1} }));

INSERT queries

Last but not least, it’s about INSERT queries. INSERT operations are also pretty straight forward.

Note: If you insert to an entity where an autogenerated ID is required, make sure you provide this. CAP does this in .on(“CREATE…) by adding this already to req.data.

const aReceiver = [
    { name: 'John Doe', countryOfOrigin: 'DE', phoneNumber: '123456' },
    { name: 'Jane Doe', countryOfOrigin: 'CH', phoneNumber: '345678' }
];

// Option 1
await tx.run(INSERT(aReceiver).into(Receiver));

// Option 2
await tx.run(INSERT.into(Receiver).entries(aReceiver));

At this point of time, I hope you enjoyed this blog post which hopefully helped you to get a quick start into the CAP CDS NodeJS cds.ql syntax.