Skip to Content
Technical Articles

CAP: Using HANA DB Sequence

If you are reading this blog because you want to know how to use HANA DB Sequence in your CAP Model project because you don’t want to use the UUID (GUID aspect) concept provided by the CDS framework, then I’m discouraging you to continue reading. Go back to the CAP Model Documentation website and learn how to use UUID in all of your data models. There are a lot of advantages in using UUID compared to DB Sequences or running number and I won’t be discussing that in this blog because there are already tons of information about it over the internet.

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.

To instantiate a SequenceHelper object, you need to pass the parameters below during instantiation:

  • db connetion
  • sequence name
  • table name
  • field name (defaults to ID if not provided)
  • 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.

Disclaimer:

  1. Using the solution above have a slight performance trade-off by querying the “NEXTVAL” value in Node.js runtime. The best option is to get this value directly via a call in the DML statement. It would be great if future versions of the CAP Model framework support the handling of DB sequences.
  2. Handling of incrementing numbers for SQLite DB doesn’t cater for multi-user scenario, simply because the use case for SQLite DB, in this case, is for developer local testing only. It is not meant for a productive use case.

For a more detailed explanation of this disclaimer, kindly read the comments made by Lars Breddemann below.

  • 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"
}

Note:

The additional parameter IEEE754Compatible=true is needed for testing OData V4 services. Without it, the service will return an error asking you to enable this parameter.

  • 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!~

10 Comments
You must be Logged on to comment or reply to a post.
  • Don’t mind if I take you up on the request in your last sentence.

    First off: this is a really nicely produced article! Well done.

    There are many other blog posts and articles out “there” that could use a tutor session with you when it comes to structuring and formatting. Good job.

    What’s not so good is the coding part of the article. Or rather the design of the solution.

    I know that you were interested in how to make the DB object “sequence” usable in an application build in “CAP”. And that you achieved – with your code the numbers are generated by a DB sequence with the code is running on HANA.

    So, what’s not to like?

    It’s that neither of the solutions (HANA/SQLLite) delivers what is expected in the first place.

    The single reason to use DB sequences is speed. DB sequences only guarantee that the dispersed numbers always increase (they might wrap-around as a special case). They don’t guarantee that there won’t be any gaps between the dispersed numbers and they are not bound to transaction logic.

    A rollback does not take back numbers in a sequence.
    The benefit of this is that the number-dispersion can happen extremely fast with no locks and barely any waits involved. This allows for highly parallelized processes that all get unique, increasing numbers.
    In short, it’s a tool for high-performance, parallel computing on DB level.

    In your implementation, this benefit is wasted by fetching the generated number to the application level (instead of including the .nextValue call in the DML statement).

    Due to the way sequences work, it’s often not a good choice for user-facing IDs either. Those IDs are usually sensitive to gaps and because they do have inherent meaning, the number part cannot just be any number (otherwise there is a lack of meaning…).

    Thus, this implementation does not provide any of the expected benefits of using a sequence.

     

    The SQLlite implementation, however, is not correct either. This one does not use sequences but just looks at the highest value in the table, increments it and that’s the new number.

    But DBs are multi-user systems – yes, even SQLLite! Awesome little DBMS!

    So it could easily happen that once you’ve fetched your MAX(value) another process comes and inserts new data with the same new ID that you’ve just computed. This is because in your SELECT MAX() statement you did not lock the table for other readers.

    You read that right: in order to ensure that no other process can compute the same new ID at the same time, you have to lock the whole table against READING and KEEP THE LOCK until your INSERT transaction has finished.
    This would lead to correct, unique IDs without gaps but is not suitable for a high-transaction workload.

    From my experience, I’d say that these are typical misunderstandings, misdesigns, and faults when “application-level” developers start to work on “DB-level” environments. The application-frameworks just do a job too well of shielding the developers from such considerations.

    I hope you don’t take this as a rant against your article; this comment is meant to highlight conceptual issues and not to make you feel bad.

    Thanks for sharing this and for allowing others to learn as well.

     

    Cheers,

    Lars

     

    • Hi Lars Breddemann,

      Thanks for the very nice compliment! And also for the constructive criticism, which is very much welcome! It’s always nice to learn about other people’s perspectives and experiences.

      Let me start first with SQLite. I don’t mean to sound too defensive, but SQLite for me is just a means to test my CAP application locally. It did occur to me the fact that this solution doesn’t work for a multi-user scenario, but if I’m using SQLite for local testing, then I’m the only user there is. The same thing goes if this very same CAP project is tested locally by another developer, they will be the only user in their own local testing. I could opt-in making the solution more robust, but the effort to do so will not really add value to the normal use case. Do you agree?

      And for the HANA DB Sequence case, your point is well taken. Thanks for your very detailed explanation. Okay not to sound too defensive again, but here it goes, I just wanted to maneuver around the lack of support for using HDB Sequences in the new CDS framework (or the lack of knowledge if such functionality is supported). I totally agree that it is best to let the “NEXTVAL” query be handled via a call in the DML statement, however, isn’t this supposed to be catered CDS framework instead? Or you have an idea of how to achieve this while maneuvering around CDS framework? My thinking is that the current solution I have is a temporary workaround solution until CAP Model starts supporting this feature. But I’m all in for a more elegant solution than this, please do share your thoughts.

      Lastly, I really appreciate the comment, it is very informative!

       

      • Glad you took my comments the way I intended them.

        Look, what you describe as a “normal” use case is normal to your current situation as a developer. With your own separate DB instance to work with.
        While this leaves out many potential issues, your intention is to write programs that run on shared databases and that are used by many users in parallel.
        Here, the intention is what your solution should be built for – not your current (albeit convenient, but special) situation as a developer.

        Besides, you wrote that you were aware that this solution wouldn’t work correctly in a multi-session scenario but the code does not contain any handling of this issue. There is no comment explaining this and there is no dealing with this situation in code either. That’s hidden technical debt and I bet many developers would easily overlook this.

        Concerning the HANA DB: as I wrote, I understand your motivation to make the DB sequence consumable in some form. But if I, the consumer, cannot get any of the benefits of it, due to the way this is implemented, why would I still want to use it?
        The goal is not to use the HANA sequence, but to be able to generate ID-numbers with certain properties in a way that comes with certain transaction and performance properties.

        And I’m sure you know the adage about “temporary solutions” in IT… this is the stuff that will be very hard to remove later in a project/product. That’s why it’s important to get it right and fit for purpose at this stage in the development.

        • Hi Lars Breddemann,

          I wrote a disclaimer on my original post to highlight the good points you mentioned. I agree that while I took it as a consideration, it may not be that transparent to other developers.

          You have pointed out a very good argument about “temporary solutions” in which I totally agree. But it can be like a chicken and egg situation, you know that what you are designing today can be replaced by the implementation of SAP as the CAP Model matures in future releases. You built a cloud application yesterday using XSJS and XSODATA but now CAP Model is in the picture. You jump in today and use the latest programming model and you are faced to fill in some gaps yourself. And if you jump in too late, you will miss a lot of the cool stuff that is happening.

  • Hi Jhodel Cailan,

     

    Good and helpful post, could you please help me with deep insert. If am using the above mentioned steps, and trying to use deep insert, the foreign table ID is not getting generated and am facing error : ERROR | 1254311]: cannot insert NULL or update to NULL: Not nullable “ID” column…

    eg : only the Parent Entity ID is generated and failing with Child(Foreign Entity ).

    I used Compositions, and also am in need of sequential ID’s

    Thanks in advance

    • Thanks for the comment VM Kumar !

      Based on the error, it seems like you’re inserting an entity with null for a not nullable property. I would suspect there’s something wrong with your logic where you perform the insert.

      • Hi Jhodel, yes I tried placing the generation of ID for Foreign inside the ID generation of Parent Entity, still that ID is not consider, Am not passing the the ID in JSON format as that is sequence generated ID is what I wanted to use for Parent and Foreign Entities at same time while performing the Deep insert.  The Json is some like Below…Am not passing ID for Parent and Foreign as that is sequence No is what I wanted. The same is working if I passed individual inserts, but deep insert as below is failing, I understood that it is not able to generate the ID for Child Entity and trying to post, so am getting that error. Trying to resolve it, any suggestion please?

        Post…../Parent

        {“name”:”Test1″,”header”: { “cost”:120″,”currency”:”INR”} }

        • Hi VM Kumar

          I can’t imagine where could be the issue based on your description above. I haven’t tried deep insert on CAP yet, because I’m only using running number for exceptional cases, and that doesn’t require deep insert. Maybe your case should have been UUID instead. See comment below by Serdar which elaborates the use cases of using DB Sequence or running numbers.

  • Hey Jhodel Cailan,Lars Breddemann

    @ Jhodel: Very well-written article.

    @ Lars: I share your concerns. In my technical domain I love UUIDs and I am happy to have UUIDs. But, there is a big “but” here. This requirement is a very reasonable one in business. I cannot think of a world where we do not have these human friendly IDs.

    For example, our current customer service today use account IDs and that’s one of the first things customer service agents would ask to easily locate customer’s account rather than asking a bunch of questions. In customer service, minimising the call time is paramount.

    If a customer calls a supplier and needs to provide a reference to the sale context, they would ask for an order number. In a world with no user friendly IDs, the customer would need to tell their full name, address, purchase date. To uniquely specify, it may be needed to even add purchase time.

    I haven’t seen one commercial sale transaction where I do not get an order number.

    How about I send this invoices report to Mr Blogs so that he can manually check. Now, he needs to enter 32 chars (instead of 8) for each and would probably severe his farsightedness.

    Now, the requirement is a unique identifier. Either we come up with one by concatenating key fields or by generating a unique number. I think we would agree the former option is not really feasible in most cases. So, we have two options:

    UUID: Universal but difficult to use

    Sequential ID: Not universal, easy to use

    Here, we can trade off universal uniqueness with a shorter identifier if the context is specific unless we convince the world to use 32 character identifiers. Again, for example, in a customer service call using such an identifier would be very painful.

    When asking for a sequential ID, I am not suggesting not using UUIDs. We can keep both. Use sequential IDs where there is user interaction as it makes it easy for humans to use it. In all other cases, use UUIDs.

    The concurrency problem is handled well in NW ABAP (I am not much familiar with HANA development; but, I presume the same holds for hdbsequence, too). Number ranges in NW ABAP provide us the means to acquire sequential numbers. We have been using them for decades. There is buffering and, yes, gaps may occur. But, I do not think gaps in a sequence is a problem at all.

    Your concern to push the nextval to DML is surely valid and in my question for which Jhodel provided the solution, this was the reason I was pushing further for number retrieval to happen with hdbtrigger which makes it closer to the DB transaction.

    In CAP, there is no streamlined solution to the above requirement for which I hope I provided enough justification. And that’s the reason we are looking elsewhere to fill in the gap.

     

    • Thanks Serdar Simsekler !

      Your comment is very well articulated! This is a very good justification and I hope somebody from the CAP team will be able to read it and will consider including support in future releases.