Database Validation - controlling data quality

A legionary with no name entered the treasury yesterday. Today - two identical entries for the same tribute. Consul Caesar.js is furious: we do validate data in the DTOs! True - but validation in the API layer guards only one gate. Data enters the database by other roads too: through a seeder, through another service, through an administrator's manual query. That is why a mature system posts its guards in layers: the DTO checks the shape of the request, the service enforces business rules, and the database is the last line of defence - a wall nobody can walk around. In this lesson we build exactly that wall.

Rules written into the columns

The first line of the wall is the column definitions. The @Column decorator takes options which the database turns into hard constraints:

1@Entity()
2export class Legionary {
3  @Column({ length: 100, nullable: false })
4  name: string;
5
6  @Column({ unique: true })
7  codeName: string;
8
9  @Column({ type: 'int', default: 0 })
10  tributeCount: number;
11
12  @Column({ type: 'boolean', default: true })
13  isActive: boolean;
14}

Let's read those options as orders to the database. nullable: false is SQL's NOT NULL - a record with no name is rejected before it touches the table. unique: true creates a uniqueness constraint: a second legionary with the same codeName raises a database error rather than becoming a silent duplicate. default fills in a value when none is given - a new legionary starts with zero tributes without writing that in code. And type with length match the column type to the data: an integer for a counter, a length-limited text for a name. Note that these rules always apply - no matter which road the data takes in.

Indexes - the treasury's table of contents

Since codeName must be unique, the database has already built an index for it. But an index also helps where we search often without requiring uniqueness - there we add the @Index decorator ourselves:

1@Entity()
2@Index(['rank', 'isActive'])
3export class Legionary {
4  @Index()
5  @Column()
6  lastName: string;
7}

An index works like the table of contents of an inventory ledger: instead of leafing through the whole table, the database jumps straight to the right page. A single @Index() above a column speeds up searching by lastName; a composite index above the class - queries filtering by rank and activity at once. This is the simplest query optimisation in this lesson: no changes in service code, and queries with a where on those columns stop scanning the entire table. There is one price - every write must update the index too - which is why we index the columns we really search by, not all of them.

Timestamps - a self-writing chronicle

When was this record created? When was it last changed? These two columns we never fill in by hand:

1@CreateDateColumn()
2createdAt: Date;
3
4@UpdateDateColumn()
5updatedAt: Date;

@CreateDateColumn receives its date once, when the record is created, and never changes again. @UpdateDateColumn refreshes on every save. The chronicle writes itself - and you gain the answer to "what happened here recently" before anyone asks the question.

Entity hooks - a ritual before saving

Sometimes something must be taken care of before a save: trimming spaces, normalising letter case, computing a derived field. That is what hooks are for - entity methods marked with a decorator, which TypeORM calls itself at the right moment:

1@Entity()
2export class Legionary {
3  @Column()
4  codeName: string;
5
6  @BeforeInsert()
7  @BeforeUpdate()
8  normalizeCodeName() {
9    this.codeName = this.codeName.trim().toLowerCase();
10  }
11}

@BeforeInsert fires the method just before a record's first save, @BeforeUpdate - before every update. Here both decorators sit above one method, so the normalisation works in both situations: however sloppily codeName was typed, the database receives a trimmed, lowercase version. An important boundary: a hook lives inside the entity and sees only itself. When you need to react to saves across many entities in one place (logging every change in the treasury, say), TypeORM offers subscribers - separate classes listening to events across the whole database. Remember the split: a hook is one entity's ritual, a subscriber is the whole system's observer.

Soft delete - exile instead of execution

The order "delete this legionary" is often hasty. A plain delete removes the record for good - and with it the tribute history and the relations. That is why mature systems use soft delete: the record gets a deletion marker but physically stays in the table. In TypeORM one column is enough:

1@Entity()
2export class Legionary {
3  @DeleteDateColumn()
4  deletedAt: Date;
5}
6
7// soft removal - sets deletedAt, the record stays
8await legionaryRepository.softDelete(legionaryId);
9
10// restoring - clears deletedAt
11await legionaryRepository.restore(legionaryId);
12
13// finding the deleted ones too
14const all = await legionaryRepository.find({ withDeleted: true });

The presence of @DeleteDateColumn changes the behaviour of the whole repository: softDelete writes a date into deletedAt, and every ordinary find starts skipping records with that date set automatically - the deleted legionary vanishes from results while still sitting in the table. restore clears the marker and the legionary returns to service. And when an auditor wants to see everyone, the exiled included - they add the withDeleted: true option. Execution has become exile: reversible, and leaving a trace.

Cascade - the fate of related records

One guard's decision remains: what about the tributes when their owner disappears? We write the answer into the relation definition:

1@OneToMany(() => Tribute, (tribute) => tribute.legionariusze, {
2  cascade: true,
3})
4tributes: Tribute[];
5
6@ManyToOne(() => Legionary, { onDelete: 'CASCADE' })
7legionariusze: Legionary;

These are two different cascades and they are worth telling apart. cascade: true acts on save: saving a legionary with new tributes in the array makes TypeORM save both him and them - in one move. onDelete: 'CASCADE' acts on deletion, on the database side: when a legionary really disappears, the database removes his tributes itself, leaving no orphan records. Alternatives to 'CASCADE' include 'SET NULL' - the tribute stays but loses its owner. The choice is yours - but make it deliberately, because delete cascade is the sharpest tool in this lesson.

findAndCount - pagination on the repository side

Finally we tie data quality to something you know from the Query Builder lesson: serving results in portions. The repository has a ready pair of methods rolled into one:

1const [legionaries, total] = await legionaryRepository.findAndCount({
2  where: { isActive: true },
3  order: { name: 'ASC' },
4  skip: (page - 1) * limit,
5  take: limit,
6});

findAndCount returns a two-element array: the list of results and the total number of matching records. Writing const [legionaries, total] = ... is destructuring - unpacking the pair into two named variables in a single line. The rest looks like an ordinary find: skip skips previous pages, take fetches a portion. It is the repository twin of getManyAndCount() from the Query Builder - a simpler form for simpler cases; when the conditions get complicated, you go back to the builder.

Summary

The wall around the treasury stands. You laid it in layers:

• @Column options - nullable, unique, default, type and length - hard rules enforced by the database itself,
• @Index - a table of contents for the columns you search by; faster reads at the cost of writes,
• @CreateDateColumn and @UpdateDateColumn - a chronicle that writes itself,
• @BeforeInsert and @BeforeUpdate - an entity's ritual before saving; subscribers - an observer of database-wide events,
• @DeleteDateColumn with softDelete, restore and withDeleted - exile instead of execution,
• cascade on save and onDelete: 'CASCADE' on delete - a deliberate fate for related records,
• findAndCount with [results, count] destructuring - pagination without the Query Builder.

Ahead of you is the project closing this module: a tribute inventory system where these guarding mechanisms meet in one codebase. For now remember: DTO validation guards the gate, but the rules in the database are the wall - they always hold, whichever road the data tries to take.

1// Database Validation - Data Quality Control
2import {
3  Entity, Column, PrimaryGeneratedColumn, Index,
4  CreateDateColumn, UpdateDateColumn, DeleteDateColumn,
5  Check, BeforeInsert, BeforeUpdate, AfterInsert,
6} from 'typeorm';
7
8console.log("Database validation - the last line of defense!");
9
10// ===========================================
11// 1. Constraints on columns
12// ===========================================
13
14@Entity('tribute_records')
15@Index(['provincia', 'year']) // Composite index
16export class TributeRecord {
17  @PrimaryGeneratedColumn()
18  id: number;
19
20  @Column({ length: 100 })
21  provincia: string;
22
23  @Column({ type: 'decimal', precision: 12, scale: 2 })
24  @Check('"amount" > 0') // Value must be positive
25  amount: number;
26
27  @Column({ type: 'int' })
28  year: number;
29
30  @Index() // Index on the column
31  @Column({ length: 100 })
32  collector: string;
33
34  @Column({ type: 'enum', enum: ['gold', 'silver', 'goods'], default: 'gold' })
35  type: string;
36
37  @CreateDateColumn()
38  createdAt: Date;
39}
40
41// ===========================================
42// 2. Entity hooks (lifecycle events)
43// ===========================================
44
45@Entity('centurions')
46export class Centurion {
47  @PrimaryGeneratedColumn()
48  id: number;
49
50  @Column()
51  name: string;
52
53  @Column()
54  rank: string;
55
56  @Column({ default: 0 })
57  experience: number;
58
59  @Column({ nullable: true })
60  slug: string;
61
62  @CreateDateColumn()
63  createdAt: Date;
64
65  @UpdateDateColumn()
66  updatedAt: Date;
67
68  @BeforeInsert()
69  generateSlug() {
70    if (this.name) {
71      this.slug = this.name.toLowerCase().replace(/ /g, '-');
72    }
73  }
74
75  @BeforeUpdate()
76  validateExperience() {
77    if (this.experience < 0) {
78      this.experience = 0;
79    }
80  }
81
82  @AfterInsert()
83  logCreation() {
84    console.log('New centurion enlisted: ' + this.name);
85  }
86}
87
88// ===========================================
89// 3. Soft delete
90// ===========================================
91
92@Entity('decrees')
93export class Decree {
94  @PrimaryGeneratedColumn()
95  id: number;
96
97  @Column({ length: 200 })
98  title: string;
99
100  @Column({ type: 'text' })
101  content: string;
102
103  @Column({ type: 'boolean', default: true })
104  isActive: boolean;
105
106  @CreateDateColumn()
107  createdAt: Date;
108
109  @UpdateDateColumn()
110  updatedAt: Date;
111
112  @DeleteDateColumn() // Soft delete - don't actually remove
113  deletedAt: Date;
114}
115
116// Using soft delete:
117// await decreeRepo.softDelete(id);     // Sets deletedAt
118// await decreeRepo.restore(id);        // Restores
119// await decreeRepo.find();             // Skips soft-deleted
120// await decreeRepo.find({ withDeleted: true }); // All of them
121
122console.log("\n=== DB VALIDATION SUMMARY ===");
123console.log("@Check() - constraints at the database level");
124console.log("@Index() - indexes that speed up searching");
125console.log("@BeforeInsert/@BeforeUpdate - lifecycle hooks");
126console.log("@DeleteDateColumn - soft delete (don't actually remove)");
127console.log("unique: true - column uniqueness");
128