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: "[email protected]",
      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: "[email protected]",
      name: "John Doe",
      password: "password123",
    },
    {
      email: "[email protected]",
      name: "Jane Smith",
      password: "pass@123",
    },
    {
      email: "[email protected]",
      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: [email protected]

Connect with me on Twitter, LinkedIn, Youtube, and GitHub.

Thank you for Reading :)