Appsheet and SQL - Virtual Column vs Virtual Column

[mykrobinson]

I'm getting my feet wet with SQL and exploring the differences between using MySQL and Google Sheets. One huge benefit that i really like so far is that i can add columns in the SQL database and AppSheet doesn't care. It won't have access to the new columns until regenerated in AppSheet, but it won't break either.

My question is, it looks like virtual columns are a thing in SQL as well (forgive me, i am very much a sql rookie). So let's take a very simple example like concatenating a last name and first name
Is that best done with a virtual column in appsheet, or should i do it in sql? 

I suppose it's easy enough for me to test, but at the moment, my experiment is with very simple databases, and i'd like to better understand best practices at scale.

Thanks

[Markus_Malessa]

---

@mykrobinson wrote:

add columns in the SQL database and AppSheet doesn't care

---

while just working on the design of the app that is correct, however if you were to add a row to the datasource you would get an error because of mismatched columns between AppSheet and MySQL database.

When talking about virtual columns in SQL are you referring to using the default value of a column using DEFAULT CONCAT(Column1, Column2) or are you referring to a stored procedure? If only using DEFAULT CONCAT() then the value for the column is only set when the row is created. If using only a procedure, then you would need the procedure for adds and updates within SQL. If setting this up in SQL you might want to consider both default and a procedure for updates only.

Similarly if you set this up in AppSheet you will want to consider doing the Concat in formula or initial value. Also, if doing this in AppSheet you will want to consider if there are any other programs or scripting routines that may not observe the same behavior that interact with your DB. In that case doing the default and update procedure in SQL might be better or you would need to find a way to incorporate the same behavior in your other programs or use the AppSheet API to add or update records so the same behavior is executed through AppSheet.

[mykrobinson]

interesting. I actually tested earlier today because another user had mentioned that Appsheet would not give the mismatch error with SQL, and i confirmed this with my own tests. Of course it would break if i dropped a column, but i was able to add columns in Sql and sync the app without regenerating and the app continued to function without error.

[Markus_Malessa]

It has been a while that I have dealt with this so I suspect you are correct that it is not an issue when adding columns but is when deleting columns.