Why I Kissed NoSQL Goodbye and Went Back to SQL for Primary DBs

Damn, the early 2010s, a simpler time when I was deeply immersed in PHP and wrestling with PDO to manually craft SQL queries. It was an era marked by the exhilaration of building and the constant dread of database migrations. Every time we decided to tweak our data structure, we’d embark on a migration adventure, often disabling foreign key constraints to move things around, only to re-enable them and hope everything still worked. It felt like trying to change the tires on a moving car. Unwieldy? Absolutely. Heavy? Like lifting a database with your bare hands.

The MongoDB Mirage

And then, like a mirage in the vast desert of database management, MongoDB appeared. Its promise was as seductive as a siren’s song: “Store whatever you want, whenever you want.” And oh, did we fall for it. Hard. But as we abandoned our structured approach to ERD, Schema Design, and Data Modelling, we found ourselves in a new kind of chaos. We traded foreign key constraints for the wild west of embedded documents and with that, ushered in a new era of codebase anarchy. Validation logic ballooned, indexes crawled at the speed of a snail racing uphill, and MongoDB’s aggregation features, despite their best efforts, could never hold a candle to the good old JOIN statement. Yes, I’ve penned 100-line JOINs back in the day. They weren’t pretty, but they made sense, in a bizarre, twisted way.

Honorable Mentions: ArangoDB and Cloud Firestore

It wasn’t all doom and gloom, though. ArangoDB and Cloud Firestore came close to being the heroes we needed. ArangoDB’s graph model felt like a breath of fresh air, and Firestore’s sub-collections were a neat trick. But then reality hit. ArangoDB demanded we learn a whole new DSL, turning data modeling into a brain teaser. And Firestore? Let’s just say its security rules were as easy to navigate as a maze… blindfolded. I still use Cloud Firestore for certain projects, but let’s just say it’s with a healthy dose of caution.

A Revelation at Amazon

My stint at Amazon was a wake-up call. Data modeling there was akin to a sacred ritual, scrutinized by an array of teams, from legal to privacy. That’s when it hit me: the issue wasn’t SQL vs. NoSQL, but our lackadaisical approach to Schema Design and Data Modelling. They weren’t just items on a checklist; they were the foundation of everything.

A Method to the Madness

Since then, I’ve approached service design with a newfound reverence for planning. Before a single line of code is written, I map out the problem, jot down assumptions, and draft database schemas. It’s a dance between application design and database architecture, revealing the intricate ways they’re intertwined. This isn’t just an exercise in theory; it’s a practical look at the very fabric of our systems.

A Cautionary Tale of ACID Compliance

Here’s a fun story: once, due to the lack of ACID compliance, an API designed to verify bank accounts by depositing tiny amounts was exploited. Thanks to eventual consistency, our system became an ATM for a savvy attacker, doling out funds faster than a slot machine on a winning streak. Despite safeguards, the delay in reflecting transactions turned into a costly lesson.

Back to SQL: A Leap of Faith

Armed with these insights, I took a deep breath and dove back into the world of SQL databases. Yes, the specters of SQL Injections and migration headaches were waiting, but the allure of transactional integrity was too strong. Recent benchmarks showing Postgres outpacing MongoDB in transaction processing were the final push I needed.

A Balanced Ecosystem

Today, I live in a hybrid world where SQL and NoSQL databases coexist in harmony. SQL reigns supreme for core data like users and transactions, ensuring integrity and reliability. NoSQL, on the other hand, takes on the role of handling metrics, metadata, and the unstructured chaos where strict consistency isn’t paramount. It’s a system that works, even if migrations still give me nightmares.

In conclusion, my journey through the database landscape has been akin to a rollercoaster ride through the tech equivalent of Wonderland. It’s been bizarre, enlightening, and, at times, downright frustrating. But as I sit here, typing this out, I can’t help but smile at the journey. SQL, with all its quirks, feels like coming home - albeit a home that’s constantly under renovation, but home nevertheless!