Supabase database select

If you have installed the Supabase data source plugin, you will have access to the Supabase Database select action in workflows:

Supabase Collection vs Supabase Workflow

When you fetch Supabase data through a WeWeb workflow, you have more control over the data Supabase returns (more on these options below) than when you create a Supabase data collection.

This is because, for now, we want the UX of data collections to be the same no matter what data source plugin(s) you are using.

We are exploring solutions to add these Supabase options at collection level as well.

Guided mode

By default, when you make a SELECT call to your Supabase data base, you will fetch all the fields from the Supabase table you selected:

In the example below, we can see we are in Guided mode, fetching All fields, which returns 1,000 objects (from index 0 to 999) with all the fields from our Supabase table.

Still in Guided mode, you could choose to fetch only the information from the id, mileage, location_id and vin fields:

Advanced mode

In Advanced mode, you can also get data from referenced tables:

In the example above, you can see that:

• we fetch the values in the id, mileage, and vin columns of the vehicles table
• fetch the values in the name and type columns of the locations table based on the value of location_id in the vehicles table

For reference, this is what our locations table looks like in Supabase:

As a result, Supabase returns a location_id object with the values of the name and type of that location (in our Guided mode example, Supabase simply returned the id of the location, i.e. location_id: 1).

Filters

To fetch filtered data from Supabase, you can click on Add filter and configure the filter(s) of your choice:

In the example above, we have a simple filter to fetch only the items where the value in the make column exactly matches Chevrolet.

As result, Supabase only returns 90 items (from index 0 to 89) and all these items are Chevrolet vehicles.

Learn more about Supabase filters in their user documentation.

Count results

By default, when you select this option, Supabase will return an object with:

• the number of items returned (the count), and
• the list of items (the data)

You can choose to return only the count of items without the list of items switching Return count only to On.

Count algorithms

By default, WeWeb will request the Exact count of items to Supabase, but you can ask Supabase to returned the Planned or Estimated count instead.

• Exact will return the precise number of rows
• Planned will return the count from Postgres' statistics table
• Estimated will return the same number as Exact if the total count is small or the same number as Planned if the total number is large.

TIP

If you are unsure which count algorithm to choose, Supabase recommends starting with Exact and explore other options when performance becomes an issue.

Learn more about Supabase count algorithms.

Order the results

With this option, you can order the items in ascending or descending order:

In the example above, the cars are ordered by mileage value, in descending order (Ascending is Off). As a result, cars with the highest mileage are listed first.

Order by referenced table value

You can order items based on the value of a related field.

For example, in our vehicles table, we have a location_id field that references the locations table.

In the Fields step of our select configuration, we used the Advanced mode to fetch the related name and type values of each location record:

Now, we can order our vehicles based on the name of the location it's in:

WARNING

You can only order data based on a reference field that you have accessed in the Advanced mode of the Fields configuration.

If we tried to sort by location name without first telling Supabase how to get the name value, it would return an error:

This is because, by default, location_id is the id value of the item in the locations table, it is not an "embedded resource" with additional information about the corresponding name value of the item.

Limit number of rows

With this option, you can limit the number of rows returned by Supabase. In the example below, you can see Supabase only returns 10 items as requested (from index 0 to 9):

This can be very helpful when setting up custom pagination. You can bind the limit to a variable that is updated when a user clicks on a load more button for example.

TIP

You can also limit the number of rows returned in a field referencing another table.

Learn more about adding a limit on a referenced table.

Limit range

With this option, you can ask Supabase to return a range of items with a start index and end index, for example from index 10 to index 19:

This can be very helpful when setting up custom pagination. You can bind the limit to a variable that is updated when a user clicks on a paginator element for example.

TIP

Notice how Supabase returned items with id 11 to 20 in our example above.

This is because:

• we had no special sort or filters in our query,
• by default, items are sorted in ascending order by id,and
• in programming, the first item of a list is index 0.

Since item 1 is index 0 and we have no special sort or filters, it follows that item 11 is index 10 in our example.

Retrieve one row

With this option, you can return the first row of the query. In the example below, since we have no filter on our query, Supabase returns the first item in the vehicles table:

Retrieve as CSV

With this option, you can get the data as a CSV:

TIP

The data retrieved from Supabase as a CSV uses ,, to separate rows.

You can download this data in a nicely formatted CSV file by:

1. adding the CSV plugin to your WeWeb project,
2. testing your Supabase action first to get a result you can use in the Download data as CSV action
3. binding the result of the Supabase action with a split formula to format the Supabase data

Explain

EXPLAIN is a PostgreSQL command that can help you understand and optimize how your queries are performing.

This is an advanced backend feature that we recommend learning through the user documentation of Supabase and its underlying technology PostgreSQL.