Lesson Thursday

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

Creating our database structure works quite differently that it does with H2. Whereby previously we could rely on our create.sql file to run our table setup for us, we have to do this manually for postgres. The syntax is similar, but not the same.

First, log into psql in bash:

psql (9.5.4)
Type "help" for help.


Execute this series of commands. These are commands for the Jadle project - adapt them for your application as you see fit.

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

Now we are ready to create our tables. See below how we have to change our syntax:

Before: ```sql SET MODE PostgreSQL;

CREATE TABLE IF NOT EXISTS restaurants ( id int PRIMARY KEY auto_increment, name VARCHAR, address VARCHAR, zipcode VARCHAR, phone VARCHAR, website VARCHAR, email VARCHAR ); ```


# CREATE TABLE restaurants(id SERIAL PRIMARY KEY, name VARCHAR, address VARCHAR, zipcode VARCHAR, phone VARCHAR, website VARCHAR, email VARCHAR);

Note that the syntax for creating an auto_increment id column has changed.

Go ahead and create all of the tables you need for your application using the blueprint above. Don't forget your join table(s)! Once you think you are complete, check your table schema of each table carefully with

\d restaurants
Table "public.restaurants"
Column  |       Type        |                        Modifiers                         
id      | integer           | not null default nextval('restaurants_id_seq'::regclass)
name    | character varying |
address | character varying |
zipcode | character varying |
phone   | character varying |
website | character varying |
email   | character varying |
"restaurants_pkey" PRIMARY KEY, btree (id)

It is tricker to make changes to your schema in psql than with our create.sql file, so double checking can save you a lot of time later.

Once you are sure your column types, names, and overall schema are correct, we need to manually create our testing database. We will stick with our pattern of writing test data into its own database, so that it does not interfere with our production build.

It's easy to create a second database with psql:

# CREATE database jadle_test WITH TEMPLATE jadle;

Great. Now we have two databases. 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. Differently than with the create.sql file, 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.

Now, you can safely delete your create.sql file.

Adding dependencies

First, let's add any dependencies we need to our build.gradle:

dependencies {
    testCompile group: 'junit', name: 'junit', version: '4.12'
    compile "com.sparkjava:spark-core:2.6.0"
    compile "com.sparkjava:spark-template-handlebars:2.5.5"
    compile 'org.slf4j:slf4j-simple:1.7.21'
    compile 'org.sql2o:sql2o:1.5.4'
    compile 'com.google.code.gson:gson:2.8.2'
    compile group: 'com.h2database', name: 'h2', version: '1.4.191' //remove me!
    compile group: 'postgresql', name: 'postgresql', version: '9.0-801.jdbc4' //add me!

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 these 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 Sql2oRestaurantDaoTest {

private static Connection conn; //these variables are now static.
private static Sql2oRestaurantDao restaurantDao;
private static Sql2oFoodtypeDao foodtypeDao;
private static Sql2oReviewDao reviewDao;

@BeforeClass //I have changed from @Before
public static void setUp() throws Exception { //changed to static
    String connectionString = "jdbc:postgresql://localhost:5432/jadle_test"; //!
    Sql2o sql2o = new Sql2o(connectionString, null, null); //!
    restaurantDao = new Sql2oRestaurantDao(sql2o);
    foodtypeDao = new Sql2oFoodtypeDao(sql2o);
    reviewDao = new Sql2oReviewDao(sql2o);
    conn = sql2o.open();

public void tearDown() throws Exception {  //I have changed
  //  restaurantDao.clearAll(); //coming soon
    System.out.println("clearing database");

public static void shutDown() throws Exception{ //changed to static
    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 will pass, some will fail) Great! We are connecting to our DB Don't worry about the failing tests, that's actually fine. This is the result of test data no longer getting cleared between tests. Let's add a clearAll() method we can call to empty our database in between tests, just like closing the connection would with our H2 setup.


public void clearAll() {
    String sql = "DELETE from restaurants";
    try (Connection con = sql2o.open()) {
    } catch (Sql2oException ex){

We also need to mention this in our RestaurantDao as well, so don't forget to add it: void clearAll(); It would be a great idea to write a test for this method as well.

Cool. Let's call that in our @After annotation so that it runs after every test. Uncomment the line referring to this method, and run your tests again.

Adjustments and fixes

If you see a Ssl2oException whenever your add() method runs like this:

org.sql2o.Sql2oException: Keys where not fetched from database. Please call executeUpdate(true) to fetch keys

Don't fret. There is a simple fix. Go to the add()` method of your respective class, and make the following change:


public void add(Restaurant restaurant) {
    String sql = "INSERT INTO restaurants (name, address, zipcode, phone, website, email) VALUES (:name, :address, :zipcode, :phone, :website, :email)";
    try (Connection con = sql2o.open()) {
        int id = (int) con.createQuery(sql, true) //I have changed!
    } catch (Sql2oException ex) {

This method did not cause an error on our H2 setup without this extra parameter, but it DOES on postgres. Newer editions of the curriculum and example repo have been changed to reflect this issue.

You may still have some tests that don't pass. Go ahead and implement all of the steps necessary for your other classes, and correct any failing tests before you move on. You'll need to do all the steps for all the classes.

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:

Before: java String connectionString = "jdbc:h2:~/jadle.db;INIT=RUNSCRIPT from 'classpath:db/create.sql'"; Sql2o sql2o = new Sql2o(connectionString, "", ""); After: java String connectionString = "jdbc:postgresql://localhost:5432/jadle"; //do NOT make the mistake of connecting to your test DB! 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:

# SELECT * FROM restaurants;
id |    name    |       address       | zipcode |    phone     |         website          |          email           
 1 | Don's Dogs | 6000 N Ainsworth St | 97232   | 503-402-9874 | http://www.fishwitch.com | [email protected]

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

Now there is only one remaining step - it's a good idea for us to dump our schema so that we can more easily create our database on a different db server.

See the next lesson on how to do that. There is a sql file included in the example repo you can play with if you like. Congrats!

Example GitHub Repo for Jadle at this stage