Database Workflows & API Development with Prisma

Welcome to the database Workflows and api Development with prisma Workshop. I'm really excited to give this workshop today. Actually, these kinds of workshops are pretty much the favorite part of my job as a developer advocate who works at prisma to work with people and show them kind of their first steps with prisma. And that's exactly what you'll be doing today. So no need for any prior kind of knowledge about SQL or typescript or anything. We'll start from scratch and I'll introduce all the relevant concepts so that you don't need to know anything upfront. One thing that I would ask all of you to do is to take this link that I'm now also dropping in the chat and open it because then you can see also this Notion document that I'm using here right now. And just for logistics, I have a second screen here to my left where I'm monitoring the chat. So forgive me if I'm not always looking directly at you all. I'm just checking what's going on in the chat. And if things don't work or if you have a question, you can always drop that in the chat. Or because as I already mentioned, we do a regular Zoom call here. You can also just unmute yourself and then ask a question. So that's definitely possible here as well. An issue that like Notion doesn't work for encrypting. I actually don't have another source. I didn't have problems so far with Notion. But it's also not too big of a deal if you cannot open it yourself because I will walk everybody through the document. And what I can do is I think I can export it as a PDF. I can do that later when you get to work on your first task. And then I can send you over the PDF document. So we should be able to resolve that. All right. So welcome again, database Workflows and api Development with prisma. So what you're going to learn today is basically the most important workflows that you have to be aware of when you want to build an application with prisma. And I'll explain in a little bit actually what prisma actually is and what it does. But first, I want to give you a rough overview of what we're covering in this workshop. So at first, we start by setting up prisma with a SQLite database. You learn about data modeling with prisma and performing database migrations. Then you learn about prisma Client, a type-safe query builder that can be used to query your database. And you're going to explore various queries. They are from plain crud to relation queries to filters and pagination. And next, you learn how you can use prisma Client to implement the routes of a REST api and then also the resolvers of a graphql api. And you don't have to know what a REST api actually looks like, what a graphql api looks like. I prepared kind of the what a graphql api looks like. I prepared kind of the boilerplate for you, the skeletons of the projects that you're going to be working with. So if you haven't heard of what a graphql resolver is, for example, that's not a problem at all for this workshop. So this is the high level agenda. Actually, I realized that it's all one hour too early. So that should be starting at four and then you add one hour each time. What does a lesson look like? So I prepared these four lessons that map to the different things that we want to do in the workshop. So setting up prisma, data modeling and migrations, exploring prisma Client, REST api and graphql api are the lessons. And each lesson looks as follows, that at first, I'm going to give a walkthrough of the lesson. So for example, for the first one here, I'll click on it. And then I'm going to explain what you are supposed to do in that particular lesson. And I'll even show you, like here with my terminal, with my VS code, a little bit of the tasks that you're going to be working on. And after having shown you maybe three or four tasks per lesson, you'll get the time to work on the tasks of that lesson by yourself. And what I really want to emphasize for you all here to get the most out of this workshop is to not already code along when it's still me doing the walkthrough. So I think it's really best if when I show you what a lesson is going to look like, when I explain the individual tasks, that you just pay attention and that you raise any kind of questions that cross your mind, but really like focus on the things that I do, that I say, and try to understand that. And after that, you'll have plenty of time to actually finish these tasks yourself. So be a little bit patient when I'm explaining each task and each lesson, and then you'll make the most out of the workshop for yourself. My name is Nicholas. I'm the host of this workshop. I really like to educate other developers, and as a developer advocate of prisma, that's basically my main job. And you can follow me on Twitter, on GitHub, you can drop me an email, or you can also find me on the official prisma Slack. One final note before we dive into the topics is that this workshop, this notion document, is completely free for you to use if you want to host this workshop for yourself at some point. So if you feel like what you've learned in this workshop today is really valuable, and you would like to share it maybe with your colleagues or with friends, and maybe some of them are up for spending a few hours with you on this, then you can just reuse all of these materials that I've put together here and use them when you are giving this workshop yourself. All right. I think that's it with the overview for the logistics. All right. Before we start with the actual lessons, I would quickly like to give an overview of what prisma actually is, and the way how I want to do that is actually just by going to the prisma website and give you all a quick walkthrough over our landing page, because I think that's probably most instructive here. If you have any questions along the way, also feel free to unmute yourself and drop in. I want to make this kind of as interactive as possible, and if we want to have a conversation here about databases and object-relational mappers, that's totally fine with me as well. So if anyone here actually has strong opinions about this topic, then feel free to join me here. All right. So prisma is an ORM. ORM is an abbreviation that stands for object-relational mapper, and those of you who have worked with relational databases, you might have already experienced working with an ORM as well. What it basically is an ORM, it is a tool that allows you to work with a relational database from an object-oriented programming language. So the O stands for object, and the R stands for relational, and that's because the tool, the object-relational mapper, is mapping the data structures from the relational database into the object-oriented programming language. And prisma is a new kind of ORM that's really not comparable to any other ORMs from the space. So you might have heard of libraries like SQLize or TypeORM that fall into the previous generation of ORMs where you're defining your tables as model classes, and then you instantiate these model classes to query the database. The way how prisma works is actually quite different, and you'll experience today what these differences are. So first and foremost, I think what really is a strength of prisma is the way how you model data with it, because we have a very intuitive and straightforward data modeling language that lets you describe the schema of your database. And here is an example of what that looks like. It's a lot more readable, I think, than if you were to use typescript classes, for example, with decorators, where you have a lot of noise, and here it's really just the essential information that you need to define your data structures. Once you have defined your data model, you can then go and query your database with prisma Client, a type safe database client. And that's also a bulk of the workshop that we're doing today for you to kind of get familiar with the prisma Client api, learn what kind of queries you can send to the database, and how to use prisma Client in general. And of course, all of these almost all object relational mappers also come with a way to create migrations against your database. And migrations really are important when over the time of your project, the database schema changes, and you then need to kind of track the history of these changes against your database schema. And that's what migrations allow for. And with prisma Migrate, one of the tools that prisma contains, you really have an easy time to track the migrations that are happening against the database. And if we zoom out on a very, very high level to test for kind of everyone to be on the same page, I guess that those of you who rather work on the backend or maybe are full stack developers that already have experience with building kind of your own server, your own server side application, you already have a good understanding that this is where prisma is being used, right? It's being used on the backend to implement the queries against the database. If you're only a front end developer, then it's unlikely that you're going to use prisma directly because it's not really something that you would use inside of your react application. However, with new frameworks such as next.js or remix that typically give you the chance as a front end developer to also run some kind of server side code, be it via static site generation or server side rendering or in next.js with api routes, there are a lot of different kind of mechanisms that now allow front end developers to also use prisma in the comfort of their own framework. But only when these frameworks actually allow in some capacity to actually run server side code as well. That's when you would use prisma also in a front end framework. But the majority of use cases for prisma, I would say, are typically on the backend when building an api server. And I think that's also reflected by the popular technologies that we have listed here, which for the most part are backend technologies. All right. Are there any questions about prisma so far about anything that I've said about anything that's unclear about the workshop or should we dive in and start with the lessons? So let's go ahead and dive into the lessons. So let's start with lesson one and setting up prisma. The goal of this lesson is to set up prisma, getting comfortable with prisma's data modeling and perform your first database migration. There is one question from Amal that I can quickly take before moving on in the lesson. So yes, what's up Amal? Hello. Hey there. Do you hear me well? Yes. Perfect. Okay. So I sort of have a question which is not related to prisma, but related in general with the full stack. Yes. If I may ask. Okay. So perfect. The thing is that I'm an IT student and I have my project this year and I actually like made a training concerning the full stack javascript, like the package. And I haven't touched the code for like six months. And I have to like start right now and do like a B2B schedule meeting platform. And I don't know where to start. Like it's sort of a blur. So I kind of, I need your advice about this. I see. And you said that like you already have a project from six months ago that was using the Merin stack. Actually, like I had a training like six months ago, like, but I didn't touch the code. It's been six months. Like, I see, I see. Yes. Okay. So it seems to me like what you're kind of looking for basically is like sort of like a full stack like solution to build the project that you're tasked to build there. And what I would recommend actually is a tool called Redwood JS, which is a full stack framework that takes care of the front end and the backend for you. So you might want to look into that. I've heard a lot of good things about Redwood. And there are a couple of similar frameworks. If you don't have the need for very complex business logic on the backend, then you could also just go with next.js, which also allows you to run like server-side code and to integrate api routes so that you wouldn't have to build your own express server, for example, because the api routes are already included in next.js. So these are probably the two frameworks that I would recommend to you just off the top of my head. And yeah, if you have more specific questions about how to get started, we can also like follow up about this after the workshop and I'd be happy to share more advice. Yes, sure. Thank you so much. I really appreciate it. Thank you. And I will ask you like after the workshop. Okay, great. Okay. Another question here from Amandib. Would we deploy our app today as well, including the prisma database? So one clarification here, prisma is not a database. It's only a layer that you can put on top of your database. And in terms of deployment, that's not planned in the scope of this workshop today. To me, it's rather important that you learn about the basic workflows with prisma and the ability to query and like migrate your database. But we won't go a lot further in terms of application development, because then prisma is basically only a library in your application and there is nothing too specific, for example, about how you deploy your application then. So you would use a deployment provider as for any other app like Heroku or like Versil, Digital Ocean, like wherever you prefer to deploy your apps these days. And you can do that with prisma as well when you integrate it into your project. That being said, let's get back to the setup. And I have prepared this starter repository here. And I'm just going through the steps that you are doing afterwards as well. So I'm like copying this command to clone the starter project. Then inside of the starter project, we have these instructions we have these instructions as well to then install the node dependencies. And once you are done with that, you can open the project inside of VS Code. Now you don't have to use VS Code for this workshop. prisma also works, of course, or just with any other text editor that you might be using for development. But I would recommend to use VS Code because they have really good typescript support. And we also built a VS Code extension that helps you with like syntax highlighting and linting and quick fixes of prisma schema files that we're going to take a look at in a bit as well. So once you have done this and you have your project ready here and installed the dependencies, you can get started with the first task. And please remember that you will have your own time to work on these tasks. And for now, I think it's just best if you follow what I'm doing here. So I have already mentioned in the initial intro that prisma has a very straightforward way to do data modeling. And that's always done in these schema dot prisma files. So dot prisma is the custom file extension that we use for prisma schemas. And at the moment, we have defined two blocks inside of the schema. One is data source and one is the generator. With the data source, we just configure our connection to the database and we tell prisma that we want to use a SQLite database in this case, and also where to find that database. And here we could also use, like for example, Postgres or MySQL. The mongodb and CockroachDB connectors are currently in preview, but you can already start using them. So you have the flexibility to choose the database that you prefer here with prisma. And then we also have these generator blocks that are actually quite interesting. But for now, I just want to point to the fact that we're telling prisma to generate the prisma client library that will later allow us to send queries to the database from our node.js application. All right. So let's look at the task here. Start by creating the following user model with the following fields and choose the data types that feel the most appropriate to you based on the description below. So we have to create a new model here, and I'm going to show you how that works in just a bit. And we have to add these three fields, ID, name, and email. So I'm going ahead. Oh, actually I might have unfolded all the solutions here, just making sure that they're collapsed. When you start defining a model in prisma, you always start with a model keyword. And it's important to note that each model that you define in the prisma schema will then map to a table inside of your database. So this model user will then map to a table called user inside of the database. Then to define a field on a model, you first add the field name. So in this case, that was predetermined as ID, the type. So if we look at the description here, it's supposed to be an auto incrementing integer and integer types I can declare in the prisma schema using the int keyword. And then we have to tell prisma that this is a primary key and also can use the auto increment attribute or the auto increment function for the default attribute to automatically populate this field whenever we are creating a new user record. Then we are supposed to add a name and an email field. I will make both of them strings. The name is supposed to be optional. And if I have an optional field in the database, I can annotate the field in the prisma schema with a question mark. And I also have the email that's also a string. This one is required in the database. So I'm not putting the question mark. And I'm also adding the unique attribute here. And now notice that I can also format the file with the prisma VS code extension. So for me, I have configured this on the formatting, which for me is option shift F in VS code. And now my fields, the data types, and the attributes are nicely aligned in this columnar layout. So once you're done with this task, you can expand the solution here and see if you're correct. And the next task is to then go ahead and actually create a migration. And the command that you're going to use for this is the prisma migrate command. If I just run the command like this, then you'll see all the different options that you have as subcommands for prisma migrate. Because prisma migrate is a command that then accepts subcommands and options in the CLI. And in this case, I want to use the prisma migrate def command, which is used to create migrations locally in development. When you actually want to apply your local migrations against your production database at some point, that's when you're going to use prisma migrate deploy. But that's also not in the scope of today's workshop. So I can now run the prisma migrate def command and I can provide the name for the migration that's being created as an option here as well. And let's see what actually happened on the file system now, because a few things have happened. We see that there is this new migrations folder right here. I'm increasing the font size just a little bit more. And in this new migrations folder, we also have this new folder that's called this long number, which reflects the current timestamp. And then the name of the migration that I provided in it, because it's my first migration. And we can actually see the SQL code that prisma has executed against the database. So it just ran this SQL code against the database and created the database file right here, where I told it in the prisma schema, where I would want the def DB file to have. So we now have a database and one table in the database already. So the next step is to actually create some data in the database. And in this lesson, we are not yet using prisma client, but instead we're using a tool called prisma studio, which is basically a GUI for your database. And you can use it to put data into the database like this. I'm adding three records here and save the changes. And now I have three records in the database that I just created. There is one question about whether it's possible to roll back migrations. And I'm very happy to share that this is one of the most common questions that we're being asked as prisma. And as of the last release, actually, we introduced a really nice way in prisma Migrate to work with rollbacks. So it is indeed possible. And I will just link you to a blog post here that we launched and explained how the rollback workflows work with prisma Migrate. The short story is that it's basically a better way to do rollbacks and down migrations because with prisma, you don't have to write a down migration before things go wrong. But instead, you can figure out what went wrong in a particular migration and then basically decide if you want to move forward or if you want to move backward and prisma is going to generate the required SQL code for you. So in terms of developer experience and in terms of safety and confidence in your database workflows, I think that this was a great step forward and prisma now supports rollbacks for database migrations. All right. This is already the end of lesson one. One question from Christian was, is there an option to automate the migrate command while editing the prisma file? Do you mean like in a hot loading sense so that basically whenever you save the file that it would automatically execute a migration? That's how I read your question, at least, and it's not currently possible. We actually had a command like this in the past, but we got rid of it because it showed to be very complex to do it right. I think this could be a fun side project for anyone if they're interested in what that looks like. But because you're working with a database and changes against a database schema are somewhat at least delicate, I want to say, to execute. I'm not even sure if it's necessarily desirable to have a tool that automatically picks up everything that you type and immediately executes it against the database. At the moment, it doesn't exist. If you really want to see it, you can always go to our GitHub repository and open a feature request and make a case there so that our product and engineering teams can then evaluate it. Another question, can we close prisma Studio after we're done editing the data? Yes, you totally can, but you can also keep it open and use it later again, but you can also, with the same command, you can always bring it back up, so whatever is convenient for you. If you want to close it, then you can definitely close it in VS Code because it's blocking your terminal, of course. Then you can just open another terminal tab here, and that should also work. That's up to you. Are we covering prisma Seed too? Unfortunately, we're not going to cover the seeding workflow with prisma today, but it's pretty straightforward. I can link a page in the documentation that explains the seeding, and yeah, it's pretty straightforward. If you search in the prisma documentation for seeding, we have a guide about this, and I'm dropping a link to that guide in the chat. Then Eveline is asking if we should update to the latest version from 3.91 to 3.92. It's not really important for this workshop, so if that warning message annoys you, then you're free to update, but it's not necessary to successfully follow this workshop. Then there is Norma Moody that also wants access to the PDF documents, so let me very quickly arrange that. Give me one second, and then we'll continue with the next lesson. Okay, so that email is out as well. There is one last question before we move into the next... Actually, I just realized that I dropped the seeding link in a private conversation here, now everybody should be able to see it. Another question about the DEF and deploy workflows, because we can manage multiple environments simultaneously, and yeah, that's pretty much it. Then there are also some guardrails in the migrate DEF command that will prevent you from doing things that you never want to do. Sorry, in migrate deploy, you have some guardrails that prevent you from doing things that you never want to do against a production database. You can read up on these commands also in the prisma CLI reference here under the prisma Migrate section, so we have a lot of documentation about all of these commands. All right, so I think pretty much everyone probably is done by now, and if not, then you can also just continue after I did the next host walkthrough. Now I want to show you how to get started with prisma Client. Looking at the next lesson, exploring prisma Client, the goal of this lesson is to get comfortable with your prisma Client api and explore some of the available database queries that you can send with it. You'll learn about CRUD, relation queries, filtering, and pagination, and along the way, you will run another migration to introduce a second model with a relation to the user model that you created before. One thing to note is that we'll only work in this VS Code project that you cloned from my GitHub, and it currently already contains this script.ts file, which is really just a very plain typescript script that you can execute. If you just want to test that it works, you can add this hello or some other logging statement, and then you can execute it with npm run dev. That's just executing the code in the script. Now you will have a number of tasks to write certain kinds of prisma Client queries, and then after you have written one of these queries, you can just run them against the database again by running the script with the npm run dev command. A couple of hints that I want to point out before I get started here. I really want to recommend to you that you type yourself, so even have to look up the solution by expanding the solution block here. I still recommend you to not copy and paste the code from that solution block, but still type it out. That has a lot more of an instructive and educational effect for you and your brain if you actually type these things yourself, and it also will enable you to experience prisma's autocompletion, which is a very nice feature in general when you're using prisma. That's also already the second tip that you can use autocompletion. I'll show you how that works, and whenever you want to see what the current state of your database looks like, you can run the npx prisma Studio command again and explore your data in prisma Studio. All right, so let's see how we can write queries with prisma Client. Write a query to return all user records. To warm yourself up a bit, go and write a query to return all user records from the database. Print the result to the console using console lock. Let's make our first query. I'm going to store the results of my query in a variable called users, and then I use the await keyword because prisma Client returns promises for all of its queries. Here you can already see the autocompletion that we have available when using prisma. At the moment, it shows a lot of these top-level prisma Client methods that are all prefixed with a dollar to avoid name clashes just in case the models that you defined in your prisma schema conflict with the names that we gave to these higher-level methods. The most interesting right now is this one user property on the prisma Client instance, which now gives us access to all the model queries that I can execute for this user model. So if I look at the autocompletion, I can see a ton of different queries that are possible here, and in this case, the appropriate one that just returns everything if I don't provide any further filtering or pagination is the find many query, and then I'm doing as told and logging all the users using console lock. At this point, I'll actually stop prisma Studio, trash this one, and I'm only using this terminal, and now I can run npm run def, and when I run this, I basically expect to see the three database records that I previously created with prisma Studio to be printed to the console now, and that's exactly it. So prisma Client always returns plain javascript objects, in this case, an array of javascript objects, and we can actually see the type of that array also when we hover over the user's variable right here. So that was very easy, very straightforward. Let's see what the next task is. In this task, you will create another user record in your prisma Client query, provide only a value for email, but not for name, and the value for email should be ls.prisma.io. So again, I'm storing the result in a variable, I'm using a weight, I'm using the autocompletion to navigate my weight to all the model queries on user, and I find the create one, and the create option, the create query takes an object where I can now provide the data that I want to provide when this record is being created in the database, and in the task, it says I should only provide an email, and that's okay because only email is actually required. That's ls.prisma.io. Again, I'll log this to the console like this and run npm run dev. So that's worked. If I now were to create find many again, to run find many again, then we would see an array of four users that are being printed, but what I cannot do, for example, is to run the same query with ls.prisma.io again without an error. And that's, of course, because we declared the email as unique in our database schema, so if I run this, so to prove my point to you here, npm run dev, I'm running the script again, I see an error, and the error message says that a unique constraint failed on the field's email, which is true, right? We are violating the unique constraints that we defined in the prisma schema, and that's why we cannot execute this twice with the same email, at least. All right. The next query is a query to update an existing user record. In this task, you will update the user record you just created and add a value for its name field. So let's go ahead and do that. I'll have, of course, to change my query here, so update seems like the right model query to use here. And now, in addition to the data that I want to provide, so I want to set the name of this user to Alice, but I need to identify the user that I just created as well, and that I can do by adding this where option here. And what's very neat about using prisma Client in this way is also that where now accepts all the fields from your prisma model that are declared as unique, and these are ID and email. And if you're wondering about the ID field here and wondering, but we didn't declare that one as unique explicitly, that's true. We didn't add the unique attribute here because we don't have to, because unique is already implied in the ID attribute, which is the unique identifier, the primary key for that particular model. So that's why we don't have to add at unique here in particular. And I can now go and just use the ID of that record that I just created, but in this case, I want to do it by email, alice at prisma.io. So I'm running this, and now the name is actually set to Alice on the same record that was just created. Let's go ahead with the next task here, and that's to create a migration and a second model, or first the second model, then the migration in that order. And we're adding a post model. So I'm using the model keyword again, I'm calling this model post, and this first part I can just copy and paste. All my models will have this configuration for their ID fields. And now let's see what else we have to put on there. So we should put the title, the content, a published field, and an author on the post. So let's start. Title is going to be a string and required in the database, so no question mark. Content is the content body of the post. This field should be optional in the database, so I'm adding the question mark. A published field indicates whether a post is published or not. This field should be required in the database, and by default, any post that is created should not be published. So how can I represent the state of published or not published with a data type that's being supported by prisma with a Boolean? So it has two states. It's either published or not published, and by default, I want this to be not published, so I'm adding false here. And then I also want each post to have an author and an author ID. So I'll start with the author ID, and this author ID now actually is a reference to this ID field on the user model. I'll explain later what exactly that means. And then in addition to that, I can also declare this user fields, or I have to declare this user field as a relation field. So I have to mark it with the add relation attribute, and that's just how you configure in prisma when two models are related to each other. Under the hood, it's then using foreign keys for this. And the way how you write this is that you tell in the relation attribute which fields of your current model should be the foreign key. So that's the author ID. And now you tell it which fields it references on the user model, and that's the ID field in this case. And right now, prisma is not yet happy because prisma always requires both sides of a relation. So if you declare a relation between two models, it's not enough to only say on one side of the relation what it's supposed to look like, but you actually have to do it on both sides of the relation. I'm also using the formatting again, and now I have a nice prisma schema here that reflects the changes with a new model and with a relation between user and post via the author ID foreign key right here. The relation should be optional. So I also added the question mark to both the author ID and the author fields. And to run the migration, I'm just using the same command as before, but I'm supplying a different name to the migration. If you're curious what happened under the hood again, you can look at the SQL that was generated, and really it only created one new post table with a bunch of columns in the database here. All right. Let me do one or two more prisma client queries because before you get the time to work on this yourself. So let's go and write a nested query, a query to create a new post record. It's not yet a nested query. So now you can already see that in the auto completion, we now have this new post field on the prisma client instance as well that now lets us invoke the queries for the post model. And in this case, I'm creating a new post. So nothing too fancy here. That's the same that we've done before. And we see that the post was created. It's being printed. And the last query that I want to show is how to connect a user and post with a nested query. So we now have several user records and exactly one post record in the database. These can be connected via the author ID foreign key. When using prisma client, you don't need to manually set up a nested query. We want to update the post record and connect it to an existing user via the email. So we don't actually want to set the foreign key, but we want to set the email. And I can still remember that it's the post with ID one. So I'm finding the post by its ID this time. And we then want to update the author field. So I'm using the data field inside of the update object that I provide. And then I can just navigate my way through with the autocompletion that I can invoke every time with control space and connect. And I want to connect the author via the email. So I'm running this. And now I can actually see that this was successful because I can now see that this, which prior was null after I had created the record, now the author ID is set to four. And just to very quickly print all the posts that we have so far. I can use find many again to show you that this post was created. And then a cool thing that you'll explore a little bit later, but that I'm already showing you, you can use the include option in all the prisma model queries as well to also fetch a relation. So if I now run this code, it's not going to print anything because I don't have a console lock statement posts and PM run death. You can actually see that it prints the nested object because it was fetching the author for that particular post as well. So one hint first from Alexander was to use a console dot table, which I think is a very nice idea, especially I don't know if it also works with related data, but I also use sometimes like console dot deer. Let me see how it prints the relation here. Yeah, it says this is an object. What's nice to print more deeply like nested objects is this command with console deer. Ah, actually depth null, not depth null. Perfect. So that also works and it works nicer if you actually have like two or three levels of nesting. Another question was from Fernando, what if I want to create a field with an alias, for example, store it in the database as updated at with a spelled and snake case, but then I want to name it prisma schema in, I want to name it in a camel case in the prisma schema. And Romeo was the kind to already respond to that. It's indeed possible with the attribute. So I'm not going to change anything in the prisma schema, but I'm going to show you how it works without running a migration. So if I wanted to do this now, and I have this updated at, I wanted to be camel cased in my prisma schema here. That's of type like daytime. And then I can use map right here and say in the database, I want this to be called updated at, and that way when then prisma migrate creates the SQL, actually it would be spelled in snake case here as well, because that represents the source of truth for the database. So yeah, you can do this with at map on field names to map them to column names, but you can also do this with model names and map them to table names. So if I wanted this post model to be called lowercase post, then I could do this. And now it would be lowercase posts in the database and uppercase posts in my prisma schema. Then another question from Romeo, how will we know that the relation is many to many or one too many? That you can pretty much directly see inside of the prisma schema, right? Because inside of the prisma schema, you declare whether a field is a list or not. So in this case, on one side, I declared that there is a list. And on the other side, I declared that there is no list, but only a single field or a single entry. And that's why we can know that it's a one too many. If we wanted to make this a many to many, where one post could have multiple authors, which would be a valid use case, I guess, I could do this. And in this case, we would have a many to many relation where we wouldn't store an author ID anymore because prisma now under the hood would create a relation table or a join table for you to keep track of all the related records. Another question about the name flag in the prisma migrate dev command. So the name flag is responsible for naming the migration. So it's what's being appended to the migration directories that are being created on your file system when you supply this value. Next question, is autocomplete the only best way to see all the query params available? If you ask me, yes, it's the best way, but it's not the only way. So we have pretty extensive api documentation for prisma as well. So if you go to our docs and you check out the prisma client api reference, you can see under the model queries for each kind of query for find unique, all the options that are being provided, information about the return type. So very comprehensive documentation that lists everything that you can do with all of these queries. Let's see if there are more questions. One more question from Christian. Let's say I have an SJS project and it uses Mongoose. I'm thinking of changing it to sometime in the future to use Postgres. Can I implement prisma in my project? So I can use mongodb then when the time comes, it'll just change the data. That's a very good question. So the question basically is if the prisma schema abstracts over the data sources in a way that you can always just swap out the underlying database and continue working with the same schema. If I understand your question, probably their question, otherwise feel free to correct me. So the answer to this is no, you cannot do this. And there is actually quite a good reason, I think, why this is not the case and why this is not possible. The prisma schema is not intended to be a one size fits all database abstraction. We certainly acknowledge that each database and each type of database comes with their own kind of strengths and weaknesses with their own features. And we want to make sure that we support all the features of a database when we build a prisma connector for it. And specifically, as an example, why this is not possible, if we look at SQLite, for example, which is a pretty straightforward database in terms of the data types that it supports. And for example, in SQLite, you don't have support for enums, but enums are a crucial part of data modeling when you're working with Postgres or MySQL. So on these other databases, enums are supported. That means that now here in my SQLite prisma schema, I am not able to define an enum. But if I were to change this to Postgres, where these are supported, then I would be able to use an enum here. But if I change this again back to SQLite, it's not possible. So we try to still make sure that no matter which database you use, you can make use of all the underlying features of the database. And in your example, specifically, Christian, where you're thinking about moving from mongodb to a relational database, this is also a lot more complicated than, of course, in terms of how you modeled your data in the first place in mongodb. If you're using, for example, a lot of embedded documents, there is not really a good way to represent these in a relational database. I guess on Postgres, you could use the JSON column. So that might be a way how you can migrate the data. But in general, it's not a use case, at least, that we're optimizing for. But if you your prisma schema only uses kind of pretty straightforward features that you also find in all other databases, then I guess it's possible. But it's not really a use case that we're necessarily optimizing for. Is there a way to generate models from an existing database, such as SQLize Auto for SQLize? I'm not familiar with SQLize Auto, but I think if I understand your question correctly, then yes, there is such a way. We call it introspection. And it basically is a command in the prisma CLI. It's called prisma DB poll that runs the introspection. And if you run this against a database, then it will read the database schema. So it will look at the tables, look at the columns, and it will populate the prisma schema for you so that you don't have to write the prisma models by hand if you already have an existing database. Yeah, it's like super helpful, the prisma DB poll command. And in fact, this is also something that we're currently building even for mongodb to make it easier for current Mongo users to get started. And of course, with mongodb, it's a little bit more involved because you don't actually have a schema, right? It's not something that's being enforced by the database itself. So we're kind of hoping that the data that people have put into their MongoDBs is consistent. And then we are reading it out and deriving the model structures from the individual objects that people have in their database. An example of how DB poll works. So of course, what I can actually do here is to quickly show it. So assume I have an empty prisma schema here. So far, I only defined the data source. And if I now want to populate the prisma schema with the models that adhere to the tables that I already have in the database, I can run npx prisma DB poll. It reads the structure of my database and creates the models for me. The only thing that you should be aware of is that these relation fields, because they don't actually exist in the database, right? These are virtual fields that you only put in the prisma schema to have a nicer prisma client api when working with relations, but these don't exist in SQL. It will not infer any smart names for these, but only name them according to the type. So you probably often want to rename these after you ran a prisma DB poll to make a little bit more sense and to fit the casing of your models otherwise. All right. There is a question from Pratik about defining content as string and what's the limit for a string and in types. And this question is interesting because it cannot be answered just by looking at the prisma schema because the prisma types are only abstractions over the underlying data types of the database. So in this case in particular, if you're looking at the post, we see that this was just created as a text under the hood. So now you would have to go and look up in the SQLite documentation how much characters are allowed for the text data type. And in fact, I recently learned this about SQLite. So it's special to SQLite that there are no VAR charts of a specific link, of a specific size. So if you use a SQLite database and you want to declare a column as VAR chart of like 30 characters or something like this, and then you run a migration, SQLite under the hood will actually also, it will accept the command because it accepts VAR char as a keyword, but under the hood, it will just create a text for you, a text data type. For Postgres on the other hand and MySQL and other databases, you can enrich the data types that you're declaring here in the prisma schema with native type attributes that you can access like this. DB in this case is the name of the data source. And here you can now specify, for example, how long the title is supposed to be. And that way the constraint will be enforced when you try to create a post record in the database that has more than 256 characters. All right. I think that all the questions from the chat are cleared. I would already suggest to move on with the next task. Maybe a quick show of hand again, although, wait, I cannot. Okay. So quick show of hands again, who has already done with all. Quick note for those of you that ran into the issues of the null constraint violation on the fields author ID. I'm supposing, I'm guessing that you forgot the question mark right here. And that way you're making this field required. And what prisma Migrate is trying to tell you here is that you're trying to add a required column to the database, but that would violate the consistent or the integrity of your data because you already have a post record in the database that has no field for author ID yet. So first you would have to go and update all the records that you have already in the database, set a value for them, and then you could run this migration. But the easier way is to just make this optional for you to circumvent this problem. I think that should be the issue. Let me know if that's not. Issue from Derek about the new model not showing up in autocomplete, but you can execute queries against it. So that's probably then an issue with your VS code setup. I have seen some issues with that in the past. And what usually helps is to restart the typescript server inside of VS code. And the way how I usually do this is I use the command palette with command shift P, and then I can type restart typescript. Wait, did they remove this? Maybe they renamed it since I last used it. Restart dev server. No. Interesting. I don't know why this is not working anymore, why this is not possible. What else you can do is just to try and delete node modules and reinstall like node modules because that will also regenerate your client and maybe your prisma types would be picked up then. So that's another thing that you could try. Just delete node modules, run npm install again, and that should then hopefully fix your types. But yeah, it's an issue with kind of your VS code setup. Another question, do we always have to give a unique ID to find an entity and find unique? Yes, that's the entire point. Find unique is a method that helps you to find a record in the database by any unique property. And as I mentioned before, that could either be one where you use the unique attribute or also just the ID because there it's implicit that this is unique. If you just want to have a random record, you can use find first and that's when you don't have to provide a unique field. All right, I would suggest that we move on with the next task or with the next lesson. And that being said, in the next lessons, by the way, you will also again write prisma Client Queries. So it's not the end of the world. If you didn't get through all the prisma Client Queries here, you will now basically learn how prisma Client integrates into a REST api or into a graphql api, kind of how it fits in. And for that, you are going to write prisma Client Queries again. But instead of running them inside of a script, you'll add them in REST api routes and graphql resolvers. So let's go back to the overview page and move on to the next lesson. REST api. And let's first go and follow all the instructions for the setup. So here I just provided a number of commands that just make it easy for you to switch over to the new lesson because we're going to work in the same project the entire time. And I prepared kind of the starter kits for lesson three and lesson four in different ways. So let's go ahead and do that. So let's go ahead and do that. So we're going to run our lesson four in different branches of the same repo. So first you can just stash your current work and reset everything that you've done. Then you can check out the REST api branch. Actually, I'll run these one by one. Checking out the REST api branch, I'll talk about the data model in just a bit. And we also start from scratch with our database. So we're removing we're removing our SQLite database files. We're removing all the node modules and reinstalling them. And that being said, let's quickly look at the package JSON file and see what kind of dependencies we have in this project. So it's very straightforward. We only use the prisma client and prisma dependencies that we had in the previous project already. And then we add Express and the types for Express, the types for node, typescript node, and typescript because we're using typescript here. Oh, and actually, I was wrong when I said that we wouldn't be using the seeding workflows because I did configure the seeding here so that each project we already start with some data in in the database. But before we do that, and before we run that, let's take a look at the prisma schema in this one. It's very similar to the prisma schema that you were using before. So we have the user and the post records. And then we have two new fields here created at and updated at that are being maintained at the database, each also with default values. So in this case, prisma is always going to generate the current timestamp to keep track of when a record has been created. And also this updated at will be invoked every time a record is being updated and write the current timestamp to keep track of the last update of every record. And then we also add one new field that's called view count. It's an integer and it starts at zero and it's used to keep track of how many people have viewed a particular post in our app here. For the setup again, let's run the npx prisma migrate def command. And one thing to note here is that under the hood, this command is also invoking the prisma db seed command. And the db seed command in turn is looking at the package.json file and it checks what I have defined as my seed command here. And in this case, it just executes a seeding script and the seeding script is just some random data for us to get started with for this task. So if I now open this in prisma Studio, I see that I start with a user table with three user objects for post objects. And I can actually also navigate and change the relations in here. One of the very nice features about prisma Studio, the way how it handles the traversal and the configuration of relations among your models. But let's go back to the notion document and to your tasks. So for this lesson, you will find all of the tasks marked with to do's inside of the index.ts file here. And that file has a bunch of comments already that are marked as to do. And you can go through them and just figure out what's the right prisma client query based on the instruction that you see here again in the notion document. Collapsing on the solutions here. All right. So, and the way how this is going to work, by the way, I created a couple of test HTTP requests for you that you can use if you install the REST client extension inside of VS code. This is super handy when you need to test like REST APIs and you don't want to leave VS code for that. So as an example, I'm going to show you how to do this with the user's query that's just supposed to return all users. So I'm using the same find many query that we were using in the script before. And now I can start the express server again also with the npm run def command. And the server is now ready at port 3000. And if I go to slash users, then it will invoke the user's route that I just implemented and fetch all the users from the database and return them. But to make this even nicer, you can use this test HTTP file and you can also test this right inside of the inside of VS code here. So if I click on send request, I also see the HTTP request that was fired and the response that I'm getting in return with the data that I'm expecting a little bit nicer, a little bit more nicely formatted than in the browser. I could also already try to send this post request to create a new user. But because I haven't implemented that route yet, and I'm also not returning anything, this is actually just running into kind of an infinite loop here. So let me first go ahead and implement this query to create a new user based on the name and email that I receive here npm run def. Start the server. And I can go back to the test HTTP. Now I click send request again, and I see that the new user was created. Same problem as before in the script that I showed you, if I send this same request here one more time, it again fails with the unique constraint violation on the email field because I haven't changed the email value and I was trying to create a second record with the same email. But if I do this and send the request again, then I created a second entry just with a slightly different email. Let's see what other tasks there are. And then you will get some time to work on it by yourself. Actually, I want to show you this particular one, the incrementing of the view count of a post and how that's done. For that, I'm using the app.put method here or function call and the route is at post slash ID slash views. So what I know is I want to increment the views of the post with this ID that we already extract from the request params. And I want to increment it by one. And the way how to do that in prisma is by using an atomic operation, atomic number operation to be precise. And again, I'm just using the auto completion here. I want to update the post and I provide the ID. And now I can say I want to increase the view count and increment it by one every time that this is being run. I think I actually have to do this because it comes out as a string, but it expects a number here. So that should work. So that should work. I'm stopping the server and restarting it. And now I can go into my test.http file again, and I send this request and you can see how on the right side the view count is incremented by one every time I send this request to the api. Okay. That's it for this task, for this lesson and the walkthrough. So now you'll have say 20 minutes again to implement these prisma client queries yourself and test them inside of VS code directly. So what graphql basically is, it's an alternative way to talk to an api server from the front end. So even though it has the name QL, so query language in the name, it's not actually database technology, but it's an api technology. And it's similar to like REST APIs in that it lets a front end developer or a mobile developer send a request to an api and then the application server is going to perform some logic, retrieve some data from the database and return that to the client. With REST, you typically have a situation where you have a number of different endpoints, right? So in the example that we were just using ourselves, we have all of these different endpoints here. We have a user's endpoint, a signup endpoint, a post endpoint, post ID views endpoint. So we have all of these different endpoints and we send different kinds of HTTP methods to these endpoints. So get requests, post requests, put requests, and you also have delete requests. So you have other kinds of requests that you can send to these REST api routes. And with graphql, it's not the same. With graphql, your api server only has a single endpoint and your front end and mobile developers, they send graphql queries to that single endpoint. And that makes a lot of the things that front end developers usually have to deal with in their work, such as aggregating data from different endpoints or over-fetching and under-fetching data. A lot of these problems that front end developers have to deal with makes these problems obsolete because graphql lets you very precisely define what data you actually want to have from the server and from the database as a front end developer. So that's kind of the high level overview. If you want to learn more, I would recommend to you. I have also checked this in graphql and NestJS. So it's also part of NestJS indeed. When you're using NestJS, they also have a graphql integration that allows you to build a graphql api as well. And that's where I was, you can see. But today we're not going to use NestJS graphql, but we're going to use another graphql implementation on the server that's called SDL first or schema first with apollo server. Okay. I was mixing this with this graphql with NodeJS graphql. That's why I was asking that. Oh, okay. Thank you so much. Yeah. And so for the next lesson, this is the graphql api. I'm going to run through the setup code again. So again, we're stashing everything that we worked on before. We're checking out the other branch with the graphql api. We're removing our migrations and database and also node modules and reinstalling them. The data model is the same as in the previous lesson for the REST api. So no surprises this time. And to continue our setup, we run NPX prisma Migrate Def again, and we're feeding the same data again as well. So let's take a look at the project. Here is the package JSON file with a few more dependencies this time. So we have apollo server, graphql and graphql scalars as dependencies. And then a few more that are needed to get this project running again, and all the typescript dependencies here. And then all the code again is located in index.ts. And in fact, we can already go ahead and run the server, start the server here, and then open a graphql playground, which allows me to explore the graphql api here. And at the moment, I can already see what kind of queries are available because the schema for my graphql server is already defined. The schema is defined right here, and that defines all the operations that I can send to my graphql api. And here in the documentation, the playground, you basically see what operations you have to find in the schema. However, if I start sending queries now, and would, for example, want to retrieve all the users and the IDs and the names of them, then I get an error. And that's because the resolvers are not yet implemented. The resolvers are right here for each operation in the schema that we have defined. We have to define a corresponding resolver function that's responsible for fetching the data for these resolvers for these operations. So starting with the all users query, we would go in and we can say, return prisma. And actually, we have the prisma Client instance attached to the context here. So in order to get auto-completion, we have to use the context.first here. Text is our prisma Client instance. And then we can just do the find many as we did before. And again, I have to restart the server after I made a change. It's running. And now I can actually see which users are in the database because the resolver is now implemented. One thing to note, though, is that about graphql, a nice thing is that you can send nested queries. So I can do this to learn more about the posts that a user has published. But this isn't working yet. So we should actually already see data here. But we only see empty lists. And that's because the relation queries have to be implemented via these so-called type resolvers down here. And the implementation of these follow a pretty straightforward pattern that you don't really have to understand if you haven't worked with graphql before. So that's certainly beyond the scope of today's workshop. But the way how to implement them is just to find the author of the posts like this. So you use a find unique query in here. You identify the current post by the ID. And then in the end, you're using the fluent api to invoke that author. And this is the same for the other way around, except that here you're using user and posts. And that's the pattern how you always implement these type resolvers with prisma. You kind of have to learn that once and then you can apply that everywhere. Sorry, accidentally inserted a string here. Okay. I'm stopping the server, restarting it. And now if I send this nested query, I can actually, for each user, I can actually see what posts they have published already. Very cool. That's pretty much it for the walkthrough again of this part. I will give you about 20 minutes again to work on these lessons yourself. And then we are going to go ahead and wrap up the workshop for today. Yeah, it's a really good question. In this case, actually, we're always using post requests, because that's how graphql usually works. It's that you have to create a post request and then you have to create a post request, and then you have to create a post request. And then you have to create a post request, and then you have to create a post request. You send a post request to the graphql api, and in the body of that post request, you include the query. So if we are opening the developer tools here, and we inspect the network tabs, and we see that, so we are sending post requests, and as the payload, we see that the query that's defined here in the graphql playground is included. And you can access the playground here at localhost 4000. There was one question, any idea why the autocomplete isn't working on the graphql branch anymore? Also on the context object, previously it worked just fine. That's a good question. So it should work on the context object as long as it's defined as the context type here, because on the context type, we define the interface with a field called prisma that's of type prisma Client, and that way it should work. And I don't really know why it doesn't work for you. I am sorry. If you want to get the autocompletion, I mean, you don't actually have to use the prisma Client instance via the context, but what you could also do is just inside of your index.ts file, and you could go and instantiate prisma Client like this, and then just use the autocompletion like this again. But in graphql, it's kind of good practice to include any objects or anything that all the resolvers need access to inside of the context object. So as a best practice, that should be done, but I guess for the demo project today, it's not super relevant how you do it. All right, everyone. Are there any more questions about prisma or the workshop today? I slowly want to start wrapping it up. Of course, you can finish everything on your own time. All the solutions are in the Notion document anyway, so as long as you have access to that, you basically have access to all the resources that you needed for this workshop. And there are also, again, the contact details, how to reach me is included in the Notion document as well. There was one question about the generator in the very beginning of today's workshop, and I quickly wanted to circle back to that actually, because the concept of a generator is actually really interesting, I think, and also, again, distinguishes prisma from other ORMs. Basically, a generator is a function that's being invoked when somebody calls the npxprisma generate command. Now, you haven't actually used this command today, because just like the bbcd command, it gets called under the hood when you run prisma Migrate Dev. That's why you never had to explicitly run this command by yourself. But if you invoke it, then what prisma does for you, it invokes all the generator blocks under the hood. Invoking a generator block means that it invokes the function that's associated with the provider. And the main one that you typically use for that is the one for prisma Client that generates the data access library that we have been using today. But the cool thing about these generators is that you can also build your own generators. And we have a whole section in our documentation in the section about generators that lists a number of existing generators that have been built by the community. So, for example, let's try the prisma ERD generator. I haven't done this in a while, but I hope that this is functional. So, ERD is a certain kind of diagram to visualize the entity relationships in your database schema. So, it's the prisma ERD generator. And if I run NPX prisma Generate again, actually, first, I have to install the generator in my project. Otherwise, it can't be invoked. And we have also integrations for existing frameworks and libraries. So, for example, there is a TypeGraphQL prisma generator that will generate all the CRUD resolvers that you need for a CRUD api with TypeGraphQL, which is really handy. Oops, not again. NPX prisma Generate. So, let's see what it looks like when it generates the ERD, the Entity Relationship Diagram, Generated Entity Relationship Diagram as an svg file. Okay, that's an svg. Is there, I don't know, can I, what's the fastest way to visualize an svg actually? So, that's what's being generated from this prisma ERD generator. And it's basically, so like these generators are basically tools that look at your current prisma schema, and they somehow transform or use the input from the prisma schema, your models, to turn them into something else. And that could be resolvers for graphql APIs, that can be diagrams, it can be like validations using the Zot library. So, a lot of different things that are already available here, but it's really up to you what you want these generators to do and build your own one after all. Okay, let me see if there have been new questions. In a few sentences, why should we use prisma as an ORM in favor of other solutions? Okay, that's an interesting question that I can quickly speak to. So, first and foremost, when you're picking a database library for your project, you first have to decide if you want to pick a database library where you in the end still have to create SQL queries. So, you can use plain database drivers like NodePG, for example. That's a plain driver where you instantiate the database client like this, and then you just submit SQL statements to this client that you write as strings, or you use a query builder like Knex.js, where in the end, you're still constructing SQL queries, but by calling functions. So, that's already a little bit closer to what we do with prisma, but in the end, you still have to know and be intimately familiar with SQL to use this. And if you don't want to do this, then you can use higher level abstractions, these so-called object relational mappers, ORMs, and in the typescript space and node.js space, there are really two that are the most popular at the moment. One is called SQLize, and then TypeORM. We have comparisons for both SQLize and TypeORM in our documentation, so if you're curious how prisma compares against these, you can also go to our documentation and check out the comparing prisma section here against TypeORM and SQLize. The short story why I would recommend prisma over TypeORM and also the main benefits that we hear from our users compared to these two libraries is the way how you do data modeling in prisma is much nicer, much better experience, and the prisma schema as the single source of truth for your database and for your application models, then the generated api and how straightforward and intuitive it actually is to query your database. Where in SQLize or TypeORM, because they also don't have very strong typescript support, of course, TypeORM is a little bit better there than SQLize, but you'll find yourself often looking up api documentation to figure out what a certain query has to look like with each of these. And with prisma, that's really not the case. You can figure out most of the queries just by typing and using the autocompletion. And another thing actually is the type safety and the type safety guarantees that prisma can provide. And we have a comparison in that regard with TypeORM, which otherwise is probably the most type safe ORM in the typescript ecosystem. Certainly not more type safe than SQLize. And here we're explaining various scenarios in which prisma can provide stronger type safety guarantees than TypeORM can. For example, when you only select partial models or when you include relations, then this is always strongly tied with prisma through some very interesting typescript magic that we're doing under the hood. You will see that all of your prisma client queries, even if you include relations, even if you select only specific fields, will always be strongly typed. All right. I think that's it. And we are ready to wrap up the session for today.