Simplified
Simplified

Chirp: rules

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

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

YOU

Chirp: rules

Time
About 45 min
Steps
8
Where
Your editor

You'll need

  • β€’Your chirp repository, with comments working
  • β€’Your Supabase project, simplified-practice

Puts into practice

  • Chirp: comments
  • 4.1 Adding Rows
  • 4.2 Changing and Removing Rows
  • 5.1 Unique Values
  • 5.2 Deleting a Parent Row
  • 5.3 Why the Database Says No

Chirp has four tables: users, posts, follows and comments.

Posts, follows and comments all point at users with foreign keys, and comments point at posts too.

But two pages still return sample data: Sign up, and Delete account in Settings.

So what should happen to a person's posts, follows and comments when their account goes?

You'll find out by trying it, with a test account of your own.

Then you'll add rules to the schema, so Postgres does the right thing for every program that writes.

The goal

Sign-up that refuses a taken handle, and Delete account that takes a person's posts and leaves their comments as [deleted].

When you're done

  • βœ“Sign-up says That handle is taken when a handle is in use.
  • βœ“Deleting an account deletes its posts too.
  • βœ“Its comments stay, shown as [deleted].

What you're working toward

Dee's Deployed post with four comments: fay's, ada's and yours, then Nice one! under [deleted], with a grey avatar.
1

Step 1: Your turn: sign up

The page at localhost:3000/signup asks for a handle and a name.

It calls createUser(handle, name) in lib/data.ts, which still returns sample data.

When it gets back { id }, the page signs you in as the new user.

So the INSERT must send back the new row's id, with RETURNING.

With Supabase's library, .select("id") after .insert(…) is your RETURNING id.

Your turn 1

Make createUser(handle, name) add a row to users, and return { id } with the new row's id.

Done when

Sign up as zed, named Zed. The sidebar says you're signed in as zed, and Switch user lists eight people.

export async function createUser(handle: string, name: string) {
  // INSERT INTO users (handle, name) VALUES ('zed', 'Zed') RETURNING id;
  const { data, error } = await supabase
    .from("users")
    .insert({ handle, name })
    .select("id")
    .single();
  if (error) throw new Error(error.message);
  return { id: data.id };
}

You should see

Signed in as zed, with zed under ada, ben, cy, dee, eli, fay and you in Switch user.

2

Step 2: Use Chirp as zed

Give zed some rows that point at zed's row in users.

  1. As zed, post Hello from zed from the box on localhost:3000.
  2. Open Dee's "Deployed" post, at localhost:3000/post/20, and comment Nice one!
  3. Open localhost:3000/ada, and follow ada.
  4. Switch user to ada, and comment Welcome! on zed's post.
  5. Switch user back to zed.

Now zed has a post, a comment and a follow, and someone has commented on zed's post.

You should see

zed's post at the top of the feed with πŸ’¬ 1, and Dee's "Deployed" card showing one more comment than before.

3

Step 3: Your turn: delete the account

Open Settings, at localhost:3000/settings.

Its Delete account button calls deleteAccount(userId) with your id, which still does nothing.

Once it returns, the page signs you back in as you.

Your turn 1

Make deleteAccount(userId) delete that user's row from users.

Done when

Pressing Delete account as zed sends your DELETE to Postgres.

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

You should see

An error, not a deleted account:

update or delete on table "users" violates foreign key constraint "posts_user_id_fkey" on table "posts"

zed is still in Switch user, and zed's post is still on the feed.

The terminal printing an error: update or delete on table "users" violates foreign key constraint "posts_user_id_fkey" on table "posts".
4

Step 4: Pick a rule for each foreign key

Postgres refused, because posts' user_id says REFERENCES users (id), and nothing more.

So by default, zed's row can't go while zed's post points at it.

A refused write changes nothing, so zed's post, comment and follow are all still there.

And posts isn't the only table that points at a row zed has:

TableForeign keyPoints at
postsuser_idusers (id)
commentspost_idposts (id)
commentsuser_idusers (id)
followsfollower_idusers (id)
followsfollowee_idusers (id)

Here is what Chirp wants when a row is deleted:

  • When an account goes, its posts and its follows go with it.
  • Its comments on other people's posts stay, so the threads still make sense.
  • When a post goes, its comments go too.

For each foreign key, pick refuse, ON DELETE CASCADE or ON DELETE SET NULL.

Your turn 1

Write each foreign key's line as its CREATE TABLE would hold it, with the rule Chirp wants.

A right answer gives

Five lines, each ending on its ON DELETE rule, that match the solution's.

-- posts
user_id     integer NOT NULL REFERENCES users (id) ON DELETE CASCADE

-- comments
post_id     integer NOT NULL REFERENCES posts (id) ON DELETE CASCADE
user_id     integer REFERENCES users (id) ON DELETE SET NULL

-- follows
follower_id integer NOT NULL REFERENCES users (id) ON DELETE CASCADE
followee_id integer NOT NULL REFERENCES users (id) ON DELETE CASCADE

You should see

Four CASCADEs and one SET NULL, on comments' user_id, which has no NOT NULL so it can hold NULL.

5

Step 5: Add the rules

Your tables already hold rows, so you can't run their CREATE TABLEs again.

ALTER TABLE changes a table that's already there. You'll learn it in Migrations.

For now, paste this into the SQL Editor, and press Run.

ALTER TABLE posts
  DROP CONSTRAINT posts_user_id_fkey,
  ADD FOREIGN KEY (user_id) REFERENCES users (id) ON DELETE CASCADE;

ALTER TABLE comments
  DROP CONSTRAINT comments_post_id_fkey,
  ADD FOREIGN KEY (post_id) REFERENCES posts (id) ON DELETE CASCADE,
  DROP CONSTRAINT comments_user_id_fkey,
  ALTER COLUMN user_id DROP NOT NULL,
  ADD FOREIGN KEY (user_id) REFERENCES users (id) ON DELETE SET NULL;

ALTER TABLE follows
  DROP CONSTRAINT follows_follower_id_fkey,
  ADD FOREIGN KEY (follower_id) REFERENCES users (id) ON DELETE CASCADE,
  DROP CONSTRAINT follows_followee_id_fkey,
  ADD FOREIGN KEY (followee_id) REFERENCES users (id) ON DELETE CASCADE;

Each DROP CONSTRAINT removes an old foreign key, by the name Postgres gave it, like the one in your error.

Each ADD FOREIGN KEY puts it back with one of your lines from the last step.

And DROP NOT NULL lets comments' user_id hold NULL, which SET NULL needs.

You should see

Success. No rows returned under the editor.

6

Step 6: Delete zed again

As zed, open Settings and press Delete account again.

This time Postgres deletes zed's row, and follows each rule.

CASCADE deletes zed's post and zed's follow. Then it deletes ada's Welcome!, which was on that post.

SET NULL keeps zed's Nice one! on Dee's post, with NULL in its user_id.

Now open Dee's "Deployed" post, at localhost:3000/post/20.

Why does the page break now?

Because no user has a NULL id, so the JOIN finds no author for Nice one!

getComments sends it back with users: null, and the page reads comment.users.handle.

Your turn 1

In the Comment component, show the word [deleted] where the handle goes when a comment has no author.

Done when

Dee's "Deployed" post shows Nice one! under [deleted], below fay's, ada's and your comments.

// user_id is NULL, so the JOIN finds no user, and users comes back as null
<span className="font-medium">{comment.users?.handle ?? "[deleted]"}</span>

You should see

Signed in as you again, with seven people in Switch user and no zed.

zed's post is gone from the feed, and Dee's "Deployed" card still counts zed's comment.

7

Step 7: Your turn: That handle is taken

Sign up as ada, named Ada.

It works, and Switch user now lists two adas. So whose profile is localhost:3000/ada?

As the new ada, press Delete account. Nothing points at her row, so she goes.

In a CREATE TABLE, you'd write UNIQUE on handle's line: handle text NOT NULL UNIQUE.

users already holds rows, so paste this ALTER TABLE into the SQL Editor instead, and press Run.

ALTER TABLE users ADD UNIQUE (handle);

Postgres names this rule users_handle_key: the table, the column, then key.

Now sign up as ada again. Postgres refuses the INSERT, and createUser throws an error naming users_handle_key.

Every error also has a code: Postgres gives each kind of refusal its own one.

23505 means a unique constraint refused the write.

Your turn 1

Make createUser(handle, name) return { error: "That handle is taken" } when the error's code is 23505, and still throw for any other error.

Done when

Signing up as ada shows That handle is taken under the form, and Switch user still lists seven people.

export async function createUser(handle: string, name: string) {
  // INSERT INTO users (handle, name) VALUES ('ada', 'Ada') RETURNING id;
  const { data, error } = await supabase
    .from("users")
    .insert({ handle, name })
    .select("id")
    .single();
  if (error?.code === "23505") return { error: "That handle is taken" };
  if (error) throw new Error(error.message);
  return { id: data.id };
}

You should see

That handle is taken for ada, and only one ada in Switch user: the refused INSERT stored nothing.

Chirp's sign-up form with ada as the handle and Ada as the name, and That handle is taken in red under it.
8

Step 8: Save your work

Commit your changes, and push them to GitHub.

git add -A
git commit -m "Delete accounts and refuse taken handles"
git push

You should see

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

Step 5's paste says a constraint does not exist

Your foreign key has another name. Run SELECT conname FROM pg_constraint WHERE contype = 'f';, which lists every foreign key's name, and use yours.

Delete account is still refused, naming another foreign key

That foreign key has no rule yet. Run all of step 5's paste again: it's safe to run twice.

Step 7's ALTER TABLE says could not create unique index "users_handle_key"

Two rows still hold ada, and UNIQUE can't be added while they do. Delete the second ada's account first, then run it again.

Signing up as ada still breaks the page

Check error.code before the line that throws, or the throw runs first.

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

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