Skip to main content

Autogenerate Primary Key with TSID

info

Feature is only applicable for relational databases. It is not available for MongoDB.

DB2Rest supports a way to automatically create chronological sequences of the written data out of the box. It is made possible by generating Time-Sorted Unique Identifiers (TSID) for primary key columns during insert operation. This does not require any native database support.

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. This also makes data migration much easier.

Structure of TSIDs​

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.

note

When stored as a Long, TSIDs look like this:

38352658567418872

note

And when written as Text or a String they look like this:

01226N0640J7Q

Auto Generating TSIDs - Varchar Column​

DB2Rest can automatically generate TSID primary keys as it inserts data through its instant API into the 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 department table and let DB2Rest worry about automatically creating our tables' primary keys for the row inserted.

PostgreSQL

CREATE TABLE department (
	id varchar NOT NULL,
	"name" varchar NOT NULL,
	CONSTRAINT department_pk PRIMARY KEY (id)
);
info

The table must have column with primary key constraint.

HTTP Request​


curl --request POST \
--url 'http://localhost:8080/v1/rdbms/pgdb/department?tsIdEnabled=true' \
--header 'Content-Type: application/json' \
--header 'User-Agent: insomnia/9.2.0' \
--data '{

 "name" : "Engineering"

}'


HTTP Response​

{
	"row": 1,
	"keys": {
		"id": "0G8VG94AJ7NXV"
	}
}

info

The tsIdEnabled request parameter must be set to true.

Auto Generating TSIDs - Integer Column​

In case the primary key column was from int type family, DB2Rest will generate integer type TSID. DB2Rest is smart and knows about the type of the primary key column.

PostgreSQL

CREATE TABLE college (
	id int8 NOT NULL,
	"name" varchar NOT NULL,
	CONSTRAINT college_pk PRIMARY KEY (id)
);

HTTP Request​


curl --request POST \
--url 'http://localhost:8080/v1/rdbms/pgdb/college?tsIdEnabled=true' \
--header 'Content-Type: application/json' \
--header 'User-Agent: insomnia/9.2.0' \
--data '{

"name" : "ABC College"

}'


HTTP Response​

{
	"row": 1,
	"keys": {
		"id": 586449754280387258
	}
}

Auto Generating TSIDs for Bulk Insert​

TSID can be generated for bulk insert as well.

HTTP Request​


curl --request POST \
--url 'http://localhost:8080/v1/rdbms/pgdb/department/bulk?tsIdEnabled=true' \
--header 'Content-Type: application/json' \
--header 'User-Agent: insomnia/9.2.0' \
--data '[
{

    "name" : "Finance"

},
{

    "name" : "Marketing"
}

]'

HTTP Response​

{
	"rows": [
		1,
		1
	],
	"keys": [
		{
			"id": "0G8VZ6RF7YV1B"
		},
		{
			"id": "0G8VZ6RFBYR1N"
		}
	]
}

DB2Rest detected the data type of our existing id column and determined to use the String format for automatic TSID generation when writing the 2 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!