Setting Up a Backend with Prisma, Express, and PostgreSQL
Tags: javascript, express, prisma, postgres
Published: 2024-07-03 19:03:33

Hi,
In this post, I will go through the steps of setting up a backend using Prisma, Express, and PostgreSQL. We will cover the installation, configuration, and implementation of a REST API and basic CRUD operations.

**Prerequisites**

1. Node.js installed on your machine
2. PostgreSQL installed and running
3. Basic understanding of JavaScript and SQL


**Step 1: Initialize the Project**


First, create a new directory for your project and navigate into it. Then, initialize a new Node.js project.

```
mkdir my-backend
cd my-backend
npm init -y
```

**Step 2: Install Dependencies**

Install the necessary dependencies for Express and other packages

```
npm install express bcryptjs jsonwebtoken dotenv cookie-parser
npm install -D nodemon

```
**Step 3: Set Up Prisma**


```
npm install prisma --save-dev
npx prisma init
```

**Step 4: Configure PostgreSQL**


Update your .env file with your PostgreSQL database connection URL.

```
DATABASE_URL="postgresql://postgres:jondoe@localhost:5432/backendprisma?schema=public"
```

Update the schema.prisma file to use PostgreSQL and define your models.



```
generator client {
  provider = "prisma-client-js"
}

datasource db {
  provider = "postgresql"
  url      = env("DATABASE_URL")
}

model User {
  id         Int       @id @default(autoincrement())
  name       String?
  email      String    @unique
  password   String?
  post Post[]
  comment Comment[]
  created_at DateTime  @default(now())
}

model Post {
  id            Int       @id @default(autoincrement())
  user          User      @relation(fields: [user_id], references: [id], onDelete: Cascade)
  comment       Comment[]
  user_id       Int
  title         String
  description   String
  comment_count Int       @default(0)
  created_at    DateTime  @default(now())
}

model Comment {
  id         String   @id @default(uuid())
  post       Post     @relation(fields: [post_id], references: [id], onDelete: Cascade)
  post_id    Int
  user       User     @relation(fields: [user_id], references: [id], onDelete: Cascade)
  user_id    Int
  comment    String
  created_at DateTime @default(now())
}
```


**Step 5: Migrate the Database**


Run the following command to create the database tables based on your Prisma schema.

```
npx prisma migrate dev --name init

```

**Step 6: Install and generate Prisma Client**


```
npm install @prisma/client

```

**Step 7: Set Up Express Server
**


Create an server.js file for your Express server.

```
import "dotenv/config";
import cookieParser from "cookie-parser";
import express from "express";


const app = express();
const PORT = process.env.PORT || 4000;

// * Middleware
app.use(express.json());
app.use(express.urlencoded({ extended: false }));
app.use(cookieParser());

app.get("/", (req, res) => {
    return res.send("Hi Everyone.");
});
  
// * routes file
import userRouter from "./routes/userRoutes.js"
import postRouter from "./routes/postRoutes.js"
import commentRouter from "./routes/commentRoutes.js"

app.use("/api/user", userRouter);

app.listen(PORT, () => console.log(`Server is running on PORT ${PORT}`));

```

**Step 8: Implement User Authentication
**


Create authentication routes and controllers.

routes/userRoutes.js

```
import { Router } from "express";
import {
    createUser,
    deleteUser,
    loginUser,
    logoutUser,
    updateUser
} from "../controllers/userController.js";
import { verifyJWT } from "../middleware/auth.middleware.js";

const router = Router();

router.post("/adduser", createUser);
router.post("/login", loginUser);
router.post("/logout", verifyJWT, logoutUser);
router.put("/update", verifyJWT, updateUser);
router.delete("/delete", verifyJWT,  deleteUser);

export default router;
```


controllers/userController.js


```
import prisma from "../DB/db.config.js";
import { ApiResponse } from "../utils/ApiResponse.js";
import bcrypt from "bcryptjs";
import jwt from "jsonwebtoken";

export const createUser = async (req, res) => {
  const { name, email, password } = req.body;

  try {
    // Validate that all fields are provided
    if (
      [name, email, password].some((field) => !field || field.trim() === "")
    ) {
      return res
        .status(400)
        .json(new ApiResponse(false, 400, {}, "All fields are required"));
    }

    // Check if the user already exists
    const findUser = await prisma.user.findUnique({
      where: {
        email: email,
      },
    });

    if (findUser) {
      return res
        .status(400)
        .json(
          new ApiResponse(
            false,
            400,
            {},
            "Email already taken. Please use another email."
          )
        );
    }

    // Hash the password
    const hashedPassword = await bcrypt.hash(password, 10);

    // Create the new user
    const newUser = await prisma.user.create({
      data: {
        name: name,
        email: email,
        password: hashedPassword,
      },
    });

    // Exclude password from the response
    const { password: _, ...userWithoutPassword } = newUser;

    return res
      .status(201)
      .json(
        new ApiResponse(
          true,
          201,
          userWithoutPassword,
          "User registered successfully"
        )
      );
  } catch (error) {
    console.error(error);
    return res
      .status(500)
      .json(new ApiResponse(false, 500, null, "Internal Server Error"));
  }
};

export const loginUser = async (req, res) => {
  const { email, password } = req.body;

  try {
    // Validate that all fields are provided
    if ([email, password].some((field) => !field || field.trim() === "")) {
      return res
        .status(400)
        .json(
          new ApiResponse(false, 400, {}, "Email and password are required")
        );
    }

    // Check if the user exists
    const user = await prisma.user.findUnique({
      where: {
        email: email,
      },
    });

    if (!user) {
      return res
        .status(401)
        .json(new ApiResponse(false, 401, {}, "Invalid email or password"));
    }

    // Check if the password is correct
    const isPasswordValid = await bcrypt.compare(password, user.password);
    if (!isPasswordValid) {
      return res
        .status(401)
        .json(new ApiResponse(false, 401, {}, "Invalid email or password"));
    }

    // Generate JWT access token
    const accessToken = jwt.sign(
      { userId: user.id, email: user.email },
      process.env.JWT_SECRET,
      {
        expiresIn: "1d",
      }
    );

    // Exclude password from the response
    const { password: _, ...userWithoutPassword } = user;

    // Set the access token as a cookie
    res.cookie("accessToken", accessToken, {
      httpOnly: true,
      secure: true, // Ensure secure cookies
      maxAge: 24 * 60 * 60 * 1000, // 1 day
    });

    return res
      .status(200)
      .json(
        new ApiResponse(
          true,
          200,
          { user: userWithoutPassword, accessToken },
          "Login successful"
        )
      );
  } catch (error) {
    console.error(error);
    return res
      .status(500)
      .json(new ApiResponse(false, 500, null, "Internal Server Error"));
  }
};



// update the user
export const updateUser = async (req, res) => {
  const { name, password } = req.body;

  try {
    const userId = req.user.id;

    if (!req.user) {
      return res
        .status(401)
        .json(new ApiResponse(false, 401, null, "Unauthorized request"));
    }

    const updateData = {};

    // Only add fields to updateData if they are provided
    if (name) {
      updateData.name = name;
    }

    if (password) {
      const hashedPassword = await bcrypt.hash(password, 10);
      updateData.password = hashedPassword;
    }

    if (Object.keys(updateData).length === 0) {
      return res
        .status(400)
        .json(
          new ApiResponse(false, 400, null, "No fields provided to update")
        );
    }

    const updatedUser = await prisma.user.update({
      where: {
        id: Number(userId),
      },
      data: updateData,
    });

    return res
      .status(200)
      .json(
        new ApiResponse(true, 200, updatedUser, "User updated successfully")
      );
  } catch (error) {
    console.error(error);
    return res
      .status(500)
      .json(new ApiResponse(false, 500, {}, "Internal Server Error"));
  }
};

// * Delete user
export const deleteUser = async (req, res) => {
  try {
    const userId = req.user?.id;

    if (!req.user) {
      return res
        .status(401)
        .json(new ApiResponse(false, 401, null, "Unauthorized request"));
    }

    // Attempt to delete the user
    await prisma.user.delete({
      where: {
        id: Number(userId),
      },
    });

    return res
      .status(200)
      .json(new ApiResponse(true, 200, null, "User deleted successfully"));
  } catch (error) {
    console.error(error);
    return res
      .status(500)
      .json(new ApiResponse(false, 500, null, "Internal Server Error"));
  }
};

export const logoutUser = (req, res) => {
  try {
    // Check if user is authenticated
    if (!req.user) {
      return res
        .status(401)
        .json(new ApiResponse(false, 401, null, "Unauthorized request"));
    }

    // Clear the cookie
    res.clearCookie("accessToken", {
      httpOnly: true,
      secure: false, // Set to true if using HTTPS in production
      sameSite: "strict",
    });

    return res
      .status(200)
      .json(new ApiResponse(true, 200, null, "User logged out successfully"));
  } catch (error) {
    console.error(error);
    return res
      .status(500)
      .json(new ApiResponse(false, 500, null, "Internal Server Error"));
  }
};
```



middleware/authMiddleware.js


```
import jwt from "jsonwebtoken";
import prisma from "../DB/db.config.js";
import { ApiResponse } from "../utils/ApiResponse.js";

export const verifyJWT = async (req, res, next) => {
  try {
    const token =
      req.cookies?.accessToken ||
      req.header("Authorization")?.replace("Bearer ", "");

    if (!token) {
      return res
        .status(401)
        .json(new ApiResponse(false, 401, null, "Unauthorized request"));
    }

    const decodedToken = jwt.verify(token, process.env.JWT_SECRET);
    const user = await prisma.user.findUnique({
      where: {
        id: decodedToken.userId,
      },
      select: {
        id: true,
        name: true,
        email: true,
        post: true,
        comment: true,
      },
    });

    if (!user) {
      return res
        .status(401)
        .json(new ApiResponse(false, 401, null, "Invalid Access Token"));
    }

    req.user = user;
    next();
  } catch (error) {
    return res
      .status(401)
      .json(
        new ApiResponse(
          false,
          401,
          null,
          error.message || "Invalid access token"
        )
      );
  }
};


```


'utils/ApiResponse.js'


```
class ApiResponse {
  constructor(success, status, data, message) {
    this.success = success;
    this.status = status;
    this.data = data;
    this.message = message;
  }
}

export { ApiResponse };
```


_Please note that there might be errors in this post and provided code, so refer to the official documentation, YouTube tutorials, and the GitHub repository for the complete and error-free version._


Prisma:

{% embed https://www.prisma.io/docs/getting-started/setup-prisma/start-from-scratch/relational-databases-node-postgresql %}


Youtube Tutorial: 

{% embed https://youtu.be/GReoWKUnwAg?si=7X6K_hyxQ2Tqg5YY
 %}

Sample-Code : 

{% embed https://github.com/Ajor-Saha/prisma-express-postgresql
 %}