How to Build a Blog Backend with Replit

1// Prompt to type into Replit Agent:
2// Build a Node.js Express headless blog backend with Replit Auth and built-in PostgreSQL using Drizzle ORM.
3// Schema in shared/schema.ts:
4// * authors: id serial pk, user_id text not null unique, name text not null,
5//   bio text, avatar_url text, created_at timestamp default now()
6// * posts: id serial pk, author_id integer references authors not null,
7//   title text not null, slug text not null unique, excerpt text,
8//   body text not null, cover_image_url text, status text default 'draft',
9//   published_at timestamp, created_at timestamp default now(), updated_at timestamp default now()
10// * categories: id serial pk, name text not null unique, slug text not null unique, description text
11// * post_categories: id serial pk, post_id integer references posts not null,
12//   category_id integer references categories not null, unique on (post_id, category_id)
13// * tags: id serial pk, name text not null unique, slug text not null unique
14// * post_tags: id serial pk, post_id integer references posts not null,
15//   tag_id integer references tags not null, unique on (post_id, tag_id)
16// Public routes (no auth): GET /api/posts, GET /api/posts/:slug, GET /api/categories, GET /api/tags
17// Admin routes (Replit Auth): POST/PUT /api/admin/posts, PATCH /api/admin/posts/:id/publish,
18// DELETE /api/admin/posts/:id, POST /api/admin/categories, POST /api/admin/tags
19// React frontend: public blog with post list and single post view, admin panel with post editor

1import { db } from '../db.js';
2import { posts } from '../../shared/schema.js';
3import { like, sql } from 'drizzle-orm';
4
5export function slugify(text) {
6  return text
7    .toLowerCase()
8    .replace(/[^\w\s-]/g, '') // remove non-word chars
9    .replace(/[\s_-]+/g, '-') // replace spaces/underscores with hyphens
10    .replace(/^-+|-+$/g, ''); // trim leading/trailing hyphens
11}
12
13export async function generateUniqueSlug(title) {
14  const baseSlug = slugify(title);
15
16  // Check for existing slugs that start with baseSlug
17  const existing = await db
18    .select({ slug: posts.slug })
19    .from(posts)
20    .where(like(posts.slug, `${baseSlug}%`));
21
22  if (existing.length === 0) return baseSlug;
23
24  // Find the highest suffix number
25  const suffixes = existing
26    .map(r => r.slug)
27    .filter(s => s === baseSlug || s.match(new RegExp(`^${baseSlug}-\\d+$`)))
28    .map(s => {
29      if (s === baseSlug) return 0;
30      return parseInt(s.replace(`${baseSlug}-`, '')) || 0;
31    });
32
33  const maxSuffix = Math.max(...suffixes);
34  return `${baseSlug}-${maxSuffix + 1}`;
35}

1import { db } from '../db.js';
2import { posts, authors, categories, postCategories, tags, postTags } from '../../shared/schema.js';
3import { eq, and, inArray, desc, count, sql } from 'drizzle-orm';
4
5export async function listPublishedPosts(req, res) {
6  const page = Math.max(1, parseInt(req.query.page) || 1);
7  const limit = Math.min(50, parseInt(req.query.limit) || 10);
8  const offset = (page - 1) * limit;
9  const categorySlug = req.query.category;
10  const tagSlug = req.query.tag;
11  const searchQuery = req.query.q;
12
13  try {
14    // Build base query: published posts only
15    let whereConditions = [eq(posts.status, 'published')];
16
17    // Full-text search
18    if (searchQuery) {
19      whereConditions.push(
20        sql`to_tsvector('english', ${posts.title} || ' ' || ${posts.body}) @@ plainto_tsquery('english', ${searchQuery})`
21      );
22    }
23
24    // Filter by category slug
25    if (categorySlug) {
26      const [cat] = await db.select({ id: categories.id }).from(categories).where(eq(categories.slug, categorySlug));
27      if (cat) {
28        const postIds = await db.select({ postId: postCategories.postId }).from(postCategories).where(eq(postCategories.categoryId, cat.id));
29        whereConditions.push(inArray(posts.id, postIds.map(r => r.postId)));
30      }
31    }
32
33    const condition = and(...whereConditions);
34    const [{ total }] = await db.select({ total: count() }).from(posts).where(condition);
35
36    const results = await db
37      .select({
38        id: posts.id, title: posts.title, slug: posts.slug,
39        excerpt: posts.excerpt, coverImageUrl: posts.coverImageUrl,
40        publishedAt: posts.publishedAt,
41        authorName: authors.name, authorAvatarUrl: authors.avatarUrl,
42      })
43      .from(posts)
44      .leftJoin(authors, eq(posts.authorId, authors.id))
45      .where(condition)
46      .orderBy(desc(posts.publishedAt))
47      .limit(limit)
48      .offset(offset);
49
50    res.json({
51      data: results,
52      pagination: { page, limit, total: Number(total), totalPages: Math.ceil(Number(total) / limit) },
53    });
54  } catch (err) {
55    res.status(500).json({ error: 'Failed to fetch posts' });
56  }
57}

1import { db } from '../db.js';
2import { posts } from '../../shared/schema.js';
3import { eq } from 'drizzle-orm';
4
5// PATCH /api/admin/posts/:id/publish
6export async function publishPost(req, res) {
7  const { id } = req.params;
8  const [updated] = await db
9    .update(posts)
10    .set({ status: 'published', publishedAt: new Date(), updatedAt: new Date() })
11    .where(eq(posts.id, parseInt(id)))
12    .returning();
13
14  if (!updated) return res.status(404).json({ error: 'Post not found' });
15  res.json(updated);
16}
17
18// PATCH /api/admin/posts/:id/unpublish
19export async function unpublishPost(req, res) {
20  const { id } = req.params;
21  const [updated] = await db
22    .update(posts)
23    .set({ status: 'draft', updatedAt: new Date() })
24    .where(eq(posts.id, parseInt(id)))
25    .returning();
26
27  if (!updated) return res.status(404).json({ error: 'Post not found' });
28  res.json(updated);
29}
30
31// DELETE /api/admin/posts/:id — soft delete (archive)
32export async function archivePost(req, res) {
33  const { id } = req.params;
34  const [updated] = await db
35    .update(posts)
36    .set({ status: 'archived', updatedAt: new Date() })
37    .where(eq(posts.id, parseInt(id)))
38    .returning();
39
40  if (!updated) return res.status(404).json({ error: 'Post not found' });
41  res.json({ success: true, post: updated });
42}