Now it's time for an exercise.
And this exercise has a starting file in the current lesson which is called 8.3 Travel Tracker dot zip.
Go ahead and unzip it and then use npm install to install all of the required modules.
And what we're going to be building is essentially a website that allows us to track all the countries
that we've been to.
If you want to know how many countries you've been to and which countries they are, you can get these
scratchable world maps that you can put onto your wall where you scratch off each country as you visit
them.
But in our case, we're going to use technology to build this.
This is an app where we can enter a country that we've been to.
And the great thing about this app is it's backed by our powerful Postgres database.
We can enter some data into our database, namely some countries.
And our front end will know which countries those are and display them in this beautiful way of visualizing
all the countries we've been to.
Once you're ready, let's get started.
I want you to head into VS code and again, making sure you've extracted the files, you've installed
all the node modules, and do all of that prep work that we always have to do every single time.
And now if you take a look inside the index.js, you'll see that I've got a very basic backbone of an
express app for you.
Now I'm going to delete that table I already made, and we're going to do it all together, because
this one is a little bit more interesting.
Let's go into our query tool and do our usual create table.
And I want you to follow along with me.
This table is going to be called Visited Countries.
And it's only going to have two columns.
So the first one is simple.
It's ID, it's serial and it's primary key.
Now the second one we have to think a little bit more deeply about.
We're going to create a column called Country Code to represent the code of the countries that we're
going to store in this table, representing the countries that we've already been to.
Firstly, we are going to store it as a char two because it is going to store the two character ISO
country codes.
But more importantly than that, we need to make sure that this field is never empty.
We don't end up adding data into this table that is empty, where we just end up with a primary key
or something like that.
So we need to make sure that it's not null, so it cannot accept null as a input and it will reject
it.
That's quite an important bit of mitigating errors in the future.
The other thing that we want to make sure is that we're only putting in unique values into this field,
because we don't want to log a country that we've been to twice.
It will just add to the number of countries we've been to erroneously if we start double counting.
So we want to make sure that our data is sanitized.
And these two are some of the most common modifiers to your Postgres database columns that you will
see.
So not null and unique.
And now that we've added that, we can go ahead and hit run to create this table.
Now that we've created the table, we're actually going to manually add some data into this table.
In the next lesson, I'm going to show you how we're going to do this from our Node Express backend.
But for now, because we want to do the first part of the application where we just display the countries
we've been to, we're going to get started by adding some data manually using Pgadmin.
To do that, you click on this button to add a row, and the first field we're going to leave empty.
We're going to let it use the default value.
And the second value is something that we have to add in because we said it should be not null.
And you can see in gray what Pgadmin is trying to show you is what will be added as data.
If you don't do anything, if you don't change any of these fields, and if you try and hit save using
this button, you'll see that it's not going to accept it.
And that's because country code does not accept null as a value.
So we have to put something in here.
So let's put in a country code.
For example let's put in fr.
Double click on that field and put in fr.
Fr is the two country code for France.
And go ahead and hit okay.
If you want to refer to the full table of two letter codes, you can go into your database and view
it right here.
You can scroll through this list of countries.
And you can find one or 2 or 3 countries that you've been to.
And just write down their country codes.
For example, I'm going to add fr for France, which I have been to, and GB for the UK, which is where
I live.
And finally I'm going to put in US for the United States of America.
Those are the three pieces of data I'm going to add to my visited countries database manually.
And you'll notice when I refresh this, the data that I added previously is gone.
Because unless you press the save button, it's not going to be persisted.
So let's do that again.
Let's add fr and let's go ahead and add another one which is GB.
And finally I'm going to add us.
All right.
So I've got my three rows of data.
And I'm going to click on the save data changes.
So now that is added to my database.
And you can see that the IDs get automatically generated because the IDs have to be unique.
So if there was any previous data, for example, when I created my first piece of data that was null,
that got rejected, it already hogged the first ID placeholder.
So it doesn't really matter as long as the ID is unique.
And this is a really important way that the primary key ensures that each record has its own unique
ID.
Now that I've created my table manually and hopefully you have done too.
We've now got some data in this visited countries table and we can now use it inside our application.
Now is kind of the challenging part of this whole exercise taking what's inside index.js.
I want you to be able to make this application display the countries that you just added, and you can
check how this should work by firstly, of course, making sure that you've installed all the node modules,
but then next using Nodemon to run the solution one dot js.
So now if we go over to localhost 3000, you should see the countries that I added just now manually
show up on our map.
This is the US, the UK and France showing up here.
In your case it should do the same.
Now you're going to think through how to do this.
And the first thing to look through is of course the index.js to see how the data in here is structured.
So you can collapse this section that says AG Canvas, because that's our entire world map, and it
is massive, and it's going to make it hard for you to scroll around in the code.
Ignoring that, you can see that I've got some code already written for you inside the script tag.
The first thing we do is we get hold of a value in ejs called countries, which is going to be an array
of country codes.
And then we split each of the country codes by a comma.
This way we can get hold of each of the country codes.
And this line of code basically creates an array inside this script that looks something like this.
So become FR and then GB and then us like so.
And once we've gotten hold of that we can loop through it using the for each.
And then this line of code gets hold of the element by ID with the code for each of the codes in this
array, and gives it a fill of teal color.
If we expand this world map, you can see each of the countries have their own unique ID, and the path
is an SVG, which is just a shape defined in a particular format to represent the shape on the page.
Essentially, this entire world map is made up of every single country drawn on the screen, and we
can identify them by their ISO country codes, and we can change the background color of each of those
pieces by using this line of code.
Now a couple of things to note, which is here.
Why are we doing this?
Why are we splitting the countries that we get hold of.
Because we're already getting hold of an array.
Why do we need to do this?
Well, the key is the console log that I've included in here.
And you can run a type of the country codes that you generate from this.
But you can also do a type of the countries that gets passed in here.
Like this.
And what you'll find is that when Ejs does templating, it's inserting a template string.
So you're getting a string here and not a JavaScript code or JavaScript array as we would expect.
So we're treating it as a string.
And there is a string method called split, which allows us to split each of the items by the comma.
And we know that arrays are formatted using commas.
And then we can end up with our array in JavaScript again so we can recreate it essentially.
Have a think about this part which might trip you up.
And the other thing I want you to think about is think about logging what you're getting back from Postgres.
This challenge is, I have to admit, quite difficult, but we're kind of at the last pages now of the
course.
And hopefully you've realized that a lot of programming is just about persevering through difficulties.
And the harder the challenge, the more reward you'll feel at the end.
I'm just giving you a little bit of heads up.
You might have to spend a little bit of time thinking about this and being stuck, but it is normal.
And ask any programmer.
This is an entirely necessary and if not essential, part of our growth as software developers.
Be patient with yourself.
Don't beat yourself up too hard.
Everyone trips up and see if you can figure out how to write the code in the index.js, so that it's
able to pull up the country codes from your visited countries table, and then pass that data over to
the index.js and render each of the identified country codes to change its fill color.
Now, if you haven't worked with Postgres for a little while or you're just coming back to this lesson,
this is just a quick slide reminding you of how to get set up using Postgres in your node back end,
so you can pause on this video while you work on the exercise, or completely ignore it.
If you want to do everything from scratch yourself, pause the video and give that a go.
All right.
My wish is that you've tried and failed and tried again and failed again.
And at the end you managed to get it to work, or at least get it to partially work through logging
and making sure that you've got some parts of what you're trying to achieve.
So now we're going to walk through the solution together, and we're going to go through it step by
step.
The first part of working with Postgres is importing the Postgres module, which should be already installed
in your node modules, because I had included it in the starting file.
If you are working with a project from scratch, then all you have to do is to run npm I and then the
module is called pg and all you have to do is to write mpm, I pg and you would install that module.
Once we've done that, then we're going to set up the configuration for our database.
This is code that we've used before.
You can either copy it from a previous project that you got, or look up the documentation on npm to
see how this would work.
I recommend just copying your previous code and just doing a sense check.
That's probably the easiest way.
So in my case, the username I use to access that particular database is Postgres.
The host is localhost and it's running on port 5432 which is the standard Postgres port.
The database name that I have is world, and it should be the same for you.
And the password that I've set to access this database is just 123456.
Yours might be different.
Once we've configured our database, then all we have to do is run Dbconnect in order to establish an
active connection with our database.
The next stage is to create an App.get.
And this is going to give us access to the homepage when we go to localhost 3000.
Now here what you have is a query.
And what we need to first establish is to query our database for the countries that we visited.
We're going to run db dot query.
And then we can insert the SQL query that is going to get us that entire column.
So remember how we use the select column from table.
Well this is essentially that it select column.
We insert the name of the column we want and then the name of the table we want just like this.
And then once we get hold of the result it's a good idea to console log result rows just so you can
see what the structure of the data looks like.
And it actually looks like this.
It's an array with lots of different JavaScript objects, each object has a key which is set as the
country code, and then it has a value, which is the code that actually exists in the record, because
we have lots of these values in the array.
Then in order to get hold of each of these values, what we need to do is to run a for each.
I created a empty array called countries, and I loop through each of the rows in our results.
And for each of the countries or each of the records in there, I'm accessing the dot country code.
Remember, if we type in dot and then the key, we get hold of the value which is what we want.
And then we push each of these values into this empty array.
So then at this point if you console log countries should look like this.
It's an array of the various country codes.
In my case it will be GB, fr and then finally us.
Like this.
I'm not sure that's the right order, but this is essentially the structure of our data.
And then we send this over to our index.js.
And we also send over the countries dot length.
So count how many values we have in there so that we can use it in our index.js.
And then finally we close our connection to the database.
Now let's take a look inside the index.js, and you'll see that we take the countries that gets passed
over, and we go ahead and split the string by the comma.
So we had fr com.
A US comma GB.
Again, I've got a different order, but this part doesn't matter for us.
We split the string by the comma, and this method basically creates a new array in JavaScript called
country codes.
Now we can loop through country codes.
And for each of the codes we try to grab hold of the element that has that ID.
And we change its fill to a different color.
And also that other value that we sent over the total gets added into this H2 and displayed on screen.
That is the whole process of how we end up with this website.
Remember, none of these things here work yet because we don't have any ways of catching the post request,
which we're going to do in the next lesson, but have a review of the code inside solution one and check
against your own code to figure out if there's anything you need to figure out, if there's anything
you need to fix, or if you have done it in a completely different way and it still works, then kudos
to you.
It doesn't have to look the same as my code as long as it works.
That's kind of all I care about at this stage.
There's multiple ways of achieving the same thing, but once you're done, head over to the next lesson
and we're going to continue building on this app.