Program
Databases
Every app you use runs on a database. Learn how they work from the first table to a million users, and build a real app on one as you go.
1.Where data lives
You close the app on your phone but your data is still there. Where did it go?
- Start
You post a photo and your friend sees it a minute later. Where was it in between?
- How does your photo reach your friend?
- What survives a restart?
- Finding your photo among millions
- The two jobs of a database
- How a server asks for your photos
Some data fits a neat grid and some doesn't. Meet the two kinds of database built for each.
- Two kinds of database
- Data that fits a grid
- Data that doesn't fit a grid
- Which kind do most apps use?
What does a database look like inside? Open one table and see how every row, column and cell fits together.
- What goes in a row?
- Where a row meets a column
Why won't a database store a word where a number belongs? See how types keep your data clean.
- Why every column has a type
- Stopping a name that's too long
- Why money needs exact numbers
2.Your first table
Every app you use started as one empty table. Time to build yours.
Two users have the same name. How does the database know which one you mean?
- Telling two rows apart
- Who picks the id?
- Why not the email?
Someone skips a field at sign-up. What gets stored, and how do you decide which fields can't be skipped?
- What goes in an empty cell?
- How Postgres requires a value
- How Postgres fills in a value
How does a database know its rules before the first row exists? Write them down in the language it reads.
- Rules before the first row
- The language Postgres reads
- Writing your first schema
Set up a real Postgres database on Supabase, and hand it the users table's schema.
A real Postgres database of your own, with Simplified's users table in it.
6 steps · about 15 min · on supabase.com
3.Reading data
Your table holds millions of rows and you only want three of them. How do you ask for just those?
A table can have dozens of columns when you only need two. Learn to ask for just those.
- Asking for certain columns
- Asking for every column
Your table holds millions of rows. How do you ask for only the one you want?
- Finding one row among millions
- Why text needs quotes
- When no row passes
Writing and running your own SELECT queries on Simplified's videos table, in a real Postgres database in your browser.
Write the queries Simplified's server sends for its videos, and get back exactly the rows it needs.
6 steps · about 8 min · in your browser
Maya's site has every page and no database. Make its pages show real posts from a table you create.
Chirp's pages show rows from your database instead of sample data.
8 steps · about 45 min · on Your editor
How does a filter find everything cheaper, later or bigger? Learn to compare values, and what to do when one is missing.
- Greater than and less than
- Greater than or equal to
- Not equal to
- Why equals can't find NULL
One condition is rarely enough. Combine them to find rows that match both, either, a range or a list.
- When both must be true
- Asking for a range
- When either will do
- Matching a list of values
Writing the WHERE for each question Simplified's team asks about its videos: ranges, either-or, and lists.
Write the condition for each question, and get back exactly the videos that pass it.
7 steps · about 10 min · in your browser
How does a search box find a name from the first few letters you type? Match text by pattern, whatever the capitals.
- How a search box finds a name
- Exactly one more character
- When capitals shouldn't matter
Rows come back in no particular order unless you ask. Sort them, break ties, and keep only the top few.
- Putting rows in order
- Who comes first in a tie?
- Just the first few rows
Finding videos by a pattern in their title, then sending them back in order, and only the first few.
Find videos by a pattern, and send them back in the order the team asks for.
6 steps · about 10 min · in your browser
Do you really need a million rows to show one number? Let the database count, add and average for you.
- One number from millions of rows
- Adding up a column
- Finding the average
- One count for each group
Getting each total Simplified's team asks for from Postgres in one query: a count, a total, an average, and one number per group.
Answer each of the team's questions with one query, and let Postgres do the counting.
6 steps · about 10 min · in your browser
Maya's members want the newest post on top, a search box and their likes added up. Let Postgres do all of it.
Every list and number on Chirp is sorted, searched, filtered or counted by Postgres.
7 steps · about 60 min · on Your editor
4.Changing and linking data
Users edit, delete and follow each other all day. Learn to change your data and connect one table to another.
Someone signs up, posts or places an order. Here's how that new row gets into the table.
- Adding a new row
- Three rows in one go
- Which id did Postgres pick?
Writing the INSERT that adds each new video to Simplified: one row, three at once, and one whose id comes back.
Add new videos to the videos table, and get back what Postgres filled in for them.
5 steps · about 10 min · in your browser
Edit a row, delete a row, and avoid the one mistake that changes every row at once.
- Changing a value in a row
- Taking a row out for good
- What a missing WHERE does
- How many rows did that change?
Writing the UPDATE and DELETE Simplified's server sends for its videos, so each one hits only the row it means.
Change and remove exactly the rows you mean, and read what Postgres reports back.
6 steps · about 12 min · in your browser
Let Chirp write a post and open it on its own page, then edit it and delete it.
Chirp's compose box writes real posts, and you can edit and delete your own.
7 steps · about 35 min · on Your editor
What happens to your old comments when you change your username? See why data is split across tables, and how they stay linked.
- Why one table isn't enough
- Pointing at a row that exists
- Which table gets the foreign key?
- Whose id goes in the new row?
The name is in one table and the comment in another. How do you read them side by side?
- Which table's id do you mean?
- Reading two tables side by side
- Filtering the joined rows
Saving a new video under the right lesson, then reading videos beside lessons in one query.
Save a video under the right lesson, and read across two tables in one query.
7 steps · about 15 min · in your browser
A playlist has many songs, and a song sits in many playlists. Where does that link get stored?
- When neither table can hold the key
- A third table for the pairs
Saving each learner and video that go together as a row in watches, then reading that join table from both sides.
Save who watched which video in a join table, and read it from both sides.
6 steps · about 12 min · in your browser
Give Chirp a users table, posts that point at their authors, and follows, then a feed of the people you follow.
Chirp with its own users, posts that point at their authors, and a feed of the people you follow.
8 steps · about 45 min · on Your editor
Where do an app's tables come from? Find them in its screens, then turn a messy spreadsheet into clean ones.
- Where do an app's tables come from?
- One value per cell
- Turning a spreadsheet into tables
Design Chirp's comments table yourself, then show comments under each post, add your own, and count them on every post card.
Comments under every post on Chirp, from a table you designed, with a count on each post card.
7 steps · about 40 min · on Your editor
5.Data you can trust
One bad row can break an app. What if the database simply refused to store it?
Two people try to sign up with the same email. How does the database make sure only one gets it?
- Rules Postgres checks on every write
- Two sign-ups, one email
- The two rules in every key
A user deletes their account. What should happen to everything they posted?
- Deleting a row others point at
- Deleting the child rows too
- When a comment outlives its author
The database just refused your write. Learn to read its error and find the rule you broke.
- How a refused write reports back
- What if only one row breaks a rule?
- Why not check in the server's code?
- Tracing an error to its rule
Let people delete their accounts, with rules for what goes with them, and refuse a handle someone already has.
Sign-up that refuses a taken handle, and Delete account that takes a person's posts and leaves their comments as [deleted].
8 steps · about 45 min · on Your editor
6.Building for many users
It worked for a handful of users. What happens when a crowd arrives at once?
How does an app find your account among billions in an instant? Meet the index, and what it costs.
- Reading every row to find a few
- A sorted copy of one column
- Going straight to the right rows
- Why not index every column?
An index doesn't speed up every search. Learn which ones it helps, and how to check for yourself.
- Which searches does an index speed up?
- Is every login a full scan?
- The index you add yourself
- Checking without guessing
Give Chirp half a million posts, find the search that reads them all, and fix it with an index.
Chirp with 500,000 posts, and a profile page that goes straight to a person's posts with an index.
5 steps · about 35 min · on Your editor
You send money and the app crashes halfway. Did it leave your account but never arrive?
- What if it fails halfway?
- Making two writes one
- Taking back what you already sent
An error or a crash hits halfway through a write. What does the database keep, and what does it throw away?
- When one statement is refused
- When can an app say "done"?
- A crash before it reaches disk
- How a change survives a crash
Two people tap like at the same instant, and only one counts. Why, and how do you stop it?
- What other requests see
- Two likes at the same moment
- Let the database do the math
- Selling the last ticket once
Sometimes the only safe answer is to make one request wait for another. See how a lock does it.
- Making the second write wait
- Making the second read wait
Give Chirp a like button, watch 50 likes at once lose some of the count, and fix it in the database.
A like button that adds the like and the count together, and loses none when 50 people press it at once.
7 steps · about 40 min · on Your editor
7.A million users later
One machine was plenty, until it wasn't. Learn to spread your database across many.
Your whole app depends on one database machine. What happens the day it dies?
- Many servers, one database
- What if the database server dies?
- Rows that outlive their machine
A copy is only useful if it's up to date. How does every change reach it, and why can only one machine take writes?
- How a replica gets every change
- Why only one machine takes writes
- What if a write goes to the copy?
Most of what users do is read. Share that work across copies, and meet the moment a copy falls behind.
- Sharing the reads
- Does a write wait for the copies?
- Why your new comment vanishes
You post a comment, refresh, and it's gone. Where did it go, and how do apps make sure you see it?
- One like count, two numbers
- Seeing what you just did
The main database machine just died. How does a copy take over, and can anything get lost on the way?
- Promoting a replica
- The commit that never arrived
- Losing nothing in a failover
- What does waiting cost?
What do you do when a table grows too big for any one machine? Split it.
- When a table outgrows one machine
- Which partition gets the row?
- When one partition does all the work
Split a table badly and one machine does all the work. Spread the rows so every machine shares the load.
- Spreading new rows around
- No partition left idle
- Which partitions does a query visit?
8.Databases in the real world
A slow page, a stranger typing into your login box, and one accidental delete. Is your database ready?
Every query needs a line to the database, and there aren't many to go round. Learn to share them.
- How a query reaches Postgres
- Dial once, borrow after
- How many connections is too many?
Why does one page send a hundred queries, and why is it so slow? Replace them with one.
- One page, a hundred queries
- Why every query adds to the wait
- One query instead of a hundred
See every query Chirp's feed sends, and cut its comment counts from one query per post to one.
Chirp's feed, getting every comment count in one query instead of one per post.
6 steps · about 30 min · on Your editor
What if a stranger could break into your login box by typing one quote mark? See the attack, then shut it out.
- How your typing ends up in a query
- What if someone types a quote?
- Input that can never become SQL
Someone deletes the wrong rows. Can you get them back, and could you have stopped them?
- Letting each app do only its job
- Why a replica isn't a backup
- Getting yesterday's rows back
Your table already holds millions of rows and now it needs a new column. How do you change it safely, everywhere?
- Adding a column to a live table
- The same change on every database
Add a bio to Chirp's profiles with a migration, then put Chirp on the internet.
Chirp live on the internet, with a bio on every profile, added by a migration.
7 steps · about 45 min · on Your editor
Not all data fits neat columns. What do you do when every record has a different shape?
- When data doesn't fit columns
- Storing a document in a column
