Configure PostgreSQL Connection in Logic Apps (Standard) with JDBC, call Function with Execute Query

[Guest Omar_Abu_Arisheh]

*

 

 

 

Many are using relational databases, the JDBC Connector for Logic Apps (Standard) allows to establish a connection with most relational databases and perform actions. We are exploring here, step-by-step, how to create an Azure PostgreSQL, configure the connection with all the requirements, then call a function and use the response in the body of an email to be sent.

 

 

 

Here is a step-by-step for creating the PostgreSQL in Azure, configuring the connection and other requirements, then creating the Logic App (Standard) workflow, add the actions, and test a function, finally parse the response and send it by email.

 

 

 

• Create a PostgreSQL on Azure, search for PostgreSQL in the search box in Azure Portal, then select “Azure Database for PostgreSQL”, the cost will depend on the VM resources you select.
  

 

 

 

 

 

 

 

 

• Once you create the server, browse to the Connection strings and copy the one for JDBC, this will be used later.
  

 

 

 

 

 

 

 

 

• I downloaded the pgAdmin to be able to create the database, tables, enter some data, and create the function that we will be calling: PostgreSQL: File Browser
  
• I changed the below settings, as I was able to connect from pgAdmin using the Certificate, but not from Logic Apps.
  

 

 

 

 

• How to configure SSL/TLS for PostgreSQL: SSL/TLS - Azure Database for PostgreSQL - Single Server | Microsoft Learn
  
• The Certificate you can use when connecting, if you have enabled SSL/TLS: https://www.digicert.com/CACerts/BaltimoreCyberTrustRoot.crt.pem
  
• When connecting from pgAdmin, enter a name for the PostgreSQL:
  

 

 

 

 

• Under Connection, enter the server URL, full username@servername:
  

 

 

 

 

• Under parameters, use sslmode “verify-full”, if you disabled the SSL/TLS in PostgreSQL server you can select “disable”.
  
• Use the Certificate mentioned earlier if using SSL/TLS.
  

 

 

 

 

• After connecting successfully, you can create Tables, insert data, and create functions:
  

 

 

 

 

• Now we create a Logic App Standard, a new workflow with any trigger that you require.
  
• This is a good reference for more in-depth guide on this: Connecting to a relational database using JDBC in Azure Logic Apps (Standard) (microsoft.com)
  
• For JDBC Connector to be able to connect to PostgreSQL, you will have to do some configurations before:
  
• Download the JAR file from: Home | pgJDBC (postgresql.org)
  
• postgresql-42.6.0.jar
  

 

 

 

 

• Next, create a folder: site/wwwroot/lib/builtinOperationSdks/JAR/
  
  
• Then upload the JAR file there:
  
  
• You can use kudu to upload the file
  Kudu service overview - Azure App Service | Microsoft Learn
  
  

 

 

 

 

• You can use the portal:
  

 

 

 

 

• You can use Microsoft Azure Storage Explorer as well:
  

 

 

 

 

 

 

 

 

 

• If you don’t know which storage to upload to, browse to the Logic App Configurations, and find the storage name:
  

 

 

 

 

• Under the Logic App Configuration, create a new Application setting, this will allow JDBC Connector use the JAR library we uploaded:
  
• Name: AzureWebJobsFeatureFlags
  
• Value: EnableMultiLanguageWorker
  

 

 

 

 

• We then add a new action, from the Connectors search, under buit-in Connectors, select JDBC, Execute Query action.
  
• To create a new connection, use the URL we got earlier from Postgresql Server, enter the username, and password:
  

 

 

 

 

• You can edit these values later from the Logic App Configuration (App Settings).
  
  
• The URL should look like this: jdbc:postgresql://mypostgresqlserver.postgres.database.azure.com:5432/postgres?user=myuser@mypostgresqlserver&password=mypassword@&sslmode=disable
  
  

 

 

 

 

• For the action, you can either start with a simple select statement on one of your tables, or instead of executing a query you can use Get tables action.
  
• For our example, we are calling a Function and passing a parameter value of 1, “SELECT demo_function(1)”
  

 

 

 

 

• In pgAdmin, create this Function (assuming we have a table called mydata with two columns, data1 (bigint) and data2(text)):
  

 

 

 

CREATE or replace FUNCTION demo_function(in mydata1 bigint)

 

RETURNS text AS $mydata2$

 

DECLARE

 

mydata2 text;

 

begin

 

select data2 from mydata where data1 = mydata1 limit 1 into mydata2;

 

RETURN mydata2;

 

end

 

$mydata2$ language plpgsql

 

 

 

• Fill the query in the action:
  

 

 

 

 

• Save your workflow, and on the workflow overview; execute the trigger, you should have the response as below:
  

 

 

 

 

• We can now use the result from the execute query and send an email for example.
  
• Add a Send an email action (Connector: Outlook 365).
  
• And to parse the output refer to this reference if needed: Reference guide for expression functions - Azure Logic Apps | Microsoft Learn
  
• As the response is inside an array, I can either put the Send an email action in a for-each or just get the first item (this will throw an error if there is no items in the response of the previous action).
  

 

body('Execute_query_2')[0]?['demo_function']

 

 

 

 

 

 

• Now will test the workflow again, and receive an email with the data.
  

 

 

 

 

Thank you :)

 

 

 

*PostgreSQL and JDBC logos are sole property of their respective owners.

 

Continue reading...