10. TypeOrm with MSSQL

https://typeorm.io/

Giới thiệu

TypeORM là một Object-Relational Mapper (ORM) cho TypeScript và JavaScript (ES7, ES6, ES5). Nó giúp bạn tạo ra các đối tượng và cơ sở dữ liệu chung, tạo và thực thi truy vấn.

MSSQL (Microsoft SQL Server) là một hệ thống quản lý cơ sở dữ liệu phổ biến của Microsoft.

Lý do kết hợp TypeORM và MSSQL

TypeORM:

• Hỗ trợ TypeScript natively.
• Tích hợp chặt chẽ với modern ES/TS.
• Linh hoạt và mở rộng.

MSSQL:

• Bảo mật cao.
• Hiệu suất tốt.
• Hỗ trợ tốt từ Microsoft và cộng đồng
• Đã học từ trước.

Cài đặt các packages

npm install typeorm reflect-metadata mssql

Ví dụ

Mã nguồn

https://github.com/ngothanhtung/nodejs-tutorials/tree/main/Batch31/express-typescript

Cấu hình datasource

data-source.ts

import 'reflect-metadata';

import { DataSource } from 'typeorm';

export const AppDataSource = new DataSource({
  type: 'mssql',
  host: '113.160.224.121',
  port: 1433,
  username: 'developer',
  password: 'developer',
  database: 'TypeOrm',
  entities: ['entities/**/*.entity{.ts,.js}'],
  synchronize: true,
  logging: false,
  options: {
    encrypt: false,
  },
});

Tạo entity

entities/category.entity.ts

import { Column, Entity, PrimaryGeneratedColumn, BaseEntity, OneToMany } from 'typeorm';
import { Product } from './product.entity';

@Entity({ name: 'Categories' })
export class Category extends BaseEntity {
  @PrimaryGeneratedColumn({ name: 'Id' })
  id: number;

  // ----------------------------------------------------------------------------------------------
  // NAME
  // ----------------------------------------------------------------------------------------------
  @Column({ name: 'Name', unique: true, length: 50 })
  name: string;

  // ----------------------------------------------------------------------------------------------
  // DESCRIPTION
  // ----------------------------------------------------------------------------------------------
  @Column({ name: 'Description', length: 500, nullable: true })
  description: string;

  // ----------------------------------------------------------------------------------------------
  // RELATIONS
  // ----------------------------------------------------------------------------------------------
  @OneToMany(() => Product, (p) => p.category)
  products: Product[];
}

Tạo router

routes/categories.ts

import express, { Express, Request, Response } from 'express';

import { AppDataSource } from '../data-source';
import { Category } from '../entities/category.entity';

const router = express.Router();

const repository = AppDataSource.getRepository(Category);

/* GET categories */
router.get('/', async (req: Request, res: Response, next: any) => {
  try {
    const categories = await repository.find();
    if (categories.length === 0) {
      res.status(204).send();
    } else {
      res.json(categories);
    }
  } catch (error) {
    console.error(error);
    res.status(500).json({ error: 'Internal server error' });
  }
});

/* GET category by id */
router.get('/:id', async (req: Request, res: Response, next: any) => {
  try {
    const category = await repository.findOneBy({ id: parseInt(req.params.id) });
    if (!category) {
      return res.status(404).json({ error: 'Not found' });
    }
    res.json(category);
  } catch (error) {
    console.error(error);
    res.status(500).json({ error: 'Internal server error' });
  }
});

/* POST category */
router.post('/', async (req: Request, res: Response, next: any) => {
  try {
    const category = new Category();
    category.name = req.body.name;
    category.description = req.body.description;
    await repository.save(category);
    res.status(201).json(category);
  } catch (error) {
    console.error(error);
    res.status(500).json({ error: 'Internal server error' });
  }
});

/* PATCH category */
router.patch('/:id', async (req: Request, res: Response, next: any) => {
  try {
    const category = await repository.findOneBy({ id: parseInt(req.params.id) });
    if (!category) {
      return res.status(404).json({ error: 'Not found' });
    }

    Object.assign(category, req.body);
    await repository.save(category);

    const updatedCategory = await repository.findOneBy({ id: parseInt(req.params.id) });
    res.json(updatedCategory);
  } catch (error) {
    console.error(error);
    res.status(500).json({ error: 'Internal server error' });
  }
});

/* DELETE category */
router.delete('/:id', async (req: Request, res: Response, next: any) => {
  try {
    const category = await repository.findOneBy({ id: parseInt(req.params.id) });
    if (!category) {
      return res.status(404).json({ error: 'Not found' });
    }
    await repository.delete({
      id: category.id,
    });
    res.status(200).send();
  } catch (error) {
    console.error(error);
    res.status(500).json({ error: 'Internal server error' });
  }
});

export default router;

Bài tập CRUD với RESTful API

Cấu trúc database online-shop

Categories

Id	Column Name	Data Type	Null	Key	Default	Constraint
1	Id	int		PK		AUTONUMBER
2	Name	nvarchar(50)				UNIQUE
3	Description	nvarchar(500)	yes

Suppliers

Id	Column Name	Data Type	Null	Key	Default	Constraint
1	Id	int		PK		AUTONUMBER
2	Name	nvarchar(100)
3	Email	varchar(50)				UNIQUE
4	PhoneNumber	varchar(50)				UNIQUE
5	Address	nvarchar(500)

Customers

Id	Column Name	Data Type	Null	Key	Default	Constraint
1	Id	int		PK		AUTONUMBER
2	FirstName	nvarchar(50)
3	LastName	nvarchar(50)
4	Email	varchar(50)				UNIQUE
5	PhoneNumber	varchar(50)				UNIQUE
6	Address	nvarchar(500)
7	Birthday	datetime	yes

Employees

Id	Column Name	Data Type	Null	Key	Default	Constraint
1	Id	int		PK		AUTONUMBER
2	FirstName	nvarchar(50)
3	LastName	nvarchar(50)
4	Email	varchar(50)				UNIQUE
5	PhoneNumber	varchar(50)				UNIQUE
6	Address	nvarchar(500)
7	Birthday	datetime	yes

Products

Id	Column Name	Data Type	Null	Key	Default	Constraint
1	Id	int		PK		AUTONUMBER
2	Name	nvarchar(100)
3	Price	money				n > 0
4	Discount	decimal(18,2)			0	0 <= n <= 90
5	Stock	decimal(18,2)			0	n >= 0
6	Description	nvarchar(max)	yes
7	CategoryId	int		FK		Refrence to Categories (Id)
8	SupplierId	int		FK		Refrence to Suppliers (Id)

Orders

Id	Column Name	Data Type	Null	Key	Default	Constraint
1	Id	int		PK		AUTONUMBER
2	CreatedDate	datetime			NOW
3	ShippedDate	datetime	yes			n < CreatedDate
4	Status	varchar(50)			WAITING	n in [WAINTING, COMPLETED, CANCEL]
5	Description	nvarchar(max)
6	ShippingAddress	nvarchar(500)	yes
7	ShippingCity	nvarchar(50)
8	PaymentType	varchar(20)			CASH	n in [CASH, CREDIT CARD]
9	CustomerId	int		FK		Refrence to Customers (Id)
10	EmployessId	int		FK		Refrence to Employees (Id)

---

Order Details

Id	Column Name	Data Type	Null	Key	Default	Constraint
1	OrderId	int		PK + FK		Refrence to Orders (Id)
2	ProductId	int		PK + FK		Refrence to Products (Id)
3	Quantity	decimal(18,2)				n > 0
4	Price	decimal(18,2)				n > 0
5	Discount	decimal(18,2)				0 <= n <=90

1. Viết RESTful API cho Categories

• Get all
• Get by id
• Create
• Update by id
• Delete by id

2. Viết RESTful API cho Suppliers

• Get all
• Get by id
• Create
• Update by id
• Delete by id

3. Viết RESTful API cho Customers

• Get all
• Get by id
• Create
• Update by id
• Delete by id

4. Viết RESTful API cho Employees

• Get all
• Get by id
• Create
• Update by id
• Delete by id

5. Viết RESTful API cho Products

• Get all
• Get by id
• Create
• Update by id
• Delete by id

6. Viết RESTful API cho Orders

• Get all
• Get by id
• Create
• Update by id
• Delete by id