SQL vs NoSQL

“SQL or NoSQL?” is the database question every system design conversation trips over — and the honest answer is “it depends, and here’s exactly on what.” That’s not a dodge. The two families optimize for different things, and once you can name what each one trades away, the choice usually makes itself.

The short version: relational databases optimize for correctness and flexible querying; NoSQL stores optimize for a specific access pattern at scale. A default of “Postgres until it hurts” is right far more often than the internet suggests — but knowing why it might hurt is the actual skill.

The relational model

A relational database (Postgres, MySQL) stores data as tables of rows, with a fixed schema and relationships expressed by foreign keys. Its superpower is the join: you can ask questions that span entities without pre-deciding them. “Give me every order placed by users in California who signed up last month” is one query, even if you never planned for it.

What you get:

• ACID transactions — multiple writes commit atomically or not at all.
• Flexible querying — ad-hoc joins, aggregations, and filters.
• Strong constraints — uniqueness, foreign keys, and types enforced by the DB.
• A schema — a contract that catches a whole class of bugs at write time.

What you pay: a single primary node is the source of truth, so scaling writes horizontally is hard (you can read-replicate freely, but sharding a relational DB is real work), and the rigid schema means migrations to change shape.

The NoSQL families

“NoSQL” isn’t one thing — it’s an umbrella over several models, each built for a different access pattern. We dig into each next lesson, but the shapes:

The common thread: NoSQL stores denormalize — you shape the data to match how you’ll read it, often duplicating fields so a request is one lookup with no joins. That’s what lets many of them shard and scale writes across a fleet where a relational primary would choke.

ACID vs horizontal scale: the real tradeoff

The deepest fault line isn’t “tables vs JSON” — it’s strong consistency on one node vs scaling writes across many. Relational databases were built when the correct answer mattered more than the throughput; classic NoSQL stores were born at companies (Amazon, Google, Facebook) whose write volume had already outgrown any single machine, so they relaxed consistency to get partition tolerance and scale. That’s the CAP tradeoff, which gets its own lesson later.

How to actually decide

Forget the labels and ask about the workload:

A concrete reflex: a payment ledger goes in Postgres (you cannot lose or double-count money — that demands transactions). A session store or a feed cache goes in a key-value store (fast lookup by id, loss-tolerant). Most real systems use both — relational for the system of record, NoSQL for the hot paths it can’t serve cheaply. That’s polyglot persistence, and it’s the norm at scale, not a compromise.

The JavaScript angle

In Node you talk to both families through similar-looking clients, which makes it tempting to treat the choice as interchangeable. It isn’t — the same logical write has very different guarantees underneath:

// Postgres (via `pg`): two writes, atomic. Both land or neither does.
async function transfer(client, from, to, amount) {
  try {
    await client.query('BEGIN');
    await client.query('UPDATE accounts SET balance = balance - $1 WHERE id = $2', [amount, from]);
    await client.query('UPDATE accounts SET balance = balance + $1 WHERE id = $2', [amount, to]);
    await client.query('COMMIT');           // both succeed together
  } catch (err) {
    await client.query('ROLLBACK');         // or the DB undoes everything
    throw err;
  }
}

// A plain key-value store has no multi-key transaction. If the process
// dies between these two writes, money silently vanishes:
async function transferKV(kv, from, to, amount) {
  await kv.decrby(`balance:${from}`, amount);
  // 💥 crash here = `from` debited, `to` never credited. No rollback.
  await kv.incrby(`balance:${to}`, amount);
}

The lesson: the database you pick decides which invariants the store enforces for you versus which ones your application code has to guarantee by hand. For money, you want the store doing it.

With the families named, let’s look at the shapes themselves — the five data models and the access patterns each was built to serve.