使用查询构建器

信息

自 v4 以来，默认情况下禁用高亮，并且有 2 个荧光笔可以选择使用（你需要先安装它们）。

¥Since v4, we need to make sure we are working with correctly typed EntityManager or EntityRepository to have access to createQueryBuilder() method.

import { EntityManager, EntityRepository } from '@mikro-orm/mysql'; // or any other driver package

当你需要执行一些不涉及所有 ORM 内容的 SQL 查询时，你可以自己编写查询，也可以使用 QueryBuilder 助手为你构建查询：

¥When you need to execute some SQL query without all the ORM stuff involved, you can either compose the query yourself, or use the QueryBuilder helper to construct the query for you:

// since v5 we can also use `em.qb()` shortcut
const qb = em.createQueryBuilder(Author);
qb.update({ name: 'test 123', type: PublisherType.GLOBAL }).where({ id: 123, type: PublisherType.LOCAL });

console.log(qb.getQuery());
// update `publisher2` set `name` = ?, `type` = ? where `id` = ? and `type` = ?

console.log(qb.getParams());
// ['test 123', PublisherType.GLOBAL, 123, PublisherType.LOCAL]

// run the query
const res1 = await qb.execute();

QueryBuilder 还支持 智能查询条件。

¥QueryBuilder also supports smart query conditions.

执行查询

¥Executing the Query

你可以使用 execute(method = 'all', mapResults = true) 的参数来控制结果的形式：

¥You can use execute(method = 'all', mapResults = true)'s parameters to control 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 }`

第二个参数可用于禁用数据库列到属性名称的映射（默认情况下启用）。在以下示例中，Book 实体具有 createdAt 属性，该属性使用隐式下划线字段名称 created_at 定义：

¥Second argument can be used to disable mapping of database columns to property names (which is enabled by default). In following example, Book entity has createdAt property defined with implicit underscored field name created_at:

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

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

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

const book = await em.createQueryBuilder(Book).select('*').where({ id: 1 }).getSingleResult();
console.log(book instanceof Book); // true
const books = await em.createQueryBuilder(Book).select('*').getResult();
console.log(books[0] instanceof Book); // true

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

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

等待 QueryBuilder

¥Awaiting the QueryBuilder

自 v5 以来，我们还可以创建实体引用而无需访问 QueryBuilder。现在，QB 实例根据 select/insert/update/delete/truncate 方法的使用情况被键入以下类型之一：

¥Since v5 we can await the QueryBuilder instance, which will automatically execute the QB and return appropriate response. The QB instance is now typed based on 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

const res1 = await em.qb(Publisher).insert({
  name: 'p1',
  type: PublisherType.GLOBAL,
});
// res1 is of type `QueryResult<Publisher>`
console.log(res1.insertId);

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

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

const res4 = await em.qb(Publisher)
  .update({ type: PublisherType.LOCAL })
  .where({ name: 'p1' });
// res4 is QueryResult<Publisher>
console.log(res4.affectedRows > 0); // true

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

将原始结果映射到实体

¥Mapping Raw Results to Entities

从原始结果（不一定映射到实体属性）创建实体的另一种方法是使用 EntityManager 的 map() 方法，这基本上是通过 IDatabaseDriver.mapResult() 映射结果的快捷方式（将字段名称转换为属性名称 - 例如 created_at 到 createdAt）和 merge()，将数据转换为实体实例并使其受管理。

¥Another way to create entity from raw results (that are not necessarily mapped to entity properties) is to use map() method of EntityManager, that is basically a shortcut for mapping results via IDatabaseDriver.mapResult() (which converts field names to property names - e.g. created_at to createdAt) and merge() which converts the data to entity instance and makes it managed.

当你想要使用第三方查询构建器时，此方法非常方便，其中结果不会自动映射到实体属性：

¥This method comes handy when you want to use 3rd party query builders, where the result is not mapped to entity properties automatically:

const results = await knex.select('*').from('users').where(knex.raw('id = ?', [id]));
const users = results.map(user => em.map(User, user));

// or use EntityRepository.map()
const repo = em.getRepository(User);
const users = results.map(user => repo.map(user));

隐式连接

¥Implicit Joining

QueryBuilder 支持基于实体元数据的自动连接：

¥QueryBuilder supports automatic joining based on entity metadata:

const qb = em.createQueryBuilder(BookTag, 't');
qb.select('*').where({ books: 123 });

console.log(qb.getQuery());
// select `t`.*, `e1`.`book_tag_id`, `e1`.`book_uuid_pk`
// from `book_tag` as `t`
// left join `book_to_book_tag` as `e1` ON `t`.`id` = `e1`.`book_tag_id`
// where `e1`.`book_uuid_pk` = ?

这也适用于多层嵌套：

¥This also works for multiple levels of nesting:

const qb = em.createQueryBuilder(Author);
qb.select('*')
  .where({ books: { tags: { name: 'Cool' } } })
  .orderBy({ books: { tags: { createdBy: QueryOrder.DESC } } });

console.log(qb.getQuery());
// select `e0`.*
// from `author` as `e0`
// left join `book2` as `e1` on `e0`.`id` = `e1`.`author_id`
// left join `book2_to_book_tag2` as `e3` on `e1`.`uuid_pk` = `e3`.`book2_uuid_pk`
// left join `book_tag2` as `e2` on `e3`.`book_tag2_id` = `e2`.`id`
// where `e2`.`name` = ?
// order by `e1`.`tags` asc

这目前仅适用于过滤（where）和排序（orderBy），只会选择根实体。要填充其关系，你可以使用 em.populate()。

¥This is currently available only for filtering (where) and sorting (orderBy), only the root entity will be selected. To populate its relationships, you can use em.populate().

显式连接

¥Explicit Joining

另一种方法是通过 join()/leftJoin() 方法手动指定连接属性：

¥Another way is to manually specify join property via join()/leftJoin() methods:

const qb = em.createQueryBuilder(BookTag, 't');
qb.select(['b.uuid', 'b.*', 't.*'], true)
  .join('t.books', 'b')
  .where({ 'b.title': 'test 123' })
  .limit(2, 1);

console.log(qb.getQuery());
// select distinct `b`.`uuid_pk`, `b`.*, `t`.*, `e1`.`book_tag_id`, `e1`.`book_uuid_pk` from `book_tag` as `t`
// join `book_to_book_tag` as `e1` ON `t`.`id` = `e1`.`book_tag_id`
// join `book` as `b` ON `e1`.`book_uuid_pk` = `b`.`uuid_pk`
// where `b`.`title` = ?
// limit ? offset ?

映射连接结果

¥Mapping joined results

要选择多个实体并从 QueryBuilder 映射它们，我们可以使用 joinAndSelect 或 leftJoinAndSelect 方法：

¥To select multiple entities and map them from QueryBuilder, we can use joinAndSelect or leftJoinAndSelect method:

// `res` will contain array of authors, with books and their tags populated
const res = await em.createQueryBuilder(Author, 'a')
  .select('*')
  .leftJoinAndSelect('a.books', 'b')
  .leftJoinAndSelect('b.tags', 't')
  .where({ 't.name': ['sick', 'sexy'] })
  .getResultList();

连接子查询

¥Joining sub-queries

有时你可能想要加入一个关系，但又想对查询有更多的控制权。ORM 允许你使用子查询覆盖连接目标，同时保留原始元数据以进行水化：

¥Sometimes you might want to join a relation, but want to have more control over the query. The ORM allows you to override the join target with a sub-query, while keeping the original metadata for hydration:

// subquery can be a knex query builder as well
const subquery = em.createQueryBuilder(Book, 'b')
  .where({ ... })
  .orderBy({ title: 'asc' }).limit(1);

const authors = await em.createQueryBuilder(Author, 'a')
  .select('*')
  // pass in both the property path and the subquery into the first argument as a tuple
  .leftJoinAndSelect(['a.books', subquery], 'b')
  // you can join more relations on top of the subquery join
  .leftJoinAndSelect('b.tags', 't')
  .getResultList();

这将产生类似于以下内容的查询：

¥This will produce query similar to the following:

select `a`.*,
  `b`.`id` as `b__id`, `b`.`title` as `b__title`, `b`.`author_id` as `b__author_id`, `b`.`publisher_id` as `b__publisher_id`,
  `t`.`id` as `t__id`, `t`.`name` as `t__name`
  from `author` as `a`
  left join (
    select `b`.*, `b`.price * 1.19 as `price_taxed`
    from `book` as `b`
    order by `b`.`title` asc
    limit 1
  ) as `b` on `b`.`author_id` = `a`.`id`
  left join `book_tags` as `e1` on `b`.`uuid_pk` = `e1`.`book_uuid_pk`
  left join `book_tag` as `t` on `e1`.`book_tag_id` = `t`.`id`

复杂 Where 条件

¥Complex Where Conditions

有多种构造复杂查询条件的方法。你可以手动编写 SQL 的部分内容，使用 andWhere()/orWhere()，或者提供条件对象：

¥There are multiple ways to construct complex query conditions. You can either write parts of SQL manually, use andWhere()/orWhere(), or provide condition object:

使用自定义 SQL 片段

¥Using custom SQL fragments

WHERE 查询或 ORDER BY 子句中的任何 SQL 片段都需要用 raw() 或 sql 封装：

¥Any SQL fragment in your WHERE query or ORDER BY clause need to be wrapped with raw() or sql:

const users = em.createQueryBuilder(User)
  .select('*')
  .where({ [sql`lower(email)`]: 'foo@bar.baz' }) // sql tagged template function
  .orderBy({ [raw(`(point(loc_latitude, loc_longitude) <@> point(0, 0))`)]: 'ASC' }) // raw helper
  .getResultList();

这将产生以下查询：

¥This will produce following query:

select `e0`.*
from `user` as `e0`
where lower(email) = 'foo@bar.baz'
order by (point(loc_latitude, loc_longitude) <@> point(0, 0)) asc

在 使用原始 SQL 查询片段 部分中阅读有关此内容的更多信息。

¥Read more about this in Using raw SQL query fragments section.

自定义 SQL 位置

¥Custom SQL in where

const qb = em.createQueryBuilder(BookTag, 't');
qb.select(['b.*', 't.*'])
  .leftJoin('t.books', 'b')
  .where('b.title = ? or b.title = ?', ['test 123', 'lol 321'])
  .andWhere('1 = 1')
  .orWhere('1 = 2')
  .limit(2, 1);

console.log(qb.getQuery());
// select `b`.*, `t`.*, `e1`.`book_tag_id`, `e1`.`book_uuid_pk` from `book_tag` as `t`
// left join `book_to_book_tag` as `e1` ON `t`.`id` = `e1`.`book_tag_id`
// left join `book` as `b` ON `e1`.`book_uuid_pk` = `b`.`uuid_pk`
// where (((b.title = ? or b.title = ?) and (1 = 1)) or (1 = 2))
// limit ? offset ?

andWhere() and orWhere()

const qb = em.createQueryBuilder(BookTag, 't');
qb.select(['b.*', 't.*'])
  .leftJoin('t.books', 'b')
  .where('b.title = ? or b.title = ?', ['test 123', 'lol 321'])
  .andWhere('1 = 1')
  .orWhere('1 = 2')
  .limit(2, 1);

console.log(qb.getQuery());
// select `b`.*, `t`.*, `e1`.`book_tag_id`, `e1`.`book_uuid_pk` from `book_tag` as `t`
// left join `book_to_book_tag` as `e1` ON `t`.`id` = `e1`.`book_tag_id`
// left join `book` as `b` ON `e1`.`book_uuid_pk` = `b`.`uuid_pk`
// where (((b.title = ? or b.title = ?) and (1 = 1)) or (1 = 2))
// limit ? offset ?

条件对象

¥Conditions Object

const qb = em.createQueryBuilder(Test);
qb.select('*').where({ $and: [{ id: { $nin: [3, 4] } }, { id: { $gt: 2 } }] });

console.log(qb.getQuery());
// select `e0`.* from `test` as `e0` where (`e0`.`id` not in (?, ?) and `e0`.`id` > ?)

计数查询

¥Count queries

要创建计数查询，我们可以使用 qb.count()，它将使用 count() 函数初始化 select 子句。默认情况下，它将使用主键。

¥To create a count query, we can use qb.count(), which will initialize a select clause with count() function. By default, it will use the primary key.

const qb = em.createQueryBuilder(Test);
qb.count().where({ $and: [{ id: { $nin: [3, 4] } }, { id: { $gt: 2 } }] });

console.log(qb.getQuery());
// select count(`e0`.`id`) from `test` as `e0` where (`e0`.`id` not in (?, ?) and `e0`.`id` > ?)

// to get the count, we can use `qb.execute()`
const res = await qb.execute('get');
const count = res ? +res.count : 0;

为了简化此过程，我们可以使用 qb.getCount() 方法。以下代码等效：

¥To simplify this process, we can use qb.getCount() method. Following code is equivalent:

const qb = em.createQueryBuilder(Test);
qb.select('*').limit(10, 20).where({ $and: [{ id: { $nin: [3, 4] } }, { id: { $gt: 2 } }] });

const count = await qb.getCount();

这还将从查询中删除任何现有的限制和偏移量（QB 将在后台克隆，因此调用 getCount() 不会改变原始 QB 状态）。

¥This will also remove any existing limit and offset from the query (the QB will be cloned under the hood, so calling getCount() does not mutate the original QB state).

分页

¥Pagination

如果我们想对 QueryBuilder 的结果进行分页，我们可以使用 qb.getResultAndCount() 方法。它返回一个有序元组，第一项是结果数组，第二项是项目总数，丢弃限制和偏移子句。

¥If we want to paginate the results of a QueryBuilder, we can use qb.getResultAndCount() method. It returns an ordered tuple, the first item being an array of results, and the second one being the total count of items, discarding the limit and offset clause.

const qb = em.createQueryBuilder(User);
qb.select('*')
  .where({ age: 18 })
  .limit(10);
const [results, count] = await qb.getResultAndCount();

console.log(results.length); // max 10, as we used the limit clause
console.log(count); // total count regardless limit and offset, e.g. 1327

覆盖 FROM 子句

¥Overriding FROM clause

你可以指定 FROM 子句中使用的表，如果已经指定了表名，则替换当前表名。这通常用于在 SQL 中指定子查询表达式。

¥You can specify the table used in the FROM clause, replacing the current table name if one has already been specified. This is typically used to specify a sub-query expression in SQL.

const qb = em.createQueryBuilder(Book2);
qb.select('*').from(Author2).where({ id: { $gt: 2 } });

console.log(qb.getQuery());
// select `e0`.* from `author2` as `e0` where `e0`.`id` > 2;

你还可以在 FROM 中使用子查询，如下所示：

¥You can also use sub-queries in the FROM like this:

const qb1 = em.createQueryBuilder(Book2).where({ id: { $lte: new Date() } }).orderBy({ id: 'DESC' }).limit(10);
const qb2 = em.createQueryBuilder(qb1.clone())
qb2.select('*').orderBy({ id: 'ASC' });

console.log(qb2.getQuery());
// select `e1`.* from (select `e0`.* from `book2` as `e0` where `e0`.`id` <= ? order by `e0`.`id` desc limit ?) as `e1` order by `e1`.`id`;

要设置别名以引用 SELECT 语句中的表，请传递第二个参数，如下所示：

¥To set up an alias to refer to a table in a SELECT statement, pass the second argument as follows:

const qb1 = em.createQueryBuilder(Book2, 'b1').where({ id: { $lte: new Date() } }).orderBy({ id: 'DESC' }).limit(10);
const qb2 = em.createQueryBuilder(qb1.clone(), 'b2')
qb2.select('*').orderBy({ id: 'ASC' });

console.log(qb2.getQuery());
// select `b2`.* from (select `b1`.* from `book2` as `b1` where `b1`.`id` <= ? order by `b1`.`id` desc limit ?) as `b2` order by `b2`.`id`;

使用子查询

¥Using sub-queries

你可以在 where 条件中使用子查询进行过滤：

¥You can filter using sub-queries in where conditions:

const qb1 = em.createQueryBuilder(Book2, 'b').select('b.author').where({ price: { $gt: 100 } });
const qb2 = em.createQueryBuilder(Author2, 'a').select('*').where({ id: { $in: qb1.getKnexQuery() } });

console.log(qb2.getQuery());
// select `a`.* from `author2` as `a` where `a`.`id` in (select `b`.`author_id` from `book2` as `b` where `b`.`price` > ?)

对于选择中的子查询，请使用 qb.as(alias) 方法：

¥For sub-queries in selects, use the qb.as(alias) method:

动态属性 (booksTotal) 需要在实体级别定义（作为 persist: false）。

¥The dynamic property (booksTotal) needs to be defined at the entity level (as persist: false).

const knex = em.getKnex();
const qb1 = em.createQueryBuilder(Book2, 'b').count('b.uuid', true).where({ author: knex.ref('a.id') }).as('Author2.booksTotal');
const qb2 = em.createQueryBuilder(Author2, 'a');
qb2.select(['*', qb1]).orderBy({ booksTotal: 'desc' });

console.log(qb2.getQuery());
// select `a`.*, (select count(distinct `b`.`uuid_pk`) as `count` from `book2` as `b` where `b`.`author_id` = `a`.`id`) as `books_total` from `author2` as `a` order by `books_total` desc

const knex = em.getKnex();
const qb3 = em.createQueryBuilder(Book2, 'b').count('b.uuid', true).where({ author: knex.ref('a.id') }).as('books_total');
const qb4 = em.createQueryBuilder(Author2, 'a');
qb4.select(['*', qb3]).orderBy({ booksTotal: 'desc' });

console.log(qb4.getQuery());
// select `a`.*, (select count(distinct `b`.`uuid_pk`) as `count` from `book2` as `b` where `b`.`author_id` = `a`.`id`) as `books_total` from `author2` as `a` order by `books_total` desc

当你想通过谓词左侧的子查询进行过滤时，你需要先通过 qb.withSubquery() 注册它：

¥When you want to filter by sub-query on the left-hand side of a predicate, you will need to register it first via qb.withSubquery():

动态属性 (booksTotal) 需要在实体级别定义（作为 persist: false）。你始终需要在 qb.withSchema() 中使用前缀（因此 a.booksTotal）。

¥The dynamic property (booksTotal) needs to be defined at the entity level (as persist: false). You always need to use prefix in the qb.withSchema() (so a.booksTotal).

const knex = em.getKnex();
const qb1 = em.createQueryBuilder(Book2, 'b').count('b.uuid', true).where({ author: knex.ref('a.id') }).getKnexQuery();
const qb2 = em.createQueryBuilder(Author2, 'a');
qb2.select('*').withSubQuery(qb1, 'a.booksTotal').where({ 'a.booksTotal': { $in: [1, 2, 3] } });

console.log(qb2.getQuery());
// select `a`.* from `author2` as `a` where (select count(distinct `b`.`uuid_pk`) as `count` from `book2` as `b` where `b`.`author_id` = `a`.`id`) in (?, ?, ?)

const knex = em.getKnex();
const qb3 = em.createQueryBuilder(Book2, 'b').count('b.uuid', true).where({ author: knex.ref('a.id') }).getKnexQuery();
const qb4 = em.createQueryBuilder(Author2, 'a');
qb4.select('*').withSubQuery(qb3, 'a.booksTotal').where({ 'a.booksTotal': 1 });

console.log(qb4.getQuery());
// select `a`.* from `author2` as `a` where (select count(distinct `b`.`uuid_pk`) as `count` from `book2` as `b` where `b`.`author_id` = `a`.`id`) = ?

在更新查询中引用列

¥Referring to column in update queries

你可以使用静态 raw() 助手插入原始 SQL 片段，如下所示：

¥You can use static raw() helper to insert raw SQL snippets like this:

const qb = em.createQueryBuilder(Book);
qb.update({ price: raw('price + 1') }).where({ uuid: '123' });

console.log(qb.getQuery());
// update `book` set `price` = price + 1 where `uuid_pk` = ?

锁定支持

¥Locking support

我们可以通过 qb.setLockMode() 设置 LockMode。

¥We can set the LockMode via qb.setLockMode().

const qb = em.createQueryBuilder(Test);
qb.select('*').where({ name: 'Lol 321' }).setLockMode(LockMode.PESSIMISTIC_READ);

console.log(qb.getQuery()); // for MySQL
// select `e0`.* from `test` as `e0` where `e0`.`name` = ? lock in share mode

可用的锁定模式：

¥Available lock modes:

模式	Postgres	MySQL
LockMode.PESSIMISTIC_READ	for share	lock in share mode
LockMode.PESSIMISTIC_WRITE	for update	for update
LockMode.PESSIMISTIC_PARTIAL_WRITE	for update skip locked	for update skip locked
LockMode.PESSIMISTIC_WRITE_OR_FAIL	for update nowait	for update nowait
LockMode.PESSIMISTIC_PARTIAL_READ	for share skip locked	lock in share mode skip locked
LockMode.PESSIMISTIC_READ_OR_FAIL	for share nowait	lock in share mode nowait

我们还可以选择通过第二个参数传递我们想要锁定的表别名列表：

¥Optionally we can also pass list of table aliases we want to lock via second parameter:

const qb = em.createQueryBuilder(User, 'u');
qb.select('*')
  .leftJoinAndSelect('u.identities', 'i')
  .where({ name: 'Jon' })
  .setLockMode(LockMode.PESSIMISTIC_READ, ['u']);

console.log(qb.getQuery()); // for Postgres
// select ...
//   from "user" as "u"
//   left join "identity" as "i" on "u"."id" = "i"."user_id"
//   where "u"."name" = 'Jon'
//   for update of "u" skip locked

使用 Knex.js

¥Using Knex.js

在底层，QueryBuilder 使用 Knex.js 来编写和运行查询。你可以通过 qb.getKnexQuery() 方法访问已配置的 knex 实例：

¥Under the hood, QueryBuilder uses Knex.js to compose and run queries. You can access configured knex instance via qb.getKnexQuery() method:

const qb = em.createQueryBuilder(Author);
qb.update({ name: 'test 123', type: PublisherType.GLOBAL }).where({ id: 123, type: PublisherType.LOCAL });
const knex = qb.getKnexQuery(); // instance of Knex' QueryBuilder

// do what ever you need with `knex`

const res = await em.getConnection().execute(knex);
const entities = res.map(a => em.map(Author, a));
console.log(entities); // Author[]

你还可以通过 getKnex() 方法从连接中获取清晰且配置好的 knex 实例。由于此方法在基本 Connection 类上不可用，你需要手动将连接类型转换为 AbstractSqlConnection（或你正在使用的实际实现，例如 MySqlConnection），或者为你的 EntityManager 实例提供正确的驱动程序类型提示，然后在 em.getConnection() 方法中自动推断。

¥You can also get clear and configured knex instance from the connection via getKnex() method. As this method is not available on the base Connection class, you will need to either manually type cast the connection to AbstractSqlConnection (or the actual implementation you are using, e.g. MySqlConnection), or provide correct driver type hint to your EntityManager instance, which will be then automatically inferred in em.getConnection() method.

驱动程序和连接实现不直接从 @mikro-orm/core 模块导出。你可以从驱动程序包（例如 import { PostgreSqlDriver } from '@mikro-orm/postgresql'）导入它们。

¥Driver and connection implementations are not directly exported from @mikro-orm/core module. You can import them from the driver packages (e.g. import { PostgreSqlDriver } from '@mikro-orm/postgresql').

const conn = em.getConnection() as AbstractSqlConnection;
// you can make sure the `em` is correctly typed to `EntityManager<AbstractSqlDriver>`
// or one of its implementations:
// const em: EntityManager<AbstractSqlDriver> = em;

const knex = conn.getKnex();

// do what ever you need with `knex`

const res = await knex;

运行原生 SQL 查询

¥Running Native SQL Query

你可以通过底层连接运行原生 SQL

¥You can run native SQL via underlying connection

const connection = em.getConnection();
const res = await connection.execute('select 1 as count');
console.log(res); // res is array of objects: `[ { count: 1 } ]`

自 v4 以来，不再需要手动安装 em.execute() 包。

¥Since v4 we can also use em.execute() which will also handle logging and mapping of exceptions.