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.

 

Step 1 PostgreSQL connection setup.png

 

 

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.

 

Step 2 PostgreSQL integration source setup.png

 

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

 

Step 2.5 PostgreSQL integration connection string setup.png

 

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.

 

 

Preview data of PostgreSQL integration.png

 

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.

 

Microsoft Flow create

 

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

 

Microsoft Flow search trigger

 

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

 

Microsoft Flow request

 

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. 

 

Cloud Connector JSON schema

 

Microsoft Flow JSON schema

 

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.

 

Microsoft Flow set JSON schema

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.

 

 

Microsoft Flow add new step

 

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

 

Microsoft Flow run

 

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

 

 

target setup for microsoft flow integration in the Layer2 Cloud Connector

 

 

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. 

 

Step 5 PostgreSQL data integration start.png

 

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

 

PostgreSQL integration ready.png

 

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

 

Finished PostgreSQL integration.png

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

Search for more data integration & synchronization solutions

Icon of Contact us - Chat - Layer2 leading solutions

Questions?

Contact us directly to discuss your specific requirements, help you with purchasing, or with any other questions.

 

Icon for Layer2 Solutions Finder- Layer2 leading solutions

About the Layer2 Cloud Connector

Get more information about the Layer2 Cloud Connector on the product page.

 

 

Icon for Layer2 Solutions Finder- Layer2 leading solutions

Try it out for 15 days

Get a free trial of the Layer2 Cloud Connector after a short registration.