Database in the Cloud

Heroku does not play well with SQLite

SQLite runs in memory, and backs up its data store in small files on disk that are easily created and moved around. While easy to use, SQLite is not intended as a production grade database. In particular, SQLite will not play well with Heroku because Heroku uses an ephemeral filesystem; you can write to it, and you can read from it, but the contents will be cleared periodically. If you were to use SQLite on Heroku, you would lose your entire database at least once every 24 hours!

PostgreSQL database

Heroku provides production grade PostgreSQL databases as a service. PostgreSQL database can be used by any language and framework, and it is very easy to connect your Java App to it; you'll need to change your JDBC driver but probably not much else.

First, add the JDBC driver for PostgreSQL to your project dependencies:

compile 'org.postgresql:postgresql:42.2.10'

Let's write a sample code to demonstrate the process of connecting to Heroku Postgres. Here is the main method from last reading; I added a call to workWithDatabase() at the end:

public static void main(String[] args) {
  final int PORT = getHerokuAssignedPort();
  Javalin app = Javalin.create().start(PORT);
  app.get("/", ctx -> ctx.result("Hello Heroku!"));
  workWithDatabase();
}

Here is the implementation of workWithDatabase():

private static void workWithDatabase(){
  try (Connection conn = getConnection()) {

    String sql = "CREATE TABLE IF NOT EXISTS Courses(" +
                    "name VARCHAR(30) NOT NULL," +
                    "url VARCHAR(100)" +
                 ");";
    Statement st = conn.createStatement();
    st.execute(sql);

    sql = "INSERT INTO Courses(name, url) VALUES ('oose', 'jhu-oose.com');";
    st.execute(sql);

  } catch (URISyntaxException | SQLException e) {
    e.printStackTrace();
  }
}

The workWithDatabase is a very simple example of using JDBC to (1) establish a Connection to a SQL database and (2) execute two SQL statements to create a table and insert a record into it. Note the use of getConnection() method; here is the implementation for it:

private static Connection getConnection() throws URISyntaxException, SQLException {
  String databaseUrl = System.getenv("DATABASE_URL");
  if (databaseUrl == null) {
    // Not on Heroku, so use SQLite
    return DriverManager.getConnection("jdbc:sqlite:./Store.db");
  }

  URI dbUri = new URI(databaseUrl);

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

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

When we were working with SQLite, we used a URI like jdbc:sqlite:./Store.db to instantiate a JDBC connection in your code. Heroku puts the URI of the PostgreSQL database in a environment variable DATABASE_URL. We can directly get the DATABASE_URL in code. The DATABASE_URL for the Heroku Postgres 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>

The code in getConnection() converts the Heroku DATABASE_URL into a JDBC URI.

Warning

The example above uses SQLite when you are not on Heroku and PostgreSQL database when you are on Heroku. This is only for demonstration purposes. It is important that you use the same database in production as in development. So, if you are going to deploy your app on Heroku, you will need to install the PostgreSQL database locally.

PostgreSQL Add-on

Before you deploy your app to Heroku, you need to create the Heroku Postgres add-on for your app using the Heroku CLI; open the terminal at the root directory of your Java project and type the following command:

$ heroku addons:create heroku-postgresql

Deploy

Finally, run the following command to deploy your app:

$ ./gradlew build deployHeroku

To checkout the Postgres database provisioned for your app (and its content) you can use third-party tools like https://heroku-data-explorer.herokuapp.com/ or https://www.adminer.org/. Alternatively, install PostgreSQL locally and connect it to Heroku Postgres following the instructions here.

Here is the repository of a the sample code above, for your reference.