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, @name - 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.