Instant API for databases adds automatic TSID primary keys
Tags: nocode, restapi, database, opensource
Published: 2024-05-05 06:49:47

In a [previous post](https://dev.to/thadguidry/expose-your-database-as-a-rest-api-safely-and-without-code-5242), we saw how we can read and write data through an **instant API** provided by [DB2Rest](https://db2rest.com).  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](https://db2rest.com) has a built-in ability to automatically create Time-Sorted Unique Identifiers ([TSID](https://www.wikidata.org/wiki/Q125756633)) 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](https://en.wikipedia.org/wiki/Snowflake_ID) 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](https://www.wikidata.org/wiki/Q26907166) 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.

{% details 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.
{% enddetails %} 

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](https://db2rest.com) 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
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 our `actor` table with id's `0012` and `0036` with famous actor names?

[DB2Rest](https://db2rest.com) 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:
{% embed https://dev.to/thadguidry/expose-your-database-as-a-rest-api-safely-and-without-code-5242 %}

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](https://db2rest.com) , an APACHE 2 licensed open source middleware.