PostgreSQL is one of the world’s most popular relational database management systems. Millions of developers and companies rely on PostgreSQL as their transactional data store of choice to drive application health and decision making. With Heroku Postgres, you gain the advantages of Heroku’s operational expertise of scaling applications and allow your team to quickly access data in the cloud. But how can you get data in and out of Heroku Postgres quickly and easily?
That’s where MuleSoft can help accelerate data integration and development speed with pre-built connectors that reduce the need to write any code. One of the most popular connectors — and one that will help access data from Heroku Postgres — is the MuleSoft Database Connector which provides the ability to easily connect to any JDBC compliant database.
In this article, we’ll walk through the process of configuring the connector to Heroku Postgres. This article assumes you have a Heroku account with Postgres already added and configured. This article also assumes that you have Anypoint Studio 7.x installed on your local machine and have some experience creating flows.
| Tool Versions: – Anypoint Studio 7.3.4 – Database Connector 1.5.0 – Postgresql JDBC Driver 42.2.1 – Mule Server 4.2.0 EE |
To start, let’s create a new Mule project and build a simple flow with the following components in Anypoint Studio.
The flow will listen for an HTTP request, make a call to Heroku Postgres, and return the data back to the user in JSON format. We’ll keep the HTTP listener simple and use the defaults that come out-of-the-box. In this case, localhost and port 8081. For the path, we’ll use /post so the config looks like the following:
Next, let’s configure the Database Connector. Under Basic Settings, click on the green plus sign to add a new configuration. Change the Connection drop down to Generic Connection then click on Configure… for the JDBC Driver field and then click on Add Maven dependency.
Paste the following snippet into the text box on the right of the Maven dependency dialog box.
org.postgresql postgresql 42.2.1
It should look like the following:
Click “Finish.”
Back in the Global Element Properties dialog window. Fill in the URL, Driver class name, User, and Password fields with your credentials from Heroku Postgres.
You can find your credentials in Heroku Postgres under Settings > Database Credentials. Just click on View Credentials to see the host, database, port, username, and password.
The URL should be in the following format below. The sslmode field is required.
jdbc:postgresql://: / ?sslmode=require
The Driver class name should be org.postgresql.Driver
Your configuration should look like the following below. Click on Test Connection to make sure everything was configured correctly.
Once you click OK, fill in the SQL Query Text field with a valid SQL query that returns data back from your Heroku Postgres database.
The last thing we need to configure is the Transform Message to convert the data from the database to JSON. Use the following DataWeave script:
%dw 2.0 output application/json --- payload
It should look like the following:
And that’s it! I’ll leave it up to you to run and test the flow from your browser. Using the Database Connector, we were able to quickly retrieve data from Heroku Postgres. Understanding how to configure the Database URL connection string is often the most challenging part. But once you have that setup, you can reuse the connection configuration throughout the application. If you have any questions or run into any issues, leave a comment below.
Visit MuleSoft’s Anypoint Exchange to see even more connectors available on Anypoint Platform.
