31 — Databases and SQL Fundamentals

Level: Intermediate · Time: ~45 min · Prerequisites: Lesson 30 — Typing API Responses at the Boundary

Why this matters

Lesson 27 fetched the irradiance, Lesson 29 made the fetch survive a flaky network, and Lesson 30 stopped trusting the payload until a schema proved it — but nothing was kept. A solar tracker only becomes useful when it can answer "what did Sokone produce in March?" from its own records.

Choosing where the data lives

Store	Excels at	Breaks down when
A file (CSV, JSON)	One writer, one human reader, a stable shape	Two writers overwrite each other; "March rows above 30 kWh" means loading everything
SQLite	A local tool, a test suite, a single-user app	Several processes write at once, or the file sits on a network share
PostgreSQL	Many concurrent writers, real types, extensions	You are shipping a desktop tool with no server to run it on
A document store	Nested, self-contained documents whose shape varies	You need joins and aggregates, or multi-document transactions

The progression: a file first, SQLite when you need queries and integrity, a server when several processes write at once — the last two steps are Lessons 32 and 33.

Tables, keys and constraints

A table is a type declaration for its rows, enforced by the engine forever.

CREATE TABLE sites (
  site_id      INTEGER PRIMARY KEY,
  name         TEXT NOT NULL UNIQUE,
  capacity_kwp REAL NOT NULL CHECK (capacity_kwp > 0),
  commissioned TEXT NOT NULL
);

CREATE TABLE daily_production (
  site_id INTEGER NOT NULL REFERENCES sites(site_id) ON DELETE CASCADE,
  day     TEXT    NOT NULL,
  kwh     REAL    NOT NULL CHECK (kwh >= 0),
  PRIMARY KEY (site_id, day)
);

A primary key identifies a row. (site_id, day) is composite: one row per site per day, so a duplicate import cannot happen.
A foreign key is a value that must exist elsewhere, so an orphan production row cannot be written, and constraints are types for data the compiler never sees.
Dates are ISO YYYY-MM-DD text here: that format sorts chronologically, which 01/03/2026 does not.

[!WARNING] SQLite ignores foreign keys unless they are switched on per connection (PRAGMA foreign_keys = ON). Lesson 32 sets it in the connection factory; forget it and the constraint above is decoration.

Reading, writing and joining

SELECT day, kwh
FROM daily_production
WHERE site_id = 2 AND kwh > 0
ORDER BY day DESC
LIMIT 7;
INSERT INTO sites (site_id, name, capacity_kwp, commissioned) VALUES (3, 'Sokone-2', 8.5, '2026-02-01');
UPDATE daily_production SET kwh = 31.2 WHERE site_id = 3 AND day = '2026-03-01';
DELETE FROM daily_production WHERE site_id = 3 AND day < '2026-02-01';

UPDATE and DELETE without a WHERE touch every row: run the condition as a SELECT first. The point of a relational database is the join — two tables combined on a matching key.

SELECT s.name, d.day, d.kwh
FROM daily_production AS d
JOIN sites AS s ON s.site_id = d.site_id
WHERE d.day BETWEEN '2026-03-01' AND '2026-03-07'
ORDER BY s.name, d.day;

A plain JOIN is an inner join: only rows matching on both sides survive, so a site with no production disappears. LEFT JOIN keeps every left-hand row and fills the missing columns with NULL — what you want for "every site, with its week if it has one".

Monthly totals: GROUP BY, and HAVING versus WHERE

Aggregates collapse many rows into one (COUNT, SUM, AVG, MIN, MAX); GROUP BY says what "one" means.

SELECT s.name,
       substr(d.day, 1, 7) AS month,
       SUM(d.kwh)          AS kwh
FROM daily_production AS d
JOIN sites AS s ON s.site_id = d.site_id
WHERE d.kwh > 0
GROUP BY s.name, month
HAVING SUM(d.kwh) > 100
ORDER BY month, kwh DESC;

WHERE filters rows before grouping; HAVING filters groups after it. WHERE d.kwh > 0 drops a zero row before the sum; HAVING SUM(d.kwh) > 100 drops whole months whose total is too small. SUM also ignores NULL while COUNT(*) counts rows — when a total looks wrong, that is the usual cause.

Indexes, and reading a query plan

An index is a sorted lookup structure maintained beside the table: it turns a full scan into a targeted search, at the cost of disk space and slower writes, because every insert must update it too.

CREATE INDEX idx_daily_production_day ON daily_production (day);
EXPLAIN QUERY PLAN
SELECT COUNT(*) FROM daily_production WHERE day = '2026-03-01';

One line decides whether it worked: SCAN daily_production read the whole table, while SEARCH daily_production USING INDEX idx_daily_production_day (day=?) used the index. PostgreSQL prints a fuller plan with EXPLAIN ANALYZE, where Seq Scan is the bad word on a large table. Index the columns you filter and join on, plus the foreign key on the many side. Beware wrapping a column in a function: WHERE substr(day, 1, 7) = '2026-03' scans, while WHERE day >= '2026-03-01' AND day < '2026-04-01' uses the index on day.

Transactions, ACID and parameterised queries

A transaction groups statements so they succeed or fail together. COMMIT writes, ROLLBACK undoes.

BEGIN;
INSERT INTO sites (site_id, name, capacity_kwp, commissioned) VALUES (3, 'Sokone-2', 8.5, '2026-02-01');
INSERT INTO daily_production (site_id, day, kwh) VALUES (3, '2026-03-01', 31.2);
COMMIT;

ACID in plain language: Atomic (all statements take effect, or none), Consistent (constraints hold at the end), Isolated (concurrent transactions do not see half-finished work), Durable (a committed write survives a power cut). The surprise that bites in practice is the lost update: two processes read the same row, both act on the old value, and one change vanishes — so read and write inside one transaction, or lock the row while you hold it.

Never build SQL by concatenating values, because the value then becomes part of the program:

// Wrong: a crafted siteId changes the meaning of the statement.
const sql = `SELECT kwh FROM daily_production WHERE site_id = ${siteId}`;

Parameterised queries send the statement and the values separately, so a value can never be parsed as syntax: SQLite writes SELECT kwh FROM daily_production WHERE site_id = ? AND day = ? and binds the two values afterwards, while PostgreSQL writes $1 and $2 in the same places. The plan is compiled once and reused with different values — safe and fast together.

Common mistakes

Mistake	What happens	The fix
No primary key on a table	Duplicate imports accumulate and no row can be identified	Declare one; composite keys give "one row per X per Y"
A server database for a single-user tool	You now operate a server, a backup policy and a connection pool	SQLite in a file does this job, with the same SQL
Storing dates as `01/03/2026`	Sorting and ranges silently return the wrong rows	Store ISO `YYYY-MM-DD` text, or a real date column
`UPDATE`/`DELETE` with no `WHERE`	Every row changes, and there is no undo	Run the `SELECT` version first and check the count
Filtering aggregates in `WHERE`	The engine rejects `WHERE SUM(kwh) > 100`	Use `HAVING` for aggregated conditions
Interpolating values into SQL text	One crafted name reads or destroys the table	Parameterised queries, always — no exceptions

Try it

Open a scratch database and switch foreign keys on before creating anything. If sqlite3 is missing, sudo apt install sqlite3 or brew install sqlite provides it:

sqlite3 ~/dev/ts-zero-to-hero/solar-scratch.db

Expected output: the sqlite> prompt.

Run PRAGMA foreign_keys = ON;, paste the two CREATE TABLE statements from this lesson, then insert two sites and ten production days over two months — a 12 kWp site in Senegal yields 40–60 kWh on a clear day.

Expected output: SELECT COUNT(*) FROM daily_production; answers 10.

Prove the constraint is real, then ask the database whether anything is broken:

INSERT INTO daily_production (site_id, day, kwh) VALUES (99, '2026-03-05', 40);
PRAGMA foreign_key_check;

Expected output: Error: FOREIGN KEY constraint failed for the insert — no site 99 exists, which is the foreign key doing its job — then nothing at all from the pragma, the good result. Now run the monthly GROUP BY query: that report is what the exercise has been building towards.

Key takeaways

Choose storage by writers and readers: a file for one writer, SQLite for a local tool, a server when several processes write concurrently.
A table is a type declaration enforced by the engine; primary keys, foreign keys and CHECK constraints catch the data mistakes the type checker never sees.
WHERE filters rows before grouping, HAVING filters groups after it, and SUM ignores NULL while COUNT(*) does not.
Indexes trade write cost for read speed, and one line of query plan shows whether the trade happens.
Transactions give ACID; parameterised queries are the only reliable defence against SQL injection.

Check yourself

You store commissioned as 01/03/2026 text. Which two operations break first, and why does 2026-03-01 not have the same problem?
A report lists six sites but your JOIN returns four rows. What did you write, and what should it be?
Why can a WHERE clause never contain SUM(kwh), while a HAVING clause can?