Building a Node.js Backend with NestJS, Sequelize, and MySQL: A Step-by-Step Guide

The article begins with a brief introduction explaining why Node.js, despite being single‑threaded, is suitable for small projects and how NestJS offers a decorator‑based syntax similar to Java SpringBoot, making it a good choice for learning backend development.

Project Initialization

# Enter project directory
cd full-stack-demo/packages
# Install NestJS CLI globally
npm i -g @nestjs/cli
# Create a new NestJS project
nest new node-server-demo
# Move into the project directory
cd node-server-demo
# Run the development server
npm run start:dev

After creating the project, the author outlines the recommended folder structure, including common utilities, configuration files, controllers, services, DTOs, entities, modules, and the main entry point.

Choosing and Configuring MySQL

The guide advises installing MySQL (or a GUI tool like MySQL Workbench) and creating a database with appropriate character sets. It emphasizes setting a root password and provides download links for various platforms.

Example SQL for creating a table:

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

Another example shows a more detailed log table definition with primary key, unique index, and additional indexes.

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
);

Integrating Sequelize

# Install Sequelize and related packages
npm install --save @nestjs/sequelize sequelize sequelize-typescript mysql2
# Install TypeScript definitions
npm install --save-dev @types/sequelize

Configuration of Sequelize in the NestJS module:

import { Module } from '@nestjs/common';
import { SequelizeModule } from '@nestjs/sequelize';
import { Log } from '@/entities/Log';

@Module({
  imports: [
    SequelizeModule.forRoot({
      dialect: 'mysql',
      host: '127.0.0.1',
      port: 3306,
      username: 'root',
      password: 'hello',
      database: 'world',
      synchronize: true,
      models: [Log],
      autoLoadModels: true,
    }),
    LogModule,
    UserModule,
  ],
})
export class AppModule {}

Entity definition using sequelize‑typescript decorators:

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

@Table({ tableName: 'test_sys_req_log' })
export class Log extends Model
{
  @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;
}

Service example for creating a log entry with basic validation:

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

  async create(createLogDto: AddLogDto): Promise
> {
    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();
  }
}

Controller example for handling user queries:

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 };
  }
}

The guide also mentions optional enhancements such as adding GraphQL for precise data queries, integrating Swagger for API documentation, implementing middleware for authentication, and setting up database replication, read/write splitting, and Redis caching.

Finally, the article notes deployment considerations, suggesting that the backend can be deployed on standard Node.js hosting or cloud databases, and encourages developers to explore further features in the official NestJS documentation.

END