After adding and authenticating a PostgreSQL Connection, you can add Form Connectors to push data to a database or pull data from a database using Data Source Connectors, which is what this article is about.

 Our platform lets you quickly and easily connect Data Sources to your SQL Server tables, enabling one-way synchronization where any changes in the SQL table are regularly downloaded into your data source.

 Your data source will be automatically updated in this way until you remove the connector or an error occurs, such as losing authorization to access the database.

Adding

Data Source Settings

• Navigate the side menu to Data Hub > Data Sources
• Hover over a data source and click the “Settings” icon link ()
• In Settings, hit the “Add Connector” button
• Select the relevant option to add the connector

This will refresh the page with the newly added connector ready for configuration.

Nothing has been saved at this stage, so save your connector after making any changes to enable or update it.

Alternatively, if you’re viewing the rows of a data source, you can navigate to the Settings page using the option under the page’s title.

Configuring

After adding the Data Source Connector, configure the connector. Enter an optional schema, the table name to retrieve rows from, or an optional clause to return specific rows.

Refresh Frequency

 The frequency at which this connector pulls data.

Schema

Add an optional PostgreSQL database schema where the table is stored. If kept empty, the default schema will be selected.

Table Name

The PostgreSQL database table from which rows will be retrieved.

Date Format

Specify the format used to convert date values to text when retrieving new rows

Where Clause

Add an optional where clause to the query that only returns the desired records. e.g. WHERE LastName = ‘Good’

Ignore Internal Columns

If enabled, this connector will ignore all columns where the column name starts with an underscore, e.g., _lastupdated.

If your table has a column named _identity and internal columns are not ignored, this column will always be the first column of the data source. Other internal columns are added to the end.

Connector Logs

When you save your changes, the connector will be fired for the first time, and after that, it will run at the refresh frequency you specified in the connector settings.

Wait a minute or so, or click the three-dot menu (  ) and access the RUN NOW to manually trigger the connector and check the Rows in your data source.

If rows are not downloaded, there may be an error with the connection, which can be confirmed by viewing the connector’s logs. To identify and resolve the issue, you can review the logs by clicking the three-dot menu (  ) and selecting VIEW LOGS.

Once resolved, you can run the connector again and confirm that the rows have been successfully downloaded.

Identify Updated and Deleted Rows in your SQL Table

For more advanced platform users who wish to have more granular control over their SQL data, we have a way for you to identify the updated and deleted rows in your SQL table. To do this, you will need to add the following two columns to the end of your SQL table unless the table is auto-maintained by a Form Connector that creates the columns for you.

• _lastupdated
  This column contains the date-time value of when this row was last updated.
  Please ensure it is a not null column of the DateTime data type. All values for this column must be in the UTC timezone.
• _deleted
  A boolean column that represents whether or not this row has been deleted.

 For the _lastupdated column, we expect users to update this column with the current UTC time whenever updating an existing row or adding a new row on the SQL Server side.

So when the next sync occurs on the data source and the value of this column exceeds the previous sync time, the platform will know to pull that row data across.

The _deleted column is how the platform determines whether the row was deleted on the SQL server side.
So, instead of simply deleting rows from the SQL table, you should mark these rows as deleted by setting this column to 1 (true) and also update the _lastupdated for that row to the current UTC time.

This is important because if you were to delete rows from the table, then the platform would never know about this, and the row would persist on the platform unless you have deleted and re-added the connector.

Once you are sure that these deletes have been synced, you can choose to delete these rows from the SQL Database entirely. However, we would suggest that you leave them in place.