How to connect PostgreSQL with Microsoft Flow

This "how-to-connect" documentation will explain the initial configuration of an example connection from PostgreSQL to Outlook using Microsoft Flow. We will be synchronizing data stored in PostgreSQL.

This guide presupposes that you have installed the Layer2 Cloud Connector and that you are familiar with its basic functionality. The Layer2 Cloud Connector User Documentation will provide you with all necessary information.

Contents

1. Configuring PostgreSQL

2. Configuring the Layer2 Cloud Connector

2.1 Creating a new connection

2.2 Configuring the Data Entity 1

2.3 Configuring the Data Entity 2

2.4 Running your connection

3. Hints and known issues

3.1 Connection direction

3.2. More options to access your data with Microsoft Flow

1. Configuring PostgreSQL

You are going to need the PostgreSQL server address, the database name and an user name with the rights to connect to the database. Get this information with your system administrator.

2. Configuring the Layer2 Cloud Connector

2.1. Creating a new connection

Create a new connection by using the Create New Connection option in the Actions pane (right-hand side). The new connection will appear at the bottom of the Connection Manager List (left-hand side). Click on your newly created connection to open the connection configuration settings.

Choose a meaningful name for your connection and replace the current "New Connection" Connection Title with it.

Connections to PostgreSQL can be bi-directional. This specific connection with Microsoft Flow, cannot. An initial connection should always be uni-directional to assure that both data entities are identical before switching to bi-directional. Therefore, choose Left to Right as Direction.

2.2. Configuring the Data Entity 1

We will now set up our Data Entities. Go to the data entity “Data Entity 1” to open the configuration settings.

Choose a Data Entity Title. It is recommended to give your entities meaningful names to maintain an overview when you decide to set up multiple connections.

Select the Data Provider for PostgreSQL from the data provider list. You can search for PostgreSQL by typing into the selection box.

For the Connection String, we need the information mentioned in step 1. You can copy the below connection string and adjust it to match your gathered information. Use the Verify Connection String option to evaluate if the provided connection string is valid.

Server=localhost;Database=postgres;User=postgres;	

• "Server=Server_IP_address;" this property defines which IP address will be connected to.
• "Database=databasename;" this property defines the database you are connecting to.
• "User=username;" this property defines the user with access to the database you are connecting to.

Enter the user account's password into the Password field. The field masks the value for better security.

The Select Statement text box is used to define specific data queries. We will be gathering all data from our employees table. You can copy the below select statement and adjust it to match your needs. Save your changes by using the right-hand pane option Save Changes.

SELECT * FROM employees

To check if all necessary columns are received, you can use the Preview Data option on the right-hand pane which will provide you with a pop-up window showing your sample data from your PostgreSQL entity.

2.3. Configuring the Data Entity 2

We are going to use Microsoft Flow to send an e-mail notification when a new employee is added to the PostgreSQL database.

Go to Microsoft Flow and in My Flows, click on New and Create from blank to create a new Flow.

In the page bottom click on Select hundreds of connectors and triggers.

Type request in the search box and select the trigger When a HTTP request is received.

You are going to need a JSON schema for the request. Go to the Cloud Connector and open the data entity two from the connection. Select the data provider for Flow typing in the box or searching for it. After selecting the provider, set as connection string this first initial value:

WebhookURL=""

Set as SELECT statement the columns from the PostgreSQL table you want to retrieve information from.

SELECT employeeId, employeename, employeemail, employeeaddress, employeecity, employeecountry, employeeloginname

Now in the new section called JSON Schema. Click on Show to get a JSON Schema from the fields you typed in. 

Change the fields accordantly to the table columns you want to get data from and paste the schema into the field Request Body JSON Schema in your Flow. More information about the JSON types and structure can be found here json-schema.org.

Add a new step and select Office 365 Outlook. Than select Send an email. Set the parameters for the email receiver. You can also set the JSON/PostgreSQL data into the e-mail body. 

Save the flow and copy the HTTP POST URL created.

You can now test it. Click on Test and select "I'll perform the trigger action".

Go back to the Cloud Connector and set the WebhookURL property with the value you copied from the HTTP Request in the Microsoft Flow as your Connection String.

WebhookUrl=generatedURLfromMicrosoftFlow

2.4. Running your connection

To run your connection switch back to the main connection configuration node and use the Run Now Button located on the bottom of the setup page. The Run Synchronization Toolbox will also display the synchronization process. 

Below is a data preview of the information we have accessed in our source entity:

This will be the result in our E-Mail after our initial successful synchronization:

See section 3.1 for further information.

3. Hints and known issues

3.1. Connection direction

As far as tested, this connection supports uni-directional only.

3.2. More options to access your data with Microsoft Flow

You can also use SharePoint as intermediate storage to push your data to Microsoft Flow. In this case, you have to configure the Layer2 Cloud Connector to sync your data to SharePoint. From there you can use all the actions provided by Flow to work with your data.

↑Top