Typescript express 新手教程 S8 换sql类的DBMS,postgreSQL

太长不看

• 介绍了本应用 使用postgreSQL的操作示范。(简称pqsl)
• 搭配使用typeorm

使用docker安装数据库和管理界面 并连接数据库

类似之前的教程，仍旧使用docker来运行数据库，并且配合使用volume来存储容器中数据
注意我在postgres服务中 更改了宿主机的端口号，默认是5432，我改成了5460。这是因为我本机在5432端口运行有其他项目。

version: "3"
services:
  postgres:
    container_name: postgres
    image: postgres:latest
    ports:
      - "5460:5432"
    volumes:
      - postgres-data:/data/postgres
    env_file:
      - .env
    networks:
      - postgres

  pgadmin:
    container_name: pgadmin
    image: dpage/pgadmin4
    ports:
      - "8080:80"
    volumes:
      - pgadmin-data:/root/.pgadmin
    env_file:
      - .env
    networks:
      - postgres
volumes:
  postgres-data:
    driver: local
  pgadmin-data:
    driver: local
networks:
  postgres:
    driver: bridge

在.env文件中，配置了postgresql和 pgadmin的用户名密码等敏感信息。

# if use postgres
POSTGRES_USER=admin
POSTGRES_PASSWORD=admin
POSTGRES_DB=tutorial
POSTGRES_HOST=localhost
POSTGRES_PORT=5460

PORT=5000
PGADMIN_DEFAULT_EMAIL=admin@admin.com
PGADMIN_DEFAULT_PASSWORD=admin

这样就能使用docker-compose来 运行pgadmin和psql了

连接数据库

• 配置好连接参数
• 使用typeorm提供的方法，传入连接参数

配置连接参数，

# src/config/orm.config.ts
import { ConnectionOptions } from "typeorm";
/**
   * I had a similar issue, probably not related to the one of @zalper but with the same error message so I'll leave the solution here for anyone having the same problems.

I'm using Windows and had ProstgreSQL 12 installed.

At the same time I tried to run a postgres:10 docker container.

When I tried to connect to the database running in the docker container using psql, I always got an error saying psql: FATAL: password authentication failed for user "postgres".

The issue was that the postgres docker container was using the default port 5432 on localhost and the PostgreSQL server on Windows was using the same port on localhost. However, there were no errors when starting either of them.

Solutions:

Option 1: Stop the PostgreSQL service on Windows
Option 2 (using WSL): Completely uninstall Protgres 12 from Windows and install postgresql-client on WSL (sudo apt install postgresql-client-common postgresql-client libpq-dev)
Option 3: Change the port of the docker container
   * 
   */
const postgresConfig: ConnectionOptions = {
  type: "postgres",
  host: process.env.POSTGRES_HOST,
  // postgres默认端口是 5432 本机如果已经安装了 pqsl docker的 pqsl也使用5432端口，会报错，而且报错信息是 password 错误 之类的(很有可能是乱码,折磨啊)
  port: Number(process.env.POSTGRES_PORT),
  username: process.env.POSTGRES_USER,
  password: process.env.POSTGRES_PASSWORD,
  database: process.env.POSTGRES_DB,
  entities: ["src/**/*.entity{.ts,.js}"],
  // entities: [__dirname + "/../**/*.entity{.ts,.js}"],
  synchronize: true,
};

export default postgresConfig;

把连接函数封装，放到utils中

#src/utils/connectPostgres.ts
import { createConnection } from "typeorm";
import postgresConfig from "../config/orm.config";

/**连接到 postgres 数据库 */

async function connectPostgres() {
  try {
    const connection = await createConnection(postgresConfig);

    if (connection.isConnected) {
      console.log("连接postgres database成功");
    } else {
      console.log("连接postgres database发生初次连接失败错误");
      process.exit();
    }
  } catch (error) {
    console.log(error);

    console.log("连接postgres database发生意外错误");
    process.exit();
  }
}

export default connectPostgres;

先连接数据库，成功后才初始化app

#src/App.ts
// 连接数据库，需要数据库已经运行,
connectPostgres().then(() => {
  const app = new App(controllers);
});

CRUD

之前说了，要使用typeorm来简化crud, 这个简化是指不再手工写sql语句，而是使用typeorm提供的抽象（来完成Model）。
那么如何告诉typeorm创建表？首先需要创建模型(Model)，在typeorm里叫Entity，这个实体表示的是sql库里的表，有了实体，typeorm就会在sql里创建对应的表。

import { Column, Entity, PrimaryGeneratedColumn } from 'typeorm';
 
@Entity()
class Post {
  @PrimaryGeneratedColumn()
  public id?: number;
 
  @Column()
  public title: string;
 
  @Column()
  public content: string;
}
 
export default Post;

提普斯

• 上面几个装饰器都得靠reflect-metadata(r-m)才能用，r-m这个需要在server.ts中引入。
• 为了让reflect-metadata在Typescript里好用，还得把tsconfig的compilerOptions 加上 "emitDecoratorMetadata": true
• 当然之前也使用了装饰器，所以 "experimentalDecorators": true肯定早就有了
• 具体见代码仓库中的内容(postgres分支)

Repository

typeorm提供了 EntityManager和更为具体的Repository

你可以管理（insert, update, delete, load 等）任何实体。 EntityManager 就像放一个实体存储库的集合的地方
Repository就像EntityManager一样，但其操作仅限于具体实体

简单说，typeorm给我们提供了一个类，这个类上抽象了CRUD方法，使用这个抽象来操作数据库中的数据

操作示范如下:

import * as express from 'express';
import { getRepository } from 'typeorm';
import PostNotFoundException from '../exceptions/PostNotFoundException';
import Controller from '../interfaces/controller.interface';
import validationMiddleware from '../middleware/validation.middleware';
import CreatePostDto from './post.dto';
import Post from './post.entity';
 
class PostController implements Controller {
  public path = '/posts';
  public router = express.Router();
  private postRepository = getRepository(Post);
 
  constructor() {
    this.initializeRoutes();
  }
 
  private initializeRoutes() {
    this.router.post(this.path, validationMiddleware(CreatePostDto), this.createPost);
    this.router.get(this.path, this.getAllPosts);
    this.router.get(`${this.path}/:id`, this.getPostById);
    this.router.patch(`${this.path}/:id`, validationMiddleware(CreatePostDto, true), this.modifyPost);
    this.router.delete(`${this.path}/:id`, this.deletePost);
  }
 
  private createPost = async (request: express.Request, response: express.Response) => {
    const postData: CreatePostDto = request.body;
    const newPost = this.postRepository.create(postData);
    await this.postRepository.save(newPost);
    response.send(newPost);
  }
 
  private getAllPosts = async (request: express.Request, response: express.Response) => {
    const posts = await this.postRepository.find();
    response.send(posts);
  }
 
  private getPostById = async (request: express.Request, response: express.Response, next: express.NextFunction) => {
    const id = request.params.id;
    const post = await this.postRepository.findOne(id);
    if (post) {
      response.send(post);
    } else {
      next(new PostNotFoundException(id));
    }
  }
 
  private modifyPost = async (request: express.Request, response: express.Response, next: express.NextFunction) => {
    const id = request.params.id;
    const postData: Post = request.body;
    await this.postRepository.update(id, postData);
    const updatedPost = await this.postRepository.findOne(id);
    if (updatedPost) {
      response.send(updatedPost);
    } else {
      next(new PostNotFoundException(id));
    }
  }


  private deletePost = async (request: express.Request, response: express.Response, next: express.NextFunction) => {
    const id = request.params.id;
    const deleteResponse = await this.postRepository.delete(id);
    if (deleteResponse.raw[1]) {
      response.sendStatus(200);
    } else {
      next(new PostNotFoundException(id));
    }
  }
}
 
export default PostController;

关于使用pgadmin的提普斯

之前使用docker-compose安装了pqsl和pgamin，
pgadmin 通过侧边栏的servers>Create>Server来连接 pqsl数据库，注意在其中的Connection 选项卡的host项
因为使用了docker，所以pgadmin 连接pqsl 的host不是 localhost，而是 postgres (docker-compose 的时候设定的service名字)
而端口就是5432,因为两个应用实在docker内部network直接沟通，所以端口设置不是映射到外部的5460