💾 Database

Interact with your Supabase database directly from your Bubble.io app.


In this section, we'll explore the process of interacting with the PostgreSQL database provided by Supabase from within your Bubble.io app.


Setup

Add the element Supabase Database to your page.

Ensure that you use a separate database component for each Supabase table that you want to manage within your Bubble.io application.


You can configure the following fields:

  • Name
    Data Type
    Type
    App Data
    Description

    You must first initialize the API Connector in order to select the correct type.
    Please refer to this section for detailed instructions.

  • Name
    Table
    Type
    string
    Description

    Supabase table name (e.g todos). Optional if a function name is provided

  • Name
    Function Name
    Type
    string
    Description

    Supabase function name. Only used for RPC. Optional if a table name is provided

  • Name
    Listen to changes in real-time?
    Type
    boolean
    Description

    Receive real-time updates on Insert, Update or Delete.


Fetch settings

  • Name
    Fetch data on page load?
    Type
    Boolean
    Description

    Automatically fetch data when the page loads. Only used if table name is not empty. You can still fetch data anytime using the Fetch action in your workflow.

  • Name
    Columns
    Type
    string
    Description

    Allows you to specifiy specific columns. If you leave columns empty or set it to *, it will fetch all columns in the table.

  • Name
    Filters
    Description

    Check this section for more details.

  • Name
    Order By
    Type
    text
    Description

    The column to order by. Leave empty to not order results.

  • Name
    Order
    Type
    Dropdown
    Description

    Specify the sorting order. Ascending or Descending.

  • Name
    Limit Count
    Type
    Number
    Description

    The maximum number of rows to return. Setting this to 0 does not apply any limit.

  • Name
    Limit By Range
    Type
    Boolean
    Description

    When set to true, the query will limit results to a specific range defined by 'Limit From' and 'Limit To'.

  • Name
    Limit From
    Type
    Number
    Description

    The starting index from which to limit the result. This index is 0-based, meaning an index of 0 refers to the first record.

  • Name
    Limit To
    Type
    Number
    Description

    The last index to which to limit the result. This is inclusive, so the record at this index will be included in the result.


Supabase - Database settings

Database settings - Interact with a table

Supabase - Database settings

Database settings - Call a Postgres function (RPC)


States

The element exposes the following states:

  • Objects: contain the objects retrieved from your Supabase table or RPC call.
  • Object: in case the results contain only one object, this field will contain that specific object retrieved from your Supabase table of RPC call.
  • Status Code: the status code will be '200' in case of success, and a different code in case of an error.
  • Status Message: the status message will be 'success' in cases of success, and it will contain error details in cases of an error.

Events

The element triggers the following event:

  • Has Changed: when receiving new objects from Supabase
  • Error Received: when receiving an error from Supabase
  • Real-Time Insert Detected: triggered upon a new record insertion into the database
  • Real-Time Update Detected: triggered upon a record update in the database
  • Real-Time Delete Detected: triggered when a record is deleted from the database

Using filters

Filters allow you to only return rows that match certain conditions.
The actions Fetch, Update, Delete and Call a function (RPC) offers two ways to define filters.

You have the option to use either one method or both, as the filters will be combined for the final query to your Supabase instance. The choice is yours.


Method 1 - Using the filters dropdown

This is easiest and the recommanded way to define filters on your actions. You can define up-to 5 filters to apply on your query.

Supabase - Filters

A filter is defined by:

  • Name
    Filter Type
    Type
    Dropdown
    Description

    The filter to apply

  • Name
    Filter Name
    Type
    string
    Description

    The name of the column to filter on

  • Name
    Filter Value
    Type
    string
    Description

    The value of the filter


Method 2 - Using JSON format

You can also define filters through JSON. The format must follow this structure:

Filters

{
  "<Filter Type>": {
    "<Field Name>": "<Field Value>"
  }
}

Where:

  • Name
    <Filter Type>
    Type
    string
    Description

    Can be one of the following:
    eq, neq, gt, gte, lt, lte, like, ilike, is, in, contains, containedBy, rangeGt, rangeGte, rangeLt, rangeLte, rangeAdjacent, overlaps, textSearch

  • Name
    <Field Name>
    Type
    json
    Description

    The name of the field you want to apply the filter on

  • Name
    <Field Value>
    Type
    json
    Description

    The value you want to filter by

Supabase - JSON filters

JSON filters

Examples:

Fetch only rows where completed column is true

{
  "eq": {
    "completed": true
  }
}

Fetch only rows where completed column is true and title is "Running"

{
  "eq": {
    "title": "Running",
    "completed": true,
  }
}

Fetch data

Perform a SELECT query on the table or view. You can configure the following fields:


  • Name
    Columns
    Type
    string
    Description

    Allows you to specifiy specific columns. If you leave columns empty or set it to *, it will fetch all columns in the table.

  • Name
    Use Filters For Real-Time Update
    Type
    Boolean
    Description

    Use the selected filters for incoming real-time updates. This only applies if you select the option 'Listen to changes in real-time' on the database component.

  • Name
    Filters
    Description

    Check this section for more details.

  • Name
    Order By
    Type
    text
    Description

    The column to order by. Leave empty to not order results.

  • Name
    Order
    Type
    Dropdown
    Description

    Specify the sorting order. Ascending or Descending.

  • Name
    Use Ordering Options for Real-Time Update
    Type
    Boolean
    Description

    Select this to use the selected ordering options for incoming real-time updates. This only applies if you select the option 'Listen to changes in real-time' on the database component.

  • Name
    Limit Count
    Type
    Number
    Description

    The maximum number of rows to return. Setting this to 0 does not apply any limit.

  • Name
    Limit By Range
    Type
    Boolean
    Description

    When set to true, the query will limit results to a specific range defined by 'Limit From' and 'Limit To'.

  • Name
    Limit From
    Type
    Number
    Description

    The starting index from which to limit the result. This index is 0-based, meaning an index of 0 refers to the first record.

  • Name
    Limit To
    Type
    Number
    Description

    The last index to which to limit the result. This is inclusive, so the record at this index will be included in the result.

  • Name
    Use Limits Options for Real-Time update
    Type
    JSON
    Description

    Select this to use the selected limits options for incoming real-time updates. This only applies if you select the option 'Listen to changes in real-time' on the database component.


Insert data

Perform an INSERT into the table or view.
You can configure the following field:


  • Name
    Payload
    Type
    Array
    Description

    The values to insert into your Supabase DB.

  • Name
    JSON Payload
    Type
    JSON
    Description

    The values to insert into your Supabase DB in JSON format.


You have the option to use either Payload, JSON Payload, or both. The values from these will be combined for the final call to your Supabase instance.

Supabase - Insert new data

Database - Insert new data


Update data

Perform an UPDATE on the table or view.


  • Name
    Payload
    Type
    Array
    Description

    The values to update your object with.

  • Name
    JSON Payload
    Type
    JSON
    Description

    The values to update your object with in JSON format.

  • Name
    Filters
    Description

    Check this section for more details.


Delete data

Perform a DELETE on the table or view. You can configure the following field:



RPC

Perform a function call (RPC) wich allows you to execute logic in your database.
You can configure the following field:


  • Name
    Payload
    Type
    Array
    Description

    The arguments to pass to the function call.

  • Name
    JSON Payload
    Type
    JSON
    Description

    The arguments to pass to the function call in JSON format.

  • Name
    Filters
    Description

    Check this section for more details.

  • Name
    Head
    Type
    boolean
    Description

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

  • Name
    Count
    Type
    Choice
    Description

    Count algorithm to use to count rows returned by the function. Only applicable for "set-returning functions".


Supabase - RPC

Perform a function call (RPC)


RPC example

We'll create the function hello_world_with_name that takes a single text argument name, and returns a JSON object containing a greeting message concatenated with the provided name.


1 - Create the RPC function on Supabase

Function

CREATE OR REPLACE FUNCTION hello_world_with_name(name text)
RETURNS json AS $$
BEGIN
  RETURN json_build_object('message', 'Hello world ' || name);
END;
$$ LANGUAGE plpgsql;

2 - Paste the schema to the Bubble.io API Connector

Our function will returns the following JSON schema:

{
  "message": ""
}

All we need to do is to initialize the API connector in order to use the format in our database component:

Paste the schema to the API Connector


3 - Initializing the database component

You can now add a database component to your page:

Initialize a database component


And finally simply call the function from a Bubble workflow like this:

Supabase - RPC Call

Perform a function call (RPC)

You can then retrieve the result of the call from the Object state in your database component.


Do you need any help? Feel free to reach out