💾 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.

Please ensure that a Supabase Auth component is visible on every page of your app. This is crucial because this component initiates the connection with your Supabase instance.


Setup

Add the element Supabase Database to your page.

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

Supabase - Database setup


You can configure the following fields:

  • Name
    Data type
    Type
    App type
    Description

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

  • Name
    DB schema
    Type
    App type
    Description

    The Postgres schema which this table belong to.

  • Name
    Table
    Type
    text
    Description

    Supabase table name (e.g todos).

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

    Receive real-time updates on Insert, Update or Delete. By default, realtime is disabled on the Supabase database.

  • Name
    Link ID
    Type
    text
    Description

    Propagate the states and events of this database to data container components that share the same Link ID. Useful for displaying database results on reusable components, for example.


Fetch settings

  • Name
    Fetch on page load?
    Type
    Boolean
    Description

    Automatically fetch data when the page loads. You can still fetch data anytime using the Fetch action in your workflow.

  • Name
    Columns
    Type
    text
    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

    Please check this section for more details.

  • Name
    Nulls first?
    Type
    Boolean
    Description

    If true, null values are treated as the lowest possible values when sorting in ascending order and as the highest possible values when sorting in descending order. This applies to all columns where ordering is enabled. The default is true.

  • Name
    Order By
    Type
    text
    Description

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

  • Name
    Ascending order?
    Type
    Boolean
    Description

    When set to true, the query will use an ascending order for this column.

  • 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
    Count
    Type
    Dropdown
    Description

    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.
  • Name
    Head
    Type
    Boolean
    Description

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

  • Name
    Expected response (JSON)
    Type
    JSON
    Description

    By default, the plugin attempts to automatically retrieve the expected result based on the type defined in the API connector. To prevent any retrieval failures and ensure that the plugin can properly publish results from Supabase, we recommend you paste your schema into this field. This schema should match the one you have already entered in the API connector.


States

The element exposes the following states:

  • Objects: contain the objects retrieved from your Supabase table.
  • Object: in case the results contain only one object, this field will contain that specific object retrieved from your Supabase table.
  • 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.

Count

  • Count: number of rows fetched when using the options count.
  • Count insert: number of added rows when using the options count.
  • Count update: number of updated rows when using the options count.
  • Count delete: number of deleted rows when using the options count.
  • Count upsert: number of upserted rows when using the options count.

Last

  • Last insert: last object added using the insert action.
  • Last update: last object updated using the update action.
  • Last upserts: last object upserted using the upsert action.
  • Last inserts: list of objects added using the insert action.
  • Last updates: list of objects updated using the update action.
  • Last upserts: list of objects upserted using the upsert action.

Events

The element triggers the following event:

  • Has Changed: when receiving changes from Supabase
  • Fetched: triggered when data has been succesfully fetched from Supabase
  • Fetch error: triggered when receiving an error while fetching data
  • Inserted: triggered when data has been succesfully inserted to Supabase
  • Insert error: triggered when receiving an error while inserting data
  • Updated: triggered when data has been succesfully updated on Supabase
  • Update error: triggered when receiving an error while updating data
  • Upserted: triggered when data has been succesfully upserted to Supabase
  • Upsert error: triggered when receiving an error while upserting data
  • Deleted: triggered when data has been succesfully deleted from Supabase
  • Delete error: triggered when receiving an error while deleting data
  • 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

Explore our demo app to see how to effectively use the database component in your app.


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

    Please check this section for more details.

  • Name
    Nulls first?
    Type
    Boolean
    Description

    If true, null values are treated as the lowest possible values when sorting in ascending order and as the highest possible values when sorting in descending order. This applies to all columns where ordering is enabled. The default is true.

  • Name
    Order By
    Type
    text
    Description

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

  • Name
    Ascending order?
    Type
    Boolean
    Description

    When set to true, the query will use an ascending order.

  • 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.

  • Name
    Count
    Type
    Dropdown
    Description

    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.
  • Name
    Head
    Type
    Boolean
    Description

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


Insert data

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


  • Name
    Insert type
    Type
    Dropdown
    Description

    Inserting a single row (using Payload and/or JSON payload) or multiple rows (using List payload or List JSON payloads).

  • Name
    Payload
    Type
    Array
    Description

    The values to insert into your Supabase DB when using single insert.

  • Name
    JSON Payload
    Type
    JSON
    Description

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

  • Name
    List payloads
    Type
    Array
    Description

    The values to insert into your Supabase database when the insert type is set to "bulk".
    This must be a list of items that match your table schema.
    You can use the Bulk data state as the value from the database component after adding data to it using the Add item to bulk data action.
    If this field is specified, "List JSON payloads" will be ignored.

  • Name
    List JSON payloads
    Type
    JSON
    Description

    The values to insert into your Supabase DB in JSON format. Must be a valid array of JSON objects.
    If "List payload" is specified then this field is ignored.

  • Name
    Default to null?
    Type
    Boolean
    Description

    If true, missing fields in the payload will be inserted as null values. Otherwise, missing fields will used the the default column value. Defaults to true. Only applies for bulk insert.

  • Name
    Count
    Type
    Dropdown
    Description

    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.
  • Name
    Return inserted rows?
    Type
    Boolean
    Description

    If you set "true," a select operation will be executed post-insertion, and the inserted row(s) will be available in the "Last insert" and the "Last inserts" states.
    This requires that you have an RLS (Row Level Security) policy that allows "select" on the table.


When using single insert, 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.


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

    Please check this section for more details.

  • Name
    Count
    Type
    Dropdown
    Description

    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.
  • Name
    Return updated rows?
    Type
    Boolean
    Description

    If you set "true," a select operation will be executed post-update, and the updated row will be available in the "Last update" and "Last updates" states.
    This requires that you have an RLS (Row Level Security) policy that allows "select" on the table.


Upsert

Perform an UPSERT on the table or view.

  • Name
    Upsert type
    Type
    Dropdown
    Description

    Upserting a single row (using Payload and/or JSON payload) or multiple rows (using List payload or List JSON payloads)

  • Name
    Payload
    Type
    Array
    Description

    The values to upsert with. These will be automatically merged with the JSON payload field. To ensure a value is treated as a string, enclose it in double quotes (e.g., "text").

  • Name
    JSON Payload
    Type
    JSON
    Description

    The values to upsert with in JSON format. These will be automatically merged with the payload field.

  • Name
    List payloads
    Type
    Array
    Description

    The values to upsert into your Supabase database when the upsert type is set to "bulk".
    This must be a list of items that match your table schema.
    You can use the Bulk data state as the value from the database component after adding data to it using the Add item to bulk data action. If this field is specified, "List JSON payloads" will be ignored.

  • Name
    List JSON payloads
    Type
    JSON
    Description

    The values to upsert into your Supabase DB in JSON format.
    Must be a valid array of JSON objects (e.g. [{"c1": "v1", "c2", "v2"}, {"c1": "v1", "c2", "v2"}]).
    If "List payload" is specified then this field is ignored.

  • Name
    Default to null?
    Type
    Boolean
    Description

    If true, missing fields default to "null".
    Otherwise, missing fields will used the the default column value. This only applies when inserting new rows, not when merging with existing rows under "Ignore duplicates?" is false. Defaults to true. Only applies for bulk upsert.

  • Name
    Count
    Type
    Dropdown
    Description

    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.
  • Name
    On conflict
    Type
    text
    Description

    Unique column(s) to specify how duplicate rows are determined. Two rows are duplicates if all the "On conflict" columns are equal.

  • Name
    Return upserted rows?
    Type
    Boolean
    Description

    If you set "true," a select operation will be executed post-upsert, and the upserted rows will be available in the "Last upsert" and "Last upserts" state. This requires that you have an RLS (Row Level Security) policy that allows "select" on the table.


When using single upsert, 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.


Delete data

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


  • Name
    Filters
    Description

    Please check this section for more details.

  • Name
    Count
    Type
    Dropdown
    Description

    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.

Export to CSV

Export current results (Objects state) as CSV.


Add item to bulk data

Adds a single item to the Bulk data state of the database component.
You can then use the Bulk data state to bulk insert objects into your table.


Remove item from bulk data

Removes a single item from the Bulk data state.


Clear bulk data

Clears all items from the Bulk data state.


Do you need any help? Feel free to reach out