Learn Sequelize - A NodeJS ORM | The Best Way to Write Database Queries

hello and welcome back to the immigrant programmers channel my name is kritika and this video is the first in our series on orms so in this video we're going to learn about sqlize and to tell you a little bit more about orms they're called object relation mapping and what that means is these are libraries that are used to do all the heavy lifting in the background for you so that you don't focus on database queries and you focus more on your node.js code and orms convert database queries into easy to use javascript objects and in this video we're going to talk about sqlize and sqlize is an orm for sql based databases so without further ado let's get coding now wasting another second let's start learning sqlize right away so i've already prepared the project structure for you so that we don't waste time and we concentrate on the main stuff here so first of all what we need to do is we need to install the sqlize package and another package that is used by sqlize which is called mysql2 so i'm just gonna go npm install or you could also just go in pmi and dash dash save because this is gonna be a production dependency it's a core dependency of your project and then you type the name of the package so sqlize oops okay or sorry and you need another package which is mysql2 or my sql so go ahead and hit enter if you're doing it with me right now i'm not going to do that because i already have these package installed so let's move on to the next step so in order to use a database you first need to establish a connection with that database so that's exactly what we're going to do here in the database.js file so i'm going to require this equalized class from the package sqlize and i kept the s capital here deliberately because this is a class or a constructor function so i'd like to follow the industry practice okay the next step that we do is we create a new instance of this class so this sqlize here is a new instance that i created and now whenever you use the sqlize constructor you have to pass it takes like certain options that you need to pass so the first one here is the name of the database that you need to connect to the second one is the the user name which is root and the third parameter that it takes is the password so i expect you to put your username here and your password to connect to the database and now the fourth parameter that i used here is optional but you can pass on a javascript object here and in that object you can define certain things so for example this dialect field here explicitly tells sqlize that we're going to talk with the mysql database as different sql databases or engines can use a label of a different structure now the second field that i use here is the host field so i explicitly defined i explicitly told sqlize that i'm gonna use my localhost but you don't necessarily have to do that because the default value of this host field is localhost okay so next i just want to export my sqlize instance because i want to use this instance in other files to call different sqlize methods and now that we have established a connection with our database the next thing that we're going to do is create models so there will be two different entities in our database the first one would be customer and the second one would be orders so each customer can have multiple orders so to say it in sql terms we have two different tables a customer table and an order table so now the customer table will have certain fields and we need to define these fields in the models now how do we do that it's right here so we use the defined method on the sqlize instance that we just created in the last step now this defined method takes two parameters the first is the name of the table which is customer and the second is an object describing each of the fields of this table so my customer table has an id a name and an email field so let's describe how each of these fills is gonna look so the id field in the customer table that will be the customer id it's gonna have a type of sqlize.integer so we can use the data drives provided by sqlize just by calling the data type on the sqlize class now we can provide some other information as well so for example i want my id field to be an auto incrementing field i won't allow null values and i want to make it as the primary key of my customer table now this right here should give you a better understanding of the actual orm functionality we're literally mapping sql tables into javascript objects here okay now let's move on to the name field we would like to describe this name field and it's going to have a type of string and we're not going to allow now values here as well and same with the email field so i now have my complete customer model and i would like to export it now okay now we're gonna define the model for the second table which is order and we're gonna do it the same way we did for customers so we're gonna use the dot define method on the sqlize instance and it's going to take the name of the table as the first parameter so which is order in our case and as the next parameter it's going to take a javascript object now the order table has two different fields an order id and a total purchase price so i define the order id pretty much the same way as i define the customer id so the type is integer i wanna auto increment it and i won't allow null values and i want to make it the primary key of the order table the next field is the total field so this will have a type of integer as well or you know what you can make it as a float too if you want so it's up to you and i don't want to take null values here as well okay now i have my order model ready as well so i'm going to export this okay so now that we have an established database connection and we have also defined our models it's now time to sync our javascript models to actual sql tables and how do we do that we use the sync method provided by sqlize pretty straightforward huh okay so i'm gonna call the sync method on my sqlize instance and then i'm gonna see the result that it returns i'm just gonna console.log it and i can also catch any errors that occur here and i will also console of the errors perfect so this looks good now and if you're wondering how calling the sync method on the sqlize object will actually create the tables for you it's because when we define our models we use the same sqlize object here so the sqlize object knows about all your models about all your relations if any and it will translate these models here the customer model in the order models into actual tables okay so without further ado let's run this code and see what happens and there you go i'm going to expand this so you can see the entire object that i received here so it executed some queries and as you can see there were the create table queries and the customer table and the order table was created perfect so let's go take a look at mysql workbench and see if the tables were actually created okay so i am here and i'm gonna refresh all the databases that i have and if i go inside tables i see my customer table and my order table perfect so it works okay now it's time to populate these tables so we're going to do some insert queries and we're going to do some select queries using sqlize okay before doing the queries i think i forgot something here so we know that there exists a relation between our customer table and an order table so we need to define this relation here using sqlize so that the sqlize knows about this relation and takes appropriate actions in the table okay so what's the relation between our customer and order table think about it a customer can have many orders so there will be a has many relation between our customer and the order so how do we define it we go customer dot has many oops with a single y and then order so that's how we define the one-to-many relation between our customer table and our order table now in order to define a relation between two sql tables you need a foreign key so here what's sqlize will do is now that it knows that the customer and the order table is related with a one-to-many relation it will automatically create a foreign key called customer id inside your order table and how do i know that because it's present in this equalized documentation and you're going to know that too in a second so if i go to google search and i just type sqlize documentation and click on the first link i'm using version 6. so i'm going to go to the documentation for version six and inside here we have information about how to define the models how to query and also we have information about associations right here okay so inside association there can be four different types of association has one belongs to has many belongs to many and we have used the has many type of association so if i go down here it says whenever we use the a dot b sorry whenever we use the a dot has many b association which we have in our case which is customer dot has mini order the association means that a one to many relationship exist between a and b so between customer and order in our case with the foreign key being defined in the target model b so that is the reason the target the foreign key was defined inside the order table and let's go and execute our code and see if that is the case okay so we don't need to check the response because we already saw it in the last step and it's going to be the same let's go to mysql workbench and refresh it and see if we have the foreign key so if i go and expand my table so my customer table has the fields that i define id name email and these are the default field added by mysql created at an updated act and what about the orders table let's see the fields here it's id total created and updated that well it doesn't seem to have the customer id foreign key here okay let's see what's wrong so there's a little trick here because i already use the sync method and all my tables have already been sent and i did not define the relation at that time so instead of using the sync method now what i'm going to do is i'm going to force sync it so that my tables are going to be created again so when i use the far sink what i do is i just go and pass an object inside the sync method and i pass the field force and i set it to true now what this will do is if there exists already created tables it's going to drop those table first and then it's going to recreate those tables with the new information so that's how i first send my sqlize models so now i'm going to run it again and let's see if i have my foreign key now so the customer table is still the same perfect and the foreign key was supposed to be added to the orders table and it is present so if you see this customer id foreign key sqlize added this automatically because we just defined the relation that we have between the two tables isn't that great and you see here in the result as well that when i use the first sing i first see a drop table query before any create table queries so that's how you know that your tables are literally created all over again when you use the first thing okay so who's excited to populate the customer table by creating the first customer i know i am and how do we do that is we call the create method provided by sqlize on our customer table or the customer model and inside this create method i pass on an object which take the fields that we define in the customer model so we define the name let's go back here and see that we define the name and we define the email we don't need to pass on the id field because this is an auto increment field and it will be generated by default so let's go ahead and pass the name of our first customer so i'm gonna pass on chandler bing a big friends fan i've watched it like i don't know how many times i don't even remember now okay so i'm gonna pass on the email as well so cb gmail.com oh wait it shows me an error what am i missing here oh i actually missed out the field names so i'm gonna pass the name here and email because that is what i define here remember the name of my field is name and email and that's exactly what i'm gonna pass here now i can do a dot then here and see what the result is but i'm not going to do that i'm going to do a promise chaining you're going to use a better approach i'm going to return this promise from here and do a dot then outer um so now i will receive a customer and i can console login perfect so we have successfully implemented our first insertion statement using sqlize let's see how it works out so i'm gonna go ahead and run the program okay let's take a look at the result first and then we'll see we'll go to workbench and see if it was actually inserted okay so by the looks of it i think the insertion query actually it was triggered at least i can see it here in the log and then i see my return my console log first customer created and this is my customer object the name the email the id and updated and created that field okay let's go to workbench and refresh all the tables and all the databases and see if i have the first customer inside my table so customer and there you go it was successfully inserted awesome let's go ahead and insert an order now so right here i have my customer in the dot then block and now i'm gonna insert an order that this customer placed so i will go order dot create and let's go check out what we need to pass to create an order so we go to the order model and we check that there's just one field which is called total okay so we're gonna go and just pass the total field inside our object and i'm gonna put 45 here and now i'm going to do another promise chaining so i'm gonna go ahead and create another dot then block and i will now receive the order and i'm gonna just console log the order let's run this okay so we take a look at the result and i have the customer and i also have the order and now that i used the first thing so you know now my customer table and the order table must have been deleted first so the entry that i just bought in the last step was deleted as well and they were recreated so my entry here chandler bing my first customer was added again and as a proof i can show you that the id is still one perfect and now we have the first order placed by chandler wing as well and let's go and take a look in the database okay so what we got here we got our first customer chandler bang and the id is still one it's not two so this is the first customer and we have the first order here but do you notice something odd here do you see the customer id field is null so how do i know that this is the customer channeler bing is the customer who plays this order i have the customer id field but i have not populated it so that means i've just inserted an order but i have not related this order to my customer so what did i do wrong huh okay so let's understand now you see that i've inserted a new order by calling the create method on the order object but what i wanted to do instead was to add the order placed by this customer here chandler bing and how do i do that so for starters we know that we have this customer object here so i must use this customer object right to create its order and now this is where you'll realize the beauty of sql eyes oh it rhymes okay so the point is that i have already have a create order method provided by sqlize on my customer object since i have the order table and i have the customer table and sqlize knows that there is there exists a relation between them so it gives me a create order method that i can invoke on the customer object and i can pass on the total price here and now i don't need this i'm gonna return this instead and everything else stays the same and now let's see what happens so let's go and have a look at the customer table and orders table so customer correctly added an order is correctly added as well because i see the customer id is filled with the id of the customer who plays this order that was one so that's how you use equalize to not focus on the sql statements to focus more on your code on your core node.js code now what should we do next let's select all the orders placed by this customer so in order to do that we use the find all method on our order table because we want to return the select statement from our order table so since we want to find all the orders associated with this customer right here it takes aware clause as well and in the where clause we're going to pass the customer id so it takes an object and i'm going to pass the where clause and i want the customer id for that but do you realize that i don't have access to my customer object here so what do i do i'm gonna create a variable customer id and i'm gonna store my customer id in this variable and access it here in my where class and to do that i simply need to assign the customer id to this customer id variable right here when i have my customer perfect so i've completed my select from where sql statement using sqlize and now i'm gonna return this and we could do another then block here and i'm gonna receive all the orders here and then i can console log these orders as well and i'm gonna go ahead and run the code awesome so do you see the select from aware sql statement executing here and now i have my console log all the orders and i have the orders so there's just one order because we just inserted this one order for 45 dollars for our customer channeler bing who has the customer id one so that has been returned correctly awesome so we have completed our sqlize tutorial with this we have connected to the database we have created the models we've actually seen those model and translated them into actual sql tables and we have defined the relation between these models and also we have executed some insertion statements and some select from where statements if you have any more questions i would highly encourage you to go check out their official documentation right here and i'm going to put a link to it down in the description below and i'm also going to put a link to my github repository and you can go and check out the code if you need help you can refer to it and stuff so i'll see you back at the studio for the outro so thank you so much for watching this video if you liked the video hit the like button and press subscribe if you haven't already subscribed to the channel share this video with your friends and family especially whoever is interested in learning about javascript and we will keep creating more content for you thank you have a great day bye

Channel: Kritika & Pranav | Programmer Couple

Views: 9,058

Rating: 4.9130435 out of 5

Keywords: javascript, the immigrant programmers, programming, kritika, programming news, coding, trending software, developers, web development tutorials, web development news, coder girl, programmer girl, developer explains, learn programming, learn web development, learn coding, learn javascript, orm, sequelize, sequelize nodejs, sequelize javascrpt, sequelize database, sequelize sql, sequelize mariadb, sequelize mysql, sql queries, sequelize setup, sequelize crud, crud nodejs, SQL CRUD

Id: ExTZYpyAn6s

Length: 22min 2sec (1322 seconds)

Published: Sun Apr 25 2021