Video details

Modern backend with TypeScript, PostgreSQL and Prisma - Part 1


The goal of the live-stream series is to explore different patterns and architectures for modern backends with Prisma. Prisma Client is an autogenerated type-safe database client for Node.js (ORM replacement).
The series will focus on the role of the database in every aspect of backend development including data modeling, the API layer, validation, authentication, authorization, and deployment.
In this first stream, we will start with data modeling, CRUD operations, and aggregations for a real-world problem.


- And we're live, welcome. This is a new series that we're starting about building a modern backend with TypeScript, PostgreSQL and Prisma. In this series of livestreams and we're gonna also have articles accompanying, we're gonna be focusing on building a backend with TypeScript, Postgres and Prisma. And in this first part, what we're gonna do is we're gonna look at how we design the data model and perform CRUD operations with Prisma. And if time allows, we'll also look at how to query aggregates using Prisma. So before we get started, I'm gonna sort of cover what we have planned for the rest of this series. And I'll also share a link to the GitHub repository. So as you're all coming in, I see the comments coming in, Timo, Erickson and Timo great, wonderful that you're all joining us. So in this episode, we'll focus on the data modeling, CRUD and aggregations. And then in later episodes, we'll talk about the API layer, validation, testing, authentication, authorization, deployment, and even integration with external APIs. This sort of came to fruition as a result of a question that we sent out about some of the aspects of modern web development and specifically backend development that many of you find confusing. And so we thought it would be a great way for us to see how Prisma fits into this. And so, when building applications, one of the very, very first things that comes to mind is how to model the data. Almost all modern applications these days, they use a database and they rely on some form of persistent storage. And so how you model the data and how you understand the problem domain has a huge impact on how you actually develop the application. And so, I believe that taking this approach of really understanding the problem domain first, enables a really sort of a really fluid process of development. So I'm just gonna share the link now to the repository if you're interested in following along. Let's do that. Okay, so I just shared the repository and you'll see that the default branch is part one. So if you look inside, it's mostly empty. We just have the skeleton for the application. We have TypeScript configured and a Docker Compose file, which we'll look at in a second. And yeah, so let's start with some of the prerequisites and some of the assumed knowledge that is necessary. So in terms of assumed knowledge, the series assumes basic knowledge of TypeScript, Node.js and relational databases. If you're experience with JavaScript, but haven't had the chance to try TypeScript, you'd still be able to follow along. And while the series obviously focuses on Postgres, most of these concepts apply to other relational databases, such as MYSQL and MariaDB. In fact, Prisma supports both Postgres, MYSQL and MariaDB. And then beyond that, there's not much prior knowledge that is required in terms of Prisma. That's something that we aim to focus on and really clarify in this series. If you are, if you wanna follow the coding aspect of this, then you should have Node.js installed. And Docker is also very useful if you, well, we're gonna be using it to run a development instance of Postgres. But I mean, if you have Postgres locally installed, that should work, or if you're using a remote, like a hosted version of Postgres, that's also possible. Heroku offers a free Postgres database which is just great for development and I'll also share a link to that. Let's see. Okay, so I just shared a quick guide that we created on getting a Postgres instance on Heroku. Okay, so the starting point, as I said, is part one, the part one branch of the repository. And now I will share my screen. Let's see. Okay, so add that to the stream. And it's hard for me to know whether the, I'm zoomed in enough. So please leave a comment. Also, if you have questions throughout this live stream, feel free to use the YouTube comments. That'd be a great way for me to adapt and perhaps cover things that I may have overlooked. And so, let's start with the data model and the problem domain. So, as I said, when building a backend, one of the foremost concerns is the problem domain and by problem domain, or it's often referred to also as problem space, it's just a term referring to all the information that defines the problem and the constraints on the solution. And so by understanding the problem domain, the shape and the structure of the data model should become clear. And so today what we're gonna focus on is a grading system for online courses. What does that mean? It just means that we are gonna be storing users, we're gonna be storing courses. And each course can have multiple tests. And then for each test, we can have multiple test results. So if you're familiar already with some of the relational data modeling concepts, you can probably already see some of the relations here. And I've created a diagram using this really nice tool called DB Diagram to visualize that, which I'll just share on the screen. And so we have here the data model that I created that should represent the problem domain that we have. And so, all of these lines might be already confusing. These are what we call foreign keys, but the idea is that we start off with entities and entities really, either a physical object or an intangible concept. For example, a user represents a person which is a real physical thing. Whereas a course is a rather intangible concept. And let's talk a little bit about how this model looks like is. Let's start with users. So of course can have. So, I mean, a user is a person with an account and a user can be both a teacher or a student through their relation to a course. That means that if you have a user account in the system, that user account could either be, could be simultaneously a teacher for one course and the student for another course. And we'll see how we model that with the database, you can probably already see the course enrollment table, which creates essentially, a many-to-many relationship. So that was the user. And then we have the courses and I mean, a course this might even be a course. Okay, I will zoom in. I'm getting some feedback here that it's not big enough. So I hope you can see this now. Let's see if there's any other comments. Okay, yes, oh, sorry about that. Please increase the zoom, please zoom in, okay. All right, let's see if this is any better otherwise what I can do is zoom even more. All right, okay. Some built in, great. Okay, I hope it's fine now. Someone asked if it's prerecorded. It's not prerecorded, we're live. Great. Okay, now we can continue. Thank you for that. Okay, so we had a course and then a course can have multiple tests and each one of those tests can have multiple test results. And each of those test results actually has more than one relation. So in this case, a test result has three relations. The first one is grader ID. That is the identifier of the teacher, essentially who graded the test. And we have the student ID. The student ID obviously represents the student who sat the test. And then we have test ID, which links that test result to a specific test. So, if a single student is part of a course, he will have to take multiple tests and we need to be able to link each test result with each test. Then as we saw, a single course can have many tests, and this is even nicely visualized here if you can see the one and then the star next to the course ID field. And similarly, I mean, this applies to all of the relations in this diagram. I'll also share this link because I think it might be useful if you're following along. Also a nice thing about this DB Diagram is that you can export your database schema from Postgres and import it in, and it'll just visualize it for you. And I'm a visual learner. I think many of you out there are also. Okay, we're still getting some complaints about being unable to see. Let's see what I can do here. Okay, Timo Muller, I can hardly see anything. Okay, I hope this is a bit better. Otherwise I've shared the link. And Bram also asked, what's the name of the tool that I'm using. So it's called DB Diagram and I just shared a link. And then we also had a question about, is it possible to do aggregations from Berakna? And the answer is yes. We will hopefully if we have time, we'll get to that today. That's the goal. And let's see if there's anything else I can do to improve the quality. Okay, I've got an idea. I'm gonna just reduce the resolution of my screen. Let's see. And let's try to make this a bit bigger. This should maybe be a bit better. Otherwise you can just open up the link that I shared and that might help. Okay, cool so. And we had course enrollment. So I think, so we spoke about courses, tests so a course can have multiple tests. Each test can have multiple test results and each of those test results will be associated with a user for the teacher who graded it and for the student who sat the test. And what's really interesting is the way that this relationship between users and courses works. So this is what we would typically call the many-to-many relationship. And so the examples in the world of many-to-many relationships are, let's think, so blog posts and in a blog, you would typically have tags. And so each blog can have multiple tags and each tag can be used in multiple blog posts. And so similarly here, a single user can be associated with multiple courses and each course can have multiple users, either students or teachers. And so this course enrollment table will allow us to create those links. And this is what we call a relation table. And what you will see is that this relation table actually has an extra field. So this extra field is called user role, and that's just an enum type of either student or teacher. And so, through entries, through records in this table, we're able to associate multiple teachers and multiple students to a course. Now, done most of the work here in terms of designing the data model. And now let's look at how this would be done with Prisma. So with Prisma we have this idea of Prisma schema and a Prisma schema is a declarative configuration for your database tables. Under the hood, we're gonna use Prisma Migrate, which we'll actually use the Prisma schema to create the tables in the database. And then sort of similar to what we did with this entity diagram here, we defining the Prisma schema columns and relations between database tables. So let's hop on to the, let's see if we have any, let's hop on to the code editor here. And so if you open up the schema, trying to make this as big as possible, you'll see that we have these four models. And so with Prisma schema, each model maps to a database table. And so as we had in the diagram, maybe I can have it here on the side. We had five of these tables and as you see here, we only have four. So we're actually gonna need to add another one. And we're gonna call that course enrollment. And as you see, I'm getting these red squiggly lines. This is thanks to this wonderful extension that we have. Shout out to Carmen for really having polished it recently. And we're getting a lot of really nice features. So if you're working with Prisma, I highly recommend this for FIES code and hopefully in the near future, we'll have this also for other editors. And so this gives us a lot of syntax highlighting, code completion and formatting, and most importantly probably is linting. So this will help us to figure out some of the mistakes as we go along. And so here, what we're seeing is an error validating model. And what it's saying is that it needs to have at least one unique criteria, and that would be typically represented with a primary key field. So, we're gonna look at that in a second. Also, if you're following along and you clone the repository, what I did is I added a schema.solved.prisma in the Prisma folder where you can already see the solution. So if you're interested in already getting the meat of it, feel free to check that out. Otherwise I would recommend actually that you just follow along and see the process of creating that. So, let's look a bit at user, all right? So the first thing we need is an ID field, and that will be if type integer and I'm already getting here, I think it's an Int. And then here we are gonna actually set a default value and we wanna actually auto-increment it. So I'm really relying here on a lot of the auto-completion, which makes life much, much easier. So this is really, I mean, having come from a background working with a lot of SQL, this is a bit of a breath of fresh air, I would say. Okay, so we've defined the ID field and then we have the email. The email is of type string. And what you see here is we have the option of adding a question mark. A question Mark would actually make this field optional, but we want the email to be required. This will also help us later to do all sorts of relations. And we'll get to that. And with this, I'm just gonna comment this because all of these red lines are giving me a bit of, making me a little bit anxious. Okay, so what do we have? Okay. And I just used the format command, which aligns it nicely. And then here we wanna add a unique constraint. And so we have the ability here to define all sorts of different field attributes. And in this case we want a unique constraint and, wooh, it's already happy. I think I also forgot to. So the reason that it's squiggly before even defining the email field is because we need to define one unique field, which would either be a unique field or an ID field. And since the ID is the ID, we need to add this ID attribute. And now we add the unique constraint. Actually the unique constraint will also add an index, which is really useful. I mean because a lot of the lookups that we'll end up doing the CRUD part of the application and just in case you're not familiar with the term CRUD, CRUD means create, read, update, and delete, which are the sort of the four typical operations that you do with a database. So when doing CRUD operations, when doing lookups, being able to look up an E via search, the user table by email will give us the result pretty fast. Otherwise it has to do a full scan. And so with an index, the database is able to pull that record very quickly. Okay, and now we have the first name, which is also String, last name String, to surname part of, sort of gotten adopted to last name. And then we have a social field. The social field will be a free form JSON field. And the reason that I'm using JSON for this is so that I, I can easily, I don't have to adhere to a schema. So, by using the JSON data type, it's very flexible. So I mean, if there's a new social network that pops up, we can add fields as we like without having to do a database migration. Typically when we work with relational databases and we wanna make, add new fields to tables and so one, we have to run a database migrations and essentially that's what Prisma Migrate is for, it solves this migration problem. And I should point out that while Prisma Client is already in general availability and is production-ready, Prisma Migrate is still in experimental state. It works and we're gonna be using it. But there's a lot of work being done right now by the engineering team at Prisma to improve that. So, we have this social JSON field and I'm not gonna even define any of the relations yet. I'm just going to define the fields for each one. And then we'll slowly go through the relation fields. And so for a course, we have an ID. In fact, I can just copy this line here and we have an ID. It's also automatically incremented. And it's worth pointing out here that MYSQL, for example, in comparison with Postgres has slightly different syntax for defining this auto-increment behavior. One of the nice things of using something like Prisma is that it abstracts a lot of the database specific details. And so this would work with both MYSQL and Postgres and it would implement it with the database-specific details. Another thing I should probably mention is this data source block. This essentially defines the kind of database that you can use. And as you saw, I used control space here in VS Code to get the order completion. So we're using Postgres and then this env thing here that will essentially, so you can pass a string here and then use Postgres and pass the whole database URL. But typically because the Prisma schema is committed into a repository, we avoid committing secrets into a repository. And so that's why we use this env incremental loaded dynamically. And so what we need to make sure and we'll get to this a bit later is that we have this database URL environment variable. There's two ways to do that. You can define it just as a plain old environment variable, I'm using Fish show here. But if I were to just open up Zen show, which has a more standard syntax for them, that would look something like, database export, database URL and so on and so on and so forth. Yeah, alternatively, so that was the one option and then the second option is to have an env file inside the Prisma folder where you define the database URL. Let's see, I'm gonna pause, go back to the schema. And I'm gonna pause here and just look at some of the comments coming in to see if there's any questions. Okay, there's a lot of comments about the resolution. I'm sorry about that. We'll see what we can do, this is from 646. Yeah, I guess the diagram didn't really work out too well, but I've shared the URL. So you can go to that and I'm just gonna close this now. I'll just move this to the other screen that I'm using here. And then let's see. Okay, someone's asking about Docker Compose. This is beyond the scope of, I mean, using Docker Compose for Prisma Migrate. Maybe we'll get to that, thank you for that request. I forgot the idea, oh yeah, thanks Gregor, yeah. Luckily we had LinkedIn gear to solve that. And then we have another question. What would the created_at and updated_at fields look like for automatic updates? So I'll just show this and then I'll show you how that can be done. So what we can do here is we can do updatedAt and do date time and then do default. And then we have this updatedAt. But I believe that I didn't do that correctly. I think it's something that. 90% Sure that's the correct way to do it, nope. UpdatedAt is different so correction, that is the correct way to do it updatedAt. But createdAt, there's also a date time, but what it will use is it will use a default and then here we can use now. And so that's to answer your question, I'm gonna remove the banner now. Is it possible to have a JSON array field? I'm not 100% sure about this, but I believe that it's possible looking into this. Also, I'm gonna just open up the Prisma docs just so you have this, and Prisma docs are really, they're getting a lot of love recently and so I highly recommend that you check it out. And let's see. So by the way, JSON is a field, is a data type that we recently added and we have a lot of different documentations or let's see if, there's, okay so here there's an optional JSON field. But, based on parsing the linting, I would say that it is possible. And perhaps at some point we'll get to that and test it out. So thank you for that question. And so let's continue. I'll even leave the createdAt and updatedAt for the fun of it. I think we also have it, createdAt, yeah, I think we have it on other models that which we'll get to. So I'm just gonna remove it from here for now. And then back to the course. We had the ID for a course and we had a name and that's just really a required, string field, plain text field. We also have course details, which might be, we have the course name then course details might be a bit longer. And so we'll have a string for that and I'm using the shortcut. So what you saw here was the shortcut for formatting the schema as you go along. All right, so we have the course details and that's it basically for course, I mean, besides the relations which we'll get to. And now we get to test. And so, a test will have an idea as we had before. Always a bit fun with a bit of good old copy pasta. And then a test also has an updatedAt. This might be relevant in the context of say, a test changing its date. And so it's really interesting to see when it was updated and by using the updatedAt. Whoops, sorry, that was a date time. And there we go. And we also have a name for the test, which is String and a date, obviously, which is a DateTime, but doesn't have any default value. That's something that's will be user entry or entered via programmatically as we sort of get into the business logic aspect of this application. And then we have the test result. Test result is quite interesting because it has three relations if you remember from the schema that we, the data model that we created, where do we have that? Yeah, so we had the three relations. So, when we get to the relations, it'll get quite interesting. So for a test result, we first have an ID And we have createdAt which is DateTime and default now, was it now? Result. We have different data types and we can represent that as a float, but to avoid a lot of the floating point arithmetic, I decided to just use an Int and then represent that essentially as a percentage precise to one decimal point. So it will be represented as, so make a comment here and precise to one decimal point represented as result times 10 to the power of minus one. And that just is a fancy way of representing. So if we have, well, that's not a good example, but if we have 956, that's actually 95.6. Okay, we had the result and that's pretty much it for the scalar fields. So all of these fields, we consider them scalar fields. I'll remove the JSON array type from the social. Yeah, and so all of these fields are what we call scalar types and they're just basically implemented as native data types in the database. And, I'm gonna pause here and look at some of the comments to see if there's any questions. Okay, Prisma docs are awesome. Thank you, Bram, that's really nice to hear. Are you going to be covering authentication? Yes, we will be covering, however, not today. That's beyond the scope of today's livestream. Okay, Prisma Migrate doesn't seem to work with JSON array. So thank you for pointing that out. Actually, I will probably relay this to the engineering team to get maybe their feedback on this. Perhaps someone is also live and can chime in. And Wesley asked a question. In relation to fields of type ByteA, any predictions? Not sure that I understood the question, perhaps you can share a bit more about your use case and then we can answer that. Also remember that. So we have this chat for this livestream, which is a good way to interact sort of on a more immediate basis, but we also have a very active Slack. So if you wanna join us on Slack, you can go to where you can sort of get an invite. And you can also check out. And there's a lot of interesting discussions happening there, and a lot of really interesting use cases that people are using Prisma for. So we now have 42 viewers live. It's really nice to have you all here and now let's move on and define some relations. Are you ready for some relations? All right, cool so, let's do the difficult part first, which is the many-to-many relation. In general, there's two types of many-to-many relations that you can define with Prisma. We have what we call a implicit, an implicit many-to-many relation, which would look something like this. So we had the, all right, we have the many-to-many between users and courses as we said before. And so, what we could do here is we could give it a courses field. And then when defining a relation, typically you point the sort of the data type for the field is just another model. And so in this case, it will be course but I remember that you can have multiple courses, it's a many-to-many. And that's not all of it because we also have to define the relation on the other model. And so here we will have members. And the reason that I'm using members rather than students or teachers is because we will get to that in a second, but because we can have essentially both students and teachers, both of which are users, which will be related. So we have members here and then the type would be user, an array of user, which is essentially the way this is how you define an implicit many-to-many relation. However, there's a problem with that. And that is exactly what I said about this user types that we wanna have. And this isn't, so in an implicit relation, what Prisma will do is it'll actually create this relation table that we saw in the diagram, this course enrollment table. That will be automatically created by Prisma. But since it's implicit we don't have a way to customize it. And so we have to use an explicit many-to-many relation. And so for that, what we're gonna do is we're gonna define a new model that's called the course enrollment model. And in this course enrollment, first of all, I wanna have a creativeAt, why? Because it's always nice to know when things were created. Essentially we can think of the createdAt as the time when either a student or a teacher joined the course, which could happen at any point, perhaps throughout the life cycle or throughout the duration of the course. And so the createdAt is DateTime, and it has a default of now, oops, okay, we're getting the squiggly lines because we still don't have a unique field. And we will get to that in a second because I don't wanna define another identifier. There's another really cool way to do that. A cool sort of SQL way to do that. And that is a multi=field primary key, which we'll see in a second. And so we have this, and then the main thing that we care about is this role because through the relation between a user and the course, we want to denote which role does this user have through his association with the course, either a student or a teacher. I hope I'm not repeating that too much, but I'm gonna define this user role. And obviously it's telling me, hey, user role doesn't exist, but I have this nice quick fix feature here which allows me to create a new enon. And so, du du. Like magic here, I can define the different types of users. And so we have student and we have teacher. Okay, but we still haven't defined the, a unique ID field. And now comes the part of the relations. So, let's bring this up closer to the user in courses. Exactly, I'm gonna make this a bit smaller, I hope it's not too small. I'm also pausing to go back to the questions, any possibility of 10 ADP streams? As far as I know, it's not possible with the technology that we're using. But if you've joined just recently, I should point out that we're gonna have also an associated article, which covers everything that we do here in detail which you can also follow at your own pace. So I'm sorry about the 10 ADP, but we'll try to figure out something for the future. And JSON arrays also work. Well, we'll see about that but, and then is geolocation data type supported? No, so currently it's not supported, but this is an often requested feature and there's a lot of work being done on getting more native data types supported. So this is one of the initiatives that's going on right now. If you have these type of questions and you wanna maybe submit a feature, then you can always go into the Prisma GitHub and you can search here. Oh, there we go. So we already have supported. So generally here, if you have a feature that you care about and you wanna get it sort of pushed, it's always good to go here and give it a thumbs up. And also perhaps detail your use case, like working with geo types and spatial data types, there's a lot of like very specific details that need to be covered. So it's always good to have as much context as possible when you do this. So thank you for that question, Vamshi. And now we're back here so okay. Now let's define the relation scalar fields. And so, we care about the user ID, which is just an Int. And we care about the course ID, which is just an Int. And, we also need to define the actual relation field. So these are the relations scalars, and then we have the relation fields and the relation fields are relevant in terms of the way that you interact with Prisma Client, as we'll see. And so here, I wanna define user and it's a type of, type user. And then I'll use this relation attribute here to denote that this is associated with a field. So fields, and then here we wanna have user ID and then references. And in the references part, what we do is we actually tell it which field is it referencing in this user model? And so, because we're using the user ID in this case, we'll look, which one do we want? The ID field. And worry not about the squiggly lines. We'll solve that in a second. And then we also have the course and the courses of type. Course and then it's also relation fields and the fields are course ID. There's different styles about how to order these. I generally like to have like the relation scalar in this case, user ID, right next to the relation field, some prefer to have the relation scalars at the top. I don't think it really matters. It's just one of those tastes questions. Okay, so we had the fields course ID and then we have references. And then in this case, it also references the ID. And so we're still getting, because we don't have any primary key on this, any primary key or any sort of unique constraint. And, now I will define using At At ID. At At ID allows me to define a multi-field primary key on a table. And then in this case, so a primary key has to be unique. And then when we're doing multiple field, like a compound primary key, the combination has to be unique. And so, in this case, what we want to do is we want to do that on the user ID, course ID and the role and just formatted it and the squiggly lines are gone. So we're in good shape. This constraint, essentially what it does is it limits the ability to associate users to courses with a single role. So for example, if you have Alice with user ID one, she can only be associated to a course with a single role. What it also means is it means that theoretically in this data model, what it does support is it supports having users being members of the same course with different roles. So that means Alice could be associated with the same course once as a student and once as a teacher. And this is actually not a bad thing. Otherwise, if we had removed the role from this compound primary key, we would only be able to have the user associated once with a course. So we wouldn't have this ability to do association by multiple roles. Okay, I think we're pretty much done here. I'll remove these to do's. If you're wondering where the highlighting for the to do's comes, I'm using an extension for that and I'll pose now and look at some of the questions that may have come. Okay, Adam Tuck, MN relation, so many-to-many relations confuse you. Yeah, I feel you. They are a bit confusing. I highly recommend checking out some of the docs that we have and hopefully also this series, as we'll get into some of the details and the CRUD implementation we'll be able to look at that. And then thank you for the compliment. Prisma is awesome from Dooboolab. Okay, let's continue now. So we've defined the Prisma schema and all of the models. And we talked a bit about the relations, implicit and explicit relations. I think actually I haven't covered all of the relations. It was just for the course and the course enrollment. Oh yeah, right, yeah. We still need to define the rest of the relations. I'm sorry about that. We can leave those comments in. Okay, so what actually happened was this was automatically added by the format. And the reason is these relation fields are required on both sides. And so, a little bit confused myself for a moment, let's see. Oh right, that's because we still had the implicit relation. So we're gonna remove the implicit relation. And so, actually what we see now is really the difference between this implicit and explicit relation. In an implicit, the models just point to each other in this case user to course, but we decided to go for an explicit relation. And so in explicit many-to-many, the relation fields on the two models will always point to the relation table in this case course enrollment. And the name can be always renamed to fit your use case. In this case, I think a good name for the association on the relation field or for user would be courses. And then for a course, we can just call it members as we did before. Let me just format ahead of that. Okay, so we have this explicit made-to-many relation. And now let's look at a bit at the relations of tests and test results. So first of all, a course can have multiple tests. And so, a course can have many tests. And then in this case, the many side will have a relation scalar field pointing to the one relation that it's referring to. So in this case course ID is just an Int. And, right, and then we have the normal relation field, which is a course field. And we need the relation attribute here and then fields. And then we're referring to the ID. Whoops, there's no need for the quotes. And references, ah whoops, sorry. Fields is the course ID. Right, so references. And then here we reference the ID field. And what you will see is that the course should automatically get this tests field. So in this case, we'll have here, a course will have test and then, and then it can have many of them. So, pausing there for a moment, we had this test field essentially that says, a course can have many tests and then a test is associated with a course. And then we do that here with the relation scalar. And this relation field also will allow us as we'll see in the CRUD side with Prisma Client to get from a test to its associated course. And so this field is mostly useful for the interaction with relations in the Prisma Client. Okay, now we have test result, that's also an interesting one. And a test result, we can have many of it associated with the test. And so we'll have here a test ID and that will be a type Int, and then we'll have a test relation field pointing to test and relation, and as we did before, fields, test ID and then references ID, and then this ID is actually pointing to the ID field of a test. And, tada, I don't know if you saw that, but the moment that I formatted it, the format automatically added the other relation field on the test and then we can rename that to TestResult. It's like generally the name used here is the, also the name that will be used in the Prisma Client. And so it's very useful to follow camel casing, I believe it is. I mean, starting with a lowercase, just to stick to the, some of the JavaScript and TypeScript conventions. Okay, cool, so we define one relation of a test result, but we also care about the student that test result is associated with in addition to the teacher who graded it. So, let's add a student ID here, type Int and a, let's do it one by one. So student ID and then we have the student. So remember, one-to-many, many-to-many, you always have, well, let's talk about one-to-many. So we have this one-to-many relation between test result and a user. And so, typically there will be three fields. Two relation fields on the two models and one relation scalar in this case on the many side. So the test does many test results per user. And so the relation scalar goes here and then we have the student and the student is a type user and it's a relation field. Fields are student ID references ID. And then this is the ID field on the user model, which is defined here. Let's see, and then we wanna do the same thing essentially for teachers. So I'll just copy that and then grader ID and then graded by will be the name that I'll call it. And then that actually is using the grader ID relation scalar field. And it references also ID on the user model. And here what we're getting is an error, ambiguous relation detected. Okay. So in such cases where we have two relation fields that are pointing to the same model, we have to diambiguate that. And so we do that just with a simple name field. So, I'll just name this results. And then here I'll name that graded and error validating model, automatic related field generation would cause a naming conflict, okay. I think this might be related to the, let's see. So we have test ID. Right, yes, that's because we need to also have the relation field on the other side. And so let's do that. So test results of typed test result, we can have many of them, and this is a relation and we actually need to tell it here the name. So in this case, it's the test results which are associated with the student. And so we'll use the same name that we used here. And we'll also have another field, another relation field called test graded, test results, and then it's tests graded. And let's see if auto-completion picks that up. No it doesn't, but we can just put the name here. And let's see what we're getting here is the line is not valid, oh yeah. I think I may have added double brackets here so that's why we're getting that. Okay, there we go. Nice, no more red squiggly lines. All right, so we've defined all of these relations. Let's see if we've missed anything. Also I'm gonna put this in on down at the bottom just for. And I usually like to add a comment before, and we're already at 7:24 Berlin time. Let's see how far we get with this. Yes, this is all Prisma too. Nicholas, why is course enrollment MN but test result isn't? I believe that that's because so course enrollment is this explicit relation table. Whereas test result, if we go back to the diagram, it's the many side of a one-to-many relation. And it's a one-to-many because a, well, it depends on the context of which relation we're speaking of, but in the context of all of them, it is a one-to-many. So one test can have multiple test results, but one test result cannot be associated with multiple tests. That is the reason to answer your question, Adam. Okay, so we've defined this nice data model and we have this full schema here. Let's see. And I'll just clean it up a bit here. And, wonderful. So we have courses, test results and test graded, great. Okay, I think at this point, we're ready to carry out the migration. So I'll open up the terminal. I've got the terminal, hopefully big enough here. We'll zoom in even a little bit more. And I'm gonna run my Docker Compose up which will get the database running. So I have this database. If you're interested in how that's configured, I'll just quickly show. So, here I'm just defining a, I'm using the Postgres Docker image and I have these two environment variables so that it's easy to connect. And these also correlate with what we have in the env. So, and then you hear him pointing to this grating app database which I haven't even created. And, yes so we have the database running. And so let's talk a bit about how Prisma Migrate works. Generally when working with Prisma Migrate, there's two steps that are required to run a migration. The first step is saving the migration and in this step, Prisma Migrate takes a snapshot of your schema as it is at the moment that you use the Prisma Migrate save command, and it figures out what steps need to be carried out in terms of altering the database, really the SQL commands. And when you run this, an artifact is created in the Prisma migrations folder. So, here we have this Prisma folder it's currently empty and so let's run it. So npx prisma migrate save, and we need to give it the experimental, as I pointed out early at Prisma Migrate is still experimental, but it's also really nice. And in the near future, there's a lot of work being done on it. I'm just gonna get some water. Okay. So we had this experimental flag and that's pretty much it. And it's detecting that this database doesn't exist. So I'll just let it create it. You don't even need to define the name of the migration. You ahould just go and it'll automatically give it a name. And as you'll see now in the Prisma folder, we have this migration. And so I can even run a tree on Prisma just so you can see all of the files. So it has the steps and then a snapshot of the schema and also a file. Okay, now let's run the migration. We can do that with npx. In case you're not so familiar with npx, npx is just a way to call some of the local dependencies that you might have as like a CLI tool. And so in the package JSON, we have the Prisma CLI here. And so that has this Prisma command in it. So, Prisa Migrate up experimental and woo. Okay, so it created the database and I'll give you, if you really wanna make sure that it's done, there's a couple of ways that we could look at whether the database has been created. I am a fan of pgcli which is a fancier version of the Prisma CLI sorry of the Postgres CLI. And what did we call the table again? Let's see in the env file, it was grading app. And so here I can list the tables and there we go. We have all of these different tables. We can actually look at one of them. it needs the quotes. And so we have this course details. Let's look at the user table. And what we can see here is these are all the relations that we defined. Let's look at course enrollment, and here we can see, I don't know if you remember, but we had in our schema a multi-field primary key. And so that's what we see here in this line. So great. We have our database created and now we're about an hour in, I think I will continue so that we can look a bit at the Prisma Client aspect. And what we'll do is our goal is to create a seed script that will fill the database with data. And so I've already created a skeleton here. And the important thing to point out is that, first of all, this is a TypeScript file, but as you'll see, you won't have to define a lot of types because Prisma Client is actually a, it's generated in TypeScript and so you have all of the types here which can be inferred by the TypeScript compiler. So we have this Prisma Client, and then we're doing two operations with it. Here we're just instantiating it and Prisma will be the main object that we use in order to interact with the database, oops, copied the wrong thing. And then lastly, we call this disconnect function. This is necessary. If you don't have this and you run this script, it'll actually keep, the event loop won't be empty. And then the script won't exit until you sort of give it a Control + C, I mean an interrupt. And so it's generally good practice to disconnect at the end of a one-shot-type script. Otherwise there's obviously when deploying this to the Cloud, whether that's a serverless platform, there's other details that come in, but let's focus right now in this seed script. So, let's try to fill the database with some data. And before we do that, let's talk about Prisma Client. So we looked at Prisma schema that was this declarative definition of our data model, and we looked at Prisma Migrate which uses that in order to create the database. And now we'll look at Prisma Client. Prisma Client is the type-safe, TypeScript auto-generated database client that is essentially tailored to your data. And, we need an additional block here so that we can generate this client. So this is what it looks like. Generally, it would just look like this. And the idea with this generator is that you can generate clients for multiple languages. Right now, we only have the TypeScript, which is essentially production-ready, but we're also working on a go client. And so here you would be able to define that. And then we also recently introduced this preview features, which allows you to enable some of the, it's sort of a feature flag for some of the functionality that we wanna deliver but we're waiting for feedback. So initially we put it in this preview features, we hide it behind this feature flag. And if you look also, if you're interested in following some of the new features coming in, you can also see that in the Prisma releases. So. We have these preview features. And so, we recently introduced distinct and we recently introduced middlewares and there's a lot of interesting things happening there. But what we're gonna look at specifically is the, let's see, where can I get all of the releases? We're gonna be using the aggregate. That should be our goal. So we have this aggregations API, which essentially allows you to do things like group buy and mean, max, average and sum and so on. And so this would be relevant in the context of our real world grading app, because we might wanna get, say, the average test result for a student who may have been part of a course and sat three exams or three tests. And we might wanna see the minimum, the maximum or we might wanna see even the average for a specific test across multiple students. So we'll be able to do all of that. And, what was it? The aggregate API that was the name of this preview feature. Okay so with this generator, now we can go in and generate this Client. And the command for that is simply, oh, oh yeah, npx prisma generate. And there we go, so that's been generated. And now we can go into our seed script and let's start by creating a user so. All functions. So most of these operations are asynchronous in the sense that you have to communicate with the database. And so that's what we use the, that's why we defined here an a sync function and let's create a user. So, let us create a user and maybe that user will serve as the teacher of the first course that we introduce. So Prisma, and then here, what we have is we have, so all of this is typed and the code has been generated based on our schema. And so this is really nice because it just gives you quick access. You don't need to do copying and pasting and you really get everything, it's time typed so you get a lot of annotations and help as you go along. So here we wanna create a user. And so we have multiple functions here, but we just wanna create one. And then we get a lot of information. I'm gonna pause here and see if there's any question so far, because I've been running through a lot of details. Okay. Let's see. We have 24, so we lost some listeners, that's okay. Thank you for everyone who is still with us. Basically an explicit many-to-many relation is defined by drawing to one-to-many relations via link table. I'm not sure if I understood that, but I get the feeling that it is correct. Okay, let's go back to our user creation. And so here, every time I do Control + Space, I really just get all of the auto-completion. And so we care here about the data. And so let's start with an email. Let's see. Let's say our teacher is Grace and she's using, a com, first name. And let's add some social info. Here we can have anything so, maybe she's using Facebook. And maybe Twitter. And so we have all of that and that's about it. Now let's run this, I'll open up my terminal. Can I make this big? And I'm using TS Node here to run this. Okay, well, how do we know that went well? Well, there's a couple of ways, we could open up the Postgres CLI and I can just connect to the grading app and then select cue from user. Let's see, oh yeah. There we go so here we have this record that we created. Alternatively, if you're not so crazy about using the CLI, there's a couple of options, we could just log this. And. And then what I expect if we run this again, is that we will get a unique constraint and let's test that to make sure that it's all correct. And indeed, we do get an error here. Unique constraint failed on the field. So because this is a seed script, this is probably not something you should do in production, but because we're gonna be running it a couple of times, I'm just gonna do a delete, delete many. And so if you delete many without parsing it any options, it won't actually allow you because it expects arguments, but you can just sort of bypass that by parsing an empty object, again, not be done in production and then let's run it to this again, and then let's see what it logs out. And so we get here this idea, this is the idea of the newly created user. Wonderful, so we created a user. Now let's get to some juicy stuff. Let's get to creating a course. And what we're gonna do is we're gonna also create a course with nested relations. So in a single command, what we're gonna try to do is we're gonna try to create a course and three tests that are associated with that course. And one of the nice things with Prisma when you do these nested rights, first of all, it's all encapsulated within a single transaction, which is a really good way to guarantee that either everything gets written or nothing gets written. So let's take a look at that so, let's create. Course is equal to await, prisma.course.create. And then we have this data object here. And so here we can already start seeing some of the fields that we can add. And then let's start with a name, CRUD with Prisma in the real world. And now we have this tests field. Is that correct? Let's see, see if the auto-completion helps me here. Oh yeah, I think I need to add a comma first. Yeah, there we go. And so tests, parse it in an object and then we have two options here we have create or connect. Connect allows us to connect an already existing course, sorry, test to this course that we're creating, but in our case, we wanna create. So we'll create, and then here we can already parse all of the fields for the tests. I'll just open up here, the schema, just so you can really see that. So a test has these two required fields for that aren't automatically created. And so this really maps. So date and I'm just gonna use these date functions to, these date functions that I have up here in order to create a bunch of timestamps. And so I've already prepared that. So here we have just three timestamps. In fact, I think we should be able to see the type, yeah. So it's able to infer the type. And so we wanna create, but actually we don't wanna just create one. Let's create multiple, so you can just parse an array here. And I think this should, there we go. So if I remove that, I can parse here an array and then, array of objects, and then each one of these objects needs to have the date. So let's do one week from now and we'll give it another field of the name, which will be the first test of this course. And then we'll create another one two weeks from now and second test. And then here we'll create the last one which will be the final exam which will be in four weeks from now. You better start preparing. All right, final exam. And so, this syntax is, the Prisma API allows us to work very easily with these relations. Now, let's log what comes out of this. Cool, so course, and I'm gonna run it. Oh, okay, unable to compile, it's missing, oh, that's one of the beautiful things about Prisma is the type safety. So we're catching a lot of these bugs before they get to, the question I'm curious about is why didn't we get this in VS Code? Let's see. I'm just gonna simplify the screens here. And so. Course details, start a required field, let's see. Oh yeah, so we made that a required field. Okay, let's see if we restart TS server if we get that as an error, oh yeah, there we go. We're already seeing the error. So sometimes when you're generating code VS Code is not so quick to pick up on it. And then in such cases, you can use this restart TS server command and then usually we'll get these areas so there we go. We're missing these course details and what we just saw now is a genuine develop a mistake that gets caught by type safety. So I'm happy about that. Wait, oh yeah, that was on the course. So course details, a soft introduction to CRUD with Prisma. Need another comma here and then let's try to run that now. And, okay. So we got back this course that we created, but we didn't get any of the related tests. That's a bit annoying. Well, what can we do about that? Well, the good news is that we have this include syntax and include allows us to select additional fields. So by default, what we'll get is, I'm just gonna open up the schema again, is we'll get all of these relations scalar, sorry, these scalar fields. And in this case, these are the three that we saw in the terminal, ID name and course details. In order to fetch relations. And this is actually a good thing because you have full control over what you're fetching. And some of you may know, fetching relations can be expensive. So what we can do here is we can do include and then we parse it an object and we have the options of which related fields we wanna fetch. And so we're gonna fetch the tests too. And if I run this again, it will probably fell true to the unique constraint. Probably not because the name or the course details aren't, don't have a unique, nevertheless, just to reduce any possible confusion, let us delete first the tests and course. And so now when we run it, we should get back as we saw before, the ID name and course details, but also an array here of all of the associated courses. And as we see, we have this updatedAt and this date. And then if we see, we have really a week from now is the fourth for August, the 11th of August is two weeks from now. And then we have four weeks from now, which is the 25th of August. So with that in mind, I'm gonna pause here and look at some of the comments and I wanna wrap up in about 13 minutes. So let's see how far we can get if there are till any questions. Okay. Nope, there's no questions, all right. So let's go back and I'm going to. Now, I'm going to look at how we can associate also a teacher with this course in this single query that we have here. So, remember if we look here at the course, a course can have many tests and it can also have many members. And the members, as you may remember, is a many-to-many, an explicit many-to-many. So this might look a little bit confusing, but bear with me and I hope that you will get this. So okay, so we define the test field, and now we wanna associate the user that we created, Grace. I will actually name this Grace. So I wanna relate Grace to this course as a teacher. And so if I just use the auto-complete, I have the option of defining members, but members is a course enrollment field. And so, here I have the opportunity to create a role in this field. So this is what it will look like. I wanna create. And then here, I wanna create a role. Teacher, user, but user in this case is a user field. So I have to do another relation and I wanna connect in this case because the user already exists and that is Grace. And so, here I have the option of. Is that correct? I think it is yes, email. And then I can take Grace dot, see, everything is fully typed. In fact, this will actually also be types of, remember when we added the tests to this query, course will actually have that as a type. And you can see that here below that it's a, I believe it's a union here of course. And another type which includes test. Okay, so now we created a row in this course enrollment field through the members and then connected that to the user Grace. Okay, let's give that to go. But in addition to including test in our results set, I also wanna get the members. And so here I'll parse true. And let's see, is there is a way to. So members you might remember is pointing. Yep, so we have the members, it's a course enrollment, and then what we're gonna actually get in the result set is just the scalar field. So we're gonna get the role, the createdAt and these user ID and course ID. In order for us to get the actual user, we need to do something like include and then user true and let's make this look a bit nicer. Okay. Now, I'm gonna run this again. And are we logging this after? Let's do that, log that course. And there we go so we have this as an object so we can't see, but we can see that the user ID is six. I have an idea. Why don't we look at another nice feature that Prisma has? So Prisma has got another feature called Studio. And in case you aren't familiar with Studio or you haven't heard of it, Prisma Studio is a visual editor for your database. So, it's also an experimental feature that we have, but let's check it out, see what it can do for us. So, okay, it opened up my browser and I'll move this to this screen. And what we have here is, let's open this up. So we have here all of the different models. So we have a single user Grace, and we can look at her related courses. And, oh, we see here that she's a teacher of which course is that? Let's just look at that. And that is, so if we're looking now at the course model, we see the members and the tests. If I double click that, then I can see, actually, I think there's a tree view which is a bit nicer to traverse relation. So let's start from a user and go to tree view. So we have this user and then this user is associated with courses through the course enrollment. And she's a teacher. CRUD with Prisma in the real world. And let's look at the tests of this. So really with this, you can fully traverse the whole, essentially it's a graph that is formed for all of the different relations. Okay so that was all nicely created. I'm gonna close this. And now I wanna, let's see if we can, in the seven minutes remaining to this stream, let's see if we can quickly cover some of the aggregation features. So, let's see. In order to aggregate, what we have to do is we have to create two students. So I've already prepared two pre-canned queries to create users and there seems to be something wrong with them. Social is missing. I thought social was optional, but I guess I forgot to. So, let's just add social. And, we've got David here. David and Shakuntala, and, all right so we created these two users and oh, right. And we are associating them as students with this course that we created the course we have that here. In fact, we could do these through nested, right? But I think that would just make the first query too complicated. So I'm just gonna keep it in this one. And so we created these two users and now I wanna add some test results for these. And so, adding a test result for one of these would look something along the lines of, let's see. Equals, so prisma.testresult.create and start an object and then we have data. And then here we start actually parsing in all of the, all of the different fields. So we have the graded by, and then what we're gonna do here is we're gonna connect that to Grace. So, we're gonna take and then we're gonna do student. We wanna connect that with, and if I hover over this, I can see also the different fields that are available. That's because it's old type safe, oh missing a comma here. And so we have the student, the grade by, and then of course it needs to be associated with a test. And then, so we wanna connect that with the, some of the tests that we created here. And because we included the tests in the results set of this query, I can actually just use reference this course. So connect and then ID and then let's do course.tests. And then because tests is an array, I'm just gonna do the first one and then using the nice optional operator just to prevent potential errors. I don't believe, actually, I can't remember the name of this operator in TypeScript, but what it does is it prevents exceptions. And what are we missing here? Of course we're missing the result, probably the most important part, so result. And then, let's say it was 950. Okay, so now we have that. And we have three minutes so I'm gonna sort of race through this. And what I'm gonna do is I'm going to create just an array here with results for. And, I am going to just add a loop to loop over there. So that counter equals zero, and then we'll do a for of and then test of course.tests. Remember dot test was an array. And we get that here. So, I'm gonna counter plus plus, and what we'll do is we'll move this here and we're gonna pre-fill, we're gonna see the database with a result for each one of the tests. And then here when I'm connecting the test, instead of pointing it to that one, I'm just gonna do and the result should be test results and counter. Okay I think that should work. If it should, why don't we already go ahead and generate the aggregate query? Let's see. So, after we finished that constant results equals await prisma.testresult.aggregate, and then here we parse it an object and then we tell it where. And we wanna do this specifically for the student, right? So, let's look again at the Prisma schema to see which field we should use test result. Right, so we have test ID and student ID. So we wanna do that for Shakuntala. So here we'll use ID. Sorry, student ID is a ID. Can use, for this where field, can use any unique field. Yep. So, for Shakuntala, we're going to get the aggregate. And what we wanna get here is the average that will be the average for all the tests that she has written. Result, true. Quick reminder again that you can find all of this in the documentation and then we wanna get maybe the max and the min, and maybe even the count just to, and let's log the results and we'll wrap up with that. And now I'm gonna run the seed script. And, all right yeah. I think it's the order of how I deleted them. And, that should help. Oh result, no. Okay, we can't leave it like this. And so, let's see. Let's open up quickly the CLI to see here or let's use the Prisma Studio to see what some of the data that we have in the database. So we have the three students, let's look at this as a tree. And so we did this for Devi, courses, oh, test results, okay. So it looks like something went wrong with the test results, perhaps we associated them, nope. All right. So, oh, I see what we did here. We forgot to put an await and that's why it wasn't written. Let's see if this works. Oh, and there we go. Okay so we got an average of eight 81.6, a max of 95 and a minimum of 700, which seems to be right. Just to be sure, 800 plus 950 plus 700. Let's divide that by three and yeah, indeed. We were, we got the right answer. So, that was it for today's livestream. We went a bit over time, but I hope that you found this useful. Sorry about all the screens here. I'm just gonna remove the screen. So thank you all for joining us. We're gonna continue with this series with a lot more streams to come. I hope you enjoyed this. If you have any feedback, feel free to reach out via Slack, Twitter or using the chat and see you at the next one.