Setting Up a Backend with Prisma, Express, and PostgreSQL

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:

Start from scratch with Prisma ORM using JavaScript and PostgreSQL (15 min) | Prisma Documentation

Learn how to create a new Node.js project from scratch by connecting Prisma ORM to your PostgreSQL database and generating a Prisma Client for database access.

prisma.io

Youtube Tutorial:

youtube.com

Sample-Code :

Ajor-Saha / prisma-express-postgresql

A backend and rest api set up using expressjs, prisma and postgresql database

API Features

User Management

• User Registration: Allows users to register by providing their name, email, and password. Passwords are hashed for security.
• User Login: Authenticates users and provides a JWT token for secure access.
• User Logout: Logs out the user by clearing the authentication cookie.
• Fetch Users: Retrieves a list of all registered users.
• Show User: Fetches details of a specific user by their ID.
• Update User: Allows users to update their profile details including their name and password.
• Delete User: Deletes a user's account.

Post Management

• Create Post: Allows authenticated users to create a new post with a title and description.
• Fetch Posts: Retrieves a paginated list of all posts, including comments and the names of users who made the comments.
• Search Posts: Searches posts based on their description.
• Show Post: Fetches details of a specific post by…

View on GitHub