Using MySQL With Node.js

[Music] hey what's going on guys in this video I'm going to show you how to integrate MySQL with nodejs now this is a question that I get a lot from developers that are coming from a lamp stack background PHP developers that you know use Apache in MySQL and they want to start dabbling a node but maybe they're not ready for MongoDB or you know some of the other know SQL databases that are commonly used with node you know you have the mean stack and and note are closed very closely related and used often so what we're going to do is we're going to use the MySQL client here MySQL package that we can install using NPM so obviously you need nodejs installed and then this has some pretty good documentation this is the github page we're just going to install it we're going to set up a connection I'm going to give you the tools that you need to make selects and inserts updates deletes we're not going to build a full application but it'll give you you know the knowledge you need to go and create your own application now we're also going to be using Express because I do want to use routes we could use it without Express but it's a lot of work to set up routes and stuff without without a framework and I don't really want to focus on that I want to strictly focus on MySQL so that's what we'll be doing in this video guys hope you enjoy it and let's get started so if you guys really like my videos and you learn a lot from them and maybe you have a couple extra dollars to spare check out my patreon page I'm working on creating special content for patrons you also get special deals on future courses and there's even an email support tier for all YouTube videos and projects to learn more visit patreon.com slash Travis e-media all right guys you're going to need MySQL installed on your machine ok I'm using Windows and using a program called xampp which gives me an Apache server MySQL and PHP now this is cross-platform you can install this for Mac and Linux as well and I would definitely suggest it for development with PHP or and Apache and any of that stuff gives you a nice localized environment so that's what we're using it also comes with PHP myadmin so if that's installed you can go to localhost / PHP myadmin and this will give you all your databases show you your tables and all that stuff we're not going to really be doing anything through here aside from just visually checking our results that that are going on through the application but just know that I'm using xampp and you can install that if you want so I have Visual Studio code open here and I just have an empty folder called node MySQL that's we're going to be building this application or semi application and I'm going to be using the integrated terminal as well actually you know what I got to clear this stuff oh all right now I'm using git bash as my command line and I have that integrated with Visual Studio code to do that all I did was install get bash and I went to the settings and I just added just make this a little bigger I just added this this right here terminal integrated shell windows and I pointed to the git bash software alright so if you want to do that you can so first thing we're going to do is we're going to create a package dot JSON file and we can do that with NPM and knit and if you're new to no js' package jason is kind of like a manifest file it has your app name version has all the dependencies as far as modules you're using things like that so it's going to ask us some questions the name of the application that's fine version description will just say sample app or using MySQL with node for the entry point I'm going to call it let's call it app dot J s and then I'll just enter through the rest all right and now you'll see that we have a package JSON file it should look something like this now to install our client here let's go to the github page we just need to run NPM install MySQL alright so let's go down here I'll clear this out and we're going to say NPM install and I'm going to do - - save because that's what that's going to do is it's going to save it as a dependency to our package JSON file and then we're going to install MySQL and I also want to install Express which is our back-end framework all right and that should pop up in this file since we use the - - save alright so there we go so we have Express and - queue installed so we'll close that up now we need to create our entry point which is the app J s file okay and then we're going to just bring it up over here we're going to bring in both Express and MySQL so let's say Const Express and we're going to require the Express module okay we also want to bring in MySQL so stay constant my SQL equals require and we want to bring in MySQL all right so let's um let's just set up a very simple Express server so we're going to create a variable called app set it to Express alright then we want to be able to start the server so we're going to take that out variable and just say dot listen and we want to listen on port 3,000 or whatever you'd like and then we're going to just add an arrow function here this is just going to run after the server starts I'll just say console dot log and let's just say server started on port 3000 alright and that should allow us to run it so if we go down here you know what else I'm going to do is I'm going to install something called node lawn which allows us to run our server but not have to restart it every time we make a cinema a small change so what I'm going to do is say NPM install and I'm actually going to install this globally so I'm going to do - G and then node Mon all right and then once you have that installed you can simply run node LAN and then will run your application ok just make sure you're in the correct folder we'll say node 1 and now it's running on port 3000 so if we go over here and I want to keep that open close that let's go to localhost 3000 now we're going to get this cannot get slash because we don't have any routes for our server for our application that's why we're getting that we're just creating routes so that we can run certain queries and certain functionality we're not going to have a user interface or anything like that all right we'll just have for instance when we insert a post or something we'll just say post inserted all right so to start using MySQL we need to create a connection so if we look at the documentation you'll see right here we already required MySQL now we need to create a connection and we need to connect so let's go right we'll go right above the where we created the app variable and let's say create Jesus create connection and we're going to take that we're going to create a variable I'm going to call it DB and we're going to set it to MySQL dot connection all right now you're seeing stuff happen down here because I have autosave on I'm actually going to shut autosave off all right so create connection and then this is going to take in a configuration object and if we look at the documentation it takes a host the user password and database so let's go ahead and just copy that all right you want to put your credentials for your database I'm using the root account and I'm going to put my password in here and for database we haven't created a database yet because I want to show you how to do that through the application but we're going to call it node MySQL all right so that's our configuration now we need to actually connect so to connect we're going to take that DB variable that we have and we're going to call the connect function all right and then we're going to pass in here and at now you can do like a callback like this if you want but we're going to stick to ef6 syntax and we're going to use an arrow function so just parentheses and then an arrow and then some curly braces all right and that's going to take in an error and we want to check to see if there's an error before we do anything so let's say if there's an error then we want to do a console log oops so console.log and we're just going to say you know what let's just throw the error we'll say throw her all right and if there's no error then we just want a console log and we're going to say MySQL yeah MySQL connected all right and that should actually connect us so let's save the hell's going on here unknown database node MySQL so we have we don't have the database created now we could go to PHP myadmin and create it but I want to I want to actually create a route that's going to show you how to create the database through the application so let's go right under the app declaration here and let's say create dB so we're going to create a route so to create routes and Express we can do app get because it's going to be a get request and let's make the route we'll just say create create DB all right and then we're going to put an arrow function okay that our function will have a request and response object and then what we want to do is create our query so let's say let SQL equals and if you know SQL this should be really easy we're just going to stay create database and we're going to call it node MySQL all right so that puts the query into a variable and then to run it we can just take that DB variable and say DB query and then we want to pass in first of all the query itself and then a callback or an arrow function okay and then this arrow function is going to take an error and result okay so it's going to run the query we're going to check for the error let's just say if there's an error then let's throw that error and if not then we're just going to res dot send because I want something to happen in the browser so we're just going to say database created all right and we can also log the result okay it's going to give us a result back so we'll say console dot log result alright so hopefully that works let's go ahead and save it and let's see if we go to localhost 3000 slash create dB alright so so it's not actually letting us run it because we specified the database up here so what I'm going to do is just get rid of that for now and then it should yeah okay so now it's actually letting us run it you can see mysql connected so let's run that route again database created and this is what it gives us back as a result because it's this this okay packet gives us a field count affected rows insert ID this is a common result you'll get back from things like inserts all right now let's go to PHP myadmin and let's reload and there it is no - I asked you L okay so it created that database for us now a lot of times you're not going to have your application do that but it's good to know you can do it okay so now that that database is created we can now add that back so we'll say database I think it was it was a database yeah database set it to node MySQL and now the application should run just fine alright so now we're going to do is create a table okay I'm going to show you how to create a table we're going to create a post table we'll say this is like a blog application so let's create another route we'll say create table and we're going to do apt-get for the route let's just say create so that I say create posts table ok let's put a slash there and then we'll have our arrow function okay and that's going to take in request response and then we're going to create our query just like we did above so say let SQL equals and let's say we want to create table posts and this is going to have an ID which is going to be an integer all right and I want to set this to auto increment everything you do within this if this query here is just pure SQL if you know SQL then you understand you should understand all this all right and we're going to have a title which is going to be a varchar' and yeah let's give make that 255 max and then we'll have a body and I'm just going to make that a varchar' as well 255 and I think that's it yeah but we need to make the primary key let's go ahead and we'll say primary key is going to be ID all right so ID is going to be Auto increment and it's also going to be the primary key let me just wrap this so I don't go off-screen all right so under the SQL query just like we did with creating the table we're just going to say DB query pass in the SQL and then have our arrow function okay and then the arrow function is going to take error and result and then what we'll do is we'll just check for the error you can handle errors however you want and then let's just do console log result and let's send out to the browser and we'll say posts table created all right so save that let's go back to Chrome and let's go to our route so it's going to be what I call it create post table ok we'll run that and looks like we have an error you have an error in your serum SQL syntax let's say create table post i D just going to be an integer it's going to be auto increment I'm going to have a title which is a varchar' body which is a varchar' body what am I missing here oh I don't have a collar right here all right so let's try that nope still in work still an error in my syntax check your manual yeah right noise two lines what if I miss in here primary key oooo ID should be wrapped in parentheses alright so hopefully that works there we go post able created let's let's reload there we go so now we have our post stable and make this bigger so we have our database node by SQL with with our post table if we look in the table and we look at the structure we have an ID primary key it's Auto increment we have a title and body which are both far char alright so next thing we're going to do is we're going to insert some data so let's go ahead and create a new route we'll say insert post let's say insert post one will say app gets let's make the row will say say post or let's just say add post one alright request response let's create our query so let us key well equals and let's see what we're going to do here let's actually before we create the query let's create the data so we'll say let post equals and we're going to set that to an object let's say title title I'll just say post one body say this this is post number one and yeah that should do it just the title on the body and then we'll make our query so we can say insert into posts and we're going to say set and then a question mark alright and then let's see we're going to go down here and we're going to say let qui very and set that to our actual DB query all right so DB query and we're going to pass in SQL and then this question mark is basically like a placeholder for what we put here and what we're going to put here is the actual post data okay like that and then the third parameter will be our arrow function all right so that's going to take error and result okay and then in here we're just going to do the same stuff we've been doing we'll just check for the error log the result and then let's change the the message here we'll just say post one post one added all right so let's save that let's go back to our browser and let's change this to what was it add post or add post one okay post one added let's go to PHP myadmin and let's go to our post table browse and there it is ID 1 post 1 and our body all right so let's create another route to insert another post just because I want to have more than one here we'll change it to two let's change this to add post two will change the title and the body and this can all be the same we'll just change the message all right and then we should be able to go back and say add post to post to added reload and there we go so now we have two posts so that leads us to making a select ok we want to be able to select this data so to make things a little quicker let's copy this change this to select select records or select posts all right we'll change the route to let's say get posts we don't need this for our query or just simply going to say select select all from posts okay and then let's see that get rid of the post here we don't need that we just want to pass in our SQL query and then we have our error I'm going to change this to results that makes more sense we're going to check for the error and then we're console.log the results and then in here let's just say posts fetched all right and it should log our posts down here so let's save and we need to actually go to get post to run it and if we look down here there's our posts ok id-1 id2 so let's let's fetch an individual post okay so I'm just going to copy this and what you would do when you're building an actual application is you probably would be using like a template engine like ejs or handlebars and you would make your query and then you would grab on to this results and you would pass it into your template and then loop through those and output the results okay but we're not building a full application I'm just showing you how to implement this all right now I'm going to do something here I'm gonna say the route get post this is going to be select single closed just going to get post and then I want to put the actual ID in the URL so we're going to say : ID so we're going to pass it as a parameter all right and then for our query I'm going to be putting a variable in here so I'm going to use back ticks instead of quotes because that will allow us to put a variable in and we're going to say select all from posts where let's say we're ID is equal to and then since we're using a template string we can use our money sign and curly braces to put a variable and what we want to do is grab on to this ID that's in the URL and to do that we can say requests dot params dot ID okay so it's going to select from that that ID and then for the query we're going to go ahead and run that and let's just change that to result since it's just going to be one and then we're going to console.log the result and I'll say post fetched alright so let's go ahead and go to Chrome and we'll go to get post slash one post fetched if we look down here there it is alright if we go to get post / - there it is post - alright so we can now select a single post so next thing we're going to do is an update say update post and I guess we'll just copy this okay so let's say let's change get post to update post / ID and then we're going to change this to let's see what I do in here let's first specify what we want to update so we'll say let let's say new title we want to change the title and we'll change it to the text updated title alright and we're going to say update posts and then set we want to set the title equal to new title and we're going to do that we're ID equals whatever is in the URL all right and then for the result that's that's good we'll just say post updated and save okay now we're going to go to update post and let's let's update post - okay what's this looks like we get an error in the syntax so update posts set title equal to new title all this has to have quotes around it like that all right so let's try that again post updated and now if we want to check that out we can just go back to will say get post to and now you can see that that title has been updated all right now the last thing we're going to do is a delete so we say delete posts and I'll just copy that and we'll change this to change the route to delete post slash ID and then we're just going to do right here delete from posts where ID equals that ID and log the result post deleted save and now let's go back to our browser and we'll say slash delete post and let's delete post to post deleted and we want to check it out we could go back we could go to get posts but if we look at PHP myadmin you'll see that it's been deleted all right so this should give you kind of a premise for creating crud applications or even or we could create a restful api pretty easily going by what we've done here so that's it guys hopefully that helped you out for the people that have asked me how to integrate MySQL I mean there's a lot of different ways to do it but I think this is one of the easiest sequel eyes does give you a lot more features but it's also a lot more complicated but that's it thanks for watching guys and I will see you in the next video

Channel: Traversy Media

Views: 386,340

Keywords: node.js mysql, mysql, mysql node.js, node mysql, node.js sql

Id: EN6Dx22cPRI

Length: 26min 13sec (1573 seconds)

Published: Thu Jul 20 2017