💾 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.
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 aCOUNT(*)
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 SupabaseFetched
: triggered when data has been succesfully fetched from SupabaseFetch error
: triggered when receiving an error while fetching dataInserted
: triggered when data has been succesfully inserted to SupabaseInsert error
: triggered when receiving an error while inserting dataUpdated
: triggered when data has been succesfully updated on SupabaseUpdate error
: triggered when receiving an error while updating dataUpserted
: triggered when data has been succesfully upserted to SupabaseUpsert error
: triggered when receiving an error while upserting dataDeleted
: triggered when data has been succesfully deleted from SupabaseDelete error
: triggered when receiving an error while deleting dataError Received
: when receiving an error from SupabaseReal-Time Insert Detected
: triggered upon a new record insertion into the databaseReal-Time Update Detected
: triggered upon a record update in the databaseReal-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 aCOUNT(*)
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 theBulk data
state as the value from the database component after adding data to it using theAdd 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 aCOUNT(*)
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 aCOUNT(*)
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 theBulk data
state as the value from the database component after adding data to it using theAdd 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 aCOUNT(*)
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 aCOUNT(*)
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