Prepare for the PgBouncer and IPv4 deprecations on 26th January 2024

Database

Converting SQL to JavaScript API

Select a set of columns from a single table with where, order by, and limit clauses.


_10
select first_name, last_name, team_id, age
_10
from players
_10
where age between 20 and 24 and team_id != 'STL'
_10
order by last_name, first_name desc
_10
limit 20;


_10
const { data, error } = await supabase
_10
.from('players')
_10
.select('first_name,last_name,team_id,age')
_10
.gte('age', 20)
_10
.lte('age', 24)
_10
.not('team_id', 'eq', 'STL')
_10
.order('last_name', { ascending: true }) // or just .order('last_name')
_10
.order('first_name', { ascending: false })
_10
.limit(20)

Select all columns from a single table with a complex where clause: OR AND OR


_10
select *
_10
from players
_10
where ((team_id = 'CHN' or team_id is null) and (age > 35 or age is null));


_10
const { data, error } = await supabase
_10
.from('players')
_10
.select() // or .select('*')
_10
.or('team_id.eq.CHN,team_id.is.null')
_10
.or('age.gt.35,age.is.null') // additional filters imply "AND"
_10
.not('team_id', 'eq', 'STL')

Select all columns from a single table with a complex where clause: AND OR AND


_10
select *
_10
from players
_10
where ((team_id = 'CHN' and age > 35) or (team_id != 'CHN' and age is not null));


_10
const { data, error } = await supabase
_10
.from('players')
_10
.select() // or .select('*')
_10
.or('and(team_id.eq.CHN,age.gt.35),and(team_id.neq.CHN,.not.age.is.null)')

Get a count of rows, but don't return any data.


_10
select count(*)
_10
from players
_10
where team_id = 'NYM';


_10
const { count, error } = await supabase
_10
.from('players')
_10
.select('*', { count: 'exact', head: true }) // exact, planned, or executed
_10
.eq('team_id', 'NYM')

Resources