MuleSoft: Connect PostgreSQL Database and Call PostgreSQL Function
In this article, we will explore how to connect the PostgreSQL database from the Mule application. We will also see how to call the PostgreSQL function.
Join the DZone community and get the full member experience.Join For Free
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:
- Select the Generic Connection from the Connection dropdown.
- Click on Configure JDBC Driver and select the Add Maven dependency option.
- Search for PostgreSQL dependency. Add it, and click on the Finish button to download the dependency.
- 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.
Opinions expressed by DZone contributors are their own.