Where to keep the data

a file, SQLite or a real database

What it is

Four options, and the choice is nearly always between the second and the third:

The decision is taken on day one, in one line, and almost never revisited — because by the time it starts getting in the way, the whole project rests on it. So it is worth knowing not only how the options differ but exactly when the difference becomes noticeable.

What it looks like in code

A file versus SQLite: the numbers

The commonest scenario: "we'll just put it in JSON and redo it later". Keeping a list in a file means that on every write you read the whole file, append, and write the whole thing back.

with open("data.json", encoding="utf-8") as f:
    rows = json.load(f)
rows.append(record)
with open("data.json", "w", encoding="utf-8") as f:
    json.dump(rows, f)

Here is how long adding records takes — measured, on the same machine:

What to look at isn't the difference in the last row but how the columns grow. Doubling the number of records roughly doubles SQLite's time and roughly quadruples JSON's: every new record forces a re-read of everything already accumulated.

And the main thing: at five hundred records there is no difference — half a second either way. Which is exactly why the wrong decision is invisible at the moment it is taken. It becomes visible six months later, when there are eight thousand records and the program stops to think for a minute and a half.

Searching

finding one city among 50,000
  JSON (load the file and scan):  19.9 ms
  SQLite with an index         :   0.02 ms

A thousand times. The reason is simple: a file has to be read whole and parsed before anything can be found in it, while a database reads only the page it needs (article 7.6).

Two processes writing at once

This is the difference no optimization gets round. Two processes each add 300 records to one JSON file:

run 1: 300 of 600 written (A=300, B=0)
run 2: 300 of 600 written (A=300, B=0)
run 3: 300 of 600 written (A=300, B=0)

Half the data vanished in all three runs. And in each run one of the processes crashed:

json.decoder.JSONDecodeError: Expecting value: line 1 column 1 (char 0)

That is reading the file at exactly the moment the other process had emptied it but hadn't yet written the new contents.

The same test with SQLite:

SQLite — expected 600, written 600 (A=300, B=300), lost 0

A database can do what a file fundamentally cannot: put simultaneous writes in a queue. That is the main reason databases exist — not speed, but the fact that data isn't lost.

When a file is enough

A file is excellent storage when one process writes, and rarely:

• settings a human edits by hand;
• a cache of an external API's responses;
• exports and data exchange;
• a reference list updated once a month;
• a small bot only you use.

The sign that a file will do: you can open it in a text editor and that is useful.

When SQLite stops being enough

SQLite is not a toy database. It is a full SQL database that lasts a very long time, and for one application on one server it is most often the right answer. Moving to a server database (PostgreSQL, MySQL) makes sense once at least one of these appears:

• several machines that need the same data;
• many simultaneous writers (SQLite admits one writer at a time);
• access rights — different applications with different access to different tables;
• a volume at which you need tuning, scheduled backups, replication.

The other side: a server database is another program to install, configure, update and back up. For a bot used by ten people that is work with no payoff.

The move people make too late

The most expensive part of moving from a file to a database isn't the database but the fact that the code talks to the storage from ten places. If reading and writing are gathered into one file from the start — storage.py, repository.py, whatever — the move takes an evening, because one implementation changes and not the whole project.

That is perhaps the most useful architectural habit in the whole chapter: all data access through one door.

What to tell the AI

By default an AI proposes what appeared most in its training data — usually PostgreSQL and an ORM. For a two-hundred-line bot that is surplus. So name the choice yourself and ask the AI for the justification:

Project: <description>. I expect <how many> records.
Writing is done by <one process / several>.
The data is needed by <just me / several users>.
Which of these is enough: a file, SQLite or a database
server? Name the sign by which the chosen one will stop
being enough.

That last line is the valuable one: it turns the choice into a checkable condition.

Put all data access through one storage.py module:
the functions add_city, get_city, list_cities, delete_city.
SQLite inside. The rest of the code mustn't know about the
database.

The data is currently in a JSON file and access is smeared
across the project. Find every place where that file is read
or written. A list with line numbers. Don't change the code
yet.

Move the storage from JSON to SQLite.
Don't change storage.py's external functions or their
signatures. Separately, write a migration script for the
data already accumulated, and say what to do if it fails
halfway.

What to look for

A JSON file as the main storage for a server or a bot. That is data loss, not slow operation.

Data access from ten places. Moving to a database turns into rewriting the project.

PostgreSQL for a single-user project. Configuration, backups and updates for a load that doesn't exist.

No check that the write succeeded. With a file, a write error often goes unnoticed.

A data migration with no backup. A migration script is the prime candidate for failing halfway.

Passwords and keys in the same database in plain text. The storage was chosen and the question of what is in it was never asked (article 5.7).

No answer to "what happens if the file is opened twice". Worth asking before the answer arrives as missing data.

Cheat sheet