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.

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

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.

REQUEST:

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

RESULT:
Table: actor

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!

Summary

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:

Easy no-code REST API for your databases

Thad Guidry ・ Apr 11

#restapi #sql #database #opensource

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.