Skip to Content
Technical Articles
Author's profile photo Leo Richard Irudayam

Getting started with cds.ql in NodeJS for CAP – 101 on how to query data

CAP and its documentation capire are continuously growing ( 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.



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 {
    } default 'B';

    typeOfSender            : String(32) enum {


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;

    entity Country as projection on db.Country;

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

and this admin-service.js

const LOG = cds.log('admin-service');
const {
} = 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). It’s not anymore needed in newer CAP versions, so you can skip this.
  2. (only for actions with parameters): Get your POST/GET-Parameters via
  3. (only for bound action): Get your bound entity via await
  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) => {
    // getting the transaction may be needed in older cds versions
    // const tx = cds.tx(req);
    const { receiverID } =;

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 ='Receiver').where({name:'John Doe'})

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

// reflected definitions
const { Receiver } = cds.entities
let q1 = (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 is not anymore needed. I’ve updated the blog post. The following code fragment is a more lightweight edition of a 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 to the query:

const oReceiver = await{ 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 {

// Option 2
view ReceiverCountry as select from Receiver {

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

const {
} = 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{ name:'John Doe' });
oReceiver.Country = await{ 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

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 Kunz , I’ve added details that is not necessary in newer cds version.

Assigned Tags

      You must be Logged on to comment or reply to a post.
      Author's profile photo Yogananda Muthaiah
      Yogananda Muthaiah

      Great explanation Leo Richard Irudayam !!

      All the examples you shown above makes everyone to understand easily. 🙂

      Also, If you have some examples with external API's (server,js) with authentication logic using other entity would be also be good to understand

      Author's profile photo Jason Scott
      Jason Scott

      This is a great bog post. I believe that as of recent CDS versions the transaction handling is implicit so it is no longer required to wrap all db calls in



      const { Books } = cds.entities
      let q1 = (Books) .where `ID=${201}`
      let q2 = INSERT.into (Books) .entries ({title:'Wuthering Heights'})
      let q3 = UPDATE (Books) .where `ID=${201}` .with `title=${'Sturmhöhe'}`
      let q4 = DELETE.from (Books) .where `ID=${201}`


      Author's profile photo Leo Richard Irudayam
      Leo Richard Irudayam
      Blog Post Author

      This would be new to be, that is not needed anymore. All your previous examples still do return queries and not results of the db transaction (also since db execution is async). Therefore, you could use also late materialisation to create subqueries (

      Author's profile photo David Kunz
      David Kunz

      In the current version of @sap/cds, it's not needed anymore to explicitly write const tx = cds.tx(req) as we'll automatically use the main transaction when awaiting CQN objects inside your CAP handlers:


      const tx = cds.tx(req)
      // is equivalent to
      await SELECT.from('foo')


      Best regards,

      Author's profile photo Leo Richard Irudayam
      Leo Richard Irudayam
      Blog Post Author

      Thank you for the clarification David 🙂

      So SELECT. and UPDATE. all return Promises of the db execution, right? Is const tx = cds.tx(req), recommended to be used or what's the new best-practice?

      Best regards,


      Author's profile photo David Kunz
      David Kunz

      The new best practice is to remove const tx = cds.tx(req) as it's not needed anymore and makes the code a bit simpler.

      SELECT.from('foo") does not return a Promise but a CQN object (which can be inspected/manipulated), yet it's still possible to await it, it's a bit of a trick.

      Author's profile photo Leo Richard Irudayam
      Leo Richard Irudayam
      Blog Post Author

      Thanks for clarification, added comments in the blog post 🙂

      Author's profile photo David Kunz
      David Kunz

      Thanks, Leo!

      Author's profile photo Tanmoy Mondal
      Tanmoy Mondal
      module.exports = (srv) => {
        const cds = require("@sap/cds");
        srv.on("READ", "Books", async (req) => {
           const aAllBooks = await SELECT.from(Books);


      Leo Richard Irudayam 

      David Kunz 


      when we launch the app in port 4004 and try to navigate to Books entity via the hyperlink to trigger the get call, it is throwing an error - 'This site can’t be reached'


      What is the issue here?


      Best Regards,




      Author's profile photo David Kunz
      David Kunz
      • You must access the correct route (see localhost:4004 for an index page)
      • Your on handler must return the data.
      Author's profile photo Tanmoy Mondal
      Tanmoy Mondal

      Thank you. It works now. Updated the code.


      const logger = cds.log("cat-service");
      const { Books, Authors, Orders } = cds.entities("com.bookshop");
      module.exports = async (srv) => {
        srv.on("READ", "Books", async (req) => {
          const aBook = await SELECT.from(Books).where({
            title: "The Power of Habits",
          return aBook;
      Author's profile photo SATISH AROCKIARAJ SINGAROYAN

      Is it possible to delete the table entries based on another table / range ?


      For example : delete based on noofreceipts from another table .


      await DELETE.from(Reciever).where({ noOfReceipts: {'<':1} });




      Author's profile photo Lorenzo Fratus
      Lorenzo Fratus

      Hi Leo Richard Irudayam, fantastic blog!

      I have a question regarding SELECT operations.
      It seems that by default the results of SELECTs are limited to 1000 rows.
      Looking on the official documentation I discovered that it is possible to control this limit either by putting an annotation (@cds.query.limit.max) on each entity or by directly changing the environment variable.

      But let's say I need to build a query that reads all the rows of a table, and let's say that I don't know the number of rows of that table. How can I do this programmatically using cds.ql?

      My concerns are:

      • I obviously can't solve it by setting a higher max limit (both because I don't know how many rows I need to read and because a high value could cause a timeout in the service exposing the entity);
      • I couldn't find a way to access the `nextLink` property that is exposed by the OData service to handle pagination implicitly (;

      My attempt at doing this would be like this:

      const top = 1000
      const results = []
      const query = SELECT.from(Table)
      let page
      do {
          page = await, results.length))
      } while(page.length === top)
      return results

      But this option looks a bit excessive to do every time I need to read an entity and I would expect cds to be able to handle this without additional code.

      Is there any better way to handle pagination, possibly "automatically", when using cds.ql? If not, do you have any suggestion of where I should put this piece of code to maximize code readability (maybe an event handler)?

      Thank you so much in advance.

      Author's profile photo Glauco Kubrusly
      Glauco Kubrusly

      Hi Leo Richard Irudayam

      Do you have an example on how to use groupBy with alias "as" ?

      Author's profile photo Glauco Kubrusly
      Glauco Kubrusly

      Hi I found out.

              const oSelect = await
                  SELECT.columns(["left(cpuDt,7) as mes", "COUNT(*) as count"])
                  .where `cpuDt between ${dtFiltroInicial} and ${dtFiltroFinal}`
                  .orderBy({'left(cpuDt,7)': 'asc'})