Table of Contents

1. Challenge

   Introduction

   Welcome to the Guided: Implementing Prisma in a Next.js Finance Application lab.

   Throughout this lab, you will become familiar with how to integrate a Prisma database to persist data for an existing finance application. This lab will cover how to set up a Prisma database, specify a schema, build migrations, seed tables, and use the database within the codebase. This lab will use a sqlite database because it is lightweight and easy to configure.

   To start, you have been given a Next.js finance application. You can launch the application by entering npm run dev in the Terminal. The application has a Home page that displays all transactions in a table and highlights expense transactions in red and income transactions in green. There is also an Income page and an Expenses page where you can create, edit, and delete the corresponding transactions. Lastly there is a Plan page where you can create, edit, and delete budget plans. Currently the application reads data from JSON files in the app/lib/ directory. The application does not have support for creating, updating, or deleting any data.

   You will be responsible for creating the transaction, plan, cadence, and type tables, seeding data for development, and using Prisma to perform database queries to complete the CRUD (create, read, update, and delete) functionality the application needs.

   You do not need any previous experience with Next.js or Prisma to complete this lab. This lab assumes you have basic experience with Javascript, JSX, React, and Database Architecture. Details on Next.js and Primsa will be given to provide context for your implementations in each step.

   There is a solution directory with corresponding step directories that you can refer to if you are stuck or want to check your implementations at any time. Keep in mind that the solution provided in this directory is not the only solution, so it is acceptable for you to have a different solution so long as the application functions as intended. The solution provided will also be for the entire step.

   ---

   Activity

   Launch the application by entering npm run dev in the Terminal. The application can be seen in the Web Browser tab at localhost:3000. Take a moment to become familiar with the different pages. The starting application should look like the picture below:

   

   Remember: throughout the lab, code changes within the app directory and to the database will be live on the website after refreshing if the application is running. If you do however want to stop and restart the application, you can do so by typing Ctrl+C in the Terminal and rerunning npm run dev.

2. Challenge

   Prisma Overview and Setup

   Prisma Overview

   Prisma is a modern database toolkit designed for TypeScript and JavaScript applications. It simplifies database access by providing a type-safe query builder, an Object-Relational Mapping (ORM) layer, and a schema definition language. Here are some of its main components and features:

   • Prisma Client: An auto-generated and type-safe query builder for your database, allowing you to write database queries in TypeScript or JavaScript.
   • Prisma Migrate: A powerful and easy-to-use migration tool that helps manage your database schema and version control.
   • Prisma Studio: A GUI to view and manipulate data in your database.
   • Prisma Schema: A declarative language to define your database schema, models, and relationships.

   Prisma supports multiple databases, including PostgreSQL, MySQL, SQLite, SQL Server, and MongoDB. It aims to improve developer productivity and reduce the complexity of database interactions.

   ---

   Setting Up a Prisma Database

   Prisma has already been installed using Node Package Manager, npm commands, and is ready to be used in the application. If you are using npm in a project, simply run npm install prisma to get started. After Prisma is installed, you can initialize it with Node Package eXectute, npx commands. In this lab, you will initialize Prisma with SQLite.

   ---

   Activity

   Run npx prisma init --datasource-provider sqlite to initialize Prisma in Terminal.

   This initialization generates a prisma directory in the project. Inside the prisma directory will be a file titled schema.prisma. Your FILETREE should now look like the photo below.

   

   The schema.prisma file is where you will specify your schema definition in the next step.

   Take a moment to locate and examine the contents of schema.prisma. You will see that the schema initializes Prisma with sqlite, sets the database provider to sqlite, and the database url can be found in the .env file.

   The initialization command will create a .env file (if it does not exist) and add the following line to the .env file: DATABASE_URL=”file:./dev.db. Run ls -a in the Terminal to verify that the .env file was created and does exist. You can look at its contents by running the vim .env command. Then, type :q to exit the vim editor. The contents of the file should look like the photo below as it was generated by the npx command you ran to initialize Prisma.

   

   Once the database is created you will see a dev.db file in your prisma directory that is being referred to as the database url.

3. Challenge

   Specify a Database Schema with Prisma

   Specify a Database Schema With Prisma

   Now that Prisma has been initialized using SQLite, you will work on specifying a database schema for the project. In schema.prisma you will build the schema using Prisma ORM’s Prisma Schema. The Prisma Schema is the main method of configuration for your Prisma ORM setup. It consists of the following parts:

   • Data sources: These specify the details of the data sources Prisma ORM should connect to. The data sources in this application point to SQLite.
   • Generators: These specify what clients should be generated based on the data model (e.g. Primsa Client).
   • Data Model Definition: These specify your application models (the shape of the data per data source) and their relations.

   When schema.prisma was generated, it was created with correct data sources and generators. You will be responsible for adding models that match the following schema UML to the "Data Model Definition" section in the schema.prisma file.

   

   ---

   Defining Models

   Models represent the entities, or tables, of your application domain. For the finance application you are working on in this lab, those entities are cadence, type, plan, and transaction.

   In Prisma, models are represented using model blocks that define fields within them. Fields are defined with a field name and a field type with optional type modifiers and attributes.

   Prisma supports the following field types:

   • String
   • Boolean
   • Int
   • BigInt
   • Float
   • Decimal
   • DateTime
   • Json
   • Bytes

   The following field type modifiers are available in Prisma:

   • []
   • ?

   Prisma also supports the following field attributes:

   • @id:Defines a single-field ID on the model
   • @@id: Defines a multi-field ID (composite ID) on the model. This is the same as a PRIMARY KEY on the table.
   • @default: Defines a default value for a field.
   • @unique: Defines a unique constraint for this field.
   • @@unique: Defines a compound unique constraint for the specified fields.
   • @index: Defines an index in the database.
   • @relation: Defines meta information about the relation.

   ---

   Model Relationships

   A relation is a connection between two models in the Prisma schema, and a relation field's type is another model. In databases, there are three types of relations: one-to-many, one-to-one, and many-to-many. In this lab, you will only setup one-to-many relationships so if you would like to learn more about how to establish a one-to-one or many-to-many relationship, peruse Prisma's Relations Documentation.

   Here is an example of how to establish a one-to-many relationship using Prisma:

   model User {
     id    String    @id @default(uuid())
     posts Post[]
   }
   
   model Post {
     id       String  @id @default(uuid())
     author   User @relation(fields: [authorId], references: [id])
     authorId Int // relation scalar field  (used in the `@relation` attribute above)
   }
   
   

   ---

   Activity

   Underneath the datasource db object prisma/schema.prisma define the models from the UML diagram.

   1. Define the Type model from the UML above.
      • Define an id field of type String
        • The id field should have the @id attribute on it
        • Using the @default attribute, set the id field default value to uuid()
      • Define a name field of type String.
        • The name field should have the @unique attribute on it.
      • Define a plans field of type Plan[] to create a many-to-one relationship with the Plan model
   2. Define the Cadence model from the UML above.
      • Define an id field of type String
        • The id field should have the @id attribute on it
        • Using the @default attribute, set the id field default value to uuid()
      • Define a name field of type String.
        • The name field should have the @unique attribute on it
      • Define a plans field of type Plan[] to create a many-to-one relationship with the Plan model
   3. Define the Plan model from the UML above.
      • Define an id field of type String
        • The id field should have the @id attribute on it
        • Using the @default attribute, set the id field default value to uuid()
      • Define a name field of type String
      • Define a budgetAmount field of type Decimal
      • Define a description field of type String
        • Default this field to an empty string
      • Define a relation scalar field, cadenceId to use in the @relation attribute
      • Define a field called cadence of type Cadence that relates the Plan model to the Cadence model
      • Define a relation scalar field, typeId to use in the @relation attribute
      • Define a field called type of type Type that relates the Plan model to the Type model
      • Define a transactions field of type Transaction[] to create a many-to-one relationship with the Transaction model
      • Enforce a compound unique constraint on typeId and name fields
        • Hint Using the @@unique attribute with the array `[typeId, name])
   4. Define the Transaction model from the UML above
      • Define an id field of type String
        • The id field should have the @id attribute on it.
        • Using the @default attribute, set the id field default value to uuid()
      • Define a date field of type DateTime
      • Define a amount field of type Decimal
      • Define an optional notes field of type String
        • HInt: The ? type modifier is added to make a field optional
      • Define a relation scalar field, planId to use in the @relation attribute
      • Define a field called plan of type Plan that relates the Plan model to the Transaction model

   Example Model Definition in schema.prisma

   model Transaction {
     id String @id @default(uuid())
     date DateTime 
     amount Decimal
     notes String?
     planId String
     plan Plan @relation(fields: [planId], references: [id])
   }
   
   model Plan {
     id String @id @default(uuid())
     budgetAmount Decimal
     name String
     description String @default("")
     
     cadenceId String
     cadence Cadence @relation(fields: [cadenceId], references: [id])
     typeId String
     type Type @relation(fields: [typeId], references: [id])
     transactions Transaction[]
     @@unique([typeId, name])
   }
   
   model Type {
     id String @id @default(uuid())
     name String @unique
     plans Plan[]
   }
   
   model Cadence {
     id String @id @default(uuid())
     name String @unique
     plans Plan[]
   }
   

4. Challenge

   Create a Database and Migrations

   Create a Database and Migrations

   In the previous step, you created four models: Type, Cadence, Transaction, and Plan with all the properties that are necessary for the finance application at hand. At this point, you have a Prisma Schema, but you do not have a database.

   In this step, you will create the initial migration that will be responsible for creating the database and syncing it with the schema.

   ---

   Activity

   In Terminal run the following command:

   npx prisma migrate dev --name init

   This command has completed the following four things after execution:

   1. Creating the dev database which can be found in the /prisma directory
      • It is stored in dev.db which is also what the .env file stored as the database url in an earlier step
   2. Creating the Type, Cadence, Transaction, and Plan tables based on their corresponding Prisma model definitions in prisma/schema.prisma
   3. Creating a new SQL migration file for this migration
   4. Runs this SQL migration against the database

   After running the above command, your database is now in sync with your Prisma Schema. You can locate the migration file that was created and ran in /prisma/migrations/{{migration_id}}_init/migration.sql. The content of the migration file is the raw SQL used to make the database changes.

   ---

   More Information on Migrations

   If you were to make changes to the schema later down the road, you would need to create and execute new migrations to make the necessary changes to the database. Migrations are created using the command: npx prisma migrate dev.

   Lastly, if you wanted to only create the migration file without executing it, you would run npx prisma migrate dev --create-only.

   The migrations that are created after the initial migration are found under /prisma/migrations/{{migration_id}}_{{migration_name}}/migration.sql

5. Challenge

   Handle Next.js Hot Reloading Issue in Development Mode

   Handle Next.js Hot Reloading Issue in Development Mode

   In this step, you will be making changes to app/db.ts. The existing db.ts file assigns the variable prisma to be a new PrismaClient. This allows you to use the database throughout the project and create queries using the PrismaClient. However, Next.js development mode does hot reloading which can cause issues with Prisma because it can attempt to constantly create new connections with the Prisma Client.

   You will implement a solution Prisma provides to get around this issue in Next.js development mode.

   ---

   Activity

   Implement the following changes to app/db.ts to set a global prisma instance to new PrismaClient() if it has not previously been defined in development mode.

   1. Wrap line 5 in an if statement for when process.env.NODE_ENV === "production"
   2. Implement the else statement (when you are in development mode) as follows
      1. If global.prisma is null (meaning it has not been defined), assign global.prisma to a new PrismaClient()
      2. Else, global.prisma has previously been defined so assign prisma to global.prisma

   Example db.ts File Contents

   import { PrismaClient } from "@prisma/client";
   
   let prisma: PrismaClient;
   
   if (process.env.NODE_ENV === "production") {
     prisma = new PrismaClient();
   } else {
     if (!global.prisma) {
       global.prisma = new PrismaClient();
     }
     prisma = global.prisma;
   }
   
   export default prisma;
   

   This code will only make a connection with the PrismaClient once despite Next.js hot reloading. This is because now the prisma variable acts as a singleton.

6. Challenge

   Seed Application Data

   Seed Application Data

   Now that your database is up and running, you can seed the database with data relevant to the application. Seeding data can be helpful when you are developing new applications, so you do not have to create a bunch of data by hand. In this step, you will finish a script in seedDatabase/seed.js to seed the database with relevant data and add a script to package.json to run the aforementioned seeding script.

   ---

   Activity 1

   Follow the steps below to finish implementing the script in seedDatabase/seed.js that will be used to seed the database.

   1. Use the prisma variable (which is already initialized to a Prisma Client instance at the top of seed.js) to perform database queries to delete all the data in the Transaction, Plan, Cadence, and Type tables in that order.
      • Hint: You can perform CRUD operations with your generated Prisma Client API. For example, you can call deleteMany() on your transaction model to delete all transaction records. It is important to remember that all Prisma queries are asynchronous calls that need to be awaited.
      • Example of how to delete all transaction records below

      	await prisma.transaction.deleteMany();
      

      • The order in which you delete records from the tables matters due to Foreign Key restraints since you cannot delete a record if another table is using that record's Primary Key id as a Foreign Key.
   2. Use the prisma variable to add the data from seedDatabase/data.js to its corresponding table in the following order: Type, Cadence, Plan, Transaction.
      • Hint: You can use createMany() to bulk insert data into a table. Under the hood, a createMany() call creates a single INSERT INTO statement which is more efficient that a separate INSERT statement for each new row.
      • Example below of to add all transaction data to the transaction table below:

      	await prisma.transaction.createMany({
      		data: transactions,
      });
      

      • Like above, the order in which data is added to the tables matters as Foreign Keys need to exist as Primary Key ids in their personal tables before they can be added as a Foreign Key to a relating table.

   Example seed.js File Contents

   import { PrismaClient } from "@prisma/client";
   import { cadences, plans, transactions, types } from "./data.js";
   const prisma = new PrismaClient();
   
   const load = async () => {
     try {
       await prisma.transaction.deleteMany();
       console.log("Deleted records in transaction table");
       await prisma.plan.deleteMany();
       console.log("Deleted records in plan table");
       await prisma.cadence.deleteMany();
       console.log("Deleted records in cadence table");
       await prisma.type.deleteMany();
       console.log("Deleted records in types table");
   
       await prisma.type.createMany({
         data: types,
       });
       console.log("Seeded type table");
       await prisma.cadence.createMany({
         data: cadences,
       });
       console.log("Seeded cadence table");
       await prisma.plan.createMany({
         data: plans,
       });
       console.log("Seeded plan table");
       await prisma.transaction.createMany({
         data: transactions,
       });
       console.log("Seeded transaction table");
     } catch (e) {
       console.error(e);
       process.exit(1);
     } finally {
       await prisma.$disconnect();
     }
   };
   
   load();
   

   ---

   Activity 2

   Now that you have a script that can be ran to seed the database, you need to add to the package.json scripts the command to run your seedDatabase/seed.js script.

   1. In your package.json file, add another script to the scripts section that reads "seed": "node seedDatabase/seed.js"
      • Do not forgot to add a comma where is necessary to not corrupt the JSON file
      • With this line, you can run npm run seed and it will run the seedDatabase/seed.js script and seed your database

   At this time run npm run seed to seed your database with the appropriate records.

   More Information

   ---

   Seeding is possible above through a prisma property with a value of { "seed": "node seedDatabase/seed.js" } on the same level as scripts in package.json. If you were to include this in the package.json, the seeding script will run whenever a migration is created and ran. The solution has both implementations.

7. Challenge

   Prisma findMany Read Query for Reading Data

   Prisma findMany Query for Reading Data

   In this step, you will finish implementing the methods that read and return several records at once from the database in app/lib/actions.js.

   Currently, data is retrieved for the application by reading from JSON files. You will change this and use Prisma to get data directly from the database.

   For all Prisma queries, Prisma will convert these transactions into SQL statments and run them on the server to perform INSERT, SELECT, DELETE, and UPDATE operations.

   ---

   Activity

   Import Prisma From db.ts

   1. In app/lib/actions.js, import prisma from db.ts
      • Remember prisma is the global instance of a Prisma Client connection import prisma from "../db";

   Implement getCadences Next.js Server Action

   1. Remove the current assignment value to the cadences variable
   2. Use the findMany() Prisma command on the cadence table to return all records in the table
      • Hint: All Prisma queries happen asynchronously, so remember to await the return value

   Example getCadences Method

   export async function getCadences() {
     let cadences = await prisma.cadence.findMany();
   
     cadences = JSON.parse(JSON.stringify(cadences));
     return cadences;
   }
   

   Implement getTypes Next.js Server Action

   1. Remove the current assignment value to the types variable
   2. Use the findMany() Prisma command on the type table to return all records in the table

   Example getTypes Method

   export async function getTypes() {
     let types = await prisma.type.findMany();
   
     types = JSON.parse(JSON.stringify(types));
     return types;
   }
   

   Implement getPlans Next.js Server Action

   1. Remove the current assignment value to the plans variable
   2. Use the findMany() Prisma command on the plan table to return all records in the table of a specific planType
      • Hint: findMany accepts an object as a parameter. In this object you can specify options to filter the search and order, select, include, omit, and skip results. You want to use the where option to filter the search by related record field values (because planType exists on the related type record).
      • Below is an example of how to filter by related record field values where the query returns users that have a published post

      	const users = await prisma.user.findMany({
      		where: {
      			posts: {
      				published: true,
      			},
      		},
      	});
      

   3. Use the include option in the findMany parameter object to eagerly load the cadence, type, and transactions relations on the return object
      • This is necessary because the frontend has pages that rely on this nested data - see app/(ui)/components/planTable.jsx line 25 for example
      • Below is an example of how to include relation data on the return value of findMany. The query below returns all users and their post data.

      	const users = await prisma.user.findMany({
      		include: {
      			posts: true,
      		},
      	});
      

   4. Order the returned plans list from findMany by cadence name in ascending order first and plan name in ascending order second with the orderBy option
      • Example of how orderBy is used is below where profile is a relation to user

      	const users = await prisma.user.findMany({
      		orderBy: [
      			{ name: "asc" },
      			{ profile: { nickname: "desc" } },
      		],
      	});
      

   Example getPlans Method

   export async function getPlans(planType) {
     let plans = let plans = await prisma.plan.findMany({
       where: {
         type: {
           name: planType,
         },
       },
       include: {
         type: true,
         cadence: true,
         transactions: true,
       },
       orderBy: [
         {
           cadence: {
             name: "asc",
           },
         },
         {
           name: "asc",
         },
       ],
     });
   
     plans = JSON.parse(JSON.stringify(plans));
     return plans;
   }
   

   Implement getTransactions Next.js Server Action

   1. Remove the current assignment value to the transactions variable
   2. Use the findMany() Prisma command on the transaction table to return all records in the table of a specific transactionType if it is defined otherwise return all transactions.
      • Hint: As in the getPlans method, use the where option that can be passed to findMany to compare transaction's plan's type's name to transactionType.
      • Hint You can destruct an object and use a ternary to see if transactionType is defined and create an object with name mapping to transactionType if it is and an empty object if it is not.
   3. Use the include option in the findMany parameter object to eagerly load the plan and plan's type relations on the return object
      • This is necessary because the frontend has pages that rely on this nested data - see app/(ui)/components/transactionTable.jsx line 32 for example
   4. Order the returned plans list from findMany by transaction date in descending order first and plan type name in ascending order second with the orderBy option

   Example getTransactions Method

   export async function getTransactions(transactionType) {
     let transactions = await prisma.transaction.findMany({
       where: {
         plan: {
           type: {
             ...(transactionType ? { name: transactionType } : {}),
           },
         },
       },
       include: {
         plan: {
           include: { type: true },
         },
       },
       orderBy: [
         {
           date: "desc",
         },
         {
           plan: {
             type: {
               name: "asc",
             },
           },
         },
       ],
     });
   
     transactions = JSON.parse(JSON.stringify(transactions));
     return transactions;
   }
   

   Implement getPlanCategories Next.js Server Action

   1. Remove the current assignment values to the categories variable
   2. Use the findMany() Prisma command on the plan table to return all records in the table of a specific planType
      • Hint: This will look exactly like the query in getPlans
   3. Use the select option in the findMany parameter object to specify that only name and id are properties to include on the returned object
      • Below is an example of how to select data in a Prisma query

      	const users = await prisma.user.findMany({
      		select: {
      			email: true,
      			posts: {
      				comments: true,
      			},
      		},
      	});
      

   4. Order the returned plans list from findMany by plan name in ascending order
      • Hint: Since there is only one order by criteria, the value of the orderBy option can be an object instead of an array of objects.

   Example getPlanCategories Method

   * Below is an example of how to include relation data on the return value of `findMany`. The query below returns all `users` and their `post` data.
   	```js
   	const users = await prisma.user.findMany({
   		include: {
   			posts: true,
   		},
   	});
   

8. Challenge

   Prisma findUnique Query For Reading Data

   Prisma findUnique Query for Reading Data

   Now that the application is reading several records at once from the database, you will implement more methods in app/lib/actions.js to get a single record from the database at a time.

   ---

   Prisma findUnique Read Query

   The findUnique query can only be performed with a where option on properties that are unique.

   If you were filtering a query result on a property that was not unqiue, you would use the findFirst query.

   ---

   Activity - Implement Methods

   Implement getPlan Next.js Server Action

   1. In the getPlan method within app/lib/actions.js remove the plans variable
   2. Remove the current assignment value to the plan variable
   3. Use the findUnique() Prisma command on the plan table to return the record with id
      • Hint: findUnique has the same options available as findMany - so this is possible with a where option
   4. Use the include option to return plan's cadence, type, and transactions
   5. Also include transactions's plan and plan's type

   Example getPlan Method

   export async function getPlan(id) {
     let plan = await prisma.plan.findUnique({
       where: {
         id: id,
       },
       include: {
         cadence: true,
         type: true,
         transactions: {
           include: {
             plan: {
               include: {
                 type: true,
               },
             },
           },
         },
       },
     });
     plan = JSON.parse(JSON.stringify(plan));
     return plan;
   }
   

   Implement getTransaction Next.js Server Action

   1. Remove the transactions variable
   2. Remove the current assignment value to the transaction variable
   3. Use the findUnique() Prisma command on the transaction table to return the record with id
   4. Use the include option to return transaction's plan and plan's type objects

   Example getTransaction Method

   export async function getTransaction(id) {
     let transaction = await prisma.transaction.findUnique({
       where: {
         id: id,
       },
       include: {
         plan: {
           include: {
             type: true,
           },
         },
       },
     });
   
     transaction = JSON.parse(JSON.stringify(transaction));
     return transaction;
   }
   

   Implement getTypeByName Next.js Server Action

   1. Remove the types variable
   2. Remove the current assignment value to the type variable
   3. Use the findUnique() Prisma command on the type table to return the record where the name property matches typeName

   Example getTypeByName Method

   export async function getTypeByName(typeName) {
     const type = await prisma.type.findUnique({
       where: {
         name: typeName,
       },
     });
     return type;
   }
   

   Implement getCadenceByName Next.js Server Action

   1. Remove the cadences variable
   2. Remove the current assignment value to the cadence variable
   3. Use the findUnique() Prisma command on the cadence table to return the record where the name property matches cadenceName

   Example getCadenceByName Method

   export async function getCadenceByName(cadenceName) {
     const cadence = await prisma.cadence.findUnique({
       where: {
         name: cadenceName,
       },
     });
     return cadence;
   }
   

   Implement getPlanByTypeAndPlanName Next.js Server Action

   1. Remove the plans variable
   2. Remove the current assignment value to the plan variable
   3. Use the findUnique() Prisma command on the plan table to return the record where the name property matches name and typeId matches typeId
      • Hint: Since typeId and name are a compound unique property in your schema, you can use the findUnique query with the typeId_name property within the where option

   Example getPlanByTypeAndPlanName Method

   async function getPlanByTypeAndPlanName(planType, name) {
     const typeId = await getTypeByName(planType);
   
     const plan = await prisma.plan.findUnique({
       where: {
         typeId_name: {
           name: name,
           typeId: typeId,
         },
       },
     });
     return plan;
   }
   

   ---

   Activity - Visit the UI

   If you visit the Web Browser when the application is running, you will see that data is displaying on all of the pages (including the edit pages). You will also notice that the order the data is displayed in matches the order you specified in the previous step.

   This is a great way to verify that your Prisma read queries are working.

9. Challenge

   Prisma Create Query

   Prisma Create Query

   At this point, the application does not persist any data when a user attempts to create, update, or delete data in the UI. You can try this out yourself by attempting to create, update, or delete a transaction through the UI. You will see that the the transaction change is not persisted.

   In this step, you will finish implementing some of the Next.js server actions in app/lib/actions.js that are responsible for persisting data creation.

   ---

   Activity - Implement Methods

   Implement createPlan Next.js Server Action

   1. In the createPLan method within app/lib/actions.js remove the early return in the try block
   2. Assign the Prisma create command on the plan table return value to a variable called plan
      • Hint: The create model query accepts data as an option. data is a Javascript object that represents the new record. A data object has already been created for you at the top of createPlan. This data object is assigned to the variable plan. Be sure to pass plan as the data option within the create query

   Example createPlan Method

   export async function createPlan(planType, formData) {
     const type = planType ? planType : formData.get("type");
     let plan = {
       name: formData.get("name").toLowerCase(),
       budgetAmount: new Prisma.Decimal(formData.get("amount")),
       cadenceId: await getCadenceByName(formData.get("cadence")).id,
       typeId: await getTypeByName(type).id,
       description: formData.get("description"),
     };
   
     try {
   		// this is the line you will add
       plan = await prisma.plan.create({ data: plan });
       console.log("New plan successfully added!");
     } catch (error) {
       console.log(`Error adding new plan: ${error}`);
       throw new Error("Failed to add plan");
     }
   
     revalidatePlanPaths(plan.id);
     plan = JSON.parse(JSON.stringify(plan));
     return plan;
   }
   

   Implement createTransaction Next.js Server Action

   1. Remove the early return in the try block
   2. Assign the Prisma create command on the transaction table return value to a variable called transaction
      • Hint: As before, the data object you should use is given to you above and assigned to the transaction variable

   Example createTransaction Method

   
   export async function createTransaction(transactionType, formData) {
     const type = transactionType ? transactionType : formData.get("type");
     let transaction = {
       date: new Date(formData.get("date").replace("-", "/")),
       amount: new Prisma.Decimal(formData.get("amount")),
       planId: await getPlanByTypeAndPlanName(type, formData.get("category")),
       notes: formData.get("notes"),
     };
   
     try {
       transaction = await prisma.transaction.create({ data: transaction });
       console.log("New transaction successfully added!");
     } catch (error) {
       console.log(`Error adding new transaction: ${error}`);
       throw new Error("Failed to add transaction");
     }
   
     revalidateTransactionPaths(transactionType, transaction.id);
     transaction = JSON.parse(JSON.stringify(transaction));
     return transaction;
   }
   

   ---

   Activity - Visit the UI

   Visit the application in Web Browser while it is running. Create an expense transaction, income transaction, and plan to verify the queries above work, the data is persisted, and the data is added to the corresponding table in the UI.

10. Challenge

    Prisma Update Query

    Prisma Update Query

    In the previous step, you finished implementing the create methods. Now, you will be responsible for implementing the update methods using Prisma's update query in app/lib/actions.js.

    ---

    Activity - Implement Methods

    Implement updateTransaction Next.js Server Action

    1. In the updateTransaction method within app/lib/actions.js remove the early return in the try block
    2. Below the TODO comment in the try block, use Prisma's update query on the transaction table to update the transaction record that has the correct id. Save the return value from the update query to the already existing transaction variable.
       • Hint: The update model query accepts data and where as options. data is what represents the updated record properties as an object. A data object has already been created for you above the TODO comment. This data object is assigned to the variable newTransactionData. Be sure to pass newTransactionData as the data option within the update query
       • Hint: As with the findUnique queries you wrote in a previous step, use the where option with id to update the correct transaction record

    Example updateTransaction Method

    export async function updateTransaction(id, formData) {
      const inputData = {
        date: new Date(formData.get("date").replace("-", "/")),
        amount: formData.get("amount"),
        category: formData.get("category"),
        notes: formData.get("notes"),
      };
      let transaction;
    
      try {
        transaction = await getTransaction(id);
    
        let planId = transaction.plan.id;
        if (inputData.category != transaction.plan.name) {
          const plan = await getPlanByTypeAndPlanName(
            transaction.plan.type.name,
            inputData.category
          );
          planId = plan.id;
        }
        const newTransactionData = {
          date: inputData.date,
          amount: inputData.amount,
          planId: planId,
          notes: inputData.notes,
        };
    
    		// You will add this line
        transaction = await prisma.transaction.update({
          where: {
            id: id,
          },
          data: newTransactionData,
        });
    
        console.log("Transaction successfully updated!");
      } catch (error) {
        console.log(`Error saving transaction item ${error}`);
        throw new Error("Failed to update transaction item");
      }
    
      revalidateTransactionPaths(transaction.type, id);
      transaction = JSON.parse(JSON.stringify(transaction));
      return transaction;
    }
    

    Implement updatePlan Next.js Server Action

    1. Remove the early return in the try block
    2. Below the TODO comment in the try block, use Prisma's update query on the plan table to update the plan record that has the correct id. Save the return value from the update query to the already existing plan variable.
       • Hint: As above, use the where and data update options. A data object has already been created for you above the TODO comment and is stored in the newPlanData variable

    Example updatePlan Method

    
    export async function updatePlan(id, formData) {
      const inputData = {
        name: formData.get("name").toLowerCase(),
        budgetAmount: formData.get("amount"),
        description: formData.get("description"),
        cadence: formData.get("cadence").toLowerCase(),
        type: formData.get("type").toLowerCase(),
      };
    
      try {
        let plan = getPlan(id);
    
        let cadenceId = plan.cadence.id;
        let typeId = plan.type.id;
    
        if (inputData.cadence != plan.cadence.name) {
          const cadence = await getCadenceByName(plan.cadence.name);
          cadenceId = cadence.id;
        }
        if (inputData.type != plan.type.name) {
          const type = await getTypeByName(plan.type.name);
          typeId = type.id;
        }
    
        const newPlanData = {
          name: inputData.name,
          budgetAmount: inputData.budgetAmount,
          description: inputData.description,
          cadenceId: cadenceId,
          typeId: typeId,
        };
    
        plan = await prisma.plan.update({
          where: {
            id: id,
          },
          data: newPlanData,
        });
    
        console.log("Plan successfully updated!");
      } catch (error) {
        console.log(`Error saving plan item ${error}`);
        throw new Error("Failed to update plan item");
      }
      revalidatePlanPaths(id);
      plan = JSON.parse(JSON.stringify(plan));
      return plan;
    }
    

    ---

    Activity - Visit the UI

    Update an expense transaction, income transaction, and plan in the UI to verify that the update queries are working correctly. When you update any item, the change should be reflected in the table (which is a representation of the current data stored in the database).

11. Challenge

    Prisma Delete Query

    Prisma Delete Query

    Lastly, you need to finish implementing the server actions responsible for persisting the deletion of data in app/lib/actions.js.

    ---

    Activity - Implement Methods

    Implement deleteTransaction Next.js Server Action

    1. In the deleteTransaction method within app/lib/actions.js remove the early return in the try block
    2. Below the TODO comment in the try block, use Prisma's delete query on the transaction table to delete the transaction record with the id given and assign the return value to the variable deleteTransaction that has already been defined for you at the top of the method
       • Hint: The delete Prisma query accepts where as an option as other queries have - be sure to use the where option on id
    3. Include plan and plan's type on the return value from the delete query using the include option
       • Hint: The delete query returns the record that was deleted and this will include the transaction's relational data on the return value
    4. Assign the return value from the delete query to the deletedTransaction variable that has already been defined for you at the top of the method

    Example deleteTransaction Method

    export async function deleteTransaction(id) {
      let deletedTransaction;
      try {
        deletedTransaction = await prisma.transaction.delete({
          where: {
            id: id,
          },
          include: {
            plan: {
              include: {
                type: true,
              },
            },
          },
        });
        console.log(`Transaction with id ${id} successfully deleted`);
      } catch (error) {
        console.log(`Error deleting transaction: ${error}`);
        throw new Error("Failed to delete transaction");
      }
      revalidateTransactionPaths(deletedTransaction.plan.type.name, id);
    }
    

    Implement deletePlan Next.js Server Action

    1. Remove the early return in the try block
    2. Below the TODO comment in the try block, use Prisma's delete query on the plan table to delete the plan record with the id given
    3. Assign the return value to the variable deletedPlan that has already been defined for you at the top of the method

    Example deleteTransaction Method

    export async function deletePlan(id) {
      let deletedPlan;
      try {
        deletedPlan = await prisma.plan.delete({
          where: {
            id: id,
          },
        });
        console.log(`Plan with id ${id} successfully deleted`);
      } catch (error) {
        console.log(`Error deleting plan: ${error}`);
        throw new Error("Failed to delete plan");
      }
      revalidatePlanPaths(id);
    }
    

    ---

    Activity - Visit the UI

    Delete an expense transaction, income transaction, and plan in the UI to verify that the delete queries are working correctly. When you delete any item, the item should be removed from the UI's corresponding table to reflect the record has been removed from the database.

12. Challenge

    Conclusion

    Conclusion

    In this lab, you became familiar with Prisma. You can now confidently setup a Prisma database, create a Prisma Schema, seed the database, perform migrations on the database, and use the Prisma Client to perform CRUD Prisma queries against the datavse. The finance application used in this lab now performs CRUD operations against a database which is a huge feature.