Stored Procedures

You can call stored procedures as a "Remote Procedure Call".

That's a fancy way of saying that you can put some logic into your database then call it from anywhere. This is especially useful when the logic rarely changes - like password resets and updates.

Toggle Example Tables

We will be fictional "world" database in all of our examples.

Countries
idnameiso2continent
76BrazilBRSouth America
156ChinaCNAsia
250FranceFREurope
554New ZealandNZOceania
566NigeriaNGAfrica
840United StatesUSNorth America
Cities
namecountry_id
Rio de Janeiro76
Beijing156
Paris250
Auckland554
Lagos556
San Francisco840

Single call

Example of invoking a stored procedure that taking in a name of a city and returning it.
import { createClient } from '@supabase/supabase-js'
const supabase = createClient('https://world.supabase.co', '1a2b-3c4d-5e6f-7g8h')
const echoCity = async () => {
try{
let city = await supabase
.rpc('echo_city', { name: 'The Shire' })
return cities
} catch (error) {
console.log('Error: ', error)
}
}

Bulk call

This example would call a the same stored procedure above (twice), but it will also wait to receive the response only when all the calls have completed:

Example of invoking the same stored procedure multiple times within one call.
import { createClient } from '@supabase/supabase-js'
const supabase = createClient('https://world.supabase.co', '1a2b-3c4d-5e6f-7g8h')
const echoCities = async () => {
try{
let cities = await supabase
.rpc('echo_city', [
{ name: 'The Shire' },
{ name: 'Mordor' }
])
return cities
} catch (error) {
console.log('Error: ', error)
}
}

Reading data

This example shows how to call a stored function that returns a table type response:

Example of invoking the same stored procedure multiple times within one call.
import { createClient } from '@supabase/supabase-js'
const supabase = createClient('https://world.supabase.co', '1a2b-3c4d-5e6f-7g8h')
const echoCities = async () => {
try{
let cities = await supabase
.rpc('echo_all_cities')
return cities
} catch (error) {
console.log('Error: ', error)
}
}

Reference

rpc()

supabase
.rpc(functionName, functionParameters)

functionName: string

Name of stored function in the database.

functionParameters: { object? | array? }

Parameters to be passed to the stored function.


Filtering

filter()

Shows how to use filter( ) with rpc( )
import { createClient } from '@supabase/supabase-js'
const supabase = createClient('https://world.supabase.co', 'public-key-bOYapLADERfE')
const echoCities = async () => {
try {
let cities = await supabase
.rpc('echo_all_cities')
.filter({'name', 'eq', 'Paris'})
return cities
} catch (error) {
console.log('Error: ', error)
}
}

General form

supabase
.from(tableName)
.filter(columnName, operator, criteria)

This allows you to apply various filters on your query. Filters can also be chained together.

columnName: string

Name of the database column.

operator: string

Name of filter operator to be utilised.

criteria: { object | array | string | integer | boolean | null }

Value to compare to. Exact data type of criteria would depend on the operator used.


match()

Shows how to use match( ) with rpc( )
import { createClient } from '@supabase/supabase-js'
const supabase = createClient('https://world.supabase.co', 'public-key-bOYapLADERfE')
const echoCities = async () => {
try {
let cities = await supabase
.rpc('echo_all_cities')
.match({name: 'Beijing', country_id: 156})
return cities
} catch (error) {
console.log('Error: ', error)
}
}

General form

supabase
.from(tableName)
.match(filterObject)

Finds rows that exactly match the specified filterObject. Equivalent of multiple filter('columnName', 'eq', criteria).

filterObject: object

An object of { 'columnName': 'criteria' }


eq()

Shows how to use eq( ) with rpc( )
import { createClient } from '@supabase/supabase-js'
const supabase = createClient('https://world.supabase.co', 'public-key-bOYapLADERfE')
const echoCities = async () => {
try {
let cities = await supabase
.rpc('echo_all_cities')
.eq('name', 'San Francisco')
return cities
} catch (error) {
console.log('Error: ', error)
}
}

General form

supabase
.from(tableName)
.eq(columnName, filterValue)

Finds all rows whose value on the stated columnName exactly matches the specified filterValue. Equivalent of filter(columName, 'eq', criteria).

columnName: string

Name of the database column.

filterValue: { string | integer | boolean }

Value to match.


gt()

Shows how to use gt( ) with rpc( )
import { createClient } from '@supabase/supabase-js'
const supabase = createClient('https://world.supabase.co', 'public-key-bOYapLADERfE')
const echoCities = async () => {
try {
let cities = await supabase
.rpc('echo_all_cities')
.gt('country_id', 250)
return cities
} catch (error) {
console.log('Error: ', error)
}
}

General form

supabase
.from(tableName)
.gt(columnName, filterValue)

Finds all rows whose value on the stated columnName is greater than the specified filterValue. Eqiuvalent of filter(columnName, 'gt', criteria).

columnName: string

Name of database column.

filterValue: { string | integer | boolean }

Value to compare to.


lt()

Shows how to use lt( ) with rpc( )
import { createClient } from '@supabase/supabase-js'
const supabase = createClient('https://world.supabase.co', 'public-key-bOYapLADERfE')
const echoCities = async () => {
try {
let cities = await supabase
.rpc('echo_all_cities')
.lt('country_id', 250)
return cities
} catch (error) {
console.log('Error: ', error)
}
}

General form

supabase
.from(tableName)
.lt(columnName, filterValue)

Finds all rows whose value on the stated columnName is less than the specified filterValue. Eqiuvalent of filter(columnName, 'lt', criteria).

columnName: string

Name of database column.

filterValue: { string | integer | boolean }

Value to compare to.


gte()

Shows how to use gte( ) with rpc( )
import { createClient } from '@supabase/supabase-js'
const supabase = createClient('https://world.supabase.co', 'public-key-bOYapLADERfE')
const echoCities = async () => {
try {
let cities = await supabase
.rpc('echo_all_cities')
.gte('country_id', 250)
return cities
} catch (error) {
console.log('Error: ', error)
}
}

General form

supabase
.from(tableName)
.gte(columnName, filterValue)

Finds all rows whose value on the stated columnName is greater than or equal to the specified filterValue. Eqiuvalent of filter(columnName, 'gte', criteria).

columnName: string

Name of database column.

filterValue: { string | integer | boolean }

Value to compare to.


lte()

Shows how to use lte( ) with rpc( )
import { createClient } from '@supabase/supabase-js'
const supabase = createClient('https://world.supabase.co', 'public-key-bOYapLADERfE')
const echoCities = async () => {
try {
let cities = await supabase
.rpc('echo_all_cities')
.lte('country_id', 250)
return cities
} catch (error) {
console.log('Error: ', error)
}
}

General form

supabase
.from(tableName)
.lte(columnName, filterValue)

Finds all rows whose value on the stated columnName is less than or equal to the specified filterValue. Eqiuvalent of filter(columnName, 'lte', criteria).

columnName: string

Name of database column.

filterValue: { string | integer | boolean }

Value to compare to.


like()

Shows how to use like( ) with rpc( )
import { createClient } from '@supabase/supabase-js'
const supabase = createClient('https://world.supabase.co', 'public-key-bOYapLADERfE')
const echoCities = async () => {
try {
let cities = await supabase
.rpc('echo_all_cities')
.like('name', '%la%')
return cities
} catch (error) {
console.log('Error: ', error)
}
}

General form

supabase
.from(tableName)
.like(columnName, stringPattern)

Finds all rows whose value in the stated columnName matches the supplied pattern. Equivalent of filter(columnName, 'like', stringPattern).

columnName: string

Name of database column.

stringPattern: string

String pattern to compare to. A comprehensive guide on how to form proper patterns can be found here.


ilike()

Shows how to use ilike( ) with rpc( )
import { createClient } from '@supabase/supabase-js'
const supabase = createClient('https://world.supabase.co', 'public-key-bOYapLADERfE')
const echoCities = async () => {
try {
let cities = await supabase
.rpc('echo_all_cities')
.ilike('name', '%la%')
return cities
} catch (error) {
console.log('Error: ', error)
}
}

General form

supabase
.from(tableName)
.ilike(columnName, stringPattern)

A case-sensitive version of like(). Equivalent of filter(columnName, 'ilike', stringPattern).

columnName: string

Name of database column.

stringPattern: string

String pattern to compare to. A comprehensive guide on how to form proper patterns can be found here.


is()

Shows how to use is( ) with rpc( )
import { createClient } from '@supabase/supabase-js'
const supabase = createClient('https://world.supabase.co', 'public-key-bOYapLADERfE')
const echoCities = async () => {
try {
let cities = await supabase
.rpc('echo_all_cities')
.is('name', null)
return cities
} catch (error) {
console.log('Error: ', error)
}
}

General form

supabase
.from(tableName)
.is(columnName, filterValue)

A check for exact equality (null, true, false), finds all rows whose value on the state columnName exactly match the specified filterValue. Equivalent of filter(columnName, 'is', filterValue).

columnName: string

Name of database column.

filterValue: { null | boolean }

Value to match.


in()

Shows how to use in( ) with rpc( )
import { createClient } from '@supabase/supabase-js'
const supabase = createClient('https://world.supabase.co', 'public-key-bOYapLADERfE')
const echoCities = async () => {
try {
let cities = await supabase
.rpc('echo_all_cities')
.in('name', ['Rio de Janeiro', 'San Francisco'])
return cities
} catch (error) {
console.log('Error: ', error)
}
}

General form

supabase
.from(tableName)
.in(columnName, filterArray)

Finds all rows whose value on the stated columnName is found on the specified filterArray. Equivalent of filter(columnName, 'in', criteria).

columnName: string

Name of database column.

filterArray: array

Array of values to find a match. Data type of values is dependent on the columnName specified.


not()

Shows how to use not( ) with rpc( )
import { createClient } from '@supabase/supabase-js'
const supabase = createClient('https://world.supabase.co', 'public-key-bOYapLADERfE')
const echoCities = async () => {
try {
let cities = await supabase
.rpc('echo_all_cities')
.not('name', 'Lagos')
return cities
} catch (error) {
console.log('Error: ', error)
}
}

General form

supabase
.from(tableName)
.not(columnName, filterValue)

Finds all rows whose value on the stated columnName does not match the specified filterValue. Equivalent of filter(columnName, 'not', criteria).

columnName: string

Name of database column.

filterValue: { string | integer | boolean }

Value to not match.


Responses

200 OK

Successful request

201 Created

Successful

400 Bad request

An invalid syntax or configuration was sent.

401 Unauthorized

Invalid credentials were provided.

404 Not found

Requested resource cannot be found.

406 Not acceptable

The response provided by the server does not match the list of acceptable values stated in the request's headers.

500 Internal Server Error

The server was unable to encounter the situation it encountered.