外部表

概述

¥Overview

Prisma ORM 中的外部管理表（简称外部表）可以通过 Prisma 客户端查询，但会被 Prisma Migrate 忽略。

¥Externally managed tables (or external tables for short) in Prisma ORM are tables that can be queried via Prisma Client but are ignored by Prisma Migrate.

有时，你可能不希望 Prisma ORM 管理特定的表，例如由其他团队或服务处理的表。

¥Sometimes, you might not want Prisma ORM to manage specific tables—such as ones handled by another team or service.

一些具体的用例如下：

¥Some concrete use cases for this are:

使用 Clerk 或 Auth0 等身份验证服务来管理包含用户和会话数据的特定表

¥auth services like Clerk or Auth0 that manage specific tables with user and session data
存储服务，例如 Supabase Storage，其中包含用于存储存储桶和对象元数据的表

¥storage services like Supabase Storage with tables for storing metadata about buckets and objects
基于微服务的组织，特定团队负责数据库中的特定表

¥a microservice-based organization where specific teams own specific tables in the database

根据自定义组织约束或偏好，可能还有许多其他场景，你可能不希望 Prisma ORM 管理特定的表。

¥There may be many other scenarios based on custom organizational constraints or preferences where you may not want Prisma ORM to manage specific tables.

警告

外部管理表目前位于预览中。

¥Externally managed tables are currently in Preview.

注意

外部管理表通常与 multi-schema 数据库设置结合使用。但是，这并非硬性要求。你的数据库中只能有一个模式，并且还可以在其中声明外部管理的表。

¥Externally managed tables are frequently used in combination with multi-schema database setups. However, this is not a hard requirement. You can have only a single schema in your database and also declare externally managed tables within it.

警告

Prisma ORM 不会验证数据库中表的结构与 Prisma 模型的结构是否真正匹配。一方面，它要求开发者在更新 Prisma 模式时非常彻底（最安全的方法是使用 prisma db pull）。On the other hand, this flexibility enables you to represent only part of the underlying table in the database (and e.g. not expose all its columns).

¥Prisma ORM will not verify that the structure of the tables in the database and the structures of the Prisma models actually match. On the one hand, it requires the developer to be thorough when updating the Prisma schema (the safest way to do it is by using prisma db pull). On the other hand, this flexibility enables you to represent only part of the underlying table in the database (and e.g. not expose all its columns).

工作流程

¥Workflow

如果你想使用外部表，以下是主要工作流程：

¥If you want to use external tables, here's the main workflow:

在 Prisma 配置文件中声明外部表的名称

¥Declare the name of the external tables in your Prisma Config file
更新你的 Prisma 模式（例如，通过 npx prisma db pull）

¥Update your Prisma schema (e.g. via npx prisma db pull)
使用 npx prisma generate 重新生成 Prisma 客户端

¥Re-generate Prisma Client with npx prisma generate
现在，你可以使用 Prisma 客户端查询外部表，但 Prisma Migrate 会忽略它。

¥You can now query the external table using Prisma Client but it will be ignored by Prisma Migrate
当表被更改时（由其所有者更改）：

¥When the table gets changed (by whoever owns it):
1. 使用 npx prisma db pull 重新检查数据库或手动更新 Prisma 文件中的模型
  
  ¥Re-introspect your database using npx prisma db pull or manually update the models in your prisma file
2. 使用 npx prisma generate 重新生成 Prisma 客户端
  
  ¥Re-generate Prisma Client with npx prisma generate

Prisma 配置语法

¥Prisma Config syntax

你可以通过 tables.external 属性在 Prisma Config 文件中指定外部管理表：

¥You can specify externally managed tables in your Prisma Config file via the tables.external property:

prisma.config.ts

export default defineConfig({
  // required when using unstable features
  experimental: {
    externalTables: true
  },
  // declare the `users` table and `role` enum as external
  tables: {
    external: [
      "public.users",
    ]
  },
  enums: {
    external: [
      "public.role",
    ]
  },
})

Analogous to tables, you can also have externally managed enums.
On PostgreSQL and SQL Server you have to specify the fully qualified table/enum name including the schema name.例如：public.products 或 auth.users。

¥On PostgreSQL and SQL Server you have to specify the fully qualified table/enum name including the schema name. For example: public.products or auth.users.
在 MySQL 和 SQLite 上，你只需指定表名。

¥On MySQL and SQLite, you only have to specify the table name.

关系

¥Relationships

Prisma 可以创建和更新其管理的表与外部管理表之间的关系。

¥Prisma can create and update relationships from tables it manages to externally managed tables.

但是，为此，Prisma 需要在创建迁移时了解这些外部管理表的结构。你可以提供一个 SQL 脚本，Prisma 将在所有迁移之前在其影子数据库上运行该脚本，以在迁移创建期间模拟外部表和枚举。

¥However, for this Prisma needs to be aware of the structure of those externally managed tables during migration creation. You can provide a SQL script that Prisma will run on its shadow database ahead of all migrations to emulate the external tables and enums during migration creation.

创建的占位符表不需要具有实际表的完整结构，但需要存在主键。

¥The created placeholder table does not need to have the full structure of the actual table but primary keys need to be present.

如果外部表未被任何托管表引用（即没有托管表包含外部表的外键约束），则无需在 migrations.initShadowDb 中为其提供任何 SQL。

¥If the external table is not referenced by any managed table—that is no managed table contains a foreign key constraint on the external table—you do NOT need to provide any SQL for it in migrations.initShadowDb.

prisma.config.ts

export default defineConfig({
  // required when using unstable features
  experimental: {
    externalTables: true
  },
  // declare a `users` table
  tables: {
    external: [
      "public.users",
    ]
  },
  migrations: {
    // setup the users table for the shadow database
    initShadowDb: `
      CREATE TABLE public.users (id SERIAL PRIMARY KEY);
    `
  },
})

从外部表到托管表的关系（其中外部表包含托管表上的外键约束）不受 Prisma 管理，因为这会修改外部表。

¥Relationships from an external table to a managed table, where the external table contains the foreign key constraint on the managed table, are NOT managed by Prisma as that would modify the external table.

示例

¥Example

假设你有以下 Prisma 模式，其中仅包含 posts 表：

¥Assume you have the following Prisma schema which only contains the posts table:

generator client {
  provider = "prisma-client-js"
  // ...
}

datasource db {
  provider = "postgresql"
  // ...
}

model posts {
  id          Int       @id @default(autoincrement())
  created_at  DateTime  @default(now())
  title       String
  content     String?
}

你已经通过之前的迁移创建了 posts 表。现在，你的数据库中还有一个 users 表和 role 枚举，你希望将其视为外部管理的。

¥You have created that posts table already via a prior migration. You now also have a users table and role enum in your database which you want to treat as externally managed.

因此，在默认 public 架构中，PostgreSQL 数据库中的表如下所示：

¥So the tables in your PostgreSQL database in the default public schema look like this:

-- Enum used by users table
CREATE TYPE role AS ENUM ('customer', 'support', 'admin');

-- Users table
CREATE TABLE users (
  id SERIAL PRIMARY KEY,
  username VARCHAR(50) NOT NULL UNIQUE,
  email VARCHAR(100) NOT NULL UNIQUE,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  role role
);

-- Posts table
CREATE TABLE posts (
  id SERIAL PRIMARY KEY,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  title VARCHAR(200) NOT NULL,
  content TEXT
);

1. 在 Prisma Config 中声明外部管理表

¥ Declaring externally managed tables in Prisma Config

通过 tables.external 属性启用外部管理表：

¥Enable use of externally managed tables via the tables.external property:

prisma.config.ts

export default defineConfig({
  experimental: {
    externalTables: true
  },
  // declare the `users` table and `role` enum as external
  tables: {
    external: [
      "public.users",
    ]
  },
  enums: {
    external: [
      "public.role",
    ]
  },
})

2. 更新 Prisma 架构

¥ Update the Prisma schema

接下来，你需要更新 Prisma 模式。你可以执行以下任一操作：

¥Next, you need to update your Prisma schema. You can do this either:

通过手动创建模型

¥by manually creating the models
或使用 introspection：

¥or by using introspection:

npx prisma db pull

users 表现在位于你的 Prisma 模式中：

¥The users table is now in your Prisma schema:

model posts {
  id         Int       @id @default(autoincrement())
  created_at DateTime? @default(now()) @db.Timestamp(6)
  title      String    @db.VarChar(200)
  content    String?
}

model users {
  id         Int       @id @default(autoincrement())
  username   String    @unique @db.VarChar(50)
  email      String    @unique @db.VarChar(100)
  created_at DateTime? @default(now()) @db.Timestamp(6)
  role       role
}

enum role {
  customer
  support
  admin
}

3. 重新生成 Prisma 客户端

¥ Re-generate Prisma Client

为了能够查询 users 表，你需要重新生成 Prisma 客户端：

¥In order to be able to query the users table, you need to re-generate Prisma Client:

npx prisma generate

4. 使用 Prisma 客户端查询 `users` 表

¥ Query the users table using Prisma Client

现在，你可以使用 Prisma 客户端查询外部 users 表：

¥You can now query the external users table with Prisma Client:

await prisma.users.findMany()

5. 添加关系

¥ Add a relationship

假设你现在想要添加从 posts 到 users 的作者关系。

¥Let's say you now want to add an author relationship from posts onto users.

首先更新 Prisma 模式。

¥First update your Prisma schema.

model posts {
  id         Int       @id @default(autoincrement())
  created_at DateTime? @default(now()) @db.Timestamp(6)
  title      String    @db.VarChar(200)
  content    String?
  author     users @relation(fields: [author_id], references: [id])
  author_id  Int
}

model users {
  id         Int       @id @default(autoincrement())
  username   String    @unique @db.VarChar(50)
  email      String    @unique @db.VarChar(100)
  created_at DateTime? @default(now()) @db.Timestamp(6)
  role       role
  posts      posts[]
}

enum role {
  customer
  support
  admin
}

然后添加一个 migrations.initShadowDb 脚本，以便 Prisma 在迁移期间了解 users 表。

¥Then add a migrations.initShadowDb script so Prisma knows about the users table during migrations.

// prisma.config.ts
export default defineConfig({
  // ...
  migrations: {
    // setup the users table for the shadow database
    initShadowDb: `
      CREATE TABLE public.users (id SERIAL PRIMARY KEY);
    `
  },
})

现在你可以运行 prisma migrate dev 命令了。

¥Now you can run prisma migrate dev command.

概述​

工作流程​

Prisma 配置语法​

关系​

示例​

1. 在 Prisma Config 中声明外部管理表​

2. 更新 Prisma 架构​

3. 重新生成 Prisma 客户端​

4. 使用 Prisma 客户端查询 users 表​

5. 添加关系​

概述

工作流程

Prisma 配置语法

关系

示例

1. 在 Prisma Config 中声明外部管理表

2. 更新 Prisma 架构

3. 重新生成 Prisma 客户端

4. 使用 Prisma 客户端查询 `users` 表

5. 添加关系