16

I want to retrieve row count in Supabase.

I am guessing it would be something like this:

const { data, error } = await supabase
  .from('cities')
  .select('name', 'COUNT(*)')

Is this possible with Supabase?

dshukertjr
  • 15,244
  • 11
  • 57
  • 94

3 Answers3

63

For future visitors, we are working on this functionality with the PostgREST maintainer:

https://github.com/supabase/postgrest-js/issues/94

(I'm a maintainer)

This is now released:

const { data, count } = supabase
  .from('countries')
  .select('*', { count: 'exact', head: true })

If you want to return the results simultaneously, remove head:

const { data, count } = supabase
  .from('countries')
  .select('*', { count: 'exact' })
kiwicopple
  • 1,192
  • 9
  • 8
  • 1
    The count appears to cap out at 1000 (which is the limit for general select calls from the supabase api). Question is if there's a way to get the count without having to make multiple calls on tables with more than a thousand rows? – Alan W. Smith Feb 16 '22 at 04:18
  • It appears now the count func works as expected. There's now a count section in the doc as well, https://supabase.com/docs/reference/javascript/select#querying-with-count-option – manat Feb 21 '22 at 16:33
  • But how i can get the count from a foreign table. – Tazim Rahbar Jun 27 '22 at 17:49
  • 2
    This should be `.select('*', { count: 'exact', head: true })`. Adding `head` only returns row count, which is often what is wanted. – Emre Jan 26 '23 at 05:19
  • you forgot to put `await` before the supabase call – Hugobop Aug 24 '23 at 23:24
3

As a workaround, you could write a standard Postgres stored procedure or function that returns the count then call that via the SB client.

https://supabase.io/docs/client/rpc

Shorn
  • 19,077
  • 15
  • 90
  • 168
1

It is currently not supported yet, but there is a WIP issue on Github that would bring this feature to Supabase.

The code below has not been implemented in Supabase yet, but it might look something like this:

const { data, error, count } = await supabase
    .from('table')
    .select('*')
    .gt('id', 10)
    .count()

Edit 7.19.2021

As kiwicopple has answered, this feature has been added to Supabase with a slightly different form from what I have above. View the accepted answer for more.

dshukertjr
  • 15,244
  • 11
  • 57
  • 94