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:
- Get the automatically managed database transaction to this request via cds.tx(req). It's not anymore needed in newer CAP versions, so you can skip this.
- (only for actions with parameters): Get your POST/GET-Parameters via req.data
- (only for bound action): Get your bound entity via await tx.run(req.query)
- Write your queries
- Return results / throw error and rollback
Steps 1-3 lead to an example like following:
srv.on("receiverReceivedDelivery", async (req) => {
// getting the transaction may be needed in older cds versions
// 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 CQN object 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:
Edit: The CAP product team has outlined that tx.run is not anymore needed. I've updated the blog post. The following code fragment is a more lightweight edition of a tx.run style:
const aAllReceiver = await SELECT.from(Receiver);
Note: You still need to await to get the result.
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 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 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 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 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 SELECT.from(Sender).where(orWHEREClause);
// Option 2: use CSN-style where clause
const aSender = await SELECT.from(Sender).where([ { ref: ["hasPrimeShipping"] }, '=', { val: [true] }, 'or', { ref: ["typeOfSender"] }, '=', { val: ['daily'] } ]);
// Option 3: tagged template string literals style
const aSender = await 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 SELECT.from(Sender).columns('name', 'typeOfSender').where({ hasPrimeShipping: true });
Enhance the column projection by SQL-functions
const aSender = await SELECT.from(Sender).columns('name', 'SUBSTR(typeOfSender, 0, 1)').where({ hasPrimeShipping: true });
Use alias for column names
const aSender = await SELECT.from(Sender).columns('name', 'SUBSTR(typeOfSender, 0, 1) as firstLetter').where({ hasPrimeShipping: true });
Use all wildcard columns
const aSender = await 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 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 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 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 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 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 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 SELECT.one.from(Receiver).where({ name:'John Doe' });
oReceiver.Country = await 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 UPDATE(Sender).set({ hasPrimeShipping: true }).where({ typeOfSender: 'daily' });
await UPDATE(Sender).set({ defaultPackagePriority: 'A' }).where({ hasPrimeShipping: true, typeOfSender: 'daily' });
await 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 UPDATE(Receiver).set({ noOfReceipts: { '+=': 1 }}).where({ countryOfOrigin_code: 'LI' });
If you need more sophisticated methods, this syntax style might get a little unreadable:
await UPDATE(Receiver).set({ name: {xpr: [{ref:[ 'name' ]}, '||', '- Receiver'] } }).where({ countryOfOrigin_code: 'AX' });
Here, the other syntax might be better:
await 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 DELETE.from(Sender).where({ countryOfOrigin_code: 'AX' });
await 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 INSERT(aReceiver).into(Receiver);
// Option 2
await 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.
Edit: Thanks to the input of
david.kunz2 , I've added details that tx.run is not necessary in newer cds version.
