Nestjs TypeORM One-to-One, Many-to-Many, One-to-Many & Many-to-One Relationships

hello guys in this next J's tutorial we are going to learn about entity relationships using type orm this is basically talking about relationship between the tables in our database we are going to cover some of the most common types of relationships and that is one to one relationship one to many where the reverse is many to one and many to many relationship including a type of many to many where you can have additional colums in the table that joins the two entities on the screen is a diagram that tries to illustrate these relationships we are going to cover let's take a look at for example the relationship between leaders and countries we assume that for each country there's one leader that is the topmost office this is an example of one toone relationship it is a type of relationship where entity a can only contain one instance of entity B the reverse is the case in other words entity B can only contain one instance of entity a now A and B can mean leaders and countries in this example so what you can do is you go to one of the entities in this example to leader table and you define a foreign key that Maps or points to the key in the country table in this case I have country ID that references the primary key ID in countryes table we are going to see this in the code in a moment so it's going to get clear another type of relationship is one to many if you go to a country you're likely going to see many cities so we can say that there is a one to many relation ship between country and cities so in our diagram in the country table we don't have the foreign case specified in the country's table instead we have it on the many side in this case many cities belong to one country so on the many side we typically we specify that relationship not typical so what that means is that if I fetch a country I can decide to join all all the cities that belong to that country so here we are saying country ID references ID in the country table so whenever we create a city we can decide to associate it with a country ID that points to the country's table another type of relationship is many to many by the way for one to many the reverse is the case so you can either say many to one or one to many then for many to many relationship it is a case whereby entity a can have many instances of entity B and vice versa that is entity B can have many instances of entity a take for example the relationship between countries and time zones some countries have multiple time zones they're not of bound within the same time zone so it means that a country can have multiple time zones the reverse is true that for a given time zone we can have multiple countries so it is possible for a time zone to cut across multiple countries this is an example of many to many relationships but the diagram is a bit different so here we have countries that is pointing to another table instead of time zones d directly that is because to form a many to many relationship we have this intermediary table this joint table so call it pivot I'm not sure if that is technically correct so we have this third table Country Time Zone you canit whatever you want that holds the relationship between country table and time zones table so in the country time zone table we have this forign key time zone ID that references ID in the time zones table in a similar way we have country ID which is a foreign key that references ID in the countes table also you can decide to have additional columns in this table if you wish to but the primary function is to map that relationship between our many to many entities in this example I have additional column named stat which does not really form part of the mapping for relationships all right let's go over to the code now and see how to implement these relationships I will start with the one to one relationship by the way I already have a project set up for this demonstration this is a next JS project and I have four entities already created but we have not done any relationship mapping between those entities I will start the server and we take a look at the database and the tables as it is right now so I'll go to PG admin and refresh the tables so we have the four tables this one is for cities if we check the cities there's no relationship mapped right now now these are the columns the name description and when I check the constraints foreign Keys there's no foreign key yet remember we use that foreign key to map the relationships the same thing applies to countries and the other tables in this database if we check the entity we can see that right now we don't Define any relationships for example in the city's um entity file we are going to have have relationship between City and Country but right now we don't have those things in a similar thing for leaders we're supposed to have onet toone relationship between leaders and Country stable and finally for time zone we also supposed to have many to many relationship between time zone and countries so let's start with one to one and this is going to be relationship between country and leaders so I will go to one side of those entities in this case I am picking to go to the leaders so that when I create a leader I can specify the country it is the person is leading so over here how do we map the relationship first how we do it in such a way that type orm will automatically generate the country ID column in the database but then we look at how to customize it to map a one to one we simply say one to one then inside here have this AR function what are we mapping this U leader to we are mapping it to Country so I'm going to say country there the next argument here another function this will give us access to you can call this whatever you want anyway but it makes sense to call it country so here we say that the relationship is country. leer it means that when we go to Country entity we are going to create a field named leader and then down here I want to say let me call this country of type country so this is how we can map one to one relationship but we are not done we are going to come back to this far in a moment here we say country. leader and then over there in the in the country entity we are going to do something similar so let me just copy this I will copy this head over to country but I'm going to change all the occurrences of country to leader so One to One Import that this is leader and this here is leader we can call this guy leader not like that we say leader do country here I'm going to say leader oh okay yeah leader so now we have one to one to one relationship between country and leader and vice versa between leader and Country what I would do is to go go to one side of this relationship mapping and specify joint column this is about the column that that forms the column for the foreign key reference so here I'm going to say join join column just like that so what will happen is that type orm is going to automatically have a colum named country ID so just the entity name plus ID in a Kam format so if I go back now to the database and look at the leaders table we are going to see a new column named country ID let me go and refresh the tables then I'll take a look at the leaders not like that go to properties and check the columns you can see that we have country ID this is named in C case right and now when we check the constraint the foreign key now we have country ID referencing ID in the countryes table that's what this is saying we can choose to give this column a different name that is instead of C for example we can make it country ID or you can give it whatever name you want to do that we come to this joint column and we give it a name so we say that the name should be country ID or whatever you prefer I will save that and we take another look now so leaders properties and the columns so the column has changed to Country ID now let's talk about how to create leader with the country ID it is actually straightforward so what I do now is head over to leader service and here I already have a a create method that takes the dto this dto Supply the name and Country ID I will go ahead and call this from Postman and see what happens but first let's create a country because right now I don't have any country in the database I'm going to create a country let's say the United States that is created let me add another one India all right and maybe one more Canada so we have three countries in the database as you can see here now let's go ahead and create a leader in creating a leader we can supply the country ID for that leader let's say that John do is for country with ID one if I send this request we have John do for country with ID one but let me go ahead and fesh leaders let me also load the relationships we are going to see something so this is fine off for leaders I want to load the relation that is country in this case by the way I've covered how to query relations in a previous lesson including using the repository API and query Builder and doing search and filter all that so you can take a look at that lesson here we are fishing Leaders with the country I'll send the request again you notice that the country field for this guy is no and why is that so if you take a look at the request to create a country with specified country ID but that is with the K case country ID but there's no such field in the entity let's go to leader entity and search for this guy we don't have anything like that what we have is country ID although type orm we automatically generate this column in the database but for working with this entity we can decide to specify that field here but the name for this column has to match the name we use for the relationship that is ID Here country ID otherwise a new a different column will be created let me just first remove this guy and let's give that a try I will save this and refresh check this guy again and we supposed to have okay I guess this is because we already have some values in that database let me delete everything and start a fresh restart the server so everything will be recreated okay the server is running I will refresh come on refresh the tables and check the leaders and the column so what what happened is that we have additional column instead of this only this column that's why we need to make sure that the name for the two are the same I will save that so this way type knows that this is not an additional column by the way this is how you specify the name for the column otherwise the name of the field will be used for that column so if I go back now and check what we have now we should have only three only the three columns so the other column is gone all right now let's go ahead and try to create country again the country ID is in the dto so we don't have any issues with that this is create dto there's country ID here and in entity we have that field so type orm can automatically map know that this country ID refers to this guy over here I'll go ahead and send a request to create a a leader John do for Country ID one which is United States I guess we not sure what this ER is talking about select all from leaders right now we don't have any record oh I deleted everything so we don't have countries oh my gosh let me go ahead and read the countries again Canada India the United States United States okay so John do is the leader for for the US and let's say Lucy Luc see whatever is the leader for the second country and for the third one Mary okay so we have those three records there now if I go ahead and find the leaders you can see that for each leader we have the country that they lead so we have successfully mapped onet toone relationship between the leader entity and Country entity the next thing we are going to look at is one to many relationship in this case the relationship between country and city so a country can have many cities or in other words a city can only belong to one country which is the reverse of one to many for that right now we don't have anything in the city's table let's go ahead and map that relationship for this I will go to the the many side that is the cities entity go to to seeing entity file so what we want to do here is to say um when we create a city we want to pass the country ID for that City for that I will come to the city um entity and say at many to one and this is going to point to Country and then country country do cities right now we don't have the cities fied in countries but let me finish country not country country of type country so I can go over to the country take note that here we say many to one that is because many cities belong to one country but when we go over to countries this country entity what we are going to have is the rivs which is one country to many cities so we reverse this guy to city and copy this thing this is going to be of type City array and it is going to be named cities cities now this going to be City dot by way this should be City country so this is the reverse one country has many cities in cities we say many cities belong to one country then for the joint column we put it on the many side on the sh side the country is the parent this guy the city is the sh so we say join colum just like what we've done for for one to one we can specify the name for that join column in this casee this is going to be once again country ID and let's have that column as well so I'm going to have a column named country ID of type number and the name matches the name for the foreign key constraint all right with this I will go ahead and fetch city with the real Rel country you can do the RSE you can fish country with the relation cities by the way let me enable that here I commented it out so C this let me head over now and I will find find cities oh we don't have any City so let me create one New York belongs to country with ID one is it three I think three is for the United States let me confirm here the US is one really us is three okay so I will create a city New York that is for three let's say uh Los Angeles so that is another city in the US and for another country let's speak India with id2 so I will create a city of Mumbai Mumbai for ID to now if I go ahead and fetch the cities you can see that we have city with the countries we have New York United States Los Angeles United States Mumbai India and the reverse is true that is the one to many one country to many cities if I fetch the countries now you can see that each one of them has the children right here we can have multiple records for the relationship but in the city we can only have one country for each City all right this covers the one to many relationship before we talk about many to many relationship let's take a look at the database and see what has changed so I'm going to refresh the table this time it is for cities check the properties we can see that we have the column country ID and when we check the constraint we see that we have country ID referencing ID in the country's table okay for many to many relationship in this example we have a country can have multiple time zone or can belong to multiple time zone a time zone can have multi multiple countries so it is many to many and how do we do that with type orm for this I will go to Country entity here I want to have many to many this is going to point to time zone The Entity we say time zone time zone dot country countries not country then down here I'm going to have time zones of type time zone collection array then I will copy what I have over here go to time zone paste exactly the same thing the only difference is that we are going to change the occurrences of time zone two country country so this is the same on both side except for a little change we are going to make this is country and Country not that country do time zones this is countries that belong to this guy once again we need a join column what we need to do is to specify this on any one side of the relationship so you can pick country you can pick time zone for this I'm going to pick countries so in the country why do I have this error so this should be small letter in the country I will specify the join actually this should be join table not join column because we have this thir table here this guy here that joins the relationship between contes and time zone that is the many to many relationship so this should be join table on one side of the relationship just like that what will happen now is that typo RM is going to create a table not exactly this something similar we are going to get to this table ler our own custom table it's going to create a table that basically Maps the the foreign key ID to primary key ID and then this foreign key ID to primary key ID so let's go ahead and check the database to see that mapping happening so I'm going to refresh right now we have only four tables if I refresh you can see now we have additional table named countries uncore time zones time zones this is just the naming convention that type orm we use what we going to do later is to use our own table and give the table whatever name we prefer we can also have additional columns because right now if I check this guy we going to have those two columns so you can see we have countries ID which is combination of the table name and just ID added to it and then time zones ID when we check the constraint we are going to see the following Keys countries ID references ID in the country table then time zone ID time zones ID references ID in the time zones table what I want to do now is to change this table name to something similar to what we have here that is Country Time Zone we also going to change the columns to use the snake case country _ ID time zone uncore ID and also we add one additional column to this table for this I will go to this joint table and customize it so for the table name we are going to say name this is going I already have this this entity that was commented out so this is the table I'm going to use I'll copy that over here and paste the name and then for the foreign keys I can use the joint code colum option to customize that so here I'm going to specify the name for the column so here I'm changing it from the country ID to Country uncore ID country ID and this references references the ID column we can as well change the name of the foreign key that is generated so it's a foreign that refers to let me go over here this name here so we can use our own custom name if we wish to so for example I can just copy this and say that this foreign key should have the name that so the joint colum option is on the side that owns this relationship this mapping on this uh country side then we have inverse which has to be for the time zone so we say inverse inverse join column let me go ahead and copy everything here and just change the value so this one is going to be time zone time zone ID and let me also rename this to that so now we have this table that is called country time zone but because we already have the kai here this is going to be used and this extra column here is going to be added in the table then for the ID column we we need the primary key autogenerated column for the table all right so I'm going to save this and let's see what we have now I'll will go over here and close this refresh when I refresh this old table is still here because type orm doesn't know doesn't have connection to it anymore so it doesn't know that it exists what I can do is to delete that table and let's check what is inside our own custom table so in the custom table you can see we have the foreign Keys now which go to the constraint foring ke now uses the underscore the custom t column names we specify spe ified as well as the forign key name that we specified and for the columns we have the country ID time zone ID an additional column named status now one thing I want to point out is that I tried to follow the guidelines in the documentation to achieve this custom table but somehow I kept getting additional columns in this table so while these options configured here here of course from the type urm you can check the official documentation for detailed guide on how to use custom table for for the joint table now the next thing I want to do is to insert records in the for the many to many relationship for this I'm going to go to this um time zone service this should be time zone not CD I did copy and paste so to create a time zone or to create a country it goes both ways we can assign countries to time zone so let me make this an empty array for now then I will go to the D this dto this is D for creating a time zone so we we expecting the Zone which is going to be a string then for the country I'm going to take array of country IDs because it a time zone can have multiple countries in it so this is going to be a number array then back to the service method what I'm going to do is to create instances of country and assign to this guy the time zone here so I'm going to say ID dot map come on what was that map ID and new country you can create this one by one if you prefer to do so just have to assign the array of countries to time zone all right after that we just call Save and that should save the record with the relationship if I go to the database what if I if I check this table select all from this table we don't have any record right now but let's go ahead and insert some records so the zone that is the time zone for the country IDs this is going to be an array so for country with ID three and the one with id2 of course it can be one record it can be multiple records I we send a request and we get this response let me create another record let's say for another country with ID one we have time zone of I don't know plus 10 whatever send a request and let's say minus 6 for Country ID 3 so now if I go back to countries that should load the countries with the um by the way I recreated some of the previous records because I cleared my database but what I'm looking for here is the time zone okay that is not loaded for countries so I'll go to Country service and select time zone among the relations I will do something similar for time zone so I'll go to time zone service okay the country is is already selected so I'll go back and send the request again this time we get the time zone for the countries so for the country we have the leader the cities and array of time zones also when we call the time zone and point we expect to get the time zone with the countries so our custom table and many to many relationship works and once again please take a look at the official documentation all right guys I believe we've touched on everything we have in our objective for this lesson we have seen onetoone relationship one to many and many to many I hope this makes sense until next time enjoy coding

Channel: ZestMade

Views: 6,340

Keywords: typeorm many to many relationship, typeorm onetomany, typeorm onetoone, typeorm save many-to-many, typeorm entity relationship, typeorm tables relationship, web development, Typescript, nodejs, node js

Id: _q5-EnV7M_Y

Length: 35min 5sec (2105 seconds)

Published: Mon Nov 20 2023