Constructing a Database

Jump to Examples

Almost every program I have written over the years ended up with a database behind it – first in Microsoft Access, later in MySQL. Today it is the same everywhere: your phone's contacts and messages, the browser's history, Home Assistant's sensor history, Nextcloud's file index and the e-shop where you bought your boots all live in databases. Most of them are invisible, and most of them use the same few ideas that were worked out in the 1970s.

A database is more than a file full of data. It is a program that stores data in a defined structure, answers questions about it quickly, lets many users work at the same time without getting in each other's way, and protects the data from mistakes – a half-finished change, a duplicate, a link to something that does not exist. This note explains how that works, which database to choose for what, and how to build and look after one yourself.

A Bit of Database History

Storing data by machine is older than the computer. For the 1890 United States census, Herman Hollerith recorded each person as holes in a punched card and counted them with electric tabulating machines; his company later became part of IBM. Early computers stored data on punched cards and magnetic tape, read from start to end. Every program had its own files in its own format, and a question that nobody had planned for meant writing a new program.

In the 1960s, disks made it possible to jump directly to any record, and the first database systems appeared. Charles Bachman's Integrated Data Store (released in 1964 at General Electric) linked records with pointers, an idea standardised by the CODASYL group as the network model. IBM's IMS, built for the Apollo programme, first installed in 1968 and sold from 1969, used a hierarchical model – data as a tree, like folders on a disk. Both were fast, but the programmer had to know exactly how the records were linked and follow the pointers by hand.

The idea that changed everything came in 1970. Edgar F. Codd, a mathematician at IBM, published A Relational Model of Data for Large Shared Data Banks: store data in simple tables, link them by values instead of pointers, and let the user say what they want rather than how to find it. IBM's research project System R and Donald Chamberlin and Raymond Boyce's language SEQUEL (1974, later renamed SQL) turned the idea into practice, and at Berkeley Michael Stonebraker built Ingres. The first commercial SQL database came from a small company that later took the name of its product: Oracle (1979). SQL became an ANSI standard in 1986 and is still the common language of databases today.

Personal computers brought databases to the desk. dBase (1980) was the database of the DOS era, and Microsoft Access (1992) made it possible to build a small database application with forms and reports without being a programmer. With the web came free database servers: MySQL (1995, from Sweden) became the "M" in the LAMP stack (Linux, Apache, MySQL, PHP) behind countless websites, while PostgreSQL grew out of Stonebraker's POSTGRES project at Berkeley (1986) and got its present name in 1996. In 2000 D. Richard Hipp released SQLite, a complete SQL database in a single file, placed in the public domain – today it is in every phone and browser. When Sun Microsystems bought MySQL in 2008 and Oracle bought Sun soon after, MySQL's original author Michael Widenius started the fork MariaDB (2009).

At the same time, the giants of the web hit the limits of a single database server. Google's Bigtable (2006) and Amazon's Dynamo (2007) spread data over thousands of machines and gave up some guarantees for speed and scale. Around 2009 the movement got the name NoSQL, with document databases such as MongoDB and CouchDB, key-value stores such as Redis, and wide-column stores such as Cassandra. A decade later the pendulum swung back: most "NoSQL" systems added SQL-like queries, relational databases learned to store JSON documents, and PostgreSQL became the developers' favourite – in the Stack Overflow Developer Survey 2025, 55.6% of developers said they use it. The newest additions are analytical databases such as DuckDB (2019), which work through millions of rows on a laptop, and vector databases, which store the numerical "meaning" of texts for AI search (see Constructing an AI). Licences have also become a topic: in 2024 Redis moved to a licence that is not open source, the community answered with the Linux Foundation's fork Valkey, and in 2025 Redis returned to an open-source licence (AGPLv3).

A short timeline:

Some resources for history:

• Database – History (Wikipedia)
• Relational model (Wikipedia)
• The history of PostgreSQL
• Stack Overflow Developer Survey
• Computer History Museum

How a Database Works

The table goes from the basic vocabulary to the parts that make a database reliable. Example 1 below uses all of the first group.

The building blocks of a relational database:

Which database for what? (my own rough guide, not a ranking):

A few observations. First, the structure is the most important decision: a good design makes questions simple and mistakes impossible, a bad one cannot be fixed with faster hardware. Second, let the database check the rules – a constraint in the table protects the data from every program, a check in one program only from that program. Third, a database is only as safe as its last tested backup.

Some resources for learning:

• SQLBolt – interactive SQL lessons in the browser
• PostgreSQL Tutorial (official documentation)
• SQL as understood by SQLite
• Use The Index, Luke – a free book about indexes and performance
• PostgreSQL Exercises
• My Databases resources

Examples: Building and Looking After a Database

The first three examples use only Python and its built-in SQLite, so they run on any computer. The fourth sets up a PostgreSQL server for a home network. I ran Examples 1 to 3 with Python 3.13 and SQLite 3.45, and the SQL and backup commands of Example 4 on PostgreSQL 16; the outputs shown are from those runs. Save each example as a .py file and run it with python3 name.py (on Windows: python name.py). The Python basics are in Constructing a Program.

Example 1 – A database of my hikes with SQLite

Two tables – peaks and hikes – linked by a key. The script creates the database file hikes.db, fills it and asks three questions: a simple filter, a JOIN that combines both tables, and a summary with GROUP BY. At the end it tries to record a hike on a peak that does not exist.

import sqlite3

db = sqlite3.connect("hikes.db")          # the whole database is this one file
db.execute("PRAGMA foreign_keys = ON")    # SQLite checks the links between tables only when asked

# 1. Structure: two tables, linked by peak_id
db.executescript("""
DROP TABLE IF EXISTS hikes;
DROP TABLE IF EXISTS peaks;

CREATE TABLE peaks (
    id        INTEGER PRIMARY KEY,
    name      TEXT NOT NULL UNIQUE,
    height_m  INTEGER NOT NULL CHECK (height_m > 0),
    mountain_range TEXT NOT NULL
);

CREATE TABLE hikes (
    id        INTEGER PRIMARY KEY,
    peak_id   INTEGER NOT NULL REFERENCES peaks(id),
    date      TEXT NOT NULL,             -- ISO format: 2026-07-12
    hours     REAL,
    company   TEXT
);
""")

# 2. Data
db.executemany("INSERT INTO peaks (name, height_m, mountain_range) VALUES (?, ?, ?)", [
    ("Triglav",   2864, "Julian Alps"),
    ("Krn",       2244, "Julian Alps"),
    ("Grintovec", 2558, "Kamnik-Savinja Alps"),
    ("Stol",      2236, "Karawanks"),
    ("Snežnik",   1796, "Dinaric Alps"),
])
db.executemany("INSERT INTO hikes (peak_id, date, hours, company) VALUES (?, ?, ?, ?)", [
    (1, "2025-08-14", 11.5, "friends"),
    (3, "2025-09-06",  7.0, None),
    (4, "2026-05-01",  6.0, "friends"),
    (2, "2026-06-20",  8.5, "friends"),
    (1, "2026-07-12", 12.0, None),
    (5, "2026-10-04",  4.5, "family"),
])
db.commit()

# 3. Questions
def show(title, sql):
    print("--", title)
    for row in db.execute(sql):
        print("  ", row)

show("peaks above 2500 m, highest first",
     "SELECT name, height_m FROM peaks WHERE height_m > 2500 ORDER BY height_m DESC")

show("every hike with its peak (JOIN)",
     """SELECT h.date, p.name, h.hours
        FROM hikes h JOIN peaks p ON p.id = h.peak_id
        ORDER BY h.date""")

show("summary per mountain range (GROUP BY)",
     """SELECT p.mountain_range, COUNT(*) AS hikes, ROUND(AVG(h.hours), 1) AS avg_hours, MAX(p.height_m) AS highest
        FROM hikes h JOIN peaks p ON p.id = h.peak_id
        GROUP BY p.mountain_range
        ORDER BY hikes DESC, highest DESC""")

# 4. The database protects itself
try:
    db.execute("INSERT INTO hikes (peak_id, date) VALUES (99, '2026-10-16')")
except sqlite3.IntegrityError as e:
    print("-- refused:", e)

Output:

-- peaks above 2500 m, highest first
   ('Triglav', 2864)
   ('Grintovec', 2558)
-- every hike with its peak (JOIN)
   ('2025-08-14', 'Triglav', 11.5)
   ('2025-09-06', 'Grintovec', 7.0)
   ('2026-05-01', 'Stol', 6.0)
   ('2026-06-20', 'Krn', 8.5)
   ('2026-07-12', 'Triglav', 12.0)
   ('2026-10-04', 'Snežnik', 4.5)
-- summary per mountain range (GROUP BY)
   ('Julian Alps', 3, 10.7, 2864)
   ('Kamnik-Savinja Alps', 1, 7.0, 2558)
   ('Karawanks', 1, 6.0, 2236)
   ('Dinaric Alps', 1, 4.5, 1796)
-- refused: FOREIGN KEY constraint failed

Notice that the hike table never repeats the height or the mountain range of a peak – it only points to it. If Triglav's height were ever corrected, it would change in one place and every question would use the new value at once. The last line shows the database refusing invalid data on its own: the program made a mistake, the database did not. Open hikes.db in DB Browser for SQLite to see the tables.

Example 2 – A million sensor readings and an index

The temperature sensors in Microcontroller Communication send a reading every few minutes. Ten sensors over a year make about a million rows. The script fills such a table and asks for the daily average, minimum and maximum of one sensor for the first week of October – first without an index, then with one.

import random
import sqlite3
import time

db = sqlite3.connect("sensors.db")
db.executescript("""
DROP TABLE IF EXISTS readings;
CREATE TABLE readings (
    id      INTEGER PRIMARY KEY,
    sensor  TEXT NOT NULL,
    time    TEXT NOT NULL,          -- '2026-10-16 14:30:00'
    temp_c  REAL NOT NULL
);
""")

# One year of readings from 10 sensors, one every 5 minutes: about a million rows
random.seed(1)
rows = []
for minute in range(0, 365 * 24 * 60, 5):
    stamp = time.strftime("%Y-%m-%d %H:%M:%S", time.gmtime(1767225600 + minute * 60))   # from 1 Jan 2026
    for s in range(10):
        rows.append((f"room-{s}", stamp, round(random.gauss(21, 2), 1)))
with db:                                    # one transaction for all rows: much faster
    db.executemany("INSERT INTO readings (sensor, time, temp_c) VALUES (?, ?, ?)", rows)
print("rows:", db.execute("SELECT COUNT(*) FROM readings").fetchone()[0])

QUERY = """SELECT substr(time, 1, 10) AS day, ROUND(AVG(temp_c), 1), MIN(temp_c), MAX(temp_c)
           FROM readings
           WHERE sensor = ? AND time BETWEEN ? AND ?
           GROUP BY day"""
ARGS = ("room-3", "2026-10-01", "2026-10-08")

def run(label):
    plan = db.execute("EXPLAIN QUERY PLAN " + QUERY, ARGS).fetchall()[0][-1]
    start = time.perf_counter()
    result = db.execute(QUERY, ARGS).fetchall()
    ms = (time.perf_counter() - start) * 1000
    print(f"{label}: {ms:7.1f} ms   plan: {plan}")
    return result

run("without index")
db.execute("CREATE INDEX idx_sensor_time ON readings (sensor, time)")
result = run("with index   ")
for row in result[:3]:
    print("  ", row)

Output (the times depend on the computer):

rows: 1051200
without index:    69.6 ms   plan: SCAN readings
with index   :     1.7 ms   plan: SEARCH readings USING INDEX idx_sensor_time (sensor=? AND time>? AND time<?)
   ('2026-10-01', 21.0, 15.7, 26.7)
   ('2026-10-02', 20.9, 13.1, 26.3)
   ('2026-10-03', 20.9, 16.0, 27.2)

Without an index, SQLite has to scan all 1,051,200 rows to find the few thousand that matter. With an index on (sensor, time) – ordered by sensor first, then by time – it searches directly for the right sensor and time range, and in my runs the same question was 30 to 40 times faster. The difference grows with the size of the table. Note also the line with db: around the inserts: writing a million rows in one transaction takes seconds, while a separate transaction for every row could take many minutes, because each confirmed transaction must be safely written to the disk.

Example 3 – SQL injection and transactions

Two lessons in one script. The first is the most common database security error on the web; the second shows why a transaction is "all or nothing".

import sqlite3

db = sqlite3.connect(":memory:")            # a temporary database in memory
db.executescript("""
CREATE TABLE users (name TEXT PRIMARY KEY, is_admin INTEGER NOT NULL);
INSERT INTO users VALUES ('ana', 1), ('bor', 0);
CREATE TABLE accounts (name TEXT PRIMARY KEY, balance INTEGER NOT NULL CHECK (balance >= 0));
INSERT INTO accounts VALUES ('home', 100), ('holiday', 0);
""")

# 1. SQL injection: text from a form glued into SQL becomes part of the command
name = "x' OR '1'='1"
wrong = db.execute(f"SELECT name FROM users WHERE name = '{name}'").fetchall()
right = db.execute("SELECT name FROM users WHERE name = ?", (name,)).fetchall()
print("glued into the SQL:", wrong)
print("as a parameter:    ", right)

# 2. A transaction: all steps happen, or none
def transfer(src, dst, amount):
    try:
        with db:                            # BEGIN ... COMMIT, or ROLLBACK on an error
            db.execute("UPDATE accounts SET balance = balance + ? WHERE name = ?", (amount, dst))
            db.execute("UPDATE accounts SET balance = balance - ? WHERE name = ?", (amount, src))
        print(f"moved {amount}:", db.execute("SELECT * FROM accounts").fetchall())
    except sqlite3.IntegrityError as e:
        print(f"refused {amount} ({e}):", db.execute("SELECT * FROM accounts").fetchall())

transfer("home", "holiday", 60)
transfer("home", "holiday", 60)

Output:

glued into the SQL: [('ana',), ('bor',)]
as a parameter:     []
moved 60: [('home', 40), ('holiday', 60)]
refused 60 (CHECK constraint failed: balance >= 0): [('home', 40), ('holiday', 60)]

SQL injection: when text from a user is glued into an SQL command, the quote in x' OR '1'='1 ends the string and the rest becomes part of the command – the condition is always true and the "login" returns every user. With a parameter (?), the database receives the text separately and treats it only as data, so it finds nobody. Always use parameters; never build SQL with string formatting.

The transaction: the first transfer works. In the second, the money is first added to "holiday", then the subtraction would make "home" negative, and the CHECK rule refuses it. Because both steps are inside one transaction, the database undoes the first step too – no money appeared from nowhere. Without a transaction, "holiday" would now have 120.

Example 4 – PostgreSQL on a home server, with users and backups

For several applications or users, a database server is the better choice. The easiest way is a container, in the same way as Nextcloud in Constructing a Cloud. The container part is a starting point, not a tested recipe for your system – check the official image documentation, which changes between major versions.

1. Start the server. Create a folder with this compose.yaml and run docker compose up -d:

   services:
     db:
       image: postgres:17
       restart: unless-stopped
       environment:
         POSTGRES_USER: admin
         POSTGRES_PASSWORD: change-this-password
         POSTGRES_DB: hikes
       ports:
         - "127.0.0.1:5432:5432"      # only this machine; never open 5432 to the Internet
       volumes:
         - ./pgdata:/var/lib/postgresql/data

2. Create the tables. The same design as in Example 1, in PostgreSQL's dialect – note the real date and numeric types. Save it as hikes_pg.sql and load it with docker compose exec -T db psql -U admin -d hikes < hikes_pg.sql:

   CREATE TABLE peaks (
       id              integer GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
       name            text NOT NULL UNIQUE,
       height_m        integer NOT NULL CHECK (height_m > 0),
       mountain_range  text NOT NULL
   );
   
   CREATE TABLE hikes (
       id       integer GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
       peak_id  integer NOT NULL REFERENCES peaks(id),
       date     date NOT NULL,
       hours    numeric(4,1),
       company  text
   );
   
   INSERT INTO peaks (name, height_m, mountain_range) VALUES
       ('Triglav', 2864, 'Julian Alps'), ('Krn', 2244, 'Julian Alps'),
       ('Grintovec', 2558, 'Kamnik-Savinja Alps'), ('Stol', 2236, 'Karawanks'),
       ('Snežnik', 1796, 'Dinaric Alps');
   
   INSERT INTO hikes (peak_id, date, hours, company) VALUES
       (1, '2025-08-14', 11.5, 'friends'), (3, '2025-09-06', 7.0, NULL),
       (4, '2026-05-01', 6.0, 'friends'), (2, '2026-06-20', 8.5, 'friends'),
       (1, '2026-07-12', 12.0, NULL), (5, '2026-10-04', 4.5, 'family');
   
   SELECT p.mountain_range, count(*) AS hikes, round(avg(h.hours), 1) AS avg_hours
   FROM hikes h JOIN peaks p ON p.id = h.peak_id
   GROUP BY p.mountain_range
   ORDER BY hikes DESC, avg_hours DESC;

   The last query prints:

      mountain_range    | hikes | avg_hours
   ---------------------+-------+-----------
    Julian Alps         |     3 |      10.7
    Kamnik-Savinja Alps |     1 |       7.0
    Karawanks           |     1 |       6.0
    Dinaric Alps        |     1 |       4.5
   (4 rows)

3. Give each program its own user with only the rights it needs. A dashboard that only reads should not be able to delete anything. Open a console with docker compose exec db psql -U admin -d hikes and run:

   CREATE ROLE reader LOGIN PASSWORD 'another-long-password';
   GRANT CONNECT ON DATABASE hikes TO reader;
   GRANT USAGE ON SCHEMA public TO reader;
   GRANT SELECT ON ALL TABLES IN SCHEMA public TO reader;

   When this user tries to delete, PostgreSQL answers ERROR: permission denied for table hikes, while reading works.
4. Back up with the database's own tool, not by copying the files of a running server, which can give a broken copy:

   docker compose exec -T db pg_dump -U admin -Fc hikes > hikes_2026-10-16.dump

   The -Fc format is compressed and can be restored selectively. Automate it every night and copy the files elsewhere – the backup script and the systemd timer in Constructing a Program can do that with small changes.
5. Test the restore, into a new, empty database, so that you never practise on the real one:

   docker compose exec db createdb -U admin hikes_test
   docker compose exec -T db pg_restore -U admin -d hikes_test --no-owner < hikes_2026-10-16.dump
   docker compose exec db psql -U admin -d hikes_test -c "SELECT count(*) FROM hikes"

   If the count is right, you have a backup – not just a file that you hope is one.
6. Connect with a graphical tool. DBeaver or pgAdmin on the server itself connects to localhost:5432. From another computer, use an SSH tunnel (ssh -L 5432:localhost:5432 user@server) or the WireGuard VPN from Constructing a Cloud instead of opening the port.

The same steps work for MariaDB with its own tools: the mariadb image, mariadb-dump for backups and GRANT SELECT for read-only users.

Keeping the Data Safe

• Back up, and test the restore. Use the dump tool of the database (pg_dump, mariadb-dump, or SQLite's .backup command), keep copies on another machine, and follow the 3-2-1 rule from Security Considerations. When a server is in trouble, the database is the first thing to save – see Troubleshooting Linux.
• Never expose a database port to the Internet. Database servers are scanned constantly. Bind them to 127.0.0.1 or the internal network and reach them through the application, an SSH tunnel or a VPN.
• One user per program, with the least rights it needs, and long, unique passwords stored outside the code (see Constructing a Program).
• Always use parameters in queries, never glued strings (Example 3).
• Keep the database software updated, and read the release notes before a major upgrade – moving to a new major version of PostgreSQL, for example, needs a dump and restore or the pg_upgrade tool.
• Mind personal data. A database of customers, members or patients is exactly what the GDPR is about: collect only what you need, protect it, and delete it when it is no longer needed.
• Watch the disk. A full disk can stop a database or damage it. Logs, old backups and forgotten test databases are the usual reasons.

A Few More Thoughts

Databases are one of the quiet successes of computing. Codd's idea from 1970 – simple tables, and a language that says what you want instead of how to find it – has outlived the mainframes it was invented for, every wave of new programming languages and a decade of predictions that NoSQL would replace it. When I started with Access and MySQL, a database felt like a big, serious thing for companies. Today the same power is in a single SQLite file on a phone, and a PostgreSQL server fits on a Raspberry Pi.

What has not changed is where the real work is: thinking about the structure before the first line of data, letting the database guard its own rules, and making backups that have actually been restored. The tools are free and excellent. The discipline is still up to us – and the reward is data that is correct, fast to question and still there in twenty years.