Now that we've created our tables and we've imported some data into there, or you could have created
some data fields of your own, it's now time to see how we can actually use that data in a real node
backend.
So we can power a project using data from our database.
The project we're going to be building as an example is a capitals quiz.
I don't know how good you are on your geography, but mine is pretty terrible.
But nonetheless, it's a quiz where you get a prompt for a country and you have to enter the name of
the capital.
So we're going to normalize the casing.
We're going to get rid of any blank characters they add in there.
But essentially we're checking whatever the user enters against what's in our database.
And we know that we have the names of the countries and their corresponding capitals.
And if the user gets it right, then they get to increase their score.
And up until the point where they get one question wrong and then that's their maximum score.
I'm not very good at this.
My max score so far in the testing and playthrough of this has been three, which is pretty abysmal,
but you let me know once you've built it if you get a lot higher of a score.
Essentially, this is what building.
Very simple, but it's going to be powered by our Postgres database to get all of that data, to get
a hold of all of those records, the line of code we need to read from our SQL database is the simply
this select all.
So that's what the asterisks does from the name of our table.
The SQL code is very simple and you've done this before.
You know exactly what it does.
But in order to implement it in our node and express backend, it takes a few more steps.
We saw this when we first introduced you to Postgres, but now that we know a little bit more about
Postgres, let's analyze this again so that we familiarize ourselves with how to work with Postgres
and the pg npm package.
In order to add a database to our backend, we're going to use this PG package, which simplifies things
down for us vastly.
And once we've installed it and imported it, then we can use it to create a new PG client.
So this is basically connecting to our database.
When we create this we have to add some configuration details such as what is the username which as
you remember defaults to Postgres.
Hopefully you haven't changed that and you should have the same username, the host, because we are
hosting our database on our computer that will be set by default as local host.
So also nothing to change there.
The name of our database, which if you remember I asked you to call it world which contains our two
tables, flags and capitals, and the password which corresponds to our username.
This is the password that we set up when we installed Postgres.
And you might remember I asked you to write it down because it's used quite a lot, such as in this
case to connect to our database.
We obviously wouldn't want some random person to connect to our database.
So that's why you set up a password when your database is locally hosted, such as in our case, it
doesn't really matter because obviously nobody else can access your computer at the same time trying
to access your database.
But once this database goes remote, goes on to the internet, then these kind of things and set up
becomes a little bit more important.
The final one is the port number.
And by default Postgres will run on port 5432.
And there is nowhere where we have changed that.
So yours will also be hopefully 5432.
Once we've created all of those configuration details, then we can use DB Dot connect to connect to
our world database.
You might remember the world database has lots of tables.
That's why when we run our SQL query using db dot query, we're going to run the code.
Select all from the capitals table.
That is going to be the table where we're going to be getting the data for our current project.
And then we have a callback.
If there's an error console log the error.
If there are no errors we're going to save the result dot rows.
So all of the data that comes from this line of SQL query into the quiz variable, so that we can use
it in our quiz application.
And then finally, at the very end, we close our connection to the database.
And this is good practice so that you're not connected to it and you don't accidentally do something
else or insert or delete your data so that you are closing off your session.
And that is all there is to it to connect to our database, to get, hold and run the query that we
want to run.
And then finally to close the connection.
Let's go ahead and do this in practice.
In the current lesson you'll see a zip file called World Capital Quiz.
And if you go ahead and download and extract that onto your computer and open it inside VS code, you'll
see all of these files that I've lovingly prepared for you ahead of time.
As always, first things first, it is to navigate to the file path and to install all the node modules
by running npm install.
So you end up with this folder node modules.
And once we've done that, take a look inside index.js.
This is effectively our JS file that defines this very simple quiz application.
And we've got our main.css and the background image hosted in the public folder.
So if you look inside index.js you'll see that I've got the middleware where I say use express dot static
defining the static folder as the public folder.
So then we can link to it in our HTML and other parts of our code.
I've already built the basic application, the back end and the front end for you, and I recommend
simply to use this because we're going to be building in all of the relevant parts of this module,
which is the Postgres, using the PG module, connecting to our database and reading from our database.
If you want, you can of course create this completely from scratch.
And to do that, all you need to do is run node nodemon solution dot js, hit enter and then go ahead
and open up localhost 3000.
And here you will see the working version of the app using the solution so you can try out the application.
Don't worry if you don't know the name of the capital, you can always google it.
But I think Norway's capital is Oslo, so I'm going to type it in, hit submit.
Hopefully I get it right.
If I do, my total score goes up and you'll see a little check mark.
And until you get to the point where I have no idea and I get it wrong and then hit submit, we get
a pop up game over final best score one, which is pretty sad, but hopefully you can do better than
me.
You can look at this project and create all the back end, front end by yourself, using the assets
that are available to you in the downloaded file.
But as I said, I recommend actually to use the index.js as your starting point because there's a lot
of new things, and it's best that we concentrate on one thing at a time.
But I'm going to walk through this code with you just so that we're all on board as to how it works.
First, we've got our express app running on port 3000.
We have a quiz item that is an array and it contains just three objects.
The objects are essentially the records that are in our database.
Has a country and a capital.
And of course, each being its own individual object means that they are linked to each other.
So we can check the country against the capital.
Now we've got a scorekeeper here called total correct.
And then we've got our middleware to be able to use body parser to get hold of data from the form,
and to use the public so that we can have our static files, our background images, our CSS or working.
If you go over to the index.js, you'll see that I've set up the JS file so that we are putting over
the total score.
We are taking the question dot country to display in that big H one as the question.
And then I've also got the input element to take the user input asking them to enter the capital.
There's some keywords on here Auto-focus autocomplete off, which means that that field is going to
be selected when you first load up, so that you don't have to click on it to type on it, which improves
the user experience just a little bit.
But it's always the small touches that help.
And the other thing that it doesn't do is it doesn't try to auto suggest your past entries, which is
not very helpful in this case.
Those are the settings.
And then it has the name of answer, which is what we're going to try and get hold of when the user
clicks the submit button.
The submit button is going to submit this form to the path of forward slash submit using a Post request,
and we catch that over here in the post request App.post forward slash submit and we can tap into request
dot body dot answer.
In order to get the answer that the user typed in.
You'll notice that I have this keyword trim here.
And basically what this does is if the user types in one, two, three, four and then they added a
space at the end, or they added a space in the beginning, whatever it is, they can't see it.
But of course that is not going to match up with whatever answer we have.
Space one, two, three, four is not equal to one, two, three, four.
So we use trim to get rid of any of those spaces at the beginning.
At the end.
Next what we do is we check to see if the capital that they entered is equal to the capital.
In the current question, this current question object is set to be equal to a random object.
So a random record.
In that quiz array, we use the randomization method to get hold of a random object which has a country
and also has a capital.
And we set this current question to that random country, and we use it both to send the country over
when we render the index.
JJ's.
So we send over the current question here containing that country, and we also use it to check against
the answer right here.
So current question dot capital against the answer that the user entered in the form.
We're using two lowercase in both of these answers so that we can normalize the casing so that we don't
end up with the official answer for France being Paris.
But then the user's answer is Paris and or vice versa.
So this just gets rid of this problem for us, turning them both into lowercase, therefore making it
match.
Next, if the user was correct.
If this is indeed equal, we increase their score, we log their total score, and finally we set the
is correct to true.
And then we go ahead and set up the next question.
Pulling out the next country in capital pair.
And then we render the index.js passing over the new question and also telling the JS file whether if
the previous answer was correct, and also updating the score on the front end.
So I've tried to keep it relatively simple in terms of the back end and front end code.
And at this point, if you have done all of the JS and the back end sections, all of this, there's
nothing new here.
The logic is relatively simple, and I, in fact, really encourage you to go ahead and run this code
as it is if you simply just use Nodemon to run the index.js in the World Capital quiz and go over to
the localhost 3000, you'll see that as you refresh on this website.
You'll see the same question quite a few times, because we're randomizing through just three entries
France, the US and the United Kingdom.
You can test out this very basic version.
So London is correct.
Paris is the capital of France.
And you'll see this occurring asking you for the same thing again and again, which is a lot easier
of a quiz.
But essentially it has all of the basic working components and it should all be functioning.
The step that we're interested in is to actually tap into our powerful database, and to get hold of
all of the entries in there, first, have it explore of your own.
Make sure you understand all of the existing code before we start adding new code, and then come back
and continue with me.
The first thing we're going to do is we're going to stop the server and use npm to install the package
we need, which is pkg.
It's just lowercase p and g, and we are installing it onto the World Capital quiz.
Once we have done that then we can use the pkg package in here.
So we can say import.
And we're going to import pkg from the pkg module just like that.
And once we've imported it we can go ahead and use it.
And to use it we have to first define a new client and configure it.
We're going to write const db equals new pkg dot client.
So pkg comes from that module we just imported.
And it has a class called client.
And we can construct that class by giving an object that contains all of the required properties.
And the only thing that you really have to change is if you changed your name of your database from
world to something else, if you named it something else, then you will need to match it.
Hopefully you named it the same and you can keep it as world.
The password will be the password that you set when you first installed Postgres for your default user,
which is Postgres, and leave the port as 5432, because that is the default port that Postgres uses
to run the database.
Once you've done all of that, then we can go ahead and write db dot connect.
And this is going to start up our connection to our database that we have defined here.
Once we have connected then we need to think about what is the point where we want to make that SQL
query.
Well, it's probably going to be the first time we grab hold of the home page.
And then we need to think about when we want to get hold of the data from our database.
Well, we only really want to run at once when our index.js first starts up, and instead of having
quiz with all of these items, we can replace it with the records from our database.
So we're going to use db dot query to run that SQL code, select all from capitals.
And that's going to give us all the records from our capitals table.
And we're going to use a callback to check if there was an error.
Then console log that error.
Otherwise if there was no error, we're going to set this quiz to equal raise dot rows from this query.
So that's going to give us all of the rows in the capitals table.
And finally we're going to run DB dot end to close off our connection.
Once all of that is done at this point in time on line 34, our quiz is now replaced with an array of
records from our database.
Now that we've done all of that, we can leave the rest of the code as it is because it's all using
that quiz array, which we've just simply updated to use our database data instead of the hard coded
data here.
And we can finally open up our terminal to use Nodemon to run our index.js.
Now, if we head back over to localhost 3000, you'll see that every time we hit refresh that we're
getting a new country name from our giant database containing all of the countries in the world.
And you can sort of cheat at this game a little bit by refreshing to a country that you at least know
the capital of, and then you can continue from there.
That's the only way that I figured out to make this game a little bit easier.
The capital of Iceland is Helsinki.
I think that's how you spell it.
I have no idea.
Let's hit submit.
Oh, and I already got it wrong.
The name of South Korea is Seoul, and let's try that.
Yes.
Finally we got one right.
The capital of Nigeria is not Lagos, but it is a different name and it cannot.
I cannot think of it right now.
Have a go at the game.
Once you've built it using this giant database and feel that success coursing through your veins and
tell me what is your top score that you're able to reach before the game ends?
Now it's not enough to just follow along, of course.
So I have an exercise for you to try out all of that without any of my guidance.
If you go ahead and download the other zip file in this class 8.2 Postgres, read zip and extract that
and open it inside VS code.
Then you'll be able to take a look at a similar but slightly different project where we're going to
use that other table that you imported the flags table to use what you learn in this lesson to make
this quiz app work.
First, again, CD over to the project folder and then use npm install to install all of the required
packages.
And then you'll have your node modules folder and you can use Nodemon to run solution dot js.
So now going back to localhost 3000, you'll see a similar but slightly different quiz.
This time we see a flag and we have to guess the country that that flag corresponds to.
This is maybe even more difficult depending on how much you know about flags, but oh okay.
This one I know I think this is Nepal.
Yes.
Got one and now I'm already stuck.
I have no idea what this is.
This is the quiz that we're building, and we're using that other table that we imported the flags table.
And you'll know that the names of the fields are slightly different here, so be careful.
The name of the country here is called name, and the name of the flag is called flag.
Observe your data.
Make sure that you know what's going on and open up the index.js file to start updating and writing
your code to use your Postgres database.
The flags, table and power this game, so I'm not going to give you any more hints.
I'm going to let you pause the video and try and figure this out on your own.
All right, so let's run through the solution quickly.
First we had to install an import pkg from the Postgres module.
Then we set up our database connection using pretty much the same settings as before because that flag
table is in the same database.
And then we connected to our database and we ran our query to select all from the flags table.
And then I set the quiz to equal the result rows from our flags table.
And I close the connection.
There's a few things going on.
Hopefully you manage to remember to do all of these things.
If not, go back and update your code to fix it.
Make sure it works.
And once you run this code in your index.js, hopefully you will end up with exactly the same game.
I think this game is slightly harder than the previous one.
I'm not sure what you think, but I wonder which spelling for turkey they've got here.
Ah, okay, so it's actually the old way of spelling it.
Yeah.
Let me know, what is your highest score?
Maybe somebody out there is actually a genius when it comes to flags and capitals.
This is one of those things I always obsessed about.
When I was a kid, I felt like it was really important to know the names of all the capitals of all
the countries.
I remember my mum would quiz me for no reason and today I realised with Google this is not a very useful
skill, but it does come in handy when you want to build some games for people to make.
So I hope you had fun with me building these two games and show them off to your family and friends.
Show off any modifications you make, or create a table of your own, and make a quiz of your own.
And be sure to let me know in the Q&A or on Twitter what you have built and what your friends and family
think about it.
So in the next lesson, we're going to dive deeper into Postgres and running databases with our back
end.
So for all of that and more, I'll see you there.