Posts Endpoints

The posts endpoints are the core of Bulletin. This lesson builds the complete CRUD API for posts plus the upvote toggle.

Posts router

src/routes/posts.ts

import { Router } from 'express'
import { listPosts, getPost, createPost, deletePost, toggleUpvote } from '../controllers/posts.js'
import { authenticate } from '../middleware/authenticate.js'
import { requireFields } from '../middleware/validate.js'

const router = Router()

router.get('/', listPosts)
router.get('/:id', getPost)
router.post('/', authenticate, requireFields('title', 'body'), createPost)
router.delete('/:id', authenticate, deletePost)
router.post('/:id/upvote', authenticate, toggleUpvote)

export default router

Posts controller

src/controllers/posts.ts

import { Request, Response } from 'express'
import { db } from '../db/index.js'

interface PostRow {
  id: number; title: string; body: string
  user_id: number; upvotes: number; created_at: string
  author_username: string
}

// GET /posts
export function listPosts(req: Request, res: Response) {
  const limit = Math.min(parseInt(req.query.limit as string || '20', 10), 50)
  const page = Math.max(parseInt(req.query.page as string || '1', 10), 1)
  const offset = (page - 1) * limit
  const sortField = req.query.sort === 'top' ? 'upvotes DESC' : 'posts.created_at DESC'

  const posts = db.prepare(`
    SELECT posts.*, users.username AS author_username
    FROM posts
    JOIN users ON posts.user_id = users.id
    ORDER BY ${sortField}
    LIMIT ? OFFSET ?
  `).all(limit, offset) as PostRow[]

  const { total } = db.prepare('SELECT COUNT(*) as total FROM posts').get() as { total: number }

  res.json({ posts, total, page, limit })
}

// GET /posts/:id
export function getPost(req: Request, res: Response) {
  const post = db.prepare(`
    SELECT posts.*, users.username AS author_username
    FROM posts
    JOIN users ON posts.user_id = users.id
    WHERE posts.id = ?
  `).get(parseInt(req.params.id)) as PostRow | undefined

  if (!post) return res.status(404).json({ error: 'Post not found' })
  res.json(post)
}

// POST /posts
export function createPost(req: Request, res: Response) {
  const { title, body } = req.body as { title: string; body: string }
  const { userId } = res.locals.user

  const result = db.prepare(
    'INSERT INTO posts (title, body, user_id) VALUES (?, ?, ?)'
  ).run(title.trim(), body.trim(), userId)

  const post = db.prepare(`
    SELECT posts.*, users.username AS author_username
    FROM posts JOIN users ON posts.user_id = users.id
    WHERE posts.id = ?
  `).get(result.lastInsertRowid) as PostRow

  res.status(201).json(post)
}

// DELETE /posts/:id
export function deletePost(req: Request, res: Response) {
  const { userId } = res.locals.user
  const postId = parseInt(req.params.id)

  const result = db.prepare(
    'DELETE FROM posts WHERE id = ? AND user_id = ?'
  ).run(postId, userId)

  if (result.changes === 0) {
    return res.status(404).json({ error: 'Post not found or not yours' })
  }
  res.sendStatus(204)
}

// POST /posts/:id/upvote
export function toggleUpvote(req: Request, res: Response) {
  const { userId } = res.locals.user
  const postId = parseInt(req.params.id)

  const existing = db.prepare(
    'SELECT 1 FROM post_upvotes WHERE user_id = ? AND post_id = ?'
  ).get(userId, postId)

  const upvoteTransaction = db.transaction(() => {
    if (existing) {
      db.prepare('DELETE FROM post_upvotes WHERE user_id = ? AND post_id = ?').run(userId, postId)
      db.prepare('UPDATE posts SET upvotes = upvotes - 1 WHERE id = ?').run(postId)
      return false // removed upvote
    } else {
      db.prepare('INSERT INTO post_upvotes (user_id, post_id) VALUES (?, ?)').run(userId, postId)
      db.prepare('UPDATE posts SET upvotes = upvotes + 1 WHERE id = ?').run(postId)
      return true // added upvote
    }
  })

  const upvoted = upvoteTransaction()
  const post = db.prepare('SELECT upvotes FROM posts WHERE id = ?').get(postId) as { upvotes: number }
  res.json({ upvoted, upvotes: post.upvotes })
}

Testing

# Create a post (need token from registration)
curl -X POST http://localhost:3000/posts \
  -H "Authorization: Bearer YOUR_TOKEN" \
  -H "Content-Type: application/json" \
  -d '{"title": "Hello Bulletin", "body": "My first post"}'

# List posts
curl http://localhost:3000/posts

# Upvote
curl -X POST http://localhost:3000/posts/1/upvote \
  -H "Authorization: Bearer YOUR_TOKEN"

Exercise

1. Build the complete posts router and controller.
2. Test all five endpoints with curl or Thunder Client.
3. Confirm you can’t delete someone else’s post.
4. Upvote a post twice — confirm the count increases by 1 on the first and decreases on the second (toggle behavior).

Recap

• Public routes (GET /posts, GET /posts/:id) need no authentication.
• Protected routes (POST, DELETE, upvote) require the authenticate middleware.
• DELETE uses AND user_id = ? to enforce ownership — the database handles authorization.
• The upvote toggle uses a transaction to atomically update both tables.
• Always return full data (including joined author_username) rather than just IDs.