Exercise Monday

Now that we have decided what our data model is going to look like, we need to create a database to begin implementing functionality and testing it.

Creating our SQL File

If you recall, in To Do List we had a simple SQL file containing the information needed to create a Postgres database for our app.

Let's create a new folder called DB in resources, and a create.sql file within it, just like before. (Be sure to not create it in test). It should look like this:

src/main/resources/db/create.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 IF NOT EXISTS foodtypes (
 id int PRIMARY KEY auto_increment,
 name VARCHAR
);

CREATE TABLE IF NOT EXISTS reviews (
 id int PRIMARY KEY auto_increment,
 writtenby VARCHAR,
 content VARCHAR,
 rating VARCHAR,
 restaurantid INTEGER
);

Nice. Now we have a place to store all our data when it comes time to test. We just need to instruct H2 to create a database for us with these tables. We will be adding one more table later this week, but for now, this is sufficient.


Example GitHub Repo for Jadle Code at this stage