[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]

A more detailed guide specific to AppSheet column types and Postgres data types can be found in the docs here:

Use data from PostgreSQL - AppSheet Help

[Marc_Dillon]

Nice.

I just tried it out, following your walkthrough, it worked well.

This service feels pretty good to me. Thanks for letting us know about it.

[Suvrutt_Gurjar]

Thank you @SkrOYC . The guide is detailed. I will also try it out.

[Joseph_Seddik]

Thanks a million @SkrOYC !!

[SkrOYC]

This is a more detailed page related to upgrade the actual database instance:

@Jonathon, related to your comments here about the specs, it seems like all projects are on the first option "(inclided)", then you can upgrade with this add-ons on top of the pricing plan you chose

[takuya_miyai]

Thank you @SkrOYC 

This sounds like a very interesting initiative.🤩
I would love to give it a try!

[makiprodan]

Works fine!

[SkrOYC]

Good to know!

[Ryan_Wagner]

@SkrOYC Hi, I have been wanting to try this. Any idea how (or if) it could handle 200,000 rows and ten columns of data?

[SkrOYC]

Are you familiar with SQL databases?

[Ryan_Wagner]

Not very. I am planning on working with our IT supervisor to integrate our new Enterprise Appsheet account with an SQL database. I just didn't know how Appsheet handled a huge volume of data. I know SQL on its own is made for that.

[SkrOYC]

In general, I'd say that 200k rows on your database shouldn't be a problem.
The main thing is how you handle the information inside AppSheet. On that regard, I don't think you will need 200k records to be available for the whole set of users. So if you write simple security filters, those are translated to the database when querying the data so you can have a good UX.

In which ways are you wanting to use those 200k records?

[Ryan_Wagner]

That is good news, thanks. I currently have 200,000+ assets for inspection broken up into seven different apps. It would make editing much simpler if I could re-combine the apps into one. 

[SkrOYC]

If those 7 apps work fine, you could then just make one app and define which set of the data you need based on a user setting

[Ryan_Wagner]

That makes sense. However, *IF* I wanted to try to load more rows at a time, at what point does it become unstable? Ideally, I'd like each user to be able to see everything. Or I guess I could define the security filter by area instead of by person...

[SkrOYC]

You should try to find it out. I haven't had the need to use that amount of rows at the same time.

The best approach will always be to use a security filter so that your users have just the data they need

[Ryan_Wagner]

Thanks. I'll try to remember to report back when I do...

[dbaum]

Thanks a lot for posting this!!! I finally got around to trying it out. You've provided a great explanation about a great resource.

Consider adding to the OP a link to your handy related post about Postgres column types vs AppSheet column types.

Also, it's worth noting that while Supabase column settings do allow assignment of a data type, including varchar, but not (unless I've missed something) a maximum length [e.g., varchar(8)]. When using Supabase with AppSheet, that only simplifies things further since column data further constraints can be controlled with the app definition and don't need to be kept in sync with the database settings.

[SkrOYC]

---

@dbaum wrote:

that only simplifies things further since column data further constraints can be controlled with the app definition and don't need to be kept in sync with the database settings.

---

Yep, I should post a list similar to the one about Postres but just mentioning the column types available on the UI/No-code version of Supabase config.

I'll do it maybe this week

[SkrOYC]

Hi @dbaum, please check my post about the way the Supabase UI options relate to Postgres column types.

Re: Postgres column types vs AppSheet column types - Google Cloud Community

[Office_Geek]

Thanks so much

Amazing. Concise write up

[Hermann]

Hello SkrOYC, did you find out how to get a secure connection (ssl-connection) from AppSheet to supabase? That would be very helpful if there is a way to get this done. Thanks for reply.

[SkrOYC]

Nope, and it's a thing for all Postgres databases. I recall someone had the same problems

[SkrOYC]

FYI:

Re: Trouble Connecting To Postgres (Google Cloud S... - Google Cloud Community

[SkrOYC]

Hi everyone. I made some updates to this post.

The most important one is about the port used to connect to the database. I urge everyone that has connected Supabase with AppSheet following my post to change it asap and see the benefits.

Some links to get context about it
Connecting to your database | Supabase Docs
Connection Pooling with Appsheet? - Google Cloud Community
Solved: PostgreSQL error - Google Cloud Community
Unable to fetch app definition - Google Cloud Community

This could also be another benefit of using Supabase btw, since just by changing the port you will be enjoying their implementation of pgBouncer (more on the first link)

[dbaum]

Thanks a lot for maintaining this really valuable tip!

Can you clarify the update related to utilizing connection pooling? I think you're saying:

• In Supabase, confirm that "Transaction" is selected for the project's database Pool mode setting. Otherwise, no changes are needed.
• In AppSheet, from the account's Sources select the Supabase data source and use "Modify an existing data source" to revise the Server property by replacing a "Host:Port" string with a different string copied from the Supabase project's database settings. If this is right, I don't understand which precise string to copy.

I don't follow how to interpret in concert the various highlighted portions of the following. They seem contradictory or like you meant to include specifics regarding connection pool settings but inadvertently omitted those details.

---

@SkrOYC wrote:

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
Password: The project password I asked you to remember
SSL: Don't require ssl

I also don't follow what you entered in AppSheet for the data source's Server property (seemingly instead of "Host:6543") in the following screenshot:

---

@SkrOYC wrote:

---

Is that string for the Server property excerpted from the Supabase project's database "Connection string"?

[SkrOYC]

Hi @dbaum, glad to hear about you.

It's actually much simplier

1. Change the config with the new port, same URL as used before. The URL is in the same spot as it were, just use the new port.
2. The text that I left with strikethrough is the one that was previously there and has to be ignore now

[dbaum]

Ah, got it. I see now that's what you originally explained. I think I was confused because you struck "Host" one time but not the other. Thanks again.

FWIW, here's another set of screenshots from how I ended up walking myself through @SkrOYC's steps.

For the Server property in the AppSheet data source's database connection information:

Enter the value of the Host property in the "Connection info" section of the Supabase project's Database Settings:

Followed by a colon followed by the Port number in the "Connection Pooling"  section of the Supabase project's Database Settings:

Pattern:

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

[SkrOYC]

Yep, that's what I tried to explain by "Host:6543" inside Server

[kstirto]

 Thank you for sharing @SkrOYC. 🙏

[T_I_Phong]

Thank you @SkrOYC ,

Can you help me with this issue? I've been using Supabase recently and I've frequently encountered the error:

'Failed to load schemas. Error: failed to get pg.schemas: remaining connection slots are reserved for non-replication superuser connections.'

I'm not sure how to resolve it.

[SkrOYC]

Where you see this error, inside AppSheet or Supabase? In the editor or in an app?
Are you using connection pooling?
Did you copy the data from another source or you created the structure from within supabase directly?

[T_I_Phong]

Hi @SkrOYC , Thank you for response,

---

@SkrOYC wrote:

Where you see this error, inside AppSheet or Supabase? In the editor or in an app?

---

Sometimes, I encounter two connection issues, one in AppSheet and another in Supabase.

---

@SkrOYC wrote:

Did you copy the data from another source or you created the structure from within supabase directly?

---

I created a table in Supabase and connected it to AppSheet.

---

@SkrOYC wrote:

Are you using connection pooling?

---

I dont know this???

It seems that the connection is not stable, possibly because I'm still learning and haven't configured everything optimally, or I haven't delved deep enough into Supabase. The issue might also be related to my experimentation with the free version. There are various suspicions that come to mind.

Besides these issues, I've noticed that the performance of Supabase is excellent.

[SkrOYC]

Try to remove calculations from Supabase and use it as just a plain database.
AppSheet was made with the ability to handle all logic and data creation, and sometimes when a managed dabatase plays a bigger role in the behavior of the data, AppSheet can't handle it.

I think starting there is a good idea

[kstirto]

@SkrOYC – 

I recently delved into Supabase (thanks to your post) and am currently in the process of migrating my personal app to the platform. Everything has been smooth sailing so far, but I've encountered a hurdle when attempting to load a Materialized View into AppSheet. While I can successfully load a Standard View, it appears that AppSheet does not currently support Materialized Views.

The reason I am keen on using Materialized Views is to implement a trigger for automatic refresh, a functionality not feasible with Standard Views. I'm curious if you've had any experience with loading Materialized Views into AppSheet and would greatly appreciate any insights or advice you could share. For further details on my specific issue, you can refer to the post linked here:
Challenges with Appsheet Recognition of Supabase Materialized Views during Migration

[SkrOYC]

Well, materialized views, as a Postgres feature, may not be supported by the standard connector used by AppSheet

[kstirto]

Thank you @SkrOYC for confirming. 🙏

[khiemtg]

thanks for sharing @SkrOYC , do we need to upgrade to enterprise subscription for this connection type?

[SkrOYC]

If you want to use it in a production app, yes, they require you to have an enterprise license for it.

You can test prototype apps or use personal apps with no license