Define a Simple Data Model and OData Service with CDS

In the tutorial Learn about OData Fundamentals, you familiarized yourself with some of the structure and content of the Northwind OData service. In this tutorial, you’ll create your own simple OData service based on information in the Products entity set, so now’s a good time look at that product data.

Jump to the Products entity set in the V4 version of the OData service, with this URL https://services.odata.org/V4/Northwind/Northwind.svc/Products.

In a previous tutorial, we used the V3 version at https://services.odata.org/V3/Northwind/Northwind.svc/Products. This resource has a default resource representation of XML; more specifically, the value of the Content-Type header returned with this resource is application/atom+xml;type=feed;charset=utf-8 (you can check this by using your browser’s developer tools to inspect the HTTP response headers).

In this tutorial, we’re using the V4 version. After all, OData version 4 has been around as an OASIS standard since 2014. Notice that the default representation of most OData V4 resources here is JSON; more specifically, the value of the Content-Type header in the response is application/json;odata.metadata=minimal;odata.streaming=true;IEEE754Compatible=false;charset=utf-8. This JSON representation is also used for OData service document resources in V4 too, whereas in earlier versions it was XML.

The representation of the Products entity set should look something like this:

{
  "@odata.context": "https://services.odata.org/V4/Northwind/Northwind.svc/$metadata#Products",
  "value": [
    {
      "@odata.etag": "W/\"1,1\"",
      "ProductID": 1,
      "ProductName": "Chai",
      "SupplierID": 1,
      "CategoryID": 1,
      "QuantityPerUnit": "10 boxes x 20 bags",
      "UnitPrice": 18.0000,
      "UnitsInStock": 39,
      "UnitsOnOrder": 0,
      "ReorderLevel": 10,
      "Discontinued": false
    },
    {
      "@odata.etag": "W/\"1,1\"",
      "ProductID": 2,
      "ProductName": "Chang",
      "SupplierID": 1,
      "CategoryID": 1,
      "QuantityPerUnit": "24 - 12 oz bottles",
      "UnitPrice": 19.0000,
      "UnitsInStock": 17,
      "UnitsOnOrder": 40,
      "ReorderLevel": 25,
      "Discontinued": false
    }
  ]
}

This of course is just the data; to understand what you’re looking at, look now at the heart of the definition of this entity set, in the OData service’s metadata document at https://services.odata.org/V4/Northwind/Northwind.svc/$metadata.

Ignoring the navigation properties of the Product entity type for now, we see this set of property definitions:

<EntityType Name="Product">
  <Key>
    <PropertyRef Name="ProductID"/>
  </Key>
  <Property Name="ProductID" Type="Edm.Int32" Nullable="false"/>
  <Property Name="ProductName" Type="Edm.String" Nullable="false" MaxLength="40"/>
  <Property Name="SupplierID" Type="Edm.Int32"/>
  <Property Name="CategoryID" Type="Edm.Int32"/>
  <Property Name="QuantityPerUnit" Type="Edm.String" MaxLength="20"/>
  <Property Name="UnitPrice" Type="Edm.Decimal" Precision="19" Scale="4"/>
  <Property Name="UnitsInStock" Type="Edm.Int16"/>
  <Property Name="UnitsOnOrder" Type="Edm.Int16"/>
  <Property Name="ReorderLevel" Type="Edm.Int16"/>
  <Property Name="Discontinued" Type="Edm.Boolean" Nullable="false"/>
</EntityType>

So, we know that the ProductID property is the only key field, and the types of other properties make sense to us too.

To find the right balance between realism and efficiency (no-one wants to type in a large amount of definition or data), the first entity definition in the OData service you’ll create will be a cut down version of this Product entity type, encompassing the following properties:

• ProductID
• ProductName
• UnitsInStock

Further entities will be cut down versions of entities in the Northwind OData service too; this suggests that a cut down name for your OData service is appropriate too, so we’ll go from Northwind to Northbreeze (see what we did there?).

To start creating your Northbreeze OData service, start by creating a new CAP project in your App Studio dev space using the “New project from Template” wizard available on the Get Started page (if you don’t have the Get Started page open, you can recall it with menu path Help → Get Started).

In the “Select Template and Target Location” step, select the CAP Project template and then use the Start button to continue.

In the “CAP Project Details” step, enter northbreeze for the project name, ensure that “Node.js” is selected for the runtime, and leave all the other options as they are. Then select the Finish button to complete, and wait for the generated project to appear.

It’s better if you use the all-lowercase version of the name (northbreeze) as the name is used as the name of the NPM package that you’re (indirectly) creating, and convention there dictates lowercase only.

Make yourself acquainted with the content of the generated project, by looking through some key files and directories in the App Studio’s Explorer. Among these, you should see three directories named app/, db/, and srv/. To understand what these are, and how they relate to what you’re going to do in the rest of this tutorial, think of them in a vertical structure like this:

+------+
| app/ |
+------+
| srv/ |
+------+
| db/  |
+------+

At a high level this represents a typical full stack application, with the frontend represented by app/, the business logic and services represented by srv/, and the persistence layer represented by db/. CAP supports work in all of these layers.

In building your OData service, however, you won’t need to make use of the app/ layer. This is because an OData service is just that - a service. You’ll be focusing your efforts at the persistence layer (in the db/ directory) and the business logic layer (in the srv/ directory).

While ultimately you’ll have created an OData service, which is “flat”, providing access to entity data through a uniform and well understood interface, it’s best if you think about that service as being the combination of two things – schema and service – at two different levels, thus:

+------+
| app/ |
+------+
| srv/ | <-- service: combination(s) of entities focused on consumption
+------+
| db/  | <-- schema: basic level entity definitions
+------+

The OData service you’ll be creating is simple and has a one-to-one mapping between schema and service; however, note that CAP’s focus on and strong support for domain modeling allows for flexible relationships to be constructed between these two layers, to fit your service consumption needs precisely.

The db/ directory is where entities are defined, and relationships made. Think of it as the overall schema, independent of any intended consumption.

To keep things as simple as possible, you’re going to define a single entity, with only a few properties, and (at least in this tutorial) no relationships to further entities.

Use the context menu on the db/ node in the Explorer view to create a new file; give it the name schema.cds.

It’s time to define your entity, reflecting a simplified version of the Product entity type in the Northwind service definition. Here’s the entire content that should go into schema.cds.

Try to resist the temptation to copy/paste this content; instead, type it in and get to know the rich support for CAP that the App Studio sports, via the SAP CDS Language Support extension. When entering it, you don’t have to worry about formatting either - the extension will do that for you too (just use the context menu or the Command Palette to invoke the “Format Document” facility).

namespace northbreeze;

entity Products {
    key ProductID    : Integer;
        ProductName  : String;
        UnitsInStock : Integer;
}

Note that while in the Northwind service definition the entity type followed the “singular” naming approach (“Product”), the convention in CAP is to use the “plural” naming approach for entity definitions (i.e. “Products”).

Is this all that’s needed for an OData service? Let’s find out.

Open a terminal (menu path Terminal → New Terminal) and that should give you a Bash shell and put you automatically in the root directory of the project you have open in your workspace, that is, northbreeze. You’ll see a prompt, which consists of your generic username in your App Studio’s dev space, the most significant part of the name of the directory you’re in (enter the command pwd to see the full name, if you’re curious) and the traditional shell prompt character $.

user: northbreeze $

App Studio dev spaces that have been created using the “SAP Cloud Business Application” type (as you’ll have done in the prerequisite tutorial) automatically have the CAP development kit installed (also known as the CDS DK, or “Development Kit”, from the name of the NPM package @sap/cds-dki), including the main command line toolcds. One of the features incds's arsenal is thewatch` command, which will start the CAP server (the runtime) which will start serving services, and restart the CAP server when changes are detected. It will also automatically use an in-memory persistence layer provided by SQLite, which is enough for what we need here in our explorations.

At the prompt, enter cds watch, and observe the output, which should look something like this:

user: northbreeze $ cds watch
 
cds serve all --with-mocks --in-memory? 
live reload enabled for browsers 

        ___________________________
 
[cds] - loaded model from 1 file(s):

  db/schema.cds

[cds] - connect using bindings from: { registry: '~/.cds-services.json' }
[cds] - connect to db > sqlite { url: ':memory:' }
/> successfully deployed to in-memory database. 


[cds] - server listening on { url: 'http://localhost:4004' }
[cds] - launched at 10/23/2024, 3:31:06 PM, version: 8.3.1, in: 363.072ms
[cds] - [ terminate with ^C ]


    No service definitions found in loaded models.
    Waiting for some to arrive...

This tells us an awful lot already; most importantly for our question, however, is the line “No service definitions found in loaded models - Waiting for some to arrive…”.

You have defined an entity, in a namespace, but not exposed it yet in a service definition. Moreover, if you navigate to the port 4004 that App Studio will have prompted you to connect to, you’ll see a welcome page describing what is being served, and the list of service endpoints is currently empty.

So, creating a service definition is next. You can leave the cds watch process running, and it will notice and react to anything you subsequently add or change.

In this step, you’ll create the simplest service definition exposing the entire Products entity (all three elements) in a service called Main.

Create a new file in the srv/ directory, calling it service.cds. In the same fashion as in the previous step, type (rather than copy/paste) the following into it, exploring what features such as completion help are offered by the language support for CDS in the editor:

using northbreeze from '../db/schema';

service Main {
    entity Products as projection on northbreeze.Products;
}

You should see some new output from the cds watch process in the terminal, that looks like this:

[cds] - loaded model from 2 file(s):

  srv/service.cds
  db/schema.cds

[cds] - connect using bindings from: { registry: '~/.cds-services.json' }
[cds] - connect to db > sqlite { url: ':memory:' }
/> successfully deployed to in-memory database. 

[cds] - using auth strategy {
  kind: 'mocked',
  impl: '../../../../managed-content/globals/pnpm/5/.pnpm/@sap+cds@8.3.1_express@4.21.1/node_modules/@sap/cds/lib/auth/basic-auth'
} 

[cds] - using new OData adapter
[cds] - serving Main { path: '/odata/v4/main' }

[cds] - server listening on { url: 'http://localhost:4004' }

This looks promising, in particular the message about the Main service being served.

If you have still got a browser tab open and looking at the service (or lack thereof), jump to that tab and hit refresh. If you haven’t got such a browser tab open, use the Command Palette (call it up with menu path View → Find Command…) to invoke the “Ports: Preview” command, which should give you a link to connections to ports that are currently being exposed. It should look something like this:

Make the selection, and you should see a welcome page, this time listing a service endpoint, similar to this:

This tells us that you have your very own OData service, being served by the CAP runtime. Congratulations!

Let’s pause for a moment to understand what we’re seeing here. First, there are the two well-known URLs that are standard for any OData service - the service document, represented by the /odata/v4/main hyperlink, and the metadata document, represented by the $metadata hyperlink. Note also that these two components are joined with slashes like this:

/odata/v4/main/$metadata

This denotes the relative path info for the URL of your OData service. In other words, independent of what host is to serve this service, /odata/v4/main/ is the actual relative path for the service document.

Explore the service document and the metadata document now, by following the hyperlinks. There are some high-level observations that are worth making here:

• The service document faithfully reflects the fact that there is a single entity set Products available.
• The metadata document reflects exactly the details that you defined for the entity at the schema layer; this is because the service exposure (in srv/service.cds) was the simplest thing that could possibly work, that is, a “pass through” (aka “naked”) service, where no properties were filtered out, or added from elsewhere.
• The types in the entity definition (Integer, String) have been translated into OData types (Edm.Int32, Edm.String) in the Property elements within the EntityType element in the metadata document.
• The ProductID property has been correctly marked as being a key property.
• An entity set has been defined automatically for the Products entity definition, as can be seen within the EntityContainer element.

Note also that:

• In the root element (Edmx) there’s a Version attribute that declares that the OData version is 4.0.

Don’t forget to leave the cds watch running, ready for the next step!

You have got a fully functioning OData service, but it’s not as exciting as it could be - there’s no data in it yet! If you had selected the Products hyperlink on the welcome page in the previous step, you’d have seen something like this:

{
  "@odata.context": "$metadata#Products",
  "value": []
}

In this penultimate step, you’re going to seed your fledgling OData service with data. This will allow you to better kick the tires and discover that yes, this really is a fully functional CRUD+Q OData service that you have created.

Add a new directory below the db/ directory, called data/, and in there, create a comma-separated value (CSV) file. Given the right names, CSV files in this directory are automatically read, and the data within imported, into the corresponding entities, and that data can then be served in the OData service.

In order for this to work, the names of the CSV files are important, and are based on a combination of namespace and entity name, separated by a dash.

So, create a file in the new db/data/ directory called northbreeze-Products.csv and add the following records to it:

ProductID,ProductName,UnitsInStock
1,Chai,39
2,Chang,17
3,Aniseed Syrup,13

As soon as the contents of this file are saved, you should notice the cds watch restart the CAP server, but there’s also a new line in the output, that should look something like this:

> init from db/data/northbreeze-Products.csv

Great, your seed data is now part of your OData service.

Jump back to the service (via the welcome page in the previous step) and reselect the Products entity set resource. Rather than an empty array for the value property, you should now see something like this:

{
  "@odata.context": "$metadata#Products",
  "value": [
    {
      "ProductID": 1,
      "ProductName": "Chai",
      "UnitsInStock": 39
    },
    {
      "ProductID": 2,
      "ProductName": "Chang",
      "UnitsInStock": 17
    },
    {
      "ProductID": 3,
      "ProductName": "Aniseed Syrup",
      "UnitsInStock": 13
    }
  ]
}

It’s now time to finish this tutorial with a few OData operations.

There’s plenty to explore now you have some data in your simple OData service. Try your own queries, or experiment with some of these. Each time, manipulate the path info and query string as appropriate, based on the URL in your browser. Remember that for the purposes of this tutorial, the URL can be thought of as being made up of three parts. If we take an example OData URL from App Studio, it might look something like this:

https://port4004-workspaces-ws-czcx7.us10.trial.applicationstudio.cloud.sap/odata/v4/main/Products?$top=11

• The first part is the fully qualified hostname, all the way up to the first single slash.
• The second part is the path info, all the way up to the question mark.
• The third part is the query string, introduced by the question mark and made up of one or more key=value pairs, with URL-encoded values where appropriate, and joined together with & characters.

(There is another common part that we see in some URLs, and that’s the document fragment identifier, also known as the hash path, introduced with the # character, but this part is not relevant for OData URL construction).

Return just the first product
/odata/v4/main/Products?$top=1

Return a count of how many products are available
/odata/v4/main/Products/$count

Return a single product
/odata/v4/main/Products(2)

Return only those “highly stocked” products
/odata/v4/main/Products?$filter=UnitsInStock%20gt%2015

Your OData service isn’t read-only either - it supports all operations (Create, Read, Update, Delete, and Query) out of the box, with no effort on your part at all.

Try out some write operations now, by opening up a second terminal and using the command line user agent curl that’s available automatically in all App Studio dev spaces. Here are a few for you to try; in each example, you’ll see the prompt (user: northbreeze $), the actual invocation (with curl) and an indication of the expected output.

Add a further product

user: northbreeze $ curl -H "Content-Type: application/json" -d '{"ProductID":77,"ProductName":"Original Frankfurter grüne Soße","UnitsInStock":32}' http://localhost:4004/odata/v4/main/Products
{"@odata.context":"$metadata#Products/$entity","ProductID":77,"ProductName":"Original Frankfurter grüne Soße","UnitsInStock":32}

Once you have added this new product, you can check its existence by going back to the query of the entire entity set:

/odata/v4/main/Products

Reduce the number of units in stock for the Chang product

user: northbreeze $ curl -H "Content-Type: application/json" -d '{"UnitsInStock":1}' -X PATCH "http://localhost:4004/odata/v4/main/Products(1)"
{"@odata.context":"$metadata#Products/$entity","ProductID":1,"ProductName":"Chai","UnitsInStock":1}

Remove the recently added product

user: northbreeze $ curl -X DELETE "http://localhost:4004/odata/v4/main/Products(77)"

At this point, you have exercised your OData service and tried out all five OData operation types.

Well done!