Lesson Wednesday

As we know by now, working with H2 provides us some really excellent advantages over working with a full-fledged database. It's very easy to get up and running, requiring no installation but adding a few lines to our build.gradle file. And it makes our projects uniquely portable: We can run our apps on machines that do not have postgres installed, and we don't have to spend time dumping our database schema and creating it on another machine. We can clone our repo, import our dependencies, and we are up and running. H2 acts as a kind of postgres-emulator if you will, which is great for testing purposes and building things quickly.

That said, working with H2 does have some drawbacks. Firstly, it does not have the same full-fledged capabilities as a postgres database does - there are some more advanced techniques that don't work as well on H2. Secondly, we can't log in to our server to see our data that was created. Thirdly - while services exist that allow us to publish our apps to the web, and some of those services (like Heroku) allow us to attach postgres databases to our apps, these generally don't work with solutions like H2.

Switching to postgres & installing a db server

If we want to take advantage of the above, we'll need to switch our applications over to using a full postgres server. This isn't that difficult, but it does require that you have a postgres server installed on the machine where you would like to run your app.

All Epicodus machines have postgres servers installed already, but if you are using a mac or windows machine at home, you will have to dig into installing a postgres server yourself. This is fairly straightforward on mac, but can get a little tricky on windows. That said, it is also a good learning experience, so if you have the time, we recommend trying it.

Follow the linked below to get started with setting up a postgres server. If you already have one installed or are working on a classroom mac, feel free to skip these steps. We'll walk through necessary code changes after you have confirmed you have postgres installed.

Installing Postgres on Mac and Windows

Creating our Database structure

Instead of creating our database on-the-fly when we run our app, we want to go ahead and create the Postgres databases before running the app. I say databases plural because we want to create two - the development database (todolist) and the test database (todolist_test).

One way to do this would be to manually create the databases within psql, as follows: (The text below shows the commands you type and the responses psql provides.)

$ psql
psql (9.5.4)
Type "help" for help.


# \c todolist;
You are now connected to database "todolist" as user "epicodus_staff".

# CREATE TABLE tasks (id SERIAL PRIMARY KEY, description VARCHAR, completed BOOLEAN, categoryid INTEGER);


# CREATE DATABASE todolist_test WITH TEMPLATE todolist;

# \q

That last create database line there is creating a test database modeled exactly on the current schema of the todolist database. You'll use this test database in your Dao test files, whereas you'll use the actual todolist database in the App.java file. That way you can clear the test database after each test, while allowing data to persist in the development database.

This works fine, but we can make this easier by putting those commands into our create.sql file and then just telling psql to run that file. (Note that I deleted the old create.sql file from src/main/resources/db, since we won't be needing it anymore, and I created a new one in the project root directory, though it doesn't really matter where you put it.)

\c todolist;
CREATE TABLE tasks (id SERIAL PRIMARY KEY, description VARCHAR, completed BOOLEAN, categoryid INTEGER);
CREATE DATABASE todolist_test WITH TEMPLATE todolist;

I'm also creating a sql file to drop the database:

DROP DATABASE todolist_test;

To create our development and test databases, we can run from the command line:

$ psql < create.sql

If you get any errors about the databases already existing, you may need to first clear them by running our drop.sql file through psql:

$ psql < drop.sql

Great. Now we have two databases (todolist & todolisttest). It's important to remember that these databases are independent of each other, and changes to the schema of one of them does not affect the other. If we add, drop, or delete a column or table, we need to either repeat the same action in the _other database, or we need to drop the test database and recreate it. Don't allow your database schemata to get out of sync, as this can cause hard to debug errors.

Adding dependencies

First, let's add the postgresql dependency to the dependencies section of our build.gradle (and remove the h2 dependency). See comments on the lines below to choose which postgres driver. You can check which version of postgres you're using by typing postgres --version from the command prompt.

    compile group: 'com.h2database', name: 'h2', version: '1.4.191' // REMOVE ME
    compile group: 'postgresql', name: 'postgresql', version: '9.0-801.jdbc4' // ADD ME FOR POSTGRES VERSION 9.x
    compile group: 'org.postgresql', name: 'postgresql', version: '42.2.2' // ADD ME FOR POSTGRES VERSION 10.x

Cool. We no longer need H2, but we do need the JDBC driver for postgres (JDBC stands for Java DataBase Connectivity). Make sure you refresh gradle after you add this lines so that the dependencies are available to us.

Switching over our code

Let's pop over to a test file and make the appropriate changes. We'll need to change our test annotations around a bit, as we can no longer rely on our database automatically clearing test data when the connection is closed. Compare your file to the one below, and make sure you catch all of the changes we need to make.

public class Sql2oCategoryDaoTest {
  private static Sql2oCategoryDao categoryDao; //these variables are now static.
  private static Sql2oTaskDao taskDao; //these variables are now static.
  private static Connection conn; //these variables are now static.

  @BeforeClass //changed to @BeforeClass (run once before running any tests in this file)
  public static void setUp() throws Exception { //changed to static
    String connectionString = "jdbc:postgresql://localhost:5432/todolist_test"; // connect to postgres test database
    Sql2o sql2o = new Sql2o(connectionString, null, null); // changed user and pass to null
    categoryDao = new Sql2oCategoryDao(sql2o);
    taskDao = new Sql2oTaskDao(sql2o);
    conn = sql2o.open(); // open connection once before this test file is run

  @After // run after every test
  public void tearDown() throws Exception { //I have changed
    System.out.println("clearing database");
    categoryDao.clearAllCategories(); // clear all categories after every test
    taskDao.clearAllTasks(); // clear all tasks after every test

  @AfterClass // changed to @AfterClass (run once after all tests in this file completed)
  public static void shutDown() throws Exception { //changed to static and shutDown
    conn.close(); // close connection once after this entire test file is finished
    System.out.println("connection closed");

OK, let's pause here for a minute and digest.

Firstly, some of the variables we need for our test are now static. Why? Well - because we are no longer creating a database on the fly, we have to make sure that certain actions happen @BeforeClass - before the tests begin, @After each test (clearing the database), and @AfterClass - after we have run all of our tests, which is when we'll close the connection. We don't want to open and close the connection after each test when we are working with an actual database - that is too resource intensive.

Because we are running functionality outside of a class, we need to change certain methods to static ones, which requires, in turn, that we change certain variables to static ones as well.

We also need to change some information with regards to how we connect to the database. (If you are working on a windows machine, you may have a username and password set up. Enter them here by replacing the empty strings).

Testing our connection

Let's try running our tests now to see what happens.

If you receive a connection error, check your database name (did you make a test db? is it the right schema?), make sure you added the JDBC driver to the build.gradle, and finally check that your connection string is correct.

If you see assertion errors (some tests passing, some failing), be sure that you are clearing all categories and tasks after each test (in the @After section). You can troubleshoot this by actually going into psql and looking at your todolist_test database. For example:

# \c todolist_test
# SELECT * FROM categories;
# \q

You'll need to do this same process for each Dao test file. Before continuing, ensure that all your tests are passing.

Connecting our App.java file to postgres

Sweet, we are nearly finished with changing our app from H2 to Postgres. Let's change our App.java file, write some data, and log in via psql to check it.

In your App.java, simply change the following:


String connectionString = "jdbc:h2:~/todolist.db;INIT=RUNSCRIPT from 'classpath:db/create.sql'";
Sql2o sql2o = new Sql2o(connectionString, "", "");


String connectionString = "jdbc:postgresql://localhost:5432/todolist"; //connect to todolist, not todolist_test!
Sql2o sql2o = new Sql2o(connectionString, null, null);

So easy. Now run your app. It should work just as it did before. Go ahead and write some records - either via your frontend or by firing off some requests with Postman.

Then, log in to psql to peek at your data:

epicodus_staff=# \c todolist

todolist=# SELECT * FROM categories;
 id |  name  
  1 | school
  2 | home

todolist=# SELECT * FROM tasks;
 id | description  | completed | categoryid
  1 | drink coffee |           |          1
  2 | write code   |           |          1
  3 | sleep        |           |          2

todolist=# \q

There it is! Our data is getting stored in the DB. Yay!

Example GitHub Repo at this stage