Connecting to Relational Databases on Heroku with Java

Last updated May 09, 2024

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

Databases are provisioned using the add-on system. Some applications provisions a Heroku Postgres database by default. You can check if you have one by running heroku info and looking at the list of add-ons:

$ 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:          heroku-18
Data size:      (empty)
Addons:         Heroku Postgresql Essential 0
Owner:          jesper@heroku.com

It depends on the buildpack whether a database is provisioned automatically. You can provision a database manually with

$ heroku addons:create heroku-postgresql

After provisioning 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 (or JDBC_DATABASE_URL variable described later) and set up the database connections based on that information.

Using the JDBC_DATABASE_URL

The official Heroku buildpacks for Java, Scala, Clojure, and Gradle will attempt to create a JDBC_DATABASE_URL environment variable when a dyno starts up. This variable is dynamic and will not appear in your list of configuration variables when running heroku config. You can view it by running the following command:

$ heroku run echo \$JDBC_DATABASE_URL
On Windows, you must omit the leading `` or just run `heroku run env`.

The variable will include ?user=<user>&password=<password> parameters, but JDBC_DATABASE_USERNAME and JDBC_DATABASE_PASSWORD environment variables will also be set when possible.

The authoritative source of the Database URL is still the DATABASE_URL environment variable, but the JDBC_DATABASE_URL can be used in most cases.

The Java buildpack will also set the JDBC_DATABASE_URL for the ClearDB MySQL, JawsDB MySQL, and JawsDB Maria add-ons.

For databases using the HEROKU_POSTGRESQL_<COLOR> format, the Java buildpack will create HEROKU_POSTGRESQL_<COLOR>_JDBC_URL, HEROKU_POSTGRESQL_<COLOR>_JDBC_USERNAME, and HEROKU_POSTGRESQL_<COLOR>_JDBC_PASSWORD config variables.

Using the SPRING_DATASOURCE_URL in a Spring Boot app

The official Heroku buildpacks for Java and Gradle will attempt to create SPRING_DATASOURCE_URL, SPRING_DATASOURCE_USERNAME, and SPRING_DATASOURCE_PASSWORD environment variables when a dyno starts up. The values of the variables will be identical to the values in the corresponding JDBC variables.

As long as your application has the proper JDBC driver defined as a dependency, these environment variables should allow your Spring Boot application to connect to the database without any other configuration.

If you need to override the predefined SPRING_DATASOURCE_* environment variables you can set them yourself with the heroku config:set command or in set them in the dashboard. Alternatively, you can add the -Dspring.datasource.url property to your Procfile, which will take precedence over the OS-level environment variables.

Using the JDBC_DATABASE_URL in a Spring Boot app

Spring Boot allows you to externalize your configuration so you can work with the same application code in different environments. You can use properties files, YAML files, environment variables and command-line arguments to externalize configuration.

You can set your database URL in an application.yml file like this:

spring:
  datasource:
    url: ${JDBC_DATABASE_URL}
    username: ${JDBC_DATABASE_USERNAME}
    password: ${JDBC_DATABASE_PASSWORD}

For more information see the official Spring documentation on Externalized Configuration.

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 can be configured to use this value by adding it to your conf/application.conf:

db.default=${DATABASE_URL}

When using Slick with the Play Framework, the configuration looks like this:

slick.dbs.default.driver="slick.driver.PostgresDriver$"
slick.dbs.default.db.dataSourceClass = "slick.jdbc.DatabaseUrlDataSource"
slick.dbs.default.db.properties.driver = "org.postgresql.Driver"

For more information see the Play documentation for the Play Slick module.

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 {
    String dbUrl = System.getenv("JDBC_DATABASE_URL");
    return DriverManager.getConnection(dbUrl);
}

When using DATABASE_URL directly, it would look 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);
}

Note:

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

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

However the Postgres JDBC driver uses the following convention:

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

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

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 {
        String dbUrl = System.getenv("JDBC_DATABASE_URL");
        String username = System.getenv("JDBC_DATABASE_USERNAME");
        String password = System.getenv("JDBC_DATABASE_PASSWORD");

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

        return basicDataSource;
    }
}

When using DATABASE_URL directly, it would look like this:

@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;
    }
}

Again, note the difference in the sub-protocol. The DATABASE_URL uses postgres while JDBC requires the value postgresql.

Using the DATABASE_URL with Hibernate

If you are configuring Hibernate manually with a hibernate.cfg.xml outside of some other framework, you can set the JDBC URL in your Java code when building the service registry. For example:

Map<String,String> jdbcUrlSettings = new HashMap<>();
String jdbcDbUrl = System.getenv("JDBC_DATABASE_URL");
if (null != jdbcDbUrl) {
  jdbcUrlSettings.put("hibernate.connection.url", System.getenv("JDBC_DATABASE_URL"));
}

registry = new StandardServiceRegistryBuilder().
    configure("hibernate.cfg.xml").
    applySettings(jdbcUrlSettings).
    build();

This will override any URL configuring in the hibernate.cfg.xml.

Using SSL with PostgreSQL

We used to suggest adding the URL parameter sslmode=disable to JDBC URLs. We now require use of SSL for all new Heroku Postgres databases. We will be enforcing use of SSL on all Heroku Postgres databases from March 2018. Please do not disable SSL for your database or your applications may break.

By default, Heroku will attempt to enable SSL for the PostgreSQL JDBC driver by setting the property sslmode=require globally. Use of SSL is required for all database connections on Heroku Postgres. The default sslmode is set in a small properties file that is automatically added to your classpath. You prevent this file from being injected by running heroku config:set SKIP_PGCONFIG_INSTALL=true.

Drivers for other database vendors are unaffected.

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 URL param:

sslmode=require

For example:

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

If you do not add sslmode=require you will get a connection error.

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

It is important to add this parameter in code rather than editing the config var directly. Various automated events such as failover can change the config var, and edits there would be lost.

When connecting to a Private or Shield database via mTLS there are some additional parameters required in your JDBC connection URL. See our documentation on connecting via mTLS for further details.

Running database migrations

Most databases will need to have their schema changed at some point. Migration tools such as Liquibase and Flyway control the version history of these changes. You can learn how to run these on Heroku in the article Running Database Migrations for Java Apps

Using with Heroku Postgres Connection Pooling

When using JDBC with Heroku Postgres Connection Pooling, the JDBC_DATABASE_URL will be set to the value defined by the DATABASE_CONNECTION_POOL_URL configuration variable.

Sample project

A sample project illustrating the various 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 heroku-18
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
-----> 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 -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

Feedback

Log in to submit feedback.