*
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.
Microsoft
