Choosing a database

Tables, documents and key-value stores.

Every non-trivial app has to remember things after the server restarts, so every design includes a database. The choice of kind matters because it shapes how you ask questions of the data and what guarantees you get. There are many products, but most fall into a few families, each good at a different job.

A relational database (such as PostgreSQL or MySQL) stores data in tables of rows and columns, with relationships between them. You query with SQL, can join tables, and get transactions: a group of changes that either all happen or none do. When an order is placed, the order row, the payment record and the stock decrease happen together or not at all. For most apps this is the right default.

A document database stores each record as a flexible JSON-like document. It suits data that is naturally a nested bundle, read whole, and whose shape varies: a restaurant menu with dishes, options and photos can live in one document. Reads that fetch one bundle are fast and simple; queries that cut across many documents, or relate them, are weaker.

A key-value store is the simplest: you put a value under a key and get it back by that key, very fast. It is excellent for sessions, caches and counters. But you cannot ask "find all orders over 500" without scanning, because it only knows keys.

-- relational: ask a question across tables
SELECT o.id, c.name, o.total
FROM orders o JOIN customers c ON c.id = o.customer_id
WHERE o.total > 500
ORDER BY o.created_at DESC
LIMIT 20;