CAP: Using HANA DB Sequence

But in the event that you already know the importance of using UUID in your data model and already using it, but you have a requirement to provide an external facing ID for the end-users then, by all means, go ahead and continue reading till the end.

In this blog post, I will be showing how you can use HANA DB Sequence in your CAP Model project to provide external-facing IDs for your web application. The idea behind this blog came from the Q&A section below:

https://answers.sap.com/questions/13082679/cap-auto-incremental-ids-numbering-use-of-hdbseque.html?childToView=13080861#answer-13080861

Prerequisites

• SAP Cloud Platform for Cloud Foundry Account
• SAP Business Application Studio / Visual Studio Code

CAP Model Base Project

I have prepared a starting base project where basic data models and service models (based on NorthWind service – Products entity) are already provided.

It can be found in below repository:

https://github.com/jcailan/cap-samples/tree/blog-db-sequence-base

Create a HANA DB Sequence

• 1. Start adding the HANA config file by executing command below:

> cds add hana

This will generate the .hdiconfig file in the db > src folder.

• 2. Create the file definition for your HDB Sequence. Create a file called PRODUCT_ID.hdbsequence inside db > src folder:

SEQUENCE "PRODUCT_ID" START WITH 1 MAXVALUE 2999999999
RESET BY SELECT IFNULL(MAX("ID"), 2000000500) + 1 FROM "PRODUCTS"

There’s nothing fancy in this sequence definition, it just makes sure that it gets the latest value of ID from PRODUCTS table.

• 3. Create the custom handler implementation file for your service handler. Create a file called NorthWind.js inside srv folder:

const cds = require("@sap/cds");
const SequenceHelper = require("./lib/SequenceHelper");

module.exports = cds.service.impl(async (service) => {
	const db = await cds.connect.to("db");
	const { Products } = service.entities;

	service.before("CREATE", Products, async (context) => {
		const productId = new SequenceHelper({
			db: db,
			sequence: "PRODUCT_ID",
			table: "Products",
			field: "ID"
		});

		context.data.ID = await productId.getNextNumber();
	});
});

This custom handler is for the CREATE event of Products entity and is executed before the actual creation of entity.  This is because we need to insert the logic to get the next number in the DB Sequence. The next number is provided by the SequenceHelper class in which we will create the implementation next.

• 4. Create the implementation for class SequenceHelper. Create a file called SequenceHelper.js inside srv > lib folder:

module.exports = class SequenceHelper {
	constructor (options) {
		this.db = options.db;
		this.sequence = options.sequence;
		this.table = options.table;
		this.field = options.field || "ID";
	}

	getNextNumber() {
		return new Promise((resolve, reject) => {
			let nextNumber = 0;
			switch (this.db.kind) {
				case "hana":
					this.db.run(`SELECT "${this.sequence}".NEXTVAL FROM DUMMY`)
						.then(result => {
							nextNumber = result[0][`${this.sequence}.NEXTVAL`];
							resolve(nextNumber);
						})
						.catch(error => {
							reject(error);
						});

					break;
				case "sql":
				case "sqlite":
					this.db.run(`SELECT MAX("${this.field}") FROM "${this.table}"`)
						.then(result => {
							nextNumber = parseInt(result[0][`MAX("${this.field}")`]) + 1;
							resolve(nextNumber);
						})
						.catch(error => {
							reject(error);
						});
					break;
				default:
					reject(new Error(`Unsupported DB kind --> ${this.db.kind}`));
			}
		});
	}
};

In the implementation of this helper class, I have catered all the currently supported kinds of DB in CAP Model, which are (1) HANA, (2) SQL in-memory, and (3) SQLite DB.

• 5. That’s it! The next thing to do is deploy and test the implementation of your sequence!

Deploy and Test using HANA DB

• 1. Before we can test, we need to deploy the database artifacts first. Do that by executing below command:

> cds deploy --to hana

• 2. Don’t forget to execute the below command as well:

> npm install

• 3. Start the service by executing the command:

> cds watch

• 4. We need to test the creation of a new Product entity, so you need to use a rest client tool. For me, I use Postman client, and below are my request entries:

POST http://localhost:4004/NorthWind/Products

Content-Type: application/json;IEEE754Compatible=true

{
    "Name": "Tuna",
    "Description": "Raw Tuna",
    "ReleaseDate": "1992-01-01T00:00:00Z",
    "DiscontinuedDate": null,
    "Rating": 4,
    "Price": "2.5"
}

• 5. Verify the results of the creation of a new Products entity:

{
    "@odata.context": "$metadata#Products/$entity",
    "ID": 11,
    "Name": "Tuna",
    "Description": "Raw Tuna",
    "ReleaseDate": "1992-01-01T00:00:00Z",
    "DiscontinuedDate": null,
    "Rating": 4,
    "Price": 2.5
}

The base project already has some data loaded using the Products.csv file. The last product ID is 10 and now we can see that the generated response has an ID = 11 which means our HANA DB Sequence together with the logic inside SequenceHelper.js is working.

The next thing we need to do is to test the same logic using SQL/SQLite DB.

Deploy and Test using SQLite DB

• 1. First, we need to install the sqlite3 node module. Execute below command:

> npm install --save-dev sqlite3

• 2. Then deploy our db artifacts to SQLite. We can do this by executing the below command:

> cds deploy --to sqlite

• 3. Start again the service using:

> cds watch

• 4. Test the service again using the same POST request:

POST http://localhost:4004/NorthWind/Products

Content-Type: application/json;IEEE754Compatible=true

{
    "Name": "Tuna",
    "Description": "Raw Tuna",
    "ReleaseDate": "1992-01-01T00:00:00Z",
    "DiscontinuedDate": null,
    "Rating": 4,
    "Price": "2.5"
}

• 5. Verify that you get the same result again:

{
    "@odata.context": "$metadata#Products/$entity",
    "ID": 11,
    "Name": "Tuna",
    "Description": "Raw Tuna",
    "ReleaseDate": "1992-01-01T00:00:00Z",
    "DiscontinuedDate": null,
    "Rating": 4,
    "Price": 2.5
}

As a third test, you could test the service using the SQL in-memory option and I can guarantee you that you will get the same result for the third time.

Closing

Now we can conclude that it is quite easy to use the HANA native DB Sequence together with our CAP Model Project. And together with the class SequenceHelper.js, you could test your service irrespective of the DB being used, whether it is HANA, SQLite, or in-memory SQL DB.

Just bear in mind that while I have shown in this blog how to use DB Sequence on the CAP Model project, this option should only be used if there’s a good justification for showing this ID to the end-user. If the ID is not relevant for end-user to see or use, then always make use of UUID which already the defacto standard for building CAP model projects.

~~~~~~~~~~~~~~~~

Appreciate it if you have any comments, suggestions, or questions. Cheers!~