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.

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