MySQL in Node.js Tutorial | CRUD and Creating Databases

hello everyone and welcome to this tutorial so today what I'm going to be showing you is how you can interact with a MySQL server in node.js so I'll be showing you how you can create your database also post and retrieve next and file data from that database as well as update and delete that data so to show you this in my local environment I'm going to be setting up an SQL server using the xampp software so if you download and install this you can easily set up a MySQL server on localhost by first starting the Apache server and then adding MySQL what this gives you is MySQL server running on localhost that you can view via the PHP my admin interface now if you want to deploy this live and you have shared hosting usually this will support zpanel and if you have that then you should have access to the PHP my admin interface there and you can access user privileges or accessing databases via MySQL databases tool in cpanel are for learning purposes I'll be working with mySQL running in my local environment so the host is going to be localhost and the username will be root so the project folder that I'm going to be working with I've already created it here on my desktop and in my terminal I'm going to CD into it and create a new node project inside a bit so double flag y accepts all the defaults or a new project now I'm going to install a few packages the first is MySQL it's a client or node.js that allows you to work with mySQL databases next Express because I'm going to be setting up some routes that I'll be sending some requests to and it's much quicker to do that with Express than it is in pure node.js finally the course package for Express so I can manage access to the app that I create now if we have a look at the project folder that I already have open in Visual Studio code you can see that those packages are now listed as project dependencies so I'll now start coding my node script first thing that I want to do is import those packages that I just installed so MySQL Express and also the cause package or Express so I'll create a new instance of an Express app and by calling pause inside of app.use I'm specifying that requests from any origin are allowed and I'll set it to listen on Port 3000 now for connecting with mySQL via the library so you call create connection on it then you pass into here an object containing the connection information so in this case the host is localhost and the user is root now in practice you'll probably want your access to mySQL to be password protected so I haven't set a password yet but you can do that by the PHP my admin interface so if you go to user accounts and then localhost change your password and I'll set that to Secret so you can see that we're getting some error messages and that is because the password or accessing PHP my admin has also changed so set the password that we're using to access PHP my admin you can access this file here and set the password value to the new password now when I refresh we're not getting any errors anymore because we're logging in using the new password now I'm going to attempt to connect to the MySQL server and you could do that by calling the connect method on the connection that you've created and inside there pass in a function that will run when the connection is established or it fails so if it fails an error will be passed into it and you want to check that error first of all and if there is one dot code execution by throwing an error if there isn't one then the connection has been established and this message will be logged to the console so let's check that this is working stove bar by running the app so a connection has been established and we can move on to creating a database so to do that we want to run a MySQL Query which you can do by passing it in as a string to the query method on the connection so the query is going to be create database put it by the name that we want to give to the new database after this query has run the function in the second argument position will run and if there's an error we need to check for that if there isn't one then we'll get past the if block and I'll just log a message to the console so let's see if this works I'll restart the server so we are getting the database created message and if we take a look databases on the left hand side here when I refresh the page the new database now appears there so no table exists in the database yet so let's create one but first I need to connect to the database that I've just created so you can do that by calling change user on the existing connection and then passing in the updated details to edit the connection so information that I don't specify from the original connection will remain the same all the new connection so it's just the database value that will be updated and if that successfully completes without error then I'm going to call a function that I will Define old create table so I'll Define it outside the connect method because there's quite a lot of nesting now so inside here or make another SQL query this time to create a new table I'll call it users and this is going to be a multi-line string so I'll change that to back ticks so in here you specify the type of data at the table will support so it will have an ID value that is an integer it will increase by one each time it shouldn't have a value of null and it should be unique I set the primary key the next column will be all the user's name which are set to a value a correct value and the final column will be where the file is stored so in this case a photo so if there wasn't an error in creating this table then a log a message table created now if you were to run this code it wouldn't work because a database with this name already exists and if you try to write another database on the top of it row and error so you can solve that by specifying in the query that it shouldn't create a new database if it already exists and I'll do the same for the table because we'd run into the same problem with that after the code let's run more than once so let's run the code to create the table so we're getting all of the expected console logs and if we take a look in PHP my admin refreshing the page we now have a new table in our database with the three columns and we can now start posting data into the table as rows of data so I'll create here a post endpoint or the express app that I created earlier and inside here I'm going to make a query and that's going to be to insert some data into users and if I specify that question mark then I can pass in the data here in an object as the second argument so the two column names are name and photo now the photo I'm going to move this image here into my project folder so that's now here in the root of the folder now I'm going to want to access the file system to get that image so to do that I need to import the native the whole system module and use that to read the image so it's image dot jpeg now the result of reading the file is going to be an array buffer but post it to the mySQL database it needs to be in buffer format so to do that you can just wrap the result in buffer Dot from and we still need to add the Callback function as a third argument in which we burst check for the error if there's no error then the data has been successfully inserted and because this is taking place inside a post request all in the request at the end of the function now I'm going to restart the app and post to the database we need to make a post request to localhost Port 3000 forward slash API so I'm going to be making this in vs code using the thunderclient extension so if you don't already have this extension you can install it via the extensions Marketplace so the request will be to pull 3000 forward slash API and it will be a post request so we're getting a 200 success status code back and in the terminal we're getting the record inserted message I'll just post one more time so there's two rows of data now if I refresh in PHP admin we have two rows of data those containing a name and a file and you'll notice that even though the content is the same for name and photo because we set an ID that is auto incrementing there was no clash between these entries so this is how you can post data to the table in the database now let's cover or you can get data from the database so for that I'll create a get endpoint at forward slash API so when a request comes through to this endpoint we're going to run a query and select everything using the wildcard operator and that's going to be from the users table now the result of selecting this will be available as a second parameter in the Callback function so we still need to check or the error first and after that we can log the result which will contain the data and after that into the request so I'll restart server and the request two same URL and if we take a look what's been logged to the console you can see that main data was successfully retrieved the photo file data is in the form of a buffer so to check that the image data was successfully retrieved I'll select the photo data from the user's table and the one with the ID of one only so let's check what output gives us so it's inside an array in an object on a property of photo so that we can check that the photo is being returned successfully I'll just paste in some code here that's going to set some headers on the respond to link the browser to display the photo on the page also specifying a file name and the file type and finally send the image to the user so it's on result it's the burst element in an array and it's on the photo property so if I restart the server and we make a requests to Port 3000 API in the browser now we get an image as a response that is the image that will be posted to the database so we're successfully retrieving it now or updating records and deleting them so updating a row is very similar you posting the new row so I'll copy it and change it in the relevant parts so the query is going to update the user's table with some new data and I want to do that for the first row or change the name so the change is visible and update the console log message apart from that no changes aren't required so any request this time an input request and we check the data in the table we should find when I refresh it that the data for the row with the ID of one has been updated and indeed we see the updated name appearing in the table now finally let's cover how to delete row from the data table so to do this will create a new delete endpoint so the query this time is going to be delete from the user's table followed by the ID of the row to be deleted and it's fairly straightforward with a delete query because we're not posting any data and we're also not retrieving data from the database so all we need to do is check the error and then end the request now the last time I'll start the server again and make a delete request so I'm getting back success status code now you see that row with the ID of two has been deleted from the table so that is it for this tutorial I hope you found it useful if you did please consider hitting the like button down below this video click this with the algorithm and others to find this video and if you'd like to see more content like this from us in the future don't forget you can subscribe to the channel foreign

Channel: OpenJavaScript

Views: 3,499

Keywords:

Id: xwfeik3bPpw

Length: 20min 42sec (1242 seconds)

Published: Fri May 19 2023