Why Programs Need Databases

Where files stop being enough

Advanced 8 min

In this lesson

Unit 7 ended on an awkward note: your script fetched a pile of data, printed it, and exited, taking the data with it. Writing it to a file is the obvious fix, and for a while it is the right one. This lesson is about the point where it stops being right, and what a database gives you instead.

Explain it like I’m 5

A text file is a shoebox of receipts. A database is the same receipts in a filing cabinet with labeled drawers you can actually search.

Files are fine, until they aren’t

Do not let anyone talk you out of files. A CSV or JSON file is simple, portable, readable by humans, and needs nothing installed. For configuration, exports, and anything you write once and read whole, a file is the better answer and reaching for a database is overkill.

Files fall down on three specific things:

  • Searching. Finding matching records means reading the entire file and checking each one yourself, every time.
  • Changing one record. There is no way to edit line 4,000 in place. You read the whole file, change the item, and write the whole file back.
  • Two things writing at once. Two processes writing the same file will interleave and corrupt it. Nothing stops them.

A database solves all three, because it was built for exactly these problems.

Tables, rows, and columns

A table is a named grid, and you already understand it because it is a spreadsheet. Each column has a name and a type; each row is one record. A database holds as many tables as you need.

The one genuinely new idea is that the structure is declared up front. You tell the database that books has a text title and an integer year, and from then on it enforces that. A CSV will happily let you put the word ‘banana’ in the year column.

Example 
books
┌────┬───────────────────┬──────┐
│ id │ title             │ year │
├────┼───────────────────┼──────┤
│  1 │ Dune              │ 1965 │
│  2 │ Neuromancer       │ 1984 │
│  3 │ Snow Crash        │ 1992 │
└────┴───────────────────┴──────┘
One table: three columns, three rows.

SQL is a language you send, not a program you install

SQL is how you talk to the database: a short language for describing what you want rather than how to fetch it. You write SELECT title FROM books WHERE year > 1980 and the database works out how to do it.

SQL is not a separate application. It is text your Python program sends over a connection, exactly like the JSON you sent to an API in Unit 7. Python builds the string, the database runs it and hands back rows.

Choosing, in practice

Most decisions come down to size and whether you need to search. A few hundred records you always read in full is a file. Tens of thousands, or anything you query by condition, is a database.

Finish choose_storage. Return "database" when there are more than 1000 records or the data needs searching, and "file" otherwise.

def choose_storage(scenario):
    return "file"  # TODO: return "database" for big or searchable data

scenarios = [
    {"name": "App settings", "records": 5, "needs_search": False},
    {"name": "Customer orders", "records": 50000, "needs_search": True},
    {"name": "One-off export", "records": 200, "needs_search": False},
    {"name": "Site search index", "records": 800, "needs_search": True},
]

for scenario in scenarios:
    print(f"{scenario['name']}: {choose_storage(scenario)}")

Common mistake: Assuming every database needs a server installed

Why it happens:

PostgreSQL and MySQL are the famous names, and both are servers, so that becomes the mental model.

How to fix it:

SQLite is a file plus a library that already ships with Python. import sqlite3 is the entire installation, which makes it perfect for scripts and small apps.

Common mistake: Using a database for five configuration values

Why it happens:

Databases feel like the more professional choice.

How to fix it:

Five settings belong in a config file or environment variables. A database earns its place when you need to search, update single records, or handle more data than fits comfortably in memory.

Common mistake: Thinking SQL is a separate program to learn and run

Why it happens:

It is a different language with different syntax, so it feels like a different tool.

How to fix it:

SQL is text your Python code sends to the database, just like a request body sent to an API. You will write it inside ordinary Python strings.

Which of these is a genuinely good reason to move from a file to a database?

What does SQLite need installed before you can use it from Python?

What is a primary key for?

Mini exercise (easy)

Write storage_advice(records, needs_search, concurrent_writers) returning a (choice, reason) tuple. Any of: more than 1000 records, needing search, or more than one writer, means "database", each with its own reason. Otherwise "file".

Your turn. Fill in the code below and press Run to test it right here, nothing to install.

def storage_advice(records, needs_search, concurrent_writers):
    return ("file", "small and simple")  # TODO: check the three database reasons first

print(storage_advice(20, False, 1))
print(storage_advice(90000, False, 1))
print(storage_advice(50, True, 1))
print(storage_advice(50, False, 3))

What to learn next

You now know where files stop being enough: searching without reading everything, changing one record in place, and more than one writer at a time. You met tables, rows, columns, and primary keys, and you know SQLite is a single file plus a library that already ships with Python.

Time to use one for real. SQLite with Python creates a table, fills it, and queries it back, all runnable right on the page.