MuleSoft: Connect PostgreSQL Database and Call PostgreSQL Function

What Is PostgreSQL?

PostgreSQL is an open source object-relational database system that uses and extends the SQL language to store and scale complicated data workloads.

A prerequisite to start with this walkthrough is to have a database connector added in Anypoint Studio. You can add these connectors from the Add modules option. Also, you need to have an ElephantSQL account, as we are going to use the PostgreSQL database hosted on the ElephantSQL platform. You can use any other PostgreSQL service provider platform or self-managed PostgreSQL server.

Let's configure the database connector step by step to connect to PostgreSQL:

1. Select the Generic Connection from the Connection dropdown.
2. Click on Configure JDBC Driver and select the Add Maven dependency option.
3. Search for PostgreSQL dependency. Add it, and click on the Finish button to download the dependency.
4. Provide the below connection details to connect to the database instance and click on Test Connection to check connectivity.
   • URL - jdbc:postgresql://<<host>>:<<port>>/<<databaseName>>
   • Driver class name - org.postgresql.Driver
   • User - Username
   • Password - Password 

          In ElephantSQL, you can get database connection details in the Details section.

Let's create a PostgreSQL function that will add 2 numbers and return the sum of them. In ElephantSQL's browser section, you can execute SQL script to create a function in PostgreSQL.

CREATE FUNCTION addition(num1 integer, num2 integer) RETURNS integer
    AS 'select num1 + num2;'
    LANGUAGE SQL
    IMMUTABLE
    RETURNS NULL ON NULL INPUT;

Hope you enjoyed reading this article.