Of pertinent choices, long hauls, and short falls

Today's dose of life saving refreshes for me comes as always from the SQL land.

At crisp, the authentication flows were based on user tokens delivered through authorisation headers. The token itself used to be computed at the application layer. When I started to rewrite an older project from scratch using MySQL for the database [and my data layer], was looking to see if this could be offloaded to the database layer.

One reason why we couldn't work with this earlier was Mongo not providing a way. Mongo's $setonInsert is hacky for the most part.

For some context, the token used to computed based on a few fields from the user data and environment to salt. Wanted to see if this could be pushed off to the database itself and the ever dependable layer hasn't failed. MySQL on the other hand, allows a generated field as a solution for this. And, having it as a one time work during create makes it all the more easy.

There is just one caveat however - with timestamp fields, current_timestamp doesn't work and works only with fields. But, for this particular case, there might be a created/updated field as a datetime type so works well without concerns.

There has also been a bit of change in the last couple of months (in particular), thanks to all the #devx changes I've gone through. One of the reasons why I kept away from an RDBMS and chose Mongo was the flexibility/schemaless* flows which takes strong migration concerns out of the picture. Why? Well, both places where I depended much on Mongo started as a single person back-end dev - so bandwidth.

Guess, in retrospect, probably the hard choices might have actually been equally good in terms of getting out of comfort zones and exploring marvels like these. The months since January, I've worked a good chunk on MySQL and revisited stuff I worked last about a decade back. And after building the data layer library, I guess I might choose to work with MySQL [or pgSQL depending on the JSON support benchmarks] for good and migrate Mongo to very specific use cases going forward.

mysql #mongodb #devx