资讯详情

深入了解 · 专业解读

Nest.js与MySQL整合开发实战指南

发布时间：2026/7/22 5:54:18
Nest.js与MySQL整合开发实战指南 1. Nest.js 与 MySQL 数据库开发全景指南作为一款基于 TypeScript 的渐进式 Node.js 框架Nest.js 近年来在企业级应用开发中崭露头角。它完美融合了面向对象编程OOP、函数式编程FP和响应式编程RP的优势特别适合构建高效、可扩展的后端服务。而 MySQL 作为最流行的关系型数据库之一其稳定性和性能已经过无数项目的验证。本文将带你从零开始完整掌握 Nest.js 与 MySQL 的整合开发流程。提示本文假设读者已具备基本的 JavaScript/TypeScript 知识。若尚未安装 Node.js 环境建议先访问 Node.js 官网获取 LTS 版本当前推荐 v18.x。1.1 环境准备与项目初始化首先通过命令行创建新项目确保已安装 Node.js 16 和 npm 8npm i -g nestjs/cli nest new nest-mysql-demo cd nest-mysql-demo安装 MySQL 驱动和 TypeORM推荐的关系型数据库 ORMnpm install nestjs/typeorm typeorm mysql2这里选择 TypeORM 而非 Sequelize 或 Prisma 的主要原因在于原生 TypeScript 支持与 Nest.js 生态契合度高活跃的社区维护和丰富的文档资源支持数据迁移、事务处理等企业级功能装饰器语法与 Nest.js 风格高度一致1.2 数据库连接配置在app.module.ts中配置数据库连接import { TypeOrmModule } from nestjs/typeorm; Module({ imports: [ TypeOrmModule.forRoot({ type: mysql, host: localhost, port: 3306, username: root, password: yourpassword, database: test_db, entities: [__dirname /**/*.entity{.ts,.js}], synchronize: true, // 开发环境可用生产环境务必关闭 }), ], }) export class AppModule {}警告synchronize: true会自动同步实体到数据库结构虽然方便开发但可能导致生产环境数据丢失。正式部署时应使用迁移工具。2. 数据模型与 CRUD 实现2.1 实体定义创建用户实体user.entity.tsimport { Entity, PrimaryGeneratedColumn, Column } from typeorm; Entity() export class User { PrimaryGeneratedColumn() id: number; Column({ length: 50 }) username: string; Column({ unique: true }) email: string; Column({ select: false }) // 查询时默认排除密码字段 password: string; Column({ default: () CURRENT_TIMESTAMP }) createdAt: Date; }2.2 服务层实现创建用户服务user.service.tsimport { Injectable } from nestjs/common; import { InjectRepository } from nestjs/typeorm; import { Repository } from typeorm; import { User } from ./user.entity; Injectable() export class UserService { constructor( InjectRepository(User) private userRepository: RepositoryUser, ) {} async findAll(): PromiseUser[] { return this.userRepository.find(); } async findOne(id: number): PromiseUser { return this.userRepository.findOne({ where: { id } }); } async create(user: PartialUser): PromiseUser { return this.userRepository.save(user); } async update(id: number, user: PartialUser): Promisevoid { await this.userRepository.update(id, user); } async remove(id: number): Promisevoid { await this.userRepository.delete(id); } }2.3 控制器设计用户控制器user.controller.ts示例import { Controller, Get, Post, Body, Param, Put, Delete } from nestjs/common; import { UserService } from ./user.service; import { User } from ./user.entity; Controller(users) export class UserController { constructor(private readonly userService: UserService) {} Get() async findAll(): PromiseUser[] { return this.userService.findAll(); } Post() async create(Body() user: User): PromiseUser { return this.userService.create(user); } Put(:id) async update(Param(id) id: string, Body() user: PartialUser): Promisevoid { await this.userService.update(id, user); } Delete(:id) async remove(Param(id) id: string): Promisevoid { await this.userService.remove(id); } }3. 高级特性与性能优化3.1 事务处理TypeORM 提供多种事务管理方式。以下是使用QueryRunner的示例async transferMoney(fromId: number, toId: number, amount: number) { const queryRunner this.dataSource.createQueryRunner(); await queryRunner.connect(); await queryRunner.startTransaction(); try { const fromAccount await queryRunner.manager.findOne(User, { where: { id: fromId } }); const toAccount await queryRunner.manager.findOne(User, { where: { id: toId } }); // 业务逻辑校验 if (fromAccount.balance amount) { throw new Error(Insufficient balance); } // 执行转账 fromAccount.balance - amount; toAccount.balance amount; await queryRunner.manager.save(fromAccount); await queryRunner.manager.save(toAccount); await queryRunner.commitTransaction(); } catch (err) { await queryRunner.rollbackTransaction(); throw err; } finally { await queryRunner.release(); } }3.2 查询构建器复杂查询建议使用 QueryBuilderasync findActiveUsers(minPosts: number): PromiseUser[] { return this.userRepository .createQueryBuilder(user) .leftJoinAndSelect(user.posts, post) .where(user.isActive :isActive, { isActive: true }) .andWhere(post.createdAt :date, { date: new Date(Date.now() - 30 * 24 * 60 * 60 * 1000) }) .groupBy(user.id) .having(COUNT(post.id) :minPosts, { minPosts }) .getMany(); }3.3 连接池优化在TypeOrmModule.forRoot()配置中添加连接池参数extra: { connectionLimit: 20, // 最大连接数 queueLimit: 100, // 等待队列最大数量 connectTimeout: 5000 // 连接超时时间(ms) }4. 常见问题排查与调试技巧4.1 连接失败排查当出现ER_NOT_SUPPORTED_AUTH_MODE错误时可能是 MySQL 8.0 的认证方式问题# 登录MySQL后执行 ALTER USER rootlocalhost IDENTIFIED WITH mysql_native_password BY yourpassword; FLUSH PRIVILEGES;4.2 性能问题定位启用 TypeORM 日志记录TypeOrmModule.forRoot({ // ...其他配置 logging: [query, error], logger: advanced-console, maxQueryExecutionTime: 1000, // 慢查询阈值(ms) })4.3 数据迁移管理安装 typeorm 命令行工具npm install -g typeorm创建迁移文件typeorm migration:create -n UserTable执行迁移typeorm migration:run5. 安全最佳实践5.1 SQL 注入防护TypeORM 已内置参数化查询但直接使用原始 SQL 时仍需注意// 危险可能被注入 this.userRepository.query(SELECT * FROM users WHERE name ${name}); // 安全方式 this.userRepository.query(SELECT * FROM users WHERE name ?, [name]);5.2 敏感数据处理使用Column({ select: false })排除密码字段Column({ select: false }) password: string;需要时显式查询this.userRepository.findOne({ where: { id }, select: [id, username, password] // 明确指定需要返回的字段 });5.3 生产环境配置通过环境变量管理敏感信息推荐使用nestjs/configimport { ConfigModule, ConfigService } from nestjs/config; TypeOrmModule.forRootAsync({ imports: [ConfigModule], useFactory: (config: ConfigService) ({ type: mysql, host: config.get(DB_HOST), port: config.get(DB_PORT), username: config.get(DB_USER), password: config.get(DB_PASSWORD), database: config.get(DB_NAME), }), inject: [ConfigService], })6. 项目结构与扩展建议6.1 推荐项目结构src/ ├── entities/ # 数据库实体 ├── repositories/ # 自定义Repository ├── services/ # 业务逻辑 ├── controllers/ # API端点 ├── dtos/ # 数据传输对象 ├── interceptors/ # 拦截器 ├── filters/ # 异常过滤器 └── modules/ # 功能模块6.2 性能监控集成安装 Prometheus 监控npm install nestjs/metrics prom-client配置指标收集import { PrometheusModule } from nestjs/metrics; Module({ imports: [ PrometheusModule.register({ defaultMetrics: { enabled: true, }, }), ], }) export class AppModule {}6.3 单元测试示例使用 Jest 测试服务层describe(UserService, () { let service: UserService; let repository: RepositoryUser; beforeEach(async () { const module: TestingModule await Test.createTestingModule({ providers: [ UserService, { provide: getRepositoryToken(User), useValue: { find: jest.fn().mockResolvedValue([mockUser]), findOne: jest.fn().mockResolvedValue(mockUser), save: jest.fn().mockResolvedValue(mockUser), update: jest.fn().mockResolvedValue({ affected: 1 }), delete: jest.fn().mockResolvedValue({ affected: 1 }), }, }, ], }).compile(); service module.getUserService(UserService); repository module.getRepositoryUser(getRepositoryToken(User)); }); it(should return array of users, async () { await expect(service.findAll()).resolves.toEqual([mockUser]); expect(repository.find).toHaveBeenCalled(); }); });7. 部署与持续集成7.1 Docker 容器化Dockerfile示例FROM node:18-alpine WORKDIR /app COPY package*.json ./ RUN npm ci --onlyproduction COPY . . EXPOSE 3000 CMD [npm, run, start:prod]docker-compose.yml包含 MySQLversion: 3 services: app: build: . ports: - 3000:3000 depends_on: - db environment: DB_HOST: db db: image: mysql:8.0 environment: MYSQL_ROOT_PASSWORD: rootpass MYSQL_DATABASE: test_db volumes: - mysql_data:/var/lib/mysql volumes: mysql_data:7.2 健康检查端点添加健康检查路由Get(health) HealthCheck() async check() { try { await this.dataSource.query(SELECT 1); return { status: ok, database: connected }; } catch (e) { throw new HttpException( { status: down, database: disconnected }, HttpStatus.SERVICE_UNAVAILABLE, ); } }8. 性能优化实战8.1 索引优化为常用查询字段添加索引Index(IDX_USER_EMAIL, [email], { unique: true }) Index(IDX_USER_USERNAME, [username]) Entity() export class User { // ... }8.2 缓存策略集成 Redis 缓存npm install cache-manager cache-manager-redis-store nestjs/cache-manager配置缓存模块import { CacheModule } from nestjs/cache-manager; import * as redisStore from cache-manager-redis-store; Module({ imports: [ CacheModule.register({ store: redisStore, host: localhost, port: 6379, ttl: 60, // 默认缓存时间(秒) }), ], }) export class AppModule {}使用缓存装饰器CacheKey(all_users) CacheTTL(30) async findAll(): PromiseUser[] { return this.userRepository.find(); }8.3 批量操作优化使用批量插入代替循环插入async bulkCreate(users: User[]) { await this.userRepository .createQueryBuilder() .insert() .into(User) .values(users) .execute(); }9. 日志与错误处理9.1 结构化日志安装 Winston 日志库npm install nest-winston winston配置日志模块import { WinstonModule } from nest-winston; import * as winston from winston; Module({ imports: [ WinstonModule.forRoot({ transports: [ new winston.transports.Console({ format: winston.format.combine( winston.format.timestamp(), winston.format.json(), ), }), ], }), ], }) export class AppModule {}9.2 全局异常过滤创建自定义异常过滤器import { ExceptionFilter, Catch, ArgumentsHost } from nestjs/common; import { QueryFailedError } from typeorm; Catch(QueryFailedError) export class DatabaseExceptionFilter implements ExceptionFilter { catch(exception: QueryFailedError, host: ArgumentsHost) { const ctx host.switchToHttp(); const response ctx.getResponse(); response.status(500).json({ statusCode: 500, message: Database operation failed, error: exception.message, }); } }全局注册过滤器async function bootstrap() { const app await NestFactory.create(AppModule); app.useGlobalFilters(new DatabaseExceptionFilter()); await app.listen(3000); }10. 现代化开发实践10.1 使用 DataLoader 解决 N1 问题安装 DataLoadernpm install dataloader nestjs/graphql创建用户加载器Injectable() export class UserLoader { constructor(private userService: UserService) {} createBatchUsers() { return new DataLoadernumber, User(async (userIds) { const users await this.userService.findByIds([...userIds]); const userMap new Map(users.map(user [user.id, user])); return userIds.map(id userMap.get(id)); }); } }10.2 OpenAPI 文档集成安装 Swagger 模块npm install nestjs/swagger配置 Swaggerimport { SwaggerModule, DocumentBuilder } from nestjs/swagger; async function bootstrap() { const app await NestFactory.create(AppModule); const config new DocumentBuilder() .setTitle(User API) .setDescription(The user API description) .setVersion(1.0) .addTag(users) .build(); const document SwaggerModule.createDocument(app, config); SwaggerModule.setup(api, app, document); await app.listen(3000); }10.3 领域驱动设计实践使用模块组织领域逻辑Module({ imports: [TypeOrmModule.forFeature([User])], providers: [UserService, UserRepository], controllers: [UserController], exports: [UserService], }) export class UserModule {}自定义 Repository 实现复杂查询EntityRepository(User) export class UserRepository extends RepositoryUser { async findActiveUsers(): PromiseUser[] { return this.createQueryBuilder(user) .where(user.isActive :isActive, { isActive: true }) .getMany(); } }

相关新闻