CRUD operations on Supabase

Supabase is an open-source Firebase alternative that adds real-time and RESTful APIs to the PostgreSQL database without a single line of code.

To get a detailed overview of connecting Supabase as a connector in Drona studio, give a read to Connecting to a Supabase database

In this article, we will carry out a few queries on our Supabase database such as:

  • Read/Display data
  • Delete data
  • Update data
  • Insert data

Prerequisite

Knowledge of PostgreSQL is a big support for writing queries and understanding the flow of data from the database.

Read/Display data query

Once you are done with configuring the PostgreSQL connector, add a query to your connector to fetch and read the data from the Supabase database.

Query used:

select * from industry_role

Query explanation:

In the above query, we are selecting all the details from our industry_role schema which has a table of details.

Save the query after testing with a configuration test successful message.

To display the data, drag and drop the Table grid under the Featured section of Controls from the sidebar to the created app.

Go to its Bind data.

Select the PostgreSQL connector under Connectors. Continue by opting for your Query of PostgreSQL created to read data and bind it to the table grid. Bind the columns which you want to display.

Displaying:

Delete data query

Now to delete data we will create another query for our PostgreSQL. This query will have a logic to delete all the details related to the selected data from the table.

Query used:

DELETE from industry_role WHERE id = {{id}};

Query explanation:

In the above query, we are operating delete on the industry_role to delete the details of the id selected by the user, which is a primary key. Here the id is made dynamic by creating variable inside double curly braces.

Save the query after testing with a configuration test successful message.

  1. Next is to put the query into the action flow of the table grid. Go to the Actions of the table grid and select delete_click.
  2. Action flow will open, add tasks. The first task of Confirm will give a pop-up to the user to confirm their action of delete.
  3. Next, add the task of Server-side action, select the query of delete data along with binding the id connector key. This will enable us to get the selected row id from the table grid row using tablegrid.result.rows.id, which can be found under the Use Keywords option.

Deleting:

Update data query

To perform updating of existing data, we will create a query and this will first select the details based on id (primary key) and then change the existing data.

Query used:

UPDATE industry_role

SET position = '{{pos}}', location = '{{loc}}' where id = {{id}}

Query explanation:

In the above query, we are selecting details on the basis of the selected id side and changing the values of position and location from details updated in pos and loc variables respectively, in the industry_role schema. Updating that particular row of the selected id.

When we fill the dynamic value inside {{}} it acts as a variable in the query which gets saved to their respective column.

Current data:

Providing the values dynamically for id=12, pos = SDE II and Loc = Kolkata.

After update:

Save the query after testing with a configuration test successful message.

I have created a Tray where text fields will get data from the user and on click of the button, it will update the details of the selected Id.

Here binding of data of the selected id from the table grid to the input text is made by going to the Bind section and selecting controls.

Select the screen from where the data is located, then select the component that is tablegrid, from where we will bind the data, and then select the field name, of which data is needed.

Go to Actions of the button and select button_click action. Add a task of Server-side action with selecting the query created to update the data.

Bind the key connectors properly.

Make sure to navigate to Tray by adding action to table grid on click of edit button by Actions > update_click and add task of Navigate.

image

Updating:

Insert data query

Now we will insert data from the user into the database. For this, we will write a query that will take values from the user and save it to our database.

Query used:

INSERT into industry_role (position, location)

VALUES ('{{pos}}', '{{loc}}')

Query explanation:

In the above query, we are taking values of position and location in pos and loc variables respectively, and inserting them into industry_role. The insertion sequence of the values will be the same as the sequence of the column name provided along with the table name in ()

Save the query after testing with a configuration test successful message.

To insert the values, we have to get it first from the user as input. For this I have created another tray with input texts and a button of insert.

Go to Actions of the button and select button_click action. Add a task of Server-side action with selecting the query created to insert the data.

Bind the key connectors properly.

Make sure to navigate to Tray by adding action to table grid on click of Add button by Actions > add_click and add task of Navigate.

Inserting:

Conclusion

Operations like display, delete, update and insert can be performed on the database through DronaHQ studio by having proper action flows and sets of queries. These data are handled dynamically on a real-time basis.