NestJs + TypeOrm + Ant Design Pro

portal

Online demo account: guest, password: 123456
The source address

This chapter content

TypeOrm installation
MySQL library/table creation, data initialization
Create user persistence to the database

Environment to prepare

Make sure MySQL is installed on the operating system (>= 5.7)

Install TypeOrm and MySql dependencies in project:

$ npm install --save @nestjs/typeorm typeorm mysql
Copy the code

There are many ways to create data tables using TypeOrm. Refer to TypeOrm for details. For convenience, we use scripts to create data tables, regenerate them into model, create docs folder in the root directory, create a new stock-demo. SQL file, and write initialization scripts to initialize several stock data at the same time:

-- Create database
CREATE SCHEMA `stock_demo` DEFAULT CHARACTER SET utf8mb4 ;

-- ---------------------------------------
-- User table simplifies roles
-- ---------------------------------------
CREATE TABLE `st_user` (
  id int(11) NOT NULL AUTO_INCREMENT COMMENT 'primary key id',
  mobile varchar(20) NOT NULL DEFAULT ' ' COMMENT 'Phone number',
  name varchar(20) NOT NULL DEFAULT ' ' COMMENT 'nickname',
  role tinyint(1) NOT NULL DEFAULT '0' COMMENT 'Role: 100 Super administrator, 0 Common user',
  password varchar(100) NOT NULL DEFAULT ' ' COMMENT 'password',
  salt varchar(100) NOT NULL DEFAULT ' ' COMMENT 'Code salt',
  create_dt datetime NOT NULL DEFAULT now() COMMENT 'Creation time',
  update_dt timestamp NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP COMMENT 'Modify time',
  is_delete tinyint(1) NOT NULL DEFAULT '0' COMMENT 'Delete or not'.PRIMARY KEY (`id`),
  UNIQUE KEY `uk_mobile` (`mobile`) 
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;

-- ---------------------------------------
- stock list
-- ---------------------------------------
CREATE TABLE `stock` (
  id int(11) NOT NULL AUTO_INCREMENT COMMENT 'primary key id',
  code varchar(10) NOT NULL DEFAULT ' ' COMMENT 'Ticker symbol',
  name varchar(30) NOT NULL DEFAULT ' ' COMMENT 'Name of stock',
  market varchar(10) NOT NULL DEFAULT ' ' COMMENT 'Stock market',
  price decimal(12.4) NOT NULL DEFAULT '0.00' COMMENT 'Current share price',  
  pe decimal(12.4) NOT NULL DEFAULT '0.00' COMMENT 'Current PE (price-to-earnings ratio)',
  pe_avg decimal(12.4) NOT NULL DEFAULT '0.00' COMMENT 'Average PE (price/earnings ratio)',
  pe_ttm decimal(12.4) NOT NULL DEFAULT '0.00' COMMENT 'Current PE/TTM',
  pe_ttm_avg decimal(12.4) NOT NULL DEFAULT '0.00' COMMENT 'Average PE/TTM',
  pe_ttm_rate decimal(12.4) NOT NULL DEFAULT '0.00' COMMENT 'Ratio of latest PET_TM to average',
  pe_ttm_mid decimal(12.4) NOT NULL DEFAULT '0.00' COMMENT 'PE_TTM median',
  total_mv decimal(12.4) NOT NULL DEFAULT '0.00' COMMENT 'Total market value',
  source_data json NULL COMMENT 'Data source',
  create_dt datetime NOT NULL DEFAULT now() COMMENT 'Creation time',
  update_dt timestamp NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP COMMENT 'Modify time',
  is_delete tinyint(1) NOT NULL DEFAULT '0' COMMENT 'Delete or not'.PRIMARY KEY (`id`),
  UNIQUE KEY `uk_stock_code` (`code`) 
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;

-- ---------------------------------------
-- Stock ledger: records the daily PE and PB equivalent, simplified to record only PE
-- ---------------------------------------
CREATE TABLE `stock_log` (
  id int(11) NOT NULL AUTO_INCREMENT COMMENT 'primary key id',
  code varchar(10) NOT NULL DEFAULT ' ' COMMENT 'Ticker symbol',
  log_date date NOT NULL COMMENT 'date',
  pe decimal(12.4) NOT NULL DEFAULT '0.00' COMMENT 'Current PE (price-to-earnings ratio)',
  pe_ttm decimal(12.4) NOT NULL DEFAULT '0.00' COMMENT 'Current PE/TTM',
  total_mv decimal(16.4) NOT NULL DEFAULT '0.00' COMMENT 'Total market value',
  create_dt datetime NOT NULL DEFAULT now() COMMENT 'Creation time',
  update_dt timestamp NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP COMMENT 'Modify time',
  is_delete tinyint(1) NOT NULL DEFAULT '0' COMMENT 'Delete or not'.PRIMARY KEY (`id`),
  UNIQUE KEY `uk_code_log_date` (`code` ASC,`log_date` ASC),
  INDEX `idx_code` (`code` ASC)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;

-- ---------------------------------------
-- User stock selection list
-- ---------------------------------------
CREATE TABLE `user_stock` (
  id int(11) NOT NULL AUTO_INCREMENT COMMENT 'primary key id',
  uid int(11) NOT NULL DEFAULT '0' COMMENT 'user id',
  code varchar(10) NOT NULL DEFAULT ' ' COMMENT 'Ticker symbol',
  create_dt datetime NOT NULL DEFAULT now() COMMENT 'Creation time',
  update_dt timestamp NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP COMMENT 'Modify time',
  is_delete tinyint(1) NOT NULL DEFAULT '0' COMMENT 'Delete or not'.PRIMARY KEY (`id`),
  UNIQUE KEY `uk_uid_code` (`uid` ASC,`code` ASC),
  FOREIGN KEY (uid) REFERENCES st_user(id),
  FOREIGN KEY (code) REFERENCES stock(code)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;

-- initialize stock_log
INSERT INTO `stock_log` VALUES 
 (1.'600000'.'2021-03-26'.0.0000.5.3445.0.0000.'the 2021-03-27 16:20:55'.NULL.0),
 (2.'600000'.'2021-03-25'.0.0000.5.3496.0.0000.'the 2021-03-27 16:20:55'.NULL.0),
 (3.'600000'.'2021-03-24'.0.0000.5.3747.0.0000.'the 2021-03-27 16:20:55'.NULL.0),
 (4.'600000'.'2021-03-23'.0.0000.5.4251.0.0000.'the 2021-03-27 16:20:55'.NULL.0),
 (5.'600000'.'2021-03-22'.0.0000.5.4100.0.0000.'the 2021-03-27 16:20:55'.NULL.0),
 (6.'600000'.'2021-03-19'.0.0000.5.3596.0.0000.'the 2021-03-27 16:20:55'.NULL.0),
 (7.'600000'.'2021-03-18'.0.0000.5.5509.0.0000.'the 2021-03-27 16:20:55'.NULL.0),
 (8.'600000'.'2021-03-17'.0.0000.5.4955.0.0000.'the 2021-03-27 16:20:55'.NULL.0),
 (9.'600000'.'2021-03-16'.0.0000.5.5911.0.0000.'the 2021-03-27 16:20:55'.NULL.0),
 (10.'600000'.'2021-03-15'.0.0000.5.5962.0.0000.'the 2021-03-27 16:20:55'.NULL.0),
 (16.'600006'.'2021-03-26'.0.0000.21.0770.0.0000.'the 2021-03-27 16:21:51'.NULL.0),
 (17.'600006'.'2021-03-25'.0.0000.20.4443.0.0000.'the 2021-03-27 16:21:51'.NULL.0),
 (18.'600006'.'2021-03-24'.0.0000.20.5775.0.0000.'the 2021-03-27 16:21:51'.NULL.0),
 (19.'600006'.'2021-03-23'.0.0000.20.9105.0.0000.'the 2021-03-27 16:21:51'.NULL.0),
 (20.'600006'.'2021-03-22'.0.0000.21.4765.0.0000.'the 2021-03-27 16:21:51'.NULL.0),
 (21.'600006'.'2021-03-19'.0.0000.20.6774.0.0000.'the 2021-03-27 16:21:51'.NULL.0),
 (22.'600006'.'2021-03-18'.0.0000.21.0104.0.0000.'the 2021-03-27 16:21:51'.NULL.0),
 (23.'600006'.'2021-03-17'.0.0000.21.3766.0.0000.'the 2021-03-27 16:21:51'.NULL.0),
 (24.'600006'.'2021-03-16'.0.0000.21.7096.0.0000.'the 2021-03-27 16:21:51'.NULL.0),
 (25.'600006'.'2021-03-15'.0.0000.20.8772.0.0000.'the 2021-03-27 16:21:51'.NULL.0);
 
-- Initialize stock
INSERT INTO `stock` VALUES 
(1.'600000'.'Pudong Development Bank'.'sh'.0.0000.0.0000.0.0000.5.3445.6.0118.0.8890.0.0000.0.0000.NULL.'the 2021-03-27 16:17:28'.'the 2021-03-26 23:50:00'.0),
(2.'600004'.'Baiyun Airport'.'sh'.0.0000.0.0000.0.0000.244.6567.44.3531.5.5161.0.0000.0.0000.NULL.'the 2021-03-27 16:17:28'.'the 2021-03-26 23:50:00'.0),
(3.'600006'.'Dongfeng Motor'.'sh'.0.0000.0.0000.0.0000.21.0770.41.6499.0.5061.0.0000.0.0000.NULL.'the 2021-03-27 16:17:28'.'the 2021-03-26 23:50:00'.0),
(4.'600007'.'China International Trade'.'sh'.0.0000.0.0000.0.0000.15.0099.22.2433.0.6748.0.0000.0.0000.NULL.'the 2021-03-27 16:17:28'.'the 2021-03-26 23:50:00'.0),
(5.'600008'.'Initial Share'.'sh'.0.0000.0.0000.0.0000.17.3383.31.1689.0.5563.0.0000.0.0000.NULL.'the 2021-03-27 16:17:28'.'the 2021-03-26 23:50:00'.0),
(6.'600011'.'Huaneng International'.'sh'.0.0000.0.0000.0.0000.15.0276.35.7993.0.4198.0.0000.0.0000.NULL.'the 2021-03-27 16:17:28'.'the 2021-03-26 23:50:00'.0),
(7.'600012'.'Wan Tong Expressway'.'sh'.0.0000.0.0000.0.0000.14.2025.15.3841.0.9232.0.0000.0.0000.NULL.'the 2021-03-27 16:17:28'.'the 2021-03-26 23:50:00'.0),
(8.'600015'.Huaxia Bank.'sh'.0.0000.0.0000.0.0000.4.6650.5.4504.0.8559.0.0000.0.0000.NULL.'the 2021-03-27 16:17:28'.'the 2021-03-26 23:50:00'.0),
(9.'600016'.'Minsheng Bank'.'sh'.0.0000.0.0000.0.0000.4.9715.5.7336.0.8671.0.0000.0.0000.NULL.'the 2021-03-27 16:17:28'.'the 2021-03-26 23:50:00'.0),
(10.'600017'.'Rizhao Port'.'sh'.0.0000.0.0000.0.0000.12.5360.32.0243.0.3915.0.0000.0.0000.NULL.'the 2021-03-27 16:17:28'.'the 2021-03-26 23:50:00'.0),
(11.'600018'.Sipg Group.'sh'.0.0000.0.0000.0.0000.13.2221.15.3694.0.8603.0.0000.0.0000.NULL.'the 2021-03-27 16:17:28'.'the 2021-03-26 23:50:00'.0),
(12.'600019'.'Baosteel'.'sh'.0.0000.0.0000.0.0000.15.8902.16.5238.0.9617.0.0000.0.0000.NULL.'the 2021-03-27 16:17:28'.'the 2021-03-26 23:50:00'.0),
(13.'600020'.'Zhongyuan Expressway'.'sh'.0.0000.0.0000.0.0000.28.1624.12.6981.2.2178.0.0000.0.0000.NULL.'the 2021-03-27 16:17:28'.'the 2021-03-26 23:50:00'.0),
(14.'600021'.'Shanghai Electric Power'.'sh'.0.0000.0.0000.0.0000.17.4582.21.4717.0.8131.0.0000.0.0000.NULL.'the 2021-03-27 16:17:28'.'the 2021-03-26 23:50:00'.0),
(15.'600022'.Shandong Iron and Steel.'sh'.0.0000.0.0000.0.0000.24.8837.45.3337.0.5489.0.0000.0.0000.NULL.'the 2021-03-27 16:17:28'.'the 2021-03-26 23:50:00'.0),
(16.'600023'.Zheneng Electric Power.'sh'.0.0000.0.0000.0.0000.8.5669.13.0968.0.6541.0.0000.0.0000.NULL.'the 2021-03-27 16:17:28'.'the 2021-03-26 23:50:00'.0),
(17.'600025'.'Huaneng Hydropower'.'sh'.0.0000.0.0000.0.0000.19.6953.22.2212.0.8863.0.0000.0.0000.NULL.'the 2021-03-27 16:17:28'.'the 2021-03-26 23:50:00'.0),
(18.'600026'.Medium-high sea energy.'sh'.0.0000.0.0000.0.0000.9.6214.36.6940.0.2622.0.0000.0.0000.NULL.'the 2021-03-27 16:17:28'.'the 2021-03-26 23:50:00'.0),
(19.'600027'.'Huadian International'.'sh'.0.0000.0.0000.0.0000.8.2112.19.8147.0.4144.0.0000.0.0000.NULL.'the 2021-03-27 16:17:28'.'the 2021-03-26 23:50:00'.0),
(20.'600028'.'Sinopec'.'sh'.0.0000.0.0000.0.0000.13.5744.18.3329.0.7404.0.0000.0.0000.NULL.'the 2021-03-27 16:17:28'.'the 2021-03-26 23:50:00'.0);
Copy the code

When the data is ready, use the Generator tool to generate the Entity required by our code and install the tool globally:

$ npm i -g typeorm-model-generator
Copy the code

Execute the generate command:

$ typeorm-model-generator -h database-host -d stock_demo -u root -x 'database password' -e mysql -o .
Copy the code

After the command is executed successfully, entities directory and its configuration file are generated in the current directory, copy entities directory to SRC directory, and enter app.module.ts to configure TypeOrm:

import { Module } from '@nestjs/common';
import { AppController } from './app.controller';
import { AppService } from './app.service';
import { UserController } from './modules/user/user.controller';
import { UserModule } from './modules/user/user.module';
import { TypeOrmModule } from '@nestjs/typeorm';

@Module({
  imports: [
    TypeOrmModule.forRoot({
      name: 'default'.type: 'mysql'.host: 'data base host'.port: 3306.username: 'data base username'.password: 'data base password'.database: 'stock_demo'.synchronize: false.entities: [__dirname + '/entities/*.js'],
    }),
    UserModule,
  ],
  controllers: [AppController, UserController],
  providers: [AppService],
})
export class AppModule {}
Copy the code

At this point we have completed TypeOrm and database related configuration and initialization.

Create a user

Open the user.module.ts file and inject the user Entity dependency:

import { Module, HttpModule } from '@nestjs/common';
import { UserController } from './user.controller';
import { UserService } from './user.service';
import { StUser } from '.. /.. /entities/StUser';
import { TypeOrmModule } from '@nestjs/typeorm';

@Module({
  imports: [TypeOrmModule.forFeature([StUser]), HttpModule],
  controllers: [UserController],
  providers: [UserService],
  exports: [UserService],
})
export class UserModule {}
Copy the code

In a project, the Entity is not directly exposed to the API exit. Instead, the DTO is created to define the data type to be transferred. In the user directory, the DTO directory is created, and the user.dto.

/** * Create user */
export class CreateUserDto {
  readonly name: string;
  readonly mobile: string;
  readonly password: string;
}
Copy the code

In order to ensure the security of the user account password, it is necessary to encrypt the created user password (for convenience, the salt value is also stored together), and install the BcryptJS library:

$ npm i bcryptjs -S
Copy the code

Open user.service, introduce dependencies, and add methods for creating users:

import { Injectable } from '@nestjs/common';
import { StUser } from '.. /.. /entities/StUser';
import { InjectRepository } from '@nestjs/typeorm';
import { Repository } from 'typeorm';
import { CreateUserDto } from './dto/user.dto';
// eslint-disable-next-line @typescript-eslint/no-var-requires
const bcrypt = require('bcryptjs');
import { BusiException } from '.. /.. /libs/filters/busi.exception';
import { BusiErrorCode } from '.. /.. /libs/enums/error-code-enum';

@Injectable(a)export class UserService {
  constructor(
    @InjectRepository(StUser)
    private readonly stUserRepository: Repository<StUser>,
  ) {}

  async create(dto: CreateUserDto) {
    const user = this.stUserRepository.create(dto);
    const salt = bcrypt.genSaltSync(10);
    user.salt = salt;
    user.password = bcrypt.hashSync(user.password, salt);
    return this.stUserRepository
      .save(user)
      .then((res) = > {
        return { id: res.id };
      })
      .catch((err) = > {
        throw newBusiException(BusiErrorCode.PARAM_ERROR, err.message); }); }}Copy the code

In the user.controller.ts file, add the code to create the user:

import { Controller, Get, Body, Post, HttpStatus } from '@nestjs/common';
import { BusiException } from '.. /.. /libs/filters/busi.exception';
import { BusiErrorCode } from '.. /.. /libs/enums/error-code-enum';
import { UserService } from './user.service';
import { CreateUserDto } from './dto/user.dto';

@Controller('user')
export class UserController {
  constructor(private readonly userService: UserService){}...@Post('create')
  async create(@Body() user: CreateUserDto) {
    return this.userService.create(user); }}Copy the code

To run the program, the use of API debugging tools to create user test, address: http://localhost:3000/user/create POST the content-type: application/json, parameters:

{
    "name":"Young"."mobile":"18888888888"."password":"123456"
}
Copy the code

Return result:

{
    "data": {
        "id": 7
    },
    "code": 0."message": "success"
}
Copy the code

The database validates whether the creation was successful:

mysql> select * from st_user order by id desc
Copy the code

In the next chapter we add permission validation and scheduled tasks to the API.

portal

This chapter content

Environment to prepare

Create a user

Related Posts

WebSocket Protocol Chapter 8 — Error Handling

Write promise, async, await

Horizontal comparison of Elder-tree, ZTree, ivew-Tree performance comparison analysis and source code modification custom components