Node.js, Express & MySQL Tutorial - Build a Simple FullStack App

hi guys I hope everyone is doing well today we are going to create a simple full stack web application using nodejs with express my sequel and HTML CSS and JavaScript this project is a great intro into incorporating the front end to the back end and adding a database to it so here's a demo demo of the project so I'm going to add a couple names here and I will get the it'll be filled in into the table ok so let's see what all we can do so we can add names we can also search by a name so we'll do that in we can keep searching and it let's say if a name doesn't exist we'll say no data found so we can also edit a name so instead of tests we can say test 10 update it it'll be updated here we can also edit this cool so we can edit that we can also delete there you go so this is gonna be a simple web app but there's a lot of functionality into this and I'll show you what you need to get install installed and how to get everything set up so let's get started alright guys so before we get started into the code you want to download this exam which is gonna run our local my sequel server so it's gonna look something like this and let me stop the server and we're gonna run our local my sequel server so that we can connect to that database and set up our tables we'll get started with that later we're gonna go on do the front end first and then we'll get into the back end ok so I have my folder structure here and in my project directory I have two folders a client and server it's just to separate it and make make sure that it's not confusing for anyone so in my client directory we have index dot HTML index Egeus and style sheets CSS so I'm going to generate some HTML here by pressing shift 1 enter there you go and that'll generate our boilerplate HTML and we can just say crud app here now let's do something different because I'm going to have this example product that's already done just for reference so we'll just do let's do Express that or let's do web app okay and then I'm going to right click and open this with live server so if you guys don't have that extension you want to download it here and just type in live server and you can just install it right here okay so and the first thing we're going to do is let's create this name label and with the input and the button so we're gonna have a main tag here inside the main tag will have a label will say name and then we'll have an input of type text and that should be it we'll give it a name of let's give this an ID of name input okay so we have that and then we also want to add a button here that will just say add name okay so I want to Center everything in the middle it'll just be easier on the eye so I'm gonna link our CSS file rel is equal to stylesheet then we'll give the H ref okay and let's go ahead and link our script as well okay so now in the CSS we'll just grab the main tag and then we're gonna say let's do a width of 50% and let's give a margin of 50 pixels top and bottom and auto left and right so that becomes centered okay and then let's let's do let's add the search button so let's create put this in a div the search button so we'll have a label here do we need a label now we don't need a label we can just have the input with a placeholder and we can just say search by name we'll give this an ID of search input okay and I just felt I didn't spell a placeholder correctly and another thing is we need the button as well let's do such and let's give this button an ID of search BTN and let's give this button an ID of ADD name b-team okay let's add some breakpoints here okay and in the main tag I'm just gonna add a text align of Center okay so now everything is centered as well and let's see what else we need to add the table now so we're gonna add the table okay and well let's give this ID let's give this table an idea of table okay and then we're gonna give a tea head so that we can have our table headers so we're gonna say th okay so here we're gonna say I D use Emmett to create four more of these since we're gonna have four so we're gonna say name date added dully and edit let's do yeah let's do that let's keep it okay alright so let's give some styling to the table here so in our table we're gonna say table th and TD and we're gonna give this a border collapse and we're gonna say collapse so now and we're also gonna add a border the one pixel solid stoop left okay cool and let's also give some padding here to the th and the TD of five pixels alright and let's push that down now so we'll add the margin top to the table itself not D th and TD so we'll do margin let's do 50 pixels and auto there we go so it's all centered now and that looks good so what we're gonna I think that's it for the HTML so this no data will be added in the JavaScript file when we're doing our initial call they'll load all the data so I think that's really good that's great also we're gonna add a tee body here and it's just gonna have empty it's just gonna be empty because we will dynamically insert that using javascript okay so now in our JavaScript first thing we're gonna do is we're gonna add some event handlers on when the page loads so when the page first loads we want to grab all the data that's coming from the database but if there is no data we want to show this no data here okay so what we're gonna do here is say document add eventlistener and we're gonna say Dom content loaded could have a callback function and let me make let me enlarge this a little so it's easier to see okay so in our callback function we're gonna use the fetch API but since we don't have our back-end set it right now all we're gonna do is we're gonna load the no data so we're gonna have a function here called load HTML table and that's gonna take in some data okay so let's say if the data is empty so for now I'm just gonna call load data load HTML table and I'm going to pass in an empty array so we can say here if data dot length is equal to zero what we're gonna do here is insert a row into our table and just have it say no data so first thing we want to do above the if statement is say Const table is equal to document that query selector and we want to grab that table body so we could say table t body and we should get that and then we'll have another variable called table HTML let's do it like this okay we'll set that to an empty string so now if the data array is empty we'll say table that inner HTML is equal to and we'll add an over a table row here so we can save TR and then inside the table row we'll add a data table data and let's give this a class of no data it's gonna be single quote this class won't add any CSS to it we'll use it later to see if there's no data so we'll have to remove that table row so and then also we want to have to span across all five columns so we can save call span it's equal to five and then here we'll just say no data table HTML will come later into play because right now we are just gonna use table HTM oh so I'm just gonna remove this for now okay so now that's it for the let's see I think that's it for the front end for now let's get into the back end and set up all our database stuff in our Express app and let's get that configured so that our Express app is connected to our database and we can make it a fetch call to our back-end okay so I have a get open in my terminal here if you guys don't have that you'll want to go to the browser and download git bash for Windows or Mac or whatever system you have okay so I'm going to use git here to install our server-side modules so if you guys don't have no js' installed you want to get that installed in order to be download packages and of course use the Express app so yeah I'm going to CD into our server directory and in here I'm gonna download a couple things so I'm gonna say MPM close that I want to say NPM install I'm gonna do it in it first - why so that we have a package.json file okay all right so now we can say npm install Express and some of these packages will take a couple seconds ten stall okay so Express is installed now and we should see it as a dependency here yet we do and now we're gonna install my sequel which is gonna let us interface with the my sequel server and make queries okay so now we have my sequel and let's also install dot a and V so we have a place to store all of our configurations for our database like password username it's a good practice to do this so that whenever you put your code to the to a repository you're not actually sending your configurations there as well we're going to install one more package and it's gonna be node 1 so we're gonna say NPM install no month - - save - dev and this is a very useful module so that we every time we make changes to our script we don't have to restart the server it'll watch it for us and reload it for us ok so this is a dev dependency alright so that should be it for all the modules and we will need a so in our packages package JSON we have a starting-point script here I'm going to change this to a pas so we don't get confused with our client-side index ijs when we have it open so now I'm going to create a new file and say fjs ok and now we're apt is we're gonna require some modules here so we're gonna get Express say Express is equal to require Express also guys I forgot that and saw one more we need to install cores so that we can make API calls from our front end to our back end so we can say npm install cores that will download if you don't have this installed you won't be able to make API calls the back because you'll get some console errors in the browser okay so now we can say app is equal to Express we're going to require get Korres is equal to require course and then we'll also bring in dot env let's say acquired env okay let's create our dot E and V file as well and just say dot env and we'll come back let's add in our port number for our back-end local host for our server let's make that 5,000 ok we'll revisit this later so I'm just gonna close out of it now in here I'm gonna say dot E and V dot config so that we can access it when we need to app dot use course so that when we have an incoming API call it'll you not block it and we'll be able to send data to our back-end will also use Express that JSON so Express dot JSON so we'll be able to send it in JSON format then we'll just say Express dot your L encoded we're gonna say extended to false cuz we're not gonna be sending in any form data okay so here we're gonna create our routes so like I told you we're gonna have a create read update and delete route because I want to show you guys the basics so these this is gonna cover the basics of the my sequel queries so we're gonna say app dot so when we're gonna use create we're gonna say post that's basically when you want to create new data so we're gonna give this a forward slash of insert and then we'll just say request response okay we'll leave it there for now and I'm just gonna copy this and you know for now we'll just do that create okay so at create four okay before we do create let's do the read first because we already set up our JavaScript to on the when the duck Dom is loaded we'll make a fetch call to our back-end so let's do that let's do apt-get let's say let's say get all requests response okay in here I'm just gonna consult that log I would request sorry I'm just gonna say console dot log test so then we know from our when we make an API call we can see that we have reached our back-end okay and now let's let's go ahead and make an API call so when the document is loaded I'm gonna say fetch we're gonna say HTTP okay before I do this let's start our local server so that we know that it's running so we need to say app dot listen and we're gonna give the port number so the way we can grab our port number from our dot EMV file is we can say process dot E and V dot server let's do port that's what we gave the name and then we're gonna just say console dot log app is running okay and we're gonna or let's okay we're CD into our server app so we can say no daman app okay so the app is running let's just quickly test this so we can say local host local host 5000 and we can say get all okay so we get a console dot log of test so we know our back in is working so now in our front end let's get the API call made so we can say HTTP localhost port 5000 get all okay so now we're gonna need to send and I don't think we need to send any headers now we don't need to do that so we can say dot then so we're gonna get a response and we'll convert this into JSON we're always gonna be expecting a JSON response then we're gonna get a another promise and we're gonna say date would this will get the data back in our JSON format and we'll just console download the data here will actually load that HTML table call it in this okay so now you can see here that we have made a let's refresh so we have made in to fetch call and you can see our test is running every time okay we're not getting any response so let's go ahead and get that going so in our apt is we can simply just save response JSON and let's send a success equal to true okay so here we should get Avery success is equal to true which is great so now let's set up our database so that we can successfully connect to it and we can also grab data from there because we want this no data to actually come as a response from our database not actually having any data alright guys so here okay I'll stop this so if you start these two you should see the port number that it's on so it's gonna be on 3306 okay so I'm gonna click on admin here and it's gonna open the PHP myadmin and then in here we can go to databases okay so we're gonna create a new database so I have these sample databases that I was just working with and we're gonna create a new one so we can just say let's say web app you can name it whatever you like and for this tutorial it doesn't really matter what you pick here but I'm just gonna pick this one okay so I'm gonna hit create okay so now we have a table so in this table what are we gonna store so we're gonna store we're gonna have an ID we're gonna have a name and we're gonna have the date added so we're gonna have three columns here in the name of the table let's just do crud app crud table let's just do names okay so in here we're gonna have an ID it's gonna be of a type in we'll give that limped slash values of 11 and what also going to let me enlarge this a little we're gonna add the click on this so it's gonna be Auto incremented every single time we add a new row into the table and then here we're gonna just say name okay name is going to be a bar chart type and we can just give it a length of 100 that should be it for that and we're gonna have a date added column and this is gonna be a type date/time see here okay cool and that's really it so we can hit save so now that we have our name table created what we can do now is we need to get some privileges like a username password type account that is tied to this database so we can go to privileges here and we can create a new one so we're gonna say add new user and here we're just gonna I'm just gonna say let's do web app and I'm gonna say localhost and password I'm just gonna do test one two three test one two three okay so I'm going to generate that oh I don't want I do not want to generate a password I'm going to go back to test one two three test one two three okay so great I'll grant all privileges on the database web app which is what we want and we'll just hit okay all right so now we have an user account that is tied to our web app database which is exactly what we won and now we can go back to our dotty Envy okay so I'm going to open DMV and here I'm going to say username is equal to lab at password is equal to test one two three so this is a great way of storing your database details so that when you actually update your repository this amv file will not be added to it so this stuff stays local locally on your local computer and then we also want to add a couple more things that we need for the connection so we're going to say database and we're going to set this equal to what do we name it web app did I also give the username as web I guess I did yeah probably not the best practice but you can name it whatever you like and another thing we're gonna do is we're gonna say DB underscore port and set that equal to 30 306 which is our exam it shows us what port that my sequel server is running on okay so now that should be it for that oh we also need to add one more thing which is the host which we set to localhost okay so that's all we need for that for that now let's try connecting to our DB server make sure that it's working so I'm gonna create another file in here and this is going to be a service basically it connects to our database and also it's going to be a service that lets us make queries to the database and get all the data we need to so we can just say let's do DB service there's probably a better name for that okay in the DB service we're gonna call my sequel require my sequel we're also gonna call the DMV config so we can say require da DMV we can say DMV config ok so I'm gonna create a connection here so I'm gonna say cons connection and I'm going to set that equal to my sequel and we're gonna create a connection with this so we're gonna it's gonna require some it's gonna be an object that requires some keys so I'm gonna set this empty for now actually I'll just fill it and as we go so for our hosts we have process done env dot DB I think just host and then we have user as process dot e MV dot username ok and then we have password grab password database and then last is the port and I named this DB underscore port and that's all we need to connect to it so we can just say connection dot connect takes a callback and we'll pass in a parameter to see if there's any errors connecting if there is an error well it's simply just consoles outlaw of this sorry it's gonna be air will have a object key of message so we'll know what message it will display otherwise we'll just console dot log and we'll say DB + connection dot state which will tell us that hopefully it's connected so now in our app KS we can just include the DB service so we can here we can say right above here we can say constabie service is equal to require DB service ok so I'm getting some access denied for user probably something with the password is bad let's see hey guys so I had some issue connecting to the database so what I did was I changed my username from web underscore app to tutorial and in my dotty MV I got rid of username and I set it equal to user if you guys have any trouble with this please let me know in the comments below and I'll try to help you out to the best I can ok so now it says our DB is connected which is excellent so let's get back into here ok so I'm gonna leave this to the side now and let's come in here ok so in our DB service what we need to do is I'm going to create a class in this class is gonna contain all the functions that will we'll be using to get all the data or update data insert data or delete data for this tutorial this generic service will be fine so I'm going to just say class DB service and I'm went out first thing I'm gonna do is I'm gonna create a static function what I'm gonna say get DB service instance I only want one instance of this class I don't want to keep recreating an object of this class every time so I just there's no point in it so what we can do up here is say let instance equal no and then we can say return instance we're going to use a question mark for a ternary operation we're gonna see if instance is not known if it's not known that means an object is already created so return that otherwise we're gonna create a new instance of the DB service okay let me do this perfect so let me do this a little okay so we'll need to export this using modules module exports equal to DB service so we're going to export this TV service class and then in our app dodger so I'm going to close out of this dotty MV in this package.json in this CSS we don't need that so here let's we're gonna do our read function first so what we're gonna do here is say we're gonna check for our DB instance so we're gonna grab it we're gonna say kant's DB a service sorry constabie is equal to DB service get and we have access to this function get DB service instance so this will grab get us the object back so now we can create a function in here in this class we can say we're going to use async/await here to get our data okay so we're gonna call this function async get all data we're gonna add a try-catch block here we're gonna have an error and then in the catch we'll say so we'll just consult that log the air for now okay so let's get the try catch block so in in the try block what we're gonna say is contra sponsz okay and then we're going to create a we're gonna say a weight someone can create a promise here so in our promise this is what we're gonna handle the query so we're gonna have a resolve reject so if the query is successful we'll resolve it otherwise if there's any errors with the query we're gonna reject it okay in the in that reject it'll go straight into our catch block and we'll handle the error from there okay so what we're gonna do here is write the query so we're gonna say cons query and then in strings we're going to just simply say select star from in our names table in for good practice here I'm gonna capitalize all this you actually don't have to but it's it stands out from the keywords and the table names in the ver like variables column names okay so now we can say connection dot query then we can pass in the first parameter will be the query and then if you have any conditions where you're passing in data like from your function you can have a array here that will you'll pass in your variables in so for example let me show you so if you have where ID is equal to let's say 1 right what we're gonna leave a question mark here and then you would say ID here so would parameterize a query value so that we can prevent sequel injection okay and if you guys don't know what sequel injection is please let me know in the comments below I can create a video on it and then we'll put a semicolon here so here we're gonna have a callback function so with two parameters will pass in error or results so we're gonna say if air is true we're gonna reject and we're going to create a new error object and we're gonna pass an error dot message which will be caught by our catch block okay so if not what we're gonna say is resolve results so we'll do that okay so down here let's just can't console dot log the response here's let's see what we're getting here alright so now in our fjs so we need to make a call to the function so it's gonna be what did we call this get all data okay get all data and it doesn't take in any parameters and we're gonna set this equal to a variable called result okay so now I'm gonna make a fetch call which we already have and if as you can see we get it empty array here so I'm gonna comment this out so we don't have a lot of confidence happening at once so we get an empty array here and what we're gonna do here is just say turn response so we give an empty array here so what we're gonna do is a return response and when there actually is some data in there we'll see what that looks like will console dialog that as well so I had this back and I'm just gonna comment it out for now and our index IJ s so we should be able to see the results here oh we won't be able to because we're not returning anything so this when do you call this this is actually gonna return a promise because we need to wait for this to finish so here we'll say result that then okay and then our promise we're gonna get the response back and I'm not going to call this response because we have response up here okay we'll just say some data and then we're gonna say response that JSON and let's do data data and then we're gonna also have a catch let's say there is some error so we're gonna do catch air and what does console that log it we won't really handle any air we won't ever we won't do any error handling here we're just gonna assume that it works in all cases but we will catch it in concert without log it in case we do run into him while we're trying to grab our data okay so now you can see here we get it a object which contains an array in the data key alright so here we're gonna call load the HTML table I'm gonna pass it in here we're gonna pass data in here but data is an object so data let's in order to access the array because that's what we want to do is we need to just access the data key oh all right so now let's refresh this so let's see so we're passing data empty array let's see why let's see what we're getting in here oh there's a consular so that's probably why let's get rid of that okay cool it's working so data is empty okay so let's get some data in here let's work on this create functionality so in order to do this in our app jeaious we have our create method here which for post and we're just gonna console that log and see what we get in the request our body okay so above here so I'm gonna keep all my event handler handlers in the top and then any helper functions I'll keep them towards the bottom so I try to group them as closely as possible alright so let's get some data here so we're gonna say cons add name so add name will have an ID of tn so we're gonna say at BTN is equal to document that query selector you can also use document dot get element by ID but either way it works and we're gonna say add name BTN okay and we're gonna add a on click event here and we're going to set this equal to a callback function okay so in our callback function what we're gonna grab is the input value basically name so we'll say name is equal to document query selector all right so we gave that ID of name input and we're gonna grab the value will grab the element first and then we'll grab the value so actually here this a better name for this would be name input okay so what we're gonna do next is we'll grab the value and every time we send it to our back-end we're gonna reset this value to be an empty string so what we'll do is say cons name is equal to name input dot value oh okay value and then what we'll do is once we have that we'll say name input dot value is equal to empty string so what we're gonna do is send this to our back-end I'm gonna do HTTP localhost for 5,000 and we're gonna say insert is that what I let me make sure I created yep insert and then in here we're gonna send in some headers so in it's gonna take an object we're gonna pass in an headers object as well and we're gonna say the content type is of type JSON so we can say application slash JSON and then also we need to send in the body so the body is going to create include the JSON data okay so we're gonna say json dot stringify and then in here we're gonna say name : name and then we need to do one more thing here we need to pass in the method type so this is gonna be of type post since we're actually sending in some data to our back-end we want to send it as a post data okay so in our response here we'll get a promise and then we're gonna convert that to json and then we're gonna get the data and let's so this is going to be inserting we're gonna dynamically insert new data in to our table so we're going to create a new function here we'll do insert row into table and this will take a data variable for now let's leave it like that and then in here we're gonna call this and if it's we're gonna keep this pretty consistent so we're also gonna do the same thing passing data into our function which will be an object and then we'll be able able to loop through the second array all right and then we'll also do a dot catch actually we won't need to since we have it we'll have a dot catch and our back end so if there's any errors we'll just handle it we'll be able to see it in our console here so now what we need to do is once we send this to data to our back end what are we gonna do now so first we're gonna just check and see that we get our data so I'm gonna just type in test add name and we do get our data of test okay so I'm just gonna grab the name here and I'm gonna actually do it like I'm gonna use object destructuring here so that we don't have to do requests da ba dee da ta you can just say request spot body and describe the name okay so in our service we need to create a new function so I'll go down here say async and we're gonna say insert new let's do a new name and it's gonna take a parameter of name okay so we're gonna have another try catch block every everything will be a try catch block because it's just good practice to have that especially when you're dealing with promises and you have resolve and rejected it's really good practice so we'll just console dot log the error here oh this is just err let's keep be consistent and say err okay so now we'll do the same thing so I'm just gonna copy this there's no need to rewrite everything the only thing that's going to change here is our query and the way we handle this inside the query of callback so here we're gonna say insert into I'm gonna remove this okay so our table name is names and we're gonna say values so we want to only insert in our table the name in the date added we since our ID is auto incremented we don't need to add into it so we can have a parenthesis built before the values and we can say we're only adding to day name and date added okay so our values are coming directly from the front end so whatever this happens we need to prioritize our variables so that we don't deal with sequel injection so like I said earlier we are going to have question marks and these need to be in parentheses nope go in here okay so now we're gonna add a second parameter here and let's put a comma okay so in here we're gonna expect name and date added so I haven't created a date added variable yet so I'm gonna do that right now I'm gonna say date added oh man what am i doing they had a new date and sequel accepts date format in year-month-day and it also will store the time as well since we did it as a date time so that's good and instead of calling this response what we're gonna expect from here is an insert ID response is a good name to have it because it could also fail but what we're expecting here is an insert ID so if there's an error we're gonna reject it otherwise what we're gonna do is since we're expecting only one result I'm gonna only pack us they call this result and we're gonna say result dot insert ID is what we're gonna resolve which will be returned into the insert ID and we're gonna what we're gonna do here is we're gonna return the name date added an ID back to the table so that we don't have to grab just will have the name already in the front end I guess but it's good to just get the data from the back end and make sure that it's the same all right so we can we don't have to console that login but we can so we're gonna console that log insert ID okay so now in our app KS we're gonna say we're gonna call this same DB instance all right we're gonna say consul result is equal to DB dot insert new name and that's going to take in a parameter of name let me just make sure I called it that yep okay result the den dot then we will get some data back we're gonna just say response JSON for now we're just gonna say success is equal to true or not equal to true but set to true and then we'll also do a catch okay so an hour so now we can just send in the name so we can say test will add the name it'll come to our back end and you can see right here we get an insert ID of one and if you want to Claire double-check our database we should get our first row here which is great awesome guys so what we want to do now is return now you can see that no data was here so that means when we do have a value there should be a value that we're retrieving now insert row into data oh we didn't never handle the case well if there's there is a length of greater than zero so what we want to do now is handle that what we can do is we're going to do a dot for each all right so we're gonna say data dot for each we're gonna just have a callback function and then in here we're gonna retrieve a object right so we can just use some data destructuring and we're gonna say ID name date added since we're retrieving all the values back from the database okay and then now our table HTML variable will come into handy I'm gonna set that to an empty string we're only gonna declare it if there is some data all right and also if it goes into this if statement we want to return we don't want to execute anything below this so what we're gonna do here is say table that HTML and we're gonna override everything that's in here because we this is gonna be used when the table is first loaded all right so we're gonna say basically we're just adding h2 table rows to it we're not going to be overriding anything okay table row in the table row we're gonna add table dado okay so we're getting here we're gonna use string interpolation so I use these backtick symbols I can say dollar open close pack it and I can just say I D like this and now I don't have to escape the string and concatenate my variables and I'm going to copy this five times four times and then I'm gonna end this table HTML with plus equal and then the ending table rule okay so this will now be named this will be date added okay let's spell that right and then in here we're gonna actually have any variables so here we're just gonna have a button which will be the belly button and since we're getting the ID here when we click on the delete button we're gonna need the ID so this is a perfect time to set a data attribute in the button element and we're gonna add a class as well so we're gonna say delete row BTN and then we're gonna close that and then we're also going to add a data - ID and we're gonna use our dollar open close bracket and we're gonna say curly brace I mean not bracket we're gonna say ID okay cool so next thing is we're gonna have the edit button so here we're gonna create another button and actually the edit button is gonna be the same exact thing it's gonna have except just instead of delete it's gonna be just edit so we'll just call this edit data - ID will be the same we'll need that because we want to know which row we're editing all right and then after the for each loop we're gonna say table that inner HTML is equal to table HTML okay cool so our date at it is showing like that because it's needs to be converted to locale locale string so in here we can just say new date dot - local string and then open close parenthesis okay cool so let's kind of enlarge that now so it's an okay cool and I think I added one more extra yeah I added an extra greater than tag all right so now we're getting our data guys so we can add as many names as we want and we need to handle that now when we insert data so when we insert data let's see we are going to return the insert ID okay so for our insert function we add a new name we need the ID name and date added right so we're gonna send back an object here alright so we have the name already we need the ID so I'm gonna send these in proper order so we have the insert ID and then we need the comma here and then we also need the date added so date added okay so we have our object and then in here we're gonna just return data is with the data keep it consistent all right so now in our front-end we created this insert row into table so what we're gonna do is grab the table element and we're gonna just create the new row alright so let's do that okay so here we're gonna get the table we're gonna say document dot query selector I'm gonna set that equal to table to body you could just do t body but table is also fine to it both ways will work and then we're gonna say we need to check for if there's no data so if there are no data exists which means if you see here we added a class of no data so we need to check for this class all right if it exists so is table is a table data it's a bad name for it but meaning it just means that if their table data exists okay so basically what we're checking for is does that class exist no data because we're already checking for if their data there's data or not in the table so now we're gonna say let table HTML equal to table row so in our data variable we're going to get an array of objects so we can here we can also say data dot for each alright and we can also be structure this name ID name and date added all right so we need to check okay so we're gonna use a similar process that we did here so I'm just gonna copy that but in this case we're only inserting one row alright so we're just gonna copy these three these four things alright so we get the ID name date added and this way here we're calling it something different okay we're calling a date added because we keep it manually here and the other instance in our column it's date underscore added so that's why we got to keep it that way and we'll get the delete and edit button and then also below here we need to save table HTML plus equal we need the ending clothes table row bracket okay so now what we need to do is check for the state is that no class data exists okay so it's table data true so if table data is true we're gonna say table that inner HTML is equal to the table HTML I mean just just gonna get rid of that HTML that exists and override it if it doesn't we're gonna have an else condition so we're gonna say a constant is equal to new row table dot insert row so this will create a new table row and we're gonna say a new row that inner HTML is equal to table HTML all right so let's see if that works someone do test three and let's see if there was some air I'm sure there was okay data for each is not a function okay so we need to see you should be getting in okay we're getting an object back we're not getting in an array back so let's just confirm that yes we're getting an object back not an array of objects sorry about that guys so we can't use a for each function here so we can use a for end of ray to loop through an object okay so we're gonna save our key in data all right so the first thing we're going to do is check for if the key has the property of date added because we want to have the new date here okay so before we do that we the best practice is we need to check when we loop through an object we want to check for if the data actually has that key property because if it doesn't and you try to do something it'll throw a console error and we don't want that so in the if condition if this is true we'll say if key is equal to date added what we're gonna do is a data that bracket key is equal to new date data bracket key dot to local string okay awesome and then the rest is just easy so we just say table HTML plus equal we're gonna use backtick symbols here and we can just say table data and we can say data - key get out of that and the TD okay so we have that we have that three keys values the ID name and date added but we still need to have these to edit in delete you guys probably knew about that it's not an ovary of objects it was just an object all right so now let's test that shall we so if we have test five okay we are still getting some errors let's see what that is ID is not defined here okay got it got it got it so we need to get the insert ID sorry just ID which is coming directly from the data here so as you can see we'll get the ID we can't just say ID because we're not in the loop and we're getting data here so we can say data dot ID all right guys should work now awesome oh let's see here what is what our our date time is wrong okay so I've made a mistake here new date the ending parentheses should have been with after the key all right so let's see if that will be fixed now so we can say test all right perfect so our insert is working now so let's get double the the delete working so that we don't have a bunch of data just keep popping up on us so we can maintain it okay so now we're gonna add a delete variable constantly VTN is equal to document not query selector and I believe I kept the naming convention the same but let me just make sure delete row BTN okay so the tricky part with this is we can't just add a variable like that since it doesn't actually exist in the Dom technically if there's no data then we need to dynamically listen for it so the way we can do that is by using the document eventless add event listener we're gonna say document the query selector so we need to grab something that already exists in the HTML in the Dom on page load so one thing that that's why we added the tbody here so we can say table t body okay dot add event listener it's gonna be a callback oh the first parameter is gonna be a yeah callback sorry not a callback it's gonna be the click event handler type which is gonna be click okay and then we'll have a callback okay so in our callback in here we'll take the event parameter and we're gonna listen for the event so we're gonna say let's console dot log the event target and let's see what we get so let's say when I click on delete I get this button HTML element and then I even for added as well so these at the edit and delete will work in within this event listener so we'll work on the delete first alright so if the event that target dot class name all right so that will return this class name is equal to delete row - BTN what we're gonna do is we're gonna create a new function and we will send the ID to that function you know we'll send it to the back end and it will delete the row from the database all right so let's create that function let's create it right here below it and I know I said I will keep all the event handlers on top but it's good to just keep the functions as close as possible to where they're being called so we'll just call them put it here so delete let's do delete row by ID and we'll get an ID here okay so we're gonna call it delete row by ID and in here we're gonna pass it event dot target dot data set dot ID so that'll get passed down the ID from here okay so we get the ID we're gonna do a fetch call before we set that up and our backend let's set it up so here we will say F dot delete okay we're going to give the type as delete here we're gonna say delete and get an ID in the URL itself okay so I'll have the callback as let's see request response okay and we're gonna just simply console dot log request dot params alright so now we'll make the fetch a call we'll do H let's just copy this and then here we're gonna say delete forward slash plus equal to ID all right and then in our headers do we need to do anything we need to do the we need to give them method this is gonna be a method of type delete all right so that it can be picked up by our back end and then we'll have a dot then which will give us a response type JSON one more which will give us the data here will just return a true or false so we'll just console that log that data okay so I'm gonna send this to our back-end let's see if we click on this we get the ID as one which is exactly what we want so let's set up the backend so we'll do Const object destructuring we'll set that ID we'll get the request dot params ok and let's create our function so we have here delete row by ID we'll grab the ID and we're gonna just say what we'll do ID is equal to parse int ID alright so we'll leave it like that and this is a base 10 so you don't actually have to add it but I just added 4 oh as an edge case because some browsers won't always it doesn't always work the same so here I'm gonna copy so this is gonna be similar to insert kind of all right so I'm just gonna copy this I'm going to grab more space here okay so here we'll get the response and then we'll just say here we'll say dot affected rose and we'll just give it as a result how about that so that we can see what we actually get it in the response I want you guys to be able to see it so we're gonna say delete from the table name names we're ID is equal to question okay and then here we'll just say ID okay so now here let's just console dot log the response and also I forgot to set this up in a try-catch block so let's go ahead and do that all right we'll place everything in the body of the try block all right guys so let's see if I click on delete here Oh what we're not even calling the function so let's do that let's call this let's copy this all right and then I'm also gonna copy this as well okay so here we're gonna say delete row by ID and then we'll just say success is equal to true okay so if we call the back end here we call it the lien okay we got some errors here let's see what what did that is unknown column man in where clause okay Oh am I even passing I'm not even passing the variable down let's that would be a good idea all right let's try that again okay so it got deleted because the affected rows is one so if that affected roses one then we know that the row was affected so that means they got deleted in this instance in this case all right so here we can just say return so we're gonna do resolved that affected rows so return response and then we're gonna do a question mark response is equal to one question mark true otherwise false and then you would also have a return false here in the case the query or something broke within this code we would want to return false and then you would handle that in the front end okay so we're gonna just pass in let's do this is where we add success and then we'll just do data since data will just be true or false okay and then we can we're console without logging this all right so we'll console that along that but after we deleted what we would want to do is we want to reload the table so there's several ways we could do this in a real-world application what I would probably do is call so set on the page load it calls this a load HTML table gets all the data I would create a different function that grabs all the data again from the table without refreshing the page it would just be a fetch call and then we will call it load HTML but since this is just a tutorial we'll just refresh the page it's kind of cheating but it it does the same thing all right so if I hit delete here we can say you see that success is equal to true so here what we'll do is if theta dot success alright so if that is true we will just say location dot reload all right so let's give that a try awesome guys so the lead is working now I can delete everything let's try adding it and then deleting it awesome alright so the next thing let's get the edit going so the edit one is kind of nice it adds a little bit involves a little bit more UI so when we click on the edit button we're gonna have a HTML show up here that will have the input like this and the ID that will be affected all right so let's get that working we'll have a section here this section will be will have an ID of update row okay and by default it'll be hidden but just so that we can see it we'll leave it out for now and then we'll say label what are we gonna do name because in this case we're just gonna let the user only update the name type is equal to text and then we're also gonna give this an ID of update name input okay and then we also need a button that says update okay and this will have an ID of update row BTM all right so now by default this will be hidden all right and now in our front-end we're coming back to this event listener here we're gonna also add in check for if the event target that class name is equal to edit B Rho V TN and again what we'll do is we'll pass in the ID so I'm just gonna copy this I'm gonna call create this function call it edit row by ID okay so let's create this function right below the delete one okay and I'm going to now so when we click on this edit button what we need to do we need to do some several things here so we need to show that HTML section so we're gonna say Const update section is equal to document dot query selector and I believe I call that update section okay update section dot hidden is equal to false so let's see does that work no it doesn't let's see if there's any errors there is cannot say proper okay it's no that means I give it the wrong name update row update row okay so if I click on that okay that shows cool so we'll leave it there and then what we'll do so now we can't have a event listener not in here just kind of like add BTN we'll add it you know let me let me place this right below this one for the update button so update BTN it's equal to let's see update row BTN so we're gonna do a document dot query selector okay so now we have that right so what we'll do is below here we'll say update BTN dot on click well set this as a callback function so we need to grab this and we what we need to do is we need to all right guys so what we need to do is I'm gonna rename this so I'm gonna say handle edit row' this isn't exactly going to up actually update the row in the table all we're gonna do here is actually just show this UI and with this ID we're gonna insert a data ID like we did for the delete and edit and what we're gonna do is set it for this button right here alright so that we can grab it later so we will just say document that query selector will do what is it update row BTN yep update row BTN we're gonna grab the we're gonna get the datas data set we're gonna add an ID and we're going to set that equal to ID okay so now if you see here in the HTML this will have a data - ID of 10 all right so that this will be useful for whenever we click the Update button we'll get that ID we'll leave that so now we're gonna grab the name so we're gonna say name is equal to document dot query selector what did I name this update name input which is an ID update name input okay so we'll get the name dot value let's see do we need anything else so this is gonna be an update right so in the way this is gonna be something called a patch so we're not exactly creating a new row in a table though we are updating something so we're gonna use patch in this encase alright so here I have update so I'm gonna say app dot patch alright and in here this will be a slash update and then with quest response so we're gonna set some data through the body all right so I'm gonna just copy this DB instance get it ready alright and we also need to grab the ID in the name so we need to just grab yeah we're gonna pass the ID in the name so we're gonna say Const ID slash name - common name we request dot body ok so we'll make a fetch call here and also yep fetch call and in the fetch call I'm gonna just copy this we're gonna say update don't need that forward slash have some headers not headers we're gonna just pass in a body in the method so the method is going to be hatch in the body we are going to use JSON dodge stringify and then we're gonna have the ID and from the to get the ID we're gonna say document dot query selector I would have probably done that up here not in the fetch itself but it's fine for this case so a query selector we need to grab the ID right so we can say oh actually we have the ID from this so I'm gonna call this updated name input we don't need to use takamina query senator twice you can just say this dot data set ID then we can also grab the ID sorry not the ID the name so we can say updated name input dot value all right so that'll get us the data we need to pass it to the back end and then we'll do the Dan dot then response JSON Oh and then the data will just will just be another true and false in this case so we'll just do if what we'll do if data dot success is true again we'll just reload the page in practice we would probably hide the HTML element update the table but in this case we'll just reload the page all right so now we need to create a function back here okay we're gonna call this async create async function we're gonna call it update row by ID I'm gonna do update name to be more specific so we're gonna get an ID in name and I'm gonna just copy this whole thing okay so let's rewrite this query and we're gonna say update all right and we're gonna say update name sorry not update name the table name is what is it again names yeah names we're gonna set name equal to question mark here and then we're gonna say word ID is equal to question mark alright so we need to give the in the where condition what I do row we're changing so in our query here we'll say name first and then ID okay so if yep this should be fine and this should be fine okay let's give it a try guys so test warden will now become test two we hit update let's make sure that I okay I need to do response dot sorry I need to get the result right so let's just copy directly from we already did this so we don't need to do it again right here but let's call this update name by ID and also we need a pass name here let me make sure it's in the correct order ID and they yep okay let's do this let's call this test - all right so we got some errors here let's see what the cannot read property of undefined okay how about we just see what the result is console dot log the results let me make sure that my query is correct so my query is an update should be fine that should be correct okay it looks like there must have been some air let's try that again oh it did oh let's see there's something went wrong - okay just how to save that script so here let's edit it again let's do test - ok so there's something wrong with okay unknown column nan in where clause so we're not getting that means we're not getting the ID so the reason why we're getting an empty object here and now our back end is because in our headers we need to say the content type is of application application JSON if you forget to do that then it won't be able to send it as what we're expecting we see the data that we're expecting so application slash JSON okay so let's put it there let's try this so let's name this test to update now we get the data back in the backend so now I'm gonna get rid of this and I'm going to uncomment this okay so let's try this all right so we are getting the affected roses one awesome so we'll do affected Rose and I'm gonna just ctrl C because this should okay yeah I'm gonna leave it like that so as you can see it's already been updated to test - lets update it to test three and let's see here what do we get at the response didn't ok success was equal to false response oh here we need to say affected Rose that's why oh and I just deleted that okay so let's try that test one we'll do an edit let's change that to test two and now its test two and we could update the date added but hey we don't need to do that you we could update like the new date that it was added updated but let's move on and I want to show you just how the search would work so these quarters are pretty basic I hope you guys are able to follow along and if you guys have made it this far really appreciate that and I hope you have learned something we're gonna write one more query for this search and that should be the end of it and I hope this is super helpful alright so here we will add laughs dot get search and we're gonna give a name yes we're gonna pass in the name in the URL okay request response you guys know it already and let's do this let's get the instead of ID it'll be name and then in our back-end let's create one more function async search by name oh that should be up de sink okay and this will get the name okay so let's do the get all function I'm gonna copy this all right so here we're gonna say we'll get everything we'll get all the data but we're gonna say we're name is equal to question mark so in here we will just pass a name all right so we'll get the results it'll be the same thing and let's handle it in the front end so we will let's add another variable here search BTN we'll get the ID of that query selector search BTN and then we'll just do it right below this since this is the last function you know I don't know what I'm doing search BTN is equal to function all right so we will grab this value here so this value it's gonna have in so we'll say search query actually it would be better to say search value is equal to so the ID here of so document dot query selector and then ID search input all right and we're gonna just say da valium here all right so we'll get that value directly will make the fetch call I'm going to just copy our similar one to this we're gonna actually do the same exact thing it's just gonna be a different route here update not update search and then we're gonna pass in the name which is gonna be the search value all right cool so shall we give it a try we can say test - oh that's not very helpful about that okay so that let's see that even hit our back-end Search Search value and of course we're not doing anything here let's copy the get all shall we will do this okay so instead of get almost a search by name I hope that's what I called it let's see search by name yep I mean it this is gonna take a parameter of name all right so let's search by test we get no data all right so I'm gonna refresh this I'm gonna get I'm gonna add a couple names here test3 okay so let's search by test3 cool we get that we can edit this test five refresh and everything gets refreshed alright guys that was that's all what I wanted to show you I know this was pauly long but I really hope that you guys learned something if you did please consider liking and subscribing let me know if you guys want a more advanced my sequel tutorial or just sequel tutorial in general if there's any projects that you would like to see implemented I'm looking forward to your comments thank you so much for watching bye bye

Channel: Nsquared Coding

Views: 64,012

Rating: 4.9595962 out of 5

Keywords:

Id: vrj9AohVhPA

Length: 90min 53sec (5453 seconds)

Published: Tue Feb 25 2020