Pros and Cons of using Appsheet Databases

[MarcosL]

Good morning community.
I am writing to share my confusion on the subject of using appsheet databases; Why would anyone use these databases, which have meager limits and paying onerous additional costs, being able to use Google Sheets as a database for free, which, apart from being free, does not set us the meager limits that the appsheet database?
In case of opting for the appsheet database, what are the strategies to have apps with a lot of data under such a restrictive number of records scheme? Should we gradually incorporate automated processes into the architecture of these apps with appsheet databases to pass the data elsewhere? If yes to the above, where is the data to be freed passed to to stay within the appsheet database boundaries?
Thank you very much for answering, any information that can guide me on these issues will be welcome.
Regards, Mark.

[WillowMobileSys · Accepted solution]

---

@MarcosL wrote:

I am writing to share my confusion on the subject of using appsheet databases; Why would anyone use these databases, which have meager limits and paying onerous additional costs, being able to use Google Sheets as a database for free,

---

First,  AppSheet proprietary Databases are FREE but in their infancy, have some issues and are not ready for large apps due to limits.  They are well suited for only small-ish apps at the moment, in my opinion.  So let's set them aside.

For small, and even medium sized (tens of thousands of rows - all tables) apps, without a doubt I strongly encourage Google sheets to be used. 

So, Let's focus on SQL Databases versus Google Sheets with regards to large apps.

When apps become large, e.g. hundreds of thousands of rows OR tens of thousands in several tables,  performance starts to become a concern.  All apps, regardless of datasource, will degrade in performance as they grow in data size.   I do believe with certain care taken with the structure of tables, usage of Virtual Columns,  and archival of old data,  Google sheets can serve even most large apps very well for an indefinite period of time. 

However, there is effort=cost in maintaining those Google sheets.  Virtual Columns need to be scrutinized carefully.  Archival processes need implemented and managed to reduce the size at the source (for faster loading).  Processes need to be in place when older rows need to used or reviewed.  Backup processes need to be custom built.

When using an SQL database, data reads are already optimized for fast retrieval.  But AppSheet can apply Security Filters to data read process from a database to limit the rows loaded - making reads, and subsequent server-side processing, even faster.  No archival is necessarily needed though archiving- even in a database, will make the data reads that much faster still.  While AppSheet apps using a database can read data faster,  the same effort is needed to be sure Table structures are suited for performance and VC's are used only when needed and appropriate, otherwise they kill performance.  Most databases have their own backup features.

The Differences??

It boils down to cost versus effort.

SQL Databases will cost extra but can "naturally" speed up read times.  AppSheet Security Filters can serve as a kind of archival eliminating the need for manually built archival processes.  Most db's have backup facilities eliminating the need to build those.

Google Sheets take extra care and effort to ensure best possible performance with the need for manually built processes to archive data to achieve faster read times and ensure data security with backups.  These processes will need to be maintained and updated through the life of the app.

I hope this helps!

[WillowMobileSys · Accepted solution]

---

@MarcosL wrote:

I am writing to share my confusion on the subject of using appsheet databases; Why would anyone use these databases, which have meager limits and paying onerous additional costs, being able to use Google Sheets as a database for free,

---

First,  AppSheet proprietary Databases are FREE but in their infancy, have some issues and are not ready for large apps due to limits.  They are well suited for only small-ish apps at the moment, in my opinion.  So let's set them aside.

For small, and even medium sized (tens of thousands of rows - all tables) apps, without a doubt I strongly encourage Google sheets to be used. 

So, Let's focus on SQL Databases versus Google Sheets with regards to large apps.

When apps become large, e.g. hundreds of thousands of rows OR tens of thousands in several tables,  performance starts to become a concern.  All apps, regardless of datasource, will degrade in performance as they grow in data size.   I do believe with certain care taken with the structure of tables, usage of Virtual Columns,  and archival of old data,  Google sheets can serve even most large apps very well for an indefinite period of time. 

However, there is effort=cost in maintaining those Google sheets.  Virtual Columns need to be scrutinized carefully.  Archival processes need implemented and managed to reduce the size at the source (for faster loading).  Processes need to be in place when older rows need to used or reviewed.  Backup processes need to be custom built.

When using an SQL database, data reads are already optimized for fast retrieval.  But AppSheet can apply Security Filters to data read process from a database to limit the rows loaded - making reads, and subsequent server-side processing, even faster.  No archival is necessarily needed though archiving- even in a database, will make the data reads that much faster still.  While AppSheet apps using a database can read data faster,  the same effort is needed to be sure Table structures are suited for performance and VC's are used only when needed and appropriate, otherwise they kill performance.  Most databases have their own backup features.

The Differences??

It boils down to cost versus effort.

SQL Databases will cost extra but can "naturally" speed up read times.  AppSheet Security Filters can serve as a kind of archival eliminating the need for manually built archival processes.  Most db's have backup facilities eliminating the need to build those.

Google Sheets take extra care and effort to ensure best possible performance with the need for manually built processes to archive data to achieve faster read times and ensure data security with backups.  These processes will need to be maintained and updated through the life of the app.

I hope this helps!

[WillVerkaik]

The greatest reason to consider proprietary DBs is the automation around maintaining ACID transactions.  The maturity around DBs on this is fundamental.  I have no evidence that AppSheet manages this against spreadsheets.  I would expect collisions occur when 100s of users start using apps which share spreadsheets.  DBs inherently manage a lot of this when properly used.  

Transaction fulfillment is the hallmark of centralized DB systems. 

[RedVox · Accepted solution]

Here's a few other differences that I would note.  For using Appsheet db, my main concern is that there is currently no easy way to copy the data or keep a back up of the data.  This gives me the willies.  Particularly because there's also no way to easily export the AS DB to flat files or MYSQL files.   

Also, there are some oddities when using MySQL that might make Google Sheets a better choice for your data.  I haven't got definitive knowledge of this issue, but I've ran into situations where a formula to select a list of suggested records for an enum field would normally be members[id], but for MySQL you need to use SPLIT(ORDERBY(members[id], [name]), ",") to get the same outcome.

Another thing to watch for is mixing and matching data sources.  Records from one database cannot be referenced in records of another database, it seems, or at least not when one is MySQL and the other is not.  I have some projects that use MYSQL and PHP for some apps and AppSheet for another, and I thought the AS DB might be a nice place to store action flags and other things that are specific to my AS app and pointless for my PHP apps.  No such luck.  The references need to share a database (or maybe a db type).  

Finally, I've found that AS doesn't handle MySQL databases with nulls as I would expect.  For example, if you have a date field where the default value is null, you're going to have trouble getting an AS date field to behave as expected.  And whenever you regenerate the table structure within AS, some of these problems will need re-fixing.  I've learned to avoid using null as a default value for my MySQL tables, but, of course, that causes different limitations when working in non-AS apps.  

Cheers!

[WillVerkaik]

A simple alternative to backing up all related sheets is the feature of BigQuery to be connected directly to the Sheet.  You then have the features of a data warehouse to manage backups, aggregation, cross queries and a host of enterprise wide solutions.   Sheets can then even recursively access BigQuery results and display back into the same AppSheet for analytics and once again cross queries against other systems of record. We for example use the content from Workday joined to AppSheet OLTP data to produce OLAP output back in each AppSheet. 

[MarcosL]

Thank you very much for your answers, the picture is much clearer for me, greetings.

[RedVox]

Ouch!  Just discovered the biggest killer of all for not using the ASDB:  When you try to export data, you discover that the rowid fields are not shown.  So you can have the data, but none of your tables will have any relationships anymore!

[RedVox]

Actually, it was after transferring ownership of an app that i ran into the problem of rowid's not being included.  Later, I made a new app, connected my appsheet DB to it, and then, from within the app, I was able to make copies to Sheets with rowids.    Seems like there's some bugs related to transferring databases/apps when using ASDB.

[WillowMobileSys]

You should report this to AppSheet support.

[WillVerkaik]

You are using RowIDs to maintain table relationships?  I am not confident that  should be used for any db schema design/architecture.  The unique identifier should not be based off of system rows in my experience. 

[Scott_Nesbitt]

Can you connect Looker Studio to Appsheet Databases?

Is there a plan to add an Apps Script interface to Appsheet Databases?

[WillVerkaik]

@Scott_Nesbitt i have been successful in connecting to AppSheet directly from Lookers native connector.  It uses AppSheet as a proxy to what ever data is managed by your app.  It makes it quite easy to use.  I have not tried where an AppSheet is using the app db but now I am curious. 

[WillowMobileSys]

---

@WillVerkaik wrote:

i have been successful in connecting to AppSheet directly from Lookers native connector.  It uses AppSheet as a proxy to what ever data is managed by your app.

---

I am curious...which flavor of Looker are you using - Looker, Looker Studio or Looker Studio Pro?   And how is your performance/response time?

I am in the middle of a big db migration project.  For reports, it was considered to use Looker Studio with direct connection to AppSheet.  It was found, by another person in this project, that this connection was extremely slow.  Instead, we are now using Looker Studio connected to Big Query with a data transformation tool in between.  This seems to provide a much more reasonable response time.

[WillVerkaik]

The looker Studio is the version we have with our Enterprise GWS.  It is not Pro.

Performace seems reasonable especially when pagination is used.  I have merged and joined data with both bigquery, sheets directly and through appsheet.  It is slow when connecting initially, but seems to cache the data.  I suspect frequent updates can be a problem if you look for real time data.  I have not used it for this as of yet through Appsheet connector as mostly batch reporting and publishing which is a 'next day' event.  

The option to go directly to the Sheets directly and perform Blends works very quickly.    

One interesting feature is the ease of connecting to Google Calendar and then subsequently use looker to report to that Calendar do to both being native connections simplifies the process.  

The solution of ingesting Bigquery all the data works and in my opinion is better because you perform all the views in Bigquery and control security access through Bigquery.

Let me know your findings as I am also very interested in the progress.