Connecting to Relational Databases on Heroku with Java

Last Updated: 27 November 2013

database java playframework

Table of Contents

Applications on Heroku can use a variety of relational database services including the Postgres database offered by Heroku and MySQL offered by AWS.

If you have questions about Java on Heroku, consider discussing them in the Java on Heroku forums.

Databases are provisioned using the add-on system. Some applications will have a small, free postgres database provisioned by default. You can check this by running

$ heroku info
=== sparkling-wine-2003
Web URL:        http://sparkling-wine-2003.herokuapp.com/
Git Repo:       git@heroku.com:sparkling-wine-2003.git
Repo size:      21M
Slug size:      916k
Stack:          cedar
Data size:      (empty)
Addons:         Heroku Postgresql Dev
Owner:          jesper@heroku.com

and look for the “Heroku Postgresql Dev” under Addons. It depends on the buildpack whether a database is provisioned automatically. You can provision the dev database manually with

$ heroku addons:add heroku-postgresql

The dev database is meant for testing purposes. For a production application you must use one of the Heroku Postgres databases or SQL add-ons.

Once you have provisioned a relational database to your application. The application reads the database connection information from the DATABASE_URL config variable. It is formatted like this:

[database type]://[username]:[password]@[host]:[port]/[database name]

For instance:

postgres://foo:foo@heroku.com:5432/hellodb

You can see the DATABASE_URL provided to an application by running:

$ heroku config
DATABASE_URL            => postgres://foo:foo@heroku.com:5432/hellodb

It is not recommended to copy this value into a static file since the environment may change the value. Instead an application should read the DATABASE_URL environment variable and setup the database connections based on that information.

Using the DATABASE_URL in a Play! Framework app

Play! Framework supports the DATABASE_URL environment variable out-of-the box. The built-in ORM framework will automatically use this variable if it is present, so there is no need for any additional configuration.

Using the DATABASE_URL in plain JDBC

To instantiate a JDBC connection in your code, you can use a method like this:

private static Connection getConnection() throws URISyntaxException, SQLException {
    URI dbUri = new URI(System.getenv("DATABASE_URL"));

    String username = dbUri.getUserInfo().split(":")[0];
    String password = dbUri.getUserInfo().split(":")[1];
    String dbUrl = "jdbc:postgresql://" + dbUri.getHost() + ':' + dbUri.getPort() + dbUri.getPath();

    return DriverManager.getConnection(dbUrl, username, password);
}

Using the DATABASE_URL in Spring with XML configuration

This snippet of Spring XML configuration will setup a BasicDataSource from the DATABASE_URL and can then be used with Hibernate, JPA, etc:

<bean class="java.net.URI" id="dbUrl">
    <constructor-arg value="#{systemEnvironment['DATABASE_URL']}"/>
</bean>

<bean id="dataSource" class="org.apache.commons.dbcp.BasicDataSource">
    <property name="url" value="#{ 'jdbc:postgresql://' + @dbUrl.getHost() + ':' + @dbUrl.getPort() + @dbUrl.getPath() }"/>
    <property name="username" value="#{ @dbUrl.getUserInfo().split(':')[0] }"/>
    <property name="password" value="#{ @dbUrl.getUserInfo().split(':')[1] }"/>
</bean>

Using the DATABASE_URL in Spring with Java configuration

Alternatively you can use Java for configuration of the BasicDataSource in Spring:

@Configuration
public class MainConfig {

    @Bean
    public BasicDataSource dataSource() throws URISyntaxException {
        URI dbUri = new URI(System.getenv("DATABASE_URL"));

        String username = dbUri.getUserInfo().split(":")[0];
        String password = dbUri.getUserInfo().split(":")[1];
        String dbUrl = "jdbc:postgresql://" + dbUri.getHost() + ':' + dbUri.getPort() + dbUri.getPath();

        BasicDataSource basicDataSource = new BasicDataSource();
        basicDataSource.setUrl(dbUrl);
        basicDataSource.setUsername(username);
        basicDataSource.setPassword(password);

        return basicDataSource;
    }
}

Note:

The DATABASE_URL for the Heroku Postgres add-on follows the below convention

postgres://<username>:<password>@<host>/<dbname>

However the Postgres JDBC driver uses the following convention:

jdbc:postgresql://<host>:<port>/<dbname>?username=<username>&password=<password>

Notice the additional l at the end of the URL scheme. Due to this difference, for Postgres, you will need to hardcode the scheme to postgresql in your Java class or your Spring XML configuration.

Connecting to a database remotely

If you’re using a Heroku Postgres database you can connect to it remotely for maintenance and debugging purposes. However doing so requires that you use an SSL connection. Your JDBC connection URL will need to include the following:

ssl=true&sslfactory=org.postgresql.ssl.NonValidatingFactory

If you leave off ssl=true you will get a connection error. If you leave off sslfactory=org.postgresql.ssl.NonValidatingFactory you may get an error like:

unable to find valid certification path to requested target

Click here for more information on SSL support with Postgres JDBC driver. For other SQL databases, refer to your vendor specific JDBC documentation on how to configure SSL support.

Sample project

A sample project illustrating these three methods of setting up a database connection on Heroku can be found at: https://github.com/heroku/devcenter-java-database

To try it out first clone the git repository:

git clone git://github.com/heroku/devcenter-java-database.git

In the devcenter-java-database directory run the Maven build for the project:

mvn package

If you have a local Postgres database and want to test things locally, first set the DATABASE_URL environment variable (using the correct values):

  • On Linux/Mac:
export DATABASE_URL=postgres://foo:foo@localhost/hellodb
  • On Windows:
set DATABASE_URL=postgres://foo:foo@localhost/hellodb

To run the example applications locally, execute the generated start scripts:

  • On Linux/Mac:
sh devcenter-java-database-plain-jdbc/target/bin/main
sh devcenter-java-database-spring-xml/target/bin/main
sh devcenter-java-database-spring-java/target/bin/main
  • On Windows:
devcenter-java-database-plain-jdbc/target/bin/main.bat
devcenter-java-database-spring-xml/target/bin/main.bat
devcenter-java-database-spring-java/target/bin/main.bat

For each command you should see a message like the following indicating that everything worked:

Read from DB: 2011-11-23 11:37:03.886016

To run on Heroku, first create a new application:

$ heroku create
Creating stark-sword-398... done, stack is cedar
http://stark-sword-398.herokuapp.com/ | git@heroku.com:stark-sword-398.git
Git remote heroku added

Then deploy the application on Heroku:

$ git push heroku master
Counting objects: 70, done.
Delta compression using up to 8 threads.
Compressing objects: 100% (21/21), done.
Writing objects: 100% (70/70), 8.71 KiB, done.
Total 70 (delta 14), reused 70 (delta 14)

-----> Heroku receiving push
-----> Java app detected
-----> Installing Maven 3.0.3..... done
-----> Installing settings.xml..... done
-----> executing /app/tmp/repo.git/.cache/.maven/bin/mvn -B -Duser.home=/tmp/build_2y7ju7daa9t04 -Dmaven.repo.local=/app/tmp/repo.git/.cache/.m2/repository -s /app/tmp/repo.git/.cache/.m2/settings.xml -DskipTests=true clean install
   [INFO] Scanning for projects...
   [INFO] ------------------------------------------------------------------------
   [INFO] Reactor Build Order:
   [INFO]
   [INFO] devcenter-java-database-plain-jdbc
   [INFO] devcenter-java-database-spring-xml
   [INFO] devcenter-java-database-spring-java
   [INFO] devcenter-java-database
   [INFO]
   [INFO] ------------------------------------------------------------------------
   [INFO] Building devcenter-java-database-plain-jdbc 1.0-SNAPSHOT
   [INFO] ------------------------------------------------------------------------
   Downloading: http://s3pository.heroku.com/jvm/postgresql/postgresql/9.0-801.jdbc4/postgresql-9.0-801.jdbc4.pom
...

Now execute any of the examples on Heroku:

$ heroku run "sh devcenter-java-database-plain-jdbc/target/bin/main"
Running sh devcenter-java-database-plain-jdbc/target/bin/main attached to terminal... up, run.1
Read from DB: 2011-11-29 20:36:25.001468

Further learning

Learn about the database options