Hey guys, it's time to apply everything that you've learned to build a project.
And this project is called The Penniless Project, and it's a permanent to do list.
And the reason why it's permanent is because we now have all of these Postgres skills, so we can store
all the data that gets entered into our to do list, into our permanent storage using Postgres.
This is what the website is going to look like.
It's going to be fully designed.
It's going to be a list where you can do all of the Crud actions, such as add a new item.
Edit any of the to do list items, and you can even check them off and delete them off your to do list.
So create, read, update, delete all of this being fully persisted.
So even if you refresh the website or restart your server, nothing gets destroyed because it's all
persisted in our Postgres database.
And all of that stuff is going to be stored inside a table called items, where we're going to persist
all of the data that gets entered and updated and deleted by the user.
In addition to testing the Crud methods that you have learned in this module using Postgres commands,
using Pgadmin to view your data, using your knowledge of Node Express and Postgres and the PG package
to work together to build this website, you're going to be putting all of these skills together.
In order to create this project.
Go ahead and download from this current lessons course resources the perma list project zip extract
the file and open it inside VS code.
Now notice that there are already a whole bunch of stuff that has been created for you, such as the
icons such as styling, such as a lot of the JS files including partials.
So remember way back when we learned about JS?
If you need to remind yourself of how these work, then you can always go back.
I know it's been a few modules since we talked about this, but you can always go back in the course
to have a refresh if you need to.
The first thing I want you to take a look is inside index.js.
So here I've got some starting code for you, which is simply going to render an persisted to do list.
And I want you to go ahead and CD over to this project, the Permollis project.
Use npm to install all of the required packages, and then use nodemon to run index.js.
Now if we go to localhost 3000, you can see that we have pretty much the entire project.
But the only difference is instead of persisting the data in a database, you'll notice here it's being
saved into this array of dictionary items, which is how we've done our persistence all of this time
up until now, when we actually know how to work with databases.
Now, with this version of the to do list, you can see that we can add an item such as write essay
goes into the to do list.
But if I go back to my server and I restart the server.
So right now it's running on port 3000.
Let's stop it and restart it.
If I go back here and I now refresh this page, you'll see all of that disappears because the data is
not persisted.
Instead, when we added it to this array and then the server restarted, it resets this array of items
back to the original state.
That has been our issue up until this point.
Nothing is really persisted, and if we ever restart our server, all of the data that's stored in that
Ram gets deleted.
Your job is to go through the index.js file.
Make sure that you understand how everything is working now.
Right now the edit and delete routes don't do anything yet.
They don't function at all, but all of the front end does in fact work.
So explore that in the index.js, see how each of the items are pulled and added in to the form here,
and how we have the edit form that goes to forward slash.
Edit how we have the delete form that goes to forward slash delete and look at the name and values that
you're going to get hold of through body parser in those post requests, and just understand how the
code works first before you get started trying to tackle it.
Notice in the front end so index.js.
I've got some front end JavaScript here.
And this is a handler function that basically has nothing to do with our persistence.
But it does allow me to click on this button.
And when I click on that button, it calls the handler passing in the ID of the row that was clicked.
And then I can use that ID to basically hide the title and the button.
So this is the title and this is.
The edit button.
So those are hidden and replace it with the done and input.
So this is the done button and this is our input.
This used to be a paragraph element.
And now it's become an input element.
And they are actually all four of these things are there.
They're just hidden and shown depending on the state of the press of the button.
The idea is you should be able to press the edit button.
It turns this paragraph tag into an input.
You should be able to edit and change the input, and then click the done button to make that Post request,
including all of the stuff that was updated in that particular input.
This is the input has a name of updated item title, and the value started off as being the original
title of the item, but it ends up being whatever the user typed in at the end.
So explore all of those files.
Try out the index.js and the last thing you need to think about before you get started is to create
a new database.
I've called mine permalinks.
That's the name of the database.
And then I've created a table in here called items.
And items is going to represent each of the items in the to do list.
So it's got a title and it's got a primary key ID.
That's all there is to begin with.
I've set the title as a Varchar 100 so that people can type in up to 100 characters, but you can decide
all of this yourself because after all, it is your project and it is your to do list.
So think about how you want to do this.
But just remember you will need a new database.
You will need a new table, and you'll need to think about what SQL commands you'll need to type to
create the table and the schema for the table.
If you get really stuck, you can look inside queries or SQL.
And I've got all of the SQL commands that you would need to set up this table of items in the schema
that I have chosen, but I recommend instead of doing that, if you get confused, look back at your
previous code and try to figure it out and then just compare afterwards.
Now once you've set up your database, then you can also run the solution JS in order to see the expected
behavior of the final product.
So it should be able to bring in all of the items in the database.
It should allow you to add items to the database, to cross them out to edit them.
And all of this behavior should work as long as you have named everything the same as me.
So in the solution, I have named my database perma list and I've called my table items and the table
has an ID and a title.
If all of these are the same, then the solution code should work for you as well.
I'm going to stop talking now, and I'm going to leave you to practice and apply what you've learned.
So expect to spend anywhere between 1 to 2 hours on this project.
Even though it is quite simple, it is quite involved and it will require a lot more thinking than writing.
So that's always a good thing.
When you spend more time or twice as much time thinking, then you'll have less bugs and everything
will make more sense.
Pause the video and complete this project.
All right, let's go through the solution together.
The first step is the creation of the database.
As you might remember, you can do that by simply going to Postgres, right clicking on databases and
then create a database.
In my case I've called it perma list.
And all you have to do is click save and you would have your database newly minted.
Inside that database we want to create a table called items and it has only two columns.
One is an ID that is going to be serially generated one until whenever whole numbers.
And it's going to be set as the primary key.
So it's going to be the unique aspect that identifies each of the records in this table.
Next we have a column called title which I've set to Varchar 100, allowing the user to type up to 100
characters as their to do list item.
And I've also set it as not null.
Now this is optional, but it does mean that we could have some error checking down the line so we could
prevent a new entry being created if it didn't have a title.
Next, I decided to insert some test data so that it would match up with the data that I have in my
array of items right here.
So we have some IDs, we have some titles, and I've created the two values to match by milk and finish
homework.
So hopefully this is going to get inserted into my database.
And once I pull it in it will match.
And I can replace this array.
Now it doesn't really matter if you do this.
This could be empty and you could insert it from the database, or your database could be empty and
you could add it from your index.js.
Either way, it works.
Once you have run these queries in Postgres on your Pumilus database, then you can view that table
in Pgadmin just to make sure that everything worked as you expect it to.
And you have those two items inserted.
Buy milk, finish homework.
So now we're ready to move on to the next part, which is to do our read in our app.get.
We want to be able to grab hold of the data from our database and then send it over to index.js in a
render.
So let's walk through the solution together.
Before we can access the database, we need to import the Postgres module from npm.
If you did an npm I, it should already be installed because it was defined in the starting package.json.
It was already added in here alongside with body parser, js and express.
In order to connect to that database, we first configure the database.
So PG client and put in your user your host the password and port.
None of this should have changed between your previous projects.
So you might have just copy and pasted it, which is fine.
But the one thing to note is you now are working with a different database.
It's no longer the world database, but instead it's the perma list database.
So if you had trouble with this, it might be because you forgot to update the name of your database
to match it with the one that you created in Pgadmin.
Once we've configured it, we connect to our database and we're ready to start using it in order to
get hold of the data inside.
Again, I'm using a try catch.
I'm doing very basic error handling here in this project.
I'm just console logging it.
But inside the try block I'm going to run the Postgres query to select everything from my table of items,
and I've decided to order it by ID ascending.
This part is of course optional, but if I do take this part out, hit save and go over here and let's
say I change for example the first item here and hit update.
Then that becomes the most updated item and it changes our order of the to do list.
So if we wanted to keep the order that we have, then we can have that order by ID ascending, which
you might have worked out because we already covered how this works, but you might not.
So this might be something to think about adding if you don't have it, but also it's completely optional.
You don't need it.
It's just one of those things that you might have wanted to apply to your project.
Now next, we set this item M's array to be the same as what we get back from the results.
I recommend console logging result dot rows, but if you remember from previous when we run this await
db dot query using the Postgres module, the content that we get back is an array of dictionaries where
each of the columns that we requested comes in as a dictionary of key value pairs.
So console log is your friend.
Don't be afraid to use it.
Once we set the data from our database into this temporary list, then we can work with it and pass
it over to our index.js front end file and render it passing in the list title, which I've called today.
The list items, which are these items that have been updated from the database.
And we are now ready to take a look at how that gets handled in the index dot JS list items gets passed
into this loop, and we loop through each of the items in the list items and use it to create one of
these rows.
You'll notice that each of the rows has a checkbox and that corresponds to this right here.
Next, we have a paragraph element that pulls in the item dot title.
If you remember from our solution dot js, the item has an ID and a title, and we grab hold of this
text and we put it into that paragraph element and it gets displayed right here.
The next part is where we have our hidden inputs, which will appear when we press the edit button,
because we have that handler that I talked to you about earlier.
But what does get displayed is this button that we can click on to edit the row of data.
And that is right here that is going to go through to the update route.
And we'll talk about it once we get there.
But essentially we now have this entire row being rendered and all the data comes from our database.
The next part is our create.
So we've done our read in the Crud.
The next part is how do we add new items into our database?
Using body parser, we get hold of the text that the user type into a input that has a name of new item,
and that is right here.
This is the input that is of type text and it has the name of new item.
So whatever value the user types into this input right here is going to be labeled with that name of
new item.
And when I hit the submit button it submits the form to the forward slash add route.
This is of course the submit button.
So the user types something, presses this button, and then we receive that data on our server side.
So looking at this we've got our forward slash add post route that we handle in our server.
And we grab hold of the text that the user entered into that field.
And then we use it to add to our database.
Previously in the index.js I had simply an items dot push to demo how we could add it into the items
array right here.
But now that we're working with our database, we no longer need to do that.
Instead, we make a DB query and we insert into our items table with the column name title and the value
corresponding to dollar one, which is going to be this right here.
And that is what the user typed in from the input.
Remember the ID is serial, so it's going to be automatically generated.
And the only other column in that table is the title.
So we can simply insert what comes from right here into this.
And that gets created in our database.
And finally, once that is completed and everything went without error, then we proceed to the next
step, which is to redirect to the home route.
If there was an error, then we would catch it and console log it.
What happens on this redirect?
Well, it goes back over to this route right here.
And we've seen this before in previous projects and also coding exercises.
Once we redirect to this location in our server, then it's going to pretend as if the server received
an App.get to the home route.
So it's going to do the same thing.
Pull back all of the new data from our database and render it in the index.js.
Essentially, it just forces a refresh of the page.
Now we've talked about create read, and now we're on to update.
The update happens in the edit route.
The update action is inside this form.
And it's triggered by this button right here.
That is the edit button.
So when I click on one of these buttons, then the JavaScript code right here is going to hide the paragraph
element, hide the edit button and unhide the done button.
And this input the parts of the code that are now hidden is this button right here.
And also this paragraph element right here.
These two are hidden and no longer seen, and they are now replaced by this input which the user can
type into to update the item's title.
And also this done button which will submit this form to the edit route.
What happens when I try to make an update?
So let's change this.
Buy cheese to buy milk.
Notice how even when I let's not submit this.
Let me just refresh this.
When I clicked on this button and this paragraph element was turned into an input element, the value
of that input is the same as what was in the paragraph element.
And the reason for this is because we were able to use the item title that comes from the insertion,
and I put it into the value of that input.
So that means the user doesn't have to type out the whole thing or to completely update it.
They can simply just select it and change it or do whatever they want.
The other part of this you'll see is I'm using the item ID in a number of places, I'm using it in a
hidden input and also the text input.
So a hidden input, which you might remember from previous lessons on HTML ages ago, is not displayed
to the user, so you don't actually see it anywhere.
But what I can do in here is I can add the item ID, which I got by looping through all of the items
so that this ID matches this title, so that I know which of these rows the user is trying to edit.
Because this one probably has an ID of one, this one has an ID of two, this one has an ID of three,
and I pass along the text that the user typed along with this ID so that I can update it on the back
end.
When the user hits this submit, this form goes to forward slash edit and we catch it right here.
Now I'm pulling in the item which is the updated item title that we got from right here.
This is the name of that input.
And the other input is the updated item ID which is that hidden input.
And I now have access to the ID of the item that I need to update.
And the new title I need to update it to.
So I'm ready to start my try catch block and make my SQL query.
So in this case we're using the update method, which we learned about in the previous lesson.
I'm updating the items table and I'm setting the title column to dollar one.
So dollar one is the first item that goes into the array and that is the new.
Text that comes from that input, and then add a condition here where the ID of the record is equal
to dollar two, which is of course this second input right here.
And that corresponds to the ID of the item that needed to be updated.
This entire try catch block is going to try and do just that, updating the record that matches that
criteria.
And once it's done, if it had no errors, again we refresh the page.
If there were errors, we simply just console log it.
So now all we're left with is our last conundrum or our last little challenge, which is when the user
clicks on the delete button, it should delete the item from the database.
That delete input is the checkbox.
And we can see it right here.
This is our input checkbox.
And here in the JavaScript on the front end, I've got an Onchange that listens for changes on the checkbox.
So that means if it was checked, basically if the user clicked on it, this is going to trigger.
And what this does is it's going to submit the form.
And submitting the form means making a Post request to the delete route.
And in this input I've also added a value.
I've got the value set as the item id.
That way I will know on my back end which of these rows the user tried to delete.
Did they try to delete the one with ID one or the one with ID two?
So I click this checkbox.
This triggers the form, passes over the ID of the row that was checked, and all of this data gets
submitted to the delete route.
Right?
So what happens in the delete route?
Well here we first get hold of the ID, the delete item id that comes from the checkbox input.
And then we use that ID to make a database query where we say delete from name of our table.
And our condition is where the ID of the record matches dollar one, which happens to be the ID that
we got from the form.
And after all of that's done, we refresh the page, making sure that the updated information is displayed
to the user.
Any errors we catch and log, that is all there is to it to complete this entire project.
Now, that's not to say that there aren't opportunities for upgrading the project.
Depending on maybe an edge case that you identified, or maybe the to do list doesn't perform the way
you want it to.
You can always change your code to update your preferences.
You now have the power in your hands to make this do whatever it is you want it to do.
And the base model of the project is the simplest form.
All I wanted to know is that you were able to use the Crud methods create, read, update, delete on
this Postgres database so that you can persist data in a actual live project.
And the reason why we're creating a to do list using this type of database persistence is because this
is one of the most common types of coding challenges you get from interviews.
It's something that people love and it's a great project to demonstrate.
Add your interviews as well, but hopefully you will make some updates to this, either styling wise
or functionality wise, to set yourself apart from the millions of other people who have also completed
the same project.
So what are some of the opportunities for upgrade?
Well, here are some of the ideas I thought about.
You can maybe sort your data in your to do list by their creation date.
Maybe you can store an additional column.
You can maybe think about using an alter command to add a new column to your database, store the creation
date through some sort of JavaScript wizardry that you could always look up on Google.
And this means that you have an extra piece of data that you can display to the user on the front end.
Another option I thought about is you could have multiple lists.
Why stop at one to do lists?
You'll notice that the to do list is called today.
And one of my favorite ways of creating to do lists is to create a to do list for today, for this week,
and for this month, because there are some tasks that can't be done in one day.
Write.
Some of them might take a week, some of them might take a month.
I actually normally keep these three types of to do lists on three post-its that I have in my notebook,
and it means that I can check off the items as they are done.
So I'll replace the today one every single day and the week one every single week.
And it's just a really nice overview.
So you can think about doing multiple lists.
Maybe you have a work list, maybe you have a school list.
Maybe you also have the same kind of format I have today, this week, this month.
But you can think about upgrading that and think about how you might start creating relationships that
can enable this.
Speaking of relationships, you could also think about what we did previously, where we had our family
travel tracker, where you could have a family to do lists.
Maybe different members will have different chores that they have to do, and it's a good place to keep
track of what chores you need to do versus what other people are doing.
Are they doing their chores?
ET.
I'm sure there are tons of upgrades that you can think of yourself and you can implement as well.
But use what you've learned and if you want to, you can provide these upgrades.
If you don't, and you just want a cursory understanding of how persistence works with Postgres with
a node backend, then you have already achieved all of that through this project.
All that's left to do now is have a play around with your code.
Remember, it is play.
It's not work.
It is acquiring knowledge for your own benefit.
And once you're done, head over to the next lesson where I've got a capstone challenge for you for
all of that.
And we'll I'll see you there.