Search

    Using Generic Providers

    CAP provides numerous generic handlers for common, recurring tasks which serve many requests out-of-the-box. This helps to capture proven best practices collected from a wealth of successful SAP applications.

    Serving CRUD Requests

    The Service SDKs for both Node.js and Java automatically add generic handlers to each application service, which handle all CRUD requests automatically under these conditions:

    • A default datasource is configured (for example, cds.env.requires.db in Node.js)
    • The exposed entity wasn’t defined using UNION or JOIN

    Auto-Generated Primary Keys

    On CREATE and UPSERT operations, key elements of type UUID are filled in automatically. In addition, on deep inserts and upserts, respective foreign keys of nested objects are filled in accordingly.

    Serving Structured Documents

    Structured documents represent the concept of relationships between entities, which can be modeled using compositions and associations. The runtime provides generic handlers covering basic CRUD requests for main (or parent) entities and their associated entities.

    Deep READ using $expand

    Use the $expand query parameter to read structured documents via OData. For example, this request:

    GET .../Orders?$expand=header($expand=note)
    

    … would return an array of nested structures as follows:

    [{
        ID:1, title: 'first order', header: {
          ID:2, status: 'open', note: {
            ID:3, description: 'first order notes'
          }
        }
      },{
        ID:4, title: 'second order', header: {
          ID:5, status: 'payed', note: {
            ID:6, description: 'second order notes'
          }
        }
      },
      ...
    ]
    

    Deep INSERT for Compositions

    To create the parent entity and its child entities, use a single POST request. That means that the body of the POST request must contain a complete set of data for all entities. On success, this request will create all entities and relate them. On failure, none of the entities will be created.

    Sample POST request with to-one composition:

    POST .../Orders {
      ID:1, title: 'new order', header: {
        ID:2, status: 'open', note: {
          ID:3, description: 'child of child entity'
        }
      }
    }
    

    A POST request for a to-many relationship:

    POST .../Orders {
      ID:1, title: 'new order', header: {
        ID:2, status: 'open', notes: [{
          ID:3, description: 'child of child entity'
        },{
          ID:4,description: 'another child of child entity'
        }]
      }]
    }
    

    Deep UPDATE for Compositions

    Similar to the deep INSERT case, the deep UPDATE of the structured document must be made with a single PUT or PATCH request to the parent entity. Depending on the request body, child entities will be created, updated, or deleted:

    • Child entities existing on the database, but not in the payload, will be deleted.
    • Child entities existing on the database and in the payload will be updated (specified fields overwrite the values on the database).
    • Child entities not existing on the database will be created.

    Consider the POST request with to-one composition from above. The following request would update this Orders entry:

    PUT .../Orders/1 {
      title: 'another order', header: {
        ID:4, status: 'canceled'
      }
    }
    

    The title property of the Orders parent entity will be changed, the child entities with the ID=2 and ID=3 will be deleted, and a new child entity with the ID=4 will be created.

    Deep DELETE for Compositions

    Since child entities of compositions can’t exist without their parents, a DELETE of the parent entity is a cascading delete of all nested composition targets.

    DELETE .../Orders/1
    

    Would delete Orders, OrderHeaders, and SpecialNotes from the example above.

    INSERT with Associations

    Entities with association relationships must be created with separate POST requests. It means that, unlike in the case of deep INSERT with compositions, the association targets (Authors in the example above) must be created before the parent entity.

    Then you can send a request to create the entity Books with the body looking like a deep INSERT. The association to the existing entity Authors is created by providing the key of the entity.

    POST .../Authors {
      ID:12, name: 'Charlotte Brontë'
    }
    
    POST .../Books {
      ID:121, title: 'Jane Eyre', author: { ID:12 }
    }
    

    A POST request with the body looking like a deep INSERT can only be sent for targets of managed to-one associations. In other cases, you can either send the foreign keys explicitly or implement your own custom handler.

    Note, that unlike the case of composition, there’s no deep Insert/Update for associated entities, therefore all non-key fields of author in the POST request on Books are ignored and their values aren’t changed.

    UPDATE with Associations

    An UPDATE request of structured documents with association relationships can be made with a PATCH or PUT request to the parent entity. You can use it to update the parent entity’s data or to change references to association targets. The restrictions of the INSERT operation are valid for the UPDATE case as well.

    DELETE with Associations

    A DELETE operation is forbidden if a managed to-one association exists with the foreign key pointing to the entity that you want to delete.

    Consider the example:

    entity Books {
      key ID : Integer;
      orders : Association to many Orders on orders.book = $self;
    }
    entity Orders {
      book : Association to Books;
    }
    

    The DELETE Request:

    DELETE .../Books(ID=3)
    

    will fail if the entity Orders has a foreign key book_ID=3. In all other cases the DELETE operation will be successful, but unlike deep DELETE, for compositions, no association targets will be affected by it.

    Programmatic Usage w/ Node.js

    Similar to the HTTP request-based examples above, you can also construct queries programmatically and send them to connected or local services for execution. See the following examples:

    Deep READ:

    const srv = cds.connect.to ('<some queryable service>')
    const orders = srv.run (SELECT.from ('Orders', o => { // projection
      o('*'), o.header (h => { // nested projection => 1st $expand
        h('*'), h.note('*') // nested projection => 2nd $expand
      })
    }))
    

    Alternatively, use srv.read() as a shortcut to srv.run (SELECT.from(...)):

    const srv = cds.connect.to ('<some queryable service>')
    const orders = await srv.read ('Orders', o => { // projection
      o('*'), o.header (h => { // nested projection => 1st $expand
        h('*'), h.note('*') // nested projection => 2nd $expand
      })
    })
    

    See Node.js API Reference to learn more about Node.js API.

    Deep INSERT:

    const srv = cds.connect.to ('<some queryable service>')
    srv.run (INSERT.into ('Orders').entries ({
       ID:1, title: 'new order', header: {
         ID:2, status: 'open', note: {
           ID:3, description: 'child of child entity'
         }
       }
    }))
    

    Alternatively, use srv.create() as a shortcut to srv.run (INSERT.from(...)):

    const srv = cds.connect.to ('<some queryable service>')
    srv.create ('Orders') .entries ({
       ID:1, title: 'new order', header: {
         ID:2, status: 'open', note: {
           ID:3, description: 'child of child entity'
         }
       }
    })
    

    See Node.js API Reference to learn more about Node.js API.

    Serving Media Data

    The following annotations can be used in the service model to indicate that an element in an entity contains media data.

    @Core.MediaType
    Indicates that the element contains media data (directly or using a redirect). The value of this annotation is either a string with the contained MIME type (as shown in the first example), or is a path to the element that contains the MIME type (as shown in the second example).
    @Core.IsMediaType
    Indicates that the element contains a MIME type. The @Core.MediaType annotation of another element can reference this element.
    @Core.IsURL @Core.MediaType
    Indicates that the element contains a URL pointing to the media data (redirect scenario).

    The following examples show these annotations in action:

    1. Media data is stored in a database with a fixed media type image/png:
    entity Books { //...
      image : LargeBinary @Core.MediaType: 'image/png';
    }
    
    1. Media data is stored in a database with a variable media type:
    entity Books { //...
      image : LargeBinary @Core.MediaType: imageType;
      imageType : String  @Core.IsMediaType;
    }
    
    1. Media data is stored in an external repository:
    entity Books { //...
      imageUrl  : String @Core.IsURL @Core.MediaType: imageType;
      imageType : String @Core.IsMediaType;
    }
    

    Reading Media Resources

    Read media data using GET requests of the form /Entity(<ID>)/mediaProperty.

    GET ../Books(201)/image
    > Content-Type: application/octet-stream
    

    The response’s Content-Type header is typically application/octet-stream.
    The media data is streamed automatically.

    Creating a Media Resource

    As a first step, create an entity without media data using a POST request to the entity. After creating the entity, you can insert a media property using the PUT method. The MIME type is passed in the Content-Type header. Here are some sample requests:

    POST ../Books
    Content-Type: application/json
    { <JSON> }
    
    PUT ../Books(201)/image
    Content-Type: image/png
    <MEDIA>
    

    The media data is streamed automatically.

    Updating Media Resources

    The media data for an entity can be updated using the PUT method:

    PUT ../Books(201)/image
    Content-Type: image/png
    <MEDIA>
    

    The media data is streamed automatically.

    Deleting Media Resources

    One option is to delete the complete entity, including all media data:

    DELETE ../Books(201)
    

    Alternatively, you can delete a media data element individually:

    DELETE ../Books(201)/image
    

    Reading External Media Resources

    The following are requests and responses for the entity containing redirected media data from the third example, “Media data is stored in an external repository”.

    This format is used by OData-Version: 4.0. To be changed in OData-Version: 4.01.

    GET: ../Books(201)
    >{ ...
        image@odata.mediaReadLink: "http://other-server/image.jpeg",
        image@odata.mediaContentType: "image/jpeg",
        imageType: "image/jpeg"
    }
    

    Implicit Pagination

    By default, the generic handlers for READ requests automatically truncate result sets to a size of 1,000 records max. If there are more entries available, a link is added to the response allowing clients to fetch the next page of records.

    The OData response body for truncated result sets contains a nextLink as follows:

    GET .../Books
    >{
      value: [
        {... first record ...},
        {... second record ...},
        ...
      ],
      @odata.nextLink: "Books?$skiptoken=1000"
    }
    

    To retrieve the next page of records from the server, the client would use this nextLink in a follow-up request, like so:

    GET .../Books?$skiptoken=1000
    

    On firing this query, you get the second set of 1,000 records with a link to the next page, and so on, until the last page is returned, with the response not containing a nextLink.

    Configuring Defaults with cds.query.limit

    You can configure default and maximum limits in the app environment as follows.

    • The maximum limit defines the maximum number of items that can get retrieved, regardless of $top.
    • The default limit defines the number of items that are retrieved if no $top was specified.

    The two limits can be specified as follows:

    Environment:

    query: {
      limit: {
        default: 20, //> no default
        max: 100     //> default 1,000
      }
    }
    

    Annotation @cds.query.limit

    You can override the defaults by applying the @cds.query.limit annotation on the service or entity level, as follows:

    @cds.query.limit: { default?, max? } | Number
    

    The limit definitions for CatalogService and AdminService in the following example are equivalent.

    @cds.query.limit.default: 20
    @cds.query.limit.max: 100
    service CatalogService {
      [...]
    }
    @cds.query.limit: { default: 20, max: 100 }
    service AdminService {
      [...]
    }
    

    @cds.query.limit can be used as shorthand if no maximum limit needs to be specified at the same level.

    @cds.query.limit: 100
    service CatalogService {
      entity Books as projection on my.Books;     //> pages at 100
      @cds.query.limit: 20
      entity Authors as projection on my.Authors; //> pages at 20
    }
    service AdminService {
      entity Books as projection on my.Books;     //> pages at 1000 (default)
    }
    

    Precedence

    The closest limit applies, that means, an entity-level limit overrides that of its service, and a service-level limit overrides the global setting. The value 0 disables the respective limit at the respective level.

    @cds.query.limit.default: 20
    service CatalogService {
      @cds.query.limit.max: 100
      entity Books as projection on my.Books;     //> default = 20 (from CatalogService), max = 100
      @cds.query.limit: 0
      entity Authors as projection on my.Authors; //> no default, max = 1,000 (from environment)
    }
    

    Implicit Sorting

    Paging requires implied sorting, otherwise records might be skipped accidentally when reading follow-up pages. By default the entity’s primary key is used as a sort criterion.

    For example, given a service definition like this:

    service CatalogService {
      entity Books as projection on my.Books;
    }
    

    The SQL query executed in response to incoming requests to Books will be enhanced with an additional where clause as follows:

    SELECT ... from my_Books
    ORDER BY ID; -- default: order by the entity's primary key
    

    If the request specifies a sort order, for example, GET .../Books?$orderby=author, both are applied as follows:

    SELECT ... from my_Books ORDER BY
      author,     -- request-specific order has precedence
      ID;         -- default order still applied in addition
    

    We can also define a default order when serving books as follows:

    service CatalogService {
      entity Books as projection on my.Books order by title asc;
    }
    

    Now, the resulting order by clauses are as follows for GET .../Books:

    SELECT ... from my_Books ORDER BY
      title asc,  -- from entity definition
      ID;         -- default order still applied in addition
    

    … and for GET .../Books?$orderby=author:

    SELECT ... from my_Books ORDER BY
      author,     -- request-specific order has precedence
      title asc,  -- from entity definition
      ID;         -- default order still applied in addition
    

    Managed Data

    Use the annotations @cds.on.insert and @cds.on.update to signify elements to be auto-filled by the generic handlers. For example, you could add fields to track who created and updated data records and when, as follows:

    entity Foo { //...
       createdAt  : Timestamp @cds.on.insert: $now;
       createdBy  : User      @cds.on.insert: $user;
       modifiedAt : Timestamp @cds.on.insert: $now  @cds.on.update: $now;
       modifiedBy : User      @cds.on.insert: $user @cds.on.update: $user;
    }
    

    Equivalent definition, using pre-defined aspect managed from @sap/cds/common:

    using { managed } from '@sap/cds/common';
    entity Foo : managed { /*...*/ }
    

    Generic Handlers for Managed Data

    These rules apply:

    • Data is auto-filled, that is, data is ignored if provided in the request payload.
    • Data can be filled with initial data, for example, through .csv files.
    • Data can be set explicitly in custom handlers. For example:

      Foo.modifiedBy = req.user.id
      Foo.modifiedAt = new Date()
      

    In effect, values for these elements are handled automatically and are write-protected for external service clients.

    Pseudo Variables $user and $now

    The pseudo variables used in the annotations are resolved as follows:

    • $now is replaced by the current server time (in UTC)
    • $user is the current user’s ID as obtained from the authentication middleware

    Learn more about Authentication in Node.js. Learn more about Authentication in Java.

    Note the differences to defaults, for example, given this model:

    entity Foo { //...
      managed   : Timestamp @cds.on.insert: $now;
      defaulted : Timestamp default $now;
    }
    

    While both behave identical INSERTs on database-level operations, they differ for CREATE requests on higher-level service providers: Values for managed in the request payload will be ignored, while provided values for defaulted will be written to the database.

    Input Validation

    @readonly Fields

    Elements annotated with @readonly, as well as virtual elements and calculated fields, are protected against write operations. That is, if a CREATE or UPDATE operation specifies values for such fields, these values are silently ignored.

    The same applies for fields with the OData Annotations @FieldControl.ReadOnly, @Core.Computed, or @Core.Immutable (the latter only on UPDATEs).

    @mandatory Fields

    Elements marked with @mandatory are checked for nonempty input: null and (trimmed) empty strings are rejected.

    The same applies for fields with the OData Annotation @FieldControl.Mandatory.

    @assert.unique Constraints

    Annotate an entity with @assert.unique.<constraintName>, specifying one or more element combinations to enforce uniqueness checks on all CREATE and UPDATE operations. This annotation is applicable to definitions that are rendered as tables. Those are either non-query entities or query entities annotated with @cds.persistence.table. The value of the annotation is an array of paths referring to elements in the entity. The path leaf may be an element of a scalar, structured, or managed association type. Individual foreign keys or unmanaged associations can’t be accessed. If the path points to a structured element, the unique constraint will contain all columns stemming from the structured type. If the path points to a managed association, the unique constraint will contain all foreign key columns stemming from this managed association.

    For example:

    @assert.unique: {
      locale: [ parent, locale ],
      timeslice: [ parent, validFrom ],
    }
    entity LocalizedTemporalData {
      key record_ID : UUID; // technical primary key
      parent    : Association to Data;
      locale    : String;
      validFrom : Date;  validTo : Date;
    }
    

    In essence, key elements constitute special cases of @assert.unique constraints.

    @assert.integrity Constraint for to-one Associations

    All Association to one are automatically checked for referential integrity, that is:

    • CREATEs and UPDATEs are rejected if a reference’s target doesn’t exist
    • DELETEs are rejected if it would result in dangling references
    • … except for associations, entities, or services annotated with @assert.integrity:false
    -- Equivalent SQL DDL statement:
    CREATE TABLE Books ( -- elements ...
      CONSTRAINT FK_author FOREIGN KEY (author_ID) REFERENCES Authors (ID)
    )
    

    This feature is currently only available on the CAP Node.js stack.

    In the Node.js stack, the integrity checks can be skipped via global config cds.env.features.assert_integrity = false.

    @assert.format Pattern Check Constraints

    Allows you to specify a regular expression string (in ECMA 262 format in CAP Node.js and java.util.regex.Pattern format in CAP Java) that all string input must match.

    entity Foo {
      bar : String @assert.format: '[a-z]ear';
    }
    

    @assert.range Check Constraints

    Allows you to specify [ min, max ] ranges for elements with ordinal types — that is, numeric or date/time types. For enum elements, true can be specified to restrict all input to the defined enum values.

    entity Foo {
      bar : Integer  @assert.range: [ 0, 3 ];
      boo : Decimal  @assert.range: [ 2.1, 10.25 ];
      car : DateTime @assert.range: ['2018-10-31', '2019-01-15'];
      zoo : String   @assert.range enum { high; medium; low; };
    }
    

    Specified ranges are interpreted as closed intervals, that means, the performed checks are min ≤ input ≤ max.

    @assert.notNull

    Annotate a property with @assert.notNull: false to have it ignored during the generic not null check, for example if your persistence fills it automatically.

    entity Foo {
      bar : String not null @assert.notNull: false;
    }
    

    Search Capabilities

    The search capability is enabled by default, allowing any structured document (entity with associated entities) to be searched for a search term. By default all elements of type String of an entity are searchable.

    There are situations, when you would need to deviate from this default and specify a different set of searchable elements, or to extend the search to associated entities. The @cds.search annotation is used for that purpose:

    General Usage

    The @cds.search annotation allows you to include or exclude elements from the set of searchable elements.

    In general, the @cds.search annotation looks like this:

    @cds.search : {
        element1,         // included
        element2 : true,  // included
        element3 : false, // excluded
        accoc1,           // searchable elements from associated entity
        accoc2.elementA   // included one element from associated entity
    }
    entity E { }
    

    Examples

    Let’s consider the following example with two entities defined:

    entity Books {
      key ID : UUID;
      title  : String;
      descr  : String;
      isbn   : String;
      author : Association to Authors;
    }
    
    entity Authors {
      key ID    : UUID;
      name      : String;
      biography : String;
      books     : Association to many Books on books.author = $self;
    }
    

    Search in All String Elements

    No need to annotate anything at all.

    Search in Certain Elements

    @cds.search : {title}
    entity Books { ... }
    

    Searches the title element only.

    Exclude Elements from Being Searched

    @cds.search : {isbn : false}
    entity Books { ... }
    

    Searches all elements of type String excluding the element isbn, which leaves the title and descr elements to be searched.

    Search Elements of Associated Entity

    @cds.search : {author}
    entity Books { ... }
    
    @cds.search : {biography : false}
    entity Authors { ... }
    

    Searches all elements of the Books entity, as well as all searchable elements of the associated Authors entity. Which elements of the associated entity are searchable is determined by the @cds.search annotation on the associated entity. So, from Authors all elements of type String are searched but biography is excluded.

    Search Using Path Expression

    @cds.search : {
        isbn : false,
        author.name
    }
    entity Books { ... }
    

    Searches the title and descr elements from Books as well as the element name of the associated Authors entity.


    Concurrency Control

    Conflict Detection Using ETags

    The CAP runtimes support optimistic concurrency control and caching techniques using ETags. An ETag identifies a specific version of a resource found at a URL.

    Enable ETags by adding the @odata.etag annotation to an element to be used to calculate an ETag value as follows:

    using { managed } from '@sap/cds/common';
    entity Foo : managed {...}
    annotate Foo with { modifiedAt @odata.etag } 
    

    The value of an ETag element should uniquely change with each update per row. The modifiedAt element from the pre-defined managed aspect is a good candidate, as this is automatically updated. You could also use update counters or UUIDs, which are recalculated on each update.

    You use ETags when updating, deleting, or invoking the action bound to an entity by using the ETag value in an If-Match or If-None-Match header. The following examples represent typical requests and responses:

    POST Employees { ID:111, name:'Name' }
    > 201 Created {'@odata.etag': 'W/"2000-01-01T01:10:10.100Z"',...}
    //> Got new ETag to be used for subsequent requests...
    
    GET Employees/111
    If-None-Match: "2000-01-01T01:10:10.100Z"
    > 304 Not Modified // Record was not changed
    
    GET Employees/111
    If-Match: "2000-01-01T01:10:10.100Z"
    > 412 Precondition Failed // Record was changed by another user
    
    UPDATE Employees/111
    If-Match: "2000-01-01T01:10:10.100Z"
    > 200 Ok {'@odata.etag': 'W/"2000-02-02T02:20:20.200Z"',...} 
    //> Got new ETag to be used for subsequent requests...
    
    UPDATE Employees/111
    If-Match: "2000-02-02T02:20:20.200Z"
    > 412 Precondition Failed // Record was modified by another user 
    
    DELETE Employees/111
    If-Match: "2000-02-02T02:20:20.200Z"
    > 412 Precondition Failed // Record was modified by another user 
    

    SELECT for Update

    If you want to read and update the retrieved data within the same transaction, you need to make sure that the obtained data isn’t modified by another transaction at the same time.

    The SELECT FOR UPDATE statement allows you to lock the selected records so that other transactions are blocked from changing the records in any way.

    The records are locked until the end of the transaction by commit or rollback statement.

    Learn more about using the SELECT FOR UPDATE statement in Node.js runtime.

    Persistent Locks

    Show/Hide Beta Features