Simplified
Simplified

Chirp: comments

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 Picking Columns β†—

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: comments

Time
About 40 min
Steps
7
Where
Your editor

You'll need

  • β€’Your chirp repository, with users, posts and follows
  • β€’Your Supabase project, simplified-practice

Puts into practice

  • Chirp: people and follows
  • 4.6 Designing Tables
  • 4.3 Two Tables
  • 4.4 Reading Across Tables
  • 4.1 Adding Rows
  • 4.2 Changing and Removing Rows
  • 3.7 Counting

Chirp has three tables now: users, posts, and follows, which links people to the people they follow.

Each post holds its author's id, and the feed joins it to users for the handle.

But the post page, at localhost:3000/post/4, has an empty space under the post.

That's where its comments go, and Chirp has nowhere to keep them yet.

So what table do comments need, and how does each one point at its post and its author?

This time nobody hands you the columns: you design the table from the screen.

Then you show comments under each post, let people add one, and count them on every post card.

The goal

Comments under every post on Chirp, from a table you designed, with a count on each post card.

When you're done

  • βœ“A member can read the comments under a post.
  • βœ“They can add a comment of their own.
  • βœ“Every post card shows how many comments it has.

What you're working toward

Dee's post Deployed. It works on more than my machine., with three comments under it: fay's Ship it!, ada's Congrats!, and your Congrats, Dee!
1

Step 1: Design the comments table

Under each post, Chirp shows its comments.

Each comment shows who wrote it, what they wrote, and when.

Before you write any SQL, answer these four questions:

  • Is a comment an entity: a thing the screen shows and the app must remember?
  • Which facts about a comment become columns, with one value in each cell?
  • A post has many comments, and each comment is on one post: which table holds the key?
  • Each comment has one author: should a comment hold the author's handle, or something else?

Name each column the way Chirp's other tables do, so the comments in the next step fit.

Then open the SQL Editor in your Supabase project, simplified-practice.

If Supabase asks about RLS when you run your query, pick Run and enable RLS, as you did for posts.

Your turn 1

Write the CREATE TABLE query for comments, from your answers, and run it. A comment's text is at most 280 characters, like a post's.

A right answer gives

Success. No rows returned under the editor, and column names that match the solution's.

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

You should see

In the Table Editor, a comments table with no rows, and the same five column names as the solution.

2

Step 2: Fill it with comments

Your table is empty, so every post page would show no comments.

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

INSERT INTO comments (post_id, user_id, body, created_at)
VALUES
  (1, 2, 'Wait until you meet JOIN.', '2026-11-01 10:02'),
  (1, 3, 'SELECT star is the gateway.', '2026-11-01 11:30'),
  (4, 2, 'Screenshot or it did not happen.', '2026-11-02 19:40'),
  (4, 1, 'Teach me your ways.', '2026-11-02 20:15'),
  (4, 7, 'Saving this for later.', '2026-11-03 08:00'),
  (11, 4, 'Same here.', '2026-11-06 13:00'),
  (16, 6, 'Lifestyle confirmed.', '2026-11-09 07:45'),
  (19, 5, 'Only three?', '2026-11-10 14:10'),
  (20, 6, 'Ship it!', '2026-11-10 17:45'),
  (20, 1, 'Congrats!', '2026-11-10 18:05');

Each row holds the id of the post it's on, then the id of its author.

Both are foreign keys, so Postgres checks that each post and each author exists.

You should see

In the Table Editor, comments with 10 rows. Row 5 holds post_id 4 and user_id 7: that's you.

3

Step 3: Show a post's comments

Open Dee's post, "Centering a div on the first try.", at localhost:3000/post/4.

Its comments come from getComments(postId) in lib/data.ts, which still returns sample data.

Each comment needs its text, its time, and its author's handle, which lives in users.

In SQL, that is a JOIN, with the oldest comment first.

SELECT comments.id, comments.body, comments.created_at, users.handle
FROM comments
JOIN users ON comments.user_id = users.id
WHERE comments.post_id = 4
ORDER BY comments.created_at;

With Supabase's library, the JOIN goes inside .select(…), as users(handle).

export async function getComments(postId: number) {
  // SELECT comments.id, comments.body, comments.created_at, users.handle
  // FROM comments JOIN users ON comments.user_id = users.id
  // WHERE comments.post_id = 4 ORDER BY comments.created_at;
  const { data, error } = await supabase
    .from("comments")
    .select("id, body, created_at, users(handle)")
    .eq("post_id", postId)
    .order("created_at", { ascending: true });
  if (error) throw new Error(error.message);
  return data;
}

The library finds the foreign key from comments to users, so it writes the ON for you.

Each comment comes back with its author inside it, like users: { handle: "ben" }.

Replace getComments(postId) with this one, save, and reload the page.

You should see

Three comments under Dee's post, oldest first: ben's "Screenshot or it did not happen.", ada's "Teach me your ways.", then yours.

Dee's post Centering a div on the first try., with three comments, oldest first: ben's, ada's, then yours, Saving this for later.
4

Step 4: Your turn: add a comment

Under the comments is a box to write one, with a Comment button.

Its form calls addComment(postId, userId, body), with your id from getMe().

addComment still does nothing, so your comment goes nowhere.

Your turn 1

Make addComment(postId, userId, body) add a row to comments, on that post, by that author.

Done when

On Dee's post "Deployed. It works on more than my machine.", at localhost:3000/post/20, comment Congrats, Dee! It shows last, under the handle you.

export async function addComment(postId: number, userId: number, body: string) {
  // INSERT INTO comments (post_id, user_id, body) VALUES (20, 7, 'Congrats, Dee!');
  const { error } = await supabase
    .from("comments")
    .insert({ post_id: postId, user_id: userId, body });
  if (error) throw new Error(error.message);
}

You should see

Three comments under Dee's "Deployed" post: fay's "Ship it!", ada's "Congrats!", then your "Congrats, Dee!"

5

Step 5: Your turn: count the comments

Each post card in the feed has room for how many comments it has, like πŸ’¬ 3.

The feed page calls countComments(postId) for each post, which still returns sample data.

Your turn 1

Make countComments(postId) return how many comments that post has, counted by Postgres.

Done when

On localhost:3000, these four cards have comments, and every other card shows πŸ’¬ 0:

PostComments
Dee: "Deployed. It works on more than my machine."πŸ’¬ 3
Ben: "TypeScript caught three bugs before lunch."πŸ’¬ 1
Cy: "Dark mode is not a feature. It is a lifestyle."πŸ’¬ 1
Ada: "Tailwind makes me feel like a designer."πŸ’¬ 1
export async function countComments(postId: number) {
  // SELECT COUNT(*) FROM comments WHERE post_id = 20;
  const { count, error } = await supabase
    .from("comments")
    .select("*", { count: "exact", head: true })
    .eq("post_id", postId);
  if (error) throw new Error(error.message);
  return count ?? 0;
}

You should see

πŸ’¬ 3 on Dee's "Deployed" card, counting your comment. If you added more than one, it shows more.

Chirp's home page with a comment count on each post: 3 on Dee's Deployed, 1 on Ben's TypeScript post, 0 on Fay's and Ada's, 1 on Cy's Dark mode.
6

Step 6: Change a handle once

Say Ada wants a new handle: ada_codes.

She has comments on two of Dee's posts, and four posts of her own.

So how many rows must change, for all of them to show her new handle?

Find out in the SQL Editor.

Your turn 1

Change Ada's handle to ada_codes with one UPDATE in the SQL Editor. Her id is 1.

A right answer gives

Reload localhost:3000/post/4: Ada's comment now says ada_codes.

UPDATE users SET handle = 'ada_codes' WHERE id = 1;

You should see

ada_codes beside Ada's comments on /post/4 and /post/20, and on her posts in the feed.

One row changed, because each comment holds her id, 1, and finds her handle through it.

Then run the same UPDATE with 'ada', so her handle is back.

7

Step 7: Save your work

Commit your changes, and push them to GitHub.

git add -A
git commit -m "Add comments"
git push

You should see

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

Step 2's query says a column of comments does not exist

Your columns have other names than the solution's. Run DROP TABLE comments;, then run the solution's CREATE TABLE, then step 2's query again.

An error like Could not find a relationship between 'comments' and 'users'

Your user_id has no REFERENCES, so there is no foreign key for the library to join on. Drop the table and make it again from the solution.

Every card shows πŸ’¬ 0

Read count from what the query sends back, not data. With head: true, data is empty.

A post shows the wrong comments

Run step 3's SQL in the SQL Editor. If its rows are right there, the bug is in your code, often a missing .eq("post_id", postId).

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.

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 7 steps done
Assistant

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