Simplified
Simplified

Chirp: make it correct

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 ↗

When Postgres says no

ERROR: duplicate key value violates
unique constraint …
↓Supabase's library
if (error?.code === "23505") {
  // a UNIQUE column already has this value
}

error.code is Postgres's code for the error, and error.message its words.

From Why the Database Says No ↗
ERROR: … violates foreign key constraint …
↓Supabase's library
if (error?.code === "23503") {
  // a row points at a row that isn't there
}
From Why the Database Says No ↗

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 ↗
CREATE INDEX ON posts (user_id);
↓SQL Editor
From Indexes ↗
EXPLAIN SELECT * FROM posts
WHERE user_id = 7;
↓SQL Editor

Shows the plan: Seq Scan reads every row, Index Scan uses an index.

From When an Index Helps ↗
BEGIN;
  …
COMMIT;
↓A Postgres function
supabase.rpc("like_post", { … })

The library has no BEGIN. Writes that must happen together go in a Postgres function, which runs as one transaction, and you call it with .rpc().

From All or Nothing ↗
UPDATE posts SET likes = likes + 1
WHERE id = 21;
↓A Postgres function

.update() sends a value, not likes + 1. To let the database do the math, write it in a function.

From When Requests Overlap ↗

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

YOU

Chirp: make it correct

Time
About 40 min
Steps
7
Where
Your editor

You'll need

  • •Your chirp repository, with checkpoint 7 done
  • •Your Supabase project, simplified-practice

Puts into practice

  • Chirp: make it fast
  • 5.2 Deleting a Parent Row
  • 6.3 All or Nothing
  • 6.4 When a Transaction Breaks
  • 6.5 When Requests Overlap
  • 6.6 Making Requests Wait

Chirp has 500,020 posts, each with a likes column, filled in by its seed.

But nobody can like a post yet, so those numbers never change.

So what happens when 50 people press ♥ on the same post at once?

In this project you give Chirp a likes table and a ♥ button.

You'll write the button the simple way, then fire 50 likes at it at once.

Then you'll fix what goes wrong, inside the database.

The goal

A like button that adds the like and the count together, and loses none when 50 people press it at once.

When you're done

  • ✓A member can like a post.
  • ✓The like and the post's count are saved together, or not at all.
  • ✓When 50 people like a post at once, its count goes up by 50.

What you're working toward

Ada's post Day 1 of learning SQL on its own page, with a red, filled heart and 43 likes.
1

Step 1: Make the likes table

Chirp needs to know who liked which post, with one row for each like.

ColumnTypeRules
user_idintegerNever NULL, points at users, and goes when its user is deleted
post_idintegerNever NULL, points at posts, and goes when its post is deleted

Why delete a like when its user is deleted?

Because without an ON DELETE rule, Delete account would be refused again, for anyone who liked a post.

And what stops someone liking the same post twice?

The primary key is user_id and post_id together, like completions' user ID and lesson ID.

So Postgres refuses a second row for the same person and post.

A primary key on two columns goes on its own line, after the columns: PRIMARY KEY (user_id, post_id).

In the SQL Editor, pick Run and enable RLS if Supabase asks.

Your turn 1

Write the CREATE TABLE query for likes, from the table above, and run it.

A right answer gives

Success. No rows returned under the editor.

CREATE TABLE likes (
  user_id integer NOT NULL REFERENCES users (id) ON DELETE CASCADE,
  post_id integer NOT NULL REFERENCES posts (id) ON DELETE CASCADE,
  PRIMARY KEY (user_id, post_id)
);

You should see

In the Table Editor, a likes table with the columns user_id and post_id, and no rows.

2

Step 2: Your turn: the like button, the simple way

Every post card has a ♥ button beside its likes.

It calls likePost(postId, userId) in lib/data.ts, with your id from getMe(). It does nothing yet.

Write it in three requests, one after the other:

  1. Add a row to likes, for this person and post.
  2. Read the post's likes.
  3. Add 1 in your code, and write the new number back.

Your turn 1

Make likePost(postId, userId) add the like, then read the post's likes, and write back one more.

Done when

Press ♥ on Ada's "Day 1 of learning SQL": its 42 likes become 43.

// INSERT INTO likes (user_id, post_id) VALUES (7, 1);
// SELECT likes FROM posts WHERE id = 1;
// UPDATE posts SET likes = 43 WHERE id = 1;
export async function likePost(postId: number, userId: number) {
  const { error: likeError } = await supabase
    .from("likes")
    .insert({ user_id: userId, post_id: postId });
  if (likeError) throw new Error(likeError.message);

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

  const { error: updateError } = await supabase
    .from("posts")
    .update({ likes: data.likes + 1 })
    .eq("id", postId);
  if (updateError) throw new Error(updateError.message);
}

You should see

43 likes on Ada's post, and in the Table Editor, one row in likes: user_id 7, post_id 1.

Press ♥ on it again: an error that names likes_pkey, and the post still has 43.

The primary key refused your second like, so your code stopped before the count.

3

Step 3: Fire 50 likes at once

The starter has a script that plays 50 people pressing ♥ at the same moment.

It picks 50 practice people who haven't liked post 1, Ada's, and calls your likePost() for all 50 at once.

Then it prints how many likes rows post 1 gained, and how far its likes column rose.

npm run like-race

So why didn't the count rise by 50?

Each request read the likes column, then wrote back that number plus 1.

But many read it before the others wrote, so they all read the same number.

Each wrote the same plus 1 in place of the others': a lost update.

The likes rows are all there, because each INSERT added its own row.

Your turn 1

In the SQL Editor, count post 1's likes rows. Then, in a new run, read post 1's likes column.

A right answer gives

51 likes rows: yours and the race's 50. The likes column says less than 93.

SELECT COUNT(*) FROM likes WHERE post_id = 1;
-- then, in a new run:
SELECT likes FROM posts WHERE id = 1;

You should see

The script prints likes rows: 1 → 51 (+50), but the likes column rose by less than 50.

The number changes from run to run. If it says +50, run it again.

The terminal after npm run like-race: 50 people liked post 1 at once. likes rows: 1 → 51 (+50). likes column: 43 → 49 (+6).
4

Step 4: Let the database do the math

The fix is the UPDATE you know: SET likes = likes + 1, so Postgres reads the count itself.

But Supabase's library can't send it. .update() only sets a number your code worked out.

And each library call is its own request, so your INSERT and UPDATE can't share a BEGIN and COMMIT.

If your server crashed between them, a post would keep the like row without the +1.

So both statements go in a function, saved in your database:

CREATE OR REPLACE FUNCTION like_post(liker_id integer, liked_post_id integer)
RETURNS void
LANGUAGE sql
AS $$
  -- your two statements go here
$$;

A function saves statements under a name, so one call runs them all. This course doesn't teach writing one.

liker_id and liked_post_id are the values each call passes in, and RETURNS void means it sends nothing back.

Postgres runs each call as one transaction, as if BEGIN came before it and COMMIT after.

Your turn 1

Replace the comment with the two statements: add the like, then add 1 to the post's likes. Run it.

A right answer gives

Success. No rows returned under the editor.

CREATE OR REPLACE FUNCTION like_post(liker_id integer, liked_post_id integer)
RETURNS void
LANGUAGE sql
AS $$
  INSERT INTO likes (user_id, post_id) VALUES (liker_id, liked_post_id);
  UPDATE posts SET likes = likes + 1 WHERE id = liked_post_id;
$$;

Your turn 2

Call your function to like post 2, Ben's, as you (user 7). Then, in a new run, read post 2's likes.

A right answer gives

One row under like_post, with no value in it. Then post 2's likes: 88, up from 87.

SELECT like_post(7, 2);
-- then, in a new run:
SELECT likes FROM posts WHERE id = 2;

Your turn 3

Run the same call again, on its own, and see what Postgres sends back.

Then, in a new run, read post 2's likes.

A right answer gives

ERROR: duplicate key value violates unique constraint "likes_pkey", and post 2 still has 88.

The refused INSERT aborted the call's transaction, so nothing in it was kept.

Even if your UPDATE came first, Postgres would undo it, and post 2 would still have 88.

SELECT like_post(7, 2);
-- then, in a new run:
SELECT likes FROM posts WHERE id = 2;

You should see

Post 2 has 88 likes, and likes holds one row for it: user_id 7, post_id 2.

5

Step 5: Your turn: call it from Chirp

Supabase's library calls a function with .rpc(), for "remote procedure call":

SQL you knowSupabase's library
SELECT like_post(7, 3);.rpc("like_post", { liker_id: 7, liked_post_id: 3 })

A refused like comes back as an error, with error.code 23505, as "That handle is taken" did.

Here it only means this person already liked the post, so there's nothing to show.

Your turn 1

Make likePost(postId, userId) call your function, and return quietly when the like is refused as a duplicate.

Done when

Press ♥ on Cy's "Hello, world.": its 5 likes become 6.

Press ♥ on Ben's flexbox post, which you liked in the SQL Editor: it stays at 88, with no error.

// SELECT like_post(7, 3);
export async function likePost(postId: number, userId: number) {
  const { error } = await supabase.rpc("like_post", {
    liker_id: userId,
    liked_post_id: postId,
  });
  if (error?.code === "23505") return;
  if (error) throw new Error(error.message);
}

You should see

Cy's post at 6 likes, Ben's still at 88, and no error on either.

6

Step 6: Fire 50 likes again

Run the race again. It picks 50 practice people who haven't liked post 1 yet.

npm run like-race

So why does every like count now?

Each call's UPDATE reads the count inside Postgres, as it runs.

And it locks post 1's row until its transaction commits.

So the next call's UPDATE waits for that lock, then starts from the new count.

You should see

likes rows: 51 → 101 (+50), and the likes column rises by +50 too.

The terminal after npm run like-race: likes rows: 51 → 101 (+50). likes column: 49 → 99 (+50).
7

Step 7: Save your work

Commit your changes, and push them to GitHub.

git add -A
git commit -m "Like posts with one database function"
git push

The likes table and like_post live in your database, not in your repository.

You should see

Your commit on GitHub, with likePost() calling .rpc("like_post", …) in lib/data.ts.

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.

The second call to like_post worked, and post 2 went to 89

likes has no primary key on user_id and post_id together. Check step 1's table in the Table Editor.

You sent the second call and the read in one run, and both failed

Postgres ran them as one transaction, so the call's error aborted the read too. Run the call on its own.

An error like Could not find the function public.like_post

The names in .rpc() must match the function's: like_post, liker_id and liked_post_id. Check each, and that step 4 ran.

After the fix, the race still loses likes

Your function may set likes to a number. It must say likes = likes + 1, so Postgres reads the count as it runs.

A like doesn't show, with no error

Run SELECT like_post(7, 3); in the SQL Editor. If that works, the bug is in your code: check .rpc()'s names.

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.