- 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, (indistinct) 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 slack.prisma.io 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 (indistinct) 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 (indistinct) 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 (indistinct) 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 (indistinct) 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, (indistinct) 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. (indistinct) 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 grace.email 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. (indistinct) 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 test.id 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 (indistinct) 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.
