PostgrestQueryBuilder

• Named parameters

• Count algorithm to use to count deleted rows.

"exact": Exact but slow count algorithm. Performs a COUNT(*) under the hood.

"planned": Approximated but fast count algorithm. Uses the Postgres statistics under the hood.

"estimated": Uses exact count for low numbers and planned count for high numbers.

Delete a single record

const response = await supabase
  .from('countries')
  .delete()
  .eq('id', 1)

Delete a record and return it

const { data, error } = await supabase
  .from('countries')
  .delete()
  .eq('id', 1)
  .select()

Delete multiple records

const response = await supabase
  .from('countries')
  .delete()
  .in('id', [1, 2, 3])

• The values to insert. Pass an object to insert a single row or an array to insert multiple rows.

• Named parameters

• Count algorithm to use to count inserted rows.

"exact": Exact but slow count algorithm. Performs a COUNT(*) under the hood.

"planned": Approximated but fast count algorithm. Uses the Postgres statistics under the hood.

"estimated": Uses exact count for low numbers and planned count for high numbers.

• Make missing fields default to null. Otherwise, use the default value for the column. Only applies for bulk inserts.

Create a record

const { error } = await supabase
  .from('countries')
  .insert({ id: 1, name: 'Mordor' })

Create a record and return it

const { data, error } = await supabase
  .from('countries')
  .insert({ id: 1, name: 'Mordor' })
  .select()

Bulk create

const { error } = await supabase
  .from('countries')
  .insert([
    { id: 1, name: 'Mordor' },
    { id: 1, name: 'The Shire' },
  ])

• The columns to retrieve, separated by commas. Columns can be renamed when returned with customName:columnName

• Named parameters

• When set to true, data will not be returned. Useful if you only need the count.

• Count algorithm to use to count rows in the table or view.

"exact": Exact but slow count algorithm. Performs a COUNT(*) under the hood.

"planned": Approximated but fast count algorithm. Uses the Postgres statistics under the hood.

"estimated": Uses exact count for low numbers and planned count for high numbers.

Getting your data

const { data, error } = await supabase
  .from('characters')
  .select()

Selecting specific columns

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

Query referenced tables

const { data, error } = await supabase
  .from('orchestral_sections')
  .select(`
    name,
    instruments (
      name
    )
  `)

Query referenced tables with spaces in their names

const { data, error } = await supabase
  .from('orchestral sections')
  .select(`
    name,
    "musical instruments" (
      name
    )
  `)

Query referenced tables through a join table

const { data, error } = await supabase
  .from('users')
  .select(`
    name,
    teams (
      name
    )
  `)

Query the same referenced table multiple times

const { data, error } = await supabase
  .from('messages')
  .select(`
    content,
    from:sender_id(name),
    to:receiver_id(name)
  `)

// To infer types, use the name of the table (in this case `users`) and
// the name of the foreign key constraint.
const { data, error } = await supabase
  .from('messages')
  .select(`
    content,
    from:users!messages_sender_id_fkey(name),
    to:users!messages_receiver_id_fkey(name)
  `)

Query nested foreign tables through a join table

  const { data, error } = await supabase
    .from('games')
    .select(`
      game_id:id,
      away_team:teams!games_away_team_fkey (
        users (
          id,
          name
        )
      )
    `)

Filtering through referenced tables

const { data, error } = await supabase
  .from('instruments')
  .select('name, orchestral_sections(*)')
  .eq('orchestral_sections.name', 'percussion')

Querying referenced table with count

const { data, error } = await supabase
  .from('orchestral_sections')
  .select(`*, instruments(count)`)

Querying with count option

const { count, error } = await supabase
  .from('characters')
  .select('*', { count: 'exact', head: true })

Querying JSON data

const { data, error } = await supabase
  .from('users')
  .select(`
    id, name,
    address->city
  `)

Querying referenced table with inner join

const { data, error } = await supabase
  .from('instruments')
  .select('name, orchestral_sections!inner(name)')
  .eq('orchestral_sections.name', 'woodwinds')
  .limit(1)

Switching schemas per query

const { data, error } = await supabase
  .schema('myschema')
  .from('mytable')
  .select()

• The values to update with

• Named parameters

• Count algorithm to use to count updated rows.

"exact": Exact but slow count algorithm. Performs a COUNT(*) under the hood.

"planned": Approximated but fast count algorithm. Uses the Postgres statistics under the hood.

"estimated": Uses exact count for low numbers and planned count for high numbers.

Updating your data

const { error } = await supabase
  .from('instruments')
  .update({ name: 'piano' })
  .eq('id', 1)

Update a record and return it

const { data, error } = await supabase
  .from('instruments')
  .update({ name: 'piano' })
  .eq('id', 1)
  .select()

Updating JSON data

const { data, error } = await supabase
  .from('users')
  .update({
    address: {
      street: 'Melrose Place',
      postcode: 90210
    }
  })
  .eq('address->postcode', 90210)
  .select()

• The values to upsert with. Pass an object to upsert a single row or an array to upsert multiple rows.

• Named parameters

• Comma-separated UNIQUE column(s) to specify how duplicate rows are determined. Two rows are duplicates if all the onConflict columns are equal.

• If true, duplicate rows are ignored. If false, duplicate rows are merged with existing rows.

• Count algorithm to use to count upserted rows.

"exact": Exact but slow count algorithm. Performs a COUNT(*) under the hood.

"planned": Approximated but fast count algorithm. Uses the Postgres statistics under the hood.

"estimated": Uses exact count for low numbers and planned count for high numbers.

• Make missing fields default to null. Otherwise, use the default value for the column. This only applies when inserting new rows, not when merging with existing rows under ignoreDuplicates: false. This also only applies when doing bulk upserts.

Upsert a single row using a unique key

// Upserting a single row, overwriting based on the 'username' unique column
const { data, error } = await supabase
  .from('users')
  .upsert({ username: 'supabot' }, { onConflict: 'username' })

// Example response:
// {
//   data: [
//     { id: 4, message: 'bar', username: 'supabot' }
//   ],
//   error: null
// }

Upsert with conflict resolution and exact row counting

// Upserting and returning exact count
const { data, error, count } = await supabase
  .from('users')
  .upsert(
    {
      id: 3,
      message: 'foo',
      username: 'supabot'
    },
    {
      onConflict: 'username',
      count: 'exact'
    }
  )

// Example response:
// {
//   data: [
//     {
//       id: 42,
//       handle: "saoirse",
//       display_name: "Saoirse"
//     }
//   ],
//   count: 1,
//   error: null
// }

Upsert your data

const { data, error } = await supabase
  .from('instruments')
  .upsert({ id: 1, name: 'piano' })
  .select()

Bulk Upsert your data

const { data, error } = await supabase
  .from('instruments')
  .upsert([
    { id: 1, name: 'piano' },
    { id: 2, name: 'harp' },
  ])
  .select()

Upserting into tables with constraints

const { data, error } = await supabase
  .from('users')
  .upsert({ id: 42, handle: 'saoirse', display_name: 'Saoirse' })
  .select()