8. SQL vs NoSQL
1
The picture to keep in your head2
8.1 What are tables, rows and keys?3
8.2 What does ACID mean?4
8.3 What is normalisation, and what are joins?5
8.4 What kinds of NoSQL databases are there?6
8.5 What's the difference between schema-on-write and schema-on-read?7
8.6 Why does NoSQL often duplicate data and avoid joins?8
8.7 How do you choose between SQL and NoSQL?9
Memory card and where the analogy breaks10
Practice: five levels11
Interview questions12
Solutions13
Sources14
Code lab8. SQL vs NoSQL
8.5 What's the difference between schema-on-write and schema-on-read?
3 min read+10 XP
In the office: The ledger checks at the door: if a line doesn't fit the printed columns, you can't write it. The folders check later: you can drop any paper in, and whoever opens the folder has to make sense of it.
In the system:
- Schema-on-write (relational): the database enforces the structure when data is written. Every row has the declared columns and types; wrong data is rejected. Changing the structure means a migration (
ALTER TABLE), which needs care on big tables. - Schema-on-read (most document stores): the database accepts documents of any shape. The application interprets the structure when it reads, and must cope with old and new shapes side by side.
| Schema-on-write | Schema-on-read | |
|---|---|---|
| Bad data | Rejected at the door | Gets in; found later, by the app |
| Changing the shape | Planned migration | Just start writing the new shape |
| Who enforces rules | The database | Your code |
"Schemaless" is a myth: there's always a schema. The only question is whether the database checks it or your code does. Many document stores can also validate documents against a schema if you ask.
Memory code: "Check at the door, or check when you open the folder."