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
- Node.js installed on your machine
- PostgreSQL installed and running
- 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:
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…
Top comments (1)
Very consise and direct. How will you deploy such application built on the localhost to a shared server (cpanel)?