Nest.js向PostgreSQL保存记录时出现Validation error问题求助

在开发NestJS接口过程中，尝试将用户记录保存到PostgreSQL数据库时触发Validation error，错误日志如下：

Executing (default): INSERT INTO "Users" ("id","name","email","password","createdAt","updatedAt") VALUES (DEFAULT,$1,$2,$3,$4,$5) RETURNING "id","name","email","password","createdAt","updatedAt";
[Nest] 17906  - 10/04/2023, 2:37:20 PM   ERROR [ExceptionsHandler] Validation error
Error: 
    at Query.run (/Users/grahammorby/Documents/GitHub/dcuk-api/node_modules/sequelize/src/dialects/postgres/query.js:76:25)
    at /Users/grahammorby/Documents/GitHub/dcuk-api/node_modules/sequelize/src/sequelize.js:650:28
    at processTicksAndRejections (node:internal/process/task_queues:95:5)
    at PostgresQueryInterface.insert (/Users/grahammorby/Documents/GitHub/dcuk-api/node_modules/sequelize/src/dialects/abstract/query-interface.js:795:21)
    at User.save (/Users/grahammorby/Documents/GitHub/dcuk-api/node_modules/sequelize/src/model.js:4153:35)
    at Function.create (/Users/grahammorby/Documents/GitHub/dcuk-api/node_modules/sequelize/src/model.js:2305:12)
    at UsersService.create (/Users/grahammorby/Documents/GitHub/dcuk-api/src/core/modules/users/users.service.ts:12:16)
    at AuthService.create (/Users/grahammorby/Documents/GitHub/dcuk-api/src/core/modules/auth/auth.service.ts:41:25)
    at AuthController.signUp (/Users/grahammorby/Documents/GitHub/dcuk-api/src/core/modules/auth/auth.controller.ts:18:16)
    at /Users/grahammorby/Documents/GitHub/dcuk-api/node_modules/@nestjs/core/router/router-execution-context.js:46:28

相关代码 #

Auth Service #

public async create(user) {
   // hash the password
    const pass = await this.hashPassword(user.password);

    // create the user
    const newUser = await this.userService.create({ ...user, password: pass });

    // tslint:disable-next-line: no-string-literal
    const { password, ...result } = newUser['dataValues'];

    // generate token
    const token = await this.generateToken(result);

    // return the user and the token
    return { user: result, token };
}

private async generateToken(user) {
    const token = await this.jwtService.signAsync(user);
    return token;
}

private async hashPassword(password) {
    const hash = await bcrypt.hash(password, 10);
    return hash;
}

UserDto #

import { IsNotEmpty, MinLength, IsEmail } from "class-validator";

export class UserDto {
    @IsNotEmpty()
    readonly name: string;

    @IsNotEmpty()
    @IsEmail()
    readonly email: string;

    @IsNotEmpty()
    @MinLength(6)
    readonly password: string;
}

User Service #

async create(user: UserDto): Promise<User> {
    return await this.userRepository.create<User>(user);
}

排查原因及解决方法 #

• 模型与数据库字段不匹配
  检查User模型定义，确认name、email、password等字段的约束（是否允许为空、字段类型/长度、唯一性等）和数据库表完全一致。比如模型设置email为唯一但数据库未创建索引，会触发验证错误。解决：开发环境可使用sequelize.sync({ alter: true })自动同步表结构，生产环境手动调整数据库表。

• 哈希密码长度超出字段限制
  bcrypt生成的哈希固定为60位，若数据库password字段长度设置小于60（如VARCHAR(50)），插入时会报错。解决：将数据库password字段改为VARCHAR(60)或TEXT，同步更新Sequelize模型的字段长度配置。

• ValidationPipe未正确配置
  若未全局或控制器级启用ValidationPipe，不符合UserDto规则的参数（如空name、无效email）会直接进入服务层，触发数据库验证错误。解决：在main.ts中全局启用验证管道：

  import { ValidationPipe } from '@nestjs/common';
  
  async function bootstrap() {
    const app = await NestFactory.create(AppModule);
    app.useGlobalPipes(new ValidationPipe({ whitelist: true }));
    await app.listen(3000);
  }
  bootstrap();
  

• Sequelize模型自定义验证未满足
  若User模型定义了额外验证规则（如自定义函数），传入参数不符合规则会触发错误。解决：检查模型的验证配置，确保传入的用户数据满足所有规则。

内容的提问来源于stack exchange，提问作者Graham Morby