NextjsVietnam

Posted on Jul 5, 2023 • Edited on Jul 6, 2023

NestJS Course Lesson 04 - Model and Repository Pattern

#nestjs #nestjstutorial #mvc #nextjsvietnam

Source Code

Lession 04 Source Code

Lesson 04

Brief introduction to one of popular ORM - Sequelize
Learn about the ActiveRecord Pattern
How to create database schema with Sequelize in NestJS
How to add seeding data with Sequelize in NestJS

1. Configure NestJS to work with MySQL via Sequelize

First you need to create the database first
Database name : nestjs_tutorial_2023

CREATE DATABASE `nestjs_tutorial_2023` CHARACTER SET 'utf8mb4' COLLATE 'utf8mb4_bin';

Connect to MYSQL using Sequelize
Connect to PostgreSQL using TypeORM
Connect to MongoDB using Mongoose

1.1. Connect to MYSQL using Sequelize

Install packages

npm install --save @nestjs/sequelize sequelize sequelize-typescript mysql2
npm install --save-dev @types/sequelize
npm install --save-dev sequelize-cli
npx sequelize-cli init

In this part, I would like to introduce to you 3 main steps to work with databases:

[x] Establish connection
[x] Declare model
[x] Database migration directory structure - initialize, update database schema

Set up connection

// src/app.module.ts

import { Module } from "@nestjs/common";
import { ServeStaticModule } from "@nestjs/serve-static";
import { join } from "path";
import { PetModule } from "./pet/pet.module";
import { SequelizeModule } from "@nestjs/sequelize";

@Module({
   imports: [
     // public folder
     ServeStaticModule.forRoot({
       rootPath: join(process.cwd(), "public"),
       serveRoot: "/public",
     }),
     PetModule,
     SequelizeModule.forRoot({
       dialect: "mysql",
       host: "localhost",
       port: 3306,
       username: "root",
       password: "123456",
       database: "nestjs_tutorial_2023",
       models: [],
     }),
   ],
   providers: [],
})
export class AppModule {}

Model declaration

Model in ORM:

It is the mapping of an entity in the database, through the ORM we only need to work with the methods of the Model, the rest are SQL/NoSQL statements that will be handled by the ORM. The advantage is that programming becomes easier and more consistent. The downside is that sometimes some queries will be slow and difficult to implement complex queries. However, in that case ORM still supports them to perform traditional SQL/NoSQL queries.

"use strict";

import { PetCategory } from "src/pet/models/pet-category.model";

/** @type {import('sequelize-cli').Migration} */

module.exports = {
   async up(queryInterface, Sequelize) {
     /**
      * Add altering commands here.
      *
      * Example:
      * await queryInterface.createTable('users', { id: Sequelize.INTEGER });
      */
     await queryInterface.createTable("pet_categories", {
       id: {
         type: Sequelize.UUID,
         defaultValue: Sequelize.UUIDV4,
         primaryKey: true,
       },
       name: {
         type: Sequelize.STRING(60),
         allowNull: false,
       },
       createAt: {
         type: Sequelize.DATE,
         defaultValue: Sequelize.fn("NOW"),
       },
       updatedAt: {
         type: Sequelize.DATE,
         defaultValue: Sequelize.fn("NOW"),
       },
     });
   },

   async down(queryInterface, Sequelize) {
     /**
      * Add reverting commands here.
      *
      * Example:
      * await queryInterface.dropTable('users');
      */
     await queryInterface.dropTable("pet_categories");
   },
};

Adjust the config to use the model and use the database uri

// src/app.module.ts

import { Module } from "@nestjs/common";
import { ServeStaticModule } from "@nestjs/serve-static";
import { join } from "path";
import { PetModule } from "./pet/pet.module";
import { SequelizeModule } from "@nestjs/sequelize";
import models from "./pet/models";

@Module({
   imports: [
     // public folder
     ServeStaticModule.forRoot({
       rootPath: join(process.cwd(), "public"),
       serveRoot: "/public",
     }),
     PetModule,
     SequelizeModule.forRoot({
       uri: "mysql://root:123456@localhost/nestjs_tutorial_2023",
       dialect: "mysql",
       models: models,
     }),
   ],
   providers: [],
})
export class AppModule {}

Database migration directory structure - initialize, update database schema

Some explanation why we need to use sequelize-cli to update database.
During project development, when there is a data table that needs to add/delete or update columns in the table. The most direct way is SQL statements, although we can still manage which statements have run or not and shared with team members or people in charge of deploying products on production environment.

In today's modern ORMs, to unify how and standardize, we will usually use the cli of these ORMs related to creating/editing databases, tables.
And perform the migration via command lines. The structure of the data tables will be implemented through code - for consistency with the model managed by the ORM.

After running the following default command of sequelize-cli we will have the following default directory structure.

npx sequelize-cli init

config
   database.json
db
   models
   seeders
   migrations

.sequelizerc

// .sequelizerc

const path = require("path");

module.exports = {
   config: path.resolve("config", "database.json"),
   "models-path": path.resolve("db", "models"),
   "seeders-path": path.resolve("db", "seeders"),
   "migrations-path": path.resolve("db", "migrations"),
};

However, to match the current structure of the project, we need to adjust a bit as follows

src
     database
         config
             config.ts
     migrations
         *.ts
     seeds
         *.ts

.sequelizerc

const path = require("path");

module.exports = {
   config: path.resolve("./dist/database/config/config.js"),
   "seeders-path": path.resolve("./dist/database/seeders"),
   "migrations-path": path.resolve("./dist/database/migrations"),
};

Here, instead of running the source version directly, we will run their build

// config.ts
module.exports = {
   production: {
     url: "mysql://root:123456@localhost/nestjs_tutorial_2023",
     dialect: "mysql",
   },
};

// src/pet/models/pet-category.model
import { Column, DataType, Model, Table } from "sequelize-typescript";

@Table({
   tableName: "pet_categories",
})
export class PetCategory extends Model {
   @Column({
     primaryKey: true,
     type: DataType.UUID,
     defaultValue: DataType.UUIDV4,
   })
   id?: string;

   @Column({
     type: DataType.STRING(60),
     allowNull: false,
   })
   name: string;
}

// src\database\migrations\20230704043449-create-pet-category-table.ts
"use strict";

import { PetCategory } from "src/pet/models/pet-category.model";

/** @type {import('sequelize-cli').Migration} */

module.exports = {
   async up(queryInterface, Sequelize) {
     /**
      * Add altering commands here.
      *
      * Example:
      * await queryInterface.createTable('users', { id: Sequelize.INTEGER });
      */
     await queryInterface.createTable("pet_categories", {
       id: {
         type: Sequelize.UUID,
         defaultValue: Sequelize.UUIDV4,
         primaryKey: true,
       },
       name: {
         type: Sequelize.STRING(60),
         allowNull: false,
       },
     });
   },

   async down(queryInterface, Sequelize) {
     /**
      * Add reverting commands here.
      *
      * Example:
      * await queryInterface.dropTable('users');
      */
     await queryInterface.dropTable("pet_categories");
   },
};

Special note for the model here

Because the PetCategory Model in the example inherits from the Model from sequelize, it will inherit some predefined fields, although in the code of the PetCategory Model we do not see them appear.
Therefore, when creating a migration script for PetCategory, you should note these two default columns.

export declare abstract class Model<TModelAttributes extends {} = any, TCreationAttributes extends {} = TModelAttributes> extends OriginModel<TModelAttributes, TCreationAttributes> {
     id?: number | any;
     createAt?: Date | any;
     updatedAt?: Date | any;
     deletedAt?: Date | any;
     version?: number | any;
     static isInitialized: boolean;

Some common commands

$ npx sequelize-cli --help

Sequelize CLI [Node: 16.19.1, CLI: 6.6.1, ORM: 6.32.1]

sequelize <command>

Commands:
   sequelize db:migrate Run pending migrations
   sequelize db:migrate:schema:timestamps:add Update migration table to have timestamps
   sequelize db:migrate:status List the status of all migrations
   sequelize db:migrate:undo Reverts a migration
   sequelize db:migrate:undo:all Revert all migrations ran
   sequelize db:seed Run specified seeder
   sequelize db:seed:undo Deletes data from the database
   sequelize db:seed:all Run every seeder
   sequelize db:seed:undo:all Deletes data from the database
   sequelize db:create Create database specified by configuration
   sequelize db:drop Drop database specified by configuration
   sequelize init Initializes project
   sequelize init:config Initializes configuration
   sequelize init:migrations Initializes migrations
   sequelize init:models Initialize models
   sequelize init:seeders Initializes seeders
   sequelize migration:generate Generates a new migration file
   sequelize migration:create Generates a new migration file
   sequelize model:generate Generates a model and its migration
   sequelize model:create Generates a model and its migration
   sequelize seed:generate Generates a new seed file
   sequelize seed:create Generates a new seed file

I would like an example command to make a migration file, then build, and run

# create migration
npx sequelize-cli migration:create --name create-pet-category-table --migrations-path ./src/database/migrations
# build
npm run build
# run migration
npx sequelize-cli db:migrate --env production

Note that if env is not specified, the default is development. As in the config file above we only set one environment as production. And the configs will be replaced with environment variables.

After running migrate, now check the database we will see

In the next step, we start testing some basic methods of the Model: add, update, delete, search

A small note when continuing the lesson with the current Pet Category example, we need to update the PetCategory Model a bit, instead of column title -> will switch to column name, for compatibility with the database at this time.

Add PetCategory

import { PetCategory } from "src/pet/models/pet-category.model";
await PetCategory.create({ ...object });

@Controller("admin/pet-categories")
export class ManagePetCategoryController {
   @Post("create")
   @Render("pet/admin/manage-pet-category/create")
   @FormDataRequest()
   async create(@Body() createPetCategoryDto: CreatePetCategoryDto) {
     const data = {
       mode: "create",
     };
     // validation
     const object = plainToInstance(CreatePetCategoryDto, createPetCategoryDto);

     // ...

     // set value and show success message
     Reflect.set(data, "values", object);

     // create PetCategory
     const newPetCategory = await PetCategory.create({ ...object });

     Reflect.set(
       data,
       "success",
       `Pet Category : ${newPetCategory.id} - ${newPetCategory.name} has been created!`
     );
     // success
     return { data };
   }
}

After running http://localhost:3000/admin/pet-categories/create we get the results as below

In addition, Sequelize also supports you to configure so that you can see the details of the sql statements generated by the ORM. Change a bit in the config for the database connection in the app module.

SequelizeModule.forRoot({
   uri: 'mysql://root:123456@localhost/nestjs_tutorial_2023',
   dialect: 'mysql',
   models: models,
   logging: console.log,
}),

Search PetCategory - list

@Controller('admin/pet-categories')
export class ManagePetCategoryController {
   @Get('')
   @Render('pet/admin/manage-pet-category/list')
   async getList() {
     const petCategories = await PetCategory.findAll();
     return {
       petCategories,
     };
   }

<%- include('layouts/admin/header'); %>
<section class="col-12">
   <div class="card">
     <div class="card-body">
       <h5 class="card-title">List Pet Categories</h5>
       <div class="table-responsive">
         <table class="table table-light table-striped">
           <thead>
             <tr>
               <th scope="col" style="width: 360px">ID</th>
               <th scope="col">Name</th>
             </tr>
           </thead>
           <tbody>
             <% petCategories.forEach(petCategory => { %>
             <tr class="">
               <td><%= petCategory.id %></td>
               <td><%= petCategory.name %></td>
             </tr>
             <% }) %>
           </tbody>
         </table>
       </div>
     </div>
   </div>
</section>
<%- include('layouts/admin/footer'); %>

// find all
const petCategories = await PetCategory.findAll();
// delete
await PetCategory.destroy({ where: { id } });
// create
const newPetCategory = await PetCategory.create({ ...object });
// find by primary key
const petCategory = await PetCategory.findByPk(id);
// update
await petCategory.update(object);

Update source code of ManagePetCategory controllers and views

import {
  Body,
  Controller,
  Delete,
  Get,
  Param,
  Post,
  Redirect,
  Render,
} from '@nestjs/common';
import { CreatePetCategoryDto } from 'src/pet/dtos/pet-dto';
import { plainToInstance } from 'class-transformer';
import { validate, ValidationError } from 'class-validator';
import { FormDataRequest } from 'nestjs-form-data';
import { PetCategory } from 'src/pet/models/pet-category.model';
import { Response } from 'express';

const transformError = (error: ValidationError) => {
  const { property, constraints } = error;
  return {
    property,
    constraints,
  };
};
@Controller('admin/pet-categories')
export class ManagePetCategoryController {
  @Get('')
  @Render('pet/admin/manage-pet-category/list')
  async getList() {
    const petCategories = await PetCategory.findAll();
    return {
      petCategories,
    };
  }

  @Post('delete/:id')
  @Redirect('/admin/pet-categories/')
  async deleteOne(@Param() { id }: { id: string }) {
    await PetCategory.destroy({ where: { id } });
  }

  @Get('create')
  @Render('pet/admin/manage-pet-category/create')
  view_create() {
    // a form
    return {
      data: {
        mode: 'create',
      },
    };
  }

  @Post('create')
  @Render('pet/admin/manage-pet-category/create')
  @FormDataRequest()
  async create(@Body() createPetCategoryDto: CreatePetCategoryDto) {
    const data = {
      mode: 'create',
    };
    // validation
    const object = plainToInstance(CreatePetCategoryDto, createPetCategoryDto);
    const errors = await validate(object, {
      stopAtFirstError: true,
    });
    if (errors.length > 0) {
      Reflect.set(data, 'error', 'Please correct all fields!');
      const responseError = {};
      errors.map((error) => {
        const rawError = transformError(error);
        Reflect.set(
          responseError,
          rawError.property,
          Object.values(rawError.constraints)[0],
        );
      });
      Reflect.set(data, 'errors', responseError);
      return { data };
    }
    // set value and show success message
    Reflect.set(data, 'values', object);

    // create PetCategory
    const newPetCategory = await PetCategory.create({ ...object });

    Reflect.set(
      data,
      'success',
      `Pet Category : ${newPetCategory.id} - ${newPetCategory.name} has been created!`,
    );
    // success
    return { data };
  }

  @Get(':id')
  @Render('pet/admin/manage-pet-category/create')
  async getDetail(@Param() { id }: { id: string }) {
    const data = {
      mode: 'edit',
    };
    const petCategory = await PetCategory.findByPk(id);
    Reflect.set(data, 'values', petCategory);
    return { data };
  }

  @Post(':id')
  @Render('pet/admin/manage-pet-category/create')
  @FormDataRequest()
  async updateOne(
    @Param() { id }: { id: string },
    @Body() createPetCategoryDto: CreatePetCategoryDto,
  ) {
    const data = {
      mode: 'edit',
    };
    const petCategory = await PetCategory.findByPk(id);
    // validation
    const object = plainToInstance(CreatePetCategoryDto, createPetCategoryDto);
    const errors = await validate(object, {
      stopAtFirstError: true,
    });
    if (errors.length > 0) {
      Reflect.set(data, 'error', 'Please correct all fields!');
      const responseError = {};
      errors.map((error) => {
        const rawError = transformError(error);
        Reflect.set(
          responseError,
          rawError.property,
          Object.values(rawError.constraints)[0],
        );
      });
      Reflect.set(data, 'errors', responseError);
      return { data };
    }
    // set value and show success message
    await petCategory.update(object);

    Reflect.set(data, 'values', petCategory);
    return { data };
  }
}

Views - list.html

<%- include('layouts/admin/header'); %>
<section class="col-12">
  <div class="card">
    <div class="card-body">
      <h5 class="card-title">List Pet Categories</h5>
      <div class="pb-4">
        <a
          class="btn btn-primary"
          href="/admin/pet-categories/create"
          role="button"
          >New Pet Category</a
        >
      </div>
      <div class="table-responsive">
        <table class="table table-light table-striped">
          <thead>
            <tr>
              <th scope="col" style="width: 360px">ID</th>
              <th scope="col">Name</th>
              <th scope="col">Action</th>
            </tr>
          </thead>
          <tbody>
            <% petCategories.forEach(petCategory => { %>
            <tr class="">
              <td><%= petCategory.id %></td>
              <td><%= petCategory.name %></td>
              <td>
                <a
                  href="/admin/pet-categories/<%= petCategory.id %>"
                  title="Edit"
                  >Edit</a
                >
                <form
                  action="/admin/pet-categories/delete/<%= petCategory.id %>"
                  method="post"
                >
                  <button type="submit">Delete</button>
                </form>
              </td>
            </tr>
            <% }) %>
          </tbody>
        </table>
      </div>
    </div>
  </div>
</section>
<%- include('layouts/admin/footer'); %>

Views - Create/Edit

<%- include('layouts/admin/header'); %>
<section class="col-6">
  <form method="post" enctype="multipart/form-data">
    <div class="card">
      <div class="card-body">
        <h5 class="card-title">
          <% if (data.mode === 'create') { %> New Pet Category <% } %> <% if
          (data.mode === 'edit') { %> Edit Pet Category <% } %>
        </h5>
        <!-- error -->
        <% if (data.error){ %>
        <div class="alert alert-danger" role="alert"><%= data.error %></div>
        <% } %>
        <!-- success -->
        <% if (data.success){ %>
        <div class="alert alert-success" role="alert"><%= data.success %></div>
        <script type="text/javascript">
          setTimeout(() => {
            window.location.href = "/admin/pet-categories/";
          }, 2000);
        </script>
        <% } %>
        <div class="mb-3">
          <label for="title" class="form-label">Name</label>
          <div class="input-group has-validation">
            <input
              type="text"
              class="form-control <%= data.errors && data.errors['name'] ? 'is-invalid': '' %>"
              id="name"
              name="name"
              value="<%= data.values && data.values['name'] %>"
              placeholder="Pet Category name"
            />
            <% if (data.errors && data.errors['name']) { %>
            <div id="validationServerUsernameFeedback" class="invalid-feedback">
              <%= data.errors['name'] %>
            </div>
            <% } %>
          </div>
        </div>
      </div>
      <% if(!data.success) { %>
      <div class="mb-3 col-12 text-center">
        <button type="submit" class="btn btn-primary">Save</button>
      </div>
      <% } %>
    </div>
  </form>
</section>
<%- include('layouts/admin/footer'); %>

Generate data seed with sequelize-cli

Note at this step, if using generate of cli, target directory in sequelize's config is now dist directory.

Therefore, we need to slightly adjust the config for .sequelizerc as follows. A next note, is to remember to edit the extension file for the seeding or migrations file to the ts extension.

const path = require("path");

const database_dist = process.env.NODE_ENV === "production" ? "dist" : "src";

module.exports = {
   config: path.resolve(`./${database_dist}/database/config/config.js`),
   "seeders-path": path.resolve(`./${database_dist}/database/seeders`),
   "migrations-path": path.resolve(`./${database_dist}/database/migrations`),
};

# create migration
npx sequelize-cli migration:create --name create-pet-category-table --migrations-path ./src/database/migrations
# create seed
npx sequelize-cli seed:generate --name pet-category
# build
npm run build
# run migration/seeds
NODE_ENV=production npx sequelize-cli db:migrate --env production

import { Column, DataType, Model, Table } from "sequelize-typescript";

export const PetCategoryTableName = "pet_categories";

@Table({
   tableName: PetCategoryTableName,
})
export class PetCategory extends Model {
   @Column({
     primaryKey: true,
     type: DataType.UUID,
     defaultValue: DataType.UUIDV4,
   })
   id?: string;

   @Column({
     type: DataType.STRING(60),
     allowNull: false,
   })
   name: string;
}

src\database\seeders\20230706141027-pet-category.ts

"use strict";

import {
   PetCategory,
   PetCategoryTableName,
} from "src/pet/models/pet-category.model";

/** @type {import('sequelize-cli').Migration} */
module.exports = {
   async up(queryInterface, Sequelize) {
     /**
      * Add seed commands here.
      *
      * Example:
      * await queryInterface.bulkInsert('People', [{
      * name: 'John Doe',
      * isBetaMember: false
      * }], {});
      */
     await queryInterface.bulkInsert(
       PetCategoryTableName,
       [
         { name: "Dogs" },
         { name: "Cats" },
         { name: "Pigs" },
         { name: "Birds" },
         { name: "Others" },
       ],
       {}
     );
   },

   async down(queryInterface, Sequelize) {
     /**
      * Add commands to revert seed here.
      *
      * Example:
      * await queryInterface.bulkDelete('People', null, {});
      */
     await queryInterface.bulkDelete(PetCategoryTableName, null, {});
   },
};

Everything looks fine, however, when running the migrate seed command, you may get the error described here here

Loaded configuration file "dist\database\config\config.js".
Using environment "production".
== 20230706141027-pet-category: migrating =======

ERROR: Field 'id' doesn't have a default value

To bypass this issue while sequelize doesn't have a patch for this bug, at the time of migration, we will have to generate uuid directly, using this package

Update a bit the seed file src\database\seeders\20230706141027-pet-category.ts

"use strict";

import { PetCategoryTableName } from "src/pet/models/pet-category.model";

import { v4 as uuidv4 } from "uuid";

/** @type {import('sequelize-cli').Migration} */
module.exports = {
   async up(queryInterface, Sequelize) {
     /**
      * Add seed commands here.
      *
      * Example:
      * await queryInterface.bulkInsert('People', [{
      * name: 'John Doe',
      * isBetaMember: false
      * }], {});
      */
     await queryInterface.bulkInsert(
       PetCategoryTableName,
       [
         { id: uuidv4(), name: "Dogs" },
         { id: uuidv4(), name: "Cats" },
         { id: uuidv4(), name: "Pigs" },
         { id: uuidv4(), name: "Birds" },
         { id: uuidv4(), name: "Others" },
       ],
       {}
     );
   },

   async down(queryInterface, Sequelize) {
     /**
      * Add commands to revert seed here.
      *
      * Example:
      * await queryInterface.bulkDelete('People', null, {});
      */
     await queryInterface.bulkDelete(PetCategoryTableName, null, {});
   },
};

$ NODE_ENV=production npx sequelize-cli db:seed:all

Sequelize CLI [Node: 16.19.1, CLI: 6.6.1, ORM: 6.32.1]

Loaded configuration file "dist\database\config\config.js".
Using environment "production".
== 20230706141027-pet-category: migrating =======
== 20230706141027-pet-category: migrated (0.155s)

And results

Feel free to read the full courses at NestJS Course Lesson 04 - Model and Repository Pattern

DEV Community

NestJS Course Lesson 04 - Model and Repository Pattern

Source Code

Lesson 04

1. Configure NestJS to work with MySQL via Sequelize

Special note for the model here

Generate data seed with sequelize-cli

Top comments (0)

Read next

change default excel template

Guru Nanak dev ji Jayanti special animation using html css and javascript code

Best Apps for Salesforce Document Management

Why Become An AWS Certified Solutions Architect In 2025?