MySQL Node.js Express

any web app needs to be able to persist data somehow and relational databases like mysql are always a good option for a web app in this video i'm going to show you how to connect a node application to a mysql database to query and insert data and then i'll show you how to connect that to an express application to see how all the pieces work together yo i pitch wisdom for the kids though solid as a prism keeps a funky wrist i'm assuming you already have my sequel installed on your computer and if you don't i'll leave a link in the description to another video showing you how to get that set up but once you have it installed you should be able to access the database using either mysql workbench or the terminal and i'm going to be using terminal to connect to my sql which i can connect to using the root user by typing my sequel dash u root that's the user dash p for password and hitting enter and then i can enter in my password hit enter again and then i'm in my sql where i can type any sql commands like show databases and right now i should just be able to see the default databases in my sql for this example i have some sql already written to set up a database and a simple table for a note-taking application so the note table will just have an id a title contents and the time that it was created and then i'm just going to insert two dummy notes into the database so we have some data to work with and i could run this file or i can just copy and paste all this sql code and dump it into the terminal here and now if i show the databases again i'll be able to see that i have my notes app and if i select star from notes i'll be able to see all my notes that are actually stored in the database and if you want more information on how to write create table statements or sql in general i'll leave links in the description to other sql resources so now i'm actually going to set up my node application i'm going to create a database.js file and this is where i'm going to put all of my code that connects to my sql and queries the database and before i write any code in here i'm going to go back into terminal and i'm going to run npm init y to create a package.json file because i want to use es modules so i'm going to use type module here and this will just allow me to write import and export statements rather than using the old common js modules but you can choose to use common js you'll just have to tweak the syntax slightly but the logic will still be the same so now we need to install the my sql library so i'm going to type mpmi my sequel 2 and you've got to make sure that you install my sequel 2 and not just my sql because my sequel 2 is a much better version of the library it has a lot of improvements and that's the one that we want to use so back in my database.js file i'm going to import my sql from my sql 2 and then i need to connect to the database so before i connected from terminal and i'm connected to the database running on my local machine uh this could be a mysql database in the cloud but to make things easy and simple we usually use a database on our local machine when developing applications so this is a database on localhost the user i used to log in as was root and the database that i've created is my notes app database so in here i'm going to ask mysql to create a pool for me using those details so the host in this case is going to be localhost my local machine and sometimes people have a little bit of difficulty when entering localhost in here so instead of localhost i'm going to put 127.0.0.1 which is what localhost resolves to so this is less likely to cause any errors since this is the end result anyway then we need the user which in my case is going to be root the password which in my case is just an empty string and the database which is going to be the notes app i love how github copilot is just autofilling these things for me so we're creating a pool here and we need to store that in a variable i'm going to call this pool any pool is kind of what it sounds like it's a collection of connections to the database so instead of creating a brand new connection for each query that i want i'm going to have a pool of connections that can be reused which can be really handy as your application starts to scale and one more thing i want to add to the end here is uh dot promise because this will allow us to use the promise api version of my sql instead of having to use callback functions so for this entire demonstration i'll be able to use async await rather than having to use the old callback version so i'm just going to run this code quickly just to make sure there are no errors we shouldn't see any output but if there were no errors connecting to the database then we should see nothing which is great and now i'm going to use this to actually query the database so on the pull object we can call the query method and pass in a plain old query to my sql so i want to select star from notes and this is a promise so i'm actually going to use async await here const result equals pull dot query select star from notes so this is just the exact same query i might enter into terminal or my sql workbench to get a list of all the notes from the notes table but now i'm going to run that directly in my application and i'm going to console log the result out here and what i'm expecting to see is those two notes right i have two notes in the database this one and this one and i'm expecting that when i run my javascript code now if i go back if i run this javascript code that's doing that exact same select statement i'm gonna expect to see those two notes uh being logged out to the console so if i run this code you can see there's actually a lot more going on here there's a bunch of information a bunch of information this is way too much information but if i scroll all the way to the top there are the two notes that exist in my database and they're just being represented as javascript objects an array of javascript objects which is fantastic you know you select a bunch of things from a database they come back as an array of javascript objects and this is just metadata about the data types within the table that can be handy but we probably don't really care about so notice here that the outer data structure is an array that contains two children uh the first array is the actual array i care about the array of data and the second array is a bunch of metadata that i don't really care about so the rows in the table and create a new variable for this r result 0 the first item in the result array so if i console log just the rows now then i can see this is just the objects that i wanted from the database and a more common way of writing this code is to use destructuring assignment so the exact same result would be to uh put square brackets around rows here and this is going to get the first item out of the results array store it in a variable called rows and then the output should be exactly the same and now to make this a little bit cleaner i'm going to wrap this inside of a function called get notes and i'll run that query and then just have this function return the rows out of this function so anytime i want to get all of the notes from the database all i have to do is call awaits get notes i need assignment in there and then i can console log the notes and that will give me the exact same output and i definitely want to write more queries here but before i do that i want to talk about environment variables these are variables that exist on the system on the computer that your code is running on and they're the things that look like this when you get process dot emv dot host or something like that you might have seen it before and in a situation like this we definitely want to use environment variables instead of hard coding the values in here and there's two reasons that we want to do that environment variables like this exist on the computer so they allow us to modify the values in an application depending on where that application is actually running so on my machine i want to connect to the database on localhost but if i deploy this to the cloud on heroku or something i'm going to want to make sure the host is to a mysql database also in the cloud and i might be deploying that app or someone else might be deploying it either way i want to be able to change this value without actually having to come into the code and modify my javascript another reason is that sensitive information like the password here i don't want to hard code into the app and i definitely don't want to commit into source control so it allows us to configure things easier and it allows us to hide information a little bit easier just by using environment variables instead of hard coding the values in here so what i'm going to do is specify environment variables for each of these and i'm actually going to prefix these with my sql so i'll go mysql user instead of root my sql password and my sql database there we go almost but now we need to actually set these environment variables somewhere and the most common way to do this when developing a javascript application is to put this in a emv file so i'm gonna create a brand new file called dot emv yes with a dot and put these values in that file and i'm gonna set this equal to the values that we had before one two seven 127.0.0.1 uh the user is root my password is actually empty and my database is notes app just like that and this file you'll never commit into git into your source control it will only exist on your machine and everyone else that runs this application will have to have their own version of the dot emv file and to make sure the application actually knows about this file we are gonna install the dot emv library and this is a library that will just load in the environment variables for us without us having to do anything but we do need to import the dot emv library and configure just like that so we'll put that at the top of the database.js file and then everything should still be working the same we should still be able to connect to the database yep because it's still using those values but we're now instead of hard coding those values into the app we're using environment variables so let's write some more queries in here right now we have a function to get notes but i might just want to get a single note using its id so i'm going to create another function here called get note singular where i can pass in the id of a note and then this should just return that single note so i'm going to basically start with what i had up here but i'm going to use a template string so i can write this on multiple lines and what i want to do is select star from notes where id equals and here i have to input the id and it's suggesting that i use a question mark which is the correct thing but first i'm going to do the incorrect thing which would be to just dump in the id as its plain old value here and this will work i could run my get note function uh let's see let's pass in one i just want to get the first note and this will run select star from notes where id equals one which should get me the first note i'll just rename this to be more explicit and if i rerun the code here we can see it gives me the first note but just dumping the value into the string here is bad practice because this value wasn't written as part of the query and it's likely that it will come over an http request from an end user so we can't trust this value this is an untrusted value and we have a value that we don't trust what we'll do is put a question mark instead of the actual value so this entire statement will get sent to my sql as an incomplete statement with a question mark instead of the actual value and then separately we'll send the id we just put this in an array as the second parameter to the query function and mysql will manage this value and this query to make sure that the query is still run correctly but that the untrusted data isn't part of the query because if we did allow untrusted data here this could lead to sql injection attacks where people can basically get any sort of data they want from your database potentially delete data but much more dangerous is that they can just query whatever they want they can get access to pretty much anything and again i've got a video on that i'll leave a link in the description if you want to watch it but let's just rerun this code for now to make sure it still works and there we go it is still working even though we have that question mark and by the way this syntax is called a prepared statement we're sending the sql and the values to the database completely separately one thing i want you to notice here is that i'm requesting a single note this is always going to be a single object because i'm saying where the id equals a value and since the id is a primary key we're always going to get one single note object but because a select statement always returns an array we're still getting an array so every single time i'm actually going to get an array and i have to get the first object out of that array and i don't think that's great so instead what i want to do is always return the first object out of that array just to make this function work a little bit nicer so anytime i call get note i'm expecting a single object to come out of this so if i run that again there is the note with id1 and what happens if i put in an id that doesn't exist like 100 i should just get undefined which is exactly what i want i try to get a note and it doesn't exist so i get nothing uh but if there was a note then i get that note object i want to add one more query here which will be to create a note using the title and the content and co-pilot know exactly what i wanted to do and in this case the query is going to be an insert statement just like that where the values are coming as input to the function so i'm going to be able to pass any title of the note and the actual content of the note and i'll write this insert statement again with question marks to represent the values that are coming from somewhere else these are definitely going to be coming from a user and i don't trust them so question marks to represent each one and then the second thing i passed to the query function is an array of those things of those values so title then content and these must appear in the order of the question mark so this first question mark represents the title so title must come first in the array the second question mark represents the content so that must be the content in this array and when i run this let's actually see what the result is in this case this is an insert statement now so i'm going to have this function just return the result and then i'm going to call this create note oh look at that test test why not uh and then console.log that result right there so let's see what we're getting from the database when we do an insert statement uh content unknown column content in field list okay it's not content it was contents aha okay so i messed up my sql query it's contents um copilot did not know what it was doing and there we go okay so this is a result set header we're still getting an array back from the query uh we get a result set header where it tells us uh how many rows were affected in this case i created a brand new row so that's one and it gives us the insert id so this is kind of useful i know the new primary key of the row that was just inserted but that's all the information we get we don't actually get the object back from the database so i would say that well this is an array so i'm going to do that result dot insert id is about the only thing that i actually care about there the only useful piece of information there so if i run this again i should see that the insert id is now four because it should just increment yeah so i inserted a thing into the database and its primary key is four and if i went back to the database and just ran a select star from notes i can see my new notes being added into the database there they are being persisted and there are the primary keys the ids that keep incrementing but when i run that insert statement in node i really only get the id and this could be fine this is maybe good enough and i could potentially even return an object here where i have the id title and content and then it would appear as though i'm returning the entire object so if i run this insert again and console.log the result i'll say okay that kind of looks like the object that was added to the database but when i insert into the database i'm having my sql auto generate the timestamp here so i don't have this piece of information so if i wanted to insert an object into the database and then also view that object as it appears in the database so that would include the id any uh default value so in this case created and any other values that were inserted i would have to run another query to get that object from the database so you don't have to do this but it can be nice um so i'll just show you how to do that quickly uh so i'm just gonna store this in a variable the id of the thing that was just inserted put that in a variable there and then i already have a function that will get a note from the database given an id so i can return uh get no with that id and now every time i run the create no function i get to create a note using the title and the content and the thing that i get back is actually the result that exists within the database so that can be nice and let's just run this node database there we go id6 title contents and the created timestamp and that's good enough for now i can create and query my notes in the database so what i want to do is look at how this could be integrated into an express app and to do that i'm going to export all of these functions so that i can use them in a different file so i'm done with my database file now i have all of my sql statements and all my database logic just in this single file so everything will just exist here and then i'm going to create a new file called app.js and here i'm going to put all of my express code to create that http server and i'm not going to go over the details of express in this video i do have another video where i go over how to use express with ejs to create web applications so you can check that out if you want to learn more about express for now i'm going to skip over those details just so we can see how we'd use the database with it so the first thing i need to do is actually install express and for this app i actually want to use express5 which i think is still in beta so this is how you have to install express5 and the reason i want to install express 5 instead of express 4 is because express 5 has asynchronous error handling which allows us to write code using async await that could cause an error and then we can handle that error in a single location which can make the code a little bit easier to write and read and run so i'm just going to come in here and find the error handling code that i need i'm going to paste that block in here just so i can handle errors in one place uh so i'm gonna import express from express i'm gonna create an app from express i have some error handling code in here i'm gonna have to app.listen on port i'm actually going to use 8080 uh and then i'll console.log server is running on port 8080 that's good and then i'm going to create a get route to get the list of notes and right now i'll just send back uh this should be the notes and then we'll connect this up to the database in a second i just want to make sure all of this is working first so if i run this by running node app js it should be running on port 8080 and then i'm going to open up a thunder client window just so i can make these requests from here so i'll go http localhost 8080 slash notes and see what i get back here this should be the notes okay so a get request to slash notes is giving me that string but that's not very useful what i want this to do is actually return the notes from the database so i'm going to import all of my database functions which is get notes get note and create note into my app.js see there we go rom database dot js and inside of this app get i'm going to make this an async callback function i'm going to ask the database for all the notes and then i'm just gonna send back the notes that's it get the notes from the database send it back and since all of the database logic is in the database.js file my server side logic gets to stay a little bit more simple a little bit cleaner and if i make this request again i should see that list of like six notes oh wait and i have to restart my server okay one more thing i'm gonna mpmi dash d nodemon and i'm just gonna set up a quick no manuscript so that this uh re-starts my server every single time i make a change so i'll have npm run dev mpx nodemon app js i go over this stuff a little bit more in my express video so check that out if you're more interested in this uh but now if i mpm run dev it should start my server and it should always restart every single time i make a change so now let's send this to get the notes and here we go this is responding to the http request with that array of notes from the database there's all six of them that's pretty cool um in my app.js i'm going to do this in the kind of you know restful way so i'm going to make another get request to get a single note where we can specify the id of a note uh this will be available to me in rec.prams.id so if i pass in an id here basically the idea would be if i go slash notes slash uh six i should get the sixth note uh so i'm gonna pass in an id here whatever that is i'll pass to get note and i'll rename this just to be more clear and again we're just calling these functions on the database file since we put that all in a separate file we don't have to worry about the queries here it keeps our server side code uh really clean and small um so that restarted already let's make a request to slash notes six we get the sixth one if i put in five we get the fifth one if i put in uh pancakes we should get undefined should probably have an error case there but this does all seem to be working so that's good then the final thing here is to make a post request if i make a post request to slash notes i'll make this async put in rec res what i'm expecting the client to do when they make this request is to pass in a title and contents in the rec.body that just github copilot just filled that out immediately for me uh so if a title and contents is supplied in the rec.body i'm going to try and create a new note using that create note function in the database where we pass in the title and the contents i'm just going to rename that to be consistent and then this function returns the note that was just created so if i look on the app.js side we get the title and contents over the http request database has no idea about http it's just a simple file that deals with database app.js has no idea about sql but it knows about http so it just grabs that data from the http request sends it over to the database file tells it to create a note this is going to return the note that was just created i'm going to send that straight back to the client and i should actually put a status code of 201 here to say it was created and one more thing because this is now a uh json api i'm gonna have to say app.use express.json just to say that any json body will be accepted and passed into the rec.body object so in my request here i'm going to create a post request to slash notes uh i'm gonna have to go to the body and input json oh if i'm running out of space here okay uh so i need a title from thunder client and content just some note contents okay so i'm going to send this as the body in the post request here send that up and it gave me a 201 response which is what i specified and here is the new note that was created that basically was sent through a post request to my app.js file this data was taken from the body passed over to this file where it was inserted into the my sequel database and then if i were to query the database directly i would see here exists my new note that i just created through an http request and this is kind of the full circle thing i can use an http server to interact with a database to persist data coming over those http requests and then i could connect this to something like a react app or a mobile app or an ejs app and i'd have a full web or mobile or even desktop application that persists data using a mysql database and i do want to demonstrate that error handling code here because i think it's really nice that we can keep our queries in the database.js file and then app.js becomes really clean but we do have to handle if there's an error for example if we can't connect to the database so i could make sure this doesn't connect by inputting a wrong username like roo instead of root uh and then that's going to cause an error when we actually try to make a connection so in my app.js i need to handle those error cases where it can't connect to my sql uh and we can do that in express5 using this single middleware function so i can log the error on the server and as the developer i can go and check the logs and fix any errors that might have happened uh maybe the database went down i just need to restart it or something but then back to the client i need to send an error message and in this case i'm just saying something broke could add in an emoji here there we go something broke and now if i make a request to this server uh let's just make a get request to keep it simple because i've now modified it it won't be able to connect to the database i should get that error message there we go something broke if i check the logs of my server i can see that the access was denied for rue at localhost so hopefully that would be enough of a hint to be like okay the username was wrong i can go in i can fix that that's enough information for me meanwhile the user is getting some notification that something bad happened you know maybe you tell them to try again in a minute or something i don't know but you do have to make sure that you're handling all of these error cases and you don't just let your application crash if there's an error or anything like that so now we have the database file that connects to the mysql database and runs all of the queries and i have my app.js file that handles the http request uses the database file to interact with data in the database and that's it for this video mysql is a really powerful open source relational database management system that is great for any web app if you made it to the end of this video please leave a comment and let me know what you thought of this video and let me know if there's any other topics that you would like me to cover in other videos yo i pitch wisdom for the kids solid as a prism keeps a funky

Channel: Sam Meech-Ward

Views: 149,492

Keywords: how to connect to mysql database from node.js, how to connect to mysql database, how to connect mysql in node js, how to connect mysql from nodejs, connect node js to mysql, node js to mysql connection, express mysql, node.js mysql, nodejs mysql tutorial, nodejs + mysql, mysql and nodejs, node js mysql project, how to connect to mysql database in javascript, node js mysql tutorial, node js mysql connection, node js mysql, express mysql tutorial, mysql async await, node mysql

Id: Hej48pi_lOc

Length: 28min 2sec (1682 seconds)

Published: Thu Jun 16 2022