第 4 章：高级

在本章中，我们将首先实现 /user 端点的所有方法，包括通过 @fastify/jwt 包提供的基本 JWT 身份验证，然后继续其余的 /article 端点。我们将涉及一些更高级的概念，如自定义存储库、虚拟实体、QueryBuilder、刷新事件等。

¥In this chapter, we will first implement all the methods of /user endpoint, including a basic JWT authentication provided via @fastify/jwt package, and proceed with the rest of the /article endpoints. We will touch on some of the more advanced concepts like custom repositories, virtual entities, QueryBuilder, flush events, and more.

改进路由注册

¥Improving route registration

在我们开始并实现其余的 User 和 Article 端点处理程序之前，让我们改进注册路由的方式。让我们在 src/modules/article 中创建一个 routes.ts 文件，并从中导出一个工厂函数：

¥Before we jump in and implement the rest of the User and Article endpoint handlers, let's improve on how we register the routes. Let's create a routes.ts file in src/modules/article, and export a factory function from it:

modules/article/routes.ts

import { FastifyInstance } from 'fastify';
import { initORM } from '../../db.js';

export async function registerArticleRoutes(app: FastifyInstance) {
  const db = await initORM();

  app.get('/', async request => {
    const { limit, offset } = request.query as { limit?: number; offset?: number };
    const [items, total] = await db.article.findAndCount({}, {
      limit, offset,
    });

    return { items, total };
  });
}

让我们为 User 模块创建一个占位符，因此在 src/modules/user 文件夹中：

¥And let's create a placeholder for the User module too, so in src/modules/user folder:

modules/user/routes.ts

import { FastifyInstance } from 'fastify';
import { initORM } from '../../db.js';

export async function registerUserRoutes(app: FastifyInstance) {
  // no routes yet
}

现在通过 app.register() 方法在你的 bootstrap 函数中使用它们：

¥Now use them in your bootstrap function via app.register() method:

// register routes here
-app.get('/article', async request => {
-  ...
-});
+app.register(registerArticleRoutes, { prefix: 'article' });
+app.register(registerUserRoutes, { prefix: 'user' });

注册路由

¥Sign-up route

是时候添加我们的第一个 User 端点，用于注册新用户了。它将是一个 POST 端点，它将接受具有 email、fullName 和 password 属性的对象负载：

¥Time to add our first User endpoint, for registering new users. It will be a POST endpoint, which will accept an object payload with email, fullName and password properties:

modules/user/routes.ts

export async function registerUserRoutes(app: FastifyInstance) {
  const db = await initORM();

  // register new user
  app.post('/sign-up', async request => {
    const body = request.body as EntityData<User>;

    if (!body.email || !body.fullName || !body.password) {
      throw new Error('One of required fields is missing: email, fullName, password');
    }

    if ((await db.user.count({ email: body.email })) > 0) {
      throw new Error('This email is already registered, maybe you want to sign in?');
    }

    const user = new User(body.fullName, body.email, body.password);
    user.bio = body.bio ?? '';
    await db.em.persist(user).flush();

    // after flush, we have the `user.id` set
    console.log(`User ${user.id} created`);

    return user;
  });
}

自定义存储库

¥Custom repositories

对现有用户的检查看起来有点太复杂了，让我们创建一个自定义存储库方法，以使事情更具可读性和可维护性。

¥The check for existing users looks a bit too complex, let's create a custom repository method instead to make things more readable and maintainable.

modules/user/user.repository.ts

import { EntityRepository } from '@mikro-orm/sqlite';
import { User } from './user.entity.js';

export class UserRepository extends EntityRepository<User> {

  async exists(email: string) {
    const count = await this.count({ email });
    return count > 0;
  }

}

并在 @Entity() 装饰器选项中使用此存储库。要使所有内容正确输入，还请指定 EntityRepositoryType 符号属性 - 这样，em.getRepository() 方法将自动在类型级别检测我们的自定义存储库：

¥And use this repository in the @Entity() decorator options. To have everything correctly typed, specify also the EntityRepositoryType symbol property - this way the em.getRepository() method will detect our custom repository on type level automatically:

user.entity.ts

import { UserRepository } from './user.repository.js';

@Entity({ repository: () => UserRepository })
export class User extends BaseEntity<'bio'> {

  // for automatic inference via `em.getRepository(User)`
  [EntityRepositoryType]?: UserRepository;

  // rest of the entity definition

}

不要忘记调整我们的 Services 类型：

¥And don't forget to adjust our Services type:

export interface Services {
  orm: MikroORM;
  em: EntityManager;
- user: EntityRepository<User>;
+ user: UserRepository;
  article: EntityRepository<Article>;
  tag: EntityRepository<Tag>;
}

现在你可以在 sign-up 端点中使用它：

¥Now you can use it in the sign-up endpoint:

-if ((await db.user.count({ email: body.email })) > 0) {
+if (await db.user.exists(body.email)) {
  throw new Error('This email is already registered, maybe you want to sign in?');
}

身份验证

¥Authentication

是时候添加第二个 User 路由了，这次用于登录。让我们再次修改我们的 routes.ts。让我们再次为 login 使用自定义存储库方法，我们将在一秒钟内实现它：

¥Time to add the second User route, this time for logging in. Let's modify our routes.ts again. Let's again use a custom repository method for the login, we will implement that in a second:

modules/user/routes.ts

export async function registerUserRoutes(app: FastifyInstance) {
  const db = await initORM();

  // register new user
  app.post('/sign-up', async request => {
    // ...
  });

  // login existing user
  app.post('/sign-in', async request => {
    const { email, password } = request.body as { email: string; password: string };
    const user = await db.user.login(email, password);

    return user;
  });
}

现在 login 方法将尝试根据密码加载 User 实体，并通过我们的 User.verifyPassword() 实例方法进行比较。如果我们找不到 email 和 password 的这种组合，我们会抛出一个错误。

¥And now the login method, it will try to load the User entity based on the password, and compare it via our User.verifyPassword() instance method. If we don't find such combination of the email and password, we throw an error.

modules/user/user.repository.ts

export class UserRepository extends EntityRepository<User> {

  // ...

  async login(email: string, password: string) {
    // we use a more generic error so we don't leak such email is registered
    const err = new Error('Invalid combination of email and password');
    const user = await this.findOneOrFail({ email }, {
      populate: ['password'], // password is a lazy property, we need to populate it
      failHandler: () => err,
    });

    if (await user.verifyPassword(password)) {
      return user;
    }

    throw err;
  }

}

测试 User 端点

¥Testing the User endpoints

我们现在有两个新的端点，我们应该测试它们是否按预期工作。为 User 端点添加一个新的测试用例：

¥We now have two new endpoints, we should test they work as expected. Add a new test case for the User endpoints:

tests/user.test.ts

import { FastifyInstance } from 'fastify';
import { afterAll, beforeAll, expect, test } from 'vitest';
import { initTestApp } from './utils.js';

let app: FastifyInstance;

beforeAll(async () => {
  // we use different ports to allow parallel testing
  app = await initTestApp(30002);
});

afterAll(async () => {
  // we close only the fastify app - it will close the database connection via onClose hook automatically
  await app.close();
});

test('login', async () => {
  const res1 = await app.inject({
    method: 'post',
    url: '/user/sign-in',
    payload: {
      email: 'foo@bar.com',
      password: 'password123',
    },
  });

  expect(res1.statusCode).toBe(200);
  expect(res1.json()).toMatchObject({
    fullName: 'Foo Bar',
  });

  const res2 = await app.inject({
    method: 'post',
    url: '/user/sign-in',
    payload: {
      email: 'foo@bar.com',
      password: 'password456',
    },
  });

  expect(res2.statusCode).toBe(401);
  expect(res2.json()).toMatchObject({ error: 'Invalid combination of email and password' });
});

当你使用 npm test 运行它时，你应该看到一个失败的断言：

¥When you run it with npm test, you should see a failed assertion:

 FAIL  test/user.test.ts > login
AssertionError: expected 500 to be 401 // Object.is equality

- Expected
+ Received

- 401
+ 500

那是因为我们没有在任何地方处理这个问题，我们只是抛出一个错误 - 现在让我们通过将身份验证集成到我们的应用中来处理这个问题。

¥That's because we don't handle this anywhere, we just throw an error - let's deal with that now, by integrating the authentication into our application.

JSON Web 令牌

¥JSON Web Tokens

所以计划是在我们的 API 中添加一个身份验证层。我们需要生成一个保存身份的身份验证令牌 - 让我们使用所谓的 JSON Web Token (JWT)，这是一种行业标准。我们可以利用 @fastify/jwt 插件轻松地对它们进行编码/解码。

¥So the plan is to add an authentication layer to our API. We will need generate an authentication token that will hold the identity - let's use so-called JSON Web Token (JWT), an industry standard. We can leverage the @fastify/jwt plugin for encoding/decoding them with ease.

npm

npm install @fastify/jwt

Yarn

yarn add @fastify/jwt

pnpm

pnpm add @fastify/jwt

现在在你的 bootstrap() 函数中注册此插件：

¥Now register this plugin in your bootstrap() function:

app.ts

import fastifyJWT from '@fastify/jwt';

// ...

// register JWT plugin
app.register(fastifyJWT, {
  secret: process.env.JWT_SECRET ?? '12345678', // fallback for testing
});

使用 JWT 插件，我们的 request 对象将具有一个 user 属性，我们可以使用它来存储有关当前记录的 User 的数据，以及 app 对象上的两个方便的方法：

¥With the JWT plugin, our request object will have a user property we can use to store data about the currently logged User, as well as two handy methods on the app object:

• app.jwt.sign() 从有效负载创建令牌

  ¥app.jwt.sign() to create the token from a payload

• request.jwtVerify() 验证并将令牌解码回有效负载

  ¥request.jwtVerify() to verify and decode the token back to the payload

我们将使用令牌有效负载来存储 user.id。让我们为它的 User 实体添加一个新属性：

¥We will use the token payload to store the user.id. Let's add a new property to our User entity for it:

modules/user/user.entity.ts

@Property({ persist: false })
token?: string;

我们在这里使用了 persist: false，这意味着该属性是虚拟的，它不代表数据库列（但可以映射和序列化）。

¥We used persist: false here, that means the property is virtual, it does not represent a database column (but can be mapped and serialized).

在我们继续之前，让我们再添加一个实用程序 - 自定义 AuthError 类，我们可以使用它来检测身份验证问题（例如密码错误）。

¥Before we continue, let's add one more utility - a custom AuthError class, which we can use to detect authentication issues (e.g. wrong password).

modules/common/utils.ts

export class AuthError extends Error {}

并在 UserRepository 中使用它：

¥And use it in the UserRepository:

modules/user/user.repository.ts

import { AuthError } from '../common/utils.js';

export class UserRepository extends EntityRepository<User> {

  // ...

  async login(email: string, password: string) {
    // we use a more generic error so we don't leak such email is registered
    const err = new AuthError('Invalid combination of email and password');
    const user = await this.findOneOrFail({ email }, {
      populate: ['password'], // password is a lazy property, we need to populate it
      failHandler: () => err,
    });

    if (await user.verifyPassword(password)) {
      return user;
    }

    throw err;
  }

}

现在在 sign-up 和 sign-in 处理程序中生成令牌：

¥Now generate the token in the sign-up and sign-in handlers:

modules/user/routes.ts

// register new user
app.post('/sign-up', async request => {
  // ...

  const user = new User(body.fullName, body.email, body.password);
  user.bio = body.bio ?? '';
  await db.em.persist(user).flush();

  user.token = app.jwt.sign({ id: user.id })

  return user;
});

// login existing user
app.post('/sign-in', async request => {
  const { email, password } = request.body as { email: string; password: string };
  const user = await db.user.login(email, password);
  user.token = app.jwt.sign({ id: user.id })

  return user;
});

最后，我们可以将中间件添加到 bootstrap() 函数中，以根据令牌对用户进行身份验证：

¥And finally, we can add the middleware to authenticate users based on the token to the bootstrap() function:

app.ts

// register auth hook after the ORM one to use the context
app.addHook('onRequest', async request => {
  try {
    const ret = await request.jwtVerify<{ id: number }>();
    request.user = await db.user.findOneOrFail(ret.id);
  } catch (e) {
    app.log.error(e);
    // ignore token errors, we validate the request.user exists only where needed
  }
});

// register global error handler to process 404 errors from `findOneOrFail` calls
app.setErrorHandler((error, request, reply) => {
  if (error instanceof AuthError) {
    return reply.status(401).send({ error: error.message });
  }

  // we also handle not found errors automatically
  // `NotFoundError` is an error thrown by the ORM via `em.findOneOrFail()` method
  if (error instanceof NotFoundError) {
    return reply.status(404).send({ error: error.message });
  }

  app.log.error(error);
  reply.status(500).send({ error: error.message });
});

就是这样，我们的测试现在应该再次通过，并且有一个基本的身份验证机制！当服务器在请求标头中检测到用户令牌时，它将自动加载相应的用户并将其存储到 request.user 属性中。

¥And that's it, our tests should be passing now again, with a basic authentication mechanism in place! When the server detects a user token in the request headers, it will automatically load the corresponding user and store it into the request.user property.

让我们实现最后两个端点，以获取当前用户配置文件并对其进行修改。首先，创建一个新的实用方法：getUserFromToken。

¥Let's implement the last two endpoints for getting the current user profile and modifying it. First, create one new utility method: getUserFromToken.

modules/common/utils.ts

import { FastifyRequest } from 'fastify';
import { User } from '../user/user.entity.js';

export function getUserFromToken(req: FastifyRequest): User {
  if (!req.user) {
    throw new Error('Please provide your token via Authorization header');
  }

  return req.user as User;
}

现在实现处理程序：

¥And now implement the handlers:

modules/user/routes.ts

app.get('/profile', async request => {
  const user = getUserFromToken(request);
  return user;
});

app.patch('/profile', async request => {
  const user = getUserFromToken(request);
  wrap(user).assign(request.body as User);
  await db.em.flush();
  return user;
});

练习

现在尝试为这些端点实现测试！

¥Try implementing the tests for those endpoints now!

可嵌入项

¥Embeddables

在我们回到文章端点之前，让我们稍微改进一下我们的用户实体。假设我们想在 User 实体上为 twitter、facebook 或 linkedin 设置可选的社交句柄。我们可以为此使用 可嵌入项，该功能允许将多列映射到一个对象。

¥Before we move on back to the article endpoint, let's improve our user entity a bit. Say we want to have optional social handles for twitter, facebook or linkedin on the User entity. We can use Embeddables for this, a feature which allows mapping multiple columns to an object.

user.entity.ts

@Embeddable()
export class Social {

  @Property()
  twitter?: string;

  @Property()
  facebook?: string;

  @Property()
  linkedin?: string;

}


@Entity({ repository: () => UserRepository })
export class User extends BaseEntity<'bio'> {

  // ...

  @Embedded(() => Social)
  social?: Social;

}

尝试使用 CLI 检查这对数据库模式的影响：

¥Try using to CLI to check how this affects the database schema:

$ npx mikro-orm-esm schema:update --dump

alter table `user` add column `social_twitter` text null;
alter table `user` add column `social_facebook` text null;
alter table `user` add column `social_linkedin` text null;

但是，将社交句柄存储到 JSON 列中可能是一个更好的主意 - 我们也可以通过可嵌入轻松实现这一点：

¥But maybe it would be a better idea to store the social handles into a JSON column - we can easily achieve that with embeddables too:

@Embedded(() => Social, { object: true })
social?: Social;

再次测试：

¥And test it again:

$ npx mikro-orm-esm schema:update --dump

alter table `user` add column `social` json null;

是的，看起来不错，让我们为它创建一个迁移：

¥Yeah, that looks good, let's create a migration for it:

$ npx mikro-orm-esm migration:create

Migration20231105213316.ts successfully created

$ npx mikro-orm-esm migration:up

Processing 'Migration20231105213316'
Applied 'Migration20231105213316'
Successfully migrated up to the latest version

通过验证 Zod

¥Validation via Zod

用户模块中还有一件事，我们需要在 sign-up 端点中处理这个新的 User.social 属性。

¥One more thing in the user module, we need to process this new User.social property in our sign-up endpoint.

modules/user/routes.ts

const user = new User(body.fullName, body.email, body.password);
user.bio = body.bio ?? '';
user.social = body.social as Social;
await db.em.persist(user).flush();

代码有点乱，让我们改用 em.create() 来使其再次干净：

¥The code is getting a bit messy, let's use em.create() instead to make it clean again:

-const user = new User(body.fullName, body.email, body.password);
-user.bio = body.bio ?? '';
-user.social = body.social as Social;
+const user = db.user.create(request.body as RequiredEntityData<User>);
await db.em.persist(user).flush();

MikroORM 将自动执行一些基本验证，但通常明确验证用户输入是一种很好的做法。让我们使用 Zod，它还将有助于使 TypeScript 编译器在没有类型断言的情况下正常运行。

¥MikroORM will perform some basic validation automatically, but it is generally a good practice to validate the user input explicitly. Let's use Zod for it, it will also help with making the TypeScript compiler happy without the type assertion.

首先，安装 zod 包。

¥First, install the zod package.

npm

npm install zod

Yarn

yarn add zod

pnpm

pnpm add zod

然后你可以创建模式对象：

¥Then you can create the schema objects:

modules/user/routes.ts

const socialSchema = z.object({
  twitter: z.string().optional(),
  facebook: z.string().optional(),
  linkedin: z.string().optional(),
});

const userSchema = z.object({
  email: z.string(),
  fullName: z.string(),
  password: z.string(),
  bio: z.string().optional(),
  social: socialSchema.optional(),
});

app.post('/sign-up', async request => {
  const dto = userSchema.parse(request.body);

  if (await db.user.exists(dto.email)) {
    throw new Error('This email is already registered, maybe you want to sign in?');
  }

  // thanks to zod, our `dto` is fully typed and passes the `em.create()` checks
  const user = db.user.create(dto);
  await db.em.flush(); // no need for explicit `em.persist()` when we use `em.create()`

  // after flush, we have the `user.id` set
  user.token = app.jwt.sign({ id: user.id });

  return user;
});

信息

此示例仅显示了使用 Zod 进行的最基本的验证，它反映了 MikroORM 已经处理的内容 - 它将自动验证所需的属性及其类型。查看 属性验证 部分以了解更多详细信息。

¥This example only shows a very basic validation with Zod, which mirrors what MikroORM already handles - it will validate required properties and their types automatically. Check the Property Validation section for more details.

文章其余端点

¥Rest of the Article endpoints

让我们实现其余的文章端点。我们需要一个公共的用于文章详细信息，一个用于发表评论，一个用于更新文章，一个用于删除文章。最后两个步骤只允许创建给定文章的用户执行。

¥Let's implement the rest of the article endpoints. We will need a public one for the article detail, one for posting comments, one for updating the article and one for deleting it. The last two will be only allowed for the user who created given article.

利用你已有的信息，实现这些端点应该非常简单。detail 端点非常简单，它所做的就是使用 findOneOrFail() 方法根据其 slug 获取 Article。

¥With the information you already have, implementing those endpoints should be pretty straightforward. The detail endpoint is really simple, all it does is using the findOneOrFail() method to get the Article based on its slug.

警告

你应该在使用请求参数之前验证它们！它被故意省略，因为它超出了本指南的范围。

¥You should validate the request parameters before working with them! It's left out on purpose as it is outside of scope of this guide.

modules/article/routes.ts

app.get('/:slug', async request => {
  const { slug } = request.params as { slug: string };
  return db.article.findOneOrFail({ slug }, {
    populate: ['author', 'comments.author', 'text'],
  });
});

创建实体

¥Creating entities

然后我们定义创建评论的端点 - 这里我们使用 getUserFromToken 助手根据 token 访问当前用户，尝试找到文章（再次基于 slug 属性）并创建评论实体。由于我们在这里使用 em.create()，我们不必对新实体进行 em.persist()，因为这样会自动发生。

¥Then we define the endpoint for creating comments - here we use the getUserFromToken helper to access the current user based on the token, try to find the article (again based on the slug property) and create the comment entity. Since we use em.create() here, we don't have to em.persist() the new entity, as it happens automatically this way.

modules/article/routes.ts

app.post('/:slug/comment', async request => {
  const { slug, text } = request.params as { slug: string; text: string };
  const author = getUserFromToken(request);
  const article = await db.article.findOneOrFail({ slug });
  const comment = db.comment.create({ author, article, text });

  // We can add the comment to `article.comments` collection,
  // but in fact it is a no-op, as it will be automatically
  // propagated by setting Comment.author property.
  article.comments.add(comment);

  // mention we don't need to persist anything explicitly
  await db.em.flush();

  return comment;
});

创建新文章非常相似。

¥Creating a new article is very similar.

modules/article/routes.ts

app.post('/', async request => {
  const { title, description, text } = request.body as { title: string; description: string; text: string };
  const author = getUserFromToken(request);
  const article = db.article.create({
    title,
    description,
    text,
    author,
  });

  await db.em.flush();

  return article;
});

更新实体

¥Updating entities

对于更新，我们使用 wrap(article).assign()，这是一种辅助方法，它将数据正确映射到实体图。它会自动将外键转换为实体引用。

¥For updating we use wrap(article).assign(), a helper method which will map the data to entity graph correctly. It will transform foreign keys into entity references automatically.

或者，你可以使用 em.assign()，它也适用于非托管实体。

¥Alternatively, you can use em.assign(), which will also work for not managed entities.

modules/article/routes.ts

app.patch('/:id', async request => {
  const user = getUserFromToken(request);
  const params = request.params as { id: string };
  const article = await db.article.findOneOrFail(+params.id);
  verifyArticlePermissions(user, article);
  wrap(article).assign(request.body as Article);
  await db.em.flush();

  return article;
});

我们还验证只有文章的作者可以更改它：

¥We also validate that only the author of the article can change it:

modules/common/utils.ts

export function verifyArticlePermissions(user: User, article: Article): void {
  if (article.author.id !== user.id) {
    throw new Error('You are not the author of this article!');
  }
}

更新插入实体

¥Upserting entities

或者，你可以使用 em.upsert() 来一步创建或更新实体。它将在后台使用 INSERT ON CONFLICT 查询：

¥Alternatively, you could use em.upsert() instead to create or update the entity in one step. It will use INSERT ON CONFLICT query under the hood:

-const article = await db.article.findOneOrFail(+params.id);
-wrap(article).assign(request.body as Article);
-await db.em.flush();
+const article = await db.article.upsert(request.body as Article);

要批量更新多个实体，你可以使用 em.upsertMany()，它将在单个查询中处理所有内容。

¥To upsert many entities in a batch, you can use em.upsertMany(), which will handle everything within a single query.

在 实体管理器 部分中阅读更多有关更新插入的内容。

¥Read more about upserting in Entity Manager section.

删除实体

¥Removing entities

有几种删除实体的方法。在这种情况下，我们首先加载实体，如果不存在，我们在响应中返回 notFound: true，如果存在，我们通过 em.remove() 将其删除，这将标记实体在接下来的 flush() 调用中被删除。

¥There are several approaches to removing an entity. In this case, we first load the entity, if it does not exist, we return notFound: true in the response, if it does, we remove it via em.remove(), which marks the entity for removal on the following flush() call.

modules/article/routes.ts

app.delete('/:id', async request => {
  const user = getUserFromToken(request);
  const params = request.params as { id: string };
  const article = await db.article.findOne(+params.id);

  if (!article) {
    return { notFound: true };
  }

  verifyArticlePermissions(user, article);
  // mention `nativeDelete` alternative if we don't care about validations much
  await db.em.remove(article).flush();

  return { success: true };
});

你还可以使用 em.nativeDelete() 或 QueryBuilder 执行 DELETE 查询。

¥You could also use em.nativeDelete() or QueryBuilder to execute a DELETE query.

await db.article.nativeDelete(+params.id);

批量插入、更新和删除

¥Batch inserts, updates and deletes

虽然我们在本指南中没有这样的用例，但使用 EntityManager 和工作单元方法的一大好处是自动批处理 - 所有 INSERT、UPDATE 和 DELETE 查询将自动批处理为每个实体的单个查询。

¥While we do not have such a use case in this guide, a huge benefit of using the EntityManager with Unit of Work approach is automatic batching - all the INSERT, UPDATE and DELETE queries will be batched automatically into a single query per entity.

插入

¥Insert

for (let i = 1; i <= 5; i++) {
  const u = new User(`Peter ${i}`, `peter+${i}@foo.bar`);
  em.persist(u);
}

await em.flush();

insert into `user` (`name`, `email`) values
  ('Peter 1', 'peter+1@foo.bar'),
  ('Peter 2', 'peter+2@foo.bar'),
  ('Peter 3', 'peter+3@foo.bar'),
  ('Peter 4', 'peter+4@foo.bar'),
  ('Peter 5', 'peter+5@foo.bar');

更新

¥Update

const users = await em.find(User, {});

for (const user of users) {
  user.name += ' changed!';
}

await em.flush();

update `user` set
  `name` = case
    when (`id` = 1) then 'Peter 1 changed!'
    when (`id` = 2) then 'Peter 2 changed!'
    when (`id` = 3) then 'Peter 3 changed!'
    when (`id` = 4) then 'Peter 4 changed!'
    when (`id` = 5) then 'Peter 5 changed!'
    else `priority` end
  where `id` in (1, 2, 3, 4, 5);

删除

¥Delete

const users = await em.find(User, {});

em.remove(users);

await em.flush();

delete from `user` where `id` in (1, 2, 3, 4, 5);

禁用更改跟踪

¥Disabling change tracking

有时你可能想禁用身份映射并更改某些查询的集合跟踪。这可以通过 disableIdentityMap 选项实现。在幕后，它将创建新的上下文，加载其中的实体，然后清除它，因此主身份图将保持干净，但从单个 find 调用返回的实体仍将相互连接。

¥Sometimes you might want to disable identity map and change set tracking for some query. This is possible via disableIdentityMap option. Behind the scenes, it will create new context, load the entities inside that, and clear it afterward, so the main identity map will stay clean, but the entities returned from a single find call will be still interconnected.

与托管实体相反，此类实体称为分离实体。为了能够使用它们，你首先需要通过 em.merge() 合并它们。

¥As opposed to managed entities, such entities are called detached. To be able to work with them, you first need to merge them via em.merge().

const user = await db.user.findOneOrFail({ email: 'foo@bar.baz' }, {
  disableIdentityMap: true,
});
user.name = 'changed';
await db.em.flush(); // calling flush have no effect, as the entity is not managed

虚拟实体

¥Virtual entities

现在让我们改进我们的第一个文章端点 - 我们使用 em.findAndCount() 轻松获得分页结果，但如果我们想自定义响应怎么办？一种方法是使用 虚拟实体。它们不代表任何数据库表，而是动态解析为 SQL 查询，允许你将任何类型的结果映射到实体上。

¥Let's now improve our first article endpoint - we used em.findAndCount() to get paginated results easily, but what if we want to customize the response? One way to do that are Virtual entities. They don't represent any database table, instead, they dynamically resolve to an SQL query, allowing you to map any kind of results onto an entity.

信息

虚拟实体用于读取目的，它们没有主键，因此无法跟踪更改。在某种程度上，它们类似于原生数据库视图 - 你也可以使用它们将原生数据库视图代理到 ORM 实体。

¥Virtual entities are meant for read purposes, they don't have a primary key and therefore cannot be tracked for changes. In a way they are similar to native database views - and you can use them to proxy your native database views to ORM entities too.

要定义虚拟实体，请在 @Entity() 装饰器选项中提供 expression。In 可以是字符串（SQL 查询）或返回 SQL 查询或 QueryBuilder 实例的回调。仅支持标量属性 (@Property())。

¥To define a virtual entity, provide an expression in the @Entity() decorator options. In can be a string (SQL query) or a callback returning an SQL query or a QueryBuilder instance. Only scalar properties (@Property()) are supported.

modules/article/article-listing.entity.ts

import { Entity, EntityManager, Property } from '@mikro-orm/sqlite';
import { Article } from './article.entity.js';

@Entity({
  expression: (em: EntityManager) => {
    return em.getRepository(Article).listArticlesQuery();
  },
})
export class ArticleListing {

  @Property()
  slug!: string;

  @Property()
  title!: string;

  @Property()
  description!: string;

  @Property()
  tags!: string[];

  @Property()
  author!: number;

  @Property()
  authorName!: string;

  @Property()
  totalComments!: number;

}

现在也为 Article 实体创建一个自定义存储库，并在其中放入两个方法：

¥Now create a custom repository for the Article entity too, and put two methods inside:

modules/article/article.repository.ts

import { FindOptions, sql, EntityRepository } from '@mikro-orm/sqlite';
import { Article } from './article.entity.js';
import { ArticleListing } from './article-listing.entity.js';

// extending the EntityRepository exported from driver package, so we can access things like the QB factory
export class ArticleRepository extends EntityRepository<Article> {

  listArticlesQuery() {
    // just a placeholder for now
    return this.createQueryBuilder('a');
  }

  async listArticles(options: FindOptions<ArticleListing>) {
    const [items, total] = await this.em.findAndCount(ArticleListing, {}, options);
    return { items, total };
  }

}

并在端点中使用这个新的 listArticles() 方法：

¥And use this new listArticles() method in the endpoint:

modules/article/routes.ts

// list articles
app.get('/', async request => {
  const { limit, offset } = request.query as { limit?: number; offset?: number };

  const { items, total } = await db.article.listArticles({
    limit, offset,
  });

  return { items, total };
});

使用 QueryBuilder

¥Using QueryBuilder

listArticlesQuery() 存储库方法会稍微复杂一些。我们希望将文章与相应的评论数量一起加载。为此，我们可以将 QueryBuilder 与子查询一起使用，该子查询将加载每个选定文章的评论计数。类似地， 执行相反的操作。要获取作者的名称，我们可以使用简单的 JOIN。

¥The listArticlesQuery() repository method will be a bit more complex. We want to load the articles together with the number of corresponding comments. To do that, we can use the QueryBuilder with a sub-query which will load the comments count for each selected article. Similarly, we want to load all the tags added to the article. To get the author's name, we can use a simple JOIN.

你可以在 使用查询构建器 部分中找到更多详细信息。

¥You can find more details in the Using Query Builder section.

让我们先做简单的事情 - 我们想选择 slug、title、description 和 author 列：

¥Let's first do the easy things - we want to select slug, title, description and author columns:

modules/article/article.repository.ts

return this.createQueryBuilder('a')
  .select(['slug', 'title', 'description', 'author']);

现在让我们加入 User 实体并选择作者的名称。要在列上使用自定义别名，我们将使用 sql.ref() 助手：

¥Now let's join the User entity and select the author's name. To have a custom alias on the column, we will use sql.ref() helper:

modules/article/article.repository.ts

return this.createQueryBuilder('a')
  .select(['slug', 'title', 'description', 'author'])
  .addSelect(sql.ref('u.full_name').as('authorName'))
  .join('author', 'u')

现在是子查询 - 我们将需要其中两个，两者都将使用相同的 sql.ref() 助手（这次没有别名）和 QueryBuilder.as() 方法来为整个子查询添加别名。

¥And now the sub-queries - we will need two of them, both will use the same sql.ref() helper (this time without aliasing) and the QueryBuilder.as() method to alias the whole sub-query.

modules/article/article.repository.ts

import { FindOptions, sql, EntityRepository } from '@mikro-orm/sqlite';
import { Article } from './article.entity.js';
import { ArticleListing } from './article-listing.entity.js';
import { Comment } from './comment.entity.js';

export class ArticleRepository extends EntityRepository<Article> {

  // ...

  listArticlesQuery() {
    // sub-query for total number of comments
    const totalComments = this.em.createQueryBuilder(Comment)
      .count()
      .where({ article: sql.ref('a.id') })
      // by calling `qb.as()` we convert the QB instance to Knex instance
      .as('totalComments');

    // sub-query for all used tags
    const usedTags = this.em.createQueryBuilder(Article, 'aa')
      // we need to mark raw query fragment with `sql` helper
      // otherwise it would be escaped
      .select(sql`group_concat(distinct t.name)`)
      .join('aa.tags', 't')
      .where({ 'aa.id': sql.ref('a.id') })
      .groupBy('aa.author')
      .as('tags');

    // build final query
    return this.createQueryBuilder('a')
      .select(['slug', 'title', 'description', 'author'])
      .addSelect(sql.ref('u.full_name').as('authorName'))
      .join('author', 'u')
      .addSelect([totalComments, usedTags]);
  }

}

请注意，在将 group_concat 表达式添加到 select 子句时，我们如何将 sql 辅助函数用作标记模板。阅读有关对 原始查询在这里 的支持的更多信息。

¥Note how we used the sql helper function as a tagged template when adding the group_concat expression to the select clause. Read more about the support for raw queries here.

执行查询

¥Executing the Query

在我们的示例中，我们仅返回 QueryBuilder 实例并让 ORM 通过我们的虚拟实体执行它，你可能会问：如何手动执行查询？有两种方法，第一种是 qb.execute() 方法，它为你提供原始结果（普通对象）。默认情况下，它将返回一个项目数组，自动将列名映射到属性名。你可以使用第一个参数来控制结果的模式和形式：

¥In our example, we just return the QueryBuilder instance and let the ORM execute it through our virtual entity, you may ask: how can you execute the query manually? There are two ways, the first is the qb.execute() method, which gives you raw results (plain objects). By default, it will return an array of items, mapping column names to property names automatically. You can use the first parameter to control the mode and form of result:

const res1 = await qb.execute('all'); // returns array of objects, default behavior
const res2 = await qb.execute('get'); // returns single object
const res3 = await qb.execute('run'); // returns object like `{ affectedRows: number, insertId: number, row: any }`

第二个参数可用于禁用数据库列到属性名称的映射。在下面的例子中，Article 实体具有一个 createdAt 属性，该属性使用隐式下划线字段名称 created_at 定义：

¥The second argument can be used to disable the mapping of database columns to property names. In the following example, the Article entity has a createdAt property defined with implicit underscored field name created_at:

const res1 = await em.createQueryBuilder(Article).select('*').execute('get', true);
console.log(res1); // `createdAt` will be defined, while `created_at` will be missing

const res2 = await em.createQueryBuilder(Article).select('*').execute('get', false);
console.log(res2); // `created_at` will be defined, while `createdAt` will be missing

要从 QueryBuilder 结果中获取实体实例，你可以使用 getResult() 和 getSingleResult() 方法：

¥To get the entity instances from the QueryBuilder result, you can use the getResult() and getSingleResult() methods:

const article = await em.createQueryBuilder(Article)
  .select('*')
  .where({ id: 1 })
  .getSingleResult();
console.log(article instanceof Article); // true

const articles = await em.createQueryBuilder(Article)
  .select('*')
  .getResult();
console.log(articles[0] instanceof Article); // true

你还可以使用 qb.getResultList()，它是 qb.getResult() 的别名。

¥You can also use qb.getResultList() which is alias for qb.getResult().

等待 QueryBuilder

¥Awaiting the QueryBuilder

你还可以等待 QueryBuilder 实例，它将自动执行 QueryBuilder 并自动返回适当的响应。QueryBuilder 实例根据 select/insert/update/delete/truncate 方法的使用情况输入以下类型之一：

¥You can also await the QueryBuilder instance, which will automatically execute the QueryBuilder and return an appropriate response automatically. The QueryBuilder instance is typed based on the usage of select/insert/update/delete/truncate methods to one of:

• SelectQueryBuilder

  • 等待产生实体数组（作为 qb.getResultList()）

    ¥awaiting yields array of entities (as qb.getResultList())

• CountQueryBuilder

  • 等待产生数字（作为 qb.getCount()）

    ¥awaiting yields number (as qb.getCount())

• InsertQueryBuilder（扩展 RunQueryBuilder）

  ¥InsertQueryBuilder (extends RunQueryBuilder)

  • 等待产生 QueryResult

    ¥awaiting yields QueryResult

• UpdateQueryBuilder（扩展 RunQueryBuilder）

  ¥UpdateQueryBuilder (extends RunQueryBuilder)

  • 等待产生 QueryResult

    ¥awaiting yields QueryResult

• DeleteQueryBuilder（扩展 RunQueryBuilder）

  ¥DeleteQueryBuilder (extends RunQueryBuilder)

  • 等待产生 QueryResult

    ¥awaiting yields QueryResult

• TruncateQueryBuilder（扩展 RunQueryBuilder）

  ¥TruncateQueryBuilder (extends RunQueryBuilder)

  • 等待产生 QueryResult

    ¥awaiting yields QueryResult

em.qb() 是 em.createQueryBuilder() 的快捷方式。

¥em.qb() is a shortcut for em.createQueryBuilder().

const res1 = await em.qb(User).insert({
  fullName: 'Jon',
  email: 'foo@bar.com',
});
// res1 is of type `QueryResult<User>`
console.log(res1.insertId);

const res2 = await em.qb(User)
  .select('*')
  .where({ fullName: 'Jon' })
  .limit(5);
// res2 is User[]
console.log(res2.map(p => p.name));

const res3 = await em.qb(User).count().where({ fullName: 'Jon' });
// res3 is number
console.log(res3 > 0); // true

const res4 = await em.qb(User)
  .update({ email: 'foo@bar.com' })
  .where({ fullName: 'Jon' });
// res4 is QueryResult<User>
console.log(res4.affectedRows > 0); // true

const res5 = await em.qb(User).delete().where({ fullName: 'Jon' });
// res5 is QueryResult<User>
console.log(res5.affectedRows > 0); // true
expect(res5.affectedRows > 0).toBe(true); // test the type

更新测试

¥Updating the tests

我们刚刚改变了 API 响应的形式，这是我们已经测试过的东西，所以让我们修复损坏的测试。首先，在我们的 TestSeeder 中创建一些测试注释：

¥We just changed the shape of our API response, which is something we test already, so let's fix our broken tests. First, create some testing comments in our TestSeeder:

seeders/TestSeeder.ts

export class TestSeeder extends Seeder {
  async run(em: EntityManager): Promise<void> {
-   em.create(User, {
+   const author = em.create(User, {
      fullName: "Foo Bar",
      email: "foo@bar.com",
      // ...
    });

+   em.assign(author.articles[0], {
+     comments: [
+       { author, text: `random comment ${Math.random()}` },
+       { author, text: `random comment ${Math.random()}` },
+     ],
+   });
+
+   em.assign(author.articles[1], {
+     comments: [{ author, text: `random comment ${Math.random()}` }],
+   });
+
+   em.assign(author.articles[2], {
+     comments: [
+       { author, text: `random comment ${Math.random()}` },
+       { author, text: `random comment ${Math.random()}` },
+       { author, text: `random comment ${Math.random()}` },
+     ],
+   });
  }
}

test/article.test.ts

expect(res.json()).toMatchObject({
  items: [
-   { author: 1, slug: "title-13", title: "title 1/3" },
-   { author: 1, slug: "title-23", title: "title 2/3" },
-   { author: 1, slug: "title-33", title: "title 3/3" },
+   {
+     slug: expect.any(String),
+     title: 'title 1/3',
+     description: 'desc 1/3',
+     tags: ['foo1', 'foo2'],
+     authorName: 'Foo Bar',
+     totalComments: 2,
+   },
+   {
+     slug: expect.any(String),
+     title: 'title 2/3',
+     description: 'desc 2/3',
+     tags: ['foo2'],
+     authorName: 'Foo Bar',
+     totalComments: 1,
+   },
+   {
+     slug: expect.any(String),
+     title: 'title 3/3',
+     description: 'desc 3/3',
+     tags: ['foo2', 'foo3'],
+     authorName: 'Foo Bar',
+     totalComments: 3,
+   },
  ],
  total: 3,
});

结果缓存

¥Result cache

MikroORM 有一个简单的 结果缓存 机制，你需要做的就是将 cache 选项添加到你的 em.find() 选项中。值可以是以下之一：

¥MikroORM has a simple result caching mechanism, all you need to do is add cache option to your em.find() options. The value can be one of:

• true 用于默认到期时间（可全局配置，默认为 1 秒）。

  ¥true for default expiration (configurable globally, defaults to 1 second).

• 一个显式到期的数字（以毫秒为单位）。

  ¥A number for explicit expiration (in milliseconds).

• 一个元组，第一个元素是 cacheKey (string)，第二个元素是到期时间 (number)。你可以使用 cacheKey 通过 em.clearCache() 清除缓存。

  ¥A tuple with first element being the cacheKey (string) and the second element the expiration (number). You can use the cacheKey to clear the cache via em.clearCache().

让我们为文章列表端点启用缓存，并设置 5 秒的有效期：

¥Let's enable the caching for our article listing endpoint, with a 5-second expiration:

modules/article/routes.ts

// list articles
app.get('/', async request => {
  const { limit, offset } = request.query as { limit?: number; offset?: number };

  const { items, total } = await db.article.listArticles({
    limit, offset,
    cache: 5_000, // 5 seconds
  });

  return { items, total };
});

现在，当你启用 调试模式 并尝试在 5 秒内多次访问端点时，你应该只看到第一个请求产生查询。

¥Now when you enable debug mode and try to access the endpoint several times within 5 seconds, you should see just the first request producing queries.

部署

¥Deployment

我们的应用几乎已准备就绪，现在让我们准备生产版本。由于我们正在使用 ts-morph 元数据提供程序，因此如果没有预建缓存，我们的启动时间会很慢。我们可以通过 CLI 执行此操作：

¥Our app is nearly ready, now let's prepare the production build. Since we are using the ts-morph metadata provider, our start-up time would be slow without a prebuilt cache. We can do that via the CLI:

npx mikro-orm-esm cache:generate

但是我们的生产依赖现在仍然包含 @mikro-orm/reflection 包，并且它依赖于 TypeScript 本身，这使得包不必要地变大。为了解决这个问题，我们可以生成一个元数据缓存包并通过 GeneratedCacheAdapter 使用它。这样，你可以将 @mikro-orm/reflection 包仅保留为开发依赖，使用 CLI 创建缓存包，并在生产版本中仅依赖它。

¥But our production dependencies still contain the @mikro-orm/reflection package now, and that depends on TypeScript itself, making the bundle unnecessarily larger. To resolve this, we can generate a metadata cache bundle and use that via GeneratedCacheAdapter. This way you can keep the @mikro-orm/reflection package as a development dependency only, use the CLI to create the cache bundle, and depend only on that in your production build.

npx mikro-orm-esm cache:generate --combined

这将创建 ./temp/metadata.json 文件，该文件可与 GeneratedCacheAdapter 一起用于生产配置。让我们调整我们的 ORM 配置，以便在 NODE_ENV 设置为 production 时动态使用它：

¥This will create ./temp/metadata.json file which can be used together with GeneratedCacheAdapter in your production configuration. Let's adjust our ORM config to dynamically use it when NODE_ENV is set to production:

src/mikro-orm.config.ts

import { defineConfig, GeneratedCacheAdapter, Options } from '@mikro-orm/sqlite';
import { SqlHighlighter } from '@mikro-orm/sql-highlighter';
import { SeedManager } from '@mikro-orm/seeder';
import { Migrator } from '@mikro-orm/migrations';
+import { existsSync, readFileSync } from 'node:fs';
+
+const options = {} as Options;
+
+if (process.env.NODE_ENV === 'production' && existsSync('./temp/metadata.json')) {
+  options.metadataCache = {
+    enabled: true,
+    adapter: GeneratedCacheAdapter,
+    // temp/metadata.json can be generated via `npx mikro-orm-esm cache:generate --combine`
+    options: {
+      data: JSON.parse(readFileSync('./temp/metadata.json', { encoding: 'utf8' })),
+    },
+  };
+} else {
+  options.metadataProvider = (await import('@mikro-orm/reflection')).TsMorphMetadataProvider;
+}

export default defineConfig({
   // for simplicity, we use the SQLite database, as it's available pretty much everywhere
   dbName: 'sqlite.db',
   // folder based discovery setup, using common filename suffix
   entities: ['dist/**/*.entity.js'],
   entitiesTs: ['src/**/*.entity.ts'],
   // enable debug mode to log SQL queries and discovery information
   debug: true,
   // for vitest to get around `TypeError: Unknown file extension ".ts"` (ERR_UNKNOWN_FILE_EXTENSION)
   dynamicImportProvider: id => import(id),
   // for highlighting the SQL queries
   highlighter: new SqlHighlighter(),
   extensions: [SeedManager, Migrator],
-  metadataProvider: TsMorphMetadataProvider,
+  ...options,
});

最后，让我们调整 NPM build 脚本以生成缓存包，并添加生产启动脚本：

¥Finally, let's adjust the NPM build script to generate the cache bundle, and add a production start script:

package.json

"scripts": {
  "build": "tsc && npx mikro-orm-esm cache:generate --combined",
  "start": "node --no-warnings=ExperimentalWarning --loader ts-node/esm src/server.ts",
  "start:prod": "NODE_ENV=production node dist/server.js",
  "test": "vitest"
},

现在你可以构建并运行应用的生产版本：

¥Now you can build and run the production version of your app:

npm run build
npm run start:prod

你可以在日志中看到 build 脚本使用 ts-morph 元数据提供程序，而 start 脚本使用默认的 reflect-metadata。

¥You can see in the logs that the build script uses ts-morph metadata provider, while the start script is using the default reflect-metadata one.

⛳ 检查点 4

¥⛳ Checkpoint 4

我们的应用成型得相当好，我们现在已经实现了所有端点并进行了基本测试。

¥Our app is shaping quite well, we now have all the endpoints implemented and covered with basic tests.

https://codesandbox.io/p/sandbox/mikroorm-getting-started-guide-checkpoint-4-dhg2vj?file=src/app.ts

app.ts

import { NotFoundError, RequestContext } from "@mikro-orm/core";
import { fastify } from "fastify";
import fastifyJWT from "@fastify/jwt";
import { initORM } from "./db.js";
import { registerArticleRoutes } from "./modules/article/routes.js";
import { registerUserRoutes } from "./modules/user/routes.js";
import { AuthError } from "./modules/common/utils.js";

export async function bootstrap(port = 3001, migrate = true) {
  const db = await initORM();

  if (migrate) {
    // sync the schema
    await db.orm.migrator.up();
  }

  const app = fastify();

  // register JWT plugin
  app.register(fastifyJWT, {
    secret: process.env.JWT_SECRET ?? "12345678", // fallback for testing
  });

  // register request context hook
  app.addHook("onRequest", (request, reply, done) => {
    RequestContext.create(db.em, done);
  });

  // register auth hook after the ORM one to use the context
  app.addHook("onRequest", async (request) => {
    try {
      const ret = await request.jwtVerify<{ id: number }>();
      request.user = await db.user.findOneOrFail(ret.id);
    } catch (e) {
      app.log.error(e);
      // ignore token errors, we validate the request.user exists only where needed
    }
  });

  // register global error handler to process 404 errors from `findOneOrFail` calls
  app.setErrorHandler((error, request, reply) => {
    if (error instanceof AuthError) {
      return reply.status(401).send({ error: error.message });
    }

    if (error instanceof NotFoundError) {
      return reply.status(404).send({ error: error.message });
    }

    app.log.error(error);
    reply.status(500).send({ error: error.message });
  });

  // shut down the connection when closing the app
  app.addHook("onClose", async () => {
    await db.orm.close();
  });

  // register routes here
  app.register(registerArticleRoutes, { prefix: "article" });
  app.register(registerUserRoutes, { prefix: "user" });

  const url = await app.listen({ port });

  return { app, url, db };
}