Node and Express - Connect to MySQL Database with Sequelize

hey everyone and welcome to the next video in my express tutorial series where here we're going to be talking about how to add persistence to our express application by connecting it to a mysql database specifically we'll be storing user credentials so username and password that were filled out in a login and signup form we will do this by using a popular orm called sqlize we'll also go over what sqlize and mysql are and i also just want to mention that all the code for this video as well as a blog format can be found on my website where i provide the link in the description but to begin so what is persistence well in programming persistence means saving data for future use and this is typically done with a database and a database is simply a collection of structured data a common example of persistence in an application is saving user information such as username and password when a user creates an account when the user visits the application and logs in we compare the provided login credentials to the ones stored in the database and so to connect to a database with our express application we are going to be using sqlize all this is is an npm package so let's install it with npm install sqlize and so let me also go over what sqlize is so sqlize is a promise based object relational mapper or orm library essentially sqlize allows us to write sql queries with javascript which is great because express is in javascript or node and then of course it makes it easier to also write our sql queries in the same language and i've also been using the term orm or object relational mapper a lot so what really is an orm well orm is a technique that allows us to query and manipulate data from a database in an object-oriented way and in orm the object is the object we will create with javascript relational is the rdbms that we will use for us this is mysql and mapper is the bridge between our javascript objects and mysql tables and so as stated earlier we're also going to be connecting using sqlize to connect our express application to a mysql database well what is mysql well mysql is a relational database management system or rdbms and in rdbms it's a type of database management system that stores data in rows and tables and these tables often have relationships with each other for example our application could have a users table and a post table and relationship between these tables could be that a user owns a post as say they wrote it and now to use mysql with sqlize we need to install the mysql driver and the mysql driver is an npm package called mysql2 so we'll do mpm install my sql2 and let me also show you how these using npm install will add this to our packs.json dependencies so we can see sqlize and we can also see our mysql driver here and now to start storing information we need to create a database and we will create a database using mysql workbench and if mysql workbench or mysql for that fact are not installed on your system they can be installed by watching a video that i'll post description or that uh with a link that i'll post in the description but for this video we're going to be using mysql workbench to interact with mysql which i have installed here if you don't have installed follow the link in the description to get it installed and so now what is mysql workbench well mysql workbench is a gui for working with mysql so instead of using the command line we just get a nice interface here and mysql workbench integrates database design creation maintenance basically all the basically everything you can do into an easy to use gui so essentially mysql workbench is a gui for the mysql server and so what we're going to do is we're going to click on one of these connections that we've created which you will do in a video in the connection in the description or that link that takes that video you'll get all this set up but so before we begin storing data from our application we need to first create a database and we will do this with mysql workbench and to create a database we click on this button here which is create schema and so a schema in mysql is um synonymous to a database essentially but so when prompted we're going to name this schema my express app and then press next until we get to the end so we're going to name it my express app and then apply and then just apply close and now before we connect to this schema or database that we made let's create a configuration folder and place a file called db config inside and so this configuration folder will hold all of our configuration and one of er will hold all of our configuration files and one of these files dbconfig.js will contain all the configuration information for connecting to our database so let's create all these constants that we're going to need but so specifically these variables that we've created here will be passed to sqlize to form a connection to our database and note that the user and password values of this exported object might be different depending on how you set up mysql so in that video i sent how to set up mysql in mysql workbench the you should be using the username and password that you configured here and also notice how the database name by express app corresponds to this schema that we made in mysql workbench so my express app and now let's go back into our express project and now the creation of our database connection and mysql tables will all be done inside a folder called models so let's create a top level folder called models and so this models folder will hold our database connection and mysql tables if you're wondering why i called it models let's talk about what a model is well a model is a sqlize term that represents a table in our database for example if we have a users table in our database we can represent it with a sqlize model called users and whatever changes we make to our sqlize model will be reflected in our mysql table now let's add two files to our models folder index.js and users.js so this index.js file will instantiate our database connection and this user.js file will house our user model but now let's connect to the database we made inside mysql workbench and to do this let's import sqlize in our database configuration file into our model-index.js and so to connect to our database with sqlize we first need to create an instance of sqlize and then provide it some information about our database so to our sqlize constructor and you could say it's a constructor because we're using this new keyword here what we do is we first pass the name of our database and this is the name my express app that we gave our database when creating it with mysql workbench next we provide sql ads the username and password to access our database these will be the username and password that were configured when setting up mysql so username and password we then pass sqlize the hostname of the database and as this database is on our local machine the hostname is simply localhost and finally as this is a mysql database we need to let sqlize know by providing it the mysql is the dialect if we go back into our which follows it dbconfig we can see our dialect is mysql and now we want this database connection to be accessible to the rest of our application whenever we need to work with it and as such we want to export our sqlize instance and we will export an object called db from this file so let's attach our sqlize instance to it and we also want to export each sqlize model we create and remember a model is a representation of a mysql table for example this table or this application will have a table of all the registered users so let's also export that so the file user.js will represent our user table and it will export a function that takes two arguments sql our sqlize instance and sqlize data types and these data types will be used to set the data type of our user model columns so you'll see more of this soon but now let's export this db object that we made so now let's work on creating our sqlize user model so let's go into user.js and this model will hold the data for our registered users we will create this model inside this model-user.js file and this file will export a function that returns our user model so remember this sqlize and data types will come in from here when we require this user file which gives us the function and then we pass in our sqlize instance and also sqlize data types so let's go back in here and let's use the past and sqlize instance to create our model and we can create a model with sqlize using the method define and so the first argument supplied to sqlize.define is the name of the model and we want to name our model user the next argument is an object describes the columns in our model so this right now we just have an empty object and the third argument is an object that can be used to customize our model even further but so let's give our sqlize model some columns and in our user model we want two columns we want username and we also want password and we also want each of these columns to be strings so we pass an object as the second argument to sqlize.define where each property this object is a column specifically the key is the name of the column and the value is the data type that the column will be for example if we also wanted to have an h column what we could do is we would add age and then data types dot integer but we only want two columns which we want password or username which will be a string and password which will be a string and now something sqlize does automatically is pluralize the model name we provided for example the name user that we provided here will be transformed to users when we look at it in mysql or mysql workbench and if we don't want this to happen we can customize our model by supplying properties to the third object argument so the property freeze table name when set to true will tell sqlize not to pluralize our model name and now the last thing we need to do is return this sqlize model so we'll do return user which is essentially a table and then what we do so we've created user it returns this user table and then we attach it to our db object under models and so now let's sync sqlize with mysql so we've connected our express application to our database and we've created a sqlize model now we need to tell mysql to create a table in our database using this sqlize model and we want to do this inside our main index.js file so first let's go over to our main index.js file here and let's import our sqlize instance so remember this is the db object that we exported from models index.js and attach this object is our sqlize instance and all the models that we created and now to sync up our sqlize models with mysql we need to use sqlize.sync and this is an asynchronous method so what we're going to do is wrap it with an async iife so we can use the await keyword so down here let's do it right here and real quick what is an iife well an iife or immediately invoked function expression is a javascript function that runs when it is defined so this will basically define this function and then run it and ifes are often used to avoid polluting the global namespace so now all we need to do is run our application and i actually need to switch my node version to a different one but i believe it's been a while since i've looked at this i'm trying to remember if we have a script no so what we're just running is nodemon index.js i believe and we can see i got a sqlize access denied error so i must have gotten the password wrong so i need to figure out what that actually is and so i remember what my password was so now we're good to go so we can see right off the bat that what this does here the sync method this is actually going to run some mysql code and we can see the code here create table of not exists user we can see some columns it's making an id we're using our username that we supplied password that we supplied and then some other ones that we'll get into what they are in a second but now if we go into mysql workbench and i think i might need to refresh this if we open up tables we can see our user table right here and even more we can see the columns and we can see username password so now let's talk about these extra columns in our mysql table so when inspecting these this column right here there are three columns present that we did not make and these are id created at and updated at and the id column will be the primary key of our table we can create our own primary key column but it is simpler to let sqlites do this for us for this example and this default primary key is an integer that will increment by one with each new row really quick if you don't know what a primary key is a primary key is essentially a special column in a relational database that uniquely identifies each row in a table so each there's can't possibly be two same ids in a column essentially if it's the primary key and now let's talk about this created and updated at columns so the created at column lists the time a row was added to the table and the updated at column lists the time a row was most recently updated and we can also choose to not have the created at and updated at columns by customizing our sqlize model so if you remember where was it in here where we added freeze table name we can also add like timestamps false or something um if we don't want those in but there's also no harm in having those there and now something real quick i want to mention is that this application we're building is an mvc design and so we're said what this means is we're setting up our application with a model view controller design pattern so i'll go over this more in a sec but for now let's create a folder called controllers and add a file user.js and an index.js file and so what is an mvc well model view controller or mvc is a software design pattern that separates the program into three parts the model the view and the controller and the model is the part that works with the data the view is the part that works the ui and the controller is the interface between the model and view that manipulates the data and also interacts with the ui and in this framework the model is our sqlize models the view is our pug templates and the controller is what we're about to make now so let's create our user controller so the controller user the controller file user.js is going to be responsible for anything related to users in our database for example removing a user from the database would be performed by our user controller so it adding a user updating a user and everything like that and as our user could.js controller interacts with our user database we need to import import our sqlize model or our sqlize user model so essentially what this does is let me also go back into where is it our models index.js we are essentially obtaining this here dude.models.user by using some kind of complicated um destructuring i wouldn't recommend doing this because i think it looks messy but for now i'm just going to leave it like this but so now we have our user model and what we're going to do with this file is export an object containing all our controller functions and as of now we're just going to be creating to be creating a method to add users to our database so now you might be wondering where this request and response comes from in this function well the request and response objects that this method takes will come from a post call to our login route and when a user submits the login form the login post route will be called and we will pass this function as middleware to the route and as the request will be a post request we know that the data will be inside the request's body so let's first check if the username and password are filled out and so if both the username and password are present then we want to add that user to our database and we want to do this with the sqlize model create method so this sqlizeycreate method what it does is adds a row to our table in our database and this create method is asynchronous so we need to wait for it to finish and after the user has been added to the database we want to render their profile page and pass it their username and i believe we have the pug file called profile excellent so we have profile and it takes user name if you remember that from a previous video and now on the other hand if the username and password were not entered or not present in the request let's just send a message to the client saying not added to the database and now let's add our user controller to our controllers.or controller's index.js file so this isn't necessary as we can directly import our user.js file of course but in this case we will use index.js to keep track of all our controllers at once so let's place the following inside this index.js file and now let's add our user controller to our dash login post route so let's add our user controller create function the one we created here to our login post route so first let's import our user controller by using object deconstruction so let's go into our routes leave its login and let's import our user controller and now let's add our create function to this post route and you can already see how much cleaner this looks as well which is what i like about doing it this way but so now anytime a post request is made to dash login we will call our user.create function here and so in other words every time a post request is made to dash login if your username and password are present in the payload that user will be added to our database so now all we have to do is run our application and submit that form we made in a previous video and i believe we are on localhost one two three four i think it's dash login here's our login form let's also add a network tab so we can see this request in action and i'm going to do username with code password subscribe let's send it off we can see a post method here we got wicco's profile which is the username pass in the body which will be passed to where is it our profile or our login up sorry which one was it profile.pug so this is passed here which comes from this create method because it renders this profile pug and passes in the username that the user entered and now for the real important part let's go into this make sure we double click the right database so it's bolded let's do select all from i'm used to postgrade so i was going to use comments but to from user might actually have to sorry from user like this and you can see what we have now is what code subscribe the created at and then update it at stamps as well and the id so we've connected officially to a database let's do a another user let's do dash login again and now let's do bruce password is wicco.com send off this request if we look at our post method we can look at the payload we have username password this is of course another reason why you'd want to encrypt because you wouldn't want your password visible in the in the open like this but anyway if we go back into here we select all from user again now we have bruce password waycode.com so we've officially connected and it says bruce's profile so we've officially connected our sqlize or sqlize or our express application to sqlize but so that's all there really was to this video um in the next one i believe we're going to start learning about cookies so be on the lookout for that if you're interested um but besides that i just want to thank you all for watching if you have any questions leave them in the comments um besides that i'd like to thank you for liking subscribing today and i hope to see you in the next video have a good one

Channel: WittCode

Views: 16,478

Keywords: WittCode, wittcode, express and sequelize, express sequelize, express mysql, express connect to database

Id: pKhdNPN4b1g

Length: 22min 54sec (1374 seconds)

Published: Sun Apr 17 2022