COMPARISON

SQL versus NoSQL

SQL databases store related rows with constraints. NoSQL is a loose label for documents, key-value stores and others. Choose based on access patterns and the invariants you need, not on which word sounds newer.

Short answer: Use SQL when you need relationships and constraints (Postgres, MySQL). Use a document store (MongoDB) or key-value store (Redis) when that shape matches the access. NoSQL is not one product.

What it is

SQL here means a relational database: tables, SQL, transactions and constraints, such as PostgreSQL or MySQL. NoSQL is not a single technology. It was a slogan for systems that rejected that model: document databases, key-value stores, wide-column stores, search engines. Lumping them together hides the choice. This page separates "I need constraints and joins" from "I need a specific non-relational shape".

Why and when it is used

Choose relational SQL when entities refer to each other and incorrect references are bugs: accounts, orders, permissions, inventory. Choose documents when you load and store a self-contained blob with fields that vary. Choose a key-value store for cache and counters, not for the ledger. Choose a search engine for full-text ranking. Choose a vector index for similarity, beside a system of record, not instead of one.

Most applications should start relational. Add another store when an access pattern hurts and you can name it. Starting with three NoSQL products and no invariants is how data drifts.

How it works

In a relational design you normalise: a user row, an order row with a user id, a foreign key so the order cannot point at nobody. Queries join. Transactions keep a transfer consistent. In a document design you embed the parts you always load together, and you accept duplication if the same fact appears in two documents. Updates must fix every copy. In a key-value design you invent the key and you do not get a rich query language; if you need one, you picked the wrong tool.

"Schemaless" still has a schema. It lives in the application, inconsistently, unless you enforce it. Relational schemas live in the database, which is stricter and sometimes less convenient.

Technologies and dependencies

One primary database your team can operate, backups, and migrations. ORMs sit on relational engines and some document engines. SQL versus NoSQL is a decision you write down. Databases for AI Applications discusses this in the AI-app setting. The extra stores (Redis, a vector index) are dependencies you add later with a reason.

How to get started

  1. Write the three most important operations (create X, list Y by user, update Z safely).
  2. Sketch a relational schema and the SQL. Note any operation that is awkward.
  3. If an operation is awkward only because you have not learned JOIN, learn JOIN. That is not a NoSQL requirement.
  4. If an operation is awkward because the payload is a large varying document, sketch that one collection as documents and keep the rest relational if you can.
  5. Pick one primary store for the first release.
  6. Revisit when you have measurements, not when a new teammate prefers a different logo.

Draw the invariants ("an order total equals its lines") and where they are enforced. If the answer is "the application remembers", the application will forget.

Cautions and trade-offs

NoSQL is not faster by definition. A missing index is slow everywhere. Relational is not always consistent with your needs if you turn the isolation down and then assume banking semantics. Multi-store architectures fail in the gap: the document updated, the search index did not, the cache is old. Each extra store needs a sync story.

Vendors will sell a single platform that claims to be all models. Convenience is real and so is lock-in. Prefer a boring primary database. And ignore architecture arguments that treat SQL as legacy. The relational model is still the best fit for a large fraction of business data, which is why the NoSQL products keep adding transactions and SQL-like queries.