Skip to main content
Version: 6.4

与 MySQL、MariaDB、PostgreSQL 或 SQLite 一起使用

要将 mikro-orm 与 MySQL 数据库一起使用，请安装 @mikro-orm/mysql 依赖并在初始化 ORM 时将类型选项设置为 mysql。自 v3 以来，多对多集合不需要具有自动递增主键，该主键用于确保集合项的固定顺序。

¥To use mikro-orm with MySQL database, install the @mikro-orm/mysql dependency and set the type option to mysql when initializing ORM. Since v4 it is no longer needed to install the mysql2 package manually.

# for mongodb
npm install @mikro-orm/core @mikro-orm/mongodb

# for mysql (works with mariadb too)
npm install @mikro-orm/core @mikro-orm/mysql

# for mariadb (works with mysql too)
npm install @mikro-orm/core @mikro-orm/mariadb

# for postgresql (works with cockroachdb too)
npm install @mikro-orm/core @mikro-orm/postgresql

# for sqlite
npm install @mikro-orm/core @mikro-orm/sqlite

# for better-sqlite
npm install @mikro-orm/core @mikro-orm/better-sqlite

# for libsql/turso
npm install @mikro-orm/core @mikro-orm/libsql

# for mssql
npm install @mikro-orm/core @mikro-orm/mssql

然后在引导应用时调用 MikroORM.init：

¥Then call MikroORM.init as part of bootstrapping your app:

要访问驱动程序特定方法（如 em.createQueryBuilder()），你需要从驱动程序包中导入 MikroORM/EntityManager/EntityRepository 类。或者，你可以将 orm.em 转换为从驱动程序包导出的 EntityManager：

¥To access driver specific methods like em.createQueryBuilder() you need to import the MikroORM/EntityManager/EntityRepository class from the driver package. Alternatively you can cast the orm.em to EntityManager exported from the driver package:

import { EntityManager } from '@mikro-orm/postgresql';
const em = orm.em as EntityManager;
const qb = em.createQueryBuilder(...);
import { MikroORM } from '@mikro-orm/postgresql'; // or any other SQL driver package

const orm = await MikroORM.init({
  entities: ['./dist/entities'], // path to your JS entities (dist), relative to `baseDir`
  dbName: 'my-db-name',
});
console.log(orm.em); // access EntityManager via `em` property

自定义驱动程序​

¥Custom driver

如果你想使用当前不支持的数据库，你可以实现自己的驱动程序。有关如何创建集合的更多信息，请参阅 在此处找到。然后通过 driver 配置选项提供驱动程序类：

¥If you want to use database that is not currently supported, you can implement your own driver. More information about how to create one can be found here. Then provide the driver class via driver configuration option:

import { MyCustomDriver } from './MyCustomDriver.ts';

const orm = await MikroORM.init({
  entities: [Author, Book, ...],
  dbName: 'my-db-name',
  driver: MyCustomDriver, // provide the class, not just its name
});

模式​

¥Schema

目前，你需要自己维护数据库架构。对于初始转储，你可以使用 SchemaGenerator 助手。

¥Currently, you will need to maintain the database schema yourself. For initial dump, you can use SchemaGenerator helper.

带有枢轴表的多对多集合​

¥ManyToMany collections with pivot tables

与 MongoDriver 相反，在 MySQL 中我们使用数据透视表来处理 ManyToMany 关系：

¥As opposed to MongoDriver, in MySQL we use pivot tables to handle ManyToMany relations:

CREATE TABLE `publisher_to_test` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `publisher_id` int(11) DEFAULT NULL,
  `test_id` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

你可以通过在拥有方定义的 @ManyToMany 装饰器中的 pivotTable 选项调整数据透视表的名称：

¥You can adjust the name of pivot table via pivotTable option in @ManyToMany decorator defined on owning side:

// for unidirectional
@ManyToMany({ entity: () => Test, owner: true, pivotTable: 'publisher2test' })
tests = new Collection<Test>(this);

// for bidirectional
@ManyToMany({ entity: () => BookTag, inversedBy: 'books', pivotTable: 'book2tag' })
tags = new Collection<BookTag>(this);

使用 QueryBuilder 执行原生 SQL 查询​

¥Using QueryBuilder to execute native SQL queries

当你需要执行一些不涉及所有 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:

const qb = orm.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();

// or run query without using QueryBuilder
const driver = orm.em.getDriver();
const res2 = await driver.execute('SELECT ? + ?', [1, 2]);

QueryBuilder 提供流畅的接口，具有以下方法：

¥QueryBuilder provides fluent interface with these methods:

QueryBuilder.select(fields: string | string[], distinct?: boolean): QueryBuilder;
QueryBuilder.insert(data: any): QueryBuilder;
QueryBuilder.update(data: any): QueryBuilder;
QueryBuilder.delete(cond: any): QueryBuilder;
QueryBuilder.count(fields: string | string[], distinct?: boolean): QueryBuilder;
QueryBuilder.join(field: string, alias?: string): QueryBuilder;
QueryBuilder.leftJoin(field: string, alias?: string): QueryBuilder;
QueryBuilder.where(cond: any, operator: '$and' | '$or'): QueryBuilder;
QueryBuilder.andWhere(cond: any): QueryBuilder;
QueryBuilder.orWhere(cond: any): QueryBuilder;
QueryBuilder.groupBy(fields: string | string[]): QueryBuilder;
QueryBuilder.having(cond: any): QueryBuilder;
QueryBuilder.populate(populate: string[]): QueryBuilder;
QueryBuilder.limit(limit: number, offset?: number): QueryBuilder;
QueryBuilder.offset(offset: number): QueryBuilder;
QueryBuilder.getQuery(): string;
QueryBuilder.getParams(): any;
QueryBuilder.clone(): QueryBuilder;

有关如何使用 QueryBuilder 的更多示例，请查看 tests/QueryBuilder.test.ts 中的 QueryBuilder 测试。

¥For more examples of how to work with QueryBuilder, take a look at QueryBuilder tests in tests/QueryBuilder.test.ts.

事务​

¥Transactions

当你调用 em.flush() 时，默认情况下会查询所有计算的更改 数据库事务内部，因此你不必手动处理事务。

¥When you call em.flush(), all computed changes are queried inside a database transaction by default, so you do not have to handle transactions manually.

当你需要明确处理事务时，你可以使用 em.transactional(cb) 在事务中运行回调。它将提供分叉的 EntityManager 作为参数，并带有清晰的隔离身份映射 - 请使用它进行更改。

¥When you need to explicitly handle the transaction, you can use em.transactional(cb) to run callback in transaction. It will provide forked EntityManager as a parameter with clear isolated identity map - please use that to make changes.

// if an error occurs inside the callback, all db queries from inside the callback will be rolled back
await orm.em.transactional(async (em: EntityManager) => {
  const god = new Author('God', 'hello@heaven.god');
  await em.persist(god).flush();
});

LIKE 查询​

¥LIKE Queries

SQL 通过原生 JS 正则表达式支持 LIKE 查询：

¥SQL supports LIKE queries via native JS regular expressions:

const author1 = new Author2('Author 1', 'a1@example.com');
const author2 = new Author2('Author 2', 'a2@example.com');
const author3 = new Author2('Author 3', 'a3@example.com');

await orm.em.persist([author1, author2, author3]).flush();

// finds authors with email like '%exa%le.c_m'
const authors = await orm.em.find(Author2, { email: /exa.*le\.c.m$/ });
console.log(authors); // all 3 authors found

原生集合方法​

¥Native Collection Methods

有时你需要执行一些批量操作，或者你只想用初始装置填充数据库。使用 ORM 进行此类操作可能会带来不必要的样板代码。在这种情况下，你可以使用 insert/nativeUpdate/nativeDelete 方法之一：

¥Sometimes you need to perform some bulk operation, or you just want to populate your database with initial fixtures. Using ORM for such operations can bring unnecessary boilerplate code. In this case, you can use one of insert/nativeUpdate/nativeDelete methods:

em.insert<T extends AnyEntity>(entityName: string, data: any): Promise<IPrimaryKey>;
em.nativeUpdate<T extends AnyEntity>(entityName: string, where: FilterQuery<T>, data: any): Promise<number>;
em.nativeDelete<T extends AnyEntity>(entityName: string, where: FilterQuery<T> | any): Promise<number>;

这些方法根据实体元数据执行通过 QueryBuilder 生成的原生 SQL 查询。请记住，它们不会将结果水合到实体中，也不会触发生命周期钩子。

¥Those methods execute native SQL queries generated via QueryBuilder based on entity metadata. Keep in mind that they do not hydrate results to entities, and they do not trigger lifecycle hooks.

它们也可用作 EntityRepository 快捷方式：

¥They are also available as EntityRepository shortcuts:

EntityRepository.insert(data: any): Promise<IPrimaryKey>;
EntityRepository.nativeUpdate(where: FilterQuery<T>, data: any): Promise<number>;
EntityRepository.nativeDelete(where: FilterQuery<T> | any): Promise<number>;

此外，还有 execute() 方法支持执行原始 SQL 查询或 QueryBuilder 实例。要创建 QueryBuilder，你可以在 EntityManager 和 EntityRepository 类上使用 createQueryBuilder() 工厂方法：

¥Additionally, there is execute() method that supports executing raw SQL queries or QueryBuilder instances. To create QueryBuilder, you can use createQueryBuilder() factory method on both EntityManager and EntityRepository classes:

const qb = em.createQueryBuilder('Author');
qb.select('*').where({ id: { $in: [...] } });
const res = await em.getDriver().execute(qb);
console.log(res); // unprocessed result of underlying database driver

使用 SQLite 扩展​

¥Using SQLite extensions

像 sqlean 这样的 SQLite 扩展可以添加许多默认情况下明显缺失的有用功能（例如正则表达式）。

¥SQLite extensions like sqlean can add many useful features that are notably missing by default (e.g. regexp).

下载要使用的扩展的二进制文件后，可以通过在 SQLite 初始化选项中提供 pool.afterCreate 处理程序来添加它们。处理程序应在底层数据库连接上调用 loadExtension，将路径传递给扩展二进制文件：

¥Once you've downloaded the binaries for the extensions you wish to use, they can be added by providing a pool.afterCreate handler in the SQLite initialization options. The handler should call loadExtension on the underlying database connection, passing the path to the extension binary:

const orm = await MikroORM.init({
  // ...
  pool: {
    afterCreate: (conn: any, done: any) => {
      conn.loadExtension('/.../sqlean-macos-arm64/sqlean');
      done(null, conn);
    },
  },
});

使用 Turso 数据库​

¥Using Turso database

要能够连接到远程 Turso 数据库，你需要使用 @mikro-orm/libsql 驱动程序。使用 password 选项设置 authToken。

¥To be able to connect to a remote Turso database, you need to use the @mikro-orm/libsql driver. Use the password option to set the authToken.

import { defineConfig } from '@mikro-orm/libsql';

export default defineConfig({
  dbName: process.env.LIBSQL_URL,
  password: process.env.LIBSQL_AUTH_TOKEN,
});

要设置其他选项（如 syncUrl 或 syncPeriod），请使用 driverOptions.connection：

¥To set the additional options like syncUrl or syncPeriod, use the driverOptions.connection:

import { defineConfig } from '@mikro-orm/libsql';

export default defineConfig({
  dbName: 'local.db',
  password: process.env.LIBSQL_AUTH_TOKEN,
  driverOptions: {
    connection: {
      syncUrl: process.env.LIBSQL_URL,
      syncPeriod: 0.5, // 500ms
    },
  },
});

MS SQL Server 限制​

¥MS SQL Server limitations

  • UUID 值以大写形式返回

    ¥UUID values are returned in upper case

  • 不支持级联路径中的循环

    ¥cycles in cascade paths are not supported

  • 模式差异功能有限

    ¥schema diffing capabilities are limited

  • 不支持全文搜索的原生支持

    ¥no native support for fulltext search

  • upsert 支持有限

    ¥upsert support is limited