Simplified
Simplified

Chirp: fewer queries

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: fewer queries

Time
About 30 min
Steps
6
Where
Your editor

You'll need

  • β€’Your chirp repository, with comment counts on the feed
  • β€’Your Supabase project, simplified-practice

Puts into practice

  • Chirp: make it correct
  • Chirp: comments
  • 3.4 Combining Conditions
  • 3.7 Counting
  • 8.1 Sharing a Few Connections
  • 8.2 One Question at a Time

Every post card on Chirp's feed shows how many comments it has, like πŸ’¬ 2.

You wrote that: countComments(postId) counts one post's comments.

And the feed calls it once for each of its 10 posts.

So how many queries does one visit to Chirp's feed send?

You can't see them yet: the library sends them quietly.

In this project you make every query show up in your terminal, and count them.

Then you cut them down, the way you'd fix Ada's dashboard on Simplified.

The goal

Chirp's feed, getting every comment count in one query instead of one per post.

When you're done

  • βœ“The feed shows the same comment counts as before.
  • βœ“It gets them in one query, not one for each post.
  • βœ“Your terminal shows every query a page sends.

What you're working toward

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

Step 1: Show every query

Each query the library sends is one request, from your server to Supabase, like a fetch() you'd write.

Supabase's server keeps a connection pool to your Postgres.

It borrows a connection, runs your query, and returns it.

The library lets you hand it your own fetch, to send those requests with.

So you can give it one that prints each request first.

Open lib/supabase.ts, and change it to this.

import "server-only";
import { createClient } from "@supabase/supabase-js";

function logQuery(input: RequestInfo | URL, init?: RequestInit) {
  const url = new URL(String(input));
  console.log("query:", init?.method ?? "GET", decodeURIComponent(url.pathname + url.search));
  return fetch(input, init);
}

export const supabase = createClient(
  process.env.SUPABASE_URL!,
  process.env.SUPABASE_SECRET_KEY!,
  { global: { fetch: logQuery } },
);

logQuery prints the request's table and what it asks for, then sends it with the usual fetch.

Save, and reload localhost:3000.

You should see

Lines that start with query: in the terminal running npm run dev, and the feed as before.

2

Step 2: Count the feed's queries

Clear your terminal, then reload localhost:3000 once.

Each line names its table after /rest/v1/.

Find the feed's own query: it reads posts, and ends in limit=10.

query: GET /rest/v1/posts?select=…&order=created_at.desc&limit=10

Then find the lines that read comments, one for each post, like this.

query: HEAD /rest/v1/comments?select=*&post_id=eq.20
query: HEAD /rest/v1/comments?select=*&post_id=eq.19

Leave the other lines: they're Trending, Most active and the Switch user menu.

So the feed takes one query for its list of posts, then one more for each post.

That's an N+1 query, with N of 10.

And the feed's loop waits for each count before it asks for the next.

So each query is one more round trip, there to Supabase and back, one after another.

Next.js prints a line for the page too, GET / 200 in …. Note its time.

You should see

One query for posts with limit=10, and ten for comments, from post_id=eq.20 down to post_id=eq.11: 11 queries for the feed.

The feed's lines in the terminal: one GET for posts ending in limit=10, then ten HEAD lines for comments, from post_id=eq.20 down to post_id=eq.11.
3

Step 3: Your turn: one query for every count

The feed shows posts 11 to 20.

Postgres can count the comments on all ten in one query, grouping them by post.

Write that query first, in Supabase's SQL Editor, so you can see its rows.

Your turn 1

Write one query that counts the comments on posts 11 to 20, with one row for each post and its count.

A right answer gives

Four rows, in any order, if you've done checkpoints 5 and 6 as written:

post_idcount
111
161
191
204
SELECT post_id, COUNT(*)
FROM comments
WHERE post_id IN (11, 12, 13, 14, 15, 16, 17, 18, 19, 20)
GROUP BY post_id;

You should see

Rows of post_id and count under the editor, one row for each post that has comments.

4

Step 4: Your turn: send it from the feed

Your query asked about ten posts, but sent back four rows.

Why not ten?

Because GROUP BY makes a group only from rows that are there.

Posts 12, 13, 14, 15, 17 and 18 have no comments, so they make no group, and get no row.

So the feed must show 0 for every post the result leaves out.

You turned on the library's grouping in "Sort, search and count":

SQL you knowSupabase's library
SELECT post_id, COUNT(*) … GROUP BY post_id.select("post_id, count()")
WHERE post_id IN (11, 12, 13).in("post_id", [11, 12, 13])

Your turn 1

In lib/data.ts, write countCommentsOn(postIds): it takes a list of post IDs, and returns each one's comment count, from one query.

Done when

It sends one query, and returns a count for each post that has comments.

// SELECT post_id, COUNT(*) FROM comments WHERE post_id IN (11, 12, …, 20) GROUP BY post_id;
export async function countCommentsOn(postIds: number[]) {
  const { data, error } = await supabase
    .from("comments")
    .select("post_id, count()")
    .in("post_id", postIds);
  if (error) throw new Error(error.message);
  return new Map(data.map((row) => [row.post_id, row.count]));
}

Your turn 2

In app/page.tsx, replace the loop that calls countComments(post.id) with one call to countCommentsOn. Give each post 0 when it has no row.

Done when

Every card shows the same count as before: πŸ’¬ 4 on Dee's "Deployed. It works on more than my machine.", and πŸ’¬ 0 on Fay's "Refactored it."

const posts = await getPosts();
const counts = await countCommentsOn(posts.map((post) => post.id));
const feed = posts.map((post) => ({ ...post, comments: counts.get(post.id) ?? 0 }));

You should see

The feed with the same comment counts as before, and no card left blank.

5

Step 5: Count again

Clear your terminal, and reload localhost:3000 once.

query: GET /rest/v1/posts?select=…&order=created_at.desc&limit=10
query: GET /rest/v1/comments?select=post_id,count()&post_id=in.(20,19,18,17,16,15,14,13,12,11)

Postgres still goes through the comments, one at a time, putting each in its post's group.

But it does that on the database server, right next to the rows.

Compare the page's time, on its GET / 200 in … line, with step 2's.

Now, in getPosts(), change .limit(10) to .limit(50), and reload once more.

The old loop would have sent 51 queries for 50 posts.

Then change it back to .limit(10).

You should see

2 queries for the feed, not 11: one for posts and one for comments. With .limit(50), still 2.

The feed's lines in the terminal: one GET for posts, and one GET for comments with select=post_id,count() and post_id=in.(20,…,11).
6

Step 6: Save your work

countComments(postId) is no longer called by the feed. Delete it if nothing else uses it.

Then commit your changes, and push them to GitHub.

git add -A
git commit -m "Count every post's comments in one query"
git push

You should see

Your commit on GitHub, with the new lib/supabase.ts and countCommentsOn in it.

No lines start with query: in my terminal

Look in the terminal running npm run dev, not the browser's console. Your queries run on the server, so they print there.

Posts with no comments show nothing, or NaN

Those posts have no row in the result. Use counts.get(post.id) ?? 0, so a missing row reads as 0.

Every post shows πŸ’¬ 0

Without post_id in .select(…), Postgres counts all the comments as one group. Name post_id beside count().

An error that says Use of aggregate functions is not allowed

Aggregates are off in this project. Run the two lines from step 5 of "Sort, search and count" in the SQL Editor.

A count looks wrong

Run your step 3 query in the SQL Editor. If its rows are right there, the bug is in your code.

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

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