Simplified
Simplified

Chirp: post, edit and delete

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 ↗

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: post, edit and delete

Time
About 35 min
Steps
7
Where
Your editor

You'll need

  • •Your chirp repository, with its feed, profiles and search reading from Supabase
  • •Your Supabase project, simplified-practice

Puts into practice

  • Chirp: find and count posts
  • 4.1 Adding Rows
  • 4.2 Changing and Removing Rows

Chirp's feed, profiles and search all read your posts table.

But every one of its 20 posts went in from the SQL Editor.

And the compose box on Chirp's home page doesn't save anything yet.

So how does an app put new rows in, change them and take them out?

Each is one write you can already write in SQL: an INSERT, an UPDATE or a DELETE.

Supabase's library has a method for each part of them:

SQL you knowSupabase's library
INSERT INTO posts (handle, body) VALUES ('you', 'Hi').from("posts").insert({ handle: "you", body: "Hi" })
RETURNING id.select("id")
UPDATE posts SET body = 'Hi'.from("posts").update({ body: "Hi" })
DELETE FROM posts.from("posts").delete()
WHERE id = 21.eq("id", 21)

An object's keys are the columns, and its values are the VALUES, so their order no longer matters.

The goal

Chirp's compose box writes real posts, and you can edit and delete your own.

When you're done

  • ✓A member can write a post, and land on its page.
  • ✓They can edit their own post, and delete it.
  • ✓No edit or delete touches anyone else's post.

What you're working toward

Your post's own page on Chirp: @you, My first real post., 0 likes, with Edit and Delete buttons.
1

Step 1: Give each post its own page

Every post card links to a page of its own, like localhost:3000/post/20.

The number is the post's id, so the reads must send it back.

In lib/data.ts, put id first in the columns of getPosts(), getPostsBy(), searchPosts() and getTrending().

.select("id, handle, body, likes, created_at")

getTrending() selects no created_at, so its columns are "id, handle, body, likes".

The post page calls getPost(id), which still returns sample data. In SQL, it is this query.

SELECT id, handle, body, likes, created_at
FROM posts
WHERE id = 20;

The id is the primary key, so one row passes at most. Replace getPost(id) with this.

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

.maybeSingle() hands back the one row as an object, or null when no row passes.

The page shows Post not found for null. Its comments are still sample data, until a later checkpoint.

You should see

Clicking Dee's "Deployed. It works on more than my machine." opens localhost:3000/post/20, with 142 likes. localhost:3000/post/99 says Post not found.

2

Step 2: Write a post, and land on it

Type something in the compose box on the home page, and press Post.

You land on Post not found, because createPost(body) saves nothing and returns a made-up id, 999.

So how does the app learn the id Postgres picks for the new post?

The same way Simplified's server learns a new learner's id: RETURNING.

INSERT INTO posts (handle, body)
VALUES ('you', 'My first real post.')
RETURNING id;

Replace createPost(body) in lib/data.ts with this.

import { getMe } from "./session";

export async function createPost(body: string) {
  const me = await getMe();
  const { data, error } = await supabase
    .from("posts")
    .insert({ handle: me.handle, body })
    .select("id")
    .single();
  if (error) throw new Error(error.message);
  return data.id;
}

getMe() says who is signed in. Chirp has no log-in yet, so it's always you.

The insert names only handle and body. Postgres generates the id, and likes and created_at get their DEFAULTs.

.select("id") is your RETURNING, and .single() hands back its one row as an object.

The compose box then opens the page for that id.

Write My first real post., and press Post.

You should see

You land on localhost:3000/post/21: your post, by you, with 0 likes.

It's also first on the home page. Your id is higher if your table ever held more than 20 posts.

Chirp's home page with your post, My first real post., first, above Dee's Deployed. It works on more than my machine.
3

Step 3: Your turn: edit your post

Your post's page shows Edit and Delete, because you wrote it. Dee's post shows neither.

Edit opens a form, and its Save calls editPost(id, body), which still changes nothing.

Your turn 1

Make editPost(id, body) change the body of the post with that id, and only that post.

Done when

Change your post to My first real post, edited., and press Save.

The new words show on its page and on the feed, and Dee's post 20 is unchanged.

// UPDATE posts SET body = 'My first real post, edited.' WHERE id = 21;
export async function editPost(id: number, body: string) {
  const { error } = await supabase
    .from("posts")
    .update({ body })
    .eq("id", id);
  if (error) throw new Error(error.message);
}

You should see

Your post says My first real post, edited., and every other post kept its own words.

4

Step 4: Your turn: delete your post

Delete calls deletePost(id), then takes you back to the home page.

Your turn 1

Make deletePost(id) remove the post with that id, and only that post.

Done when

Pressing Delete on your post takes you to the home page, with Dee's "Deployed. It works on more than my machine." first again. localhost:3000/post/21 says Post not found.

// DELETE FROM posts WHERE id = 21;
export async function deletePost(id: number) {
  const { error } = await supabase
    .from("posts")
    .delete()
    .eq("id", id);
  if (error) throw new Error(error.message);
}

You should see

Your post is gone. Write another one, Second try., and it lands on localhost:3000/post/22, not 21, because Postgres never hands out the same id twice.

5

Step 5: Break it: leave out the WHERE

In editPost(), put // in front of the .eq("id", id) line, and save.

Now the UPDATE has no WHERE, so every row in posts would pass.

Open your post Second try., press Edit, change a word, and press Save.

Why didn't all 21 posts change?

Because Supabase sets Postgres to refuse an UPDATE or a DELETE with no WHERE, from your app.

So Postgres refused the whole query, and nothing changed.

The SQL Editor doesn't refuse. It warns you first, but if you go on, Postgres changes every row.

Take the // out again, and save.

You should see

First an error that says UPDATE requires a WHERE clause, with every post as it was. Then your edit saves again.

6

Step 6: Your turn: see what each write reports

After every write, Postgres reports how many rows it touched, like UPDATE 1.

The library sends that number back too, if you ask for it:

SQL you knowSupabase's library
UPDATE 1.update({ body }, { count: "exact" }), then read count
DELETE 1.delete({ count: "exact" }), then read count

Your turn 1

Make editPost() and deletePost() print a line in your terminal like Postgres's report, and return the number of rows they touched.

Done when

Saving an edit prints UPDATE 1. Open your post in two tabs, and press Delete in each: the terminal prints DELETE 1, then DELETE 0.

// UPDATE posts SET body = 'Second try, edited.' WHERE id = 22;
export async function editPost(id: number, body: string) {
  const { count, error } = await supabase
    .from("posts")
    .update({ body }, { count: "exact" })
    .eq("id", id);
  if (error) throw new Error(error.message);
  console.log(`UPDATE ${count}`);
  return count ?? 0;
}

// DELETE FROM posts WHERE id = 22;
export async function deletePost(id: number) {
  const { count, error } = await supabase
    .from("posts")
    .delete({ count: "exact" })
    .eq("id", id);
  if (error) throw new Error(error.message);
  console.log(`DELETE ${count}`);
  return count ?? 0;
}

You should see

DELETE 1, then DELETE 0, and no error: the second DELETE worked, and no row passed its WHERE.

The terminal running npm run dev, printing UPDATE 1, then DELETE 1, then DELETE 0.
7

Step 7: Save your work

Commit your changes, and push them to GitHub.

git add -A
git commit -m "Write, edit and delete posts"
git push

You should see

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

Post cards link to localhost:3000/post/undefined

That read doesn't send back the id. Add id to its .select(…).

localhost:3000/post/99 shows an error, not Post not found

getPost() ends with .single(), which treats no row as an error. Use .maybeSingle(), which gives null instead.

createPost() returns undefined

The insert has no .select("id"), so nothing comes back. Add it, then .single().

Every post now says the same thing

An UPDATE with no WHERE went through. In the SQL Editor, run DELETE FROM posts;, then checkpoint 1's INSERT again, and put .eq("id", id) back.

The count is always null

Pass { count: "exact" } to .update(…) or .delete(). Without it, the library doesn't ask Postgres for the number.

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

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