Heroku does not play well with SQLite
SQLite is serverless, runs in memory, and backs up its data 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!
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.
Even though SQL is the standard language to use with SQL-based relational databases such as SQLite, PostgreSQL, MySQL etc., each of these DBMSs may have their dialect of SQL. This at times may affect how you would need to write/set up your SQL queries.
First, add the JDBC driver for PostgreSQL to your project dependencies:
SQLite as follows (in case the app is run locally):
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:
Here is the implementation of
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.
In the above code, note how we first decide which DBMS we are working with (SQLite or PostgreSQL) to set up our table creation accordingly;
serial is Postgres way of creating an autoincrement integer column.
Note the use of
getConnection() method; here is the implementation for it:
When we were working with SQLite, we used a URI like
jdbc:sqlite:./MyBooksAoo.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.
DATABASE_URL for the Heroku Postgres follows the below convention
However, the Postgres JDBC driver uses the following convention:
The code in
getConnection() converts the Heroku
DATABASE_URL into a JDBC URI.
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. You can download and lean about Postgres here.
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:
Finally, run the following command to deploy your app:
To checkout the Postgres database provisioned for your app (and its content) you can use Heroku CLI. Learn more on this here. Alternatively, install PostgreSQL locally and connect it to Heroku Postgres following the instructions here.