DEV Community

Cover image for Instant API for databases adds automatic TSID primary keys
Thad Guidry
Thad Guidry

Posted on

Instant API for databases adds automatic TSID primary keys

In a previous post, we saw how we can read and write data through an instant API provided by DB2Rest. It would be nice to have a way to automatically create chronological sequences of our written data so we do not have to code anything extra in our application when writing data out through our API.

Benefits of TSIDs

DB2Rest has a built-in ability to automatically create Time-Sorted Unique Identifiers (TSID) that offer benefits such as the following:

  • Chronologically sorted like integers.
  • Compact storage (14 bytes).
  • Efficient indexing due to sequential nature.

TSIDs combine the benefits of integers, UUIDs, and ULIDs and are a recommended alternative for primary key values. They borrow some structure from Snowflake IDs and Instagram IDs.

One of the additional benefits are that we get automatic date timestamping that we can use for things like sorting and data analysis later on. This can save us from adding an additional Date column in our schema for our database tables. We get a timestamp, a node (shard) id, and a sequence number, all in one! TSIDs come in handy when writing data about temporal entities or anything that is event-based, like comments, posts, transactions, etc. But they can also be used as a primary key for non-temporal data in your tables.

Click for structure of TSID
  • 42-bit timestamp: Calculated using milliseconds from a reference date (e.g., 2020-01-01).
  • 10-bit machine ID: Unique identifier for the machine generating the TSID.
  • 12-bit sequence number: Ensures uniqueness within the same millisecond.

When stored as a long TSIDs look like this:
And when written as Text or a String they look like this:

Auto Generating TSIDs

DB2Rest can automatically generate TSID primary keys as it inserts data through its instant API into your database tables. It does this by auto detecting an existing primary key column in the table you are writing to and will use the datatype category (Integer-based or Text/String based) to generate the appropriate TSID format (Long/Number or Text).

Let's use a DB2Rest API endpoint to write to our /actor/ table with bulk data and let DB2Rest worry about automatically creating our tables' primary keys for each row inserted.


curl --request POST \
--url http://localhost:8080/actor/bulk \
--header 'Content-Type: application/json'
--data '[

        "first_name" : "Salman",
        "last_name" : "Khan"

        "first_name" : "Hrithik",
        "last_name" : "Roshan"

        "first_name" : "Tom",
        "last_name" : "Cruise"
Enter fullscreen mode Exit fullscreen mode

Table: actor

id first_name last_name
0012 John Travolta
0036 George Clooney
01226N0640J7P Salman Khan
01226N0640J7Q Hrithik Roshan
01226N0640J7R Tom Cruise

Notice that we had 2 existing rows in our actor table with id's 0012 and 0036 with famous actor names?

DB2Rest detected the data type of our existing id column and determined to use the String format for automatic TSID generation when writing the 3 new rows of data from our API POST request. We didn't have to write any special application code ourselves to do this or even setup a database stored procedure!


What's really amazing here is that DB2Rest automatically creates an API for our actor table (securely and safely) for fast frontend application development. We didn't have to code an API data access layer for our database tables at all! DB2Rest does this for us without using an ORM (Object Relational Mapper) or using any code generation that slows things down!

Read more about DB2Rest benefits in my previous post:

In summary, TSIDs strike a balance between integers and UUIDs, offering chronological sorting, compactness, and efficient indexing. If you plan to store UUID values in a primary key column, TSIDs are a recommended alternative.

You can save 100's of hours of coding data access layers by quickly enabling an instant API for your database by using DB2Rest , an APACHE 2 licensed open source middleware.

Top comments (0)