So let's make sure
we do fetch the author data for populating this drop down.
And of course we need to fetch that author data
in this new post route,
because here we render this create-post template
and we need the author data in that template.
So now we'll have to do a couple of things.
But first of all, we have to connect to the database,
then we have to run a query.
Then we have to send the results of that query the offers
into this template.
And then in the template,
we have to pick up that author data
to display the data there.
So a couple of steps.
Now let's start with connecting to the database.
In order to connect, we typically use a third party package
because we don't wanna handle
all that technical nitty-gritty details
of establishing a connection ourselves.
Instead, we want to use a package
that deals with the connection for us
and that then just allows us to run queries
against that connection.
Therefore, since we are using MySQL,
you can search for node mysql
to find packages that help you with that.
And you will most likely find this MySQL package,
which is great,
but actually not the package I will use here.
Instead, not a package you find on page one here,
but a great package is node mysql2.
If you search for node mysql2,
you will find this MySQL 2 package.
Now don't get confused, it's not a package
that deals with a different version of MySQL,
we are still using MySQL version eight here.
At least I am when I'm recording this video,
instead, it's just a different package
which builds up on the other node MySQL package,
I showed you a couple of seconds ago,
but enhances its functionality a little bit.
That's why we'll use this package
and we simply install it by running npm install mysql2.
Maybe with the --save flag, but that is optional.
So you can copy this command.
And then here, quit does running development server
by clicking into this terminal and pressing Ctrl + C
and then here, simply run npm install --save, mysql2.
And this we'll now install this package into this project
and that's a package
that will help us with establishing a connection
and therefore now that this was done,
we can again run npm start again
to start this development server again.
Now, when it comes to establishing this connection,
I now don't wanna repeat to code
for a setting up such a connection over and over again.
And therefore,
instead of establishing the connection here, when I need it,
I will create a new folder in this project,
which will name data.
And in there, I'll add a database.js file.
Both those names are up to you,
but they accurately describe what will happen in there.
I'll store some code in this database.js file
that establishes a connection to my database.
Because in this database.js file,
we can now import mysql2 like this
and store it in a constant called mysql
or whatever you wanna call it.
And then mysql, this imported package here
gives us create connection function to create a connection.
However, even better than that,
it gives us a create pool function,
which creates a pool of connections,
which doesn't matter too much here in local development
and for simple websites
but if you have bigger websites
where you might be handling a lot of concurrent requests,
so a lot of requests reaching your server at the same time,
then such a pool of connections is more efficient
than creating individual connections all the time.
Hence we'll call create pool here to connect to the database
and let this package actually behind the scenes
establish a pool of connections
that will then be used and managed automatically
by that package.
Now createPool, just like create connection
needs a JavaScript object as a parameter value.
And in this object, we simply tell the package
how to connect to our database.
For example, we need to add a host key
and this must be named host
because the package will look for this key.
And that is a string with the URL off our database server.
Now here, the database server
is running on our local machine
and therefore the special URL is local host.
So what we also used for visiting our website.
The database is running on our computer as well,
so the host is always a local host.
It will just use a different port under the hood
because the database server
is typically running on port 3306.
So now we set up the host,
the port is configured automatically.
The next thing we need to define here
is the database we wanna connect to
because keep in mind,
we are connecting to a database server here
on a server we can have multiple databases.
We can see this in MySQL workbench as well.
I got the block database here and the default sys database.
And if you still have
that restaurant's database from before,
you would have that as well.
So here I wanna connect to the block database.
So we pass this as a string for the database key.
The next thing we need to add is the user we wanna use.
And that by default is a route
that was that default user that was created automatically
when you created your database before.
The last thing for this package
to successfully establish a connection is then the password.
And here, of course, you should enter the password
that you defined for creating that database.
So I'll do that here as well.
Now with that, this will return us a pool object,
and it's now this pool object, which I will export here
so that we can then import this pool into all the files
where we wanna run a query against that database.
So where we wanna use that pool
to send the query to the database
and the MySQL 2 package
will then automatically use this connection,
or one of the connections in the pool
sent the query to the database
and manage all of that for us, which is very convenient.
Therefore, we can now save that file and close it.
And then back in block.js,
we can now use that database pool.
So here at the top, I'm going to import it
and store it in a constant named db.
And I'll import from going up one level /data/database
and store my pool in this db constant.
And here where we then need all the authors,
we can now use db and then use the query method.
The built in query method
to run a query with help of that connection pool
and send that query to our database.
Now here query as the name suggests
wants a SQL query statement that should be executed
and that's passed in as a string.
And here I wanna get all my authors.
So therefore I'll select everything from authors,
and this will give me all my data from that authors table.
And that's of course the same kind of query
you saw before already
early in the course in the last course section
where we ran queries like this in MySQL workbench.
Now we're telling node.js to send this as a query
with help of the MySQL 2 package to our database
so that this query is executed in our node.js application.
Now, the result of that query,
of course, should be the data that's fetched,
but as you can probably imagine
sending such a query to a database server,
executing the query on the server
and then returning the result,
can take a couple of milliseconds or seconds.
Of course, since it's all happening
on the same machine here, it will be super quick,
but if this would be running on a real machine
on a real remote host, then the database server
might be running on a different machine
and then we might have some delay.
And even on the same machine, there is a very small delay,
maybe two or three milliseconds or anything like that.
And therefore this does not finish instantly.
Instead, this is an asynchronous operation
and we talked about the those
in the advanced JavaScript section a couple of hours ago.
So definitely go through that section again,
in case this doesn't tell you anything.
There, you also learned about a couple of strategies
for dealing with asynchronous operations.
And one of these strategies or concepts was to use promises.
The great thing is that MySQL 2 supports promises.
We just have to go to database.js again
and in there instead of requiring mysql2 tool like this,
require mysql2/promise and then save this again.
This will under the hood,
make sure that all the query methods we execute
will yield promises to us,
which we can then listen to basically
to eventually get our result.
Now we can close that file again,
after saving it again and back in block.js,
db query now will actually return a promise
so we can use then and catch as you learned it before,
but even more convenient than then and catch,
is to use async await
and nerve a concept I discussed
in this advanced JavaScript section.
We use it by adding async in front of the function here,
adding this keyword in front of our anonymous function,
and then adding a wait
in front of the asynchronous operation
that yields a promise.
With data it looks like synchronous code again,
that executes step-by-step.
But as you learned before,
this just is transformed behind the scenes so to say
that we automatically listen for this promise to resolve,
and this code is an executed after the promise resolved.
Now we get the result of that simply as a return value,
and hence we can store it in a constant
that we could name result.
And the result we do get back when we execute query
will actually always be an array
where the first item is itself an array
with all the records that were fetched
and the second item holds some metadata.
Now I'm not interested in that metadata,
I'm not interested in that second array item,
but I'm very interested in the first array item,
which is stat array of data that was fetched.
Hence, I'll use array de-structuring here
and nerve a concept covered
in this advanced JavaScript section.
So now you can probably see why I had this section there.
And with that,
we can pull out the first element of that array
that this promise resolves to in the end
and give it any name of our choice,
like for example, authors, but the name is up to you,
but it will be an array full of authors.
So this sounds like a fitting name.
And then here, for create-post for this template
where we need the authors data,
we can now pass data into this template,
maybe called authors and point at our authors
we're getting here.
So I got authors twice here,
but that here is the key
that will be exposed in the template.
Whereas this here
is simply my destructured authors array item.
And again, this syntax here,
that is called the de-structuring
and we're simply pulling out the first item of an array
and restoring it in a new constant called authors.
That's what's happening here.
And with that, we're passing this authors array
under an authors key queue to create-post template,
and hence in that template, we should now be able to use it.
If you go to create-post.ejs,
now here in this dropdown,
we should now be able to actually display
a list of the offers we fetched from the database.
So here we can now use EJS to create a for-loop,
as we learned it in one of the earlier course sections
and loop through all the authors.
So author of authors going through all the authors
in this authors array.
And yes, that's a lot of authors I'm aware of that.
And of course also close stat with this syntax,
and then between those tacks,
we can now render our option, HTML elements.
That's what you'll learn in the forums course section
that does select input element
has an option element or multiple option elements
inside of it,
which are the options that are shown in the dropdown.
And then every option can have a human readable text
and optionally also internally stored value
that would be submitted when the form is submitted.
And we're going to utilize both.
Let's start by outputting some human readable text here
by using this EJS templates tag and saying author.name here,
displaying the name of the author.
Then in addition on the option element,
I will add the value attribute
and the value for that attribute
will also be output with help of EJS.
This will not be visible to the visitor of our website,
but it will be the value that is submitted to the server
when an option is chosen and the form is submitted
and here I wanna have the id of the author.
Because on the backend,
I'll hover work with the idea of the author than the name,
because I know that the ID is unique
because that's how we configured our tables.
The ID is our primary key there,
and it's guaranteed to be unique.
And with all of that, if we save all these files
and we now go back and reload this page,
we should have a dropdown where we can see our authors.
If this is not working, if you're getting some error,
make sure your database credentials in database.js
are correct.
Also use one of my code snapshots,
which you find in the last lecture of every module
to compare your coach to mine
and make sure you don't have any error
somewhere here in the template or here in your query.
And again, double check the credentials
which you use for connecting.
Make sure you have no firewall in the way
or anything like that,
and then it should work
and you should get a result similar to mine.