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

Comments

[ANTHONY OBI]

Very consise and direct. How will you deploy such application built on the localhost to a shared server (cpanel)?

[Ajor]

You can deploy it on cPanel if your host supports Node.js apps through the Application Manager. Just upload your code, install dependencies, configure your .env with the cPanel PostgreSQL database (or an external one), run npx prisma migrate deploy, and start the app with Passenger/PM2.

Sorry for very late reply