NestJS, MySQL, TypeORM Crash Course

all right welcome back to a brand new nest js tutorial in this tutorial you're going to learn everything that you'll need to know to get a simple nest js project working with mysql and type orm so this will be a very in-depth crash course we'll cover everything such as reading from the database creating items in a database or i should say records in the database updating records deleting records you'll learn about one to many many to one relationships you'll learn about one-to-one relationships with type orm i'll show you everything that you'll need to know to actually build incredible crud applications you'll also learn about how to use the repository api with type or that's a very important fundamental concept that everyone that needs to know uh everyone needs to know that that wants to use typo ram so you'll learn about that as well so um let's go ahead and get started so let's just first create our project so i'll call it uh an sjs my sequel or let's see his tape or my sequel tutorial and we're just going to generate this project alright so our project has finished installing so now let's just cd into that directory so cd and sjs type on my sql tutorial and i'm going to go ahead and open up visual studio code okay and then we'll have all of our generated files which is okay we'll create a new module later on but what we're going to do is we're going to install the necessary the necessary packages so before we even continue make sure you have my sql server installed otherwise you won't be able to actually continue with this video so i'm assuming that you already have my sql installed that is the mysql server right you need to make sure you have that installed have it running in order for your code to actually connect to the database otherwise it's not going to work so there's plenty of videos on youtube there's plenty of guides on google on how to install mysql server so just go ahead and find one that fits uh fits your uh suitcase i do have a video on my channel you can go ahead and look that up i'll leave a link in the description as well if you need it and um yeah also i would also recommend making sure that you have the mysql cli tool installed as well so that will also help a lot but you could also use my sql workbench but in this video i'll be using the my sql command line anyways so we're going to need to install a couple packages so inside my terminal i'm going to go ahead and type yarn add and then the three packages so at an sjs slash type rm so this is the type rm package well it's the wrapper for sjs then we want to install the actual type or package and then we want to install my sql 2. now there's actually uh i think this is yeah i think this is all we'll need so we don't need to install anything else so let's install that okay so that should be almost done in just a second perfect uh it's almost done okay so now that we've installed our packages what we're going to do is we're going to connect to our database so the first thing that we'll do is we'll go inside appmodule.ts or app.module yes that's the root module i'll leave alone the app controller and the app service i don't really care about those files right now but i do want to import the type or a module which comes from the at sjs type orange package so let me zoom in a little bit so you can see that this import comes from at sjs slash type orem and then we're going to go ahead and call for root and then we want to pass in options so these options are basically going to consist of our database uh credentials okay as well as other type or specific uh configurations so the first thing that we'll set is the type so the type will be mysql because obviously we're using mysql as a database okay um the next thing is going to be the host so i am using a i'm using localhost so i'm going to type localhost but of course if you have a managed database that lives on the cloud you can also use that as well or if you have a mysql server that lives on some other you know computer and you know the domain or ip address you can use that as well uh the port is going to be 3306 that that's the default port for my sql but make sure if you have configured the port to a different port when you installed my sql make sure you provide that port for username i'm going to provide my username so that is going to be test user password will be my password testuser123 okay and for the database we're going to need to create this database which i'll show you how to do that so i'm going to call this uh an sjs sql tutorial or mysql tutorial okay and then entities so this is going to be an array later on when we create entities in type orm in our code you'll understand what this is for but for now just leave it like that and finally we'll set the synchronized property to true and basically what synchronize will do is every single time we modify our entities it's going to go ahead and basically auto update the tables it'll auto it'll auto create any new tables that is needed if you were to update like you know a field in your entity it would also update that in the table as the column as well you'll see how everything works when we when i open up the my sql cli okay but this is pretty much it for configuring type rm let's go ahead and run our application okay yarn start dev and we're going to get an error just watch yep it says unknown database nest js mysql and that's fine let me exit so we need to actually create the database so i'm going to go into my powershell and i'm going to go ahead and log into my sql so i'm going to use the powershell if you want to use my sql workbench you can do that but i'm just going to type mysql hyphen u so that's going to be my username hyphenp for password and i'm going to hit enter and it's going to prompt me for my password so i'm just going to type the same password that i provided in the type rm configuration which is test user 123 and then we're in and then i'm going to go ahead and create the database with the same exact name in sjs underscore mysql underscore tutorial so i'm going to type create database paste that in semicolon hit enter and it's going to say query ok which means that we create a database in order to actually step into the database and actually query its tables we have to use it so we have to type use sjs mysql tutorial and that's the name of the database and if i type show tables it's going to give me an empty set because we don't have any tables but that's fine let's just rerun our application now and let's just make sure that there's no errors and there shouldn't be because it should be able to connect to that database now that it has existed perfect all right so now that we have our database created our application is up and running it's able to connect to typeorm let's actually go ahead and create a type o rm entity so let's create a new folder called typeorm and i'll create a new folder called entities and this is where all of our entities will live now before we create an entity let's talk about what exactly a type orm entity is okay because that's something that you definitely need to make sure you are aware of so basically think of it like this right we're using mysql mysql is a database that consists of tables and columns right well type orm is an orm which stands for object relational mapper okay if you're not familiar with that i would highly recommend you read it on orms because they're very powerful tools basically the whole point of an orm is to take uh typically it takes you know things like javascript classes or types of classes right it depends on what language you use but in our case we're using typescript right it will take that typescript class and then what it'll do is it'll map it into the core a corresponding table with the corresponding columns based on whatever field you defined in that class okay so basically type o rm entities gives us a way to actually uh map an actual typescript class to an actual sql table all right and additionally type orm has a bunch of apis that allows us to interact with that database table so we don't ever have to worry about writing actual sql statements at all okay so what we'll do is this we'll go ahead and create a new entity and remember the entity all it does is it just represents a database table it maps to a database table so let's say we're creating an application that requires users to sign up so you would want to create something like a user entity because that makes the most sense okay and again if you're using other frameworks like you know spring boot they have their own ways of handling entities right or schemas and they have their own object relational mappers i think spring boot uses something like i think jpa i believe i'm not too sure i haven't used spring for a while but feel free to correct me down below if you are a spring boot user so let's go ahead and create a class called call user and the thing that we must do is use a decorator which is just a function and we want we must use the entity decorator so i'm going to import that from type rm and we're going to just provide a pair of parentheses after because it's just a function and you could pass in options if you want to entity options i typically like to specify the name and set the name to users because by default uh it'll create type 1 will create the table exactly as how you named the class so since we have plural obviously there's multiple users i like to have my table pluralized if that's even a word so um it just basically means that you know there's multiple users right i don't like having it as just user but that's personal preference anyways so we're going to go ahead and define a couple of properties now typically your sql tables usually will have a primary key so we're going to go ahead and use the primer generated column decorator and this will basically indicate that the field that we are defining is going to be a numeric value that auto increments and it auto generates auto it's auto generated okay so this is equivalent to the primary key for a sql table we'll define a couple more properties so for example i'll define a username property okay and in order for this property to actually map to a column in the sql table we need to annotate it with the column decorator like this okay so this marks it as a table column we'll go ahead and define uh let's do password because password is a common field and we'll mark it as a column with the column decorator and we'll do one more let's do um let's do something like created at and this will be a date a data type okay so this is how we can create a very simple entity it's literally just a class okay we define each of our properties that represents our user and each property if we wanted to map to a column in the table we must use the corresponding annotation okay um now if you wanted a certain field to be unique you could just pass in options in the column and you can set the unique property and the value you can set it to true if you want to if you wanted a column to be nullable all right let's say for example if you want a specific let's say for example you wanted a certain field in the user table to be nullable so let's go ahead and find something like um i don't know auth strategy right maybe they want to use google discord whatever right so this could be like a string right and you could set this column to be nullable which means that that value could be null so if you were to create a new user you would not have to you would not be required to pass a value for auth strategy okay whereas if you did not set it as nullable it would throw an error saying that doesn't have a default field default value if you want to set default values you could right so for example for the created at i can go ahead and set the default value to be new date so whatever the current date is okay uh so those are the basics when it comes to you know working with columns in your entity okay so let's go ahead and do this so let's take this entity and in order for us to actually register this entity set type r will actually synchronize the entity and map it to a sql table we need to pass the entity class as a value inside this array so i'm going to go ahead and pass user so that's going to be imported from type orm slash entity slash user just like that and then now you're going to go ahead and see that's let's see we have an error [Music] let's see what's going on uh i think we have a problem with auth strategy i think it's let me see i think it's complaining about the uh yeah i think it's complaining about the date um let me do this actually let me actually do this uh let me do this i'll i'll leave that alone for now and let me just restart the app i'm not sure why that's throwing an error though it's really weird but i think uh i think it just doesn't recognize the javascript date object similar to the sql one that that's my guess so it might be a better idea to you know use like a numeric value if you want like so instead of so like use like the timestamp instead of the actual date object but it's okay we'll leave it like that for now but again that's how you could set default values okay um so what i'll do is i'll go into my database or i'll go into the the shell right now and i'll type show tables and you'll see that we have a table and i'm going to describe this user's table and you'll look at the schema and you can see that it's pretty much identical i mean it's the same thing you have id type is int uh it is a primary key you can see that over here auto increment default null okay username varchar255 another thing that you could do is you could set the type so let's say for example right like we know that in sql there's many different data types one of the data types for integers are big ins so let's say if i wanted this column to be a big int because right now if you look at our table the id is actually whoops describe users the id is actually an integer rather than the biggins so maybe we might want to make it a biggent to support larger values right so i can set the type by passing the type property and set it to bigint and when i save and if i were to describe the table again you'll see that it is now a big hint okay so that's pretty cool all right so now that we've created our user entity let's go ahead and actually do something with this let's actually insert users into the database so what we're going to do is we're first going to create a module for users so i'll go ahead and use an scli so i'll type nest g module users so i'll generate a module and then what i'll do is i'll generate a controller or that module so slash users slash controllers slash users controller so this will create a subfolder called controllers inside the users folder because when we generated the module it created that user's folder for us okay and so i'll do that and then it'll also additionally create a user's subfolder inside the controller subfolder right over here as you can see right over here okay so now that we have now that we have our controller let's also create a service class and if you're a little bit confused with what i'm doing definitely check out my nest js crash course video it'll definitely help a lot and clear up all the things that i'm doing okay but i'm assuming that you already know the basics of nest so i'm going to go in and you also know like you know what controllers what services are et cetera et cetera so i'm just going to go ahead and just assume that you all know that okay so i'm going to go ahead and just create a service now so provide the path the relative path so this will go inside the use the user's subfolder services and slash users so inside users it'll create the services folder which will create the users folder for us okay and everything is going to be automatically imported and configured uh because we're using the cli tool so it automatically imports users controller and then adds that to the controls array likewise for the user service it'll add that to the provider's array so everything's good to go okay so uh what we're going to need to do is we're first going to need to create a route inside our users controller so what we'll do is we'll define two routes one for a get request to get users and one for a post request actually post users because right now our database is empty we don't have any data so let's first use the get decorator and we'll name our function get users and this controller function will need to call a method in our service class which we will have to define but let's just set up the routes real quick okay okay so now that we have our routes let's just go into our logs real quick let's make sure that those routes are mapped so that we don't have any so we don't have any issues so you can see that it mapped slash users get and then it mapped users post so that's great both of those routes are working all right so now we're going to go into the user's class and what we're going to do is we're going to need to define uh the methods that the user controller will need to invoke so in our case for getusers it's going to go ahead and call a method that will get all the users so let's create that method i'll call this fetch users okay or you can call it find users whatever you want it's up to you okay and it will create one uh method called create user okay and basically the service class is responsible for all of the business logic so what i mean by that is anything that has to do with uh you know creating users calling an external api anything that has to do with that those typically should belong in the service layer okay you don't want to put everything inside the user's controller because the controller is typically used for handling incoming requests and sending outbound responses and uh usually things that you would do in the controller would be things such as you know extracting query parameters validating a request body uh you know using condition statements to check certain uh let's check certain parameters these are sent things like that you don't want to you know call a database directly from the controller that's what the service class is for okay so we have our two methods okay and now what we need to do is we need to actually interact with the database okay so first i'll show you how we can actually create a user and then we're going to go ahead and find the user so how do we actually interact with database okay well what we need to do is we need to inject the type orm repository into our class okay so basically that is a dependency that we'll need to inject into the class or wherever we'll need it right and to do that we're going to go ahead and implement the constructor and then we're going to use the inject repository decorator okay and then we need to specify what entity we want and so in our case it's going to be the user entity so we'll pass in the user entity right over there and then after that we're just going to uh use the private keyword give our value a name so i'll just call this user repository or our argument a name and then to type annotate this you would type annotated with a repository which comes from type rm and i think this is this is a generic type it's a class and then it's a generic type and you want to you want to pass in the entity like that okay so this is how you inject a repository now you're going to notice that we're going to have an issue and it says that it says that nest cannot resolve resolve dependencies and that's fine and the reason why is because we are using our we're using type orm in a module that does not have that type or module imported so we need to go into our users module and then we're going to add an import statement or import property inside the module metadata object like this and then you're going to go ahead and import type or a module from at sjs type rm and then you're going to reference type or module you're going to call the for feature method and this takes an array of the entities that you want to use in the module in our case we're using user so i'll pass in the user entity like that and now if you save the error is going to go away so now we can actually use the user repository inside our user service okay so let's go ahead and do this so i'm going to go inside create user and we will need to take in some arguments so we will need to create a data transfer object and an additional type to type annotate the parameter okay but uh let me go ahead and do that first so i'll create a new folder called dtos and then i'll create a file called v8 user user.dto.ts and we'll go ahead and create that dto and i'm going to i'm only going to go ahead and use the fields that we defined for our entity so it's going to be very similar so we're going to need a username and password um and yeah just using my password really because all strategy it's knowable so we'll leave that alone i really only just added that just to show you an example and the id is a primary key and that's auto-generated okay and created that will have a default value so don't worry uh that isn't going to be a valid that's passed from you know the front-end or whatever or whichever user is sending a request to the server so we're only going to have username and password and of course you can use class validator to validate this dto the request body but i'm not going to do that for this video but if you want to learn how to do that check out my nest js crash course the link will be in the description uh okay so let's take this dto and let's go ahead and do this let's go to the controller we're going to use it so inside the create user um method because this is the route handler for creating users right we're going to use the body decorator and then i'm going to name the argument uh create user dto and then we'll type annotate it with create user detail okay and then what we're gonna do is well first we need to actually inject our user service into the controller so that's how that's how we can actually use it to call the methods right so it will implement the constructor and then we will go ahead and set private user service type annotated as user service or users service and this will use dependency injection to inject the serve the user service into our application so now i can just simply do this.user service i can reference the user service and then i can call create user okay but create user needs to take in parameters so we will go ahead and create a custom type or let me call this folder utils and then inside that folder i'll create a file called types.ts and i'll create a custom type called createuserprams and then we'll set the username to string password to screen the reason why you would do this so the reason why you would create a separate type uh uh outside of like the dto and the reason why you would not want to reuse the dto is because sometimes the user might send um the user might send like an additional uh it might send like additional properties that you would need on the server but that you would not save on the database right so for example if you're building a login or or if you're building like a registration form you would have you typically have like two password fields right one is for you know the password and the other ones to confirm the password you wouldn't you know you wouldn't pass in both of those values into the service typically right uh typically you would check the check those passwords if they matched uh using something like a validation pipe or like a middleware uh but in our case you wouldn't like you know pass and confirm password and password into the service because the service is really only going to take the password and save it to the database so that's why i think it's a good idea to create different types that represent the actual finalized uh data that you're going to say to the database and i think it's just good practice too okay so let's go into our services uh let's go to user service and we're going to name this argument for create user we'll call this create uh user details and then the type annotation the type for this will be create user params okay actually i'll just name this user details okay and so what's going to happen is you're going to go ahead and create the user or you're going to create the user based on the user details that are passed in so what we're going to do is we're first going to declare variable call it new user and then we're going to reference the user repository so i'll go ahead and reference this dot user repository and then there's a lot of methods that we can call the one that we need is the create method and this is where we're going to pass in all the details that we need for our user so you notice that there is intellisense so if i type u username will pop up right but because we have type inference and we know that we're going to be passing in the correct data type right username and password i can just simply do i can use a spreader operator so that way it will unpack the username and password property inside here and then additionally i can go ahead and set the created ad type to be a new date like this and save okay but we're not done yet because all this does is it just creates a user instance we need to actually save it to the database and the way that we can do that is by calling this dot user repository dot save new user like this and i'll and the save is an asynchronous uh it's it's an asynchronous method so it returns a promise so we can just actually return this call and then we'll await it when we call it from the controller uh this style user repository.create the create method is not asynchronous so you don't have to await it okay um cool so this is going to create a user so now let's go ahead and just pass in that create user dto okay and like i said um you know just for the sake of demonstration right i'll just do a confirm password field over here right and you'll see that uh let me do this real quick from password right and and ideally what you could do is you could just take out the confirmed password so you could do something like user details confirm password right you could take that out from the object and all the other properties would be spread to user details you could pass something like that so that's the reason why i suggest creating the actual dto and then creating a separate type for the finalized version that is going to be saved to the database the finalized schema that will be saved to database okay but um you don't have to do this though okay so let me just go ahead and remove this all right so let's actually test out so let's go ahead and actually uh go into postman so i'm gonna use postman to do this so let's go ahead and test out our post request endpoint so i'm going to go ahead and use postman you can use whatever http client you want um it's up to you i personally like using postman but you can use curl if you want to or invoke rest method if you're using powershell so we're going to make a post request to http localhost and the port for our application is port 3000 and the endpoint is just slash users okay and it's a post request so let's go ahead and send some data so we'll send username and password so this word hit send it's going to give us a 201 uh logs are not logging any errors that means nothing that means no errors happen let's look at our table so let's go ahead and do select asterisk from so let's select all columns from the users table and you'll see that we have our data so we have passwords one two three auth strategy is null username instant created at and this is the time and then the id it was auto generated which is good so that's how we can create records in the database so let's go ahead and fetch records okay so we're going to go inside the user service class we're going to go to our find users method and all we're going to do is we're just going to go ahead and search the database and the way we do that in typeorm is we reference the repository so this dot user repository and we can just call find and this will literally just get all of the user records okay from the users table now you can also pass in options too if you want to do filtering but we're not going to do that and so this will literally return an array of users and this is also an asynchronous method called so i'll just return this and then what i'll do is uh when i call that method i will go ahead and await that call so before i continue i actually uh let me actually continue with this first because i got to fix something that i forgot to do because uh and i realized in the create user method i didn't return anything but i'll fix that in just a second let me just come continue with the find users so i'm going to go ahead and add the async keyword in front of get users because we're going to use async 08 so we're going to go ahead and do const users equals await this dot user service that find users return users just like this okay um cool you could also if you're not really doing anything else like if you're not if you don't need a request or response object you could actually just do return this.userservice.find users and sjs would take care of serializing the data for you back to the user so watch this so if i go ahead and use a get request it's going to send me back the user okay let's create another user okay and let's make a get request and we have two users now okay and let me also do one more thing because i forgot to do this uh let me go ahead and just return this call okay and then so whenever i make a post request to our api it's going to return the user that was created now obviously in a real application you wouldn't uh you would not return the password but again this is just a tutorial so don't worry about that and you would also encrypt or not encrypt you would also hash the password to in your application before saving it to a database so i would i just want to call that out just so that you all are aware of that and i don't want you creating applications where you don't hash the password because that's not good all right so now that you know how to get data and create data uh in our database using our api as well as using you know the repository api as well now let's go ahead and create two more endpoints one for updating data in the database and one for deleting data that's in the database so we'll start with updating data first so you typically would use the put method to put http method for updating data and this is going to require us to actually specify a route parameter because we need to make sure we know which user to actually update so i'll go ahead and use a route parameter and i'll call it id and for the method name i'll just call this update user by id okay and then we need to extract that route parameter by using the param decorator so we'll pass an id and then what we want to do is we want to give our argument a name and then the data type to be a number so it is supposed to be a number and additionally what else we'll do is we'll use the parse in pipe so that way it'll make sure that the route parameter is in fact a number and if it isn't a number it will just give an error back to the user okay so this will actually validate the route parameter okay so watch this if i go into postman and if i were to make a pull request to the slash users slash idn points i'll pass in three for the id or actually i'll pass in an invalid value it's going to give me a 400 status code validation field but if i pass a number it will okay so what we're going to do is we're going to go ahead and call our user service we're going to create another method called update user okay and update user will need to actually uh take in the values that we want to update so in our case we'll actually need a request body as well so let's go ahead and create a detail for that so let's do update user dot dto dot ts um and we'll go ahead and just copy this paste this here and it's literally just going to be the same exact thing honestly update user dto save that and then we will go ahead and use the body decorator and then we'll call the parameter updates user dto type annotated with the update user dto class just like this okay and now we just need to pass in this dto into the update user but again we can do the same thing that we did where we created a type and i'll just do that just to be consistent okay and these are really the only two properties the user can update i mean the user shouldn't be able to update their username in some applications but in a lot of applications you can let them so it's really up to how you want to implement that okay so let's go into the service class because we still need to implement the update user method and then have it take in uh the the uh argument for uh update user params so we'll go into our users.service.ts file we'll implement the method update user and then it's going to take in update user details and then we'll type annotate this with update user rams okay and now let's go back to our controller let's just pass in we'll just pass in the request body or the update user detail body here we're just going to assume that the data is valid okay you still have to make sure you do validation as well okay so we need to do two things we need to pass in the dto as well as the id because we need to make sure we know what user is being updated we can't just pass in the payload like the request body without knowing what which user we're updating so we're going to take an additional parameter which is the id so i have to pass in the id first and then the dto okay sorry we're going a little bit backwards but hopefully you understand why we need the id right because what we're going to do is we're going to go ahead and go ahead and reference the user repository and we're going to go ahead and call update and we need to update based on criteria so in our case we're going to search for we're not going to search but we're going to update if the user matches the id that we're passing in okay so that's going to be for that first parameter the second parameter is what we want to actually update so we want up the username password etc etc so i'm just going to go ahead and do this so what i'll do is um [Music] i'll spread the update user details like this okay so if the user wants to update the username that means username will only be updated if they include both user and passwords and both those will be updated if they only include password then only password will be updated okay so i'm going to go ahead and await this actually i'll just return this call because it doesn't really matter we don't because this returns an update result and we don't really need anything to do with that okay and uh i'll just leave this call alone so i'll do i'll just asynclate this because i don't want to return to update result response i'll just leave it alone okay so let's go ahead and make a pull request now so i think we only have three users right if i look into my powershell i only have three users so let's update the user with id number three first so we'll go over to our postman we're making a pull request to our slash users slash three endpoint and the three is the route parameter remember that and we're going to update the username so i'll just call this ansonanson1 password i'll just call this zero zero zero zero one two one one okay so we got a 200 okay let's query our database and uh it updated perfect so for id number three it updated the username and the password if i omit the password and if i just want to update the username it would only update the username okay it doesn't update the password if i want to update only the password right it only update the password okay and obviously it makes sense because we are omitting those values so those values are not even going to be in the object itself so it wouldn't even replace those values when we update it because we're using the spreader operator right over here okay so hopefully that makes sense one thing that i will quickly mention is that i think instead of put i think patch would be better in this case because patch actually uh allows typically you would use patch if you want to update a a partial uh if you want to do a partial update of the entity uh put typically is for like mod modifying the entire resource so if you want to update the whole entire resource you would use put but patch is usually for just you know updating like a portion of the entire record so in our example we would only update the password so it would make sense to use patch but we'll just leave it like this for now okay because the whole point is you understanding the idea behind these different methods so let's go ahead and implement now a delete method so we just use the delete decorator that is going to be imported up top over here okay and we can do the same thing we can go ahead and delete by id so i'll literally just copy and paste uh this whole method and i'll just rename this delete user by id and um we're just going to remove this uh body decorator because we don't need that we really only need the request uh that we don't really only we really only need the user id sorry i can't speak today we only need the user id so we're going to go ahead and implement a method called delete user which is going to take in an id as a parameter so inside user's service we'll go ahead and implement that method right now okay and then what we'll do is we'll just return this dot user repository and we're going to call delete and we can pass in the criteria so just pass in the id just like that obviously if the record does not exist it will not do anything but if it does it'll delete it okay so let's go ahead and save and let's change the method to delete from put so now we're using a delete request and i'm going to go ahead and delete user number three seems like everything went well no errors let's go ahead and select from users you can see that user number three is gone user of id number three is gone if i were to delete number two user with id number two is gone if i were to delete number one user of ideas or user of id number one is gone and that leaves us with an empty an empty set for our users table okay so that's pretty much how you can delete records from the database now obviously it would be a lot more complicated when you have relations like one to one relations or one too many relations so it's a little bit more complex when you do it like that but i just figured i'd mention that uh in case you did need to do one-to-one relationships all right so we're gonna move on to more complex topics now so we're gonna go ahead and move on to relationships with type orm entities which is very similar to relationships in sequel we're going to talk about one to one one to many many to one relationships so if you're already familiar with sql you'll know what i'm going to be talking about but i will briefly explain it to those who are not so familiar or fairly new to sql so a one-to-one relationship is basically just where you have one record in one table that is associated with literally only one other record in another table so here's a real-life example right let's say you have a user that is registered to your application the user can have a one-to-one relationship with a profile okay because every user can only have one profile now it also depends on how you design your application how you decide to you know architect everything because every application is different okay but it just depends on how you wanna implement right so if we say a user has only one profile that just means that there is one user in the users table that is associated with only one profile that is in another table called the profile table right another example that i give you is let's say for example if you're building an application where a user or an employee can only have one job right that would be a one-to-one relationship under you know specific limitations right in the real world obviously you know you people can have multiple jobs so that would really be more so like a one-to-many relationship right same thing with the user profile like you know some applications allow you to have multiple profiles so that would be like a one two many relationship as well but of course it just depends on the constraints and the parameters that you set for your application one more additional thing that i will mention is that when you're using sql it is very important that you follow normalization um so if you're familiar with object-oriented programming you typically want to encapsulate properties into objects or classes as much as you can so if there is a set of properties that you have defined in the table that could actually belong in another table and you can create a relationship between those two tables you should most definitely do so so an example would be let's say for example if you have a users table you could obviously put every single piece of information you want in that table you can add the username the email the password the jwt token you can add access tokens all that kind of stuff right but you want to try to normalize things as much as possible because that's usually one of the rules with sql i mean you don't have to but it does optimize your database a lot more better okay and it makes it a lot more uh faster okay so um you want to try to get into the practice and habit of creating tables and then having those tables relate with each other one way or the other whether it's a one-to-one a one-to-many or many to one okay but let's just go ahead and use a simple example with a one-to-one relationship so in our in our application we'll just make it so that every single user can only have one profile okay so we'll create an entity called profile okay we'll use the entity decorator and also the name to profiles or you can call user profiles whatever you want if you want to be more specific this profile will have its own primary key so we'll use the primary generated column decorator okay and what we'll do is uh we will have more data for this profile so for example we'll have a first name last name right um yes and of course you get other add other stuff too let's add an age property as well let's add a date of birth we'll leave it like this for now okay so this could be the user's profile all right so how do we set up the one-to-one relationship okay well what we're going to do is we're going to go inside the user entity first and we're going to use the one two one decorator and we're going to pass in a callback function and this callback function is supposed to return the entity that it has a one-to-one relationship with so we're going to pass in the profile entity like that and it's going to be imported from the profile file over there and then underneath what we want to do is we want to use the join column decorator as well and then we'll go ahead and set the type we'll define the property as profile and the type will be profile okay uh so once we do this we need to then go into um profile and we actually don't even need to do anything in here because it's just a one-to-one relationship from user to profile okay so we don't need to do anything inside the profile class okay so now the only thing that we'll need to do is we'll need to figure out a way how to actually populate the profile right because it's not actually required you don't actually need to populate the profile so let me just show you real quick so uh let me see oh yeah one more thing i need to do is of course um register our entity so we have to go inside uh app.module.ts inside the array pass in file like this and that will be imported up top here and that error will go away okay so let's go ahead and just create a user again so let's just do that okay so that created a user for us but we don't have a profile now if you look in the database you're gonna see that uh what the join column actually does with that decorator that we have right over here what it does is it basically just joins that uh profile id column this is basically like the you can think of this as like the other primary key that will allow us to know which profile belongs or relates to this user okay so whatever value this is would basically be the primary key of the profile record okay um so let's go ahead and create an endpoint um real quick that will take care of uh setting up user profiles so inside the user's controller and um you i mean if you want to you could create a separate controller if you want to you don't have to but i'll just do it inside the user's controller so we'll do post and then what i'll do is this will be users profiles like that and i think there should be no problem picking up this route even though we have other routes yeah there shouldn't be there shouldn't be a problem because this route over here is just the base users route so this week for profiles create user profile and we're going to do the same exact thing that we did for create user when we make a post request we're going to create a dto so i'll call this create user profile.dto.ts and it's literally just going to be a class and it's going to have all the properties that we had defined in the profile entity so first name last name age and date of birth so we'll do first whoops first name string last name string page number the overstream and again we're assuming that everything is validated but like i said make sure you validate everything yourself in your actual application okay uh whoops create user profile dtl is what this should be called okay so now i can take this and i can type annotate the argument so we'll call this create user eto type annotated with the class but that needs to be imported and that'll be imported up top over here okay and then all we'll need to do is we'll just need to go to our user service implement that method and then call that method to create the profile like i said if you want to you can create a profile service as well as a profile controller but i'm not going to do that just to save a little bit of time so i'm going to go ahead and just create a method called create user profile okay and of course we're going to create a custom type to represent the user profile details so let's go into our types.ts file create a type called create user profile params and i'm just going to go ahead and copy everything here inside here just like that i'm going to copy the type name and then we're going to go into the user service and then we're going to pass in an argument or we're going to label the parameter so you uh open close create user profile details and then we're going to import that type that we just created so i'll be import up top here okay so in order to make sure we attach the profile on the correct user we'll obviously need the user's id so what i'll do is inside uh user controller we're going to make this route a little bit more explicit so we're going to define this route as colon id slash profiles create so the id will be the user's id and then the profiles will be the end point okay and so we'll need to obviously retrieve that parameter so i'll do that so ram id unique number like this and i'll use the parse and pipe as well now in a real application you'll have uh you'll typically have authentication so you could just get the user id from the request.userobject okay that's that's usually where the user is stored if you're using assuming you're using passport and express session okay so you don't need to actually pass in the uh the id of the user in the route okay you just grab that from the session okay in our case since we're not using sessions we have to do it this way but it's fine okay so user service will also take in the id of the user okay and what we need to do is we need to go ahead and first we need to search for the user inside create user profile so const okay user equals away this stop using repository find one and we're going to use find one by because it's going to allow us to use conditions so we want to search where the id matches the id that we pass into this function and let's add the async keyword in front of this method and we're going to go ahead and say if there's no user we'll throw a new http exception and i'll say user not found you know create profile and then the status code i'll use the http status enum which is imported up top over here from sgs common and i'll just do add request like that okay if the user does exist then we'll create the profile and then we'll need to update the user as well so uh first thing we'll do is we'll create the profile but in order to create the profile we need the actual repository so we need to do two things one we need to go into the user's module and we need to import the profile entity and then put that inside the array otherwise it's going to give us that error that we got earlier when it complained about the user repository not being a proper dependency so we need to add profile inside here because we're using the entity inside the user module in the user service next i'm going to copy this inject repository i'm going to paste that and i'm just going to replace user with a profile down here and i'm going to rename user repository to profile repository and then we're going to change this to profile okay it's just really the same thing everything's repetitive and then now what i'll do is i'll do const new profile equals this stop repository create and then what we want to do is we want to pass in the values okay so in our case we just need um the uh let's see first thing last name yeah so i'll just go ahead and just pass in create user profile details like that because we know that all the properties here are going to match exactly here okay again we're assuming that we've already validated everything already okay and if you have not validated in your application please make sure you do validate your data so create is not an asynchronous method so we don't need to await this but we do need to save the profile to the database so i'll go ahead and do constant profile equals awaits this.profile repository that save new profile next what i want to do is we know that at this point in our logic we know that the user is already in fact defined right so what i want to do is i want to update the user so i'll do user.profile equals save profile and then i'll go ahead and return this dot user repository dot save and then we want to save the user okay because that's because we're updating it okay so this will return the user entity so let's go ahead and call this method so i'll just do return this dot user profile pass in the id and then we'll pass in the dto this is the same exact thing so let's go ahead and try to create well first i think we need to create a user because i think i deleted all my users i think yeah and uh yeah so let's create a user first so let's first create a user so this user has an idea five okay so what we'll do is we'll make a request to slash users slash five profiles a post request okay so this is going to make a post request and then we want to pass in the correct request request uh payload so first name let's do anson last name the developer age 24 you'll be uh we'll just do zero one zero one 1997. okay that's not my actual birthday but i'll just leave it like that i'm gonna click send and it gives us a 500 status code let's see what's going on um i think we may have accidentally did i call the wrong method oh whoops i called the wrong method i called this stock crate user profile it's supposed to be whoops sorry about that it's supposed to be this dot user service dot create user profile excuse me there we go let's do this again perfect now it works so you can see now we now have a relationship between users and our profile this is a one-to-one relationship and now if i query my database if i were to select from users you'll see that now before the profile id was null but now the profile id is two and two is actually the primary key of the profile in the profiles table so if i select from user underscore profiles you can see that the primary key for this profile is number two so if you want to actually get the correct profile for the user you need to first get the actual profile id which is the primary key you can take that and then query the user profiles table by the id and that's how you get the profile okay so that's pretty cool so um yeah that's how you can create one-to-one relationships and you can do this literally with any single entity you want okay so that's pretty much it when it comes to one-to-one relationships all it really is is it just means that one entity or one record in the table has exactly one association with another record in another table okay that that's that's really all it is all right so now that we've actually uh created a one-to-one relationship what about if we were to query the data right let's say for example if i go to if i make a get request let me actually uh open a new tab and let's just go ahead and grab all the users right you'll notice that right now it only gives me the user but it doesn't give me the profile right now the reason why this happens is because by default it's not going to give you all of the relations you'll have to actually you'll actually need to pass in options for that to happen so i'll show you how to actually get the relations so we're going to go into the service because that's where we're calling the repository and what you're going to want to do is you're going to want to pass in options and you're going to want to pass in this relations property and this is an array and you're basically just going to pass in the exact property name for that relation so in our case user the property name is profile so that's what i'm going to pass in okay and it's an array because you can have multiple relations for a single entity so i'm going to go ahead and save and now when i click on send it's going to give me the users and it's going to give me the profile if it exists that is okay if it doesn't then it won't give it to us okay if i went to if i want to create another let me create another user so i'll create another post request so let's do this let's do json that's the username and sin one password and one okay so we have that and if i go ahead and query all the users you can see that for this user over here it does not give me a profile it just says null and then for this one over here it does okay so if you were building a real application and if the user did not have a profile set up you would just check to see if that profile property is no and if it's null you could just tell the user on the front end be like hey you have not set up your profile click on this button to set this profile have them fill out a form with all of the required information make a post request and then you're done okay so i'm just giving you an example of how uh something like that would work in a real application but hopefully this makes sense alright so now that you know about one-to-one relationships let's go ahead and move on to one too many relationships and i think this example would be a lot easier because we can think of so many one-to-many relationships in the real world right for example a single person right can have many cars so the relationship between the person and the car is a one-to-many relationship because a person can have many cars right same thing with a person can have many houses a person can have many credit cards right these are good examples of one too many relationships right so i'll show you a simple example where our user is going to have a one-to-many relationship with their posts right because if you're building some kind of social media platform or some kind of blogging application right your users are going to have posts okay so what we'll do is this we'll create a new entity called posts and it's just going to be a really simple entity so let me just use the entity decorator okay and let me actually just preview this prefixes with user underscore posts and then let's just export class so this will be called posts i'll call it post okay it's of course going to have a primary key which we use the primer generator column decorator we're going to go ahead and give the post a title property so title whoops whoops i don't know what's going on with my keyboard title string and we'll also just uh add a description string as well we'll just make it really simple okay so now we need to set up a one-to-many relationship between the user and the post and then we need to set up a many to one relationship from the post to the user let me also just rename this from posts to post okay so let's go into our user entity and right underneath our one-to-one relationship we'll set up a one-to-many relationship so to do that we're going to use the one to many decorator and then we're going to pass in a callback function and for this one it's just going to be one too many and then the type that we want to pass is going to be the actual post itself okay so or not the post entity like wait whoops not the uh not the uh type or um not not the uh an sjs decorator i accidentally imported that it's supposed to be post the post entity like this this post nc which is imported from here okay and then we need to pass in a second argument which is for the inverse side so uh for the inverse side it's going to be a post and then we're going to reference post dot and now we have not defined that property yet but we're going to have to define a user property on the post entity after okay but over here the property that we'll define here is just going to be posts and there'll just be an array of post like that an array of the post entity now we got to revisit the host entity and we have to basically do the same thing but this time instead of one to many it's many to one so many to one so it's going to take in a callback function okay and this time it's just going to take the user entity so let's import that and then from here the callback to the second callback function it takes in the user argument and then we want to map it to the posts property that we define just now in the user's entity and then over here the property that we'll define here is just going to be whatever we had right over here post.user so user user okay and now you can see that it works okay no more errors we have successfully defined our one to many and many to one relationship between the user and the post right because it makes sense the user can have many posts but those posts are always going to belong to one user now it's a different story if that post can have different you know collaborators but typically there's always going to be one single uh owner of that post okay so we'll go ahead and leave it like this okay now let's just go ahead into our users module let's go ahead and import that post entity so that way we can actually use the post repository and i think honestly the best thing that we should have done was definitely use a uh we should have used multiple controllers but it's okay i just want to make things as simple as possible so you all don't get confused but what we'll do is we'll create another post request or we'll define another post request endpoint and for this one it'll just be similar to our profiles okay but instead it'll just be colon id posts okay so the id is just the the user's id and then post is the end point where they will create the post so we'll do create user post okay and you can imagine that we'll need to do the same thing where we grab the route parameter id and then we'll also need to create a detail for the post okay so let's go ahead and do this we're going to go into dtls we'll create a dto called create user host.tlts create a class user post post yo and it only needs the title and description i think we only defined the three properties right title description yep yep that's fine and i'll also create the type so i don't have to worry about that later so export type create user post params title string description string same exact thing okay but this will be used for type annotating um the the services parameter okay so now let's go back to our controller and we're going to go ahead and use the body decorator we're going to name our arguments create user post dto and we'll type annotate it with create user post dto like that okay and then we're going to go ahead and call this dot user service dot create user post and then we'll need to of course pass in the id as well as the uh the user post dto data so we need to implement that service now or implement that service method so let's go into services users user service down here i'll implement the method create user post right over there uh yep perfect so it just needs to take in two parameters the id which is a number and then create user post uh details and the type for that will be create user post params okay and then now all we need to do is we'll need to create a post save it and then attach that post to the user so that post belongs to that user so i'll show you how to do that so uh we already imported the post entity and then we placed it inside the array for the for feature argument so we're good so now we can just import the post repository without any issues so let's import the post entity so that's going to import that up top here rename this to post repository and then change this generic over here to post that so now i can use the post repository to create post so let's do this const new post equals this dots post repository.create we'll just pass the create user post details like that okay we'll save the post so const saved post equals await this dot post repository dot save new post let's also add the async keyword in front of our create user post method um oh yeah also i forgot we should also search for user two so let me just copy what we had up top over here create user profile and just paste this here we should have also defined a method called find user by id but it's okay so that way we could just reuse that over and over again instead of just manually doing this but we'll leave it like this for now so uh we find the user if the user is not found we'll throw an error so now the only thing that we'll need to do next is we'll need to of course ensure that the post is saved and related to the user so instead of actually just only passing create user post details what we can do is we can actually pass in an object the structure create user post detail so this will give you all of the parameters like you know uh the title and description and we also need to pass in the user object like this so this will ensure that it relates to that user okay so when we save the post we can actually just instead of just doing this we just return so this will return back to the controller which will return as a response to the user so let's try this out so let's go into our console let's make sure there's no errors let's see uh entertainment data for user post not found i think we forgot to import it in user modules i think or wait app module yep let's do that real quick save there we go alright so let's just describe tables or show tables sorry and we have all three of our tables perfect all right so what we're going to do is we're going to go into postman and we're going to go and call the endpoint that we had just created for creating user post so instead of instead of id slash profiles it's just going to be id slash posts so slash users slash posts let me just also make sure that we have correct users so we have a user of id number five okay so that's only one user okay that's fine so um we'll just work with number five id of number five okay so let's pass in a request body so title hello world description i love programming and also just move this on here okay so we're gonna we're going to go ahead and make a post request this endpoint and you can see over here that we just created the post okay and um i'm not sure why yeah yeah so the post has the user relation and this is the idea of the post perfect okay so if we look in the database now if i select from user posts you're going to see that this user id column has the value 5 and that's basically just the primary key of the user that this post belongs to okay so if you were to query this in like a database you would do something like select from you would do something like a uh you do something like an inner join or if you wanted to only filter out by a user by id you do some like select from user posts where user id is equal to five and that'll give you this post okay and i can go ahead and create more and more posts so let's just do that okay and now if i want to go ahead and fetch my users you'll see that it's not going to give me my posts and that's because we did not add that relation when we were finding the user but we can easily do that so if we go into the user service and then we go into up top over here in the find users method we can add a comma after profile and then specify posts like that which is the exact uh field name in our type rm entity so after we save if i were to fetch for users again you'll see that now let me zoom out a little bit you'll see that now it gives me the user the profile and the posts okay now in an actual application you probably wouldn't want to actually include all these relations you would actually want the user to visit the actual endpoint to get the actual profile it's not a good idea to fetch all of these things because it will actually slow down the request a lot okay but that is pretty much it with one too many and many-to-one relationships so that will be pretty much it for this video i hope you all enjoyed it i really had a lot of fun teaching you all how to use uh type orm and mysql with nesjs i personally love using typeorm and mysql i'm a huge fan of my sql i love relational databases and it's my pleasure to teach you all something new hopefully for most of you but um yeah so you know thank you so much for watching if you all love this video definitely leave a like leave a comment down below to show your support subscribe to the channel if you have any questions leave a comment if you need any help with code uh you can also leave a comment or the best thing to do is join my discord server where you know i have a lot of users where they can ask questions and there's a lot of people who are willing to help um and that'll be it so thank you so much for watching this video my name is anson and i'll see you all in the next episode peace out

Channel: Anson the Developer

Views: 78,136

Keywords: nestjs, nest, nest tutorial, nest nodejs, nest framework, nestjs tutorial, nest.js tutorial, node, nodejs, nestjs typeorm, typeorm, nestjs mysql, nestjs typeorm mysql

Id: W1gvIw0GNl8

Length: 77min 37sec (4657 seconds)

Published: Fri Aug 12 2022