In aprevious post,we saw how we can read and write data through aninstant APIprovided byDB2Rest.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
DB2Resthas 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 inde xing 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 fromSnowflake IDsand 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 abouttemporal entitiesor 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
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
DB2Restcan 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
| 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 ouractortable with id's0012and0036with famous actor names?
DB2Restdetected the data type of our existingidcolumn 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 ouractortable (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 inde xing. If you plan to store UUID values in a primary key column, TSIDs are a recommended alternative.
You cansave 100's of hours of codingdata access layers by quickly enabling aninstant APIfor your database by usingDB2Rest,an APACHE 2 licensed open source middleware.
