AppSheet can build apps from MariaDB databases that are hosted in Google Cloud SQL, Amazon RDS, or other cloud-hosting provider that supports MariaDB.
Add a MariaDB data source
To add a MariaDB data source to your AppSheet account:
- Go to the My Account > Sources tab.
- Click + New Data Source.
- Enter a name for the data source and click Cloud Database or On-premises Database.
The Add database connection information dialog displays. - Configure the database connection information.
- Type: Select MariaDB.
- Server: Enter the MariaDB database server hostname or IP address.
- Database: Enter the database name.
- Username: Enter the username of the database user.
- Password:Enter the password for the database user.
- SSL: Select the SSL mode required for the connection. For more information, see Use SSL.
- Click Test to test the connection details.
- After a successful test, click Authorize Access to save the data source in your AppSheet account.
In order for AppSheet to access MariaDB data, the MariaDB instance must be hosted in the cloud (Google Cloud SQL, Amazon RDS, or other cloud-hosting provider).
In order for AppSheet servers to access your data, both your network and MariaDB must accept inbound connections to the database from AppSheet servers. See Manage IP address and firewall information for further information.
Once a MariaDB data source has been added to your account, you can add its tables or views to any app. When you click + New 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's common and natural to combine data from a MariaDB data source with data from other sources in the same app.
Use SSL
AppSheet can also connect to MariaDB instances using Secure Socket Layer (SSL) connection. Under the hood, there are 3 main modes of connection in MariaDB:
- No SSL: in this mode, SSL connection is not required, although some cloud providers will still attempt to establish SSL if possible.
- SSL required: in this mode, data to and from the MariaDB instance must be encrypted using SSL protocol. However, only the server certificate (owned by the MariaDB instance) is required to establish the connection. The client certificate (normally stored in the application client) is optional.
- X.509: in this mode, both the server and the client certificates are required in order to establish SSL connection. Since it's currently not possible to store client certificates in AppSheet's server, this mode is not supported.
To enable SSL connection using the second mode, select Require SSL when setting up the MariaDB data source. If your MariaDB instance is configured to use X.509 mode, you'll need to change the connection mode to SSL in order for the connection to work. For instances hosted on Google Cloud, X.509 mode can be turned off by going to the SSL tab and clicking on Allow unsecured connections. This will tell Google to relax the client certificate requirement.
When using SSL connection, it's highly recommended that the MariaDB instance uses a server certificate generated by a widely recognized Certificate Authority such as VeriSign or GeoTrust. This will ensure that the certificate meets all of the relevant encryption and formatting standards. Some cloud storage providers, such as Google Cloud and Amazon RDS, also generate server certificates for the MariaDB instances that they host. Currently, server certificates generated by TinyCA are not supported.
It's also good practice to sign the server certificate using SHA-2 hashing algorithms. This is because SHA-1 algorithms are no longer considered fully secure, and many cloud providers, including Microsoft, Amazon, and Google, are increasingly moving to SHA-2 and SHA-3.
Use MariaDB on Amazon RDS
If your MariaDB 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, choose Edit inbound rules, and create rules to accept all traffic from AppSheet's IP addresses, which can be found in Manage IP address and firewall information.
Work with identity columns
It's 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 doesn't let an application define the IDENTITY column value. However, with AppSheet, new rows are created in the app when it's potentially offline and may only be synced later. So the app needs to be able to assign key/IDENTITY values.
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's 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 initial value 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 initial value of your AppSheet column definition.
Save 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 MariaDB 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). The files will be saved in a subfolder of your account's default folder path (usually /appsheet/data). You can view and change the default folder path in your account page under the Settings tab.
Work with special characters
A MariaDB character set defines the characters that can be read and processed by a particular MariaDB database or table. The default character set of a MariaDB database should work with most Latin characters. However, many languages, such as Spanish or Chinese, have special characters which aren't included in the default character set. In order to work with these special characters, you'll need to configure your MariaDB database or table to use the appropriate character set.
For example, to configure an entire database to work with special characters in Spanish, such as ñ, you can use this command:
ALTER DATABASE db_name CHARACTER SET = 'utf8' COLLATE = 'utf8_spanish_ci';
Alternatively, to configure only a single table, you can use this command:
ALTER TABLE db_table CONVERT TO CHARACTER SET utf8 COLLATE utf8_spanish_ci;
More information about available character sets in MariaDB can be found here:
https://mariadb.com/kb/en/library/setting-character-sets-and-collations