AppSheet can build apps from PostgreSQL databases that are hosted in Google Cloud SQL, Amazon Web Services, or other cloud-hosting provider that supports PostgreSQL .
To build apps from PostgreSQL databases, add a PostgreSQL data source using the Account > Data Sources tab. When prompted to select a new data source, choose Database.
In order for AppSheet to access PostgreSQL data, the PostgreSQL instance must be hosted in the cloud (in Google Cloud SQL, Amazon RDS, or other cloud-hosting provider).
In order for AppSheet servers to access your data, both your network and PostgreSQL must accept inbound connections to the database from AppSheet servers. Please refer to Managing IP Address and Firewall Information for further information.
Once a PostgreSQL data source has been added to your account, you can add PostgreSQL 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's common and natural to combine data from a PostgreSQL data source with data from other sources in the same app.
Cloning Apps to a PostgreSQL Database
When you clone an existing app to a PostgreSQL database, AppSheet will create tables for the cloned app in the default schema in PostgreSQL , which is the "public" schema. Cloning apps into user-created schemas in PostgreSQL is not supported.
AppSheet can also connect to PostgreSQL instances using Secure Socket Layer (SSL) connection. When creating or editing your PostgreSQL data source, if you mark SSL as required, the connection will only be established if the database supports SSL and has a valid server certificate.
We highly recommend that the PostgreSQL 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 PostgreSQL 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.
Using PostgreSQL on Amazon RDS
If your PostgreSQL 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 Managing IP Address and Firewall Information.
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 (such as 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 PostgreSQL table, 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.
AppSheet DateTime is not time zone aware and time zones will not be captured
Text is used if the column is 100 characters or fewer
Other types are currently unsupported though AppSheet will attempt to map them to Text.
Columns of Array type (e.x. integer or text) are currently not supported and will be skipped.