Question: Migrating AppSheet from Google Sheets to SQL due to data growth

[sricse]

Hi community,

I’m looking for some advice and experiences from anyone who has faced a similar situation:

I currently have an AppSheet app that started with a small number of records using Google Sheets as the data source. From the beginning, I implemented partitions to maintain performance — initially every 6 months, then every 3 months, and now I’m having to partition monthly as I’m exceeding 60,000 records per month. I’m concerned that as the data continues to grow, the frequency of partitioning will become unsustainable.

Because of this, I’m considering migrating to an SQL database. However, I have several questions and would really appreciate your insights:

• Do SQL databases have record limits that affect performance, like Google Sheets does?
• If, for example, I reach over 2 million records in a couple of years, would I still need to create partitions? Would the app performance remain stable?
• How can I manage image storage so that user-uploaded images are saved in a Google Drive account different from the app creator's Drive?
• Which Cloud SQL service would you recommend to start with for this migration and testing? I'm looking for something accessible but stable enough to handle this data volume.

Any recommendations or shared experiences would be greatly appreciated.

Thank you all in advance for your time and support!

[Marc_Dillon]

SQL databases can handle millions of records.

You'll want to focus on defining proper Security Filters which are translated as database queries so the database itself does the filtering. Not all expressions can be translated to database queries. If you write a bad expression, it will still load all records to the appsheet server first before doing the filtering.

---

@sricse wrote:

How can I manage image storage so that user-uploaded images are saved in a Google Drive account different from the app creator's Drive?

---

I'm not sure if you can. Is this something that you're doing currently and are wondering how it will work when switching to SQL, or else how is this related to this post? When using SQL-backed apps, generally your files are still saved to the GDrive, determined by the default app folder setting. I suppose if your users share shortcuts from their folders to this folder, you could divide the files amongst them.

I like using Google Cloud SQL. It certainly isn't the cheapest, but it's managed and dependable. I've used MySQL, Postgres, and SQL Server. MySQL and SQL Server have easy connections to App Scripts through the JDBC class. Postgres and SQL Server allow adding and editing records in Views from an Appsheet app, while MySQL doesn't (due only to Appsheet's choice to add a LIMIT condition to all CRUD statements). MySQL is easier in regards to permissions than the other two. Postgres is the most modern.

[MultiTech]

---

---

Now THAT is a quality breakdown, well put good sir! 🫡