What a database is, and why SQL

By the end you'll know

Why data outgrows a spreadsheet, and what a database does instead
What tables, rows, columns and keys are, and how tables link together
What SQL is, and why you describe the answer instead of the steps
How to start a real Postgres database on your own computer
How to see what's in it, and ask it a first question

Before you start

You need: Docker Desktop (or Docker on Linux), and Git, for the practice files.
Where commands run: in your own computer's terminal. Postgres runs in a container, so there's nothing to install and nothing to break.
Quick check: docker --version and git --version should each print a version. Docker Desktop must be running, not just installed: docker ps should print a header line, not an error.

The problem: the spreadsheet that grew up

CivicGrid, the civic-data API I run, started as a spreadsheet: us_mayors_updated.xlsx, a few hundred kilobytes of cities and their mayors. It's still on my laptop:

OUTPUT

-rw-r--r--@ 1 you  staff   299K Apr  1  2026 /Users/you/Apps/SQL/us_mayors_updated.xlsx

A spreadsheet was the right way to start. It stopped being right when the questions changed. Not "who's the mayor of this city?", but "which cities have no current mayor?", "which mayor appears in two cities?", "which phone number is shared by two different town halls?". And when more than one thing needed the data at once: a website, an API, a weekly checker, me fixing records by hand. A spreadsheet can't stop someone typing a phone number into the population column, can't let two programs change it safely at the same time, and can't tell you that the copy you're looking at is three weeks old.

Think of a spreadsheet as a notebook, and a database as a library with a librarian. In the notebook, you can write anything anywhere, and finding things means flipping pages yourself. In the library, every book has a place and a catalogue number, the librarian refuses a book with no title, many people can borrow at once without tearing pages out of each other's hands, and you find things by asking: "every book by this author, newest first". You don't walk the shelves. The librarian works out how.

That's what CivicGrid's data lives in now: Postgres, a database. Almost every app you use keeps its data the same way.

What a database is made of

A database keeps data in tables. A table is like one sheet of a spreadsheet, with one big difference: its shape is fixed in advance, and the database enforces it.

Each column has a name and a type: whole numbers, text, dates. The database won't put text in a number column.
Each row is one thing: one town, one person, one meeting.
A primary key is a column whose value is unique for every row, so each row can be named exactly. Usually it's a number called id.
A foreign key is a column that points at another table's primary key. It's how tables link together: a leader row says which town it belongs to by holding that town's id.

Splitting data into linked tables, instead of one wide sheet, means each fact is stored once. A town's phone number lives in one row, however many leaders and meetings that town has. Change it once, and it's changed everywhere. This way of organizing data is called relational, and databases built on it, like Postgres, MySQL, SQLite and SQL Server, are relational databases. Not every database works this way: Redis, which CivicGrid uses for its cache and rate limits, just stores values under names, with no tables at all. Lesson 14 looks at those other kinds, and when they're the better tool.

What SQL is

SQL (Structured Query Language, often said "sequel") is the language you use to talk to a relational database. Almost every relational database speaks it, with small differences in dialect.

The key idea: in SQL, you describe what you want, not how to get it.

SQL

SELECT name, population FROM towns ORDER BY population DESC LIMIT 3;

reads almost like English: "the name and population from towns, biggest population first, just three." Nowhere does it say which file to open, which rows to read first or how to sort. The database works that out, and it can do it in a different, faster way tomorrow without you changing a word. That's the librarian. Languages that work this way are called declarative; Python, where you write each step, is imperative.

Larkspur County: your practice database

Every lesson in this track uses the same database: Larkspur County, a made-up county with twelve towns, their leaders, and their council meetings. Every name, phone number and website in it is invented. And like all real data, it has a few mistakes in it, on purpose. Later lessons find them.

Download the practice files, on your own computer, into the folder where you keep projects (such as ~/Apps):

BASH

git clone https://github.com/Kwamib/sql-practice.git
cd sql-practice

Start Postgres in a container, with this folder inside it at /practice:

BASH

docker run -d --name larkspur -e POSTGRES_PASSWORD=practice -v "$PWD":/practice postgres:16

-d runs it in the background. Postgres is a server: a program that waits for requests, and keeps running until you stop it.
--name larkspur gives the container a name, so later commands can find it.
-e POSTGRES_PASSWORD=practice sets the admin password. The image refuses to start without one. It's a practice password for a practice database; never use one like it for real.
-v "$PWD":/practice puts this folder inside the container, so Postgres can read larkspur.sql.

The first time, Docker downloads the image. Then it prints a long ID: the container is running. Give Postgres a few seconds to start, then load the county:

BASH

docker exec larkspur psql -U postgres -f /practice/larkspur.sql

OUTPUT

DROP DATABASE
psql:/practice/larkspur.sql:6: NOTICE:  database "larkspur" does not exist, skipping
CREATE DATABASE
You are now connected to database "larkspur" as user "postgres".
CREATE TABLE
CREATE TABLE
CREATE TABLE
INSERT 0 12
INSERT 0 20
INSERT 0 30

psql is Postgres's own command-line client: the program you type into, which sends your SQL to the server and shows the answer. docker exec larkspur runs it inside the container, -U postgres connects as the admin user, and -f runs a file of SQL. The file first throws away any old copy of the database, which is the NOTICE the first time, since there wasn't one, then builds it fresh: three tables, then 12 towns, 20 leaders and 30 meetings. Run it again whenever you want to start over.

Ask your own database

Every command below runs one thing with -c and prints the answer. First, what is it?

BASH

docker exec larkspur psql -U postgres -d larkspur -c 'SELECT version();'

OUTPUT

                                                          version                                                           
----------------------------------------------------------------------------------------------------------------------------
 PostgreSQL 16.15 (Debian 16.15-1.pgdg13+2) on aarch64-unknown-linux-gnu, compiled by gcc (Debian 14.2.0-19) 14.2.0, 64-bit
(1 row)

PostgreSQL 16, running on Linux (Debian) inside the container. aarch64 is the processor type of a Mac with Apple Silicon; on most Windows and Intel machines it says x86_64. Even a question about the server is answered as a table: one column, one row.

What tables are there? Commands starting with a backslash aren't SQL. They're shortcuts built into psql. \dt lists tables:

BASH

docker exec larkspur psql -U postgres -d larkspur -c '\dt'

OUTPUT

          List of relations
 Schema |   Name   | Type  |  Owner   
--------+----------+-------+----------
 public | leaders  | table | postgres
 public | meetings | table | postgres
 public | towns    | table | postgres
(3 rows)

What's the shape of one? \d describes a table:

BASH

docker exec larkspur psql -U postgres -d larkspur -c '\d towns'

OUTPUT

                 Table "public.towns"
   Column   |  Type   | Collation | Nullable | Default 
------------+---------+-----------+----------+---------
 id         | integer |           | not null | 
 name       | text    |           |          | 
 kind       | text    |           |          | 
 population | integer |           |          | 
 founded    | integer |           |          | 
 website    | text    |           |          | 
 phone      | text    |           |          | 
Indexes:
    "towns_pkey" PRIMARY KEY, btree (id)
Referenced by:
    TABLE "leaders" CONSTRAINT "leaders_town_id_fkey" FOREIGN KEY (town_id) REFERENCES towns(id)
    TABLE "meetings" CONSTRAINT "meetings_town_id_fkey" FOREIGN KEY (town_id) REFERENCES towns(id)

Everything from the section above, as the database sees it:

Seven columns, each with a type: integer for whole numbers, text for text.
id is the primary key (PRIMARY KEY), so it's not null: every town must have one, and no two can share it.
Referenced by lists the foreign keys pointing here: leaders and meetings both have a town_id that must be a real town's id.
Every other column may be empty. Nothing stops a town having no name. That's deliberate, for now, and Lesson 6 fixes it.

And what's in it? SELECT * means "every column":

BASH

docker exec larkspur psql -U postgres -d larkspur -c 'SELECT * FROM towns;'

OUTPUT

 id |     name      |  kind   | population | founded |            website             |     phone      
----+---------------+---------+------------+---------+--------------------------------+----------------
  1 | Ashford       | city    |      48210 |    1851 | https://www.ashfordcity.gov    | (555) 010-1100
  2 | Ashford       | village |       1904 |    1888 |                                | (555) 010-2200
  3 | Brennan Falls | city    |      21544 |    1867 | https://www.brennanfalls.gov   | (555) 010-3300
  4 | Cedar Hollow  | town    |       8390 |    1902 | http://cedarhollow.larkspur.us | (555) 010-4400
  5 | Dunmore       | town    |            |    1879 | https://www.dunmoretown.gov    | (555) 010-4400
  6 | Elk Ridge     | village |       2710 |    1911 | https://www.elkridge.gov       | (555) 010-6600
  7 | Fairview      | city    |      33102 |    1859 | https://www.fairview.gov       | 555-010-7700
  8 | Glen Oak      | town    |      11876 |    1884 | https://www.glenoak.gov        | (555) 010-8800
  9 | Harlow        | town    |       6045 |    1896 | https://www.harlowtown.gov     | (555) 010-9900
 10 | Juniper       | village |       1288 |    1920 |                                | 
 11 | Kestrel Bay   | city    |      27450 |    1871 | https://www.kestrelbay.gov     | (555) 011-1100
 12 | Linden        | town    |       9734 |    1890 | https://lindentown.com         | (555) 011-2200
(12 rows)

Twelve towns. Look closely, the way you'd look at real data for the first time, and you can already spot things:

Two towns are called Ashford: a city and a village. Only their id tells them apart. CivicGrid has the same problem across the U.S., which is why its rule is "never identify a city by name and state".
Some boxes are empty. Dunmore has no population; the Ashford village and Juniper have no website. An empty box in a database is called NULL, and it means "unknown", which is not the same as zero.
Cedar Hollow and Dunmore have the same phone number, and Fairview's is written differently from all the others.

None of that is wrong SQL. It's what real data looks like. Finding it with queries, instead of by eye, is a big part of this track.

A first real question, and a surprise

The three biggest towns:

BASH

docker exec larkspur psql -U postgres -d larkspur -c 'SELECT name, population FROM towns ORDER BY population DESC LIMIT 3;'

OUTPUT

   name   | population 
----------+------------
 Dunmore  |           
 Ashford  |      48210
 Fairview |      33102
(3 rows)

Dunmore came first, with no population at all. Postgres treats NULL as bigger than any number when it sorts, so "biggest first" puts the unknown on top. The query did exactly what it said; it just didn't say what you meant. Lesson 3 shows the one-word fix, and Lesson 8 is all about NULL. For now, it's the most useful habit in this track, on day one: read the answer, don't just accept it.

And a typo:

BASH

docker exec larkspur psql -U postgres -d larkspur -c 'SELECT nmae FROM towns;'

OUTPUT

ERROR:  column "nmae" does not exist
LINE 1: SELECT nmae FROM towns;
               ^
HINT:  Perhaps you meant to reference the column "towns.name".

The database knows every column's name, so it stops before doing anything, points at the mistake with ^, and suggests a fix. That's the fixed shape paying off: a spreadsheet would happily have made you a new column called nmae.

In real systems

Most apps don't talk to the database through psql. They send the same SQL from code, through a library: CivicGrid's API does it from Python. Lesson 12 covers that, and the attack it opens the door to.
A database is usually a server on its own machine, or a managed service. CivicGrid's Postgres is run by Supabase, and Amazon's version is RDS. The SQL is the same.
Spreadsheets still have their place: quick one-off analysis, and sharing a small table with people who don't write SQL. The usual pattern is the database as the source of truth, and a spreadsheet as an export of it.

Spot the problem

Work out what's wrong before you read the explanation.

The two copies. A team keeps its list of town halls in a shared spreadsheet. Someone downloads a copy to clean up the phone numbers, takes a week, and uploads it back. Meanwhile, two colleagues have added new towns to the shared one. What happens? The upload replaces the shared sheet, and the two new towns are gone, with nothing to say they ever existed. A database avoids this by keeping one copy, which everyone changes in place, one change at a time. Lesson 7 covers changing data safely, including how to undo it.

Keep this

To	Run
Start the practice database	`docker run -d --name larkspur -e POSTGRES_PASSWORD=practice -v "$PWD":/practice postgres:16`, in `sql-practice`
Load, or reload, Larkspur County	`docker exec larkspur psql -U postgres -f /practice/larkspur.sql`
Run one command	`docker exec larkspur psql -U postgres -d larkspur -c 'SQL'`
Which Postgres is this?	`SELECT version();`
List the tables	`\dt`
Describe a table	`\d towns`
Every column, every row	`SELECT * FROM towns;`
Some columns, sorted, a few rows	`SELECT name, population FROM towns ORDER BY population DESC LIMIT 3;`

Where you are now

You know what a database is made of, what SQL is for, and you have a real Postgres database of Larkspur County running on your own computer. Lesson 2 gets comfortable with it: working inside psql instead of one command at a time, the commands that find your way around, and what happens to your data when the container stops, restarts or is deleted.

Check yourself

What is a primary key?
leaders has a town_id column holding a town's id. What is it?
What does it mean that SQL is declarative?
What's the difference between Postgres and psql?
ORDER BY population DESC put Dunmore first, with no population. Why?
Two towns are called Ashford. How does the database tell them apart?

Key terms

Database: organized data kept by a program that enforces its shape and answers questions about it.
Table, row, column: a set of things with the same shape; one thing; one fact about each thing, with a type.
Primary key: a column whose value is unique for every row, so each row can be named exactly.
Foreign key: a column pointing at another table's primary key, linking the two.
Relational database: a database of linked tables, like Postgres, MySQL, SQLite or SQL Server.
SQL: Structured Query Language, for asking a relational database questions and changing its data.
Server and client: Postgres, which keeps the data and answers; and psql, which you type into.
NULL: an empty value, meaning "unknown". Not zero, and not empty text.

Go deeper

The PostgreSQL tutorial, Getting Started (opens in a new tab) and The SQL Language (opens in a new tab) chapters.
Try SELECT * FROM leaders; and SELECT * FROM meetings;. What looks odd?
Lesson 1 of the Docker track, What containers solve, for what docker run is doing.