Connecting to a Supabase database

Studio enables you to use the database connectors to connect to any of your databases like Elasticsearch, MongoDB, CouchDB, DynamoDB, Cloud DataStore, Firebase, Supabase, and so on.

Supabase as you are aware is an open-source Firebase alternative. It uses the scalable Postgres Database and listens to real-time changes through different tools that Supabase has built. At the moment Supabase has the features like databases, authentication, and storage.

Studio enables you to connect and build over your Supabase databases. Let us understand how to configure your Supabase Connection. As Supabase uses a Postgres Database we will make use of the Postgres Connector from Studio to connect to the Supabase Database.

To get started with your hosted platform on Supabase, go to the app.supabase.io and sign up if you are not already a registered user. To log in use your GitHub ID.

After you are logged in you can see that an organization has been created for you with the Github ID that you have used.

  • Now create a new Project. Click New Project and select an organization with Choose Organization or add a New organization. In this example let us use the default organization that is present in the project.

    new project

  • Now when you select the organization, enter the project Name and a Database Password. Select the Region and click Create New Project.

  • When you click Create New Project, it starts provisioning the database and API endpoints.

  • The New Supabase project is deployed and has its own URL and API ready. You can see the Database which allows you to add the Tables, Auth which makes it simple for you to add and manage users to the database, and Storage to store large files.

  • Supabase is secure with its API Gateway which needs an API key for each request. Your Project API keys are ready and you can use them to use the Supabase client libraries.

Getting the Connection String

To connect to Supabase from your Studio App you need the Connection String.

  • So now if you go to any of your Supabase databases, then on the left sidebar you can find the Database option.

  • Click Database. You will be taken to the Configuration and the Connection info which can be used to create the connection with the Supabase database from your applications.

You can copy the connection string or the connection parameters from here and paste them to your Supabase connector configuration

Table Editor

You can add the tables to your database using the Table Editor.

  • On the left sidebar, under Home you can find the Table Editor.
  • Click Create a New Table to add a new table.
  • Enter the Name, Description (optional), and then add Columns. In case you want to restrict access to your table, you can do so by Enabling the Row Level Security(RLS). Verify the columns and click Save.
  • In this example we have added a simple Product table. Following is the illustration of how it will look like in the table editor. We have also added the data for illustration purposes.

Configuring the Connector

You are now aware of how the Supabase Database can be created and tables added. You can build your apps on this data for different purposes. Now let us see how to configure the Connector.

Now when you want to build your app in Studio, using the Supabase data then go to Connectors and click + Connector. Select PostgreSQL connector.

Using the username and password to connect

By default, you can make use of the basic authentication to connect to Supabase using the Username and Password.

So, when configuring the Connector, copy the Connection information from the Database > Connection Pooling> Connection Info.

Now your Supabase connection is ready from within your Studio App. You can define queries as per your use case and bind the data to the Controls and Actions.

Using CA Certification

By default, you can make use of the basic authentication to connect to Supabase using the Username and Password. However, for additional security that can help prevent snooping and man-in-the-middle attacks, you can also add the CA Certificate authentication to your connector.

So, when configuring the Connector, copy the Connection information from the Database > Connection Pooling> Connection Info. Here simply download the certificate.

Whenever you configure your Connector, toggle switch ON to Use a self-signed certificate. Here, you can copy and paste the Certificate Authority content that is required for self-signing.

You will thus ensure further security while connecting and using data from the Supabase connection. You can start using your queries to get the data and create your apps as per your scenario.

Add query to the Database connector

Now that you have configured the connector you will find it under the Connectors list. To fetch data or undertake any other action, click the Add query option available. You can add your SQL queries to fetch data using the Syntax as specified in the Supabase Documentation.

Click Add Query and create a query. Enter a Query name and enter your SQL Query. Press Ctrl + enter or click Test Query to run the query. The rows returned from the database are shown in the Response section.

You can view the Queries that you create under your Connector.

Using Supabase Connector

Show all records

Let us consider an example to display data from the Supabase Table for Product created within the Database that was used in the example for Connection String.

Let us add a form with a simple Table grid to display the data from the respective table. First add the Query in the Connector. You need to add a SQL Query to get the data. So in this case under Write your Query, enter the Query as follows:

Select * from “Product"

When adding the Query you need to enclose the Table name in double-quotes as seen above, Click Ctrl+Enter or Test Query to run the query and view the output.

After you test the output, provide a Query name and click Save.

  • From the respective form, select the Table grid and under Bind Data > Connectors, click Select Connector.

  • Under Connectors, select your configured Connector and click Continue.

  • Select the Query from the available list and click Continue.
    select query

  • Add the Connector name. You can transform the Response and customize it with the Custom script if required. Then click Test and Finish.

  • Select the Keys from the Configured Connector and click Save.

  • If you run the form, you can see all the Data from the respective Table that was selected.

Get data for a specific product

Let us consider an example to view the data for a specific product. In this case the query that you create can have a Where condition or can contain the Like operator depending upon the data that you want to fetch.

Let us consider an example to fetch data for only those products containing the name provided dynamically by adding the variable in the query. This will be a dropdown control.

The item selected from the dropdown would be the dynamic value passed to the query. So when you add the Query you need to specify the ‘Like’ operator as seen in the example below with the variable enclosed in double curly brackets. It will get all the records from the table that satisfy the condition.

Let us get the list of these products and display them in a Detail view control.

  • Now for the Detail View control, go to Bind Data > Connectors and click Select Connector.

  • Under Connectors, select your configured Connector and click Continue.

  • Select the Query from the available list and click Continue.

    get by value

  • Authenticate with the data environment and click Continue.

  • Add the Keywords to get the data from the item selected from the dropdown control.

  • Enter the Connector name. You can transform the Response and customize it with the Custom script if required. Then click Test and Finish.

  • Select the Keys from the Configured Connector and click Save.

  • If you run the form, you can see all the Data from the respective Table that was selected.

You can use the SQL Queries to get data and carry out different operations as per your scenario.