How to get COUNT(*) in Supabase

The symptom is usually code like this:

const { data } = await supabase.from('orders').select('*')
const count = data?.length ?? 0

It works on tiny tables, but it is the wrong approach.

The root cause is that COUNT(*) in Supabase comes back as metadata, not as something you should derive by downloading every row first.

The correct pattern#

Supabase documents the count option directly on select().

const { count, error } = await supabase
  .from('orders')
  .select('*', { count: 'exact', head: true })
 
if (error) throw error
 
console.log(count)

Use this when you only need the number.

Why head: true matters#

Without head: true, you can still request a count:

const { data, count, error } = await supabase
  .from('orders')
  .select('id', { count: 'exact' })

That returns both rows and count metadata.

With head: true, you are telling Supabase: "give me the count, skip the row payload."

That is usually the better production move for dashboards, pagination totals, and badge counters.

Counting filtered rows#

The filters still apply before the count is calculated.

const { count, error } = await supabase
  .from('orders')
  .select('*', { count: 'exact', head: true })
  .eq('status', 'paid')
 
if (error) throw error
 
console.log(count)

That gives you the count of paid orders, not the count of the whole table.

Why the naive approach gets expensive fast#

Supabase's select docs note that hosted projects return a maximum of 1,000 rows by default unless you change the setting. So even before performance becomes a problem, data.length can be the wrong answer for "how many rows exist?" unless you are intentionally counting only the returned page.

This is exactly why the count option exists.

exact is not free on large tables#

count: 'exact' runs a real COUNT(*) over the filtered set on every call. On a table with a few thousand rows that is nothing. On a table with millions of rows, an exact count forces Postgres to scan every matching row, and it will quietly become the slowest part of your dashboard query — you are paying full scan cost just to render "12,483 results".

Supabase exposes two cheaper modes for exactly this problem:

// Fast, approximate — reads the planner's estimate, no scan
const { count } = await supabase
  .from('events')
  .select('*', { count: 'estimated', head: true })

• planned returns the query planner's estimate. It is effectively instant but can be stale until the table is analyzed.
• estimated uses the planned estimate for large tables and falls back to an exact count for small ones, giving you a sensible default.

For a "roughly how many" badge on a huge table, estimated is the right call. Reserve exact for cases where the number must be precise — pagination totals on moderately sized result sets, invoice counts, anything a user would notice being off by a few hundred.

The pagination pattern#

This is the simplest split:

const pageSize = 20
const from = 0
const to = from + pageSize - 1
 
const { data, count, error } = await supabase
  .from('orders')
  .select('id, total, status', { count: 'exact' })
  .order('created_at', { ascending: false })
  .range(from, to)
 
if (error) throw error
 
return { rows: data, total: count ?? 0 }

Rows for the current page. Total for the whole filtered result set. No manual counting hack needed.

The rule to remember#

• Need rows and total: count: 'exact'
• Need only the total: count: 'exact', head: true
• Do not use data.length as a stand-in for a database count unless you intentionally mean "rows returned in this response"

For adjacent query tuning:

• Why Your Supabase Queries Are Slow (And Exactly How to Fix Them)
• Database Design and Optimization for Next.js and Supabase Applications
• Next.js Data Fetching Patterns with Supabase: Server Components, Streaming, and Caching
• Next.js Supabase Type Safety Guide

References#

• Stack Overflow: How to get "COUNT(*)" in Supabase
• Supabase docs: select() and count option

Related#

• Return inserted row ID in Supabase JS
• How to query using join in Supabase