The problem with "let the database validate"
Some codebases have a pattern like this:
// Service does no validation
async createUser(dto: CreateUserDto) {
return this.userRepository.save(dto);
}
The reasoning: "If email is null, the database NOT NULL constraint will reject it. If username is too long, varchar(20) will block it. If email is a duplicate, the UNIQUE constraint will fail."
The database will catch these. But it's the wrong approach — not because the end result is different, but because the database is not a validation layer.
Why relying on the database for validation is wrong
1. Database errors are not user-facing errors
When a database constraint fires, the backend receives a technical exception:
ER_DUP_ENTRY: Duplicate entry '[email protected]' for key 'users.UQ_email'
ER_DATA_TOO_LONG: Data too long for column 'username' at row 1
ER_BAD_NULL_ERROR: Column 'email' cannot be null
To return a clean HTTP 400 with a friendly message, the code must:
- Catch the exception from the ORM
- Parse the error code and message
- Map each case individually
- Format the response
// Handling DB errors — messy, brittle, incomplete
try {
return await this.userRepository.save(dto);
} catch (err) {
if (err.code === 'ER_DUP_ENTRY') {
if (err.sqlMessage.includes('UQ_email')) {
throw new ConflictException('Email already in use');
}
}
if (err.code === 'ER_DATA_TOO_LONG') {
// parse sqlMessage to find which column?
throw new BadRequestException('Input too long');
}
throw err; // what else? no idea
}
Compare that to DTO-level validation:
// DTO validation — clear, complete, fully controlled
export class CreateUserDto {
@IsEmail()
@IsNotEmpty()
email: string;
@MaxLength(20)
@IsNotEmpty()
username: string;
}
NestJS automatically throws BadRequestException with per-field messages. No catch block needed.
2. By the time the DB rejects, resources are already spent
Letting the database catch errors means every invalid request must:
- Open a connection
- Send the query to the DB
- Let the DB parse the query
- Let the DB check the constraint
- Receive the rejection
Validating at the application layer rejects invalid requests before they touch the database — real load reduction.
For small systems this doesn't matter much. But it's the correct architectural principle: garbage should be rejected as early as possible in the pipeline.
3. DB constraints and business rules are different things
Database constraints are good at protecting schema integrity. But business rules are often much more complex:
// These validations can't be done at the DB level
if (dto.endDate <= dto.startDate) {
throw new BadRequestException('End date must be after start date');
}
if (dto.discountPercent > 0 && !dto.promoCode) {
throw new BadRequestException('A promo code is required for discounts');
}
if (user.role === 'free' && dto.fileSize > 10 * 1024 * 1024) {
throw new BadRequestException('Free accounts can only upload up to 10MB');
}
If validation is split — some things handled by DB constraints, some by service code — the behavior is scattered and harder to reason about.
4. Testing is harder
Testing business logic that depends on DB constraints requires a real database, real seeds, and integration tests. Unit testing the service layer in isolation isn't possible:
// Has to be an integration test with a real DB to know if it validates correctly
it('should reject email > 255 chars', async () => {
await expect(
service.createUser({ email: 'a'.repeat(256) + '@x.com', ... })
).rejects.toThrow(); // rejected by the DB or by logic? unclear
});
With DTO/service validation, this is a fast unit test that needs no database:
it('should reject email > 255 chars', () => {
const dto = plainToClass(CreateUserDto, { email: 'a'.repeat(256) + '@x.com' });
const errors = validateSync(dto);
expect(errors[0].property).toBe('email');
});
5. Changing DB constraints is more expensive than changing code
When a business rule changes — say, raising the username limit from 20 to 50 characters — a migration is required:
ALTER TABLE users MODIFY COLUMN username VARCHAR(50) NOT NULL;
On large tables this can lock the table, requiring downtime or complex online DDL. If the rule lives in code:
@MaxLength(50) // change this number
username: string;
Deploy the new code and it's done — no migration, no lock, no risk.
DB constraints still have a role — but a different one
DB constraints aren't useless. Their correct role is as the last line of defense for data integrity, not as the primary validation layer.
| Code validation | DB constraint | |
|---|---|---|
| Purpose | Catch invalid user input | Protect schema data integrity |
| When it runs | Before the DB is touched | When the DB receives the query |
| Error message | Fully controlled, user-friendly | Technical error, needs parsing |
| Testing | Unit test, fast, no DB needed | Requires a real DB |
| Changing the rule | Deploy code | Migration |
| Business logic | Fully expressible | Not possible |
Example: a UNIQUE constraint on the email column should still exist in the DB — not to validate input, but to prevent race conditions where two concurrent requests both pass code validation and both try to insert the same email. The DB constraint here is a safety net at the final layer.
Where to validate in NestJS
DTO layer — format and structure
import { IsEmail, IsNotEmpty, MaxLength, MinLength, IsOptional, IsInt, Min, Max } from 'class-validator';
export class CreateUserDto {
@IsEmail({}, { message: 'Invalid email format' })
@IsNotEmpty()
email: string;
@MinLength(3, { message: 'Username must be at least 3 characters' })
@MaxLength(50, { message: 'Username cannot exceed 50 characters' })
@IsNotEmpty()
username: string;
@IsOptional()
@IsInt()
@Min(0)
@Max(120)
age?: number;
}
Enable ValidationPipe globally:
// main.ts
app.useGlobalPipes(new ValidationPipe({
whitelist: true, // strip extra fields
forbidNonWhitelisted: true, // throw if extra fields are present
transform: true, // auto-transform types
}));
Invalid requests automatically receive a 400 response:
{
"statusCode": 400,
"message": ["Invalid email format", "Username must be at least 3 characters"],
"error": "Bad Request"
}
No catch block. No parsing. Nothing to handle in the service.
Service layer — business rules
DTOs validate format and structure. Business rules involving system state, cross-field logic, or database lookups belong in the service:
@Injectable()
export class UserService {
constructor(private readonly userRepository: UserRepository) {}
async createUser(dto: CreateUserDto) {
// Business rule: check for duplicate email
const existing = await this.userRepository.findByEmail(dto.email);
if (existing) {
throw new ConflictException('Email is already in use');
}
// Business rule: username format beyond what regex decorators cover
if (!/^[a-z0-9_]+$/.test(dto.username)) {
throw new BadRequestException('Username may only contain lowercase letters, digits, and underscores');
}
return this.userRepository.save(dto);
}
}
DB layer — integrity only
-- Safety net, not primary validation
ALTER TABLE users ADD CONSTRAINT UQ_email UNIQUE (email);
ALTER TABLE orders ADD CONSTRAINT FK_orders_users
FOREIGN KEY (user_id) REFERENCES users(id);
Concrete example: VARCHAR(6) and a 7-character value
Say a status column has VARCHAR(6) with allowed values: active, paused, banned.
// Wrong: let the DB catch it
async updateStatus(id: number, status: string) {
await this.userRepository.update(id, { status });
// If status = 'deleted' (7 chars) → DB throws ER_DATA_TOO_LONG
// If status = 'ACTIVE' (6 chars) → saves but wrong value
}
// Correct: validate at the DTO layer
export class UpdateStatusDto {
@IsIn(['active', 'paused', 'banned'], { message: 'Invalid status value' })
status: string;
}
Now:
'deleted'→ 400 immediately at DTO validation, DB never sees the request'ACTIVE'→ 400 with a clear message'active'→ passes, stored correctly
No ER_DATA_TOO_LONG to catch. No sqlMessage to parse. No DB work wasted.
Summary
Validate in the codebase — DTO layer and service layer — not in the database. DB constraints are still necessary, but their job is protecting data integrity, not catching bad input.
Simple rule:
- DTO: format, data types, length limits, patterns, enum values
- Service: business rules involving state, system lookups, cross-field logic
- DB constraints: UNIQUE, FK, NOT NULL — the final safety net, not the primary gate