LinkedIn
Twitter
Most of you rely on Spreadsheets to feed AppSheet apps with data, the heart of any AppSheet app. Now, at some point we start to think about moving to SQL if we want to scale, integrate with other systems, and a little bit better performance. In this case, I want to show you an alternative to the common Hosting VPS + SQL on gnu/linux scenario that most of us follow when thinking of a SQL database.
Supabase, the firebase of SQL
First of all (maybe it's needed for some) I don't receive any money by talking about Supabase. It's just a good service from my POV.
It markets itself as an "Open Source Firebase Alternative" although it doesn't provide an exact Firebase clone or something similar.
It's not a No-SQL and most of the services provided through Firebase are not there, but it has the core functionality we might expect, one of them a database with real time access, which actually is not that important for us, AppSheet creators.
The interesting part is that at the core of it's Real Time support is a Postgres database that is managed via commands as well as a UI that makes it similar to AirTable, so we have a managed Postgres database with a good UI and competitive pricing.
Some strong points for me are that they offer two projects for free as well as beign built on top of Open Source tech and leaving most of the company's resources Open Source. You can even self host your own Supabase via docker, so no vendor lock-in.
You also have database and edge functions that can make your database work on ways that are so advance that I just don't have enough knowledge to help you with them, but there you have it. (In short, you can run functions on cloud based on database changes, similar to AppSheet automation but way more advance, like calling an API and get that data when there is an Add to the database)
Another good point IMO is that we can think of AppSheet as just another client of our backend, so we can integrate with a lot more platforms. We can create a web app with some JS framework, a mobile app with Flutter or React, and use the same data.
Maybe all of this is a little too much for the average AppSheet creator, and it is since this topic is about SQL, an Enterprise feature (yikes). But I think that we should always think ahead of time when it comes to our data.
The main purpose of this post it to help you interact with SQL on a free and easy to configure way. You can use SQL on a prototype app and Supabase offers two projects for free with 500mb to store your database (among other non-AppSheet-userful things). So without further ado, let's configure Supabase and AppSheet.
Config
The Supabase side
First, you need to have a GitHub account in order to sign in. GitHub is a Git service owned by Microsoft and you can create an account here. Update: A Github account is no longer needed, you can create a Supabase account using an email as any other platform.On Supabase, sign in using your GitHub account. Update: Sing-up using an email or Github if you want. I won't cover so much detail here but the important stuff is the following:
- You have to create an Organization inside Supabase, each one can have a different plan/pricing.
- You have to create a Project, each one is related to an Organization of your choice and will have a Postgres database + other cool stuff. Remember the password that Supabase will ask you for the project because we will use it later.
If you need extra help with this step, check the Supabase docs or write a comment down below.
Here is a good resource to understand the features of Supabase that matter to us.
After creating a project you can find it inside the Supabase Dashboard, click on it and go to the first option called "Table Editor" on the left side menu.
Here you can add your tables, similar to a spreadsheet. Make sure that the schema selected on the left up corner is "public". Other ones have data related to the other goodies of Supabase.
When you create a new table you have to give it a name and add the columns you think you will need, this columns can be modified later.
Make sure to keep unchecked the RLS and Real Time options. This is because of a limitation of how AppSheet accesses the data.
I have to do more testing to know wheter RLS is supported inside AppSheet or not, maybe by configuring the Tables to be accessed as App User, but I suspect that it's just not supported which could lead to a good Feature Request once I test it enough.
Column types is also a topic I haven't tested enough yet. In general Text columns do the job along with the basic datetime, boolean (y/n), int8 (number) and float8 (decimal). Here is a comprehensive documentation around column data types in postgres.
The AppSheet side
To connect to your newly created database you need to add it as a new data source in your account here. Give it a name (eg. "Supabase") and select "Cloud Database".
Before you continue, go to your Supabase Dashboard to get the required info.
- Click on your project.
- Go to Settings, which is the last option of the left side menu.
- Select "Database" and scroll down untill you find "Connection Info"
- Copy your Host url
as well as Port. Update, read bellow about Connection pooling
After that, you can complete the data asked on AppSheet this way:
Type: PostgressServer: Host:PortUpdate: After some issues with trying to add hundreds of rows and being faced with a limit, I discovered that using the port shown in the "Connection info" section is not needed for our use. If you scroll down a bit you will find information about the "Connection pooling" settings which will prevent the issue I faced earlier. The port mentioned there is the one we need to useServer: Host:6543Database: postgresUsername: postgres
Update Jan. 2024: Current implementation already considers pooling. Go to the database settings and check your info
Then add this data following the logic:
AppSheet field: Supabase field
Type: Postgress
Server: Host:6543
Database: Database name (It's "postgres" for all projects)
Username: User (It's "postgres.projectid"
Password: The project password I asked you to remember
SSL: Don't require ssl
Click on Test. If your data was correctly typed you should be able to click on Authorize Access. If you don't, check that your password and host:port data is correct.
Awesome! You are good to go.
Using the Postgres database inside AppSheet
If you are creating a new app or adding your database to a current app you should follow this steps to find your table:
- On the "Get data from..." popup select the data source that has the name you typed when you added it as a source. In my case "Supabase".
- The path to your tables is Schema -> public -> Tables
Done!
This guide was minimal because I'm asuming you already have more knowledge than me about SQL.
If you are not a SQL-versed person, take this as an oportunity to learn thanks to the fact that now you have a free SQL database connected to AppSheet.
Another thing that may be good if you are thinking about moving to SQL is that you can test performance and others, again, for free.
If you like another alternative because for some reason Postgres is not your thing, check this awesome post by @takuya_miyai
Yesterday, I encountered a connection error but successfully resolved the issue. Check your AppSheet connection and compare it with Supabase's connection setting. Follow these steps to address it:
- Navigate to Settings => Database
- Make a note of the settings and update your AppSheet connection accordingly.
Problem fixed!
โ
I've done the whole tutorial but when I add the table to the app I get this error. The connection to supabase was made ok but I can't add the table.
