PostgreSQL

PostgreSQL is an open source object-relational database system. With the PostgreSQL integration, you can connect to an existing database and bring the data directly into your Glide app. You can both read and write to your PostgreSQL database.

Adding PostgreSQL to your app

Glide apps cannot be started directly from SQL data sources. If you’re starting a new app, select Glide Tables as the source and then follow the steps below to add your SQL data.

The location of the credentials required below will vary depending on which service you use to host your PostgreSQL database. If your credentials appear as a connection string from your hosting platform, the information above can be found in that string in this format: postgresql://username:password@host:port/database

1. In Glide, open the Data Editor and click the plus (+) next to Tables to add a new table.

2. Navigate to SQL and select PostgreSQL.

3. Give the database connection an internal name.

4. Choose your connection type (SSL/TLS or SSH).

5. Enter the Hostname or IP address of your database.

6. The default port number (5432) will appear automatically, but you may change it if needed.

7. Enter the database name. This must match the name of your PostgreSQL database.

8. Disable SSL certificate verification if desired. This is less secure.

9. Add the username and password of the database user that should be used for access.

10. Click Continue to save your configuration settings.

Syncing Data from PostgreSQL

Now that you’ve connected the database to your app, you can add data from it in the data editor. You can link a full dataset to create a read and write table in Glide, or you can query your data to create a read-only table. To read and write to a table, your table must have at least one primary key. If you haven’t set a primary key in your source table, Glide will prompt you to choose one. Date or time fields cannot be the primary key at this time.

Creating a read-and-write table

1. In the Data Editor, click the plus (+) button to add a new table.

2. Select PostgreSQL.

3. Search or browse your datasets and select the one you want to sync with Glide.

4. Select Continue.

5. Your dataset will appear as a table in the data editor.

If your table’s schema has changed, click Sync Table Schema in the upper right corner of the data editor to update the columns of your table. This will not update automatically, so if you’ve made a change to your table in your SQL database, you must sync the table schema for that change to appear in Glide.

Creating a read-only table with a query

You can also query your data to create a read-only table in Glide. Data from SQL data sources is not mirrored. This means that Glide queries your database to retrieve the data from your SQL table. This query happens on demand when you view the data in the data editor, and when users view the data in your app. To allow users to manually refresh the data in your app, you can create a Reload query action.

1. In the Data Editor, click the plus (+) button to add a new table.

2. Select PostgreSQL.

3. Select Custom Query to open the query screen.

4. In the query screen, run whatever queries you like to create the dataset you want to pull into Glide.

5. In order to save your query as the definition of your table, you will need a column called id that must have unique values for each row.

6. When you're ready, you can name the query by clicking on Untitled query and changing the name.

7. Select Save.

8. You can now work with this data for your Glide app.

Editing Your Queried Table

To change your table:

1. In the data editor, open the table you created with the query.

2. Select edit query in the upper right corner of your query table.

3. Make any changes to the query you'd like.

4. Preview your data, then save it to update your table.

Removing your SQL data source

To remove your SQL data from your Glide app:

1. In the Data Editor, unlink each table you synced from the database.

2. Once all tables are unlinked, go to the App Settings menu and select Remove from app.

Note that if you created any Reload Query actions connected to this data source, those actions will disappear from your app.

File Tracking with SQL Databases

If you use components that allow users to upload files to your app, Glide will automatically create a Big Table in your team to save both the file and several pieces of metadata. The table will be named with the convention Team Name: Uploaded Files. This new table will automatically store the following metadata:

• URL of the file

• Timestamp of when the file was added

• AppID of the app that saved the file

• Email of the user who uploaded the file

• Name of the table where the file was saved

• the RowID of the row the file was saved in

This table will be created the first time a user uploads a file to an app with a SQL data source, and the table cannot be deleted. The columns in the table also cannot be changed.

Limitations of PostrgeSQL

It is not possible to use a table from a SQL data source as your app’s Users Table.

Write Limitations

The following functionality is not yet supported for SQL data sources:

• Writing to date or time fields

• Creating new fields in Glide

We recommend structuring you app so these field limitations are respected. Fields that are non-nullable will show errors when you add rows because the default value will be null. We recommend configuring the default values in your SQL database.

Fields that have constraints that are unknown to Glide might show errors when you try to add or edit them.

Query Limitations

Query-based tables cannot be written to. The following functionality is not yet supported for PostgreSQL:

• Common Table Expressions (i.e., WITH syntax)

• ORDER BY within the sub-query will not change the ordering of the result set

  • To order your rows, you can use the Sort data feature in a collection

• If you are using functions or other expressions, you should alias the result to what you want the column name to appear as in Glide

Aggregation Limitations

The following aggregation features are not currently supported for computed columns in tables from SQL data sources:

• Rollup column

• Lookup column

• Query column

• Group by

• Filtering, sorting, and searching

• Joined List column

• Relations based on computed columns where the target column is a computed column

• Lookups of relations with multiple matches