Section 4
Databases, from a single file on your laptop up to a paid service that runs the whole back end for you — and which one to actually start with.
You already understand a database. It's a spreadsheet.
Rows going across, columns going down, one sheet per kind of thing. A sheet of songs. A sheet of people. A sheet of orders.
| id | title | artist | rating |
|---|---|---|---|
| 1 | Nobody | Mitski | 5 |
| 2 | Francis Forever | Mitski | 4 |
| 3 | Motion Sickness | Phoebe Bridgers | 5 |
That's a database table. Genuinely. The vocabulary is fancier but the shape is exactly what you'd expect.
So why not just use a spreadsheet? Two reasons, and they're the only two that matter:
Ask this before you build anything. The honest answer is usually no, and saying no saves you a great deal of work.
| What you're building | Database? |
|---|---|
| Portfolio, blog, band page, anything you write yourself | No. Just files. |
| A contact form that emails you | No. The email is the record. |
| People log in and have their own stuff | Yes |
| Visitors post, comment, or upload | Yes |
| Anything with orders, bookings, or payments | Yes |
| A searchable list that changes often | Probably |
The site you're reading has no database. It's plain files on a server. That's why it never goes down, costs nothing to run, and has nothing to back up.
| Name | What it is | Cost | Good for |
|---|---|---|---|
| SQLite | One file on your computer. No server, nothing running. | Free | Learning, and honestly most small projects |
| PostgreSQL | A proper database server. Free, open, the modern default. | Free — you run it | Real apps, anything that will grow |
| MySQL | The older standard. Runs a huge share of the web, including WordPress. | Free — you run it | Joining a project that already uses it |
| Supabase | Postgres that someone else runs, plus logins, file storage and a dashboard. | Free tier, then paid | Getting a real app with accounts working fast |
| Firebase | Google's. A different shape — not tables, more like nested folders. | Free tier, then paid | Chat and live-updating apps |
SQLite is a file called something like mystuff.db sitting in your project folder. You can copy it, email it, back it up by duplicating it. There's no server, nothing to start, nothing to pay for, nothing to break. People assume it's the toy option — it isn't. It's in every phone in your pocket and it runs plenty of real websites.
Postgres is what SQLite grows into. An actual program running in the background, built for many people at once. It's free and it's what most serious new projects choose. Slightly more to set up — which your AI can do for you.
MySQL does roughly the same job and got there first. Nothing wrong with it. But if you're choosing fresh today, Postgres is the more capable of the two and the one you'll see more of in new work. Learn MySQL when a project hands it to you, not by choice.
Supabase is Postgres with the boring parts done for you. Same database underneath, but you don't run the server — and it hands you user accounts, password resets, file uploads and a web dashboard to click around in. This matters more than it sounds: building login properly is genuinely hard and very easy to get dangerously wrong.
Firebase is the odd one out. It doesn't use tables or SQL — it stores things more like nested folders. Brilliant for chat apps and anything that updates live on everyone's screen at once. But what you learn there doesn't transfer to the others, so it's not where to begin.
Why SQLite first:
Then move to Supabase when people need to log in, or several people share data, or it needs to be online rather than on your laptop.
The jump is gentler than you'd expect, because Supabase is Postgres — the same SQL you already wrote keeps working. You're changing where the database lives, not how you talk to it.
SQL is how you talk to almost all of these. People find it intimidating for about ten minutes, until they notice it's nearly English.
There are four words that cover the overwhelming majority of what you'll ever do:
| Word | Means |
|---|---|
SELECT | show me |
INSERT | add a new one |
UPDATE | change an existing one |
DELETE | remove one |
Using that songs table from earlier:
-- show me everything
SELECT * FROM songs;
-- show me only the Mitski ones
SELECT * FROM songs WHERE artist = 'Mitski';
-- show me my favourites, best first
SELECT title, artist FROM songs
WHERE rating = 5
ORDER BY title;
-- add one
INSERT INTO songs (title, artist, rating)
VALUES ('Washing Machine Heart', 'Mitski', 5);
-- change one
UPDATE songs SET rating = 4 WHERE title = 'Nobody';
-- remove one
DELETE FROM songs WHERE title = 'Nobody';
Read those out loud. Select everything from songs where artist is Mitski. It's a sentence. That's the whole trick — SQL was deliberately designed to read like English, back when people hoped managers would write it themselves.
WHERE is the word doing the real work. It means "only the ones that match". Which brings us to the dangerous part.
DELETE FROM songs; doesn't delete a song. It deletes all of them. Instantly, with no confirmation and no undo.UPDATE songs SET rating = 1; sets every song in the table to one star.
This is the rm -rf of databases, and it has taken down real companies. It's usually someone tired, working on the live database, who ran the first half of a line before finishing the second half.
Two habits that make you basically immune:
WHERE title = 'Nobody' before you type the DELETE in front of it. Then a half-finished line can't do any damage.
SELECT with the exact same WHERE, and look at what comes back. Those are the rows you're about to destroy. If it's 4,000 rows and you expected one, you just saved yourself.
-- first, look
SELECT * FROM songs WHERE artist = 'Mitski';
-- 4 rows. That's what I expected.
-- now, delete
DELETE FROM songs WHERE artist = 'Mitski';
Ten extra seconds. Do it every time and it becomes automatic.
If your table has a column with passwords readable in it, and that database ever leaks, you've handed away every one of those accounts — and because people reuse passwords, probably their email too.
Real systems store a scrambled version that can be checked against but not reversed. Doing this correctly is fiddly and easy to get subtly wrong.
It goes in a .env file that stays on your computer. Your code reads it from there. Ship a .env.example listing only the names — Section 3 covers this.
If someone can type into a box and have their words become part of your SQL, they can write their own commands — including DELETE. It's called SQL injection and it's the oldest trick there is.
The fix is to pass values separately instead of gluing them into the text:
-- Dangerous: their text becomes part of the command
"SELECT * FROM songs WHERE artist = '" + whatTheyTyped + "'"
-- Safe: the value is handed over separately
"SELECT * FROM songs WHERE artist = ?", [whatTheyTyped]
That ? is the whole defence. Every database library supports it. Add this line to your AGENTS.md and it stops being something you have to remember:
Never build SQL by joining strings together. Always use
parameters (?) so user input can't become part of the command.
With SQLite, a backup is copying the file:
cp mystuff.db mystuff-backup-2026-09-21.db
With Supabase, backups happen for you — worth checking which plan you're on and how far back they go, before you need them.
DELETE, run the SELECT first. Do it ten times and it's permanent.