Use data from PostgreSQL

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:

1. Go to the My Account > Sources tab.
2. Click + New Data Source.
3. Enter a name for the data source and click Cloud Database or On-premises Database.
   The Add database connection information dialog displays.
4. Configure the database connection information. 
   • Type: Select Postgres.
   • Server: Enter the Postgres 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.
5. Click Test to test the connection details.
6. After a successful test, click Authorize Access to save the data source in your AppSheet account.

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. See Manage IP Address and Firewall Information for more information.

Once a PostgreSQL data source has been added to your account, you can add PostgreSQL tables or views to any app. When you add a 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 PostgreSQL data source with data from other sources in the same app.

Clone 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.

About using a UNIQUEID (UUID) key in Postgres

The following information is vital if you use UNIQUEID() as the key for a Postgres table.

Appsheet's UNIQUEID("UUID") formula generates an upper case UUID key value in the form 531B2ABA-C263-4433-A61E-AD14FCC1031C. Postgres accepts that UUID key value for the newly added record, but automatically converts the key value to lower case.

This automatic Postgres key value change can prevent client and workflow updates of the newly added record from working properly. Updates that occur shortly after the add will attempt to find the newly added record using it upper-case key. These updates will fail because the upper-case key in the Update will not match the lower-case key in the newly added record. The Update will fail and Audit History for the update will contain UpdateDeletedRecord.

UpdateDeletedRecord signifies that the server could not find the Updated record based on its key value. The server assumes the updated record was not found because it was deleted. This can arise legitimately in the following way:
1. User 1 adds a records
2. User 2 reads the newly added record
3. User 1 (or any other user) deletes the newly added record
4. User 2 attempts to update the newly added record

You can circumvent this problem by specifying the Appsheet table's initial key value as:
LOWER(UNIQUEID("UUID"))

Use SSL

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.

Use 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 Manage IP Address and Firewall Information.

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 (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.

Data types

PostgreSQL type	AppSheet type	Notes
bigint bigserialinteger smallint smallserial serial	Number
double precision numeric real	Decimal
money	Price
boolean	Yes/No
date	Date
timestamp timestamp with time zone	DateTime	AppSheet DateTime is not time zone aware and time zones will not be captured
interval	Duration
character character varying	Text LongText	Text is used if the column is 100 characters or fewer
json jsonb text xml	LongText

Other types are currently unsupported though AppSheet will attempt to map them to Text.

Columns of Array type (for example, integer[] or text[]) are currently not supported and will be skipped.