Applications on Heroku can use a variety of relational database services including the Postgres database offered by Heroku and MySQL offered by AWS. 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
:::term
$ 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: Shared Database 5MB
Owner: jesper@heroku.com
and look for the "Shared Database 5MB" under Addons. It depends on the buildpack whether a database is provisioned automatically. You can provision the shared database manually with
$ heroku addons:add shared-database:5mb
The shared database is meant for testing purposes. For a production application you must use one of the production quality SQL database services:
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]/[database name]
For instance:
postgres://foo:foo@heroku.com/hellodb
You can see the DATABASE_URL provided to an application by running:
:::term
$ heroku config
DATABASE_URL => postgres://foo:foo@heroku.com/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.
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.
To instantiate a JDBC connection in your code, you can use a method like this:
:::java
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.getPath();
return DriverManager.getConnection(dbUrl, username, password);
}
This snippet of Spring XML configuration will setup a BasicDataSource from the DATABASE_URL and can then be used with Hibernate, JPA, etc:
:::xml
<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.getPath() }"/>
<property name="username" value="#{ @dbUrl.getUserInfo().split(':')[0] }"/>
<property name="password" value="#{ @dbUrl.getUserInfo().split(':')[1] }"/>
</bean>
Alternatively you can use Java for configuration of the BasicDataSource in Spring:
:::java
@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.getPath() + ":" + dbUri.getPort() + dbUri.getPath();
BasicDataSource basicDataSource = new BasicDataSource();
basicDataSource.setUrl(dbUrl);
basicDataSource.setUsername(username);
basicDataSource.setPassword(password);
return basicDataSource;
}
}
If you're using a Heroku Postgres dedicated database you can connect to it remotely for maintenance and debugging purposes. However doing so requires that you use an SSL connection. This can be done with JDBC.
your JDBC connection URL will need to have the following parameters:
jdbc:postgresql://host/database?user=user&password=password&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
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:
:::term
$ 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:
:::term
$ 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:
:::term
$ 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