Files and databases: relational and non-relational storage, queries and transactions

Why programs need somewhere to keep data

A running program keeps its working data in memory (RAM), which is fast but forgets everything when the program stops or the machine restarts (see what software is). Anything that must survive a restart, such as a customer's account, an order or a hospital appointment, has to be written to storage that keeps its contents without power: a disk or solid-state drive. Data that survives in this way is called persistent, and deciding how to persist it is one of the first and longest-lived decisions in any system.

The simplest way to persist data is a file: a named sequence of bytes that the operating system stores on disk. Files are excellent for things that are naturally whole documents: images, PDFs, videos, configuration, logs, exported reports. Systems often keep them in cloud object storage (a service that stores files by name) and record only a reference elsewhere.

Files become painful when many users read and change small pieces of structured data at once. Imagine a shop keeping all its orders in one spreadsheet file. Two customers check out at the same moment: whose change wins? The server crashes halfway through saving: is the file half old, half new? A support agent wants one customer's orders: must the program read the whole file? Someone types a customer number that does not exist: who stops them? Every program using the file would have to solve each problem, identically.

What a database is

A database is an organised collection of data, and the program that looks after it is a database management system (DBMS), such as PostgreSQL, MySQL, Microsoft SQL Server, Oracle Database or SQLite. People often say "the database" for both. The DBMS solves the file problems once, for everyone: it lets many users work at the same time without trampling each other, finds data quickly, refuses data that breaks the rules, and recovers to a correct state after a crash.

The analogy is a library. A heap of papers on a desk works if you are its only user. A library has a catalogue, rules about where things go, a record of who borrowed what, and a librarian who will not let two people take the same copy. Applications do not touch the shelves; they ask the librarian.

In most business systems the DBMS runs as its own server process, often on its own machine, and applications talk to it over the network (see how computers talk). SQLite is a well-known exception: it is a library that runs inside the application and stores the whole database in a single file, which is why it is found in phones, browsers and small tools.

Relational databases: tables, keys and relationships

The most widely used kind of database is the relational database, based on a model published by Edgar F. Codd of IBM in 1970. Data lives in tables. Each table describes one kind of thing (customers, orders, products). Each row is one instance of that thing (one customer), and each column is one fact about it (name, email address, date joined), with a declared type such as integer, text, date or decimal.

Each table has a primary key: a column (or combination of columns) whose value is unique for every row and never empty, such as customer_id. A foreign key is a column in one table that holds the primary key of a row in another table. An order has a customer_id column, which says which customer placed it. Declaring it as a foreign key tells the DBMS to refuse an order for customer 999 if there is no customer 999: this is called referential integrity.

The full set of tables, columns, types, keys and rules is the schema. The schema is a contract: every program that uses the database can rely on an order always having a valid customer and a price that is a number. Changing a schema later, when millions of rows and several programs depend on it, is possible but takes care, which is why schema evolution has its own section in Chapter 7.

Relational designs try to store each fact once. The customer's email address lives in one row of the customers table, not copied into every order. This is called normalisation. It means an update happens in one place and cannot leave contradictory copies behind. The price for this tidiness is that answering a question often means combining tables, which is what joins are for.

Asking questions with SQL

SQL (Structured Query Language, pronounced "S-Q-L" or "sequel") is the language used to work with relational databases. It was developed at IBM in the 1970s and became an ANSI and ISO standard in the late 1980s; every major relational DBMS speaks it, each with small dialect differences. SQL is declarative: you describe the result you want, not the steps to find it. The DBMS's query planner chooses the steps, for example whether to read a whole table or use an index.

A join combines rows from two or more tables by matching columns, usually a foreign key against the primary key it refers to. The second query below lists each line of a customer's orders together with the product's name, drawing on three tables at once.

-- One customer's orders since 1 October, newest first
SELECT order_id, status, placed_at
FROM orders
WHERE customer_id = 42
  AND placed_at >= '2026-10-01'
ORDER BY placed_at DESC;

-- Each order line with the product's name (a join)
SELECT o.order_id, p.name, i.quantity, i.unit_price
FROM orders AS o
JOIN order_items AS i ON i.order_id = o.order_id
JOIN products AS p ON p.product_id = i.product_id
WHERE o.customer_id = 42;

INSERT, UPDATE and DELETE change data, and CREATE TABLE defines the schema. Application code often sends SQL through an object-relational mapper (ORM), a library that turns rows into objects. ORMs save typing but hide the SQL actually sent; a classic performance problem is an ORM quietly issuing one query per row instead of one join.

Indexes: the book's index, not the whole book

Without help, the only way for the database to find every order for customer 42 is to read every row in the orders table and check each one: a full table scan, which is O(n) in the notation from data and algorithms. A database index is an extra, sorted data structure that maps values of a column to the rows that contain them, just as the index at the back of a book maps words to page numbers. Most relational databases use a B-tree by default, a wide, shallow, always-balanced tree that finds a value in O(log n) steps.

Primary keys are indexed automatically. Other indexes are added deliberately, for columns that queries filter, join or sort on: CREATE INDEX idx_orders_customer ON orders (customer_id);. Indexes are not free. Every insert, update or delete must also update each index on the table, and each index takes disk space and memory. A table with fifteen indexes reads quickly and writes slowly. Choosing indexes is a trade-off guided by the queries the system actually runs, which is an early example of the space-for-time trade-off that runs through all of architecture.

Transactions and ACID: the bank transfer

Moving 100 pounds from Alice's account to Bob's needs two changes: subtract 100 from Alice, add 100 to Bob. If the server crashes after the first change and before the second, 100 pounds has vanished. If two transfers from Alice run at once, both might see a balance of 150 and both might succeed, leaving her overdrawn. A transaction groups several operations into one unit of work that either completes entirely (commit) or has no effect at all (rollback).

The guarantees a transaction gives are summarised as ACID, an acronym coined by Theo Härder and Andreas Reuter in 1983, building on earlier work by Jim Gray:

import sqlite3

conn = sqlite3.connect("bank.db")
# The accounts table was created with CHECK (balance >= 0)

def transfer(from_id, to_id, amount):
    try:
        with conn:  # one transaction: commit on success, rollback on error
            debit = conn.execute(
                "UPDATE accounts SET balance = balance - ? WHERE id = ?",
                (amount, from_id))
            credit = conn.execute(
                "UPDATE accounts SET balance = balance + ? WHERE id = ?",
                (amount, to_id))
            if debit.rowcount != 1 or credit.rowcount != 1:
                raise ValueError("No such account")  # forces a rollback
        return "Transfer complete"
    except (sqlite3.Error, ValueError):
        return "Transfer refused: no account was changed"

Note the check on rowcount (the number of rows each update changed): an update that matches no account is not an error to the database, so the code must treat it as one itself. Two further details matter beyond this example. The ? placeholders pass values separately from the SQL text, which is the standard defence against SQL injection, an attack in which user input is smuggled into a query as code (Chapter 8 returns to this). And the durability promise is kept by a write-ahead log: the DBMS records each change in an append-only log on disk before confirming the commit, so after a crash it can replay or undo work to reach a correct state.

Isolation is the subtlest property. Full isolation (called serializable) is safe but limits how much work can run in parallel, so databases offer weaker isolation levels that trade some protection for speed, and many use a weaker level by default. Which anomalies each level allows is covered in transactions and isolation. For now, the key idea is that a single database gives strong, well-understood guarantees within one transaction, and those guarantees are much harder to obtain once data is spread across several databases or services.

NoSQL: other shapes of data

Relational databases are not the only option. NoSQL is an umbrella name, popularised at a 2009 meetup in San Francisco, for databases that do not use the relational model. They grew up to handle very large volumes spread across many machines, flexible data shapes, and access patterns that tables serve awkwardly. The name says what they are not, so it covers quite different families.

Practitioners disagree about the default. One camp says to start with a relational database unless there is a clear reason not to: SQL is widely known, transactions and joins are powerful, and modern relational systems also store JSON documents well. The other camp says to choose the store that matches the access pattern from the start, because a document or key-value store can be simpler and scale more cheaply for the right workload. Both agree that many real systems use more than one kind, each where it fits, which is called polyglot persistence. Chapter 7's section on choosing a database gives a structured way to decide.

Worked example: an online shop's schema

Consider a small online shop. It needs to know who its customers are, what it sells, and who ordered what. A first attempt might put everything in one table with a row per order and columns product1, product2, product3. That breaks as soon as someone orders four things, and it copies the customer's name and address into every order.

Backups, recovery and managed databases

Disks fail, people run the wrong DELETE, software has bugs and attackers encrypt data for ransom. A backup is a copy of the data kept somewhere separate, from which the database can be restored. Three professional habits matter. A backup that has never been restored is a hope, not a backup, so teams rehearse and time restores. Copies are kept in more than one place (a common rule of thumb is three copies, on two kinds of media, one off-site). And a replica, a second database that continuously copies the first, protects against a machine failing but not against mistakes: a bad DELETE reaches the replica within milliseconds. Many databases can combine a periodic full backup with the saved transaction log to restore to any moment, called point-in-time recovery.

Running a database well (patching, tuning, replicating, backing up and restoring at three in the morning) is skilled, never-ending work, so cloud providers sell a managed database: Amazon RDS, Azure SQL Database and Google Cloud SQL are examples. The provider handles the machines, software updates, automated backups and failover to a standby copy; the customer still owns the schema, the queries, the indexes, access permissions and the cost. Managed services are covered more broadly in servers and the cloud.

Why this matters for architecture

Data usually outlives the code around it: applications are rewritten every few years, while customer records and medical histories are kept for decades. Decisions about where data lives, which kind of database holds it, which program owns it and which guarantees it needs are therefore some of the most expensive to reverse. Several big architectural questions grow directly from this section: whether each service should own its own database (data ownership), which database fits which job (choosing a database), and how to keep data correct when one business action touches several databases, where a single ACID transaction is no longer available (sagas). When an architect asks "where is the source of truth for this?", this section is the reason.