Checkpoint: 简单 CRUD 应用

你将完成什么

用 Node.js + Express + PostgreSQL 写一个课程管理 API，包含：增删改查、分页、搜索。

验收标准

1. RESTful API 设计
2. 参数化查询防 SQL 注入
3. 统一响应格式 { code, msg, data }
4. 错误处理中间件

核心代码

server.js

const express = require('express');
const { Pool } = require('pg');

const app = express();
const pool = new Pool({
    host: 'localhost',
    user: 'postgres',
    password: '123456',
    database: 'codeforge'
});

app.use(express.json());

// 列表
app.get('/api/courses', async (req, res) => {
    const { page = 1, pageSize = 10, keyword } = req.query;
    const offset = (page - 1) * pageSize;

    let sql = 'SELECT * FROM course WHERE 1=1';
    const params = [];

    if (keyword) {
        sql += ' AND title ILIKE $1';
        params.push(`%${keyword}%`);
    }

    sql += ' LIMIT $' + (params.length + 1) + ' OFFSET $' + (params.length + 2);
    params.push(pageSize, offset);

    const result = await pool.query(sql, params);
    const total = await pool.query('SELECT COUNT(*) FROM course');

    res.json({
        code: 0,
        data: {
            list: result.rows,
            total: parseInt(total.rows[0].count),
            page: parseInt(page),
            pageSize: parseInt(pageSize)
        }
    });
});

// 详情
app.get('/api/courses/:id', async (req, res) => {
    const { id } = req.params;
    const result = await pool.query('SELECT * FROM course WHERE id = $1', [id]);

    if (result.rows.length === 0) {
        return res.status(404).json({ code: 404, msg: '课程不存在' });
    }

    res.json({ code: 0, data: result.rows[0] });
});

// 创建
app.post('/api/courses', async (req, res) => {
    const { title, description } = req.body;

    if (!title) {
        return res.status(400).json({ code: 400, msg: '标题不能为空' });
    }

    const result = await pool.query(
        'INSERT INTO course (title, description) VALUES ($1, $2) RETURNING *',
        [title, description]
    );

    res.status(201).json({ code: 0, data: result.rows[0] });
});

// 更新
app.put('/api/courses/:id', async (req, res) => {
    const { id } = req.params;
    const { title, description } = req.body;

    const result = await pool.query(
        'UPDATE course SET title = $1, description = $2 WHERE id = $3 RETURNING *',
        [title, description, id]
    );

    if (result.rows.length === 0) {
        return res.status(404).json({ code: 404, msg: '课程不存在' });
    }

    res.json({ code: 0, data: result.rows[0] });
});

// 删除
app.delete('/api/courses/:id', async (req, res) => {
    const { id } = req.params;
    const result = await pool.query('DELETE FROM course WHERE id = $1 RETURNING id', [id]);

    if (result.rows.length === 0) {
        return res.status(404).json({ code: 404, msg: '课程不存在' });
    }

    res.status(204).send();
});

// 错误处理
app.use((err, req, res, next) => {
    console.error(err.stack);
    res.status(500).json({ code: 500, msg: '服务器错误' });
});

app.listen(3000, () => {
    console.log('服务器运行在 http://localhost:3000');
});

测试

# 创建
curl -X POST http://localhost:3000/api/courses \
  -H "Content-Type: application/json" \
  -d '{"title":"React 入门","description":"从零开始学 React"}'

# 列表
curl http://localhost:3000/api/courses?page=1&pageSize=10

# 搜索
curl http://localhost:3000/api/courses?keyword=React

# 详情
curl http://localhost:3000/api/courses/1

# 更新
curl -X PUT http://localhost:3000/api/courses/1 \
  -H "Content-Type: application/json" \
  -d '{"title":"React 进阶","description":"深入学习 React"}'

# 删除
curl -X DELETE http://localhost:3000/api/courses/1

常见卡点

1. CORS 跨域：安装 cors 中间件，app.use(cors())。
2. 参数拿不到：确认 app.use(express.json()) 在路由前。
3. SQL 注入：用 $1、$2 占位符，不要拼接字符串。

加分项

• 用 Redis 缓存热门课程
• 用 JWT 实现认证
• 用 Joi 做参数校验

下一步

完成本课后，进入「认证与授权」阶段，给 API 加登录保护。