Simplified
Simplified

Chirp: people and follows

SQL → Supabase

The SQL you know, and the same query with Supabase's library.

Every query

const { data, error } = await supabase
  .from("posts")
  .select("body, likes");
if (error) throw new Error(error.message);

The rows come back as data. If Postgres refuses the query, error says why.

From Chirp: real posts ↗

Picking columns and rows

SELECT body, likes FROM posts;
↓Supabase's library
.from("posts").select("body, likes")
From Picking Columns ↗
SELECT * FROM posts;
↓Supabase's library
.from("posts").select("*")
From Picking Columns ↗
WHERE id = 20
↓Supabase's library
.eq("id", 20)
From Picking Rows ↗
WHERE likes > 100
↓Supabase's library
.gt("likes", 100)
From Comparing Values ↗
WHERE likes >= 100
↓Supabase's library
.gte("likes", 100)
From Comparing Values ↗
WHERE likes < 100
↓Supabase's library
.lt("likes", 100)
From Comparing Values ↗
WHERE likes <= 100
↓Supabase's library
.lte("likes", 100)
From Comparing Values ↗
WHERE likes <> 0
↓Supabase's library
.neq("likes", 0)
From Comparing Values ↗
WHERE name IS NULL
↓Supabase's library
.is("name", null)

Not .eq("name", null): like = NULL in SQL, it finds no row.

From Comparing Values ↗
WHERE name IS NOT NULL
↓Supabase's library
.not("name", "is", null)
From Comparing Values ↗
WHERE likes > 100 AND id < 10
↓Supabase's library
.gt("likes", 100).lt("id", 10)

AND is two filters, one after the other.

From Combining Conditions ↗
WHERE likes BETWEEN 50 AND 100
↓Supabase's library
.gte("likes", 50).lte("likes", 100)

BETWEEN keeps both ends, so it is >= and <=.

From Combining Conditions ↗
WHERE likes > 300 OR likes < 10
↓Supabase's library
.or("likes.gt.300,likes.lt.10")

OR takes one string: each condition as column.filter.value, split by commas.

From Combining Conditions ↗
WHERE id IN (1, 4, 20)
↓Supabase's library
.in("id", [1, 4, 20])
From Combining Conditions ↗

Matching text

WHERE body LIKE '%SQL%'
↓Supabase's library
.like("body", "%SQL%")
From Matching Text ↗
WHERE body ILIKE '%sql%'
↓Supabase's library
.ilike("body", "%sql%")

% and _ mean the same as in SQL.

From Matching Text ↗

Sorting

ORDER BY created_at
↓Supabase's library
.order("created_at")
From Sorting ↗
ORDER BY created_at DESC
↓Supabase's library
.order("created_at", { ascending: false })
From Sorting ↗
ORDER BY likes DESC, created_at
↓Supabase's library
.order("likes", { ascending: false })
.order("created_at")

The second .order() breaks ties, like the second column after the comma.

From Sorting ↗
LIMIT 10
↓Supabase's library
.limit(10)
From Sorting ↗

Counting

SELECT COUNT(*) FROM posts WHERE likes > 100;
↓Supabase's library
const { count, error } = await supabase
  .from("posts")
  .select("*", { count: "exact", head: true })
  .gt("likes", 100);

Read count, not data. head: true sends back no rows, only the number.

From Counting ↗
SELECT SUM(likes) FROM posts;
↓Supabase's library
.select("likes.sum()").single()

Comes back as data.sum. SUM, AVG and GROUP BY need two lines run once in the SQL Editor: Chirp: find and count posts, step 6.

From Counting ↗
SELECT AVG(likes) FROM posts;
↓Supabase's library
.select("likes.avg()").single()

Comes back as data.avg.

From Counting ↗
SELECT handle, COUNT(*) FROM posts
GROUP BY handle;
↓Supabase's library
.select("handle, count()")

There is no .group(). The library groups by every plain column you select.

From Counting ↗

Adding, changing and removing rows

INSERT INTO posts (body) VALUES ('Hello');
↓Supabase's library
.from("posts").insert({ body: "Hello" })

Each key is a column. Columns you leave out get their default.

From Adding Rows ↗
INSERT INTO posts (body)
VALUES ('One'), ('Two');
↓Supabase's library
.insert([{ body: "One" }, { body: "Two" }])
From Adding Rows ↗
INSERT … RETURNING id;
↓Supabase's library
.insert({ body: "Hello" })
.select("id")
.single()

The new row's id comes back as data.id.

From Adding Rows ↗
UPDATE posts SET body = 'Edited'
WHERE id = 21;
↓Supabase's library
.from("posts")
.update({ body: "Edited" })
.eq("id", 21)

The filter is your WHERE. Never leave it out.

From Changing and Removing Rows ↗
DELETE FROM posts WHERE id = 21;
↓Supabase's library
.from("posts").delete().eq("id", 21)

The filter is your WHERE. Never leave it out.

From Changing and Removing Rows ↗
DELETE FROM posts WHERE id = 21;
-- Postgres reports: DELETE 1
↓Supabase's library
const { count, error } = await supabase
  .from("posts")
  .delete({ count: "exact" })
  .eq("id", 21);

count is how many rows the write changed. update() takes it as its second argument.

From Changing and Removing Rows ↗

Across tables

SELECT posts.body, users.handle
FROM posts
JOIN users ON users.id = posts.user_id;
↓Supabase's library
.from("posts").select("body, users(handle)")

The library finds the ON from the foreign key. Each post comes back with users: { handle }.

From Reading Across Tables ↗
… JOIN users ON users.id = posts.user_id
WHERE users.handle = 'ada';
↓Supabase's library
.select("body, users!inner(handle)")
.eq("users.handle", "ada")

!inner keeps only the posts whose user passes the filter.

From Reading Across Tables ↗

Only in the SQL Editor

CREATE TABLE posts ( … );
↓SQL Editor

The library reads and writes rows. Tables, indexes and their rules are made in SQL.

From The Schema ↗

A row appears here once a lesson has taught its SQL.

YOU

Chirp: people and follows

The goal

Chirp with its own users, posts that point at their authors, and a feed of the people you follow.

When you're done

  • ✓Every post shows the person who wrote it, read from a users table.
  • ✓A member can follow and unfollow someone.
  • ✓The Following feed shows only posts by people they follow.

What you're working toward

Chirp's Following page, signed in as you: posts by dee, fay and ada, newest first, starting with Dee's Deployed.
Time
About 45 min
Steps
8
Where
Your editor

You'll need

  • •Your chirp repository, with posts you can write, edit and delete
  • •Your Supabase project, simplified-practice

Puts into practice

  • Chirp: post, edit and delete
  • 2.3 The Schema
  • 3.4 Combining Conditions
  • 4.3 Two Tables
  • 4.4 Reading Across Tables
  • 4.5 Many-to-Many

Chirp's posts table holds each author's handle beside the post's words.

So Ada's handle, ada, is stored four times: once in each of her posts.

If Ada changed it, all four copies would have to change.

And Chirp can't say who follows whom: a person isn't a row anywhere.

So how does Chirp keep each person once, and know who follows whom?

The way Simplified does: a users table, posts that hold their author's id, and a join table.

In this checkpoint you rebuild Chirp's tables that way, and then its pages on top of them.

1

Step 1: Clear out the old tables

Chirp's new tables need the names posts and users.

Your project already has both: your posts, and the practice users table from your first project.

Chirp needs its own users table, so both go.

In Supabase's SQL Editor, paste these two lines, and press Run.

DROP TABLE posts;
DROP TABLE users;

DROP TABLE deletes a whole table: its rows and its schema. A later lesson, on keeping data safe, comes back to it.

Supabase may warn you that the query deletes something. Go ahead, as these tables are meant to go.

Then reload localhost:3000.

You should see

In the Table Editor, no tables. Chirp's home page shows an error like Could not find the table 'public.posts'.

2

Step 2: Make Chirp's three tables

users holds one row for each person on Chirp.

ColumnTypeRules
idintegerThe primary key, generated by Postgres
handletextNever NULL
namevarchar(50)Can be empty
created_attimestampNever NULL, and starts at now()

posts holds one row for each post, with its author's id instead of a handle.

ColumnTypeRules
idintegerThe primary key, generated by Postgres
user_idintegerNever NULL, and a foreign key to the id of users
bodyvarchar(280)Never NULL
likesintegerNever NULL, and starts at 0
created_attimestampNever NULL, and starts at now()

One person follows many people, and one person has many followers.

So follows is a join table: one row for each pair, and no id of its own.

ColumnTypeRules
follower_idintegerNever NULL, and a foreign key to the id of users: the person who follows
followee_idintegerNever NULL, and a foreign key to the id of users: the person they follow

Run each CREATE TABLE in the SQL Editor, users first. If Supabase asks about RLS, pick Run and enable RLS.

Your turn 1

Write the CREATE TABLE query for users, and run it.

A right answer gives

Success. No rows returned under the editor.

CREATE TABLE users (
  id         integer PRIMARY KEY GENERATED ALWAYS AS IDENTITY,
  handle     text NOT NULL,
  name       varchar(50),
  created_at timestamp NOT NULL DEFAULT now()
);

Your turn 2

Write the CREATE TABLE query for posts, with user_id pointing at users, and run it.

A right answer gives

Success. No rows returned under the editor.

CREATE TABLE posts (
  id         integer PRIMARY KEY GENERATED ALWAYS AS IDENTITY,
  user_id    integer NOT NULL REFERENCES users (id),
  body       varchar(280) NOT NULL,
  likes      integer NOT NULL DEFAULT 0,
  created_at timestamp NOT NULL DEFAULT now()
);

Your turn 3

Write the CREATE TABLE query for follows, and run it.

A right answer gives

Success. No rows returned under the editor.

CREATE TABLE follows (
  follower_id integer NOT NULL REFERENCES users (id),
  followee_id integer NOT NULL REFERENCES users (id)
);

You should see

In the Table Editor, three tables, users, posts and follows, each with no rows.

3

Step 3: Fill them

Paste this into the SQL Editor, and press Run once.

INSERT INTO users (handle, name, created_at) VALUES
  ('ada', 'Ada', '2026-10-20 09:00'),
  ('ben', 'Ben', '2026-10-21 10:00'),
  ('cy',  'Cy',  '2026-10-22 11:00'),
  ('dee', 'Dee', '2026-10-23 12:00'),
  ('eli', 'Eli', '2026-10-24 13:00'),
  ('fay', 'Fay', '2026-10-25 14:00'),
  ('you', 'You', '2026-10-26 15:00');

INSERT INTO posts (user_id, body, likes, created_at) VALUES
  (1, 'Day 1 of learning SQL. SELECT star feels like a superpower.', 42, '2026-11-01 09:15'),
  (2, 'Why did nobody tell me about flexbox sooner?', 87, '2026-11-01 14:22'),
  (3, 'Hello, world.', 5, '2026-11-02 07:50'),
  (4, 'Centering a div on the first try. Screenshot it.', 311, '2026-11-02 19:05'),
  (5, 'Semicolons: optional in JS, required in my heart.', 73, '2026-11-03 11:11'),
  (1, 'Shipped my first Next.js page today.', 118, '2026-11-03 18:40'),
  (2, 'Reading the docs is a skill. I am leveling up.', 16, '2026-11-04 10:10'),
  (4, 'React state finally clicked for me.', 96, '2026-11-04 20:48'),
  (6, 'First pull request merged!', 189, '2026-11-05 09:40'),
  (3, 'Git commit, git push, deep breath.', 29, '2026-11-05 16:16'),
  (1, 'Tailwind makes me feel like a designer.', 250, '2026-11-06 12:05'),
  (6, 'Naming things is the hardest part.', 47, '2026-11-06 18:18'),
  (4, 'Sql joins are next on my list.', 58, '2026-11-07 09:27'),
  (2, 'my sql query finally returned the right rows', 64, '2026-11-07 21:30'),
  (5, 'Pair programming with a friend beats tutorials.', 22, '2026-11-08 15:35'),
  (3, 'Dark mode is not a feature. It is a lifestyle.', 204, '2026-11-08 22:11'),
  (1, 'Coffee, then code. In that order.', 31, '2026-11-09 08:02'),
  (6, 'Refactored it. Same behavior, half the code.', 12, '2026-11-09 20:55'),
  (2, 'TypeScript caught three bugs before lunch.', 173, '2026-11-10 13:45'),
  (4, 'Deployed. It works on more than my machine.', 142, '2026-11-10 17:20');

INSERT INTO follows (follower_id, followee_id) VALUES
  (7, 1), (7, 4), (7, 6),
  (1, 2), (2, 1), (3, 4), (4, 1), (5, 3), (6, 1);

The users go in first, because each post's user_id must match an id that's already in users.

They are the same 20 posts as before, with ada's id, 1, where ada's handle was.

And you are user 7, who follows ada, dee and fay.

Then reload localhost:3000.

You should see

In the Table Editor: users with 7 rows, posts with 20 and follows with 9. Chirp's home page shows an error that posts has no column handle.

4

Step 4: Read each post's author with a join

The feed still asks posts for a handle, and posts no longer has one.

The handle is in users, so the feed needs a JOIN.

SELECT posts.id, posts.body, posts.likes, posts.created_at, users.handle
FROM posts
JOIN users ON posts.user_id = users.id
ORDER BY posts.created_at DESC
LIMIT 10;

With Supabase's library, the join is the table's name inside the select:

SQL you knowSupabase's library
users.handle … JOIN users ON posts.user_id = users.id.select("…, users(handle)")
WHERE user_id IN (1, 4, 6).in("user_id", [1, 4, 6])
WHERE follower_id = 7 AND followee_id = 2.eq("follower_id", 7).eq("followee_id", 2)

You never write the ON. Supabase reads it from your foreign key, REFERENCES users (id).

Replace getPosts() with this.

export async function getPosts() {
  const { data, error } = await supabase
    .from("posts")
    .select("id, body, likes, created_at, users(handle)")
    .order("created_at", { ascending: false })
    .limit(10);
  if (error) throw new Error(error.message);
  return data;
}

Each post comes back with its author inside it: { id: 20, body: "Deployed. …", users: { handle: "dee" } }.

So in components/PostCard.tsx, change post.handle to post.users.handle.

You should see

Ten posts again, with Dee's "Deployed. It works on more than my machine." first, and dee above it.

5

Step 5: Your turn: move the rest onto users

Every other function in lib/data.ts still reads or writes a handle column in posts.

Most need one change: users(handle) in the select, or user_id in place of handle.

Two more places read a post's handle: the post page's Edit check, and components/MostActive.tsx. Change both to users.handle.

Your turn 1

Make getUsers() return every user's id and handle, in id order, for the Switch user menu in the sidebar.

Done when

Switch user lists seven people, from ada to you.

// SELECT id, handle FROM users ORDER BY id;
export async function getUsers() {
  const { data, error } = await supabase
    .from("users")
    .select("id, handle")
    .order("id");
  if (error) throw new Error(error.message);
  return data;
}

Your turn 2

Write getUserId(handle): the id of the user with that handle, or null.

Then make the profile's three functions look that id up, and pick the posts whose user_id matches it.

Done when

localhost:3000/ada says 4 posts · 441 likes, with "Coffee, then code. In that order." first.

localhost:3000/you says 0 posts, and localhost:3000/nobody says User not found.

// SELECT id FROM users WHERE handle = 'ada';
export async function getUserId(handle: string) {
  const { data, error } = await supabase
    .from("users")
    .select("id")
    .eq("handle", handle)
    .maybeSingle();
  if (error) throw new Error(error.message);
  return data?.id ?? null;
}

// SELECT posts.id, posts.body, posts.likes, posts.created_at, users.handle
// FROM posts JOIN users ON posts.user_id = users.id
// WHERE posts.user_id = 1 ORDER BY posts.created_at DESC;
export async function getPostsBy(handle: string) {
  const userId = await getUserId(handle);
  if (userId === null) return [];
  const { data, error } = await supabase
    .from("posts")
    .select("id, body, likes, created_at, users(handle)")
    .eq("user_id", userId)
    .order("created_at", { ascending: false });
  if (error) throw new Error(error.message);
  return data;
}

// SELECT COUNT(*) FROM posts WHERE user_id = 1;
export async function countPostsBy(handle: string) {
  const userId = await getUserId(handle);
  if (userId === null) return 0;
  const { count, error } = await supabase
    .from("posts")
    .select("*", { count: "exact", head: true })
    .eq("user_id", userId);
  if (error) throw new Error(error.message);
  return count ?? 0;
}

// SELECT SUM(likes) FROM posts WHERE user_id = 1;
export async function getLikesTotal(handle: string) {
  const userId = await getUserId(handle);
  if (userId === null) return 0;
  const { data, error } = await supabase
    .from("posts")
    .select("likes.sum()")
    .eq("user_id", userId)
    .single();
  if (error) throw new Error(error.message);
  return data.sum ?? 0;
}

Your turn 3

Move the rest: getPost(), searchPosts(), getTrending() and getMostActive() read each author's handle from users.

And createPost() saves the signed-in user's id, which getMe() now returns as id.

Done when

Searching SQL finds Ben's, Dee's and Ada's posts. Trending runs from Dee's 311 likes down to Ben's 173.

Most active lists ada 4, ben 4, cy 3, dee 4, eli 2 and fay 3, in any order.

Writing Chirp has real people now. lands on localhost:3000/post/21, by you.

Then delete it, so your feed shows the same 20 posts as everyone else's from here on.

// SELECT posts.id, posts.body, posts.likes, posts.created_at, users.handle
// FROM posts JOIN users ON posts.user_id = users.id WHERE posts.id = 20;
export async function getPost(id: number) {
  const { data, error } = await supabase
    .from("posts")
    .select("id, body, likes, created_at, users(handle)")
    .eq("id", id)
    .maybeSingle();
  if (error) throw new Error(error.message);
  return data;
}

// SELECT posts.id, posts.body, posts.likes, posts.created_at, users.handle
// FROM posts JOIN users ON posts.user_id = users.id
// WHERE posts.body ILIKE '%sql%' ORDER BY posts.created_at DESC;
export async function searchPosts(q: string) {
  const { data, error } = await supabase
    .from("posts")
    .select("id, body, likes, created_at, users(handle)")
    .ilike("body", `%${q}%`)
    .order("created_at", { ascending: false });
  if (error) throw new Error(error.message);
  return data;
}

// SELECT posts.id, posts.body, posts.likes, users.handle
// FROM posts JOIN users ON posts.user_id = users.id
// ORDER BY posts.likes DESC LIMIT 5;
export async function getTrending() {
  const { data, error } = await supabase
    .from("posts")
    .select("id, body, likes, users(handle)")
    .order("likes", { ascending: false })
    .limit(5);
  if (error) throw new Error(error.message);
  return data;
}

// SELECT users.handle, COUNT(*)
// FROM posts JOIN users ON posts.user_id = users.id
// GROUP BY users.handle;
export async function getMostActive() {
  const { data, error } = await supabase
    .from("posts")
    .select("count(), users(handle)");
  if (error) throw new Error(error.message);
  return data;
}

// INSERT INTO posts (user_id, body) VALUES (7, 'Chirp has real people now.') RETURNING id;
export async function createPost(body: string) {
  const me = await getMe();
  const { data, error } = await supabase
    .from("posts")
    .insert({ user_id: me.id, body })
    .select("id")
    .single();
  if (error) throw new Error(error.message);
  return data.id;
}

You should see

Every page of Chirp loads with no error. editPost() and deletePost() needed no change, because their WHERE is on the post's id.

Ada's profile: 4 posts · 441 likes and a Following button, then her posts, newest first, starting with Coffee, then code. In that order.
6

Step 6: Your turn: follow and unfollow

A profile shows Follow, or Following if you already follow that person.

The button calls three functions, which still return sample data. Each takes the id of the person on the profile.

Your turn 1

Make follow(userId) save that you follow that person, and unfollow(userId) remove it.

Done when

On localhost:3000/ben, pressing Follow adds a row to follows, 7 and 2. Pressing it again takes that row out.

// INSERT INTO follows (follower_id, followee_id) VALUES (7, 2);
export async function follow(userId: number) {
  const me = await getMe();
  const { error } = await supabase
    .from("follows")
    .insert({ follower_id: me.id, followee_id: userId });
  if (error) throw new Error(error.message);
}

// DELETE FROM follows WHERE follower_id = 7 AND followee_id = 2;
export async function unfollow(userId: number) {
  const me = await getMe();
  const { error } = await supabase
    .from("follows")
    .delete()
    .eq("follower_id", me.id)
    .eq("followee_id", userId);
  if (error) throw new Error(error.message);
}

Your turn 2

Make isFollowing(userId) say whether you follow that person, from a count Postgres makes.

Done when

Ada's profile says Following, and Ben's says Follow.

// SELECT COUNT(*) FROM follows WHERE follower_id = 7 AND followee_id = 1;
export async function isFollowing(userId: number) {
  const me = await getMe();
  const { count, error } = await supabase
    .from("follows")
    .select("*", { count: "exact", head: true })
    .eq("follower_id", me.id)
    .eq("followee_id", userId);
  if (error) throw new Error(error.message);
  return (count ?? 0) > 0;
}

You should see

Follow Ben: his button says Following, and follows has 10 rows. Unfollow him: Follow again, and 9 rows.

7

Step 7: Your turn: the Following feed

localhost:3000/following shows posts by the people you follow. It calls getFollowingFeed(), which still returns sample data.

This one takes two queries: first the ids of the people you follow, then their posts.

Your turn 1

Make getFollowingFeed() return every post by the people you follow, newest first, each with its author's handle.

Done when

localhost:3000/following lists 11 posts by ada, dee and fay, from Dee's "Deployed" down to Ada's "Day 1 of learning SQL".

Follow Ben, and it lists 15, with his "TypeScript caught three bugs before lunch." second.

// SELECT followee_id FROM follows WHERE follower_id = 7;
// SELECT posts.id, posts.body, posts.likes, posts.created_at, users.handle
// FROM posts JOIN users ON posts.user_id = users.id
// WHERE posts.user_id IN (1, 4, 6) ORDER BY posts.created_at DESC;
export async function getFollowingFeed() {
  const me = await getMe();
  const { data: follows, error: followsError } = await supabase
    .from("follows")
    .select("followee_id")
    .eq("follower_id", me.id);
  if (followsError) throw new Error(followsError.message);

  const ids = follows.map((row) => row.followee_id);
  const { data, error } = await supabase
    .from("posts")
    .select("id, body, likes, created_at, users(handle)")
    .in("user_id", ids)
    .order("created_at", { ascending: false });
  if (error) throw new Error(error.message);
  return data;
}

You should see

Use Switch user to become ben: his Following shows Ada's four posts, as ben follows only ada.

Switch back to you, and unfollow Ben, so follows holds its 9 rows again.

8

Step 8: Save your work

Commit your changes, and push them to GitHub.

git add -A
git commit -m "Users, follows and the Following feed"
git push

You should see

Your commit on GitHub, with the new lib/data.ts in it.

Creating posts says relation "users" does not exist

posts points at users, so users must exist first. Run the CREATE TABLE for users, then the one for posts.

Every post card shows no handle, or an error about handle

The handle now arrives inside users. Read post.users.handle, not post.handle.

Writing a post says column "handle" of relation "posts" does not exist

createPost() still saves a handle. Save user_id: me.id instead.

An error that says Use of aggregate functions is not allowed

Checkpoint 2's two lines haven't run in this project. Run them in the SQL Editor again.

Following shows posts by everyone

The posts query has no .in("user_id", ids), so nothing picks the rows. Add it before .order(…).

Following someone says violates foreign key constraint "follows_followee_id_fkey"

That id matches no row in users. Pass the id from getUserId(handle), not a handle or a made-up number.

My tables have twice as many rows

Step 3's query ran twice. Do step 1 again, adding DROP TABLE follows; as its first line, then steps 2 and 3.

Supabase says your project is paused

Free projects pause after a week with no activity. Press Restore project on its page, and wait a few minutes.

Something is wrong, and you can't tell where

Run the SQL from the solution's first lines in the SQL Editor. If its rows are right there, the bug is in your code.

Finish the project

Tick every step once you've seen what it says you should see. Then mark the project complete to unlock the next lesson.

0 of 8 steps done
Assistant

Highlight lesson text and click Ask AI, or type a question below.