# How to Build a Reviews & Ratings with Replit

- Tool: How to Build with Replit
- Difficulty: Beginner
- Compatibility: Replit Free
- Last updated: April 2026

## TL;DR

Build a star rating and review system in Replit in 30-60 minutes. Use Replit Agent to generate an Express + PostgreSQL app with Drizzle ORM that handles item ratings, written reviews, helpful votes, and automatic aggregate score updates via a PostgreSQL trigger. Deploy on Autoscale for read-heavy traffic.

## Before you start

- A Replit account (free tier is sufficient for this build)
- Basic understanding of what a REST API does (no coding experience needed)
- A list of item types you want to make reviewable (products, services, businesses, or locations)

## Step-by-step guide

### 1. Scaffold the project with Replit Agent

Create a new Repl and use the Agent prompt below to generate the full reviews and ratings system. The prompt defines all three tables and the routes Agent needs to generate.

```
// Type this into Replit Agent:
// Build a reviews and ratings system with Express and PostgreSQL using Drizzle ORM.
// Tables:
// - items: id serial pk, name text not null, type text (enum product/service/business/location),
//   description text, image_url text, average_rating numeric default 0,
//   review_count integer default 0, created_at timestamp default now()
// - reviews: id serial, item_id integer FK items not null, user_id text not null,
//   rating integer not null (1-5), title text, body text, pros text, cons text,
//   is_verified boolean default false, helpful_count integer default 0,
//   created_at timestamp default now()
//   UNIQUE constraint on (item_id, user_id)
// - review_votes: id serial, review_id integer FK reviews not null,
//   user_id text not null, is_helpful boolean not null,
//   created_at timestamp default now()
//   UNIQUE constraint on (review_id, user_id)
// Routes:
// GET /api/items — list with average_rating and review_count
// GET /api/items/:id — detail
// GET /api/items/:id/reviews — paginated, sort by recent/helpful/rating_high/rating_low
// POST /api/items/:id/reviews — create, one per user per item
// PUT /api/reviews/:id — edit own review
// DELETE /api/reviews/:id — delete own review
// POST /api/reviews/:id/vote — helpful toggle
// GET /api/items/:id/rating-distribution — count per star level
// Use Replit Auth. Bind server to 0.0.0.0.
```

> Pro tip: Ask Agent to also create a seed script that inserts 5-10 sample items and 20+ reviews so you can see the aggregate scores and distribution charts working right away without entering data manually.

**Expected result:** A running Express app on port 5000. The console shows the server started. The Drizzle schema file exists with all three tables defined.

### 2. Add the PostgreSQL trigger for automatic aggregate updates

A trigger on the reviews table recalculates average_rating and review_count on items whenever a review is inserted, updated, or deleted. This keeps aggregates accurate without application-level logic or race conditions.

```
// Run this after Drizzle migrations in your server startup file.
// It installs the trigger function and attaches it to the reviews table.

const { sql } = require('drizzle-orm');
const { db } = require('./db');

const triggerSQL = `
  CREATE OR REPLACE FUNCTION update_item_rating_aggregates()
  RETURNS TRIGGER AS $$
  DECLARE
    target_item_id INTEGER;
  BEGIN
    IF TG_OP = 'DELETE' THEN
      target_item_id := OLD.item_id;
    ELSE
      target_item_id := NEW.item_id;
    END IF;

    UPDATE items
    SET
      average_rating = COALESCE(
        (SELECT ROUND(AVG(rating)::numeric, 2)
         FROM reviews WHERE item_id = target_item_id), 0),
      review_count = (
        SELECT COUNT(*) FROM reviews WHERE item_id = target_item_id)
    WHERE id = target_item_id;

    RETURN NULL;
  END;
  $$ LANGUAGE plpgsql;

  DROP TRIGGER IF EXISTS reviews_aggregate_trigger ON reviews;
  CREATE TRIGGER reviews_aggregate_trigger
    AFTER INSERT OR UPDATE OR DELETE ON reviews
    FOR EACH ROW
    EXECUTE FUNCTION update_item_rating_aggregates();
`;

async function setupTrigger() {
  await db.execute(sql.raw(triggerSQL));
  console.log('Rating aggregate trigger installed');
}

setupTrigger().catch(console.error);

module.exports = { setupTrigger };
```

> Pro tip: The trigger uses COALESCE to handle the case where all reviews for an item are deleted — it resets average_rating to 0 instead of NULL, which prevents display bugs on the frontend.

**Expected result:** After adding the trigger, posting a review immediately updates the item's average_rating and review_count without any extra query in the route handler.

### 3. Build the review creation route with duplicate prevention

The review creation route relies on the unique constraint for enforcement. It catches the PostgreSQL error code 23505 and returns a user-friendly 409 response instead of a generic 500 error.

```
const express = require('express');
const { reviews } = require('../../shared/schema');
const { eq } = require('drizzle-orm');
const { db } = require('../db');

const router = express.Router();

// POST /api/items/:id/reviews
router.post('/items/:id/reviews', async (req, res) => {
  const userId = req.user?.id;
  if (!userId) return res.status(401).json({ error: 'Login required to submit a review' });

  const itemId = parseInt(req.params.id);
  const { rating, title, body, pros, cons } = req.body;

  if (!rating || rating < 1 || rating > 5) {
    return res.status(400).json({ error: 'Rating must be between 1 and 5' });
  }

  try {
    const [review] = await db.insert(reviews).values({
      itemId,
      userId,
      rating: parseInt(rating),
      title: title || null,
      body: body || null,
      pros: pros || null,
      cons: cons || null,
    }).returning();

    // PostgreSQL trigger automatically updates items.average_rating
    res.status(201).json(review);
  } catch (err) {
    if (err.code === '23505') {
      return res.status(409).json({ error: 'You have already reviewed this item' });
    }
    res.status(500).json({ error: err.message });
  }
});

module.exports = router;
```

**Expected result:** POST /api/items/1/reviews creates a review and the item's average_rating updates immediately. A second POST by the same user returns 409 with 'You have already reviewed this item'.

### 4. Build the helpful vote toggle and rating distribution routes

The vote route uses the review_votes table to prevent duplicate voting. The distribution route uses generate_series to always return all 5 star levels, even if some have zero reviews.

```
const { reviewVotes, reviews } = require('../../shared/schema');
const { eq, and, sql, desc } = require('drizzle-orm');

// POST /api/reviews/:id/vote
router.post('/reviews/:id/vote', async (req, res) => {
  const userId = req.user?.id;
  if (!userId) return res.status(401).json({ error: 'Login required to vote' });

  const reviewId = parseInt(req.params.id);
  const { isHelpful } = req.body;

  const [existing] = await db.select().from(reviewVotes)
    .where(and(eq(reviewVotes.reviewId, reviewId), eq(reviewVotes.userId, userId)));

  if (existing) {
    if (existing.isHelpful === isHelpful) {
      await db.delete(reviewVotes).where(eq(reviewVotes.id, existing.id));
      await db.update(reviews)
        .set({ helpfulCount: sql`helpful_count - 1` })
        .where(eq(reviews.id, reviewId));
      return res.json({ voted: false });
    }
    await db.update(reviewVotes).set({ isHelpful }).where(eq(reviewVotes.id, existing.id));
  } else {
    await db.insert(reviewVotes).values({ reviewId, userId, isHelpful });
    if (isHelpful) {
      await db.update(reviews)
        .set({ helpfulCount: sql`helpful_count + 1` })
        .where(eq(reviews.id, reviewId));
    }
  }
  res.json({ voted: true, isHelpful });
});

// GET /api/items/:id/rating-distribution
router.get('/items/:id/rating-distribution', async (req, res) => {
  const itemId = parseInt(req.params.id);
  const result = await db.execute(sql`
    SELECT gs.star_level,
      COALESCE(r.count, 0)::integer AS count,
      CASE WHEN total.total_count > 0
        THEN ROUND(COALESCE(r.count, 0)::numeric / total.total_count * 100, 1)
        ELSE 0 END AS percentage
    FROM generate_series(5, 1, -1) AS gs(star_level)
    LEFT JOIN (SELECT rating, COUNT(*) AS count FROM reviews
      WHERE item_id = ${itemId} GROUP BY rating) r ON r.rating = gs.star_level
    CROSS JOIN (SELECT COUNT(*) AS total_count FROM reviews
      WHERE item_id = ${itemId}) total
    ORDER BY gs.star_level DESC
  `);
  res.json(result.rows);
});

module.exports = router;
```

> Pro tip: generate_series(5, 1, -1) ensures all 5 star levels are always present in the response. Without it, a new item with no 1-star or 2-star reviews would return an array shorter than 5 items, breaking your frontend bar chart.

**Expected result:** GET /api/items/1/rating-distribution returns 5 objects with star_level, count, and percentage. POST /api/reviews/1/vote toggles helpful status — clicking twice removes the vote.

## Complete code example

File: `server/routes/reviews.js`

```javascript
const express = require('express');
const { reviews, reviewVotes } = require('../../shared/schema');
const { eq, and, desc, sql } = require('drizzle-orm');
const { db } = require('../db');

const router = express.Router();

router.get('/items/:id/reviews', async (req, res) => {
  const itemId = parseInt(req.params.id);
  const page = parseInt(req.query.page) || 1;
  const limit = parseInt(req.query.limit) || 10;
  const sort = req.query.sort || 'recent';
  const orderMap = {
    recent: desc(reviews.createdAt),
    helpful: desc(reviews.helpfulCount),
    rating_high: desc(reviews.rating),
    rating_low: reviews.rating,
  };
  const rows = await db.select().from(reviews)
    .where(eq(reviews.itemId, itemId))
    .orderBy(orderMap[sort] || desc(reviews.createdAt))
    .limit(limit).offset((page - 1) * limit);
  res.json({ reviews: rows, page, limit });
});

router.post('/items/:id/reviews', async (req, res) => {
  const userId = req.user?.id;
  if (!userId) return res.status(401).json({ error: 'Login required' });
  const { rating, title, body, pros, cons } = req.body;
  if (!rating || rating < 1 || rating > 5)
    return res.status(400).json({ error: 'Rating must be 1-5' });
  try {
    const [review] = await db.insert(reviews).values({
      itemId: parseInt(req.params.id), userId,
      rating: parseInt(rating), title: title || null,
      body: body || null, pros: pros || null, cons: cons || null,
    }).returning();
    res.status(201).json(review);
  } catch (err) {
    if (err.code === '23505') return res.status(409).json({ error: 'Already reviewed' });
    res.status(500).json({ error: err.message });
  }
});

router.put('/reviews/:id', async (req, res) => {
  const userId = req.user?.id;
  if (!userId) return res.status(401).json({ error: 'Login required' });
  const { rating, title, body, pros, cons } = req.body;
  const [updated] = await db.update(reviews)
    .set({ rating: parseInt(rating), title, body, pros, cons })
    .where(and(eq(reviews.id, parseInt(req.params.id)), eq(reviews.userId, userId)))
    .returning();
  if (!updated) return res.status(404).json({ error: 'Review not found or not yours' });
  res.json(updated);
});

module.exports = router;
```

## Common mistakes

- **Recalculating average_rating in application code on every review write** — Fetching all reviews, computing AVG() in JavaScript, and updating the item creates a race condition if two reviews are submitted simultaneously — one update can overwrite the other. Fix: Use a PostgreSQL trigger on the reviews table. The trigger runs inside the same database transaction as the review insert, making it atomic and race-condition safe.
- **Using a simple integer counter for helpful votes instead of a votes table** — A counter column lets the same user vote multiple times, inflating counts. There is also no way to undo a vote without knowing who voted. Fix: Use a review_votes table with a UNIQUE constraint on (review_id, user_id). Before inserting, check for an existing vote. If the same vote type exists, delete it (toggle off). If different, update it.
- **Allowing multiple reviews per user per item without a database constraint** — Application-level checks alone can fail under concurrent requests. Two simultaneous submissions can both pass the check before either has been inserted. Fix: Add a UNIQUE constraint on (item_id, user_id) at the database level. Catch error code 23505 in the route and return a 409 response — the database is the authoritative guard.

## Best practices

- Use a PostgreSQL trigger to maintain average_rating and review_count — never recalculate aggregates in application code where race conditions can corrupt data.
- Store Replit Auth user IDs in the user_id column and use them for ownership checks in PUT and DELETE routes — never trust a user_id sent in the request body.
- Use the UNIQUE constraint on (item_id, user_id) as the primary defense against duplicate reviews, not application-level SELECT-then-INSERT checks.
- Always validate that rating is an integer between 1 and 5 before inserting — return a 400 with a clear message rather than letting a constraint error bubble up as a 500.
- Use generate_series in the rating distribution query to always return all 5 star levels — prevents frontend rendering errors when a star level has zero reviews.
- Deploy on Autoscale — review pages are read-heavy with spiky traffic and benefit from Replit's scaling model. Pre-computed aggregates on items make reads fast at scale.
- Use Drizzle Studio (built into Replit, accessible from the database panel) to inspect the items table and verify that average_rating updates correctly after each review insert.

## Frequently asked questions

### How does the star rating aggregate stay accurate without manual updates?

A PostgreSQL trigger fires after every INSERT, UPDATE, or DELETE on the reviews table. It runs SELECT AVG(rating) and COUNT(*) scoped to the affected item_id and writes the results back to items.average_rating and items.review_count. Because it runs inside the same transaction as the review write, it is always consistent.

### Can I use this for any type of item — not just products?

Yes. The items table has a type column with values product, service, business, and location. The review and rating logic is identical regardless of item type. Change the values you seed into the items table and update the frontend labels — the API stays the same.

### What prevents someone from leaving multiple reviews for the same item?

A UNIQUE constraint on (item_id, user_id) in the reviews table. If a user tries to insert a second review, PostgreSQL raises error code 23505. The POST route catches this and returns a 409 Conflict with the message 'You have already reviewed this item'.

### What Replit plan do I need?

The free tier is sufficient. This build uses Express, PostgreSQL (built into Replit), and Replit Auth — all available on free accounts. Deploy on Autoscale, which is available on all plans.

### Should I deploy on Autoscale or Reserved VM?

Autoscale. Review pages are read-heavy — most visitors read reviews rather than write them. Autoscale handles bursty read traffic well and scales to zero during quiet periods. Reserved VM is only needed for apps with webhooks or WebSocket connections, which this build does not use.

### How do I mark certain reviews as Verified Buyer?

Cross-reference the reviews table against an orders table. After an order is marked complete, run an UPDATE on reviews setting is_verified = true where item_id and user_id match the order. The is_verified column is already in the schema — you just need to populate it from your order fulfillment logic.

### Can RapidDev help build a custom reviews and ratings system?

Yes. RapidDev has built 600+ apps including review systems with verified purchases, moderation queues, owner responses, and spam detection. If your use case needs features beyond this guide, book a free consultation at rapidevelopers.com.

### How do I prevent the same user from voting on a review multiple times?

The review_votes table has a UNIQUE constraint on (review_id, user_id). The vote route checks for an existing vote before inserting. If the same vote type already exists, it deletes the vote (toggle off). If a different vote type exists, it updates the record. This makes the helpful button a true toggle with no duplicates possible at the database level.

---

Source: https://www.rapidevelopers.com/how-to-build-replit/reviews-ratings
© RapidDev — https://www.rapidevelopers.com/how-to-build-replit/reviews-ratings