Freelancers
Cancel
 Sign In
Join Now
Query Language is an essential tool to communicate with a database to perform operations such as retrieving data, inserting data, updating data and deleting data. Query languages are designed to be concise and efficient, allowing users to specify complex operations with minimal effort. There are several types of query languages, including structured query language (SQL), which is the most common query language used in relational databases, and non-relational query languages such as MongoDB’s query language.

Simillarly SAP Cloud Application Programming Model provides a query language called CDS Query Language (in short CQL). In this blog post, I will try to explain my understandings and learnings about CQL. Let’s get started!!

Note: this blog post is written for CAP framework with Node.js runtime.

Query Styles

Primarily there are two ways or styles to construct and execute queries.
  1. SQL-Like fluent API provided by cds.ql
  2. Querying API provided by cds.service

 

Fluent API Examples

// retrieve data
await SELECT.from(Roots).where({ID:'38a13fed-f2d1-45bd-91ec-642053889c92'});

// create data
input = {name:'root 6', descr:'descr 6', dfield:'2023-02-11', tfield:'14:18:29',
         dtfield: '2022-02-11T04:08:29Z',tsfield: '2022-02-11T04:08:29.129Z',
         nfield: 290,afield: 23891100.123456};

await INSERT.into(Roots).entries(input);

Querying API Examples

// retrieve data
await srv.read(Roots).where({ID:'38a13fed-f2d1-45bd-91ec-642053889c92'});
await cds.read(Roots).where({ID:'38a13fed-f2d1-45bd-91ec-642053889c92'});

// create data
input = {name:'root 6', descr:'descr 6', dfield:'2023-02-11', tfield:'14:18:29',
         dtfield:'2022-02-11T04:08:29Z', tsfield:'2022-02-11T04:08:29.129Z',
         nfield: 290, afield:23891100.123456};

await cds.create(Roots).entries(input);
await srv.create(Roots).entries(input);

Note:

  • srv.read and cds.read – these methods are actually convenient shortcuts to the respective fluent API methods

  • In above example Roots is the entity and often referred as query target.It is recommended to use entity definitions reflected from a service’s model to construct queries instead of fully qualified enitty names with namespace. By utilizing this reflection technique, code can be simplified significantly as it eliminates the need to repeatedly include namespaces throughout the codebase.

    // using fully qualified entity names
await SELECT.from('cap.cql.db.Roots')

// using reflected entity definition
const {Roots} = cds.entities;
await SELECT.from(Roots)​

  • In all above mentioned api styles, arguments like columns of entity can be passed in multiple ways as shown below:

    // tagged template string
await SELECT.from `cap.cql.db.Roots` .columns `name, descr as description`

// cql column expressions
const { Roots } = cds.entities;
await SELECT.from (Roots) .columns ('name', 'descr as description')

// cqn expression objects
await SELECT.from `cap.cql.db.Roots` 
            .columns ({ref:['name']}, {ref:['descr'], as:'description'})

// array of columns
await SELECT.from `cap.cql.db.Roots` .columns (['name', 'descr as description'])

// projection function
await SELECT.from `cap.cql.db.Roots` 
            .columns (r => { r.name, r.descr.as('description') }); ​
  • Projection functions is the recommended way as it supports nested projections aka expands, uses standard JavaScript constructs, offers type inference and code completion etc. However it does not support all functionalities. So sometimes, it might be required to use along with template string and other above mentioned mechanisms.

Schema Information for Sample Code

schema.cds

As shown in above picture, Roots is the main entity which has 4 child entities. AssocSItems and AssocMItems are associations to Roots with 1-to-1 and 1-to-N cardinality respectively. Simillarly CompSItems and CompMItems are compositions to Roots with 1-to-1 and 1-to-N cardinality respectively. Also Roots2 is another entity with more than one key.

All query examples are explained based on this schema. Also sample code is provided as git repository here: btp-cap-demo-usecases

Execute queries using Different DB

Executing queries to verify results before incorporating them into code is a common requirement. To facilitate this, the CAP framework offers the cds repl command, which enables live interaction with Node.js APIs and allows for query execution.

Following are the commands that you can use to test the queries provided in later part of this blog post.

  • Using Sqlite DB
     

    git clone https://github.com/SAP-samples/btp-cap-demo-usecases.git
cd cap-cql-examples
npm install
cds deploy --to sqlite
cds repl
await cds.test()
const { Roots, AssocSItems, AssocMItems, 
        CompSItems, CompMItems, Roots2   } = cds.entities
  • Using HANA Cloud DB 
    git clone https://github.com/SAP-samples/btp-cap-demo-usecases.git
cd cap-cql-examples
npm install
cds add hana --for hybrid
cf login
cds deploy --to hana:cap-cql-examples-db --profile hybrid --store-credentials
cds repl --profile hybrid
await cds.test()
const { Roots, AssocSItems, AssocMItems, 
        CompSItems, CompMItems, Roots2  } = cds.entities
​
  • Better SqliteIn March 2023 release for the CAP framework, the SQLite Database Service has been rebuilt with a new architecture and the better-sqlite3 driver, resulting in significant features such as full support for path expressions, including infix filters and exists predicates, allowing for more advanced queries. Standard functions like contains, concat, and startswith are now supported in a database-agnostic way.SAP HANA functions like days_between and months_between are also supported, enhancing testing for SAP HANA-based production scopes. These improvements bring enhancements to both the feature scope and performance of the service.The new SQLite service will become the default in the upcoming major release. For more information, visit this page: New SQLite Service (beta)
     

    git clone https://github.com/SAP-samples/btp-cap-demo-usecases.git
cd cap-cql-examples
npm install
npm add @cap-js/sqlite -D

jq '.cds = {requires:{db:"sql"}}' package.json > temp.json && mv temp.json package.json

cds repl
await cds.test()
const { Roots, AssocSItems, AssocMItems, 
        CompSItems, CompMItems, Roots2  } = cds.entities​

    Note: You might need to install necessary command line tools like cf cli, jq, git etc.

Retrieve Data: Select Query Examples

SELECT queries are used to retrieve data from one or more tables of database.  Let’s look at few examples with different variations.

  • Retrieve full table data 
    await SELECT.from(Roots);​
  • Retrieve single entry by one or more key
     

    // one key
await SELECT.from(Roots).byKey('38a13fed-f2d1-45bd-91ec-642053889c92');
await SELECT.from(Roots, '38a13fed-f2d1-45bd-91ec-642053889c92');

// more than one key
await SELECT.from(Roots2).byKey({first: 101, second:201});
await SELECT.from(Roots2, {first: 101, second:201});

  • Retrieve data by one or more non-key field

    // string field
await SELECT.from(Roots).where({name:'root 1'});
await SELECT.from(Roots).where(`name like '%1%'`);
await SELECT.from(Roots).where({category:{in:['rc1', 'rc2']}})

// date field
await SELECT.from(Roots).where({dfield:'2022-02-11'});
await SELECT.from(Roots).where(`dfield < '2022-06-11'`);

// time field
await SELECT.from(Roots).where({tfield:'04:08:29'});
await SELECT.from(Roots).where({tfield:{'>':'14:08:29'}});

// datetime or timestamp field
await SELECT.from(Roots).where(`dtfield < '2022-06-01T14:08:29Z'`);
await SELECT.from(Roots).where(`tsfield > '2022-10-01T04:08:29.000Z'`);

// numeric or amount field
await SELECT.from(Roots).where(`nfield between 1 and 500`);
await SELECT.from(Roots).where({nfield:{ between: 501, and: 1000}});
await SELECT.from(Roots).where(`afield < 299345.451`);

// more than one non-key field
await SELECT.from(Roots).where({and: {dfield:'2022-10-01', nfield:123}});
await SELECT.from(Roots).where(`dfield > '2022-10-01' and nfield > 500`);
await SELECT.from(Roots)
            .where({and:{ dfield:{'>': '2022-10-01'}, nfield:{'>':600} }});​

  • Retrieve data by multiple conditions

    await SELECT.from(Roots).where(`nfield > 900 or nfield < 200`);
await SELECT.from(Roots).where({or:{ nfield:{'>': 900}, nfield:{'<':200} }});​

  • Retrieve only few columns

    await SELECT.from(Roots).columns(['name','descr'])
await SELECT.from(Roots).columns(r=>{r.name, r.descr})
await SELECT.from(Roots).where({name:{ like: '%root%'}})​
  • Retrieve associated and composition entity data 
    // getting few column of associated and composition entity
await SELECT.from(Roots)
            .columns(r=>{r.name, r.descr, r.asitem(asi=>{asi.asi_sfield}) });
await SELECT.from(Roots)
            .columns(r=>{r.name, r.descr, r.csitem(csi=>{csi.csi_sfield}) })

// getting all columns of associated and composition entity
await SELECT.from(Roots)
            .columns(r=>{r.name, r.descr, r.asitem(asi=>{asi`.*`}) })
await SELECT.from(Roots)
            .columns(r=>{r.name, r.descr, r.csitem(csi=>{csi`.*`}) })
    

// getting all columns of root and associated entity
await SELECT.from(Roots).columns(r=>{r`.*`,r.asitem(asi=>{asi`.*`}) })
await SELECT.from(Roots).columns(r=>{r`.*`,r.amitems(ami=>{ami`.*`}) })
await SELECT.from(Roots).columns(r=>{r`.*`,r.csitem(csi=>{csi`.*`}) })
await SELECT.from(Roots).columns(r=>{r`.*`,r.cmitems(cmi=>{cmi`.*`}) })

// mix and match
await SELECT.from(Roots)
            .columns(r=>{r.name, r.descr, 
                         r.asitem(asi=>{asi.asi_nfield}), 
                         r.amitems(ami=>{ami`.*`})       });

await SELECT.from(Roots)
            .columns(r=>{r.name, r.descr, 
                         r.csitem(csi=>{csi`.*`}), 
                         r.cmitems(cmi=>{cmi.csm_nfield}) });

//Use filter
await SELECT.from `${Roots}[category='rc1']`
await SELECT.from `${Roots}[category='rc1' and name like '%root%']`
await SELECT.from(Roots)
            .columns(r=>{ r`.*`,
                          r.amitems`[asm_nfield > 1010]`(ami=>{ami`.*`}) })​


    SELECT.from(Roots).columns(r=>{r`.*`,r.cmitems(cmi=>{cmi`.*`}) })

    In above code snippet, The query is performed on the Roots entity using the SELECT.from method. The columns function is used to specify the desired columns in the query results. In this case, it selects all columns (.*) from the Roots entity. Additionally, it includes related cmitems entities using the nested callback function (cmi=>{cmi.*}), which indicates that all columns (.*) from the related cmitems entities are included in the query results.

    SELECT.from(Roots).columns(r=>{ r`.*`,r.amitems`[asm_nfield > 1010]`(ami=>{ami`.*`}) })

    In above code snippet, all columns (.*) from the Roots entity are selected, along with related amitems entities that meet the condition [asm_nfield > 1010]. The condition is called inflix filters. You can find more information here: infix-filters

  • Retrieve one entry or distinct entry

    //get one entry
await SELECT.one.from(Roots).where({category:'rc1'});
await SELECT.one.from(Roots)
                .columns(r=>{r`.*`,r.amitems(ami=>{ami`.*`}) });

//get distinct entry
await SELECT.distinct.from(Roots)
                     .columns('category').where({nfield:{'>':500}});​
  • Using functions and expressions 
    // count function
await SELECT.from(Roots).columns(`count(*) as totalrows`);

// case statement
await SELECT.one.from(Roots)
            .columns(`count(*) as totalcount`, 
                     `sum(CASE WHEN nfield < 500 THEN 1 END) as lowcount`, 
                     `sum(CASE WHEN nfield >= 500 THEN 1 END) as highcount`);

// aggregate functions: average, minimum, maximun etc
await SELECT.one.from(Roots).columns(`avg(nfield) as average`);
await SELECT.one.from(Roots).columns(`min(nfield) as minimum`);
await SELECT.one.from(Roots).columns(`max(nfield) as maximum`); 
await SELECT.from(Roots).columns(`ceiling(afield) as afield`);

// string function: concat, toupper, tolower
await SELECT.from(Roots)
            .columns(`concat(name, ' ',descr) as name`, 
                     `length(name) as charcount`       );
await SELECT.from(Roots)
            .columns(`toupper(name) as name`, `tolower(descr) as descr`)
await SELECT.from(Roots)
            .columns(`year(dfield) as year`, 
                     `month(dfield) as month`, 
                     `day(dfield) as day`     );
await SELECT.from(Roots)
            .columns(`hour(tfield) as hour`, 
                     `minute(tfield) as minute`, 
                     `second(tfield) as second` );​

  • Group By and Order By

    await SELECT.from(Roots).columns(`name`).orderBy('name desc');
await SELECT.from(Roots)
            .columns(`category`, `count(name) as itemscount`)
            .groupBy('category');
await SELECT.from(Roots)
            .columns('category','name','descr')
            .orderBy('category desc', 'name asc');

  • Limit by rows and offset

    // Only 2 entries are fetched
await SELECT.from(Roots).limit(2).where(`name like '%root%'`)

// Only 2 rows are fetched after 3 rows (offset)
await SELECT.from(Roots).limit(2,3).where(`name like '%root%'`)​

Note:

  • Not every CQL or SQL statement can be expressed with projection functions. This is where tagged template strings kick in.
  • Template string and special character * is used to get all columns as shown above: r.`*`
  • Inflix filters are used to filter data. In last example, both template string and inflix filter are used to apply filter on child items: r.amitems`[asm_nfield > 1010]`(ami=>{ami`.*`})
  • Some of the query concepts are not supported by sqlite like path expressions etc. However it is suppoerted in both better sqlite and HANA db. Some examples are as below: 
    await SELECT.from(Roots).columns('name',`asitem.asi_sfield`)
await SELECT.from(Roots).columns(r=>{r.name, r.descr, r.asitem.asi_sfield })
await SELECT.from(Roots).columns(r=>{r.name, r.descr, r.amitems.asm_sfield })
await SELECT.from(Roots).where(`asitem.asi_nfield > 100`)

cat = ['rc1', 'rc3']; 
await SELECT.from(Roots)
            .where `category in ${cat} and amitems.asm_nfield > 1000`​

Create Data: Insert Query Examples

Insert queries are used to create entry in tables. Let’s look at few examples below:

  • Examples with Different Entities, Associations, Compositions

    // create one entry
onerowdata = {name:'root 6', descr:'descr 6', dfield: '2022-04-12',
              tfield:'04:08:29', dtfield: '2022-02-11T04:08:29Z',
              tsfield:'2022-02-11T04:08:29.129Z', nfield: 200,
              afield:'23891100.123456',category: 'rc3'};
await INSERT.into(Roots).entries(onerowdata);

// create one entry with associated entry
onerowdata = {name:'root 7', descr:'descr 7', dfield: '2023-04-12',tfield: '14:08:29',
              dtfield: '2023-02-11T04:08:29Z',tsfield: '2022-02-11T04:08:29.129Z',
              nfield: 200,afield: '23891100.123456',category: 'rc3', 
              asitem_ID:'585dee53-69e5-47d9-b90d-945253d4af0d'};
await INSERT.into(Roots).entries(onerowdata);

one = {name:'root 7', descr:'descr 7', dfield:'2023-04-12', tfield:'14:08:29',
       dtfield:'2023-02-11T04:08:29Z', tsfield:'2022-02-11T04:08:29.129Z',
       nfield:200, afield: '23891100.123456',category: 'rc3', 
       amitems:[ {ID:'110f6a9f-b647-4e63-bdb2-03ec724d0d2b'},
                 {ID: '71bd1b1d-ca9c-403e-b435-99e58e8373c0'} ]};
await INSERT.into(Roots).entries(one);

// create one entry with composition entry
one = {name:'root 8', descr:'descr 8', dfield:'2023-04-12', tfield:'14:08:29',
       dtfield: '2023-02-11T04:08:29Z',tsfield: '2022-02-11T04:08:29.129Z',
       nfield: 200,afield: '23891100.123456',category: 'rc3', 
       csitem:{csi_sfield:'csi field 7', csi_nfield:'700'} };
await INSERT.into(Roots).entries(one);

one = {name:'root 9', descr:'descr 9', dfield:'2023-01-12', tfield:'10:58:29',
       dtfield: '2023-12-11T04:08:29Z',tsfield: '2022-09-11T04:08:29.129Z',
       nfield: 400,afield: '13892100.723456',category: 'rc2', 
       cmitems:[ {csm_sfield:'csm field 01', csm_nfield:'100'}, 
                 {csm_sfield:'csm field 02', csm_nfield:'200'} ]};
await INSERT.into(Roots).entries(one);

// create multiple entry
multi = [ {name:'root 20', descr:'descr 20', dfield:'2023-01-10', tfield:'18:08:29', 
           dtfield: '2023-02-11T04:08:29Z',tsfield: '2023-02-11T04:08:29.129Z',
           nfield: 400,afield: '23891100.123456',category: 'rc3'}, 
          {name:'root 21', descr:'descr 21', dfield:'2023-04-12', tfield:'16:08:29', 
           dtfield: '2023-02-11T04:08:29Z',tsfield: '2023-01-11T04:08:29.129Z',
           nfield: 300,afield: '23891100.123456',category: 'rc3'}];
await INSERT.into(Roots).entries(multi);​

  • Different Variations

    // using columns and values
await INSERT.into(Roots)
            .columns('name','descr','dfield','tfield','dtfield',
                     'tsfield','nfield','afield','category')
            .values('root 10','descr 10','2022-04-12','04:08:29','2022-02-11T04:08:29Z',
                    '2022-02-11T04:08:29.129Z',200,23890.456,'rc3');

// using columns and rows
await INSERT.into(Roots)
            .columns('name','descr','dfield','tfield','dtfield',
                     'tsfield','nfield','afield','category')
            .rows(['root 11','descr 11','2023-04-12','04:08:29','2022-02-11T04:08:29Z',
                   '2022-02-11T04:08:29.129Z',200,23890.456,'rc3'], 
                  ['root 12','descr 12','2023-04-12','04:08:29','2022-02-11T04:08:29Z',
                   '2022-02-11T04:08:29.129Z',200,23890.456,'rc2'], 
                  ['root 13','descr 13','2023-01-12','04:08:29','2022-02-11T04:08:29Z',
                   '2022-02-11T04:08:29.129Z',300,23890.456,'rc1']);​

Create or Modify Data: Upsert Query Examples

Upsert queries are used to create or modify entries in tables. To modify, all key fields need to be part of the data. Let’s look at few examples below:

  • Different Variations 
    // create entry
await UPSERT.into(Roots)
      .entries({name:'root 6', descr:'descr 6', dfield:'2022-04-12', 
                tfield:'04:08:29', dtfield: '2022-02-11T04:08:29Z',
                tsfield: '2022-02-11T04:08:29.129Z', nfield: 200, 
                afield: '23891100.123456', category: 'rc3'});

// create entry with association
await UPSERT.into(Roots)
      .entries({name:'root 7', descr:'descr 7', dfield:'2023-04-12', 
                tfield:'14:08:29', dtfield:'2023-02-11T04:08:29Z', 
                tsfield:'2022-02-11T04:08:29.129Z', nfield:200, 
                afield:'23891100.123456', category:'rc3', 
                asitem_ID:'585dee53-69e5-47d9-b90d-945253d4af0d'});

// modify entry
await UPSERT.into(Roots)
            .entries({name:'root 11',ID:'7891fd0d-5924-471a-89c4-519f7df071b8'});
await UPSERT.into(Roots)
      .entries({name:'root 66', descr:'descr 6', dfield:'2022-04-12', 
                tfield:'04:08:29', dtfield:'2022-02-11T04:08:29Z', 
                tsfield: '2022-02-11T04:08:29.129Z', nfield:200, 
                afield:'23891100.123456',category:'rc2', 
                ID:'7891fd0d-5924-471a-89c4-519f7df071b87'})

// modify root entry with creation of composed entry: Not working
await UPSERT.into(Roots)
      .entries({ID: '7891fd0d-5924-471a-89c4-519f7df071b8', descr: 'descr 1111', 
                amitems: [{asm_sfield: 'asm string 1111',asm_nfield: 1001,
                           root_ID: '7891fd0d-5924-471a-89c4-519f7df071b8'}] })​

     

Note: 

  • Upsert for CAP Node.js does not (unlike CAP Java) support deep payloads.

Update Data: Update Query Examples

Update queries are used to modify data in a table. Here are a few examples:

  • Using Set or With: Different Variations

    // by single key
await UPDATE.entity(Roots, 'f11aacb1-ab35-40da-918b-589cdd387ad1')
            .set({name:'root 333'});
await UPDATE.entity(Roots, 'f11aacb1-ab35-40da-918b-589cdd387ad1')
            .set({name:'root 332',dfield: '2021-12-20'});
await UPDATE.entity(Roots, 'f11aacb1-ab35-40da-918b-589cdd387ad1')
            .set({nfield: {'-=': 89}});
await UPDATE.entity(Roots, 'f11aacb1-ab35-40da-918b-589cdd387ad1')
            .set(`nfield = nfield * 2`);

// by multiple key
await UPDATE.entity(Roots2,{first:101,second:201}).set({name:'r2 name1 extra'});

// by cqn expression
cqnExpression = {xpr: [{ref:['descr']}, '||', {val:'Additional Description'}]};
await UPDATE.entity(Roots2,{first:102,second:202})
            .with({descr: cqnExpression})

// by functions with template string
await UPDATE.entity(Roots, 'f11aacb1-ab35-40da-918b-589cdd387ad1')
            .set(`descr = concat('descr',' add')`);
await UPDATE.entity(Roots2, {first:102,second:202})
            .set(`descr = concat('descr',' add'), name = 'root 33'`);

// by where clause
await UPDATE.entity(Roots)
            .set({nfield: {'-=': 89}})
            .where({nfield:{'>':500}});
await UPDATE.entity(Roots2)
            .set({name: 'r2 name2 new'})
            .where(`descr like '%Add%' and first = 102`);​


    UPDATE.entity(Roots, ‘f11aacb1-ab35-40da-918b-589cdd387ad1’).set(`nfield = nfield * 2`)

    The UPDATE.entity method is used to update the Roots entity with the specified ID ‘f11aacb1-ab35-40da-918b-589cdd387ad1’. The .set() method is used to specify the changes to be made in the update operation. In this case, it sets the nfield column to be multiplied by 2. You can look at different examples as provided above.

Note:

  • When update query executes successfully, it returns no of rows updated by the query.
  • If all keys are passed as part of query then only one entry of the entity is updated.
  • Method .set and .with are aliases to the same method.

Delete Data: Delete Query Examples

Delete queries remove data from tables. Take a look at the examples below:

  • Different Variations

    // using one or more key
await DELETE.from(Roots,'445b1357-90b2-4a2c-a60d-7f7e40d46f12');
await DELETE.from(Roots2,{first:101, second:201});

// using where condition
await DELETE.from(Roots).where({category:'rc1'});
await DELETE.from(Roots2).where({first:101})
await DELETE.from(AssocMItems).where({asm_nfield:{'>=':1014}});​

     

Note: This blog post does not explore into the usage of Native HANA features. However, if you’re interested in learning more about this topic, you can visit this page: Native Features

Conclusion 

In this blog post, We explored how CQL can be used to execute database operations such as retrieving data, inserting data, updating data and deleting data.

More information about cloud application programming model can be found here. You can follow my profile to get notification of the next blog post on CAP. Please feel free to provide any feedback you have in the comments section below and ask your questions about the topic in sap community using this link.
Tags: SAP Business Technology Platform SAP Cloud Application Programming Model
Sara Sampaio

Sara Sampaio

Author Since: March 10, 2022

0 0 votes
Article Rating
Subscribe
Notify of
0 Comments
Inline Feedbacks
View all comments
0
Would love your thoughts, please comment.x
()
x