Databases
SQL or NoSQL? How I Think About the Choice
The question isn't which database is better. It's whether I know my access patterns up front, and whether the data has relationships I need the database itself to protect.
For a long time I thought this was a question about scale: SQL for normal apps, NoSQL once you get big. That's not really it. The two projects where I actually made this choice deliberately taught me it's about something else entirely.
The question I actually ask first
Do I know how this data will be read before I design it?
That sounds backwards, since in SQL you design the schema around the data and then query it however you need later. That flexibility is the whole point of a relational database, and it's why it's the right default when I don't yet know every way the data will be used.
NoSQL mostly inverts that. You design around the queries you're going to run, and the shape of the storage follows from the access pattern.
Where that made the decision for me
The URL shortener I built is the clearest example. It only ever does one thing: given a short code, return the original URL. One lookup, by one key, forever. No joins, no reporting queries, no "show me all URLs created last Tuesday grouped by domain."
That's an access pattern I knew completely before writing a line of code, which is exactly the case where Cassandra makes sense. It's built for keyed lookups and spreads data across nodes as it grows.
The payment orchestrator went the other way, on SQL, and the reason wasn't scale either. A payment has attempts, and fraud flags, and those relationships need to be enforced. An attempt that references a payment that doesn't exist is corrupt data, and I want the database to reject it rather than trusting my code to never make that mistake.
Relationships and transactions are the real dividing line
That payment example is the general version. When the correctness of my data depends on several rows changing together or not at all, a relational database gives me that guarantee directly.
The money-transfer case is the obvious one, where debiting one account and crediting another have to both succeed or both fail. But it's more common than that. Creating an order and its line items, updating a payment and recording the attempt that changed it. Anywhere "half of this happened" is an unacceptable state, transactions are doing real work for me.
Foreign keys are the same idea applied to relationships. The database refuses to let me create an orphan, so that entire class of bug never reaches production regardless of what my application code does.
Where I'd genuinely reach for NoSQL
A few cases where I think it's clearly right, beyond the keyed-lookup one:
Data with no fixed shape, where different records legitimately have different fields and forcing them into columns means either a very wide table full of nulls or a pile of nearly-empty joined tables.
Very high write volume where the writes are independent of each other, like event logs or telemetry, where nothing needs to be consistent with anything else at the moment it's written.
Caching, which is really a separate thing but sits in the same family. Redis in front of the URL shortener isn't a database choice, it's a layer holding hot data that the actual database also has.
What I'd tell myself a year ago
Default to SQL. Not because it's better, but because it's the one that doesn't require you to be right about your access patterns in advance, and as a junior developer on a new project I'm usually not.
Reach for NoSQL when you can name the specific reason. "It scales better" isn't a reason, since a relational database on a normal server handles far more traffic than most projects ever see. "This is one lookup by one key and I need it spread across nodes" is a reason. "These records genuinely don't share a shape" is a reason.
The mistake I'd have made without building both is assuming the choice is about size. It's about whether I know the access patterns up front, and whether the data has relationships I want the database itself to protect.