[Updated] Supabase mets AppSheet

[SkrOYC]

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:

1. You have to create an Organization inside Supabase, each one can have a different plan/pricing.
2. 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.

1. Click on your project.
2. Go to Settings, which is the last option of the left side menu.
3. Select "Database" and scroll down untill you find "Connection Info"
4. 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: Postgress
Server: Host:Port
Update: 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 use
Server: Host:6543
Database: postgres
Username: 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:

1. 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".
2. 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 

[SkrOYC]

---

@Igor_Reis wrote:

I'm pretty sure that AppSheet has a way to handle connections, similar to how the pooler does so

---

Sadly not, that's the main problem

[Gustavo_Eduardo]

Two things to update:

1. Connection string has moved

You can find Project connect details by clicking 'Connect' in the top bar "Connect"

2. On server it is necessary to write "aws-0-sa-east-1.pooler.supabase.com" and not Host: 6543

[sunil_prajapati]

hello if anyone getting problem white fatching table please use port 5432insted of 6543.

[racingmat]

Until a few days ago, I was connecting directly to some databases via port 6543 (with the IPv4 addon enabled).

For example:
db.hsdfefxxqyjgtnbdsfeferebqncgj.supabase.co:6543

Everything was working perfectly. However, suddenly, I can now only connect directly using port 5432, but I often get this error:

"Unable to fetch app definition. Error: Data table 'orders' is not accessible due to:
53300: remaining connection slots are reserved for non-replication superuser connections."

I then tried connecting through:
aws-0-eu-central-1.pooler.supabase.com:6543

When I test the connection, it works, but then it stops working.

If I try without specifying the port, like this:
aws-0-eu-central-1.pooler.supabase.com

Sometimes it works, sometimes it doesn't. I'm desperate. The weirdest part is that I have other databases on Supabase that are still working with the old method, but I'm afraid they'll stop working soon.

Do you guys have any solutions?

[Obson]

Olá a principio deu certo, obrigado pela explicação!

[racingmat]

Hello everyone,

I just wanted to share a solution I found to make Supabase work with AppSheet, hoping it might be helpful to others facing similar issues.

After struggling with the native AppSheet connection to Supabase, I successfully solved the problem by setting up a server with PgBouncer. The process I followed is:

Created a server on AWS and installed PgBouncer.
Configured pgbouncer.ini with entries for multiple Supabase databases using userlist.txt for authentication.
Set up auth_type = md5 and listed all the users with their MD5-hashed passwords in userlist.txt.
Ensured all users had the correct permissions on Supabase (SELECT, INSERT, UPDATE, DELETE, etc.) with proper RLS policies if needed.
Connected AppSheet to PgBouncer via the public IP of the server, specifying the listen_port = 6432.
By using PgBouncer as a connection pooler, AppSheet can now successfully communicate with Supabase databases without connection issues.

I hope this helps anyone trying to connect AppSheet to Supabase! Feel free to reach out if you need help setting this up. 😊

[SkrOYC]

Hi everyone! I think I should give you and update on the status of the feasibility of using AppSheet and Supabase together.
I will give more details in the main post once I can fully test everything.

There are basically two options

1. Dedicated Poolers
Supabase announced a new option where we can setup a dedicated pooler (PgBouncer!) specifically for our database. This looks like the best approach moving forward and I strongly suggest you to give it a try now that it is GA. This needs testing from my side.

2. Use an Apigee data source - AppSheet Help
The Apigee connector doesn't require you to setup specifically an Apigee proxy, it can be any API proxy that is built with the Apigee connector requirements in mind. I already did this and works great but I'm making sure it's security and performance is production-level before I post anything related to this.
I plan to publish it as an open source Edge Function that you can easily deploy on your own Supabase project without changing code.