TiDB 实战项目：从需求分析到生产级代码完整记录

一、前言TiDB 实战项目从需求分析到生产级代码完整记录。本文从实际项目出发给出完整可运行的代码帮你快速掌握实战技能。二、需求分析与架构设计2.1 业务需求功能需求 - 用户注册/登录支持邮箱和手机号 - JWT 无状态认证支持 RefreshToken 续期 - RBAC 权限控制超级管理员/普通用户/访客 - 操作日志审计 非功能需求 - 支持 1000 并发 QPS - 接口响应时间 P99 200ms - 99.9% 可用性2.2 技术选型语言框架Node.js Fastify或 技术 数据库PostgreSQL Redis 认证JWT (access_token 15min, refresh_token 7d) ORMPrisma类型安全、自动迁移 APIRESTful OpenAPI 文档 部署Docker K8s三、核心功能实现3.1 数据库设计与建模-- 用户表 CREATE TABLE users ( id UUID PRIMARY KEY DEFAULT gen_random_uuid(), username VARCHAR(64) NOT NULL UNIQUE, email VARCHAR(255) NOT NULL UNIQUE, password_hash VARCHAR(255) NOT NULL, role VARCHAR(32) NOT NULL DEFAULT user, created_at TIMESTAMP DEFAULT NOW(), updated_at TIMESTAMP DEFAULT NOW(), CONSTRAINT email_format CHECK (email ~* ^[A-Za-z0-9._%-][A-Za-z0-9.-].[A-Za-z]{2,}$) ); -- 角色表 CREATE TABLE roles ( id SERIAL PRIMARY KEY, name VARCHAR(32) NOT NULL UNIQUE, permissions JSONB NOT NULL DEFAULT [] ); -- 操作日志表 CREATE TABLE audit_logs ( id BIGSERIAL PRIMARY KEY, user_id UUID REFERENCES users(id), action VARCHAR(128) NOT NULL, resource VARCHAR(256), details JSONB, ip_address INET, created_at TIMESTAMP DEFAULT NOW() ); -- 索引 CREATE INDEX idx_users_email ON users(email); CREATE INDEX idx_users_role ON users(role); CREATE INDEX idx_audit_user ON audit_logs(user_id, created_at DESC);3.2 用户认证服务// 技术 用户认证服务 const bcrypt require(bcrypt); const jwt require(jsonwebtoken); class AuthService { constructor(db, redis) { this.db db; this.redis redis; } async register({ username, email, password, role user }) { // 1. 校验唯一性 const existing await this.db.query( SELECT id FROM users WHERE username$1 OR email$2, [username, email] ); if (existing.rows.length 0) { throw new Error(用户名或邮箱已存在); } // 2. 密码哈希bcryptcost12 const password_hash await bcrypt.hash(password, 12); // 3. 创建用户 const result await this.db.query( INSERT INTO users (username, email, password_hash, role) VALUES ($1, $2, $3, $4) RETURNING id, username, email, role, [username, email, password_hash, role] ); // 4. 生成 Token const user result.rows[0]; return this.issueTokens(user); } async login({ email, password }) { const result await this.db.query( SELECT * FROM users WHERE email$1, [email] ); const user result.rows[0]; if (!user || !(await bcrypt.compare(password, user.password_hash))) { throw new Error(邮箱或密码错误); } // 记录登录日志 await this.logAction(user.id, LOGIN, { email }); return this.issueTokens(user); } issueTokens(user) { const accessToken jwt.sign( { user_id: user.id, role: user.role }, process.env.JWT_SECRET, { expiresIn: 15m } ); const refreshToken jwt.sign( { user_id: user.id, type: refresh }, process.env.JWT_REFRESH_SECRET, { expiresIn: 7d } ); // RefreshToken 黑名单注销时使用 return { accessToken, refreshToken }; } async logAction(userId, action, details) { await this.db.query( INSERT INTO audit_logs (user_id, action, details) VALUES ($1, $2, $3), [userId, action, JSON.stringify(details)] ); } }3.3 权限控制中间件// RBAC 权限检查 const ROLE_PERMISSIONS { admin: [users:read, users:write, users:delete, audit:read], user: [users:read, profile:write], guest: [] }; function authorize(...requiredPermissions) { return async (req, res, next) { const user req.user; if (!user) { return res.status(401).json({ error: 未认证 }); } const userPermissions ROLE_PERMISSIONS[user.role] || []; const hasPermission requiredPermissions.every(p userPermissions.includes(p) ); if (!hasPermission) { await authService.logAction(user.id, UNAUTHORIZED_ACCESS, { required: requiredPermissions, user_role: user.role }); return res.status(403).json({ error: 权限不足 }); } next(); }; } // 使用 app.get(/api/users, authenticate, // 认证 authorize(users:read), // 权限 async (req, res) { const users await userService.list(req.query); res.json(users); } );四、测试与质量保证4.1 单元测试// 技术 单元测试Jest describe(AuthService, () { let authService; let mockDb; let mockRedis; beforeEach(() { mockDb { query: jest.fn() }; mockRedis { set: jest.fn(), get: jest.fn() }; authService new AuthService(mockDb, mockRedis); }); test(register: 正常注册返回 Token, async () { mockDb.query .mockResolvedValueOnce({ rows: [] }) // 唯一性检查 .mockResolvedValueOnce({ // 创建用户 rows: [{ id: uuid-1, username: alice, email: ab.com, role: user }] }); const result await authService.register({ username: alice, email: ab.com, password: StrongPass123 }); expect(result).toHaveProperty(accessToken); expect(result).toHaveProperty(refreshToken); expect(mockDb.query).toHaveBeenCalledTimes(2); }); test(register: 重复邮箱抛异常, async () { mockDb.query.mockResolvedValueOnce({ rows: [{ id: existing-uuid }] }); await expect( authService.register({ username: alice, email: ab.com, password: pass }) ).rejects.toThrow(用户名或邮箱已存在); }); test(login: 错误密码抛异常, async () { mockDb.query.mockResolvedValueOnce({ rows: [{ id: uuid-1, password_hash: await bcrypt.hash(correct-password, 12) }] }); await expect( authService.login({ email: ab.com, password: wrong-password }) ).rejects.toThrow(邮箱或密码错误); }); });五、部署与运维5.1 Docker Compose 本地开发version: 3.8 services: app: build: . ports: - 8080:8080 environment: DATABASE_URL: postgresql://appuser:secretdb:5432/appdb REDIS_URL: redis://redis:6379/0 JWT_SECRET: ${JWT_SECRET} depends_on: db: condition: service_healthy redis: condition: service_started db: image: postgres:16-alpine environment: POSTGRES_DB: appdb POSTGRES_USER: appuser POSTGRES_PASSWORD: secret volumes: - pgdata:/var/lib/postgresql/data - ./init.sql:/docker-entrypoint-initdb.d/init.sql healthcheck: test: [CMD-SHELL, pg_isready -U appuser -d appdb] interval: 10s timeout: 5s retries: 5 redis: image: redis:7-alpine command: redis-server --appendonly yes volumes: - redisdata:/data volumes: pgdata: redisdata:5.2 GitHub Actions CI/CDname: CI/CD on: push: branches: [main] pull_request: branches: [main] jobs: test: runs-on: ubuntu-latest services: postgres: image: postgres:16 env: POSTGRES_DB: testdb POSTGRES_USER: test POSTGRES_PASSWORD: test options: - --health-cmd pg_isready --health-interval 10s steps: - uses: actions/checkoutv4 - uses: actions/setup-nodev4 with: node-version: 20 - run: npm ci - run: npm test -- --coverage - uses: codecov/codecov-actionv3 deploy: needs: test if: github.ref refs/heads/main runs-on: ubuntu-latest steps: - uses: actions/checkoutv4 - run: docker build -t app . - run: docker push ghcr.io/${{ github.repository }}:latest六、总结先设计再写代码数据库建模 API 接口设计在前测试驱动开发每个功能有测试提交前跑全量 suite日志要完整操作日志是排查问题的救命稻草环境隔离dev/staging/prod 配置要分开收藏本文关注我后续更新更多实战项目系列。觉得有用的话点个赞收藏关注我持续更新优质技术内容标签TiDB | 实战 | 项目 | 完整 | 后端