TypeORM Crash Course - TypeScript & Node ORM

hello everybody in this video we are going to be covering how to utilize typeorm in your nodejs application now typeform is a great orm for javascript and typescript now what is an orm well an orm is an object relational mapper and what it does is it really simplifies the way that we are going to be interacting with our database now typeworm has support for multiple different databases but typically when we use typeworm we are using some sort of sql database like mysql or postgres but typeform also has support for nosql databases like mongodb now in this video we are going to be utilizing postgres as our database and we're going to be using type orm to interact with our postgres database but the concepts that you learn here are going to be very similar to if you were using mysql database or even a mongodb database so don't worry if you have another database preference but i had to pick one for this video and i think postgres is is pretty much the standard nowadays everybody's using postgres over these other databases so that's what i'm going to be doing all right so that is enough if you want to read more about typeworm just go to the typewarm.io and they have some pretty good documentation but i'm going to try to cover almost everything in this video so what are we going to be doing in this video well we're going to be designing a database and what we want to ultimately do is create some sort of banking system let me explain so over here what i want to do is i want to store all of the clients that are utilizing a specific bank inside of our database and then i also want to store all of their transactions now each client is going to have a banker now they can have multiple bankers and bankers can have multiple clients so we need somehow to store the banker information but also the relationships so over here we have again a client and a client can have a banker and then a banker can have a client but also we can have a another client over here let's say we have another client and they can also be associated with this banker and we can also have another client over here and then maybe we have another banker and then this uh client is associated with this banker as well as this banker and then maybe this banker is associated of course with this client as well as uh this client and then i would and it's getting a little bit messy but then over here then this client would be associated with this banker it's getting a little messy but this is a many-to-many relationship and we have to somehow figure out how we can do that with typeform now also of course a client can have transactions and they can have many transactions but each one a transaction is going to fall only to one client so this is a uh many-to-one or one-to-many relationship and this is very common sql knowledge i do expect you to have a little bit of sql knowledge if you're taking uh this this typewarm crash course or even mongodb knowledge and understand how we can relate data together uh so yeah so that is pretty much it and this is what we're going to be building with a typeform now if you weren't using any sort of orm typically you would have to create a bunch of the sql statement so something like create table client and then you would add all of the columns and there were data types and then for any relationship you would have to create your own foreign key and then you would have to specify that it's a foreign key and then for anything like subsequent if you want to perform a query or a mutation you would have to do something like select star from client where id is one if you want to get a data where uh if you want to get data where there is a relationship you would have to perform some sort of join it gets really really complicated in a real application you almost never want to take this route you never want to just write raw sql so instead what you want to do is you want to rely on orm to do all these things for you so typeform what it does is it it really simplifies the way that we create tables in our database and also really simplifies the way that we can query or mutate our data so for example over here instead of doing you know something like select star from client where id is equal to one we can just do client dot find one and then we provide the id now this is very simple but we can have something very complicated where we have a lot of different conditions or we have an inner join so the the sql statement can get relatively complicated but this statement you know it might get a little more complicated but not as much so that's why we ultimately want to use some sort of orm all right so that is introducing typeorm to you in the next video we're just going to do a little bit of setup hey everybody for this section we are going to set up our environment that we need to continue on with this crash course and luckily we don't really need much all we really need is a database running on our local machine and as i said the database of our choosing is going to be postgres now thankfully postgres is a relatively easy download so what you can do is you can go to postgres.org and slash download and then download whatever postgres version you need for whatever operating system you're using for instance i am using a mac so what i suggest if you are using mac well click on mac of course and then i suggest downloading the postgres app so just go to postgres app you could also use homebrew if you want to but i suggest going to the postgres app and downloading this over here and what this is going to give you is a nice gui kind of like this if you go over here you should be able to see it and what this does is it allows us to run a postgres server on our local machine if we go to server settings you can see that it's running on port 5432 apologies if this is small don't worry we're not going to be working with this as much i just can't really zoom in with this interface now what this does is it provides us with a server a postgres server on our local machine but we need a way to actually visualize our data and to visualize our data we can use another handy tool known as postico this is a free tool there is a paid version that gives us a little bit of extra features such as like filtering our data but really we can just uh get the free version so just go ahead and download that and once you download that you should be able to open up an interface it looks relatively similar to this but now you should be able to click on your databases and see the tables so you can see here i have a bunch of tables in our database i went ahead and i created a typeorm database using the postico interface to do that just go ahead and right click on postico and just say new database and call it whatever it is that you want i'm just going to go ahead and delete this database because i don't need that database but i created a typeform database where we're going to be storing all of our postgres data okay so that is pretty much all you need other than that you will need vs code or another text editor because we are going to be working with uh well coding out our application so you're going to need some sort of text editor preferably vs code in this section we are going to be talking about connections now what do i mean by that well typeform needs to connect to our database in order for it to do all the things that we wanted to do create tables query elements as well as update or delete elements now how do we do that well we have to provide some credentials to type arm telling it hey we we want you to connect to this particular database and that is what we are going to be doing in this section so remember the database that we have created is a postgres database that is running on our local machine now if you go to the postgres app and you go to server setting you can see that over here again very very small but you can see some of the credentials so you can see that the user name is my username which is latheharb as well as the port is 5432 i don't have a password for this database so let's go ahead and let's connect to that database using typeorm now to do that we first need to initialize a node application so let's just open up our text editor i'm using vs code for this and i opened up a brand new empty directory and all i am going to do is just npm init dash y inside of the integrated terminal and what this is going to do is it's going to create the package.json for us now another thing that we need to do is create a tsconfig.json file so let's do that tsconfig.json because we are going to be working with well typescript for this because typescript is is uh pretty much used with typeorm you can use it with javascript but most of the time if you're using typeform you're using in conjunction with typescript so let's go ahead over here we're going to create this file and this file is going to house a bunch of rules that is going to apply to our typescript application now for the purpose of this crash course i'm just gonna copy the rules that i have inside of my own uh type orm crash course directory private at the moment but will be public later so you can go ahead and just go over here to this url i'll have the description in the i'll have the link in the description below and just copy this and just paste it in there really all you have to do and now what we need to do is install a few dependencies so i'm going to install some dependencies and explain exactly why we need each one the first thing is we need to create an application a rest application typeform is an orm it doesn't create a rest api for us so we need some sort of framework that allows us to create a node rest application the simplest one and the most popular is express so that is what we're going to be using now because we're doing it with typescript we need some of the types associated express so we're also going to download ad types dash express and then we're going to need a few more downloads in order to interact with a postgres database we need to install pg so that is another dependency that we need we also need ts node which allows us to run our typescript application in development and then let's also install typeworm which is the orm that we are learning about and lastly we're going to install typescript because we're using typescript all right so that is pretty much it so let's do all these installations and my cat is trying to get in so if you hear some ruckus that's my cat okay so we installed this and now what i want to do is i want to create a start script and i'm going to call this dev for development so we can run npm run dev in order to run our application so ultimately what we want to do is we want to run uh index dot ts and we want to do that inside of a source directory so the source directory is going to house our application so let's create an index.ts file and for now let's just console.log hello so i'm gonna console.log hello and then in here what we can do is say hey we want to run our application now the thing is is we need to uh um what i prefer is that we run our application and when we make a change to our application we don't have to rerun it to do that i highly suggest installing nodemon globally on our machine i already did this so but if you didn't then go ahead and execute this command it's a great command to utilize in order to uh hot reload our project and not have to rerun our server every single time so once you globally install it what you can do is nodemon dash dash watch and then we're going to say that hey we want you to watch all of the files that are inside of any directory that contain e dot ts appendage right over here so we want you to watch for all of this and then once you watch for this and if something ever changes we want you to execute ts we want you to execute ts node and then we want you to execute ts node in the source dot index dot ts file so let's just quickly try this out so now what we can do is we can do npm run dev and this should work and we should see the console hello and which is great and then this is because this is a ts file typically things won't work if you don't use ts node now if i were to add some exclamation marks you can see that the server restarts and we see those logs okay terrific so that works as expected all right so that is pretty much all we really need to do to set up our project now let's just create the connection so inside of inside of the index.ts file let's create a main function so this is going to be the function that we are going to run in order to execute our project so we're going to run this function ultimately and in here what i want to do is i want to connect to postgres so let's go ahead and let's do that now in order to connect to postgres with typeform we're going to need to import some some some methods so from typeform what we are going to import is something called create connection very uh very descriptive pretty much tells us exactly what we need to do now in here what we can do is well create the connection so what we can do is we can do something like const connection is equal to a weight and this is going to be asynchronous creating this connection is going to be asynchronous so that's why we're utilizing async await here and we can say create connection and now what we can do is we can say some of the descriptions that we need so if we hover over this you can i guess you can't i'll i'll tell you exactly what we need we need to describe the database that we are utilizing so we first have to describe the type and this is the type of database that we can utilize or that we are utilizing so over here you can see we have mariadb mongodb mysql native postgres is the one that we're utilizing and then we have to provide it with the host if we're using some sort of production database or a database inside of inside of aws or microsoft azure we have to provide the urls from there but here we're just using localhost so this is localhost and then we have to provide the ports the port for a postgres database is typically 5432 and then we have to provide the username and you can get this by going to the postgres app and looking at the server settings and i'm going to say that well i'm not going to say the username for here is later and then i'm going to provide the password we don't have a password so here what we can say is our password is undefined and then we also have to lastly define the database remember we created a typeorm database in postico and so what we can do now is just say that we want to utilize the type form database very very very simple stuff all right so now that right there is going to create the connection so you can see here that well nothing happened and that is actually good because if if something failed and if the connection didn't happen then what ends up happening is is create connection throws an error and to show you this i can just say let me provide some invalid credentials let's just get rid of the b and if i were to save this you can see here that it throws an error and that happens when we can't connect to our database but if we were to connect properly we to get no error and everything seems fine now one thing is i really like some sort of validation saying that hey i did connect to the database and if it didn't i don't want it to throw an error i just wanted to tell me hey i was unable to connect to the db so what we can do instead is put this in a try catch block so let's put this in a try catch block and let's get rid of this variable connection we'll just await this for now and we'll say if we pass this then what i want to do is i want to say console.log and connected to postgres so we have connected successfully to postgres and then if if something happened if if some error occurred then we can either throw an error i'm just going to say console.error and i'm going to say unable to connect to postgres so if i were to save this we should see connected to postgres if i provide some sort of invalid credentials you should see unable to connect the postgres and of course if we want we can also throw an error typically we would want to throw an error so you know what let's just go ahead and do that let's just say throw new error and we're going to say here that the error is let's actually go ahead and just log the error itself for this so we're going to log the error and over here we're going to say unable to connect to db so again if i provide any valid credentials then i get a bunch of errors which is good so this is descriptive errors for me and but now i can realize that hey i provided invalid credentials and now i'm able to connect to the db so that's pretty much it very very simple connecting to your database is very easy now uh right now we're using localhost so this is completely fine the way that we're doing things but if we are actually wanting to connect to a real production database this is the way that we did things is not really the best way and the reason for this is because uh this these strings are pretty much in plain uh plain sight anybody can utilize these strings if you want them if they get access to this file anybody can call connect to our database now nobody can connect to this database particularly because well it's running on our local machine but again if we have a database in aws then anybody can connect to it if they have these credentials so what i highly highly suggest is store these in environment variables and the reason why this is a great approach is for one of course it hides all of these things and then therefore nobody can see them but also sometimes we have different environments and we might want to use different databases for those environments for example maybe we have a staging environment we want to have a database for staging we don't want to use production database for staging so we might want to do that i won't do it in this section because this is a crash course about typeorm and not really utilizing environment variables but if you want to i highly suggest taking the environment variable approach because that's typically how things are done in this section we are going to be talking about entities what do i mean by entities well right now let's go to our postico database right over here or postgres database using the postico tool and if i were to open this you can see that there are no tables so typically again the way that we create tables is with create table and then the name of the table command sql commands but again we want to do that with an orm notably in this case type orm so to do that we have to define entities inside of typeworm so in typeworm tables are basically entities this is what we call them so we can define our entities and then typeworm is going to create those tables with the those specific columns that we have specified so inside of our source directory typically what is done is we create an entities directory and in here we're gonna have all of our entities that is going to correspond to tables inside of our database so let's go ahead and let's create a client entity so to do that we're going to create a client dot ts file so in here we're going to create the entity that again is going to correspond to a client table inside of our database all right so let's go about doing that right now so of course we're going to need to import some things from typewarm now the first thing that we need to import is entity so this is a decorator that defines that what we want to do is create a entity so to create an entity what we typically do is we create a class and we export to this class so we do class client and then we want to go ahead and create this class right over here but of course what we have to do is also define that this class is an entity and that is where we use the entity decorator so over here we can say at entity and then we can invoke it like a decorator so this is the decorator so we do it right above and then inside of this decorator we can also specify the name of the table that we want so over here i want to be called lowercase clients now another thing that is very important is we need to extend the base entity so over here we can also get base entity and then we can extend the base entity over here and why is that very important well because this allows us to perform all of the crud operations that is necessary and actually for now i'll leave it on but i'll show you once we if once we want to perform these card operations i'll get rid of it and show you why it's pretty much impossible to do it without base entity but for now just take my word for it we need to extend base entity okay cool so in here we're going to define all of the columns and their associated data type so the first thing is let's say we want the name of the client so let's say that we want the first name so over here we can say first name and then we can say that the first name is a string similarly we can do last name and we can say the last name is a string and this is great this works fine but we also have to specify that these are columns so to do that we can just take the column decorator so the column decorator and then in here we can say at column and then over here similarly we can say at column and this specifies that hey this is a column all right cool so that is really all that we need to do and now what we can do is we can also say that hey at column you also want to specify another column and we want this column to be an email and the type here is just the typical typescript typescript types so over here let's say we also want the email to be unique so inside of the decorator the column decorator you can provide an object and have multiple different options so over here we can say unique and then we can say unique is equal to true so we can also specify that if we want to now let's go ahead and let's create well our clients need to have a card number so let's say column and then we're going to say card number and then we're going to say string and then over here let's say that the card number also has to be unique and let's say that the length of the card is always 10. so the length of the card has to be 10. so we can also provide that and this what it does is just creates constraints inside of our database like unique constraints and over here does some sort of check constraint to check that the length is equal to 10. so of course we can add multiple columns within here it doesn't also always have to be a string we can also provide a number so well a client is going to have a balance so this is how much money they have inside of their bank account so we can say balance and we can say this is type number now the thing is is this is going to be by default type integer now when we have something like when we're dealing with something like money we probably want to use another type known as numeric so to define the type we can also over here instead of simply relying on the typescript type what we can do is simply just say type and then we can define whatever type that we want to use in this case i want to use numeric so we can also do that as well let's also create another type where the type is a boolean so let's just say at column and let's say that we're going to have let's say an is active is active column and this is a boolean now let's say that we when we create this user we always want to assume that this is active so what we can do here is well what we can do is we can provide a default so we can provide a default so we can say that hey by default it's always true so if you don't provide this in the database it's always going to be true now another thing we can do is we can also change the name of the column so over here the name of the column is always going to be this name over here but if we want to for some reason have a different name inside of our class versus the name inside the database we can just provide the name and we can say hey we want this to be called active okay cool now we can do many things as well let me say let's say here we can also have multiple different types we can say something like column and we can say maybe we have some additional information okay so we have additional info so additional info and let's say that this type is an object so we can also have a type of object where let's say we need to know their age which is a number and for some reason we also want their hair color let's say hair color i don't know why bank would need that which is a string so over here what we can also do is say that hey this is going to be of type simple json so simple json and what we can also do is say if you don't provide this information that's completely fine so we can say that this is nullable so we don't have to provide this information so we can say nullable true by default i believe it's not a nullable false so we need to define noble true uh but if we don't define it i believe it's nullable false so we we actually have to provide it all right so let's look at a few more types so another type let's let's look at simple array for instance so let's say we want to provide an array of things so for instance maybe we want to know all of the family members their names and this is just an array of strings well over here simply we can just define the type of simple array and then and then that's pretty much it and we can also if we want to even default this to an empty array if we don't want to provide it all right so that is pretty much it uh this is yeah that's pretty much how you can create your um your your table inside of your database now how do we end up migrating this data inside of our database well we have to first tell typeform about it because right now it's just in a random file so now all we really have to do is import this because remember we exported it so we can import client from and we can say entities and then inside of the create connection we can also specify an array of all the entities that we are utilizing in this case we're utilizing the client entity so i went ahead and saved this and i got an error so what is this error so you can see here entity client does not have a primary column and the thing is is we always want some sort of primary id with our database so we need to create a column that houses a primary id so to do this we need to create while an id and an id typically has maybe an auto increment a unique constraint and and things of that nature so what we could do is we could do that manually ourselves but in type arm we can also use the primary column decorator so instead of just a normal decorator we can say that hey this is a primary column and what this does is it creates a bunch of the a bunch of the um the the constraints that are needed for a primary column and it makes that a primary key so we can say here that this is an id and then we can specify that this is a number we can also say that if we want that this is a string and it is of type of uuid so you can also do that as well and it will automatically generate uuids but i'm just going to say number and so over here is just going to auto increment and make this a primary key all right so that is pretty much it and if i were to save this now it has connected to postgres okay cool so this this seems fine but now if we go to post to co and if we refresh you can see i still don't have that table and that's because we need to perform a migration and instead of migrations what i like to do because this is so much easier is go to create connection and in here i'm just going to say i want you to synchronize everything so if we were to save this it's just going to synchronize all of the entities that we have specified over here so now if we were to refresh you can see that we have our client table i hope this i really can't make this any bigger i truly apologize maybe in the next section i'll figure out a way to make this a little bit bigger let's see if there's any like zoom in seems like zoom is gone here i'll oh hopefully i can try to make it bigger but you can take my word for it that we have created a table where we have an id first name last name email card number balance active additional info and family member so we have created these entities and hopefully you can see it on your end as well now the last thing i want to talk about is some of the some of the other entities or other decorators that we can utilize so for example we may want to have a created at or an updated add column so how do we do that well all we really have to do is use some of these special decorators we have a create date column and we also have a update date column and then in here all we have to do is just say hey we want to use this decorator and we want this to be a created act and this is of type date now what this thing is going to do is it's going to well every single time we create a record it's going to well append the date to this column as well so it's going to automatically by default create it to date dot now so this is what's really cool about these decorators and there's a lot of little decorators that we can utilize and we're going to look at them throughout the crash course so we can also use update date column and then over here we can say updated at is the column name and it's of type date all right so if we were to save this and refresh our database remember because we synchronized it we should see those columns over here and you're going to see an action that again we never have to specify these these will automatically insert all right so that is the basics of entities i hope that makes sense i also tried my best to figure out a way to zoom this and if i can't really apologize uh in the next session we're going to talk about inheritance in this section we are going to be talking about inheritance and to explain what it is let's just go through an example so now let's say that we want to create another table and thus another entity for the bankers because well we have clients and we also have bankers so let's go ahead inside of our entities directory and let's just create a banker dot ts file very simple let's just go ahead and just copy everything over here and let's just paste it in there and let's just get rid of some of the things that we don't want so the banker is gonna have an id as well as a first name last name email and the banker is also going to have a card number however we're not going to store the bankers um a balance as well if we don't really want the store is active because they're always active because our employees we also don't care about their family nor do we care about this additional information okay cool so over here really um we're using a lot of the same columns that we have here inside of our client now let's also add another column and this column is going to be a number and this is just going to be the employee number so the employee number and let's go ahead and let's just add some constraints if we want to here as well so we're going to have a unique constraint we're going to set that equal to true and we are also going to have a let's say a length of 10. okay so this is cool this is terrific let's just change this to banker over here and similarly let's change this to banker and now what we can do is we can go to the index and we can also say banker over here notice that it actually auto imported so we can save this and because we have synchronized to true it seemed like something failed here actually um entity banker does not support length oh yes of course and that's because this length length doesn't work if it's a number as to be a string so we're going to say string for this so you can save this and now if we go over here to postico we go to our database we see we have a banker and the clients now the only thing is this is kind of really repetitive and the reason for this is we have a lot we're using a lot of these same columns between client and banker for instance we have the primary column as well as we have the first name last name email and card number over here we have first name last name email and card number now the only difference over here is we have employee number which we don't have in the client and then over here we have in the client we have the balance and the is active and additional information and family members which is not located in the banker but there's a lot of duplication so how can we fix this duplication well we can fix this duplication with inheritance and we kind of looked at inheritance a little bit with this over here because over here we're inheriting a lot of the properties associated with or all of the properties associated with base entity and i know we haven't looked into what base entity is but we inherited those properties so what we can also do is inherent well pro a pro all the properties from a separate class that we have created for instance what we can do here is inside of the entities directory let's create a utils directory i guess it's some this isn't really a util but let's just over here let's create a person dot ts file and again let's go ahead and let's just copy all this over here and over here we can create a person entity so this person entity we're never really going to save it in our database so really no need to have that in there and we can say that this person entity extends the base entity and we can say that the person entity has the id the first name last name email and card number but then everything else it doesn't really have so let's get rid of all of these other things over here so this these are basically the columns that it has in common between the client and the banker alright so now that we have saved that what we can do now is we can get rid of all of these columns that are redundant so we can get rid of them and instead extend person so that way we get all of the columns that are associated with this person class as well as the base entity because we're extending that over here so we're extending person and that way we don't have to have all this redundant stuff so if i were to save this you can see that it actually still works completely fine and now similarly we can do over here in the banker we can get rid of all of this and we can only have just the unique stuff to the banker and instead here extend person notice it auto imported and it's saved and now if we want to if we want to add uh we can also get rid of like base entity if we want to or primary column because we're not using that anymore similarly we can do that in the client and get rid of base entity as well as this over here but now uh let's say we need to create another column that is for both client and banker maybe we also want their maiden or we want their middle name we could just add that simply over here we can just say middle name and we can save that connect to our database and now if we go to banker we should see again very small but at the very end over here we should see middle name similarly if we go to client we also uh don't see the middle name let me quickly refresh this okay we do see the middle name over here okay so that is terrific and that is inheritance which reduces uh duplication let me just quickly just remove the middle name because we don't really want that and that should remove it from both the client and the banker in this section we are going to be talking about relationships so how do we relate data together for example so far we have created the client as well as the banker and we have said that hey for this we're gonna have a many-to-many relationship for instance many clients can have many bankers and then many bankers can also have many clients also we also want to create a relationship between a client and their transactions so one client can have many transactions but any one transaction will have just one client now let's just begin with the one too many and many to one relationship because that's a lot easier to work with and then we'll move on to the many to many relationships so let's go ahead and let's create the transaction entity so we'll say transaction dot ts and we're going to create this entity all right so let's go about doing that right now so the first thing that we're going to need to do is just a bunch of imports from typeorm so let's go here type orm and we're gonna of course need something like base entity the column uh and then we're also gonna need entity so all these you know the basic things and then over here we're gonna use the entity decorator we're going to call this transactions transactions very very simple and then we're going to export a class called transactions and then we're going to extend the base entity very very simple stuff and then over here let's just create a very simple table the first thing we need is the primary column so let's just use the primary column decorator actually the thing is is primary column we're going to have to generate the primary column so what i suggest doing instead is using primary generate column so if we go here primary generated column this automatically generates it for us and the same will hold true over here so let's just use primary genny rated column and similarly over here we'll just say jenny rated okay cool and we do not need the create and update columns for this section so we can save that and then over here we can say that over here we have an id and we'll say that this is a number now there's some syntactical errors here and that's because this extends not extend let's just quickly just create the rest of this so let's just say over here we want a column for the transaction type so whether it's a deposit or a or a withdrawal so over here we can say that this is a string but you know what i want to also say that i want to limit the these the string so it can either either be a deposit or withdraw so what we can do is we can just create an enum which is supported in typescript and we can say transaction types and then we can say that we have deposit and deposit equals the string deposit and then also we have with draw that equals the string with draw so very very easy so now how do we use this enum well very easily all we have to do is just specify the type and we can say that this is a enum type and then we have to specify the enum by saying well enum and then saying the transaction type now we have an error here because well i need a quote or a comma and the last thing is the amount so over here let's just do column we're going to say of type numeric because we're dealing with numbers here so type numeric and then we're gonna let's go over here and let's just say this is the amount and it is of type number okay so that is pretty much it we can also have the created at an updated act if we want to uh for now i think that is fine so let's go ahead and save this and let's go to here and let's just import this really we can just import it by saying transaction china this one right here transaction we call the transaction i prefer to not have it plural so over here we can say transaction similarly here we can say transaction and now if we i managed to zoom it in a little bit but the column names are not zoomed in unfortunately but if i were to refresh this we should see a transaction table with id type and amount okay cool so now what we want to do is we want to relate many transactions to a user how do we do that well typically when we want to do a one-to-many or many-to-one relationship the entity that that is the i guess the many side of that one to many so um for instance in this case it would be the transactions because we have many transactions that can belong to a client that entity is the thing that houses a foreign key which maps to the primary key of the other entity that is the one part of the one-to-many relationship so for here we would have a if we were to create a transaction we would have some sort of client id and it would link to the primary id of the client that this transaction belongs to so how do we do that in typeform well we don't have to go ahead and create a id and and then link it together all we really have to do is specify the relationships and everything else will be done for us so how do we do that well in the transactions so what we can do is we can say that hey this transactions entity has a mini to one relationship so we can actually import that from here many to one relationship so we imported it from typeform and then here we can say is so the first thing that we're going to do is say that hey what is this ultimately going to return well this is going to be a function that returns clients now we're going to have to import the clients so over here we can just do import client from from the client so you can see here that it's going to import the client and then the second parameter is going to be is going to be another function but this time we're going to have all of the clients and then we want to return the the client.transactions now this might not make a lot of sense to you do not worry the reason why this is failing is because we also have to do the same thing with the client as well but for now this is fine now another thing we can also specify is a join column because ultimately we're going to want to create a join and because this is the many to one we're going to go over here and specify a join and we're going to say that the name of the primary id is client id and then over here lastly all we're going to have to do is say that this is going to be a client and it is of type client so over here we're specifying the relationship this is a mini to one relationship over here with the at join column decorator that i auto imported from here we're specifying the name of the primary key and then over here we're specifying just the ultimate ultimate column essentially so what we can do at this point is say transactions.client and we should be able to get our client now the reason why this is failing is because we also have to do the exact same thing for the uh client section so over here let's go here and then here we're going to have to do a one too many relationship so here we can say one too many again i auto imported it but you can import it manually if you want and then we're gonna do the pretty much the same thing here so the first thing we're going to have is a function that says that we are going to return transaction so this is this transaction from the entities right over here again auto imported it and then we're going to say that what we ultimately want to do is we want to get the transaction and then from the transaction we want to do we want to return transaction dot client so that is specifying the relationship and then over here we don't really have to have a join column because we don't have a foreign key for this one remember the foreign key is usually on the many side not on the one side so over here we can just say transactions and then we can say transactions is going to be a but this is an array of transactions and you can see now that it's completely fine because we actually have a client here so when we return clients from a transaction that's fine over here we have transactions so when we return uh transactions from a client that's okay so if i were to save this everything should be fine and notice again that uh we didn't create a we didn't directly create a column with client id but at the end of the day if i go to the transactions and i will refresh you can see here that we have the client id so right there we have specified a a many to one relationship and or a one-to-many relationship all right so that is all we have to do we'll talk about how we can insert data a little bit later but uh now let's talk about many-to-many relationships so remember that in a minute to many relationship a banker can have many clients as well as a client can have many bankers so how are we going to specify all of this well we're going to do that with well as you can imagine a mini to mini decorator so inside of the client let's just create this right now uh so let's go actually you know what let's move on to the banker we haven't worked with the banker a lot so what we're going to do is we're going to create a many to many relationship so over here let's specify the many to many decorator and here all we have to do is specify a function that returns well the type and we ultimately want to return the client and so let's actually auto import the client as well so over here you can see that we auto imported the client and this is going to return well multiple clients so it's going to return multiple clients so an array of clients but over here we can also specify a join table to specify the in the overall so okay i think i should probably mention exactly how many too many relationships work so because we have a many-to-many relationship we can't have just some foreign key in an actual entity we actually have to create a separate table so we're going to have to create a separate table and all those tables are all that table is is going to contain two columns the first column is going to be the id of the banker and then the second column is going to be the id of a client and those two columns are going to link those two entities together for instance if we had let's say five clients with id one two three four five and then five bankers with id one two three four five and we wanted to link uh the client with id one to the bankers with id one two three then we would have in the separate in a separate table we would have id one for the client and then id two for the banker id one for the client id three for the banker id one for the uh client and then id uh uh id three for the banker i'm not sure if i kind of mess that up but let me just say so let's say over here we have again five clients with um with id one two three four five and then similarly with the bankers so over here we can have something like client id and then over here we can have banker id and so over here we might have one and then over here we might have three over here we can have one again so you can see here that the same exact client has two different bankers but then we can also have something like client three is also working with banker three and that is how we perform many-to-many relationships so we need a separate table so to do that we're going to have to define at least on one of the many to many entities or decorators over here because we're going to specify a many-to-many relationship here but we only have to do this once you have to define a join table so over here we're going to say that this the name of this table is going to be bankers and then clients and then we're going to specify the join columns remember we've done this before so the first column we can call it banker or banker id i'll just call it banker and this is going to reference so this is going to reference the id of the banker so it's going to reference the id of the banker now similarly we're going to have to now create the inverse join column so this is on the other side and so this is defining again this is defining the join column of the banker the inverse join column is just going to define all the other things for the the client so the inverse join column then over here we're going to say client and we're going to say for client this references the id so the primary key so if i were to save this everything should probably fail because we'd have actually i guess it didn't fail but now what we can do is go over here to the client and then specify the many-to-many relationship in the client so over here we can just do similarly at many to many and over here we can also say that this is a function that returns banker type let's actually go ahead and auto import the bankers now what we can basically do is say bankers and then banker an array of bankers you have to specify the join column because we already did that over here now what's great is if i go back to our database you see that i get this table where i have a banker column and a client column if you can't see it take my word for it but so far that's really all we have to do we can also specify one-to-one relationships that work very similarly to the one-to-many relationships using the one-to-one decorator but yeah that's how we set up relationships but that is uh pretty much it really for how we create tables there's a few more things that i'll talk about but now we're gonna start talking about exactly how to interact with our uh tables like inserting data querying for data etc in this section we are going to be learning about creating data which is basically adding a record inside of our database just a fancy term for that so let's go about doing that with typeorm now to do it we have to first configure a few things we have to create our express application because again typeform does not build our rest api we need some framework or we can use plain vanilla node i prefer to use express so we need to configure our express application first so let's do that right now so inside of our index.js let's just import express from express so we'll do import express from express if you don't know express really easy honestly if you know any rest api i feel like you should be able to follow along so over here we have our express and really all we have to do here is a few things so once we connect to our database we want to connect to our express application and get it up and running so we do that inside of this try block so we can do app.listen and we can listen on port 880 and then over here we can have a call back and we say console.log now running on port 880. so okay so that's good but you might be wondering okay well app is not defined which makes sense i actually forgot to do this you also have to create your express app so we do app is equal to express so now we have created our express application that's really almost all we have to do so if we save this we should see now running on port 880 another thing that uh we need to do is add the express middleware and we add in the middleware with the dot to use and over here we're just going to use the express json middleware and what this does is allows us to parse the body of any request that we made okay cool so that's it that's really all you have to do to configure our express application let's go to the source directory and we're going to create our routes so let's create a routes directory and to begin with let's create a route that allows us to create our clients so over here we're just going to say create underscore create underscore clients of course that's our client we're going to create just one client at a time so we're going to have that in here and in here we're going to do uh some some a little bit of express setup to create this route so we're gonna do import express from express and then we're also going to do const router this this allows us to just create a very easy route that we can later export so we can do express dot router again it just allows us to create a route if this doesn't make sense to you don't worry really all you have to do know is that now we can do router.post to make a post a request and we can also export this uh router so let's export this router uh and we can say we want to export the router as we're going to rename it to create client router and now inside of the inside of this post we're going to need a few things we're going to need the path which is going to be api client so we're going to make a post request to api client then we are going to hit this callback and perform some action this is of course the request and the response so we're going to hit some action now to actually utilize this let's actually just uh do a res.send of hello and let's just make this a get request for now actually no i'll leave it as a post request and to actually utilize this inside of our index.js we have to utilize middleware so now what we can do is we can say app.use and we want to use this so we can say create uh was it create client hopefully i can auto import it yes i can i auto imported it from here so we can save that create client router that's all we have to do and now we have configured our api so if we go to let's say postman let's open that up this isn't anything typeform specific really this is just setting up an api hopefully that made sense i kind of just went really fast with it i do expect you to have at least maybe not expressed knowledge but knowledge of an api so i do expect you to know what like post requests are and hopefully you can understand the syntax so if i were to make this request right now i have some stuff in the body that's okay but if i were to make this request and let's go here you can see that i have i'm making a request to localhost 880 which we have up and running slash api client and it's a post request and if i were to make this request we should see hello and we do okay cool so that works so now what we want to do is we eventually want to give you or inside the request have this data inside of the body so the first name the last name the email the card number and the balance and we want to eventually uh save that data inside of our database so we should see that data if i go to typeform and then i go to the client i should see that right over here so how do we do that well we're going to be able to do that through this right here so through this class and the reason why we'll be able to do it through this class is because we extended person and person extended base entity and so what we can do is we can perform many operations on that class because we extended base entity so what we can do is we can do a create operation update operation save operation whatever it is so over here what we can do is first of all let's just get all of the data from the body so we can do that by destructuring out the first name the last name the email the card number as well as the balance from the rec dot body and then what we can do is import the client so let's go ahead and let's import that client from the client entity so it's going to be in the client entity and we can import the client and now again because we import we extended base entity we could do a lot of different things we could do like client dot count dot create dot delete dot find you can do a bunch of different methods and now let me just show you if we didn't have base entity so if we didn't import that then we wouldn't have those methods so you can see here all those methods are gone and that's the reason why we have base entity so let's just save that and so now we can do client dot let's say create so we're going to go ahead and we're going to create something now when we execute this it doesn't actually save it in the database what it does is it it really is just constructs that object for us and i'll show you how we can save a little bit later but what we can do is we can do something like const client is equal to client.create and this is going to take in an object with all of the parameters that match the ones inside of the entity so we can see here we want to have the first name be the first name from the body the last name as well be the last name from the body the email the card number which will be the card number from the body and then the balance so this is really all we need so now what we can do is save this to the database so now what we can do is we can just say await and maybe we probably want to async this because this is going to be asynchronous this right here is not asynchronous because all we're doing is creating an object now we can do await and then we can say client that we created dot save and that saves it to the database and what we can also do is return that client that we have created and that's really all we have to do to create data so if i were to execute this request with i went to the body and i changed it to json format and i have over here the first name the last name email card number remember the card number has to be uh 10 in length and we also have a balance of 0 starting off so if i were to make this request you can see i get back my data and i actually also get back additional data like the id is active family member remember we we have those as defaults as well as this one it creates this for us on the spot but now if we go to our database and we were to refresh we can see our data so we see here idf1 al teddo um we have the email and we have a bunch of other things cool so yeah so that was really really easy i highly encourage you guys to do it for the banker as well uh because we want what we eventually want to do is we want to also learn how to perform it ourselves so if you want you can pause the video but if not i'll just uh create the banker route as well just for practice and this is going to be very similar if not the same really all we're changing is uh some of the uh some of these things as well because over here maybe you have the employee number whereas for the client we don't so i'm gonna do is i'm just gonna quickly copy and paste this and i'm gonna create a banker route so create banker.ts file and then in here we're going to change this we're going to do a little bit of changing so we're going to do api over here this is going to be banker over here this is gonna be banker and over here we're gonna do banker might as well change this also to banker and we'll say banker here now the data is going to be a little bit different and not much difference really instead of balance we're going to have employee number and then here instead of balance we're going to have employee number which is gonna be employee number and over here we're just gonna save or we're gonna return back the banker that's it very very easy uh also let's uh change this as well so let's just say create banker route so inside of the index.js very easily now we can just use app.use and then we're gonna have create banker router it didn't auto import now we did okay cool that's really all you have to do so now if i were to make this request so if i go to my body i have sally bartida and then we have the email the number as well as the employee number if i were to make this request it completely fails i wonder why that is so uh null value in column balances violates not null constraint [Music] hmm i actually wonder why in the world does the banker have a balance it does have a balance which is very strange it shouldn't even have a balance so either i mistakenly added it here which i did i did mistakenly add it in the persons and then irritated it let me see if it has it in here because it should have the balance on its own in here and it does okay so i just mistakenly added it there so now what we can do if i were to refresh this you can see it's gone and if i were to make this request again you can see that again if an alpha refresh you can see that we have our data all right so that is creating data now in the next few sections we're going to learn about how we can perform all of the other card operations as well as how we can relate data we talked about how we can form relationships in a table level with foreign keys mapping to primary keys but how do we do that when we want to insert data well typeform makes that extremely easy and to go through with this we're going to go through two examples forming a relationship between a client as well as a banker but we're also going to form a relationship between a transaction and a client so let's begin with the transaction client because that's a lot easier because at that point we're only dealing with a many to one relationship so in here let's create another route and we're gonna call this the create transaction row so let's go here and we're gonna say create trans transaction dot ts and then in here we're just gonna do the usual let's just do it from scratch we're gonna import express from express we're also gonna import we're gonna import transaction uh from not from typeworm but from from the entities and then from transaction okay cool and then now what we're going to do is we're just going to create our route where the routers we're going to call this router and we're going to do express a dot router this is just very simple stuff okay cool why is that giving us oh events no from express please okay so now let's create a post request because we're creating a transaction and we'll just call this slash api and then remember we have to link the transaction to a client so we need to have the client's id coming from somewhere or some sort of information that will allow us to identify the client in this case really the only uh reliable thing is the client id so we can actually get that from the params so we can do something like slash client and then we can do colon client id and this is going to correspond to the parameters so now what we can do is something like when we make this request it'll be slash api client slash one and that will be this will be the client id over here and let's also specify that this is going to be transaction so we'll say slash transaction let's make this asynchronous because we're going to be using a lot of asynchronicity okay so this is really all we have to do so now what we can do is let's extract the client id from the clients or for or from the params from the rec dot params and to create the transaction of course we're gonna need the amount that we're going to be doing as well as the type so whether it's a deposit or a withdraw so we can do that through getting the type and the amount through the rec dot body very very simple so the first thing that we need to do is get this id and actually make sure that that id belongs to a client and if it does belong to a client then we want to get that client back so we'll talk about how we can fetch data a little bit later in the lecture but for now i'll show you how to do it so we can do something like const client and then we can import the client entity so we can import that entity so the client entity and so what we can do is something like a weight and then client dot and then we have here fine so we have a bunch of find methods find gets an array find in count well you can probably assume what that does he gets this array and it counts how many there are over here this finds by ids find one or fail so that throws an error but what we want to do is just find one so we want to find one and we're going to provide the id in this case the id is the client id now the client id is going to be a string so we're going to have to parse it into an integer so we can do that very easily with parse int all right so that is pretty much it now if we don't get our client what ends up happening is it returns null so what we can do is we can say hey if the client doesn't exist we can do something like return res dot json and we can return the message a message saying you know custom message say client not found very very simple and very easy so now let's go ahead and let's create the transaction so okay we have a client that client does exist because we passed this if block so now we want to create the transaction but we also want to link the transaction to the client well we can do that by well let's create the transaction so we'll say cons transaction is equal to and we don't have to await this because we're not we're not actually saving it to the database we can say dot create and then over here the first thing that we have to do is specify of course the amount as well as the client and then you might be thinking okay well the client id then i just specify the client id as client id but that's not how it works here because in the entity in the entity in the in the transaction entity we have no column called the client id even though in the database we do have a client id but we do have a client and that is supposed to be the whole client so all we really have to do is just pass in this whole client so what we can do here instead is just say client just we want to pass in the client and what this will do is we'll we'll get that client's id and save that id in the client id column so all that really does is just create it now we have to actually save it inside of our database so now we can just do transaction.save we can also await this now one thing that i want to do is is if we decide to deposit money or withdraw money i want to actually change the balance of the client so let's go about doing that right now so we can say something like if the type remember the type is something the thing that we passed in uh triple equals and remember the type can either be deposit or or withdraw because we specified the enum let's actually get the transaction types from here we can say if the transaction type is equal to deposit then what we want to do is we want to get the client and what we want to do is we want to get their balance as well and then we want to change the we want to make the client's balance equal to whatever the client's balance is plus the amount plus the amount and similarly we can do an else if and then over here what we can do is if it is if the type is equal to the transaction type dot withdraw we can do the client balance is equal to the client balance minus the amount very very simple stuff so now all we have to do is actually save that so we can do something like client uh if i can spell client correctly client dot save and then maybe if everything is successful we can just return the new client or maybe we can just return a message because over here we have a message so might as well just return be consistent return a message saying um transaction added or something like that or we could just return the transaction honestly whatever it is that you want so last thing we have to do of course is just export that router so we'll export it as create so create a transaction router so that's all we really have to do pretty much it um okay so now what we can do is go to our index dot ts file and then inside of our index.ts file this is going to be called the this is from the create transaction so it's going to be called to create transaction router we're also going to do an app dot use and then create transaction router very very simple stuff if we were to save this everything is working perfectly so now if i were to make this request so let's say over here you want to create the transaction so let's actually uh deposit first let's actually give ourselves some money so we want to deposit and the client is going to be client to one so localhost api client client one transaction so at the end of the day what we should get is inside of let's actually go to the client so the client their balance should be 500 which i believe was the amount remember client id of one so 500 and then we should also create a transaction which we don't have thus far but the client id here should be one so hopefully this works i really hope this works it's supposed to work if it doesn't work then i made an error so if i were to make this request that doesn't work darn it what is a problem um invalid input value for enum deposit so apparently this value is wrong so let's go to our uh create transaction or actually just go to our transaction entity and i misspelled a deposit that was the problem so let's save that apologies for that so now if i were to make this request again it has worked so we have transaction added now how do we truly test if it works well let's go to our transactions and if you refresh you can see here that we have a transaction it's a deposit out of 500 and it corresponds to the user with id of one and then if we go to typeform and then we go to our client we should see their balance now at 500. all right cool so that is terrific so now what we want to do is we want to also somehow create the connection between the banker and the client so let's create a route for this so let's go over here to our routes and then we're going to call this route i wonder what we should call this route let's just call it connect banker to client so let's go here and let's say connect banker to client dot ts all right very simple honestly i'm gonna let's get rid of all these files they're kind of getting messy what i'm gonna do is i'm just gonna copy some things here i'm gonna do a little copy and pasting just for the um the uh just so you get the setup done it kind of takes a long time to set up so we don't need this we are eventually going to need to import the client as well as the bankers let's just get that out of the way so from entities we want to get the banker as well okay so that is cool uh let's just get rid of everything here everything inside of the route we're going to make this a puts request because we're really just updating things and we're going to call this uh what are we going to call this yeah let's just call it connect banker to clients now really all we want to do here is get a banker get their id and then get a client get their id and just place it in here so if you want to connect sally with ted then we would have an id of two for the banker and then idf1 for the client very very simple so let's go about doing that now the first thing is we need to of course get the client and with the banker so we can say um to get the banker we'll just use the banker id and the params so we want to say that hey we want to use this id for the banker and then for the client we want to use the client id all right very very easy so now what we can do is let's just get those ids so we'll do rec dot params and we'll do banker id client id so the first thing of course that we have to do is we have to get our client and our banker so what we'll do is we'll get our clients just to see if they exist of course so we can say something like find one and then we're going to use parse int and parse end of the client then we can say banker is equal to await the banker dot find one parse ends and then we can say the banker id so here we're going to get the banker i'm going to do a quick shortcut and just check if both are available so we can do something like if the banker and the client exist then what we can do is uh if they both exist then uh we want to do logic of course but if they don't exist you know what actually let's just say if they don't exist so if they both don't exist what we want to do is we want to return res.json and then we're going to do um we'll still do a message saying banker or client not found very simple message so if they both do exist and what we want to do is we want to connect them together now how do we connect them together well very easily let's go back to the banker entity let's get rid of all these other ones and let's just go back to the banker entity remember the banker entity has this column called clients and that is going to be an array of clients so very easily really all we have to do is get the banker dot clients not card number dot clients and then or this should be or not and sorry so we're gonna get the banker dot clients and then all we're gonna do is we're gonna say that hey this is gonna be equal to an array of all the client that we have specified now the best thing to do is probably say that hey this is going to be an array of all of the clients that already exist plus this client that we have created so plus this client that we have created right over here very very simple and we you might be thinking okay we can also do it the other way around you can do something like client dot bankers is equal to an array we can't really do that even though that does work we can only do this kind of connection with the um with the entity that has the at join table so you can have it for both if you want but i just put it for the banker at the end of the day it's going to do the same thing so now what we want to do is well very simply just go ahead and just await so we can do a banker dot save and then we'll just return something here so we'll say uh banker connected to clients so we can save that and that's that's pretty much it so now all we have to do is just import this inside of our index.js file so we can do from route slash connect whatever to banker you can use middleware here app.use use that so now what we want to do is let's actually connect someone so we should see some data over here so we have a banker sally of two and then we have a client with an id of one so let's go ahead and let's make this request and so this is going to be connect banker to client so we have this request the blank banker has an id of two the client has an id of one if you were to make this request it fails of course because something always has to go wrong it says here oh okay so let's uh yeah so let's just get rid of this yeah this doesn't work so let's do that and if i were to make this request again now the banker is connected to our clients okay so nothing really changes here so nothing changes but the only thing that changes is inside of this table we have this connection so that's really all we have to do and that's how we can connect data together and the same thing works with the one-to-one relationship really if you understand this concept you'll understand how to do it with one to ones cool in this section we're going to learn about deleting data now there's a lot of things that we could possibly delete in this crash course we can delete a client a banker or a transaction realistically we should be able to do all of them but for the sake of this crash course i'm just going to delete the client and some problems are going to arise they're going to be consistent throughout if you want to delete a banker or a transaction and we're going to solve those problems in this section so let's go ahead and let's create a route responsible for deleting clients so let's go over here and we're going to call this route well delete client because we're going to delete one client let's do it from scratch as well it's kind of annoying doing everything from scratch but it is what it is so we'll just import express from express we're going to use the client from the entity so i might as well also import that so let's go here by the way if you're wondering why we can use import without setting up babble it's because we're using typescript and typescript can compile it down to regular javascript you might be more used to require which is pretty much the same thing but i like this syntactically a lot better okay so let's just create the router so we're gonna do is equal to the router and then let's do router and this time it's gonna be a router dot delete and this is going to be slash api we want to delete the client and we also have to say which client we want to delete so we'll provide the client id this is going to be asynchronous so might as well right now just make it async await and so to do this well it's really really easy so first of all let's just get the client we're going to get to the client id and then we're going to do a rec dot param so we're going to get the client id from the rectal param and very easily what we can do now is simply just do client dot delete and then we can provide the primary key which is the client id so we can just parse into this and then we can provide that id very very easy and then over here what we could do is maybe even get the response and just return the response that's it seems super super easy all right now let's actually just export this out so we'll export router as delete client router and now what we can do in here is just do app.use delete client router notice i auto imported it and let's just make this request so what we want to do is we want to delete client one very easy if i were to send this off it's gonna fail and the reason why it fails as you can see here update or delete on table what it does is gonna violate some foreign key constraints on the transaction and the reason for this is because if we if we delete the client so if we end up deleting this client the client with id1 will not exist however over here in the transaction we're saying that this transaction belongs to the client with the id of one and so well this is gonna this is this is not right because this is a foreign key similarly this is also gonna fail this is also gonna cause some failure because these are also foreign keys so how do we fix this well the best thing in my opinion and actually scratch that this really depends on your application and what you ultimately want to do you know maybe you want to keep the transactions and what you want to do is you want to just change the client id to null or what you want to do is if you delete the client you also want to delete all of their transactions as well as um the banker client relationship in this table that is what we are going to do and that is known as a cascade so when we want to delete something we're going to cascade the deletes to all of the relationships so to do that we very easily all we really have to do is go to the entities and define cascades so for example in the transaction so in the transaction entity we're going to say over here in the many to one relationship we're also going to provide an object and we're going to say hey when we delete a client we want it to again we can do multiple things we can set the id to null or we can restrict it saying hey we can't do that or we can perform no action or we can perform just a default action which is this or we can just cascade the delete in this case i'm just going to cascade the delete now this is great this is really going to solve a lot of our issues however we're still going to run into some issues and the reason for this is because we didn't cascade the deletion of this as well so if we were to make this request again uh oh i guess it did work surprisingly oh this also deleted i guess by default it does i thought i thought it would uh okay so i guess by default this actually deletes as well uh but now you can also notice that our transaction has been deleted cool just to quickly note uh i thought that what you we had to do for a mini to many relationship is go to the many to many relationship and similarly here also provide an object this is just going to be cascade equal to true in the many-to-many relationship um this is uh this is the syntax for it but i guess by default it is true whereas if we provide false i think we would have gotten that error so what we'll do just to quickly test this out is let's uh let's actually do the exact same thing for the um for the client entity so let's let's go ahead and let's do that exact same thing so let's go here let's get the client and then in the many-to-many relationship let's also provide that cascade of uh false so over here we'll say cascade of false okay so now let's first create that relationship so let's make this request and it says here that the client is not found that makes sense so let's create the client this client has an id of two so let's go here let's create the connection and now let's try to delete the client so if i were to make this request it's still huh i'm very kind of i'm honestly surprised so i guess the cascades are not really working uh that's how it is in the documentation but i guess that's i guess that's how it works so i'm a little surprised by that but what i'm gonna do is i'm gonna leave it as true i'm going to leave this as true so i guess by default or always this is always going to cascade but when we do a one-to-many uh one-to-many we always have to provide the on delete cascade okay cool so that is a pretty much it and i'll see you guys in the next section where we're going to learn about um getting data so finally fetching data in this last section we're going to be learning about how we can query for data so we've learned about how we can add data we also learned a little bit about how we can update data as well as delete data but we haven't talked about how we can query data so let's get into it now there's a few things that we can query we can query the banker the transactions the clients we're going to be uh querying the clients but it's a little bit more tricky than just you know having a dot find and finding all the clients for one because sometimes we want to selectively pick what we want we don't want to query for everything for example over here i already ran the request let's actually go over here you can see we have the first name the last name the email sometimes when we're querying we may not want this additional information we want may not want the family member for that specific route so we want to selectively pick what we want we also want to filter for what we want so maybe we want a filter for hey if the first name is equal to this or if the balance is greater than eight or something like that so we may want to do that as well uh so that is another filter option that we can do and lastly we may want to show the clients and their associated transactions so over here remember the transactions is in a completely separate table so we're gonna have to somehow get that information and show it for that specific client so query is more complicated than the other ones usually and the reason and the because of that we're gonna have to use a different approach that we have been using thus far so so far we've been using kind of the built-in methods the find one methods to delete methods and all these things but for querying we're going to use something else so let's just create the route right now so i'm going to say create create or sorry fetch clients we're not creating anything fetch clients dot ts and let's just go ahead and let's just do a const i always forget we're using typescript so we can use import so we can import express from express and then over here we can do const app is equal to express and then we can do um we can do we're not doing app what am i doing here we want the router because we just have a router we don't want to create a brand new express application so we'll do router and this is a get request so we'll just say slash get and it will have the route b api slash bankers and then we're gonna have async rec res let's just change that this is just the callback and there we go we should have our router might as well just export it so we'll export router as um let's say fetch client router okay cool so that's really all we need but now let's go ahead and let's start querying for our uh our clients so let's just use the traditional route of querying so so far we've been doing something like we've been getting the client uh entity which i can't seem to auto import so i'll just import it myself so let's import the client entity we will not be using this actually for this section so we're just going to get that client and then we usually do something like const client is equal to a weight and then we can await um we'll do client.find to find them all and we want to just find them all with no condition so we're just gonna leave it like empty and then ultimately we could just do a return res.json and we can return that client so if we were to go over here let's just do an app.use and we can say fetch client router fetch client router let's import that fetch client router why is that not in here it's in the directory which is kind of strange it's not auto filling fetch clients.ts import fetch client router is not i'm very surprised by this because it is in the it's in that directory let's see why that is well maybe because i'm not running the application could be that let's just do mpm run dev let's see if cannot find name fetch c did you mean fetch app.use fetch c and oh that's what it is okay so let's just copy that in here and let's paste that in there so that should fix it i'm not sure why i'm still getting this weird syntax highlighting so let's see is here an import path cannot be found for fetch clients dot ts so let's just get rid of the ts maybe there we go okay so if we okay cool all right so i really didn't want to pause the video there so hopefully you guys uh appreciate the debugging so let's just go ahead and let's make this request and they call this bankers apologies let's just change that to clients so let's just change it to clients it's not bankers so let's go here and i also renamed this as well to get clients and let's go ahead and make this request to api clients and if i were to make this request you can see here that well i get all of the data and you know what let's actually go ahead and let's create more clients so let's create um first of all let's uh create a bunch of new transactions so let's do maybe uh let's see how many transactions i have right now for that particular client so let's just do a refresh it seems as though we have this many transactions as well as we have our clients you know what that's this is good enough we'll just rely on that one person so we have so we have uh this one individual over here which is terrific this is cool uh but there's a little bit of a problem at the moment so if i were to make this request again oops sorry if i were to make this request again you can see here that we have the id the first name but we may want to uh you know selectively pick what we want similarly we may want to filter similarly we may want to get the transactions associated with that individual so how do we go about doing that well it's really hard to do it with this built-in method because this built-in method doesn't allow us to do joins it doesn't allow us to do complex filters it doesn't really allow us to selectively pick what we want so instead what we're going to rely on is is the create query builder from typeform and what this allows us to do is really just make sql statements it makes uh the uh it makes uh basically getting the data similar to how you would just write a sql statement and that's why it's really important to understand sql if you are trying to learn any type of orm so let's go ahead here and let's just use this and so to use this what we would do is something like this we would do okay cost clients is equal to await create query builder and then over here we're going to specify what table we want to use so over here we're going to say hey we want to use the banker table so or or rather sorry the client table not the banker table and then over here we just append certain methods like we are utilizing e-sql statements so we're going to say hey we want to select and if we want to select everything we just say client but if you want to select specific things then we would do something like a client underscore first name or client underscore and then if you want to select something else we can do client dot last name underscore last name so we can do that but for now let's just query for everything and then okay so where are we querying from well we will say we're querying from client and we're also going to aliase this as client and then you know if we want to have any conditions like you know where clause so we can have that in there and we can say client dot id and then is equal to is equal to a variable id so we can say here is equal to the variable id and we can get this variable id from either the rec body or the rec param and so to specify a variableness you just do colon and then whatever that variable name is and then inside of an object as the second parameter then you would specify the value of that variable so i'm just going to hard code it in but you can get this variable again from either the body or the param itself and then lastly we can use get one or get many so we can just specify hey that this is we want to get just one so now if i were to save this well i guess we're just getting one client so we can save that and let's just wait for the request to go if i were to make this request it works perfectly fine but now again we can have a lot of we have gonna have more complexity again we can also select whatever it is that we want so if we just want the first name we can select that so you can see here that i just get the first name if that's really what i want i can also get multiple things if i want to and i would do that by adding another select so i can say add select and this basically specifies that hey i want to add another thing that i want to select and i can say hey i want to select client dot last name and so if i were to make this request subscribe to that comma apologies if i were to go here you can see that i get both the client last name as well as the first name i can always change this to balance if i want to let's just quickly try that out so you can see here that i also get the balance and you can also add multiple more complicated selects so maybe what you could do is you can um add something like this so maybe add a select so if you maybe did a join you could do something like hey i want the sum so really just your if you have knowledge of sql then uh this really is just sql in just an object oriented fashion so you can say oh i want maybe uh the the uh sum of the transactions if if you did some sort of uh join so i want the sum of the transactions and i want this to be called sum or something like that and you can you can also add a group by so you can also have a group by to group by the client id and things of that nature okay cool so we won't do that because it's just a little bit more complicated but really if you understand sql you should be able to figure that out all right so uh let's just talk about this over here this right here is a where clause but we could always have multiple where clauses so we can have something like let's just get many and so if i were to save this right now i'm just getting an array of all the clients where the client id is equal to three so if i were to make this request you can see here that well we get an array of one person maybe we want to get an array of all the clients that are very very rich so maybe they have a net worth over 500 000 so we can do something like client dot balance and then over here this can be balance instead and then we can say that the balance is 500 000 and we want to do here greater than or equal to 500 000. so in that case if we were to make this request we should just get an empty array if uh we were to make this less than 500 000 then we're gonna make this request we do get that we can also have obviously things that are more complicated we can also have the and so you can say and so maybe we can say um over here balance minimum or minimum balance so we want maybe like people in between so we want people that are greater than that have more money or equal to maybe 400 000 and the client is less than or equal to the max balance over here we can specify the min balance and then over here the max balance let's just say lens is let's say 600 000. okay cool so if we were to save this hopefully i made no syntactical error so over here the client balance has to be greater than 450 000 but less than and less than 600 000 so we should get this individual right over here and we do if i were to decrease the max balance to 450 000 remember this is 400 and almost 500 000 and if i were to save this we can we won't get anything and really again this is just simple uh sql so we can add whatever condition that we want in here all right so the last thing that i want to do is what if we also want to see the transactions well to do that we're going to have to perform a join so we're going to perform the join right over here after the from and over here we're just going to perform a left join we can also perform a right join or we can perform an inner join now i'm just going to perform a left join and what i'm going to do is i'm also going to perform the join and select the stuff so i can end up seeing it so here what this does is it performs the join but it doesn't select it if we don't put and select i think it's easy just to say hey and select so you want to also select it and then inside of the join uh we're going to say we're going to specify okay well we want the transactions and we're going to call this transactions all right so over here we're saying that we want to get to the client transactions and what's great about this is that it's smart enough to figure out we don't have to specify any of the ids or anything what it does is it automatically is smart enough to say hey we want the client transaction so what it's going to do is just going to look at the foreign keys and then map them together and then over here we're just calling them transactions so we can change this name to whatever it is that we want we can call it casts for instance that's just gonna be what the the uh the json blob is gonna be called so let's just save that and if i were to make this request i have to fix this let's make this 500 000 make this request you can see here that we get the first name last name as well as the transactions okay and this is going to be an array of transactions of course that match this particular individual all right cool and that is pretty much it i hope you guys learned a lot of typeform and i'll see you guys in the next one

Channel: Laith Harb

Views: 6,184

Rating: 4.9695816 out of 5

Keywords:

Id: JaTbzPcyiOE

Length: 121min 23sec (7283 seconds)

Published: Sat Jul 10 2021