[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 

[hoanguyen]

When I use AppSheet to connect to the Supabase database, I encounter an issue which is 'max client connections reached.'

The reason, as far as I understand, is due to the mechanism of AppSheet when performing CRUD operations asynchronously. Each time it does an async operation, AppSheet seems to async all tables on Supabase, leading to max connections being reached.

I've tried changing ports like 5432 and even 6543, but I still encounter similar errors. Has anyone on this forum encountered a similar situation and could share how to resolve it? 

[T_I_Phong]

Hi @SkrOYC ,

I came across this notice from Supabase:

"PgBouncer and IPv4 will be deprecated after 26th January 2024."

My internet connection is using IPv4. I'm not sure if this will have any impact or result in any changes when connecting Appsheet to Supabase. Can you please advise me on what steps I need to take to ensure a stable connection?

Thank you,

[jehoon]

Is anyone experiencing issues connecting AppSheet with Supabase due to port 6543 connection issues related to the recent Supabase IPv6 changes?

[kstirto]

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:

1. Navigate to Settings => Database
2. Make a note of the settings and update your AppSheet connection accordingly.

Problem fixed!

 

[T_I_Phong]

Hi @kstirto , could you assist me with the new pattern?

The previous pattern:

db.{project-specific string}.supabase.co:6543

 Thank you!

[jehoon]

Thank you. Currently, I have registered the hostname (address) as " aws-0-ap-northeast-2.pooler.supabase.com " and have registered a separate username (project name). I registered :6543 after the hostname, but while connections on 5432 and 6543 are successful, 6543 does not actually connect on the AppSheet interface. Thank you.

[T_I_Phong]

Thank you, I successfully connected  using port 5432. but the connection was unsuccessful with port 6543.

Server: xxxxxx.pooler.supabase.com:5432

Database: postgres

Username: postgres.xxxxxxxxxxxxxxx

password: xxxxxxx

[jehoon]

 It seems there are issues with port 6543. I would appreciate it if you could share any solutions that arise. I have inquired about the related issue with the AppSheet support department, and they said they would respond after investigating.

Seeing that Google's Looker Studio is operating normally, it seems there might also be an issue with AppSheet's communication on port 6543.

If you're experiencing frequent disconnections while using port 5432, even when there aren't many actual users, are there any solutions? There seem to be many disconnection issues regardless of user load.

[kstirto]

Scroll through the following discussion. Hope you'll find the solution.
https://github.com/orgs/supabase/discussions/17817

[SkrOYC]

I just updated the post mentioning the current implementation with Supavisor and IPv6.

Main changes are:
A new server url which is found in Supabase database settings page.
A new way to authenticate the user name, which includes the projectid in it, previously it has the project id in the server url

[racingmat]

I've been working on the new configuration for over a week but I can't find a solution. One of the projects I managed to activate IPv4 for a fee and I use the old link and everything works fine. With the new link and the new username I can only use port 5432 but it is not ok for appsheet saturates users and is not stable. Were you able to use the 6543? I tried every way

[jehoon]

I also ended up switching to the pro version and paying for the IPv4 add-on, after which everything worked normally. It seems that this method is currently the best solution.

[racingmat]

Yes the problem is in same of old my project is not possible make the add-on for now. I need wait but the problem is that the application is my company's management system and now everything is stopped.

[SkrOYC]

---

@racingmat wrote:

Were you able to use the 6543? I tried every way

---

If you already saturated it because of :5432, you need to wait for it to close the connections and then change it to :6543 or you won't be able to pass the Testing when changing the port. In other words, stop using the app with :5432 until Supabase closes those connections, then you should be able to connect using :6543, like an hour or so after your latest connection. That's per my testing

[Sirfyaad]

Hi @SkrOYC 

Is it free and safe here? Whenever I create a table, the table types are shown as public. I am new in the field, can you tell me about it? I want to make a private table. 

[SkrOYC]

Public is in the context of other tables created in the Postgres database for other purposes, like auth. But, in any case, it's completely safe, just a naming thing. It's a private database hosted in AWS under Supabase's control

[gustavoinovaa]

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.

[SkrOYC]

Have you tried with another app or by connecting to another database in Supabase?

[Joerrey]

Any updates on this? I got the same error. Using Port 5432 is working as expected. Using Port 6543, I can successfully add a supabase db as external source but adding a table to a project is not working. 

I also noticed a strange behaviour when changing a source port from 5432 to 6543: Adding some items within the appsheet interface successfully created one item in the db. But no more than one and the sync did not finish or continue.

[SkrOYC]

Could you share an screenshot of the error?

[Joerrey]

It's exactly the same error as in gustavoinovaas post.

[SkrOYC]

I did some testing and I'm reporting my findinds directly to Supabase.

Seems like the pooler is having some issues

[Joerrey]

Nice, thanks! I really like the combination of as and supabase but the max connections errors with port 5432 are driving me crazy.

[nguyenhoa8822]

Sure, I understand your frustration. You're experiencing the "max connections" error with port 5432. Do you have any strategies for handling this issue that you could share?

[nguyenhoa8822]

Have you received a response from supabase yet?

[Gustavo_Eduardo]

Very good information Oscar. I started developing in flutterflow to get started in low-code and I use supabase. It seems like a good option to integrate with Appsheet because while I work from Appsheet I can also work on the new Flutterflow project and I already have the database moderately full.

[SkrOYC]

---

@nguyenhoa8822 wrote:

Have you received a response from supabase yet?

---

Kinda, but I'm not having enough time to keep troubleshooting.

If anyone can help with this, please post your findings in this place:

AppSheet + Supabase: Since new Pooler, the connection is basically impossible to work with · Issue #...

Try being technical. If you lack some technical knowledge, better to way other's comments, rather than confuse Supabase people with AppSheet-related stuff

[bradlegassick]

I have been trying to develop a new app with Supabase on a Pro Medium compute plan & constantly get the Max connections error. I am coming to the conclusion that Supabase & AppSheet do not play well together. Have you been able to resolve your issues?

[SkrOYC]

They are working on getting the actual issue that made the Supavisor reject the connection.

The issue with the Max connections is because AppSheet doesn't have a way to apply pooling by it's own, or reusing the same connection for a group of changes. Instead, AppSheet opens one connection per CRUD

[bradlegassick]

Hi @SkrOYC seems this is not really going to be resolved in the foreseeable future. I have tried to setup a Google MySQL server to test with my app, however the connection is constantly refused regardless of adding all the AppSheet IPs to the whitelist in the console. I think on my AppSheet plan I can only connect 1 SQL server & it is unfortunately set to Supabase. Removing it before configuring the Google database will prevent any migration & require complete set up from scratch! The joys of AppSheet!

[T_I_Phong]

Is there anyone among us who has a solution to this problem? Could it be an issue between the free and paid versions? If paying could solve this issue, could someone using Appsheet + Supabase (Pro) provide more information?

[bradlegassick]

I have a paid version of Supabase & upgraded my compute & service for testing. The errors persisted. For me now Supabase is not an option any longer because of this.

[SkrOYC]

This is the current status

[SkrOYC]

---

@bradlegassick wrote:

I think on my AppSheet plan I can only connect 1 SQL server

---

There are no limits to the amount of connections you can have to databases

[Igor_Reis]

Hello!

I'm having trouble using supabase with this current issue. Can you guys recommend me any alternatives to supabase?

[bradlegassick]

Try Google service mySQL

[SkrOYC]

Look for @takuya_miyai's post

Also, try changing from port 5432 (to connect to new tables and regenerate schemas) to 6543 (when it's already working) in the supabase integration settings

[bradlegassick]

Do you have a working Supabase integration without the errors?

[Igor_Reis]

Cool. I did not see @takuya_miyai post. I would have used it to research new technologies.

Aside from that, changing the port to 5432 didn't change anything but we have found another solution.

Fo this solution it is required to pay for the IPV4 addon. Then we switch to direct connection, instead of using the pooler, and change the amount of max connections using Supabase CLI.

Here is the code that has to be inserted in the CLI to change the amount of connections:

npx supabase login

npx supabase --experimental \
--project-ref <project_ref>\
postgres-config update \
--config max_connections=<number of connections>

I think you can define any number you want for max_connections, but they warned me to be careful because way too many connections will risk an out-of-memory error.

They gave me an equation to calculate how many connections is the recommended limit: (SERVER MEMORY - 512MB - (3 * 128MB)) / 5MB

Our compute size has 2 GB of server memory, so we are able to have 230 simultaneous connections, although we are not even hitting 70% of that value.

I think this is good for us, AppSheet users, because I'm pretty sure that AppSheet has a way to handle connections, similar to how the pooler does so.

There is a chance that we had two poolers working together when we only needed one, but I'm not so sure about my last sentence and, if someone is more knowledgeable about it, I would love to know more about this. 

[Gustavo_Eduardo]

Hello Oscar, how are you? Did you finally get a chance to check if AppSheet and Supabase are compatible with row-level security? I saw above that you were going to look into it.