Why not just use files or a spreadsheet

"Why not just use a file?" is a reasonable question and the honest answer is: for some things, do. This lesson is where the line is, and what specifically breaks when you cross it — because the failures are concrete rather than theoretical.

Start with the file

You are building a small library system. Members, books, loans. The obvious first version:

[
  { "id": 1, "title": "Malgudi Days", "borrowed_by": "Asha", "due": "2026-10-14" },
  { "id": 2, "title": "Train to Pakistan", "borrowed_by": null, "due": null }
]

Read the file, parse it, change it, write it back. It works. For a personal script with one user and a few hundred rows, it will keep working, and reaching for PostgreSQL would be overkill.

Then it stops working, in five specific ways.

1. Two writers, and one of them vanishes

Two librarians check out books at the same moment.

10:00:00.000  Asha's session reads the file    [book 1 free, book 2 free]
10:00:00.010  Ravi's session reads the file    [book 1 free, book 2 free]
10:00:00.100  Asha's session writes            [book 1 LOANED, book 2 free]
10:00:00.150  Ravi's session writes            [book 1 free, book 2 LOANED]

Asha's loan is gone. Not corrupted, not errored — silently overwritten, because Ravi's session wrote the whole file from data it had read before Asha's change existed.

This is the lost update, and it is the single most important reason databases exist. You will meet it again in module 6, demonstrated against a real PostgreSQL server, where you will see it happen even inside a transaction at the default isolation level — and then learn the one-line fix.

You can defend against it with a lock file. Then you have to handle the process that dies holding the lock, and the lock that is stale, and the fact that only one person can write at a time no matter how unrelated their changes are. You are now writing a database, badly.

2. The power cuts halfway through the write

with open("library.json", "w") as f:
    json.dump(data, f)          # the process dies here

open(..., "w") truncates the file to zero length before writing a byte. If the process dies mid-write you do not have the old data and you do not have the new data. You have half a JSON document, which will not parse.

Your entire library is gone, and the backup is from last night.

The fix is to write to a temporary file, flush, fsync, and atomically rename over the original. That is genuinely the correct technique and you should know it for config files — but notice that you are now implementing durability by hand, for one file, and you still cannot make two files change together atomically.

A database gives you this by default, for everything, and extends it across many tables at once. That is what a transaction is.

3. Finding one row means reading all of them

for book in json.load(open("library.json")):
    if book["title"] == "Malgudi Days":
        return book

Two hundred books: instant. Two hundred thousand: you are parsing several megabytes of JSON to find one row, on every request.

A database keeps an index — a structure built for finding rather than storing — and goes to the row in a handful of disk reads however large the table gets. Module 7 explains what one actually is, because "add an index" without knowing what it is produces the wrong index.

And you cannot load a 50 GB file into memory at all. A database never loads the whole table; it reads the pages it needs.

4. Nothing stops bad data

{ "id": 1, "title": "Malgudi Days", "borrowed_by": "Asha" }
{ "id": 1, "title": "Malgudi Days", "borrowed_by": "Ravi" }
{ "id": 2, "title": 47, "due": "next tuesday" }
{ "id": 3, "borrowed_by": "Priya" }

Duplicate ids. A number where a title should be. A date nothing can parse. A loan to a member who does not exist. A book with no title at all.

Every one of those is valid JSON. The file will accept anything, and the checking has to live in your code — in every place that writes, forever, including the script somebody wrote at 2am to fix something.

A database refuses all five at the door:

CREATE TABLE books (
  id          bigint PRIMARY KEY,
  title       text NOT NULL,
  published   date,
  borrower_id bigint REFERENCES members(id)
);

PRIMARY KEY refuses the duplicate. NOT NULL refuses the missing title. The date type refuses "next tuesday". REFERENCES refuses the loan to a member who does not exist. Four words, enforced no matter which program, script or person is writing.

This is the difference people underestimate most. Validation in application code is enforced by the code that remembered to call it. Validation in the schema is enforced by the database, against everybody, including you at 2am.

5. The question you did not plan for

"Which books have been borrowed more than five times, but never by anyone who joined this year?"

In files, that is a program: load two files, build a lookup, count, filter, join. Twenty minutes and a real chance of an off-by-one.

SELECT b.title, COUNT(*) AS borrowings
FROM loans l
JOIN books b ON b.id = l.book_id
GROUP BY b.id, b.title
HAVING COUNT(*) > 5
   AND NOT EXISTS (
     SELECT 1 FROM loans l2
     JOIN members m ON m.id = l2.member_id
     WHERE l2.book_id = b.id AND m.joined >= DATE '2026-01-01'
   );

One statement. You will be able to write that by the end of module 5.

Note GROUP BY b.id, b.title rather than GROUP BY b.title alone. The subquery refers to b.id, and a column can only be referred to after grouping if it is in the grouping — otherwise PostgreSQL says subquery uses ungrouped column "b.id" from outer query. Module 3 explains why, and it is the single most common GROUP BY error there is.

A file is organised for the questions you thought of. A database answers the ones you did not.

When a file is genuinely right

Being honest about this matters, or the advice becomes superstition.

Configuration. A YAML or JSON config file is correct. It is small, read at startup, edited by a human, and version-controlled.

A single-user script. Scraping something and writing a CSV is fine. Do not add a database to a thing that runs once a week on your laptop.

Logs. Append-only text, rotated. Databases are a poor fit and specialised tools exist.

Large binary content. Images, video, PDFs belong in object storage — S3 or similar — with the path in the database. Storing a 40 MB video in a row bloats backups, blows the cache and slows everything unrelated.

Data that is genuinely one shape, read whole, by one process. A lookup table of pincodes loaded into memory at boot is fine as a file.

And SQLite is the middle ground

import sqlite3
con = sqlite3.connect("library.db")

SQLite is a real relational database with transactions, constraints, indexes and SQL — in a single file, with no server and no installation. It is built into Python, and it is probably the most widely deployed database in the world because it is inside every phone and browser.

It genuinely solves problems 2 to 5. It handles problem 1 — concurrency — only up to a point: one writer at a time, though many readers.

So the honest ladder is: a file, then SQLite, then a server database. Reach for PostgreSQL when you have more than one process writing, more than one machine, or data you cannot afford to lose. For a desktop app, a mobile app or a small tool, SQLite may be the final answer rather than a stepping stone.

The decision, in one place

One process, small, read whole, human-edited?        a file
One process, needs queries and integrity?            SQLite
Several writers, or a network, or data that matters? a server database
Files, images, video?                                object storage + a path in the database

Check your work

The lost update. Two writers read, both write, the first change vanishes silently.

Why open(..., "w") is dangerous. It truncates before writing, so a crash leaves you with neither version.

The by-hand durability technique. Temporary file, flush, fsync, atomic rename — and it still cannot make two files change together.

Why finding a row in a file gets slow. You read all of them; a database has an index.

The five bad-data cases a schema refuses. Duplicate key, missing required value, wrong type, unparseable date, reference to a row that does not exist.

Why schema validation beats application validation. It is enforced against everybody, including a script written at 2am.

What a database answers that a file cannot. The question you did not plan for.

Five cases where a file is right. Config, single-user scripts, logs, large binaries, one-shape data read whole.

What SQLite solves and what it does not. Transactions, constraints, indexes and SQL — but one writer at a time.

The ladder. File, SQLite, server database — and object storage for binaries.

Practice

1. Write the library as a JSON file with a read-modify-write function.
2. Run two copies at once, both loading and saving. Reproduce the lost update.
3. Add a lock file. Then kill one process while it holds the lock and see what happens next.
4. Kill a process midway through json.dump. Try to load the file afterwards.
5. Implement the temp-file-and-rename technique and kill it midway again.
6. Generate 200,000 book records and time finding one by title.
7. Put the same data in SQLite with an index on title and time it again.
8. Try to insert a duplicate id into your JSON. Then into a table with a PRIMARY KEY.
9. Try to insert "next tuesday" into a date column and read the error.
10. Write the "borrowed more than five times" question as a Python program over two JSON files. Time yourself.
11. List three things in a project of yours that are files and should stay files.
12. Find out how much data SQLite can hold, and whether that surprises you.

Official documentation

• SQLite — Appropriate uses for SQLite — The clearest statement anywhere of when a file, when SQLite and when a server database. Written by the people with the least incentive to oversell it.
• PostgreSQL — Concurrency control — What the database does about the lost update, which module 6 covers properly.
• PostgreSQL — Reliability and the write-ahead log — How durability is actually achieved.
• SQLite — How SQLite is tested — Worth reading once, for a sense of what "this data must not be lost" costs to guarantee.

Next: the seven families, and the problem each one solves.