Zero-to-Fullstack: Building a Modern Web Application from Square One

Part II: The Data Model

Many developers find data modeling tedious, but Nest.js makes it easy

This is Part II in a series I’m calling Zero-to-Fullstack. It outlines the process of developing a simple fullstack application from scratch. If you haven’t read Part I, I encourage you to start there. In that article we built our development environment by containerizing our project, used an orchestrator to integrate it with a database service, and created our first database objects from a migration. In this article, we’re going to extend our application’s data model and lay the foundation for our API application’s functionality.

You can find the source code for this article at its Github Repository here.

Domain Modeling

Before we write any code, we need to understand what exactly it is that we’re building. When I model a greenfield domain, I like to start by thinking about the nouns of the system. In other words, I like to imagine the objects I’d need to work with in order to accomplish that system’s objectives. We’re building a tool for trivia hosts to coordinate pub-style trivia games, so the nouns that we’ll need are:

• Questions (this noun was created in the previous article)

• Teams and/or players

• Answers

• Rounds

• Quizzes

• Games

Once the nouns are defined, it’s important to understand the relationships, or more formally the cardinality, between those nouns. In our system teams will join a game, which is created from a quiz. A quiz should be reusable across multiple games, which should consist of one or multiple rounds. Each round should consist of one or multiple questions and each round teams will submit answers to that round’s questions. Therefore, we can draw the following conclusions about the relationships between our domain nouns:

• Teams and Games = N:1

• Games and Quizzes = N:1

• Rounds and Quizzes = N:1

• Questions and Rounds = N:1

• Answers and Teams = N:1

• Answers and Questions = N:1

Now that our nouns have been characterized, we’re ready to write some code.

Creating the Data Model

To keep our data model flexible and extensible we’re going to create a separate Nest resource for each domain noun. As we saw in the previous article, we can leverage Nest’s CLI tool to scaffold those resources for us by executing the command npx nest g res <resources>. Just as we did in the last article, please select the REST API transport layer and Yes when prompted by the CLI tool whether you would like to generate CRUD entry points.

npx nest g res teams
npx nest g res answers
npx nest g res rounds
npx nest g res quizzes
npx nest g res games

After executing these commands, there should be five new directories in your project’s src directory, each corresponding to a newly created resource. We’re going to migrate our database in three phases. In the first migration we’ll create the domain nouns, in the second migration we’ll populate those nouns with their attributes, and in the final migration we’ll build the associations between our nouns.

Step 1

Open src/teams/entities/team.entity.ts and add the following lines to the file:

import { Entity, PrimaryGeneratedColumn } from 'typeorm';

@Entity('teams')
export class Team {
  @PrimaryGeneratedColumn()
  id: number;
}

The PrimaryGeneratedColumn decorator indicates to TypeORM that this field is both a primary key for the table and is a generated column (i.e. generated by the database). We’re going to repeat this process for the remainder of our entities.

src/answers/entities/answer.entity.ts:

import { Entity, PrimaryGeneratedColumn } from 'typeorm';

@Entity('answers')
export class Answer {
  @PrimaryGeneratedColumn()
  id: number;
}

src/rounds/entities/round.entity.ts:

import { Entity, PrimaryGeneratedColumn } from 'typeorm';

@Entity('rounds')
export class Round {
  @PrimaryGeneratedColumn()
  id: number;
}

src/quizzes/entities/quiz.entity.ts:

import { Entity } from 'typeorm';

@Entity('quizzes')
export class Quiz {
  @PrimaryGeneratedColumn()
  id: number;
}

src/games/entities/game.entity.ts:

import { Entity, PrimaryGeneratedColumn } from 'typeorm';

@Entity('games')
export class Game {
  @PrimaryGeneratedColumn('uuid')
  id: string;
}

Note the id for our game entity is a UUID string while it’s an int on our other entities. This is to make it difficult to join games by simply guessing the next ID in a sequence. Just as we did in the last article, we’re ready to generate a new migration from our code.¹ We do this by executing the following commands:

npm run db:migration:generate
npm run db:migrate

Following the execution of these commands, a new migration file should appear in the priv directory² and your application’s database should now include tables for each of the created entities.

Step 2

In this step, we’re going to add attributes to each of our entities and migrate them in the database.

Begin by opening src/teams/entities/team.entity.ts and adding the following lines to the file:

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

@Entity('teams')
export class Team {
  @PrimaryGeneratedColumn()
  id: number;

  @Column()
  name: string;
}

The Column decorator indicates to TypeORM that this attribute maps to a column in the database.

src/questions/entities/question.entity:

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

@Entity('questions')
export class Question {
  @PrimaryGeneratedColumn()
  id: number;

  @Column({ default: 1 })
  points: number;

  @Column()
  text: string;
}

Passing the default key to the Column decorator means the database will default any NULL values to the specified value. In this case, NULL values will default to a value of 1. This is sensible because most trivia questions are worth one point.

src/answers/entities/answer.entity.ts:

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

@Entity('answers')
export class Answer {
  @PrimaryGeneratedColumn()
  id: number;

  @Column({ default: false, name: 'is_correct' })
  isCorrect: boolean;

  @Column()
  text: string;
}

We also pass a name to the Column decorator so the column is snake cased in the database. If a name was not specified, it would use the name as it appears on the attribute. This is a matter of taste, but many SQL formatting tools force attributes to screaming case which can make them difficult to read without underscores (i.e. ISCORRECT is more difficult to read than IS_CORRECT).

src/rounds/entities/round.entity.ts:

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

@Entity('rounds')
export class Round {
  @PrimaryGeneratedColumn()
  id: number;

  @Column()
  name: string;
}

src/quizzes/entities/quiz.entity.ts:

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

@Entity('quizzes')
export class Quiz {
  @PrimaryGeneratedColumn()
  id: number;

  @CreateDateColumn({ name: 'created_on' })
  createdOn: Date;

  @Column({ name: 'creator_id' })
  creatorId: string;

  @Column()
  name: string;
}

The CreateDateColumn decorator instructs TypeORM to create an immutable timestamp for each resource when it’s created. This will allow us to sort our quizzes by creation date.

src/games/entities/game.entity.ts:

import {
  BeforeInsert,
  Column,
  CreateDateColumn,
  Entity,
  Index,
  PrimaryGeneratedColumn,
} from 'typeorm';

const getRandomCharacter = (): string => {
  const characters = 'ABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789';
  const randomCharacter = Math.floor(Math.random() * characters.length);
  return characters.charAt(randomCharacter);
};

const makeCode = (length: number): string =>
  Array(length).fill(null).map(getRandomCharacter).join('');

@Entity('games')
export class Game {
  @PrimaryGeneratedColumn('uuid')
  id: string;

  @Column({ unique: true })
  @Index('games_code_idx')
  code: string;

  @CreateDateColumn({ name: 'created_on' })
  createdOn: Date;

  @Column({ default: false, name: 'is_complete' })
  isComplete: boolean;

  @Column()
  name: string;

  @BeforeInsert()
  createCode(): void {
    this.code = makeCode(6);
  }
}

There’s a fair bit going on here, so let’s break it down piece-by-piece. We pass a configuration value of unique: true to our code column. This applies a unique value constraint on that column meaning we cannot have duplicate values appear in that column. This is important because it is going to represent the code players use to join games and duplicate values may lead to players inadvertently joining the incorrect game. We also add an Index decorator to that attribute because teams will retrieve games using that code to join games. We also include a BeforeInsert decorator which executes before each insertion in the database. This allows us to programatically create game codes right before saving a game to the database.

We’re now ready to once again migrate our database.³

npm run db:migration:generate
npm run db:migrate

If we inspect our schema after running this migration, it should look like this:

Step 3

In the final step, we’re going to establish the associations between our nouns. Begin by opening src/questions/entities/question.entity to add the following lines to the file:

import {
  Column,
  Entity,
  JoinColumn,
  ManyToOne,
  OneToMany,
  PrimaryGeneratedColumn,
} from 'typeorm';

import { Answer } from '../../answers/entities/answer.entity';
import { Round } from '../../rounds/entities/round.entity';

@Entity('questions')
export class Question {
  @PrimaryGeneratedColumn()
  id: number;

  @OneToMany(() => Answer, (answer) => answer.question)
  answers: Answer[];

  @Column({ default: 1 })
  points: number;

  @JoinColumn({ name: 'round_id' })
  @ManyToOne(() => Round)
  round: Round;

  @Column()
  text: string;
}

We’ve created two new associations here. These associations define the cardinality between two resources. In the case of the OneToMany decorator, one instance of that resource can have zero, one, or many associations with the decorated resource. In this case specifically, each Question can have many Answers. The ManyToOne decorator defines a relationship between resources where the current resource will have no more than one of its related resource, but that related resource might have multiple instances of it in the reverse direction. In this case, a Question belongs to exactly one Round, but each Round can have zero, one, or multiple Questions. The JoinColumn decorator allows us to specify the name of the foreign key that will be added to the table. Without it, TypeORM will create a foreign key column called roundId which can be difficult to read.

src/teams/entities/team.entity.ts:

import {
  Column,
  Entity,
  JoinColumn,
  ManyToOne,
  OneToMany,
  PrimaryGeneratedColumn,
} from 'typeorm';

import { Answer } from '../../answers/entities/answer.entity';
import { Game } from '../../games/entities/game.entity';

@Entity('teams')
export class Team {
  @PrimaryGeneratedColumn()
  id: number;

  @OneToMany(() => Answer, (answer) => answer.team)
  answers: Answer[];

  @JoinColumn({ name: 'game_id' })
  @ManyToOne(() => Game)
  game: Game;

  @Column()
  name: string;
}

src/answers/entities/answer.entity.ts:

import {
  Column,
  Entity,
  JoinColumn,
  ManyToOne,
  PrimaryGeneratedColumn,
} from 'typeorm';

import { Question } from '../../questions/entities/question.entity';
import { Team } from '../../teams/entities/team.entity';

@Entity('answers')
export class Answer {
  @PrimaryGeneratedColumn()
  id: number;

  @Column({ default: false, name: 'is_correct' })
  isCorrect: boolean;

  @JoinColumn({ name: 'question_id' })
  @ManyToOne(() => Question)
  question: Question;

  @Column({ name: 'question_id' })
  questionId: number;

  @JoinColumn({ name: 'team_id' })
  @ManyToOne(() => Team)
  team: Team;

  @Column({ name: 'team_id' })
  teamId: string;

  @Column()
  text: string;
}

We specify teamId and questionId on this table as a matter of convenience. It also serves as a helpful reminder that each of these is actually stored as a foreign key on the answers table.

src/rounds/entities/round.entity.ts:

import {
  Column,
  Entity,
  JoinColumn,
  ManyToOne,
  OneToMany,
  PrimaryGeneratedColumn,
} from 'typeorm';

import { Question } from '../../questions/entities/question.entity';
import { Quiz } from '../../quizzes/entities/quiz.entity';

@Entity('rounds')
export class Round {
  @PrimaryGeneratedColumn()
  id: number;

  @Column()
  name: string;

  @OneToMany(() => Question, (question) => question.round, {})
  questions: Question[];

  @JoinColumn({ name: 'quiz_id' })
  @ManyToOne(() => Quiz)
  quiz: Quiz;
}

src/quizzes/entities/quiz.entity.ts:

import {
  Column,
  CreateDateColumn,
  Entity,
  Index,
  OneToMany,
  PrimaryGeneratedColumn,
} from 'typeorm';

import { Round } from '../../rounds/entities/round.entity';

@Entity('quizzes')
export class Quiz {
  @Index('quizzes_id_idx')
  @PrimaryGeneratedColumn('uuid')
  id: string;

  @CreateDateColumn({ name: 'created_on' })
  createdOn: Date;

  @Column({ name: 'creator_id' })
  creatorId: string;

  @Column()
  name: string;

  @OneToMany(() => Round, (round) => round.quiz)
  rounds: Round[];
}

src/games/entities/game.entity.ts:

import {
  BeforeInsert,
  Column,
  CreateDateColumn,
  Entity,
  Index,
  JoinColumn,
  ManyToOne,
  OneToMany,
  PrimaryGeneratedColumn,
} from 'typeorm';

import { Quiz } from '../../quizzes/entities/quiz.entity';
import { Round } from '../../rounds/entities/round.entity';
import { Team } from '../../teams/entities/team.entity';

const getRandomCharacter = (): string => {
  const characters = 'ABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789';
  const randomCharacter = Math.floor(Math.random() * characters.length);
  return characters.charAt(randomCharacter);
};

const makeCode = (length: number): string =>
  Array(length).fill(null).map(getRandomCharacter).join('');

@Entity('games')
export class Game {
  @PrimaryGeneratedColumn('uuid')
  id: string;

  @Column({ unique: true })
  @Index('games_code_idx')
  code: string;

  @CreateDateColumn({ name: 'created_on' })
  createdOn: Date;

  @JoinColumn({ name: 'current_round_id' })
  @ManyToOne(() => Round, { nullable: true })
  currentRound?: Round;

  @Column({ name: 'current_round_id', nullable: true })
  currentRoundId?: number | null;

  @Column({ default: false, name: 'is_complete' })
  isComplete: boolean;

  @Column()
  name: string;

  @JoinColumn({ name: 'previous_round_id' })
  @ManyToOne(() => Round, { nullable: true })
  previousRound?: Round;

  @Column({ name: 'previous_round_id', nullable: true })
  previousRoundId?: number | null;

  @JoinColumn({ name: 'quiz_id' })
  @ManyToOne(() => Quiz)
  quiz: Quiz;

  @OneToMany(() => Team, (team) => team.game)
  teams: Team[];

  @BeforeInsert()
  createCode(): void {
    this.code = makeCode(6);
  }
}

We are now ready to generate our migration and execute it by running:

npm run db:migration:generate
npm run db:migrate

If we inspect the state of our database after executing the migration, we should see the relationships now exist between our entities.

Recap

In this article we built the bulk of our application’s data model without writing a single line of SQL. Because our migrations are generated from code, there are strong guarantees our code is consistent with the state of the database. In the next article, we’ll write our application’s business logic and lay the foundation for a robust and secure auth system.

As always…

1. Before generating the migration, it’s important that your database is fully migrated. If it’s not, the migration will include the creation of the questions table causing the subsequent migration to fail because the questions table already exists from the first migration.

2. As a matter of good hygiene, it’s always a good idea to review your migration files before executing them.

3. You may have noticed we don’t have a score attribute on any of our entities. I went back and forth on this but ultimately concluded that a score is a derived value of a team’s answers’ correctness. Rather than keep competing sources of truth consistent, I’ve decided that team scores are best computed at runtime. We’ll explore strategies for managing derived data later in this article.