9. Layered Architecture and Data Access

The previous examples kept their data in memory. A real application stores it in a database and is organized into layers (see the diagram in the introduction):

• the web layer (controllers, views): read the request, call the business layer, select the view;
• the business layer: business rules;
• the DAO layer: database queries.

Each layer is aware only of the layer below it, and as few details about it as possible: the web layer is unaware of the SQL layer, and the business layer is unaware of the HTML layer. This separation makes each layer easier to understand, test, and evolve. Example 28 applies this to managing a list of people: it is, on a smaller scale, the management of doctors and clients from the case study.

9.1. Example 28: A Three-Layer System for Managing People

28-layers/
├── database/
│   └── dbpersons.sql
├── public/
│   ├── css/
│   │   └── styles.css
│   └── js/
│       └── app.js
├── src/
│   ├── business/
│   │   ├── business.exception.ts
│   │   └── persons.business.ts
│   ├── config/
│   │   └── database.config.ts
│   ├── dao/
│   │   └── persons.dao.ts
│   ├── entities/
│   │   └── person.entity.ts
│   ├── web/
│   │   ├── flash.ts
│   │   ├── person-form.dto.ts
│   │   ├── persons.controller.ts
│   │   └── validation.ts
│   ├── app.module.ts
│   └── main.ts
├── views/
│   ├── partials/
│   │   └── confirmation.hbs
│   ├── form.hbs
│   ├── layout.hbs
│   └── list.hbs
├── nest-cli.json
└── tsconfig.json

9.1.1. The database

The database [dbpersons] is created by a script SQL. Using [Laragon], it can be run, for example, in the [HeidiSQL] tool (File menu / Run a SQL file), or from the command line:

mysql -u root < database/dbpersons.sql

The [database/dbpersons.sql] file:

-- database of example 28: a single table, [persons]
DROP DATABASE IF EXISTS dbpersons;
CREATE DATABASE dbpersons CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
USE dbpersons;

CREATE TABLE persons (
  id INT NOT NULL AUTO_INCREMENT,
  version INT NOT NULL DEFAULT 1,
  first_name VARCHAR(30) NOT NULL,
  last_name VARCHAR(30) NOT NULL,
  age INT NOT NULL,
  PRIMARY KEY (id),
  -- two persons cannot have the same first and last names
  CONSTRAINT unq_persons_first_name_last_name UNIQUE (first_name, last_name)
) ENGINE = InnoDB;

INSERT INTO persons (first_name, last_name, age) VALUES
  ('Marie', 'Pélissier', 42),
  ('Jacques', 'Bromard', 35),
  ('Philippe', 'Jandot', 58);

• Line 3: The [utf8mb4] character set accepts all characters (accents, emojis, etc.). The collation [utf8mb4_unicode_ci] compares strings without regard to uppercase letters or accents: for MySQL, [pelissier], and [Pélissier] are equal;
• line 8: a version number, incremented each time the line is modified (see “optimistic lock,” below);
• line 14: a named uniqueness constraint: the database does not allow two people with the same first and last names. The name will be used to identify the error.

9.1.2. The configuration of TypeORM

The [src/config/database.config.ts] file:

import { TypeOrmModuleOptions } from '@nestjs/typeorm';
import { Person } from '../entities/person.entity';

// the database connection; the default values are those of Laragon
export function databaseConfig(): TypeOrmModuleOptions {
  return {
    type: 'mysql',
    host: process.env.DB_HOST ?? 'localhost',
    port: Number(process.env.DB_PORT ?? 3306),
    username: process.env.DB_USERNAME ?? 'root',
    password: process.env.DB_PASSWORD ?? '',
    database: 'dbpersons',
    entities: [Person],
    // the schema is created by the SQL script, never modified by TypeORM
    synchronize: false,
    // DB_LOGGING=true: displays the SQL statements sent to the database
    logging: process.env.DB_LOGGING === 'true',
  };
}

• Lines 8–11: connection parameters, read from environment variables, with the default values from [Laragon] (username [root], no password);
• line 13: the classes that [TypeORM] maps to tables;
• line 15: [TypeORM] could create or modify the tables itself based on the classes. This is convenient for a prototype but dangerous for a database containing data: the schema remains the responsibility of the SQL script;
• line 17: with the environment variable [DB_LOGGING=true], the SQL commands sent to the database are displayed in the console.

9.1.3. The Person entity

The file [src/entities/person.entity.ts]:

import { Column, Entity, PrimaryGeneratedColumn, VersionColumn } from 'typeorm';

// a row of the [persons] table <-> a Person object
@Entity('persons')
export class Person {
  @PrimaryGeneratedColumn()
  id: number;

  // incremented by TypeORM on each modification (optimistic lock)
  @VersionColumn()
  version: number;

  @Column({ name: 'first_name', length: 30 })
  firstName: string;

  @Column({ name: 'last_name', length: 30 })
  lastName: string;

  @Column()
  age: number;
}

• Line 4: The class [Person] corresponds to the table [persons]: each row of the table will be a [Person] object;
• line 6: the primary key, generated by the database ([AUTO_INCREMENT]);
• Line 10: the version column, managed by [TypeORM];
• rows 13–20: the other columns.

9.1.4. The DAO layer

The file [src/dao/persons.dao.ts]:

import { Injectable } from '@nestjs/common';
import { InjectRepository } from '@nestjs/typeorm';
import { EntityManager, Not, Repository } from 'typeorm';
import { Person } from '../entities/person.entity';

// the DATA ACCESS layer: queries, no business rules
@Injectable()
export class PersonsDao {
  constructor(@InjectRepository(Person) private readonly repository: Repository<Person>) {}

  // inside a transaction, we must go through the transaction's "manager"
  private repo(manager?: EntityManager): Repository<Person> {
    return manager ? manager.getRepository(Person) : this.repository;
  }

  list(): Promise<Person[]> {
    return this.repository.find({ order: { lastName: 'ASC', firstName: 'ASC' } });
  }

  read(id: number, manager?: EntityManager): Promise<Person | null> {
    return this.repo(manager).findOneBy({ id });
  }

  // ANOTHER person (id different from [exceptId]) with these first and last names
  namesake(firstName: string, lastName: string, exceptId: number, manager?: EntityManager): Promise<Person | null> {
    return this.repo(manager).findOneBy({ firstName, lastName, id: Not(exceptId) });
  }

  // INSERT if the person has no id, UPDATE otherwise
  save(person: Person, manager?: EntityManager): Promise<Person> {
    return this.repo(manager).save(person);
  }

  async delete(id: number, manager?: EntityManager): Promise<void> {
    await this.repo(manager).delete(id);
  }
}

• line 9: [TypeORM] provides a repository ([Repository<Person>]) for each entity that can read from and write to the table. [@InjectRepository] injects it;
• lines 12–14: Within a transaction, all queries must go through the same [EntityManager] object—the one associated with the transaction. Each method therefore accepts an optional [manager];
• lines 16–18: [find] with a sort: [SELECT ... ORDER BY last_name, first_name];
• lines 25–27: a person with the same first and last name, other than [exceptId] ([Not]): useful for modifying a record, which must not conflict with itself;
• lines 30–32: [save] becomes [INSERT] if the object has no identifier, and [UPDATE] otherwise.

The DAO layer contains no rules: only queries.

9.1.5. The business layer

The file [src/business/business.exception.ts]:

// a business rule rejects the operation: this is not an anomaly,
// the message is intended for the user
export class BusinessException extends Error {}

A [BusinessException] indicates that a business rule has rejected an operation. This is not a program error: the message is intended for the user.

The [src/business/persons.business.ts] file:

import { Injectable } from '@nestjs/common';
import { DataSource, EntityManager } from 'typeorm';
import { PersonsDao } from '../dao/persons.dao';
import { Person } from '../entities/person.entity';
import { BusinessException } from './business.exception';

export interface PersonData {
  firstName: string;
  lastName: string;
  age: number;
}

// the BUSINESS layer: the business rules, and only them
@Injectable()
export class PersonsBusiness {
  constructor(
    private readonly dao: PersonsDao,
    private readonly dataSource: DataSource,
  ) {}

  list(): Promise<Person[]> {
    return this.dao.list();
  }

  async read(id: number): Promise<Person> {
    const person = await this.dao.read(id);
    if (!person) {
      throw new BusinessException('This person does not exist (anymore).');
    }
    return person;
  }

  add(data: PersonData): Promise<Person> {
    return this.transaction(async (manager) => {
      // rule 1: no two persons with the same first and last names
      if (await this.dao.namesake(data.firstName, data.lastName, 0, manager)) {
        throw new BusinessException('A person already has these first and last names.');
      }
      const person = new Person();
      person.firstName = data.firstName;
      person.lastName = data.lastName;
      person.age = data.age;
      return this.dao.save(person, manager);
    });
  }

  edit(id: number, version: number, data: PersonData): Promise<Person> {
    return this.transaction(async (manager) => {
      const person = await this.dao.read(id, manager);
      if (!person) {
        throw new BusinessException('This person does not exist (anymore).');
      }
      // rule 2: we do not modify a record modified meanwhile by someone else
      if (person.version !== version) {
        throw new BusinessException('This record has been modified meanwhile: reload it.');
      }
      if (await this.dao.namesake(data.firstName, data.lastName, id, manager)) {
        throw new BusinessException('A person already has these first and last names.');
      }
      person.firstName = data.firstName;
      person.lastName = data.lastName;
      person.age = data.age;
      return this.dao.save(person, manager);
    });
  }

  async delete(id: number): Promise<Person> {
    const person = await this.read(id);
    await this.dao.delete(id);
    return person;
  }

  // [work] runs inside a transaction: COMMIT if it succeeds, ROLLBACK
  // if it throws an exception. A violation of the uniqueness constraint (two
  // simultaneous additions, for example) becomes a BusinessException
  private async transaction<R>(work: (manager: EntityManager) => Promise<R>): Promise<R> {
    try {
      return await this.dataSource.transaction(work);
    } catch (e) {
      const sqlError = e as { code?: string; message?: string };
      if (sqlError.code === 'ER_DUP_ENTRY' && sqlError.message?.includes('unq_persons_first_name_last_name')) {
        throw new BusinessException('A person already has these first and last names.');
      }
      throw e;
    }
  }
}

Let’s break down this code:

• lines 16–19: The business layer uses DAO, and [DataSource] from [TypeORM] (the database connection) to open transactions;
• lines 25–31: an absent person is a [BusinessException];
• lines 33–45: adding a person within a transaction. Rule: no two people may have the same first and last names. This is verified before the write operation to display a clear message;
• lines 47–65: modification. Additional rule: the optimistic lock (lines 54–56): the modification form carries the version of the record as it was when the form was opened. If it has changed in the meantime (another user has modified the same record), the operation is rejected rather than silently overwriting the user’s work;
• lines 76–86: [dataSource.transaction(work)] opens a transaction, executes [work], then validates it ([COMMIT]) or rolls it back ([ROLLBACK]) if [work] throws an exception. Lines 81–82: Preliminary checking for homonyms is not always sufficient: two users may add the same person at the same time, and both may pass the check. The database’s uniqueness constraint, however, does not allow any exceptions; the error MySQL [ER_DUP_ENTRY], identified by the constraint, then becomes the same [BusinessException].

9.1.6. The Web Layer

The file [src/web/person-form.dto.ts]:

import { Transform, Type } from 'class-transformer';
import { IsInt, IsNotEmpty, Max, MaxLength, Min } from 'class-validator';

const clean = ({ value }: { value: unknown }) => (typeof value === 'string' ? value.trim() : value);

// the fields of the form and their constraints (lengths = SQL columns)
export class PersonFormDto {
  @Transform(clean)
  @IsNotEmpty({ message: 'The first name is required.' })
  @MaxLength(30, { message: 'The first name must not exceed 30 characters.' })
  firstName: string;

  @Transform(clean)
  @IsNotEmpty({ message: 'The last name is required.' })
  @MaxLength(30, { message: 'The last name must not exceed 30 characters.' })
  lastName: string;

  @Type(() => Number)
  @IsInt({ message: 'The age must be an integer.' })
  @Min(0, { message: 'The age must be positive.' })
  @Max(150, { message: 'The age cannot exceed 150.' })
  age: number;
}

The form and its constraints (see Example 19). The function [validateForm] (file [web/validation.ts]) is the one from Example 19, and [web/flash.ts] is the one from Example 20, supplemented with a message type (success or error).

The file [src/web/persons.controller.ts]:

import { Body, Controller, Get, Param, ParseIntPipe, Post, Redirect, Render, Res } from '@nestjs/common';
import { Response } from 'express';
import { BusinessException } from '../business/business.exception';
import { PersonsBusiness } from '../business/persons.business';
import { flash } from './flash';
import { PersonFormDto } from './person-form.dto';
import { validateForm } from './validation';

// the WEB layer: read the request, call the business layer, choose the view
@Controller()
export class PersonsController {
  constructor(private readonly business: PersonsBusiness) {}

  @Get()
  @Redirect('/persons')
  root(): void {}

  @Get('persons')
  @Render('list')
  async list(): Promise<object> {
    return { persons: await this.business.list() };
  }

  @Get('persons/add')
  @Render('form')
  addForm(): object {
    return { title: 'Add a person', action: '/persons/add', values: {}, errors: {} };
  }

  @Post('persons/add')
  async add(@Body() body: Record<string, string>, @Res() res: Response): Promise<void> {
    const view = { title: 'Add a person', action: '/persons/add' };
    const { dto, errors } = await validateForm(PersonFormDto, body);
    if (Object.keys(errors).length > 0) {
      res.render('form', { ...view, values: body, errors, error: 'The form contains errors.' });
      return;
    }
    try {
      const p = await this.business.add(dto);
      flash(res, { type: 'success', text: `${p.firstName} ${p.lastName} has been added.` });
      res.redirect('/persons');
    } catch (e) {
      // a business rule rejected the addition: we display the form again
      if (!(e instanceof BusinessException)) throw e;
      res.render('form', { ...view, values: body, errors: {}, error: e.message });
    }
  }

  @Get('persons/:id/edit')
  @Render('form')
  async editForm(@Param('id', ParseIntPipe) id: number): Promise<object> {
    const p = await this.business.read(id);
    return { title: 'Edit a person', action: `/persons/${id}/edit`, values: p, errors: {} };
  }

  @Post('persons/:id/edit')
  async edit(
    @Param('id', ParseIntPipe) id: number,
    @Body() body: Record<string, string>,
    @Res() res: Response,
  ): Promise<void> {
    const view = { title: 'Edit a person', action: `/persons/${id}/edit` };
    const { dto, errors } = await validateForm(PersonFormDto, body);
    if (Object.keys(errors).length > 0) {
      res.render('form', { ...view, values: body, errors, error: 'The form contains errors.' });
      return;
    }
    try {
      const p = await this.business.edit(id, Number(body.version), dto);
      flash(res, { type: 'success', text: `${p.firstName} ${p.lastName} has been modified.` });
      res.redirect('/persons');
    } catch (e) {
      if (!(e instanceof BusinessException)) throw e;
      res.render('form', { ...view, values: body, errors: {}, error: e.message });
    }
  }

  @Post('persons/:id/delete')
  async delete(@Param('id', ParseIntPipe) id: number, @Res() res: Response): Promise<void> {
    try {
      const p = await this.business.delete(id);
      flash(res, { type: 'success', text: `${p.firstName} ${p.lastName} has been deleted.` });
    } catch (e) {
      if (!(e instanceof BusinessException)) throw e;
      // no form to display again: the error travels as a flash too
      flash(res, { type: 'danger', text: e.message });
    }
    res.redirect('/persons');
  }
}

Let’s comment on this code:

• line 12: the controller only knows the business layer;
• lines 14–16: the site root redirects to the list;
• lines 18–22: an asynchronous action: the list is retrieved from the database;
• lines 30–47: the addition, in three steps: form validation (errors → page reload), call to the business layer (success → pop-up message and redirection), [BusinessException] (→ page reload with the message at the bottom of the page). Another exception is thrown ([throw e]): this is an anomaly, which the exception filter for [NestJS] will convert into a 500 error;
• lines 49–54: the update form, pre-filled with the record retrieved from the database (including its version);
• lines 56–61: same process as for adding a record; the version returned by the form is passed to the business layer;
• lines 78–89: deletion. There is no form to redisplay: any errors are also included in the flash message.

The file [src/app.module.ts]:

import { Module } from '@nestjs/common';
import { TypeOrmModule } from '@nestjs/typeorm';
import { databaseConfig } from './config/database.config';
import { PersonsDao } from './dao/persons.dao';
import { Person } from './entities/person.entity';
import { PersonsBusiness } from './business/persons.business';
import { PersonsController } from './web/persons.controller';

@Module({
  imports: [
    // the database connection, for the whole application
    TypeOrmModule.forRoot(databaseConfig()),
    // the Repository<T> injectable in this module
    TypeOrmModule.forFeature([Person]),
  ],
  controllers: [PersonsController],
  // the three layers: web (controller) -> business -> dao
  providers: [PersonsBusiness, PersonsDao],
})
export class AppModule {}

• line 12: the database connection for the entire application;
• line 14: enables the [Repository<Person>] to be injected;
• line 18: the business layer services and DAO.

The [views/form.hbs] file:

<h1 class="h3">{{title}}</h1>
<form method="post" action="{{action}}" novalidate>
  {{!-- the version of the record read, sent back with the modification --}}
  <input type="hidden" name="version" value="{{values.version}}">
  <div class="row g-2 mb-3">
    <div class="col-sm-5">
      <label class="form-label" for="firstName">First name</label>
      <input class="form-control {{#if errors.firstName}}is-invalid{{/if}}" id="firstName" name="firstName"
             value="{{values.firstName}}" maxlength="30" required>
      {{#if errors.firstName}}<div class="invalid-feedback">{{errors.firstName}}</div>{{/if}}
    </div>
    <div class="col-sm-5">
      <label class="form-label" for="lastName">Last name</label>
      <input class="form-control {{#if errors.lastName}}is-invalid{{/if}}" id="lastName" name="lastName"
             value="{{values.lastName}}" maxlength="30" required>
      {{#if errors.lastName}}<div class="invalid-feedback">{{errors.lastName}}</div>{{/if}}
    </div>
    <div class="col-sm-2">
      <label class="form-label" for="age">Age</label>
      <input class="form-control {{#if errors.age}}is-invalid{{/if}}" id="age" name="age" type="number"
             value="{{values.age}}" required>
      {{#if errors.age}}<div class="invalid-feedback">{{errors.age}}</div>{{/if}}
    </div>
  </div>
  <button class="btn btn-primary" type="submit">Save</button>
  <a class="btn btn-link" href="/persons">Back to the list</a>
</form>

Line 4: the hidden field that carries the record’s version. The view is used for adding and modifying records: its title and the submission URL ([action]) are provided by the controller. The views [list.hbs] and [layout.hbs], along with the confirmation script, are the same as those in Examples 20 and 21.

9.1.7. Execution

Connection parameters are passed via environment variables if the default values are not suitable. Under [Windows] ([PowerShell]):

$env:DB_USERNAME="root"; $env:DB_PASSWORD=""; $env:DB_LOGGING="true"; npx nest start

The list:

An invalid form:

A homonym, written in lowercase and without accents: the business rule detects it, because MySQL compares based on the database's collation:

A successful addition, and the corresponding SQL orders ([DB_LOGGING=true]):

query: START TRANSACTION
query: SELECT `Person`.`id` AS `Person_id`, `Person`.`version` AS `Person_version`, `Person`.`first_name` AS `Person_first_name`, `Person`.`last_name` AS `Person_last_name`, `Person`.`age` AS `Person_age` FROM `persons` `Person` WHERE ((`Person`.`first_name` = ?) AND (`Person`.`last_name` = ?) AND (`Person`.`id` != ?)) LIMIT 1 -- PARAMETERS: ["Sylvie","Durand",0]
query: INSERT INTO `persons`(`id`, `version`, `first_name`, `last_name`, `age`) VALUES (DEFAULT, 1, ?, ?, ?) -- PARAMETERS: ["Sylvie","Durand",29]
query: SELECT `Person`.`id` AS `Person_id`, `Person`.`version` AS `Person_version` FROM `persons` `Person` WHERE `Person`.`id` = ? -- PARAMETERS: [4]
query: COMMIT
query: SELECT `Person`.`id` AS `Person_id`, `Person`.`version` AS `Person_version`, `Person`.`first_name` AS `Person_first_name`, `Person`.`last_name` AS `Person_last_name`, `Person`.`age` AS `Person_age` FROM `persons` `Person` ORDER BY `Person`.`last_name` ASC, `Person`.`first_name` ASC

We can see the transaction ([START TRANSACTION] ... [COMMIT]), the search for duplicates, the insertion with version 1, and then, after the redirection, the reading of the list. The optimistic lock: two forms are open on the same record; the first is saved (the version changes to 2); the second, which still has version 1, is rejected:

Deletion, with confirmation: