Supabase is a new, open-source alternative to Google’s Firebase, built on Postgres. The API, authorization and data management are all pretty easy, and the low-code UX/UI is super slick. This makes it a perfect partner-in-crime back end to a Retool front end, and both can be developed in a fraction of the usual time.
For this tutorial we are going to set up a Supabase user database and build an admin panel that allows us to create, read, update and delete the entries. We’ll display the data in a table, and use a panel on the right-hand side to tab between create and update forms—all in less than 20 minutes.
Our end product should look something like this:
Got 20 minutes to spare? Let’s get started.
First of all, we need to set up the data in our Supabase account. For our dummy data, we are going to use the Retool REST API data generator. This is super handy for prototyping apps with fake data, and for staging environments.
Set up your columns and data types, then hit ‘Export to CSV’ in the bottom right corner of the table to save this to your computer.
Next, head to your Supabase account and click ‘New Project’.
Give it a name, set a password and choose a tier. Make sure to save this password somewhere safe, as you’ll need it later to connect to Retool.
Once your new project is ready, create a new table in the ‘Table Editor’ section. Name this table (something SQL suitable) and click ‘Import data via Spreadsheet’ to import your CSV:
Import your dummy data, check it has been imported correctly, and hit save! Your data is ready to go:
With this data set up, we are going to head over to Retool to complete the rest. If you haven’t already, sign up to set up your account.
Once in your Retool environment, click ‘Create New’ app. Name it and add it to a folder if need be.
Your Retool environment will look a little like this:
On the right-hand side you’ll see the components panel, where you can drag and drop from a selection of handy pre-made components, and use the ‘Inspect’ tab to connect to data and change design settings, data validation, and more. In the left-hand column (they are all collapsible using the toggle in the top bar), you can also explore the components, queries, transformers, and constants in your app.
The bottom panel is where you’ll find your queries and resources, as well as any JavaScript queries and transformers. This is where you’ll be connecting your Supabase resource, by clicking the ‘+ New’ button. In the resource dropdown, select ‘Create a new resource’.
In this case, we will select ‘Postgres’ from the resource selection, since this is what Supabase is built on top of:
This will pull up your resource connection form, as below. To find the information for the form, you’ll head back to Supabase. The main variables to ours will be ‘Host’ and ‘Database password’ which you set up when you created the Project.
To grab this info back in Supabase, click settings on the left-hand panel, and then select ‘Database’. Scroll down to find your connection details, as below:
Make sure that you add these carefully in the resource, and then click ‘Test connection’.
You should see a success message appear; if not, check that all your inputs are exactly as they are in Supabase, and that your password is entered correctly. Also, the API should return the exact error to help you troubleshoot.
Once your connection has been made successfully, you can ‘Save changes’ and close this resource modal.
In the bottom queries panel, you can now write standard SQL queries against your data. On the right-hand side of this panel, you can see the ‘Schema inspector’, which will give you details about the structure of your Postgres database.
Let’s start with a simple SQL query to pull in all the data:
select * from retool_app
And hit ‘Save & run’. It should return something like this:
Once this is done and your data has been pulled successfully, rename the query to something easy to reference again later; we used ‘SupabaseGet’. More info on writing queries with SQL in the Retool docs.
Now that your data is all connected, all that’s left is to set up the CRUD interface with some data and action features. Good news—Retool makes this part super easy!
First things first, let’s give this app a clear title, by dragging in a text component and adding the name of the app in the ‘value’ box of the Inspect panel. You can use Markdown to set the sizing and more.
Next, pull in a table component for your data. Since your Supabase query is already set up, this will automatically connect in the ‘data’ box in the Inspect panel. You’ll see that this query reference is in {{ }} curly brackets—you can use these anywhere in Retool to write JavaScript, to reference other components, queries, scripts, drill down into JSON objects and anything else you can do in JS!
In the Inspect panel of the table component, you’ll see that all kinds of settings are available to you, including various simple toggles to alter the UI. Under the ‘columns’ section, you also have the ability to quickly show/hide columns (the eye icon), toggle edit capabilities and create more detailed changes in individual columns:
Find out more about the table component settings in the Retool documentation.
Next, we’re going to pull in a ‘Tabbed container’ component next to our table, and set one tab to ‘Edit User’ and the other to ‘New User’, for our Create and Update functions.
Next, add the ‘form’ component to the ‘New User’ tab - this is where we’ll add all our input components to add new user data. Retool speeds up the creation of such forms by generating them automatically from your resource—just click the ‘generate from a resource’ link in the form to get started.
Pick the correct resource and table in the dropdowns and the form generator will do the rest for you. Kust make sure to adjust the input types to your data and toggle the ‘required’ checkbox according to your data validation needs. When done, just click ‘Generate form’ and Poof! the form is all ready for you to go. No heavy coding necessary.
Before we move on, let’s make some quick UI tweaks, by setting the labels to be positioned on ‘Top’ of the input box in the Inspect panel. You can read more about how to improve CRUD UI in this UI for forms blog post.
It’s also best practice to name each component according to its function, so that you (and other developers) can later reference these components more easily. Finally, give the form a title so it’s clear to your users.
Helpfully, when we generated this form, it automatically created a query to the Supabase resource called something like ‘newUserFormSubmitToRetoolApp’:
As you can see, this query has linked to our Supabase resource in GUI mode (a way of applying actions to our SQL database without writing any code) to ‘insert a record’ to the table on submit. The form component is already in object format, so that can be referenced directly using curly braces, and this data will be entered into your backend. You can also switch this over to Key value pairs, if needed.
Before testing, add an event handler to this new user query to trigger your ‘Get’ query, and therefore refresh your data with this new user.
Now you can add some data to your form and give it a whirl to check everything’s set up correctly.
With that done, let’s head to the ‘Edit User’ tab to set up a couple of inputs for users to edit.
In this case, we want the default value of each edit field to be linked to the data in the table. In Retool we can just do this by referencing table.selectedRow.data and then drilling into the correct value. Best practice is to use camel case for these headers to avoid needing to insert strings.
Insert the components you need for your edit functions. We only needed a couple of fields to be editable, so used two text inputs and a checkbox for ours. Drag in a button and add a ‘Save Updates’ label; this is what you will later connect to your update query.
To make this query, all we’re going to do is duplicate the newUser query so that our settings are the same. We’ll keep this query in GUI mode, but change the action to be ‘update an existing record’.
Use a unique ID, such as name, email or ID to filter the data for the update, then reference the selectedRow of the table. In the changeset, we’re going to switch over to Key value pairs and use the dropdowns to select a column. In the ‘value’ column, reference the .value property of each of your update user form inputs.
Check that your event handler will trigger your getData query on success (this should have copied across), hit Save & Run, making sure to test with some dummy data to check it’s all working.
Now that your query is ready, head back to your ‘Save Updates’ button and add an event handler to trigger this query on click.
Feeling fancy? Go ahead and add some confetti too! You can use event handlers in Retool for all sorts of funky things, including triggering queries, scripts, controlling components and setting temp state and local storage.
Our last task is to put the ‘d’ in CRUD app with a delete function. Just as quickly as you set up your new users, you can delete them too. Once again, duplicate one of your ready-made queries, and set the action this time to ‘Delete a record’:
Set the filter to be by a unique value in your data and link it to the selectedRow in your table. Save and test with dummy data.
To add this functionality, we are going to add a custom column to our table and set the type to ‘button’. Title the button value and set the click event handler to trigger your delete query.
Once again, test with some dummy data to make sure everything is up to scratch. For additional UI features, add a confirmation message to your delete query, just to make sure no one is accidentally deleting key data without realizing. You can do this by going into your query settings, heading to the advanced tab, and checking the ‘Show a confirmation message before running’ box:
Add a custom message, and link it back to the selectedRow for clarity. This will be the message that appears when a user clicks the delete button:
Now there is one step of confirmation between your users and that dangerous ‘delete’ button!
For some final, speedy styling, you can add a logo to the header section and adapt some of the styling of your components - you can even specify custom CSS if you want to go further.
Click the ‘Share’ button in the top right to distribute this app to your team, as editors, viewers or even as a public app.
And with that, in less than 20 minutes, you not only have a Supabase backend but also a supa-UI-friendly Retool frontend for your team to quickly create, read, update and delete your data!
Reader
