介绍

Nest：

Nest 是一个用于构建高效，可扩展的 Node.js 服务器端应用程序的框架。它使用渐进式 JavaScript，内置并完全支持 TypeScript（但仍然允许开发人员使用纯 JavaScript 编写代码）并结合了 OOP（面向对象编程），FP（函数式编程）和 FRP（函数式响应编程）的元素。

在底层，Nest使用强大的 HTTP Server 框架，如 Express（默认）和 Fastify。Nest 在这些框架之上提供了一定程度的抽象，同时也将其 API 直接暴露给开发人员。这样可以轻松使用每个平台的无数第三方模块。

Drizzle ORM：

Drizzle ORM 是一个带有磁头的无头 TypeScript ORM。🐲

Neon database：

Neon 是一个无服务器 Postgres 平台，旨在帮助您更快地构建可靠且可扩展的应用程序。我们将计算和存储分开，以提供现代开发人员功能，例如自动扩展、分支、时间点还原等。

从头开始

创建项目

• 推荐使用CLI安装

1$ npm i -g @nestjs/cli
2$ nest new project-name

• 下载drizzle-orm,pg相关依赖

1pnpm i drizzle-orm pg
2pnpm i -D drizzle-kit @types/pg

查看依赖下载是否有警告，我在创建项目的时，有警告 ：

WARN deprecated eslint@8.57.1: This version is no longer supported. Please see https://eslint.org/version-support for other options.

手动到pakege.json将eslint改为 "eslint": "^9.0.0"，再重新下载依赖即可

在Neon数据库中创建第一个表

• 前往 Neon 注册账号，跟随指引创建一个数据库后得到 Connection string：

• 在刚刚创建的项目根目录中创建 .env 文件

1// 将Connection string填入创建的变量中
2DATABASE_URL='postgresql:'

• 为drizzle创建config文件

1// 项目根目录 drizzle.config.ts
2
3import 'dotenv/config';
4import { defineConfig } from 'drizzle-kit';
5
6export default defineConfig({
7  schema: './src/drizzle/schema.ts',
8  dialect: 'postgresql',
9  dbCredentials: {
10    url: process.env.DATABASE_URL,
11  },
12});
13

• 下载依赖 pnpm i @nestjs/config 以支持在项目中读取.env中的变量
• 使用cli创建名为drizzle的模块： nest g mo drizzle

• 在创建的drizzle模块下创建文件 schema.ts

1import { pgTable, serial, text } from "drizzle-orm/pg-core";
2
3export const users=pgTable('users',{
4    id:serial('id').primaryKey(),
5    name:text('name').notNull(),
6    email:text('email').notNull().unique(),
7    password:text('password').notNull()
8})

• 修改drizzle模块下的 drizzle.module.ts

1import { Module } from '@nestjs/common';
2import { ConfigService } from '@nestjs/config';
3import { Pool } from 'pg';
4import { drizzle, NodePgDatabase } from 'drizzle-orm/node-postgres';
5import * as schema from './schema';
6
7export const DRIZZLE = Symbol('drizzle-connection');
8@Module({
9  providers: [
10    {
11      provide: DRIZZLE,
12      inject: [ConfigService],
13      useFactory: async (configService: ConfigService) => {
14        const databaseUrl = configService.get<string>('DATABASE_URL');
15        const pool = new Pool({
16          connectionString: databaseUrl,
17          ssl: true,
18        });
19        return drizzle(pool, { schema }) as NodePgDatabase<typeof schema>;
20      },
21    },
22  ],
23  exports: [DRIZZLE],
24})
25export class DrizzleModule {}
26

• 根据 schema.ts 初始化, 使用 npx drizzle-kit generate

• 将初始化的数据同步到Neon: npx drizzle-kit migrate
• 在Neon线上查看User表是否成功创建

创建多个表，表关系

• 一对一关系（user-profileInfo）

1import { integer, jsonb, pgTable, serial, text } from 'drizzle-orm/pg-core';
2import { users } from './users.schema';
3import { relations } from 'drizzle-orm';
4
5export const profileInfo = pgTable('profileInfo', {
6  id: serial('id').primaryKey(),
7  metadata: jsonb('metadata'),
8  userId: integer('userId')
9    .references(() => users.id)
10    .notNull(),
11});
12
13export const profileInfoRelations = relations(profileInfo, ({ one }) => ({
14  user: one(users, {
15    fields: [profileInfo.userId],
16    references: [users.id],
17  }),
18}));

1import { relations } from 'drizzle-orm';
2import { index, pgTable, serial, text } from 'drizzle-orm/pg-core';
3import { posts } from './posts.schema';
4import { comments } from './comments.schema';
5import { profileInfo } from './profileInfo.schema';
6import { usersToGroups } from './groups.schema';
7
8export const users = pgTable(
9  'users',
10  {
11    id: serial('id').primaryKey(),
12    name: text('name').notNull(),
13    email: text('email').notNull().unique(),
14    password: text('password').notNull(),
15  },
16  (t) => ({
17    idIndex: index('idIndex').on(t.id),
18  }),
19);
20
21export const usersRelations = relations(users, ({ one, many }) => ({
22  commenst: many(comments),
23  posts: many(posts),
24  profile: one(profileInfo),
25  usersToGroups: many(usersToGroups),
26}));
27

• 修改文件位置，使其更清晰

• 创建schema文件夹，将文件放入

1// src/drizzle/schema/schema.ts
2export * from "./users.schema";
3export * from "./profileInfo.schema";

1// drizzle.config.ts
2export default defineConfig({
3  schema: './src/drizzle/schema/**.schema.ts',
4  dialect: 'postgresql',
5  dbCredentials: {
6    url: process.env.DATABASE_URL,
7  },
8});
9

• 创建posts.schema.ts , comments.schema.ts

1import { integer, pgTable, serial, text } from 'drizzle-orm/pg-core';
2import { users } from './users.schema';
3import { relations } from 'drizzle-orm';
4import { comments } from './comments.schema';
5
6export const posts = pgTable('posts', {
7  id: serial('id').primaryKey(),
8  title: text('title').notNull(),
9  content: text('content').notNull(),
10  authorId: integer('authorId')
11    .references(() => users.id)
12    .notNull(),
13});
14
15export const postRelations = relations(posts, ({ one, many }) => ({
16  author: one(users, {
17    fields: [posts.authorId],
18    references: [users.id],
19  }),
20  comments: many(comments),
21}));

1import { integer, pgTable, serial, text } from 'drizzle-orm/pg-core';
2import { users } from './users.schema';
3import { posts } from './posts.schema';
4import { relations } from 'drizzle-orm';
5
6export const comments = pgTable('comments', {
7  id: serial('id').primaryKey(),
8  text: text('text').notNull(),
9  authorId: integer('authorId')
10    .references(() => users.id)
11    .notNull(),
12  postId: integer('postId')
13    .references(() => posts.id)
14    .notNull(),
15});
16
17export const commentsRelations = relations(comments, ({ one }) => ({
18  author: one(users, {
19    fields: [comments.authorId],
20    references: [users.id],
21  }),
22  post: one(posts, {
23    fields: [comments.postId],
24    references: [posts.id],
25  }),
26}));

• 创建联合表 groups.schema.ts

1import {
2  index,
3  integer,
4  pgTable,
5  primaryKey,
6  serial,
7  text,
8} from 'drizzle-orm/pg-core';
9import { users } from './users.schema';
10import { relations } from 'drizzle-orm';
11
12export const groups = pgTable('groups', {
13  id: serial('id').primaryKey(),
14  name: text('name').notNull(),
15});
16
17// This The Joint Table
18export const usersToGroups = pgTable(
19  'usersToGroups',
20  {
21    userId: integer('userId').references(() => users.id),
22    groupId: integer('groupId').references(() => groups.id),
23  },
24  (table) => ({
25    pk: primaryKey({ columns: [table.groupId, table.userId] }),
26    userIdIndex: index('userIdIndex').on(table.userId),
27  }),
28);
29
30export const groupRelations = relations(groups, ({ many }) => ({
31  userToGroups: many(usersToGroups),
32}));
33
34export const usersToGroupsRelations = relations(usersToGroups, ({ one }) => ({
35  user: one(users, {
36    fields: [usersToGroups.userId],
37    references: [users.id],
38  }),
39  group: one(groups, {
40    fields: [usersToGroups.groupId],
41    references: [groups.id],
42  }),
43}));

使用faker创建模拟数据

• 下载依赖 pnpm i @faker-js/faker
• 创建seed.ts

1// src/drizzle/seed.ts
2
3import { drizzle, NodePgDatabase } from 'drizzle-orm/node-postgres';
4import { Pool } from 'pg';
5import * as schema from './schema/schema';
6import 'dotenv/config';
7import { faker } from '@faker-js/faker';
8
9const pool = new Pool({
10  connectionString: process.env.DATABASE_URL,
11  ssl: true,
12});
13const db = drizzle(pool, { schema }) as NodePgDatabase<typeof schema>;
14
15async function main() {
16  const userIds = await Promise.all(
17    Array(50)
18      .fill('')
19      .map(async () => {
20        const user = await db
21          .insert(schema.users)
22          .values({
23            email: faker.internet.email(),
24            name: faker.person.firstName() + ' ' + faker.person.lastName(),
25            password: '',
26          })
27          .returning();
28        return user[0].id;
29      }),
30  );
31
32  const postIds = await Promise.all(
33    Array(50)
34      .fill('')
35      .map(async () => {
36        const post = await db
37          .insert(schema.posts)
38          .values({
39            content: faker.lorem.paragraph(),
40            title: faker.lorem.sentence(),
41            authorId: faker.helpers.arrayElement(userIds),
42          })
43          .returning();
44        return post[0].id;
45      }),
46  );
47
48  await Promise.all(
49    Array(50)
50      .fill('')
51      .map(async () => {
52        const comment = await db
53          .insert(schema.comments)
54          .values({
55            text: faker.lorem.sentence(),
56            authorId: faker.helpers.arrayElement(userIds),
57            postId: faker.helpers.arrayElement(postIds),
58          })
59          .returning();
60        return comment[0].id;
61      }),
62  );
63
64  const insertedGroups = await db
65    .insert(schema.groups)
66    .values([
67      {
68        name: 'JS',
69      },
70      {
71        name: 'TS',
72      },
73    ])
74    .returning();
75
76  const groupIds = insertedGroups.map((group) => group.id);
77
78  await Promise.all(
79    userIds.map(async (userId) => {
80      return await db
81        .insert(schema.usersToGroups)
82        .values({
83          userId,
84          groupId: faker.helpers.arrayElement(groupIds),
85        })
86        .returning();
87    }),
88  );
89}
90
91main()
92  .then()
93  .catch((err) => {
94    console.error(err);
95    process.exit(0);
96  });

• 在pakege.json中添加命令 "db:seed": "ts-node ./src/drizzle/seed.ts"

1npx drizzle-kit generate 
2npx drizzle-kit migrate 
3pnpm run db:seed

CRUD操作

• 使用命令创建资源 nest g res post --no-spec

1import {
2  Controller,
3  Get,
4  Post,
5  Body,
6  Patch,
7  Param,
8  Delete,
9} from '@nestjs/common';
10import { PostService } from './post.service';
11import { CreatePostDto } from './dto/create-post.dto';
12import { UpdatePostDto } from './dto/update-post.dto';
13
14@Controller('post')
15export class PostController {
16  constructor(private readonly postService: PostService) {}
17
18  @Post()
19  create(@Body() createPostDto: CreatePostDto) {
20    return this.postService.create(createPostDto);
21  }
22
23  @Get()
24  findAll() {
25    return this.postService.findAll();
26  }
27
28  @Get(':id')
29  findOne(@Param('id') id: string) {
30    return this.postService.findOne(+id);
31  }
32
33  @Patch(':id')
34  update(@Param('id') id: string, @Body() updatePostDto: UpdatePostDto) {
35    return this.postService.update(+id, updatePostDto);
36  }
37
38  @Delete(':id')
39  remove(@Param('id') id: string) {
40    return this.postService.remove(+id);
41  }
42}

1import { Inject, Injectable } from '@nestjs/common';
2import { CreatePostDto } from './dto/create-post.dto';
3import { UpdatePostDto } from './dto/update-post.dto';
4import { DRIZZLE } from 'src/drizzle/drizzle.module';
5import { DrizzleDB } from 'src/drizzle/types/drizzle';
6import { posts } from 'src/drizzle/schema/posts.schema';
7import { eq } from 'drizzle-orm';
8
9@Injectable()
10export class PostService {
11  constructor(@Inject(DRIZZLE) private db: DrizzleDB) {}
12  create(createPostDto: CreatePostDto) {
13    return 'This action adds a new post';
14  }
15
16  async findAll() {
17    // return await this.db.select().from(posts);
18    return await this.db.query.posts.findMany({
19      where: (posts, { eq }) => eq(posts.id, 20),
20      with: {
21        author: {
22          with: {
23            usersToGroups: {
24              with: {
25                group: true,
26              },
27            },
28          },
29        },
30      },
31    });
32  }
33
34  findOne(id: number) {
35    return `This action returns a #${id} post`;
36  }
37
38  async update(id: number, updatePostDto: UpdatePostDto) {
39    return await this.db
40      .update(posts)
41      .set({
42        title: 'AAAAAAAAA',
43      })
44      .where(eq(posts.id, id));
45  }
46
47  async remove(id: number) {
48    return await this.db.delete(posts).where(eq(posts.id, id));
49  }
50}