The MuleSoft Database Connector provides the ability to easily connect to any database as long as you have the JDBC driver. Here’s a quick post/walk-through on how to configure the connector to connect to Heroku Postgres. This post assumes you have a Heroku account with Postgres already added and configured. You also have Anypoint Studio 7.x installed. To start, let’s build a simple Mule flow with the following components:
The flow will listen for an HTTP request, make a call to Heroku Postgres, and return the data in JSON format. We’ll keep the listener simple and use the defaults, 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
<dependency>
<groupId>org.postgresql</groupId>
<artifactId>postgresql</artifactId>
<version>42.2.1</version>
</dependency>
It should look like the following:
Click on 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://<host>:<port>/<database name>?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. The hardest part was figuring out the Database URL connection string. Let me know if you have any questions or run into any issues.
Versions
- Anypoint Studio 7.3.4
- Postgresql JDBC Driver 42.2.1
- Mule Server 4.2.0 EE