Using data from MySQL

AppSheet can build apps from MySQL databases that are hosted in Oracle MySQL Cloud Service, Google Cloud SQL, or another cloud-hosting provider that supports MySQL.

To do so, add a MySQL data source using the Account>Data Sources pane.

In order to access MySQL data, the MySQL instance must be hosted in the cloud (in Oracle MySQL Cloud Service, Google Cloud SQL, Amazon RDS, or another cloud-hosting provider). 

You must also make sure that your MySQL instance accepts traffic from the IP address 104.40.67.206.  That is the IP that AppSheet will be connecting from, so your firewall must allow connections from that address.

Once a MySQL data source has been added to your account, you can add MySQL tables or views to any app. When you choose "Add Table" in your app, you can select the specific data source, and a table or view from that data source.

Once added to the app, AppSheet treats all data sources similarly. In fact, it is common and natural to combine data from a MySQL data source with data from other sources in the same app. 

MySQL Version:

When working with a MySQL data source, if you encounter an "Unknown column" error, this is most likely a version-related problem. For your MySQL instance to work with AppSheet, it is recommended that you use at least MySQL version 5.7.6. Also, because MySQL is open source, there are many other variations (also known as "forks") of MySQL, such as MariaDB, or Percona. If you use a variation of MySQL, AppSheet cannot guarantee that your database will work. For instance, at the moment, MariaDB is known to be incompatible with AppSheet.

Using MySQL on Amazon RDS:

If your MySQL instance is hosted on Amazon RDS, you may need to set the "Publicly Accessible" setting to "Yes". To ensure that the server accepts traffic from AppSheet, go to security groups settings in Amazon RDS --> Enter the EC2 Management Console --> Edit inbound rules --> select "all traffic", then insert 104.40.67.206/32, and hit save.

RDSSecurity.png

RDSSecurity.2.png

Working with identity columns:

It is common for a database table to use an identity column as a key. The values of the identity column are auto-incrementing numbers that get automatically inserted by the database. When you use such a table with AppSheet, there can be a problem. By default, SQL does not let an application define the identity column value. However, with AppSheet, new rows are created in the app when it is potentially offline and may only be synced later. So the app needs to be able to assign key/identity values. This problem is not new and every app framework that enables offline access data entry has to deal with it.
 
The best solution is to avoid IDENTITY column keys altogether in your database schema. Instead, use a column that is an nvarchar(8) (or in general an nvarchar of length greater than or equal to 8). In AppSheet's column definition for this key, give it an InitialValue of UNIQUEID(). Now unique key values can be assigned by the app and inserted into the backend database.
 
If the database must use IDENTITY columns, it is preferable to create them with a large initial seed. For example, set AUTO_INCREMENT = 100000. In AppSheet's column definition for this key, give it an InitialValue of RANDBETWEEN(10000, 99999). Now any records created from your app will have five digit values that lie randomly in the range 10000 to 99999, while records created directly against the database will have higher values. 
 
If the database schema cannot be changed and if there is already an IDENTITY column being used with the default initial seed (of 1), we recommend that you follow the same approach as described earlier. However, you can first manually increment the identity seed as follows:
 
ALTER TABLE tablename AUTO_INCREMENT = 100000;

This should "re-seed" the identity column to the desired range. Your AppSheet app will insert values in the range specified by the RANDBETWEEN() function in the InitialValue of your AppSheet column definition. 

Saving files created in the app:

Database servers differ from the rest of our cloud-storage providers in that they do not have a file system. This leads to a change in AppSheet behavior when saving files (like images and documents). If the app has to capture photographs, they are normally stored in a folder next to the source of data in cloud storage. In the case of a MySQL table though, image and document files are stored in the main cloud file system associated with the app creator's primary AppSheet account (typically Google Drive/Dropbox/Office 365/Box)

Have more questions? Submit a request

0 Comments

Article is closed for comments.