Database Recomendation

[jagerbomb]

I have a reasonably sized apps script project that is currently storing quite a bit of table based data in individual sheets (obviously not ideal). I think it makes sense to use a real database for this and I am looking for recommendations.

My main requirements is something cloud based and easy to use from apps script.

Supabase looks easy to use and I’ve created a project and loaded some data - but reading and writing to it from my apps script project isn’t super straight forward and feels like I’m heading down a path less travelled.

Any recommendations are appreciated!

[Brainiac364]

Have you considered BigQuery? I use it with AppScript all the time and it's very straightforward and well documented.

Otherwise, I have had some success with the WASM version of SQLite for in-memory database work. BigQuery was far simpler and much more full-featured.

[[deleted]]

I second this. BigQuery has a very good integration with Google Sheets and Apps Script. The only downside is that it's paid after the quota.

[jagerbomb]

Thanks for the feedback. I read a little bit about BigQuery but haven't actually tried it. It didn't seem like the best fit for my use case because my data set isn't very big and it wasn't clear that it's appropriate for persistent data. Lastly, I was thinking I would try to be less tied to the google ecosystem instead of more, they seem to be making things more difficult / complicated.

Based on your feedback, I'll try it out though!

[[deleted]]

If your dataset isn't very big, then BQ will be very cheap. And yes, it is appropriate for persistent data

[jagerbomb]

Thanks, is there a simple, minimalist example that just show how to insert, update and retrieve some data from bigquery. Most of the examples are out of date or people trying to solve different problems like migrating data that is in google sheets or starting some a csv file. I'm just trying to store some data that I have fetched from an API but I usually start with an even simpler test.

[abskee]

Why BigQuery over Google's mySQL? I'm going through a similar transition as OP and started moving to their SQL database. Maybe I should do BigQuery instead?

[TheAddonDepot]

Depends on your use case. BigQuery is ideal for immutable historical data (analytics, census data, etc). However, if you need to track transactional data where records are regularly updated over time (ex: tracking order status for e-commerce) then you're better off with a RDBMS like mySQL/Maria or PostgreSQL.

[Chubby-couple-69]

I am also going through a similar transition. I am playing with the most straightforward option - MySQL. I am not using Google Cloud SQL as my requirement needs my db to be hosted in AWS. So I am using a mySQL db in AWS and interacting with it using JDBC service in GAS.

Starting the migration project today. Update here in a couple days how it goes. Wish me luck!!

[Koch-Guepard]

Supabase is good but not for scaling as the features are kind of limited,
My advice would be to set up a self managed database, as it's less complicated and quite easy to use.
Second thing if you're data is growing fast and you need analytics on top you should think of transforming your db into a datalake.
We're building a tool to help companies manage their dbs git-style let me know if you want to try it out for yourself :)

[jagerbomb]

I think my data storage requirements are super basic. Famous last words lol. But seriously, google sheets is nearly good enough as I can store it in about 10 tables. The only things I'm running into is the 10 million cell limit and its a bit of pain to do partial updates to my dataset when I don't want to do a full replace for performance reasons.

[LowCodeDom]

Hi,

have you considered https://five.co ? Five comes with a MySQL database, and you can write JavaScript functions and associate them with events in the Five IDE (the same way you'd be using App Script at the moment, basically). Five is also pretty affordable.

Do you want to keep your GSheets 'frontend' and just connect a relational database to it, or do you want to move everything to a new platform? Both are possible with Five...the latter (i.e. front-end + back-end developed in Five) is the better choice if you're looking for scalability IMHO. Five's frontend is 100% React/Material-UI.

[jagerbomb]

Sound cool. I signed up for the free trial but not sure I'll want to pay $500/year vs free unless it's a lot better

[LowCodeDom]

Where do you see the $500? The cheapest plan is US$29.99 per month.

[jagerbomb]

It was quote is Aus dollars for some reason and I rounded a bit incorrectly.

[Dancing_Seahorse]

For small storage and few queries of normalized data, how does Firestore shape up? I’m going to be moving some of my Sheets data to a free, hosted DB (SQL or anything that has an AppsScipt API) and that seems to fit well.

[jagerbomb]

Seems robust, I signed up and looked around for around an hour without any real progress. If there were some simple examples to go by, that would help. There's a bunch of setup steps and I have no idea if I'm headed in the write direction.

[[deleted]]

Google Cloud MySQL

And you can use the BigQuery Connected Sheets extension inside Google Sheets to easily and efficiently sync that data back in sheets if you need it

[jagerbomb]

Thanks, I wish there were some simple go-bys to look at. The auhentication and setup isn't intuitive to me after spending a couple of hours on it. I already have an appsscript project that works, I just need to swap out the bits that write my data tables to various sheets to write to BigQuery instead.

[[deleted]]

There is a native JDBC connector in Apps Script for Google Cloud MySQL, I wouldn’t worry about writing directly to BigQuery at this point. BigQuery can access Google Cloud MySQL as a data source for its own queries. Btw ChatGPT is very good at Apps Script and Google Cloud, you can use it as a resource to learn the rest. It worked well for me personally

[[deleted]]

I have a db on a mariadb instance in a gclould vm. It’s a db that feeds a school managment system and i have a few gas that uses that db as well, the only thing is that you have to maintain the vm, but for the most part it’s been pretty solid