Build a Full‑Stack Node Service with NestJS, Sequelize & MySQL – Step‑by‑Step Guide

Introduction

The article explains why to use a Node service for small projects, highlights learning decorator syntax, exploring a new framework, and understanding server‑side debugging, then outlines the goal of building a NestJS + Sequelize + MySQL demo that mirrors Java SpringBoot structure.

Step 1: Run the Project

Install the NestJS CLI, create a new project, and start the development server.

<code># cd full-stack-demo/packages
npm i -g @nestjs/cli
nest new node-server-demo
cd node-server-demo
npm run start:dev</code>

Project Structure

common – shared utilities

config – configuration files

controller – request handlers

service – business logic and DB interaction

dto – Data Transfer Objects for validation

entities – ORM entity definitions

module – NestJS modules registering services and controllers

Note: The server‑side architecture differs from front‑end frameworks but helps understand backend design.

Step 2: Configure MySQL

Install MySQL (example shown for macOS), create a database, set a root password, and configure character set. Use MySQL Workbench or other GUI tools if preferred.

Create a table with generated columns:

<code>CREATE TABLE people (
    first_name VARCHAR(100),
    last_name VARCHAR(100),
    full_name VARCHAR(200) AS (CONCAT(first_name, ' ', last_name))
);</code>

Additional example with indexes and constraints:

<code>CREATE TABLE `rrweb`.`test_sys_req_log` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `content` TEXT NOT NULL,
  `l_level` INT UNSIGNED NOT NULL,
  `l_category` VARCHAR(255) NOT NULL,
  `l_created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `l_updated_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE INDEX `id_UNIQUE` (`id` ASC) VISIBLE,
  INDEX `table_index` (`l_level` ASC, `l_category` ASC, `l_time` ASC) VISIBLE
);</code>

Step 3: Implement CRUD Basics

Define DTOs, entities, services, controllers, and modules to handle create, read, update, and delete operations.

DTO Example

<code>export class CreateUserDto {
  name: string;
  age: number;
  gender: string;
  job: string;
}

export class GetUserDto {
  id?: number;
  name: string;
}

export class GetUserInfoDto {
  id: number;
}</code>

Entity Example

<code>import { Model, Table, Column, PrimaryKey, DataType } from 'sequelize-typescript';

@Table({ tableName: 'test_sys_req_log' })
export class Log extends Model<Log> {
  @PrimaryKey
  @Column({ type: DataType.INTEGER, autoIncrement: true, field: 'id' })
  id: number;

  @Column({ field: 'content', type: DataType.TEXT })
  content: string;

  @Column({ field: 'l_level', type: DataType.INTEGER })
  level: number;

  @Column({ field: 'l_category' })
  category: string;

  @Column({ field: 'l_created_at', type: DataType.NOW, defaultValue: getNow() })
  createdAt: number;

  @Column({ field: 'l_updated_at', type: DataType.NOW, defaultValue: getNow() })
  updatedAt: number;
}</code>

Controller Example

<code>import { Controller, Get, Query } from '@nestjs/common';
import UserServices from '@/service/user';
import { GetUserDto, GetUserInfoDto } from '@/dto/user';

@Controller('user')
export class UserController {
  constructor(private readonly userService: UserServices) {}

  @Get('name')
  async findByName(@Query() getUserDto: GetUserDto) {
    return this.userService.read.findByName(getUserDto.name);
  }

  @Get('info')
  async findById(@Query() getUserInfoDto: GetUserInfoDto) {
    const user = await this.userService.read.findById(getUserInfoDto.id);
    return { gender: user.gender, job: user.job };
  }
}
</code>

Service Example (Create Log)

<code>@Injectable()
export class CreateLogService {
  constructor(@InjectModel(Log) private logModel: typeof Log) {}

  async create(createLogDto: AddLogDto): Promise<ResponseStatus<null>> {
    const { level = 1, content = '', category = 'INFO' } = createLogDto || {};
    const str = content.trim();
    if (!str) return getErrRes(500, '日志内容为空');
    const item = { level, category, content: str };
    await this.logModel.create(item);
    return getSucVoidRes();
  }
}
</code>

Module Registration

<code>@Module({
  imports: [SequelizeModule.forFeature([Log])],
  providers: [LogServices, CreateLogService, UpdateLogService, DeleteLogService, ReadLogService],
  controllers: [CreateLogController, RemoveLogController, UpdateLogController],
})
export class LogModule {}
</code>

After wiring everything, run the server, use a tool like Hoppscotch to test the endpoints, and verify that data is stored and retrieved from MySQL.

Finally, the article suggests deployment options, such as using cloud databases and standard Node.js deployment methods.