Blog Post

Azure Integration Services Blog
3 MIN READ

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

Omar_Abu_Arisheh's avatar
Mar 31, 2023

*

 

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.

 

 

  • 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:

           

 

 

  • 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.

Updated Mar 31, 2023
Version 1.0
  • alfreema's avatar
    alfreema
    Copper Contributor

    Omar_Abu_ArishehI was just confirming your instructions worked for me, and doing a poor job of thanking you for posting them.  I did this last month and haven't checked the steps, so I will double check what I did and post back here.  🙂

  • alfreema's avatar
    alfreema
    Copper Contributor

    I may be wrong about the managed identity.   At first I believed we got it working by creating the user in Azure Postgresql and setting the password to the appid like ...

    In your shell:

    az ad sp list --display-name <logicappname> --query [*].appId --out tsv

    Record the result as 'appid'.

     

    In Azure Postgresql:

    SET aad_validate_oids_in_tenant = off;
    create role logicapp with login password '<appid>' in role azure_ad_user;

    Where 'appid' was the result from the az command.  

     

    But I won't swear that we got that working anymore.

  • alfreema's avatar
    alfreema
    Copper Contributor

    Outstanding.   Can confirm this works and works with managed identities.  If you use a managed identity it doesn't matter what password you use in the Logic App->Configuration->Jdbc_password field.