What Is a Database?

When you sign in to a website, the site has to find your account among millions of others, check your password and load your settings, all in a few milliseconds. It does not open a text file and read it line by line. It sends a request such as SELECT * FROM users WHERE email = 'ana@example.com' to a database, and the database jumps straight to the right row using an index, much like the index at the back of a book.

How a database works

Two pieces work together. The database is the data, organized in a fixed structure. The database management system (DBMS) is the program that stores that data in files, keeps it consistent, and answers requests. PostgreSQL, MySQL and SQLite are DBMSs; your list of customers is the database.

In a relational database, data lives in tables. Each table has columns (name, country, points) and rows (one per user). A request goes through these steps:

1. Your program sends a query, usually written in SQL (Structured Query Language).
2. The DBMS parses the query and plans how to answer it: which tables to read and whether an index can skip most of the rows.
3. It reads the data from disk in fixed-size pages (4,096 bytes by default in SQLite, 8 KB in PostgreSQL), keeping frequently used pages in memory.
4. It filters, sorts and combines rows, then returns the result.

Python ships with SQLite, so you can run a real database right here:

import sqlite3

db = sqlite3.connect(":memory:")   # a database that lives in RAM
db.execute("CREATE TABLE users (id INTEGER PRIMARY KEY, name TEXT, country TEXT, points INTEGER)")

rows = [("Ana", "BR", 120), ("Kenji", "JP", 340), ("Lea", "DE", 90), ("Omar", "EG", 340)]
db.executemany("INSERT INTO users (name, country, points) VALUES (?, ?, ?)", rows)

# Ask a question in SQL: who has more than 100 points, highest first?
query = "SELECT name, points FROM users WHERE points > 100 ORDER BY points DESC, name"
for name, points in db.execute(query):
    print(name, points)

total = db.execute("SELECT COUNT(*), AVG(points) FROM users").fetchone()
print("users:", total[0], "average points:", total[1])

Kenji 340
Omar 340
Ana 120
users: 4 average points: 222.5

The program never says how to search. It describes the result it wants, and the DBMS decides how to get it. That is the main difference between asking a database and writing a loop over a list.

Types of databases

Everything that is not relational is often grouped as NoSQL. Relational databases remain the default choice for most applications, because tables, constraints and SQL cover a wide range of problems well. Many products also combine types: PostgreSQL stores JSON documents, and pgvector adds vector search to it.

Transactions: all or nothing

Moving money from Ana to Ben takes two changes: add to Ben, subtract from Ana. If the program crashes between them, money appears from nowhere. A transaction groups changes so that either all of them are saved or none are. Here the second change breaks a rule (a balance may not go below zero), so the database undoes the first one too:

import sqlite3

db = sqlite3.connect(":memory:")
db.execute("CREATE TABLE accounts (name TEXT PRIMARY KEY, balance INTEGER CHECK (balance >= 0))")
db.executemany("INSERT INTO accounts VALUES (?, ?)", [("ana", 50), ("ben", 0)])
db.commit()

def transfer(amount):
    try:
        with db:  # one transaction: commit on success, roll back on error
            db.execute("UPDATE accounts SET balance = balance + ? WHERE name = 'ben'", (amount,))
            db.execute("UPDATE accounts SET balance = balance - ? WHERE name = 'ana'", (amount,))
        print("transfer", amount, "done")
    except sqlite3.IntegrityError as err:
        print("transfer", amount, "failed:", err)
    print(dict(db.execute("SELECT name, balance FROM accounts")))

transfer(30)
transfer(40)

transfer 30 done
{'ana': 20, 'ben': 30}
transfer 40 failed: CHECK constraint failed: balance >= 0
{'ana': 20, 'ben': 30}

Ben's 40 was added and then taken back, so the totals still add up. Relational databases promise four properties for transactions, known as ACID: atomic (all or nothing), consistent (rules such as CHECK always hold), isolated (two users running transactions at once do not see each other's half-finished work) and durable (once saved, a change survives a crash or power loss).

Database vs spreadsheet vs file

A spreadsheet is a good tool for one person looking at a few thousand rows. Once several programs or users write data at the same time, or the data has to follow rules, a database is the right tool.

How databases protect data

A DBMS writes each change to a log before it touches the data files (the write-ahead log), so after a crash it can replay or undo unfinished work. PostgreSQL can store a checksum on every 8 KB page and report a checksum failure when a failing disk returns damaged data. Users and permissions decide who may read or change which tables, and replication keeps copies on other servers. Programs must also pass user input as parameters, like the ? placeholders above, never by pasting it into the SQL text, which is how SQL injection attacks happen.

Common misconceptions

• "SQL is a database." SQL is the language. PostgreSQL, MySQL and SQLite are databases that understand it.
• "NoSQL means no SQL at all." The term is usually read as "not only SQL", and several NoSQL databases have query languages that look like SQL, such as Cassandra's CQL.
• "A database is always a big server." SQLite is a single file that a program opens directly, with no server at all. It runs inside every Android and iOS phone.
• "Data in a database is safe forever." A database protects against crashes, not against someone running DELETE by mistake. Backups are still needed.

Where to go next

The SQL course teaches queries from the first SELECT. The SQLite docs cover the next steps: what SQLite is, creating a table, indexes and preventing SQL injection. To see how the data is laid out on disk, read what is a byte, and checksum explains how damaged pages are detected.