Building a CRUD App with Node.js, PostgreSQL, and Prisma

Introduction Ever feel like working with databases & SQL queries is a bit of a headache? You are not alone! But now, with Prisma, we don't have to deal with that headache. Prisma is a powerful ORM for TypeScript projects that takes care of all of these. In this article, we're gonna explore how to use Prisma to build a CRUD App with Node JS and PostgreSQL. So, without further delay, let's begin! What is Prisma? Before moving forward, let's understand what Prisma is. So, Prisma is a next-generation ORM (Object Relational Mapping) tool that provides several components for database management and access. It is a server-side library that helps developers read and write data to the database in an intuitive, efficient, and safe way. In contrast to classical ORMs, with TypeScript, we don’t manually write models; instead, we define a schema, and Prisma generates everything needed, complete with TypeScript types. Steps: Project Setup: To start off, we'll initialize our Node project with: npm init Enter fullscreen mode. Exit fullscreen mode Next, we will install the required dependencies: npm i --save-dev prisma typescript ts-node @types/node nodemon Enter fullscreen mode. Exit fullscreen mode For TypeScript configuration, we'll create a tsconfig.json file and add the following settings: { "compilerOptions": { "sourceMap": true, "outDir": "dist", "strict": true, "lib": ["esnext"], "esModuleInterop": true } } Enter fullscreen mode. Exit fullscreen mode Now, We'll Initialize our Prisma! For that, execute the command: npx prisma init Enter fullscreen mode. Exit fullscreen mode We can also specify the database during Prisma initialization. For this Project, We are using PostgreSQL so the Command will be: npx prisma init --datasource-provider postgresql Enter fullscreen mode. Exit fullscreen mode These steps will generate essential configuration and setup files for our project. Tip: Use The Prisma Extension for code highlighting! Now that we've set up our project environment, let's move on to defining our Prisma models. Prisma Model Setup: Here, we'll define the User Model for our application in the schema.prisma file. model User { id String @id @default(uuid()) email String @unique name String? password String createdAt DateTime @default(now()) updatedAt DateTime @updatedAt } Enter fullscreen mode. Exit fullscreen mode. So, we have created our User model, but our database is not yet connected. Before utilizing these models to interact with our database, we need to establish a proper database connection. Prisma Migration: Till now, Prisma and our database are completely separate! They are able to interact with each other, but defining something in prisma won't do anything in the database! Suppose We made changes to our schema. To reflect those changes in our DB, we have to migrate our new schema! For that, we have to use a command: npx prisma migrate dev --name init Enter fullscreen mode. Exit fullscreen mode Note: Here the --name is optional Before Migration, Don't forget to change the DATABASE_URL to according to your DB. So This command will do something like this: After that, It will create a migration file. This migration file will communicate with our Postgres database. This file contains nothing but the SQL commands of our schema. It looks something like this: Prisma client: The Client is essentially all of the code for interacting with our database. Now, if we see the console, we'll find something interesting! So, As we can see here, after the migration, it has created a brand new Prisma client and pushed that into the node modules! That's what it does every time we make a migration or changes to our database; it creates a new Prisma client. But We don't have the client yet as we haven't installed the client library! To install that, run this: npm i @prisma/client Enter fullscreen mode. Exit fullscreen mode By doing this migration, we have already generated our Prisma client, but if we want to generate/regenerate the Prisma client manually, We have to use the following command: npx prisma generate Enter fullscreen mode. Exit fullscreen mode Now, to use the Prisma Client, We have to add the following code: import { PrismaClient } from '@prisma/client' const prisma = new PrismaClient() Enter fullscreen mode. Exit fullscreen mode With this, we can perform our desired operations (like Create, Read, Update, & Delete). CRUD Operations with Prisma Client: Now, we'll perform CRUD operations with the Prisma client that we created in the previous section. For that, We'll create a new file script.ts. And start Implementing CRUD! import { PrismaClient } from "@prisma/client"; const prisma = new PrismaClient(); async function main() { // ... We will write our Prisma Client queries here } main() .catch((e) => { throw e; }) .finally(async () => { await prisma.$disconnect(); }); Enter fullscreen mode. Exit fullscreen mode. Here, We have created an async function, as almost all the operations in Prisma are asynchronous! We are also catching our errors with the .catch method, and in the end, we are disconnecting from the Prisma database. Now, in side main, we'll write our functions! CREATE: Now, Let's Create our first user. async function main() { const user = await prisma.user.create({ data: { email: "arindammajumder2020@gmail.com", name: "Arindam Majumder", password: "12345678", }, }); console.log(user); } Enter fullscreen mode. Exit fullscreen mode Here We are creating our first user using the prisma.user.create method. Before that We will add one script to the package.json file in order to run the code. // package.json "scripts": { "test": "echo \"Error: no test specified\" && exit 1", "devStart": "nodemon script.ts" }, Let's start the app and see if it works or not! Great! We have created our first user! And if We want to add multiple users at a time then the code will be like this: const usersToCreate = [ { email: "john.doe@example.com", name: "John Doe", password: "password123", }, { email: "jane.smith@example.com", name: "Jane Smith", password: "pass@123", }, { email: "robert.brown@example.com", name: "Robert Brown", password: "securepwd", }, // Add more user objects as needed ]; async function main() { // ...other functions const createdUsers = await prisma.user.createMany({ data: usersToCreate, skipDuplicates: true, // Optional: Skip duplicate entries if any }); console.log(createdUsers); } Enter fullscreen mode. Exit fullscreen mode This will create multiple users. 💡Note: It's better to add skipDuplicates: true to ignore the duplicate values READ: Till now, We have created many users. So, It's time to check those data! To view all records, add the following code to the main function: const allUsers = await prisma.user.findMany(); console.log(allUsers); Enter fullscreen mode. Exit fullscreen mode Let's Run this and see the results: Great! So we got all the records! We can also retrieve a single data with a unique identifier! Suppose We want to get the user Arindam, We'll use the id and retrive Arindam's data! Here's the code for that: const userById = await prisma.user.findUnique({ where: { id: "84e37ff7-0f7e-41c6-9cad-d35ceb002991", }, }); console.log(userById); Enter fullscreen mode. Exit fullscreen mode And The Output is as expected : UPDATE: This operation is quite similar to the read operations. Previously We are only reading the data, here we'll read and manipulate the data using the queries. Suppose We want to change the Name to "Arindam," the code will be: const updatedUser = await prisma.user.update({ where: { id: "84e37ff7-0f7e-41c6-9cad-d35ceb002991", }, data: { name: "Arindam", }, }); Enter fullscreen mode. Exit fullscreen mode And See the Console! The name has been changed! DELETE: Okay, Now we will delete a user using its ID! Here's the code for that: const deletedUser = await prisma.user.delete({ where: { id: "84e37ff7-0f7e-41c6-9cad-d35ceb002991", }, }); Enter fullscreen mode. Exit fullscreen mode This will Delete the user named Arindam! To verify that, we check allUsers again! And there's no user named Arindam! We have successfully deleted the user! With that, we have completed our basic CRUD operations in our Node+Postgres app using Prisma. Pretty Simple, Right? For Reference, You can check the Code Here: Conclusion If you found this blog post helpful, please consider sharing it with others who might benefit from it. You can also follow me for more content on Javascript, React, and other web Development topics. For Paid collaboration, mail me at: arindammajumder2020@gmail.com Connect with me on Twitter, LinkedIn, Youtube, and GitHub. Thank you for Reading :) Introduction Ever feel like working with databases & SQL queries is a bit of a headache? You are not alone! But now, with Prisma, we don't have to deal with that headache. Prisma is a powerful ORM for TypeScript projects that takes care of all of these. In this article, we're gonna explore how to use Prisma to build a CRUD App with Node JS and PostgreSQL. So, without further delay, let's begin! What is Prisma? Before moving forward, let's understand what Prisma is. So, Prisma is a next-generation ORM (Object Relational Mapping) tool that provides several components for database management and access. It is a server-side library that helps developers read and write data to the database in an intuitive, efficient, and safe way. In contrast to classical ORMs, with TypeScript, we don’t manually write models; instead, we define a schema, and Prisma generates everything needed, complete with TypeScript types. Steps: Project Setup: To start off, we'll initialize our Node project with: npm init npm init Enter fullscreen mode. Exit fullscreen mode Next, we will install the required dependencies: npm i --save-dev prisma typescript ts-node @types/node nodemon npm i --save-dev prisma typescript ts-node @types/node nodemon Enter fullscreen mode. Exit fullscreen mode For TypeScript configuration, we'll create a tsconfig.json file and add the following settings: tsconfig.json { "compilerOptions": { "sourceMap": true, "outDir": "dist", "strict": true, "lib": ["esnext"], "esModuleInterop": true } } { "compilerOptions": { "sourceMap": true, "outDir": "dist", "strict": true, "lib": ["esnext"], "esModuleInterop": true } } Enter fullscreen mode. Exit fullscreen mode Now, We'll Initialize our Prisma! For that, execute the command: npx prisma init npx prisma init Enter fullscreen mode. Exit fullscreen mode We can also specify the database during Prisma initialization. For this Project, We are using PostgreSQL so the Command will be: npx prisma init --datasource-provider postgresql npx prisma init --datasource-provider postgresql Enter fullscreen mode. Exit fullscreen mode These steps will generate essential configuration and setup files for our project. Tip: Use The Prisma Extension for code highlighting! Tip: Use The Prisma Extension for code highlighting! Now that we've set up our project environment, let's move on to defining our Prisma models. Prisma Model Setup: Here, we'll define the User Model for our application in the schema.prisma file. schema.prisma model User { id String @id @default(uuid()) email String @unique name String? password String createdAt DateTime @default(now()) updatedAt DateTime @updatedAt } model User { id String @id @default(uuid()) email String @unique name String? password String createdAt DateTime @default(now()) updatedAt DateTime @updatedAt } Enter fullscreen mode. Exit fullscreen mode. So, we have created our User model, but our database is not yet connected. Before utilizing these models to interact with our database, we need to establish a proper database connection. Prisma Migration: Till now, Prisma and our database are completely separate! They are able to interact with each other, but defining something in prisma won't do anything in the database! Suppose We made changes to our schema. To reflect those changes in our DB, we have to migrate our new schema! For that, we have to use a command: npx prisma migrate dev --name init npx prisma migrate dev --name init Enter fullscreen mode. Exit fullscreen mode Note: Here the --name is optional Note: Here the --name is optional Before Migration, Don't forget to change the DATABASE_URL to according to your DB. DATABASE_URL So This command will do something like this: After that, It will create a migration file. This migration file will communicate with our Postgres database. This file contains nothing but the SQL commands of our schema. It looks something like this: Prisma client: The Client is essentially all of the code for interacting with our database. Now, if we see the console, we'll find something interesting! So, As we can see here, after the migration, it has created a brand new Prisma client and pushed that into the node modules! That's what it does every time we make a migration or changes to our database; it creates a new Prisma client. But We don't have the client yet as we haven't installed the client library! To install that, run this: npm i @prisma/client npm i @prisma/client Enter fullscreen mode. Exit fullscreen mode By doing this migration, we have already generated our Prisma client, but if we want to generate/regenerate the Prisma client manually, We have to use the following command: npx prisma generate npx prisma generate Enter fullscreen mode. Exit fullscreen mode Now, to use the Prisma Client, We have to add the following code: import { PrismaClient } from '@prisma/client' const prisma = new PrismaClient() import { PrismaClient } from '@prisma/client' const prisma = new PrismaClient() Enter fullscreen mode. Exit fullscreen mode With this, we can perform our desired operations (like Create, Read, Update, & Delete). CRUD Operations with Prisma Client: Now, we'll perform CRUD operations with the Prisma client that we created in the previous section. For that, We'll create a new file script.ts. And start Implementing CRUD! import { PrismaClient } from "@prisma/client"; const prisma = new PrismaClient(); async function main() { // ... We will write our Prisma Client queries here } main() .catch((e) => { throw e; }) .finally(async () => { await prisma.$disconnect(); }); import { PrismaClient } from "@prisma/client"; const prisma = new PrismaClient(); async function main() { // ... We will write our Prisma Client queries here } main() .catch((e) => { throw e; }) .finally(async () => { await prisma.$disconnect(); }); Enter fullscreen mode. Exit fullscreen mode. Here, We have created an async function, as almost all the operations in Prisma are asynchronous! We are also catching our errors with the .catch method, and in the end, we are disconnecting from the Prisma database. Now, in side main, we'll write our functions! CREATE: CREATE: Now, Let's Create our first user. async function main() { const user = await prisma.user.create({ data: { email: "arindammajumder2020@gmail.com", name: "Arindam Majumder", password: "12345678", }, }); console.log(user); } async function main() { const user = await prisma.user.create({ data: { email: "arindammajumder2020@gmail.com", name: "Arindam Majumder", password: "12345678", }, }); console.log(user); } Enter fullscreen mode. Exit fullscreen mode Here We are creating our first user using the prisma.user.create method. Before that We will add one script to the package.json file in order to run the code. // package.json "scripts": { "test": "echo \"Error: no test specified\" && exit 1", "devStart": "nodemon script.ts" }, Before that We will add one script to the package.json file in order to run the code. // package.json "scripts": { "test": "echo \"Error: no test specified\" && exit 1", "devStart": "nodemon script.ts" }, // package.json "scripts": { "test": "echo \"Error: no test specified\" && exit 1", "devStart": "nodemon script.ts" }, Let's start the app and see if it works or not! Great! We have created our first user! And if We want to add multiple users at a time then the code will be like this: const usersToCreate = [ { email: "john.doe@example.com", name: "John Doe", password: "password123", }, { email: "jane.smith@example.com", name: "Jane Smith", password: "pass@123", }, { email: "robert.brown@example.com", name: "Robert Brown", password: "securepwd", }, // Add more user objects as needed ]; async function main() { // ...other functions const createdUsers = await prisma.user.createMany({ data: usersToCreate, skipDuplicates: true, // Optional: Skip duplicate entries if any }); console.log(createdUsers); } const usersToCreate = [ { email: "john.doe@example.com", name: "John Doe", password: "password123", }, { email: "jane.smith@example.com", name: "Jane Smith", password: "pass@123", }, { email: "robert.brown@example.com", name: "Robert Brown", password: "securepwd", }, // Add more user objects as needed ]; async function main() { // ...other functions const createdUsers = await prisma.user.createMany({ data: usersToCreate, skipDuplicates: true, // Optional: Skip duplicate entries if any }); console.log(createdUsers); } Enter fullscreen mode. Exit fullscreen mode This will create multiple users. 💡Note: It's better to add skipDuplicates: true to ignore the duplicate values skipDuplicates: true READ: READ: Till now, We have created many users. So, It's time to check those data! To view all records, add the following code to the main function: const allUsers = await prisma.user.findMany(); console.log(allUsers); const allUsers = await prisma.user.findMany(); console.log(allUsers); Enter fullscreen mode. Exit fullscreen mode Let's Run this and see the results: Great! So we got all the records! We can also retrieve a single data with a unique identifier! Suppose We want to get the user Arindam, We'll use the id and retrive Arindam's data! Here's the code for that: const userById = await prisma.user.findUnique({ where: { id: "84e37ff7-0f7e-41c6-9cad-d35ceb002991", }, }); console.log(userById); const userById = await prisma.user.findUnique({ where: { id: "84e37ff7-0f7e-41c6-9cad-d35ceb002991", }, }); console.log(userById); Enter fullscreen mode. Exit fullscreen mode And The Output is as expected : UPDATE: UPDATE: This operation is quite similar to the read operations. Previously We are only reading the data, here we'll read and manipulate the data using the queries. Suppose We want to change the Name to "Arindam," the code will be: const updatedUser = await prisma.user.update({ where: { id: "84e37ff7-0f7e-41c6-9cad-d35ceb002991", }, data: { name: "Arindam", }, }); const updatedUser = await prisma.user.update({ where: { id: "84e37ff7-0f7e-41c6-9cad-d35ceb002991", }, data: { name: "Arindam", }, }); Enter fullscreen mode. Exit fullscreen mode And See the Console! The name has been changed! DELETE: Okay, Now we will delete a user using its ID! Here's the code for that: const deletedUser = await prisma.user.delete({ where: { id: "84e37ff7-0f7e-41c6-9cad-d35ceb002991", }, }); const deletedUser = await prisma.user.delete({ where: { id: "84e37ff7-0f7e-41c6-9cad-d35ceb002991", }, }); Enter fullscreen mode. Exit fullscreen mode This will Delete the user named Arindam! To verify that, we check allUsers again! And there's no user named Arindam! We have successfully deleted the user! With that, we have completed our basic CRUD operations in our Node+Postgres app using Prisma. Pretty Simple, Right? For Reference, You can check the Code Here: Conclusion If you found this blog post helpful, please consider sharing it with others who might benefit from it. You can also follow me for more content on Javascript, React, and other web Development topics. For Paid collaboration, mail me at: arindammajumder2020@gmail.com arindammajumder2020@gmail.com Connect with me on Twitter , LinkedIn , Youtube , and GitHub . Twitter LinkedIn Youtube GitHub Thank you for Reading :)