Prisma is an open-source database toolkit. It replaces traditional ORMs and makes database access easy with an auto-generated query builder for TypeScript & Node.js.

prisma

Prisma is an open-source ORM. It consists of the following parts: 

- Prisma Client: Auto-generated and type-safe query builder
- Prisma Migrate: Migration system
- Prisma Studio: GUI to view and edit data in your database

Prisma Client can be used in any Node.js or TypeScript backend application (including serverless applications and microservices). This can be a REST API, a GraphQL API, a gRPC API, or anything else that needs a database.

In this course, you will learn how to initialize Prisma in a TypeScript Node project, use Prisma Client to create and find records, use Express to create data dynamically, and more! 



Kamil Khadeyev

Prisma offers an extension for VS Code that enables syntax highlighting, autocompletion, and more for Prisma Schema files. In this lesson, we learn how to install the Prisma VS Code extension.

Install the Prisma VS Code Extension

The Prisma CLI comes with commands to initialize Prisma in a project, run migrations, generate Prisma Client and more. In this lesson, we install the relevant dependencies and initialize Prisma in a TypeScript Node project.

Initialize Prisma in a TypeScript Node Project

Prisma offers a powerful database modeling language called the Prisma Schema Language. It allows us to describe the shape of our tables, the columns they should have (and their types), and relations between tables. In this lesson, we get started with our first table in our Prisma Schema file.

Create a Model for a Table and Run Migrations

Prisma Studio is a GUI for viewing and interacting with our databases. In this lesson, we view our database with Prisma Studio and use it to create our first record.

Use Prisma Studio to Create a Record

Prisma Client is a fully type-safe database access client that is automatically generated based on our Prisma Schema files. In this lesson, we use Prisma Client to access records in our database programmatically. We show how to return the data from an endpoint built with express.

Use Prisma Client to Find Many Records in a Table

Prisma Client has a full API for selecting subsets of data in our tables. We can use the WHERE clause to match specific conditions for which we would like to return data. In this lesson, we look at how to use the WHERE and its operators to return a subset of data.

Use Prima Client to Select a Subset of Data

Prisma Client offers methods to create new database records. These methods accept a data object and return the result to be consumed by clients. In this lesson, we use Prisma Client's create method to create new records in the database.

Create a Database Record with Prisma Client

Prisma is API-agnostic meaning that it doesn't matter how we build our APIs. They can be REST APIs, GraphQL APIs, or anything else. In this lesson, we see how to use express to accept JSON data to be forwarded off to Prisma to create new records in the database.

Use Express to Create Data Dynamically

Prisma Schema Language is used to model our database tables and establish relations between them. Prisma offers autocompletion to register the relations. In this lesson, we create a new model for a second database table and see how to relate our two tables together.

Create a Relation Between Two Tables in the Prisma Schema

When two tables are related to one another, we can use Prisma Client to query for data through those relations. In this lesson, we see how to use Prisma Client to tie together data from two separate tables and pull it out in a nested fashion.

Use Prisma Client to Query for Related Data

Prisma Client has a specific API for relating data from one table to another when creating new records. In this lesson, we use the connect operator to establish a link between two tables as new records are created.

Use Prisma Client to Connect Related Tables when Creating New Data

Prisma Client allows us to limit the amount of data that is returned from queries. It also allows us to be specific about which data should be included in the returned dataset. In this lesson, we use the select and include operators to be specific about the shape of our returned data.

Limit the Amount of Returned Data with Prisma Client

We will learn how to leverage the power of Prisma to automatically generate a type-safe, autocomplete-ready, and efficient query builder.

Build a full-stack app with Prisma

This collection explores Prisma v2, first part of the Prisma infrastructure to exit experimental mode-Prisma client is an auto-generated and type-safe query builder.

Get started with Prisma v2. Prisma Client

Learn to use some of the tips and tricks that can help you write better TypeScript. 

TypeScript: Tips and Tricks

Build a Backend with Prisma in a TypeScript Node Project

https://app.egghead.io/api/v1/lessons/prisma-install-the-prisma-vs-code-extension/subtitles

Lecturer: [0:00] Prisma offers an extension for VSCode. It's very useful for doing things like syntax highlighting and auto-completion, and you'll want to use it when you are working with your Prisma schema.

[0:10] If you're using VSCode, you can install it from the Extensions Marketplace. Search for Prisma. The first result is the one that most users will want. It's the Prisma extension at the current version, the current stable release of Prisma, in this case, 2.26. You can install it here.

[0:27] The second result may be useful for you. It's the Prisma Insider extension. This extension is used alongside some Prisma features that are in early access or preview. In most cases, the Prisma extension at the current version is the one that you'll want to go for.

https://app.egghead.io/api/v1/lessons/prisma-install-the-prisma-vs-code-extension/transcript

https://app.egghead.io/api/v1/lessons/prisma-initialize-prisma-in-a-typescript-node-project/subtitles

Ryan Chenkie: [0:00] Here's a simple web server using Express and TypeScript. We've got server.ts. We are bringing in Express, and then we've got a single endpoint here at /ping, which sends back a message in this object here.

[0:13] If we want to start sending back some dynamic data using Prisma, we first need to get Prisma installed. In the command line, do npm install as a dev-dependency prisma. This will give us the Prisma CLI, and it will give us access to all the commands that we need to get Prisma initialized and do other tasks as well.

[0:32] Next, let's install as a regular dependency @prisma/client. This will give us access to Prisma Client, which is what we use to access our database.

[0:42] With those in place, we can now do npx prisma init. This will initialize Prisma in this project. What that does is it gives us access to this directory here called prisma. Within the prisma directory, we have schema.prisma. This is the file where we describe our database, the relationships between tables, and other information so that we can have Prisma derive a type-safe database access client for us.

[1:09] Prisma supports several relational databases, including Postgres, MySQL, SQLite and SQL Server. For now, let's use SQLite. That will give us a very easy way to use a database on the file system. Instead of pointing to the database URL in the environment file that we've got, let's change this up to go directly to file. Then we'll put the colon dot, and it'll be dev.db.

[1:34] This file doesn't yet exist, but it will be created for us automatically once we run a command to get our initial migration in place.

https://app.egghead.io/api/v1/lessons/prisma-initialize-prisma-in-a-typescript-node-project/transcript

https://app.egghead.io/api/v1/lessons/prisma-create-a-model-for-a-table-and-run-migrations/subtitles

Instructor: [0:00] Schema.prisma, this file here uses the Prisma Schema Language, the PSL. One of the key words in the Prisma Schema Language is model. Model is used to describe a table in our database. For this database, the first table that we'll want to have is going to be a products table. We can create a model called product.

[0:20] Every table needs to have a unique identifier. Most commonly, this is going to be an ID for the table. There are several ways that we can construct IDs. We can use an auto-incrementing integer, for example, or we can use a string, and use something like a collision-resistant unique ID.

[0:36] Let's opt to use the cuid() for this table. What we'll do is mark it as the primary key using the add ID symbol here. Then we'll have @default will be cuid() for collision-resistant unique ID. Some other fields that we'll want on this table will include name. That should be a string. We'll also want a description for the product. That's a string as well.

[1:00] For now, let's have one more field, and that will be the price for the product. Price, we will express as an integer. We'll keep this as the number of cents that the product costs. Instead of opting for a float, where we can do fractions, instead, we'll keep everything in cents in the database. Then we will display in dollars on the page.

[1:18] Save the schema.prisma file. Come over to the command line and run npx prisma migrate dev. We need a name for the initial migration, and we can call it init. Once the migration runs, we will have a dev.db SQLite database in the prisma directory as well as a migrations directory.

[1:40] Within the migrations directory, we have a single directory for that initial migration, and it has the SQL that was generated to run the migration. This migration creates a product table and creates several columns in that table.

https://app.egghead.io/api/v1/lessons/prisma-create-a-model-for-a-table-and-run-migrations/transcript

https://app.egghead.io/api/v1/lessons/prisma-use-prisma-studio-to-create-a-record/subtitles

Ryan Chenkie: [0:00] Running Prisma Migrate will create a SQLite database for us if we don't already have one created. We can visualize this database and look into the tables and data within it using any kind of database client that supports SQLite, or we can use Prisma Studio. In the command line, run npx prisma studio.

[0:21] Prisma Studio is a fully featured GUI to look at databases constructed with Prisma and has some special features that go beyond a typical database client. Click into the product model or product table and verify that the fields created for this table do exist.

[0:37] Prisma Studio allows us to easily add records right in place, and we can get our initial data started here. Come over and add in an initial record. Keep in mind that the price field is counting the number of cents instead of dollars. Click Save to save that change.

https://app.egghead.io/api/v1/lessons/prisma-use-prisma-studio-to-create-a-record/transcript

https://app.egghead.io/api/v1/lessons/prisma-use-prisma-client-to-find-many-records-in-a-table/subtitles

Ryan Chenkie: [0:00] This server.ts file has a single endpoint called "/ping" and that just gives a simple message. Run the app with npm run dev and verify in the browser that this will give us our message, message: "hello".

[0:13] Prisma Client can be used to get the data that is currently in the database and return it as JSON from an endpoint. Copy app.get here and create a new endpoint called "/products". Import { PrismaClient } from "@Prisma/client". Create an instance of Prisma clients with const prisma = new PrismaClient().

[0:37] In the new endpoint, retrieve the records using Prisma. We'll use async/await, so make sure that this function here is async and then call for the products, const products = await prisma.product.findMany(). Then in the response as JSON, we can return that products array. Save the file and in the browser navigate to the products endpoint.

[1:02] Prisma Client gets the products data from the database and the endpoint sends it back as JSON. When using the Prisma Client, it is important to note that it gives a full type-safe database access. On this Prisma object, there are methods and properties that are applicable to interacting with the database.

[1:22] The single table that we have right now is product and that's reflected if we look for what's available as we autocomplete this call. We have several Prisma methods, methods that are useful in various scenarios when interacting with Prisma Client.

[1:37] This property down here, product, points to the products table. On this property we have a number of methods that we can call, and all of these are generated by Prisma based on what we should be able to do with our database. In this scenario, when we want to find all of the records in our table, the findMany method would be the appropriate one to call.

https://app.egghead.io/api/v1/lessons/prisma-use-prisma-client-to-find-many-records-in-a-table/transcript

https://app.egghead.io/api/v1/lessons/prisma-use-prima-client-to-select-a-subset-of-data/subtitles

Instructor: [0:00] Prisma offers us a type-safe way to scope down our queries. If we have multiple records in a database table, which we most likely will have, we can use the WHERE clause to tell Prisma which records we would like to get back. In the call to findMany on product, supply a WHERE clause.

[0:16] The WHERE clause takes an object on which we can put a column name that were interested in. For example, we can put name. The column name then takes on objects on which we can use any of the operators that we might be interested in. For example, we might want to get products where name equals something like T-shirt. Save the query and test it out in the browser.

[0:40] We're not limited to strict equals comparisons. If we want to find a range, for example, on price, we can use the greater-than operator and supply a number to it. Let's get products where the price is greater than 1,000. We can supply multiple operators to do a compound query, for example, where the price is greater than 1,000, but less than 20,000.

[1:03] Because Prisma offers us a type-safe way to access our database, we won't be allowed to supply queries that are invalid. For example, Prisma knows that the price field is a number, so it won't allow us to pass a string here. If we try to do that, we'll get a red squiggly with a TypeError.

https://app.egghead.io/api/v1/lessons/prisma-use-prima-client-to-select-a-subset-of-data/transcript

https://app.egghead.io/api/v1/lessons/prisma-create-a-database-record-with-prisma-client/subtitles

Ryan Chenkie: [0:00] Prisma gives us methods to create data. We can create an endpoint here to add new products. Let's copy over the get endpoint. We'll make it a post endpoint going to products. In the handler, we can add in new products.

[0:13] Our constant of product now, singular, will be a call to await prisma.product.create(). The create method needs an object, on which we need to have a data key. Supply an object to the create method, put in a data key, and then on the data object, put in values for all of the columns that we'll need.

[0:33] On Mac OS, we can do Control-Space bar to find the property names that are required. We can supply name, description, and price.

[0:48] Save the call to create and then open up a res client to make a POST request to the products endpoint. To stick within VS Code, you can try Thunder Client. It's an extension that you can install in the Extensions Marketplace.

[1:03] We get back the new record with a unique id, and we can verify within Prisma Studio that the record was created.

https://app.egghead.io/api/v1/lessons/prisma-create-a-database-record-with-prisma-client/transcript

https://app.egghead.io/api/v1/lessons/prisma-use-express-to-create-data-dynamically/subtitles

Kristian Freeman: [0:00] Real-world applications will need to take this data dynamically from the calls to the endpoint. Prisma doesn't care about how you construct your APIs, whether they be REST APIs, GraphQL APIs, or something else.

[0:13] In this example, since we're using JSON endpoints with Express, we can take this data off of the request body. With Express, if we try to get the request body right away off of the request object, it's going to be undefined.

[0:29] What we have to do is apply a middleware to tell our Express app that it should forward the JSON data that comes in on a request to the handler. To do that, opt into the middleware by calling app.use(express.json()).

[0:43] With that middleware in place, the request body will now be defined. Remove the hard-coded data. Put in the data that comes from the request body. Name will come from body.name, description from body.description, and the price from body.price.

[1:03] Save the endpoint and go back to the REST client that you're using. In the JSON body section, supply some JSON data to send to the endpoint -- name, description, and price. Send the request to add the item to the database. Confirm that it went through in Prisma Studio.

https://app.egghead.io/api/v1/lessons/prisma-use-express-to-create-data-dynamically/transcript

https://app.egghead.io/api/v1/lessons/prisma-create-a-relation-between-two-tables-in-the-prisma-schema/subtitles

Instructor: [0:00] Prisma makes it very simple to relate one table to another. In a relational database, like we're using here with SQLite, it is very often the case that we'll want to have relations between different tables. Create another table that can be used alongside the main product table for reviews.

[0:17] Model review will create a new table. Use the same ID structure that is contained within product to provide an ID to the review table. Then provide the other properties that are relevant to a review, for example, text, which should be a string, and rating, which should be an integer.

[0:33] At this point, these two tables are not related together. They are standing on their own. To relate the two tables together, some information is required on each of the models. Add a new property to the product table called reviews, and for its type, say that it should be of type review, which points to the review model, and that this should be a one-to-many relation. A single product has many reviews. The many is denoted by the array-like syntax here.

[1:02] Save schema.prisma, and you'll see that there is a lot of information auto-completed. This auto-completion happens through the Prisma VSCode extension. In the review model, this new property called product is pointing up to the product model. Prisma automatically takes the casing from the model name, and most people come over and make this lowercase to abide by the rest of their property names.

[1:24] Prisma automatically made the relation to product optional for a review, but the business logic for this application says that reviews must be related to a product. To fix that, erase the question marks.

[1:36] The key name product here points to a virtual field called product, which is the Prisma model that is created above, and product ID is a string type that will be kept in the database table for review, which will point to product. Save the schema file and migrate the database, npx prisma migrate dev.

[2:00] The migration name can be Add Review. Check out the new table over in Prisma Studio. Open up a new table with the plus icon and check out review under All Models.

https://app.egghead.io/api/v1/lessons/prisma-create-a-relation-between-two-tables-in-the-prisma-schema/transcript

https://app.egghead.io/api/v1/lessons/prisma-use-prisma-client-to-query-for-related-data/subtitles

Ryan Chenkie: [0:00] Prisma Studio is aware of the relations between different tables. This makes it easy to manage data. If we add a record for a new review and give it a rating, we can then choose which product we want to assign this review to. Let's say that it should go to the first one, which is shoes.

[0:18] We can save this record, and we now have one review with a relation to a product. If we check out that product, we can see the review that is related. Prisma Client allows us to easily pull out this relation information when we make queries.

[0:33] In the call to the products endpoint to get all the products, add some information to tell Prisma Client that we want to get the review as well. In the findMany call, add an object with the include keyword and say that we want to include reviews true. Save the endpoint and check it out in the browser.

[0:54] Reviews shows up as an array because it is a one-to-many relationship between product and review. For cases where there are no reviews, the array is just empty.

https://app.egghead.io/api/v1/lessons/prisma-use-prisma-client-to-query-for-related-data/transcript

https://app.egghead.io/api/v1/lessons/prisma-use-prisma-client-to-connect-related-tables-when-creating-new-data/subtitles

Ryan Chenkie: [0:00] The relation between product and review is a required relation. A review must be related to a product. While a product may have no reviews, it might just be an empty array, there is this explicit relationship between product and review.

[0:16] When we create new reviews in the database, we need to provide information about which product it is related to. Create a new endpoint to handle new reviews. Copy the post endpoint that is already in place for products. Call it "/reviews".

[0:32] This new endpoint will have a const of review for a new review. Instead of going to prisma.product, go to prisma.review. Prisma's type safety is helping us already by saying that what we have supplied for data is invalid. Instead, we have to supply the properties that are expected. We need text that will come from body.txt and we need rating.

[0:56] The data key here is still invalid, and that's because we haven't supplied the product id to connect to, to relate the review to a given product. To establish this connection, we say that we want to connect to a given product. Product points to an object, on which we supply the connect keyword, and we connect by the product id. This will come in on body.productId.

[1:24] At this point, the call to create is sound, so we should be able to go to our res client and create a new review. Save the endpoint and before going to the res client, go to Prisma Studio and look for a product id that we want to supply the review to. Copy over that id so that we can use it in our res client.

[1:42] In the res client, make a call to reviews. It's a POST call. The JSON content should take the shape of what we are expecting in our endpoint. We'll need a text key, we'll need a rating, and we'll need product id.

[2:04] Click Send to make the call. We can now verify in Prisma Studio, and in the browser that the review was put into the database. In the call to products, we can see that we have the review coming through.

https://app.egghead.io/api/v1/lessons/prisma-use-prisma-client-to-connect-related-tables-when-creating-new-data/transcript

https://app.egghead.io/api/v1/lessons/prisma-limit-the-amount-of-returned-data-with-prisma-client/subtitles

Ryan Chenkie: [0:00] We can use Prisma to get back only a subset of the information that is contained within a table. For example, when we get back our reviews on the products, we may not want the id for the review or the productId. We might just want the text and the rating.

[0:14] In the include call, say that you want to get back only those two fields. Reviews becomes an object, in which we provide the select keyword. We say that text should be true and rating should be true as well. Save the endpoint and check it out in the browser.

[0:31] We can apply the same pattern to the base products model that we are querying for. If, for example, we wanted to get just the name and reviews, we can do so in the findMany call. To do so, we need the select keyword.

[0:45] Select and include cannot exist together at the root. In this case, we need to use just select. We can provide name: true to select, and then we can just take the reviews selection that we have already set up and put it up into the top level select. Then we get rid of include and save the endpoint to check it out in the browser.

https://app.egghead.io/api/v1/lessons/prisma-limit-the-amount-of-returned-data-with-prisma-client/transcript

Build a Backend with Prisma in a TypeScript Node Project

Ryan Chenkie

What you'll learn:

You might also like these resources:

Build a full-stack app with Prisma

Get started with Prisma v2. Prisma Client

TypeScript: Tips and Tricks

Credits

Course Content

You might also like these resources:

Build a full-stack app with Prisma

Get started with Prisma v2. Prisma Client

TypeScript: Tips and Tricks