一些原则

• 过早优化乃万恶之源
  • 如果没办法量化性能，就不要尝试优化性能
• 开发效率>可读性＞运行效率
• 可用性＞易用性＞美观

• 永远不要删除数据

  • 尽量软删除，删除前确认

    需求分析

• 博客系统

  • 用户可以登录，注册，注销。但是不可以重置密码
  • 重置密码联系管理员
  • 用户可以对博客进行增删改查
  • 用户可以对博客进行评论，但不能修改评论
  • 用户不可以编辑用户名密码姓名头像
• 可用性要求
  • 手机上也能完成操作

• 其他要求

  • 对搜索引擎优化

    思路

• 需求

  • 简单的增删改查
  • 主要表有users/posts/comments
• 主要数据
  • user(id/username/password_digest)
  • posts(id/user_id/title/content)
  • comments(id/user_id/post_id/content)
• 其他
  • 手机适配：一开始就设计两套界面PC+mobile
  • SEO:多用SSG或SSR，少用BSR

代码

删除之前的容器

$ docker ps

$ docker kill xxx

$ docker rm xxx

创建新容器

docker run -v "blog-data":/var/lib/postgresql/data -p 5432:5432 -e POSTGRES_USER=blog -e POSTGRES_HOST_AUTH_METHOD=trust -d postgres:12.2

$ docker exec -it xxx bash

# psql -U blog

# /l

# drop database blog_development;

创建新数据库

$ CREATE DATABASE xxx ENCODING 'UTF8' LC_COLLATE 'en_US.utf8' LC_CTYPE 'en_US.utf8';

清空src内的entity和migration,清空dist文件夹

seed.tsx

import "reflect-metadata";
import { createConnection } from "typeorm";
createConnection().then(async connection => {
    connection.close()
}).catch(error => console.log(error));

按两次ctrl+D退出bash
$ yarn dev

创建表

package.json写入命令
“migration:create”: “typeorm migration:create”,
或者简写成
“m:create”: “typeorm migration:create”,
$ yarn m:create -n CreateUsers

1627888…..

import { MigrationInterface, QueryRunner, Table } from "typeorm";
export class CreateUsers1627888334255 implements MigrationInterface {
    public async up(queryRunner: QueryRunner): Promise<void> {
        return await queryRunner.createTable(new Table({
            name: 'users',
            columns: [
                {
                    name: 'id',
                    isGenerated: true,
                    type: 'int',
                    generationStrategy: 'increment',
                    isPrimary: true
                },
                {
                    name: 'username',
                    type: 'varchar'
                },
                {
                    name: 'passwordDigest',
                    type: 'varchar'
                }
            ]
        }))
    }
    public async down(queryRunner: QueryRunner): Promise<void> {
        return await queryRunner.dropTable('users')
    }
}

$ yarn m:create -n CreatePosts

import { MigrationInterface, QueryRunner,Table } from "typeorm";
export class CreatePosts1627889702787 implements MigrationInterface {
    public async up(queryRunner: QueryRunner): Promise<void> {
        return await queryRunner.createTable(new Table({
            name: 'posts',
            columns: [
                {
                    name: 'id',
                    isGenerated: true,
                    type: 'int',
                    generationStrategy: 'increment',
                    isPrimary: true
                },
                {
                    name: 'title',
                    type: 'varchar'
                },
                {
                    name: 'content',
                    type: 'text'
                },
                {
                    name: 'authorId',
                    type: 'int'
                }
            ]
        }))
    }
    public async down(queryRunner: QueryRunner): Promise<void> {
        return await queryRunner.dropTable('posts')
    }
}

$ yarn m:create -n CreateComments

import { MigrationInterface, QueryRunner, Table } from "typeorm";
export class CreateComments1627890376988 implements MigrationInterface {
    public async up(queryRunner: QueryRunner): Promise<void> {
        return await queryRunner.createTable(new Table({
            name: 'comments',
            columns: [
                {
                    name: 'id',
                    isGenerated: true,
                    type: 'int',
                    generationStrategy: 'increment',
                    isPrimary: true
                },
                {
                    name: 'userId',
                    type: 'int'
                },
                {
                    name: 'postId',
                    type: 'int'
                },
                {
                    name: 'content',
                    type: 'text'
                }
            ]
        }))
    }
    public async down(queryRunner: QueryRunner): Promise<void> {
        return await queryRunner.dropTable('comments')
    }
}

$ yarn m:create -n AddCreatedAtAndUpdatedAt

import { MigrationInterface, QueryRunner, TableColumn } from "typeorm";
export class AddCreatedAtAndUpdatedAt1627891112814 implements MigrationInterface {
    public async up(queryRunner: QueryRunner): Promise<void> {
        await queryRunner.addColumns('users', [
            new TableColumn({
                name: 'createdAt',
                type: 'time',
                isNullable: false,
                default: 'now()'
            }),
            new TableColumn({
                name: 'updatedAt',
                type: 'time',
                isNullable: false,
                default: 'now()'
            })
        ])
        await queryRunner.addColumns('posts', [
            new TableColumn({
                name: 'createdAt',
                type: 'time',
                isNullable: false,
                default: 'now()'
            }),
            new TableColumn({
                name: 'updatedAt',
                type: 'time',
                isNullable: false,
                default: 'now()'
            })
        ])
        return await queryRunner.addColumns('comments', [
            new TableColumn({
                name: 'createdAt',
                type: 'time',
                isNullable: false,
                default: 'now()'
            }),
            new TableColumn({
                name: 'updatedAt',
                type: 'time',
                isNullable: false,
                default: 'now()'
            })
        ])
    }
    public async down(queryRunner: QueryRunner): Promise<void> {
        await queryRunner.dropColumn('users', 'createdAt')
        await queryRunner.dropColumn('users', 'updatedAt')
        await queryRunner.dropColumn('posts', 'createdAt')
        await queryRunner.dropColumn('posts', 'updatedAt')
        await queryRunner.dropColumn('comments', 'createdAt')
        return await queryRunner.dropColumn('comments', 'updatedAt')
    }
}

删除dits 然后$ yarn dev 会重新创建dist文件夹
$ yarn m:run

创建关联

这三张表之间的内容有这样的关系

• users表
  • 一个user有很多posts
  • 一个user有很多comments
• posts表
  • 一个post属于一个user
  • 一个post有很多comments
• comments
  • 一个comment属于一个user
  • 一个commnet属于一个post

创建entity中的实体
package.json
“e:create”: “typeorm entity:create”

$ yarn e:create -n User
$ yarn e:create -n Post
$ yarn e:create -n Comment

/src/entity/User.ts

import { Column, CreateDateColumn, Entity, OneToMany, PrimaryGeneratedColumn, UpdateDateColumn } from 'typeorm';
import { Comment } from './Comment';
import { Post } from './Post'
@Entity('users')
export class User {
    @PrimaryGeneratedColumn('increment')
    id: string;
    @Column('varchar')
    username: string;
    @Column('varchar')
    passwordDigest: string;
    @CreateDateColumn()
    createdAt: Date;
    @UpdateDateColumn()
    updatedAt: Date;
    @OneToMany(type => Post, post => post.author)
    posts: Post[]
    @OneToMany(type => Comment, comment => comment.user)
    comments: Comment[]
}

/src/entity/Post.ts

import { Column, CreateDateColumn, Entity, ManyToOne, OneToMany, PrimaryGeneratedColumn, UpdateDateColumn } from 'typeorm';
import { User } from './User';
import { Comment } from './Comment';
@Entity('posts')
export class Post {
    @PrimaryGeneratedColumn('increment')
    id: string;
    @Column('varchar')
    title: string;
    @Column('text')
    content: string;
    @CreateDateColumn()
    createdAt: Date;
    @UpdateDateColumn()
    updatedAt: Date;
    @ManyToOne(type => User, user => user.posts)
    author: User;
    @OneToMany(type => Comment, comment => comment.post)
    comments: Comment[]
}

/src/entity/Comment.ts

import { Column, CreateDateColumn, Entity, ManyToOne, PrimaryGeneratedColumn, UpdateDateColumn } from 'typeorm';
import { Post } from './Post';
import { User } from './User';
@Entity('comments')
export class Comment {
    @PrimaryGeneratedColumn('increment')
    id: string;
    @Column('text')
    content: string;
    @CreateDateColumn()
    createdAt: Date;
    @UpdateDateColumn()
    updatedAt: Date;
    @ManyToOne(type => User, user => user.comments)
    user: User;
    @ManyToOne(type => Post, post => post.comments)
    post: Post;
}

填充数据

/src/seeds.ts

import "reflect-metadata";
import { createConnection } from "typeorm";
import { Comment } from "./entity/Comment";
import { Post } from "./entity/Post";
import { User } from './entity/User';
createConnection().then(async connection => {
    const { manager } = connection;
    //创建user
    const u1 = new User()
    u1.username = 'gouson'
    u1.passwordDigest = 'xxx'
    await manager.save(u1)
    //创建post
    const p1 = new Post()
    p1.title = "Post 1"
    p1.content = "yyyyyyyyyyyyyyyyyy"
    p1.author = u1
    await manager.save(p1)
    //创建comment
    const c1 = new Comment()
    c1.content = 'zzzzzz'
    c1.user = u1
    c1.post = p1
    await manager.save(c1)
    console.log(u1.id)
    connection.close()
}).catch(error => console.log(error));

$ yarn dev
$ node dist/seed.js

创建成功