Laravel Spark , 64-bit row IDs and modern/distributed databases ... a no-go

Got excited here and bought Laravel Spark (SaaS app boilerplate) and come to find out that in fact if one wants to use a distributed database (cockroachdb in our case) … one cannot really use Spark.

This is more of a database issue and Javascript shortcoming rather than purely a Spark issue. This being said one would hope that future versions of the software will account for this.

Deep dive …

Modern databases use various methods for generating unique row IDs. In our case CockroachDB is using the UUID data type with the gen_random_uuid() function as the default value, which generates 128-bit values or a smaller SERIAL 64-bit using the unique_rowid() function.

This means that by using the migrations provided with Laravel Spark we end up with a 64-bit SERIAL (read 64-bit INT) for our unique row IDs.

The generated SQL snippet from say the users table for the “id” field looks like this :

id INT NOT NULL DEFAULT unique_rowid(),

where “INT” is 64-bit

Since I am assuming that Laravel Spark was designed with MySQL in mind where the data type INT is 32-bit this poses the following problem.

When we create a new user, the record that is being created has a 64-bit unique identifier. When we try to use the app all the Javascript functionality fails because when Javascript tries to evaluate the JSON it receives with the 64-bit int the eval gets mangled.

Take for example the following user record as coming from PHP/Laravel/Spark
[

{
“id”:316466291839008769,
“name”:“testbot”,
“email”:"testbot@mailinator.com",
“photo_url”:“https://www.gravatar.com/avatar/5e644ca1d422bcaa947f93832d1e1cd2.jpg?s=200&d=mm”,
“uses_two_factor_auth”:false,
“two_factor_reset_code”:null,
“current_team_id”:316466291903037441,
“stripe_id”:null,
“current_billing_plan”:null,
“billing_state”:null,
“vat_id”:null,
“trial_ends_at”:“2018-01-22 19:10:47”,
“last_read_announcements_at”:“2018-01-22 19:10:47”,
“created_at”:“2018-01-22 19:10:47”,
“updated_at”:“2018-01-22 19:10:47”,
“subscriptions”:[
],
“tax_rate”:0
}

]

If we take this record and plug it in a JSON parser such as http://json.parser.online.fr/ we will note that the evaluated JS object is

[

{
“id”:316466291839008800,
“name”:“testbot”,
“email”:"testbot@mailinator.com",
“photo_url”:“https://www.gravatar.com/avatar/5e644ca1d422bcaa947f93832d1e1cd2.jpg?s=200&d=mm”,
“uses_two_factor_auth”:false,
“two_factor_reset_code”:null,
“current_team_id”:316466291903037440,
“stripe_id”:null,
“current_billing_plan”:null,
“billing_state”:null,
“vat_id”:null,
“trial_ends_at”:“2018-01-22 19:10:47”,
“last_read_announcements_at”:“2018-01-22 19:10:47”,
“created_at”:“2018-01-22 19:10:47”,
“updated_at”:“2018-01-22 19:10:47”,
“subscriptions”:{
},
“tax_rate”:0
}

]

This is very much similar if not identical processing that JS/Vue goes through in the Spark app. Note the mangled ID.

In conclusion a modern database yields 128-bit or 64-bit row ids, JS fails to read these properly and hence makes the app unusable.

I have tried to create some custom migrations to see if we can reduce the size of the id to 32 bit but it seems that we cannot successfully do that in CockroachDB because the unique_rowid() function always generates a 64-bit number.

Looking forward, it seems best if Spark would account for this by leveraging 128-bit UUIDs as it seems that there are libs out there to generate and operate on these 128-bit UUIDs both for PHP as well as JS.

If anyone has any other suggestions I may be able to try to create a table with an id INT4 (32-bit) with autoincrement I would very much appreciate it.

migrane

Hi migrane,

Handling 64bit integers can indeed present a challenge in javascript. I believe the most common solution is to serialize them to JSON as strings; this works especially well in the case where they are just being used as IDs. If you have control over the application that is generating the JSON, this might be the best solution.

If you need to solve this from CockroachDB, in 2.0 we will be introducing the Sequences feature; a sequence will allow you to create numbers that ordered, and you could set a maximum value of 2^53.

CREATE SEQUENCE my_seq MAXVALUE 9007199254740992;

CREATE TABLE myTable (
  id INT PRIMARY KEY DEFAULT nextval('my_seq')
);

While still represented by an int64, the values would all be less than 2^53 and thus could be accurately represented in javascript without rounding.

Without sequences, you would need to maintain your own incrementing ID. This can be done by selecting max(id)+1 for each insert, but obviously this has a significant performance impact.