How to Build a Reviews & Ratings with Replit

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

1// Run this after Drizzle migrations in your server startup file.
2// It installs the trigger function and attaches it to the reviews table.
3
4const { sql } = require('drizzle-orm');
5const { db } = require('./db');
6
7const triggerSQL = `
8  CREATE OR REPLACE FUNCTION update_item_rating_aggregates()
9  RETURNS TRIGGER AS $$
10  DECLARE
11    target_item_id INTEGER;
12  BEGIN
13    IF TG_OP = 'DELETE' THEN
14      target_item_id := OLD.item_id;
15    ELSE
16      target_item_id := NEW.item_id;
17    END IF;
18
19    UPDATE items
20    SET
21      average_rating = COALESCE(
22        (SELECT ROUND(AVG(rating)::numeric, 2)
23         FROM reviews WHERE item_id = target_item_id), 0),
24      review_count = (
25        SELECT COUNT(*) FROM reviews WHERE item_id = target_item_id)
26    WHERE id = target_item_id;
27
28    RETURN NULL;
29  END;
30  $$ LANGUAGE plpgsql;
31
32  DROP TRIGGER IF EXISTS reviews_aggregate_trigger ON reviews;
33  CREATE TRIGGER reviews_aggregate_trigger
34    AFTER INSERT OR UPDATE OR DELETE ON reviews
35    FOR EACH ROW
36    EXECUTE FUNCTION update_item_rating_aggregates();
37`;
38
39async function setupTrigger() {
40  await db.execute(sql.raw(triggerSQL));
41  console.log('Rating aggregate trigger installed');
42}
43
44setupTrigger().catch(console.error);
45
46module.exports = { setupTrigger };

1const express = require('express');
2const { reviews } = require('../../shared/schema');
3const { eq } = require('drizzle-orm');
4const { db } = require('../db');
5
6const router = express.Router();
7
8// POST /api/items/:id/reviews
9router.post('/items/:id/reviews', async (req, res) => {
10  const userId = req.user?.id;
11  if (!userId) return res.status(401).json({ error: 'Login required to submit a review' });
12
13  const itemId = parseInt(req.params.id);
14  const { rating, title, body, pros, cons } = req.body;
15
16  if (!rating || rating < 1 || rating > 5) {
17    return res.status(400).json({ error: 'Rating must be between 1 and 5' });
18  }
19
20  try {
21    const [review] = await db.insert(reviews).values({
22      itemId,
23      userId,
24      rating: parseInt(rating),
25      title: title || null,
26      body: body || null,
27      pros: pros || null,
28      cons: cons || null,
29    }).returning();
30
31    // PostgreSQL trigger automatically updates items.average_rating
32    res.status(201).json(review);
33  } catch (err) {
34    if (err.code === '23505') {
35      return res.status(409).json({ error: 'You have already reviewed this item' });
36    }
37    res.status(500).json({ error: err.message });
38  }
39});
40
41module.exports = router;

1const { reviewVotes, reviews } = require('../../shared/schema');
2const { eq, and, sql, desc } = require('drizzle-orm');
3
4// POST /api/reviews/:id/vote
5router.post('/reviews/:id/vote', async (req, res) => {
6  const userId = req.user?.id;
7  if (!userId) return res.status(401).json({ error: 'Login required to vote' });
8
9  const reviewId = parseInt(req.params.id);
10  const { isHelpful } = req.body;
11
12  const [existing] = await db.select().from(reviewVotes)
13    .where(and(eq(reviewVotes.reviewId, reviewId), eq(reviewVotes.userId, userId)));
14
15  if (existing) {
16    if (existing.isHelpful === isHelpful) {
17      await db.delete(reviewVotes).where(eq(reviewVotes.id, existing.id));
18      await db.update(reviews)
19        .set({ helpfulCount: sql`helpful_count - 1` })
20        .where(eq(reviews.id, reviewId));
21      return res.json({ voted: false });
22    }
23    await db.update(reviewVotes).set({ isHelpful }).where(eq(reviewVotes.id, existing.id));
24  } else {
25    await db.insert(reviewVotes).values({ reviewId, userId, isHelpful });
26    if (isHelpful) {
27      await db.update(reviews)
28        .set({ helpfulCount: sql`helpful_count + 1` })
29        .where(eq(reviews.id, reviewId));
30    }
31  }
32  res.json({ voted: true, isHelpful });
33});
34
35// GET /api/items/:id/rating-distribution
36router.get('/items/:id/rating-distribution', async (req, res) => {
37  const itemId = parseInt(req.params.id);
38  const result = await db.execute(sql`
39    SELECT gs.star_level,
40      COALESCE(r.count, 0)::integer AS count,
41      CASE WHEN total.total_count > 0
42        THEN ROUND(COALESCE(r.count, 0)::numeric / total.total_count * 100, 1)
43        ELSE 0 END AS percentage
44    FROM generate_series(5, 1, -1) AS gs(star_level)
45    LEFT JOIN (SELECT rating, COUNT(*) AS count FROM reviews
46      WHERE item_id = ${itemId} GROUP BY rating) r ON r.rating = gs.star_level
47    CROSS JOIN (SELECT COUNT(*) AS total_count FROM reviews
48      WHERE item_id = ${itemId}) total
49    ORDER BY gs.star_level DESC
50  `);
51  res.json(result.rows);
52});
53
54module.exports = router;