Constructing and Executing Queries

Objective

After completing this lesson, you will be able to create and run database queries

Constructing Queries

In the previous lesson, we created the submitOrder action for the CatalogService. The implementation of this action is not yet complete, as we still lack the knowledge of how to access database tables. We will learn this next.

To implement the submitOrder action, we need the following two database accesses:

We want to make sure that the current stock for the ordered book is greater than or equal to the ordered quantity. To do this, we need to select the current stock from the database.
If there are enough books in stock and the order is therefore carried out, we must reduce the current stock. This means that we have to update the stock in the database accordingly.

Fluent API

The cds.ql module provides an SQL-like API for constructing such database queries. The queries required for our scenario are shown in the following figure.

The API is made available via the global objects SELECT, INSERT, UPSERT, UPDATE and DELETE.

Note

Alternatively, you can obtain these objects from cds.ql as follows:

Code snippet

const cds = require('@sap/cds');
const { SELECT, INSERT, UPSERT, UPDATE, DELETE } = cds.ql;

Expand

In addition, convenience methods are available to create cds.ql query objects. Details on this CRUD-style API with methods such as read(), insert(), upsert(), update() and delete() can be found in the CAP documentation.

The API available via the CRUD objects listed is designed as a fluent API. This means that the methods available on these objects return objects of the same type. Available properties - such as the .one property of the SELECT object used in the example shown - also have objects of the same type as their value. This enables chaining, which makes the coding intuitive and readable.

Note

Details on the methods used in the example shown and the available syntax variants can be found in the CAP documentation.

To identify the database table to be accessed, we use the CSN definition of the Books entity in the example shown. This is the recommended approach.

Fluent API with Tagged Templates

As an alternative to the classic method calls shown above, the fluent API can also be used with tagged templates. The following figure shows how the queries constructed above using classic method calls can be created using tagged templates. The result is the same in both cases.

Note

Tagged templates are a JavaScript feature. It makes it possible to parse and transform template literals - i.e. literals delimited with backtick characters (`) - using a function. A tagged template looks like a function call in which the function name is placed directly before a template literal without brackets - for example .where`ID=${book}`.

Executing Queries

In the previous section, we discussed how query objects are created. However, the constructed queries are not executed automatically. They still have to be explicitly sent to the database for execution. We will now explore how this is achieved.

Note

Although the fluent API of cds.ql resembles the SQL syntax, the constructed query objects are not locked in to SQL. This means that the created queries can be sent to any type of service for execution, i.e. not only to databases but also to other local or remote services. For example, if a query is sent to an OData service, it is translated into an OData request sent via HTTP.

In the following, we will examine how queries can be executed on the database. In this context, it does not matter whether the query objects were created via classic method calls or via tagged templates. In the following examples, we will use classic method calls for query construction.

Running Queries on the Database

Queries are executed on the database by passing them to the cds.db.run() method (see following figure). This applies not only to SELECT queries, as shown in the example, but also to writing database queries such as an UPDATE.

In the example, the await operator is used to wait for the result of the SELECT.one query. This result is assigned to the constant b. At runtime, the constant is therefore an object with a property called stock. The value of this property is the stock selected in the database for the book that was specified using the where() method. If a book with the specified ID cannot be found in the database table, the constant b is assigned the value undefined.

Note

The await operator can only be used inside an async function or at the top level of a module. We have therefore declared the reduceStock() method as async.

Awaiting Queries

As an alternative to the above approach, you can also simply await a constructed query. This passes the query to the cds.db.run() method by default. This means that the following and the previous example are equivalent with regard to the SELECT query:

The example also shows how the update is executed on the database.

Following the update, an object is returned to the caller in accordance with the response type of the submitOrder action. This object has a property stock whose value is the updated stock for the ordered book.

In the exercise part of this lesson you will find the complete implementation for the reduceStock() method. In this implementation, an error message is issued if the passed book ID does not exist in the database (i.e. if the constant b has the value undefined). An error message is also issued if the stock of the book is smaller than the ordered quantity.

Demonstration & Exercise: Use Queries in the Implementation of CAP Services

Note

As exercise, carry out the step-by-step instructions in the following demonstration yourself in the SAP Business Application Studio.

As a starting point for the exercise, use the outcome of the previous exercise Define and Implement a Custom Action if you have successfully completed it. Alternatively, you can also use the branch 13_custom_action from the following GitHub repository as a starting point:

https://github.com/SAP-samples/cap-development-learning-journey

The complete implementation of the simulation can be found in the 14_queries branch of the GitHub repository.

Detailed information on the content of the repository and how to use it can be found here.

Watch the video to see how to use queries in the implementation of CAP Services.

Continue to quiz