声明式数据库模式

概览 #

🌐 Overview

声明式模式为开发者提供了一种维护模式迁移的友好方式。

🌐 Declarative schemas provide a developer-friendly way to maintain schema migrations.

迁移 传统上是用命令式方式管理的（你需要提供具体怎样更改数据库的指令）。这可能会导致相关信息分散在多个迁移文件中。使用声明式模式，你只需要声明你希望数据库达到的状态，具体的操作指令会帮你生成。

因为 schema 文件是真实来源，所以每次修改都要通过编辑它们来完成，而不是通过 Studio 或 SQL 编辑器。迁移是通过 supabase db schema declarative sync 从你的 schema 文件生成的，它会将文件与你的迁移历史进行比较，而不是实时数据库。直接对数据库做的修改不会被捕捉到。

🌐 Because the schema files are the source of truth, make every change by editing them, not through Studio or the SQL editor. Migrations are generated from your schema files with supabase db schema declarative sync, which compares the files against your migration history, not the live database. Changes made directly to the database are not picked up.

模式迁移 #

🌐 Schema migrations

架构迁移是用数据定义语言编写的 SQL 语句。它们在你的 supabase/migrations 目录中有版本控制，以确保本地和远程环境之间的架构一致性。

🌐 Schema migrations are SQL statements written in Data Definition Language. They are versioned in your supabase/migrations directory to ensure schema consistency between local and remote environments.

声明你的模式 #

🌐 Declaring your schema

1
create table "employees" (
2
  "id" integer not null,
3
  "name" text
4
);

1
supabase db schema declarative sync -f create_employees_table --no-apply

1
supabase start
2
supabase migration up

正在更新你的模式 #

🌐 Updating your schema

1
create table "employees" (
2
  "id" integer not null,
3
  "name" text,
4
  "age" smallint not null
5
);

1
supabase db schema declarative sync -f add_age --no-apply

1
alter table "public"."employees" add column "age" smallint not null;

1
supabase migration up

部署你的模式更改 #

🌐 Deploying your schema changes

1
supabase login

1
supabase link

1
supabase db push

管理依赖 #

🌐 Managing dependencies

随着你的数据库架构不断发展，你可能会开始使用更多高级实体，比如视图和函数。使用普通迁移来管理这些实体 notoriously 冗长，因为每次有变化都必须重新创建整个主体。使用声明式架构，现在你可以直接在原地编辑它们，这样审核起来就容易得多。

🌐 As your database schema evolves, you will probably start using more advanced entities like views and functions. These entities are notoriously verbose to manage using plain migrations because the entire body must be recreated whenever there is a change. Using declarative schema, you can now edit them in-place so it’s much easier to review.

1
create table "employees" (
2
  "id" integer not null,
3
  "name" text,
4
  "age" smallint not null
5
);
6
7
create view "profiles" as
8
  select id, name from "employees";
9
10
create function "get_age"(employee_id integer) RETURNS smallint
11
  LANGUAGE "sql"
12
AS $$
13
  select age
14
  from employees
15
  where id = employee_id;
16
$$;

你不需要手动排序你的模式文件。生成迁移时，引擎会分析你的语句之间的依赖，比如外键、基于表的视图以及触发器使用的函数，并自动排序。文件名和目录布局只是为了可读性。如果你的语句中存在依赖循环，命令会以诊断方式失败，而不会生成损坏的迁移。

🌐 You don't need to order your schema files manually. When generating a migration, the engine analyzes the dependencies between your statements, such as foreign keys, views over tables, and functions used by triggers, and orders them automatically. File names and directory layout are for readability only. If your statements contain a dependency cycle, the command fails with a diagnostic instead of producing a broken migration.

这意味着你可以随意组织 supabase/schemas/。例如，每个表一个文件：

🌐 This means you can organize supabase/schemas/ however you like. For example, one file per table:

1
.
2
└── supabase/
3
    ├── schemas/
4
    │   ├── employees.sql
5
    │   └── managers.sql
6
    └── migrations/
7
        ├── 20241004112233_create_employees_table.sql
8
        ├── 20241005112233_add_employee_age.sql
9
        └── 20241006112233_add_managers_table.sql

或者使用 supabase db schema declarative generate 生成的每个模式布局，每个数据库模式（比如 supabase/schemas/public/tables/employees.sql）一个目录，集群级对象如角色放在保留的 _cluster/ 目录下。你也可以为手写 SQL 创建一个保留的 _custom/ 目录，用来覆盖引擎不跟踪的对象。generate 不会创建它，一旦存在，导出也不会写入或修剪它。详见 已知问题。

🌐 Or the per-schema layout that supabase db schema declarative generate produces, with one directory per database schema (such as supabase/schemas/public/tables/employees.sql) and cluster-level objects like roles under a reserved _cluster/ directory. You can also create a reserved _custom/ directory for hand-authored SQL covering objects the engine doesn't track. generate doesn't create it, and once it exists the export never writes to it or prunes it. See Known caveats.

拉取你的生产环境架构 #

🌐 Pulling in your production schema

要在现有项目上设置声明式模式，请将你的生产模式导出为声明式文件：

🌐 To set up declarative schemas on an existing project, export your production schema into declarative files:

1
supabase db schema declarative generate --linked

这会在 supabase/schemas/（或由 experimental.pgdelta.declarative_schema_path 设置的目录）下为每个对象写入 SQL 文件，并按数据库 schema 组织。在脚本中，明确传入目标（--local、--linked 或 --db-url），并加上 --overwrite 来替换现有树而不提示。之后如果想从远程数据库刷新声明式树（例如，在本地工作流程之外部署了更改后），运行 supabase db pull --declarative。它会替换 schema 文件，而不会创建迁移或修改迁移历史。

🌐 This writes per-object SQL files under supabase/schemas/ (or the directory set by experimental.pgdelta.declarative_schema_path), organized by database schema. In scripts, pass the target explicitly (--local, --linked, or --db-url) and add --overwrite to replace an existing tree without prompting. To later refresh the declarative tree from the remote database (for example, after a change was deployed outside your local workflow), run supabase db pull --declarative. It replaces the schema files without creating a migration or touching migration history.

generate 只会写 schema 文件。因为 sync 会把你的 schema 文件和迁移历史做对比，所以那段历史必须描述你第一次 sync 之前的同一个数据库。如果你的项目还没有迁移，先运行 supabase db pull 来创建一个基准迁移。没有基准的话，第一次 sync 会把整个 schema 重新生成成一个迁移。这个迁移可以顺利应用到一个空的本地数据库，但在 db push 上可能还会失败，因为远程数据库已经有那些对象了。

回滚模式更改 #

🌐 Rolling back a schema change

在开发过程中，你可能想回滚一次迁移，以便将新的模式更改保存在一个迁移文件中。这可以通过将本地数据库重置到之前的版本来完成。

🌐 During development, you may want to rollback a migration to keep your new schema changes in a single migration file. This can be done by resetting your local database to a previous version.

1
supabase db reset --version 20241005112233

重置后，你可以编辑模式并重新生成新的迁移文件。注意，不要重置已经部署到生产环境的版本。

🌐 After a reset, you can edit the schema and regenerate a new migration file. Note that you should not reset a version that's already deployed to production.

如果你需要回滚已经部署的迁移，首先应该恢复对模式文件的更改。然后你可以生成一个包含向下迁移的新迁移文件。这可以确保你的生产迁移总是向前滚动。

🌐 If you need to rollback a migration that's already deployed, you should first revert changes to the schema files. Then you can generate a new migration file containing the down migration. This ensures your production migrations are always rolling forward.

已知问题 #

🌐 Known caveats

模式差异由 pg-delta 生成，它可以建模大多数数据库实体，包括表、视图、物化视图、函数、触发器、RLS 策略、授权、评论、域、分区和发布。不过，它仍有一些情况无法捕捉。提交前请务必检查每一个生成的迁移。

🌐 Schema diffs are generated by pg-delta, which models most database entities, including tables, views, materialized views, functions, triggers, RLS policies, grants, comments, domains, partitions, and publications. There are still cases it cannot capture. Review every generated migration before committing.

数据操作语言 #

🌐 Data manipulation language

像 insert、update 和 delete 这样的 DML 语句永远不会被模式差异捕获。这也包括存储桶，它们在 storage.buckets 表中是行，而不是模式对象。在声明式模式文件中出现 DML 语句是错误的。数据更改请保存在 seed 文件 或手写的 版本迁移 中。

🌐 DML statements such as insert, update, and delete are never captured by a schema diff. This includes storage buckets, which are rows in the storage.buckets table rather than schema objects. A DML statement inside a declarative schema file is an error. Keep data changes in seed files or hand-written versioned migrations.

新对象的默认权限 #

🌐 Default privileges on new objects

两个引擎都把权限当作模式状态的一部分来处理。当你创建新对象时，生成的迁移可能会包含你没写的 GRANT/REVOKE 语句，这是反映默认权限的。如果你没自定义权限，这些行可以放心删除。

🌐 Both engines treat permissions as part of the schema state. When you create a new object, generated migrations can include GRANT/REVOKE statements you didn't write, reflecting default privileges. If you haven't customized permissions, these lines are safe to remove.

未被跟踪的对象类型 #

🌐 Object kinds that aren't tracked

有些对象类型不会被引擎跟踪：类型转换、操作符、操作符类和族、文本搜索配置、字典、解析器和模板、统计对象、语言、转换以及参数 ACL。它们从不会被悄悄丢弃。引擎会将它们报告为警告，你可以传入 --strict-coverage 将这些警告变成严重错误（在持续集成中很有用）。

🌐 Some object kinds are not tracked by the engine: casts, operators, operator classes and families, text search configurations, dictionaries, parsers, and templates, statistics objects, languages, transforms, and parameter ACLs. They are never silently dropped. The engine reports them as warnings, and you can pass --strict-coverage to turn those warnings into hard failures (useful in CI).

要在声明式模式下使用这些对象，如果 supabase/schemas/_custom/ 目录不存在就创建它，并把它们的 SQL 放进去，这样依赖的对象仍然可以解析。generate 从不会创建、覆盖或清理那个目录。变更本身要通过版本化迁移交付，并确保该迁移排在依赖对象的生成迁移之前。否则 db reset 和后面的每个 sync 都会失败。参数 ACL 是个例外，因为它们存在于一个集群中每个数据库共享的目录里。不要把它们放到 _custom/，只能通过版本化迁移来管理。

🌐 To use these objects with declarative schemas, create the reserved supabase/schemas/_custom/ directory if it doesn't exist and put their SQL there so dependent objects still resolve. generate never creates, overwrites, or prunes that directory. Deliver the change itself through a versioned migration, and make sure that migration sorts before the generated migration that depends on the object. Otherwise db reset and every later sync fail. Parameter ACLs are the exception because they live in a catalog shared by every database in the cluster. Keep them out of _custom/ and manage them through versioned migrations only.

Supabase 管理的模式 #

🌐 Supabase-managed schemas

在 pg-delta 引擎下，diff 会排除 Supabase 管理的 schema 中的平台对象，比如 auth 和 storage。它会捕捉你在这些对象上的自定义设置，比如管理表上的触发器、auth 中任何表的 RLS 策略，以及 storage.objects、storage.buckets 和 realtime.messages 上的 RLS 策略。只有当触发器的函数位于管理的 schema 之外时，它才算是你的触发器（例如，auth.users 上的触发器调用 public 中的函数）。如果触发器的函数位于 auth 或 storage 中，即使你创建了这个函数，它也会被排除。diff 还会跳过你在这些 schema 中创建的其他对象，比如你自己的函数或索引。

🌐 Under the pg-delta engine, the diff excludes platform objects in Supabase-managed schemas such as auth and storage. It captures your customizations on top of them, such as triggers on managed tables, RLS policies on any table in auth, and RLS policies on storage.objects, storage.buckets, and realtime.messages. A trigger counts as yours only when its function lives outside the managed schemas (for example, a trigger on auth.users calling a function in public). A trigger whose function lives inside auth or storage is excluded, even if you created the function. The diff also skips other objects you create inside these schemas, such as your own functions or indexes.

通过版本化迁移来管理这些对象。

🌐 Manage those objects through versioned migrations.

扩展管理的对象 #

🌐 Extension-managed objects

属于扩展的对象会被认作是扩展的，而不是你的。扩展拥有的任何东西都会从差异中排除。扩展在运行时创建的对象，比如 pg_partman 维护的分区和 pgmq 创建的队列表，从不以原始的 create table 或 drop table 语句输出。差异会通过扩展自身的 API 表达这些对象的变化，比如 select pgmq.drop_queue('q'); 或 delete from partman.part_config 行，而且 CLI 会将这些语句标记为破坏性。在扩展的功能中创建和修改这些对象，并在提交之前检查生成的 API 调用。

🌐 Objects that belong to an extension are recognized as the extension's, not yours. Anything an extension owns is excluded from diffs. Objects extensions create as they run, such as partitions maintained by pg_partman and queue tables created by pgmq, are never emitted as raw create table or drop table statements. The diff expresses changes to them through the extension's own API instead, such as select pgmq.drop_queue('q'); or a delete from partman.part_config row, and the CLI flags those statements as destructive. Create and change these objects through the extension's own functions, and review any generated API calls before committing.

采用现有的模式树 #

🌐 Adopting an existing schema tree

CLI 没有生成的模式树上对 sync 的两个检查:

🌐 Two checks gate sync on a schema tree the CLI didn't generate:

• 如果你的迁移调用了 pg_net（例如，数据库 webhook），请先在 config.toml 中使用 enabled = true 添加 [experimental.webhooks]。这个部分不属于默认的 init 模板。
• 声明你的模式文件所依赖的扩展。检查会针对 pg_net 以及你自己迁移创建的扩展触发。当触发时，交互式 sync 会提供选项，添加缺失的声明并重新计划，或者在一个兄弟 -next 目录中暂存一个新的导出并打印采纳它的命令。非交互式运行只会打印这些命令。本地堆栈已自带的扩展，比如 pgcrypto 和 uuid-ossp，即便未声明也不会触发检查，所以你仍然需要自己声明它们。

在老旧的 migra#

🌐 Declarative schemas on the legacy migra engine [#declarative-schemas-on-the-legacy-migra-engine]

没有启用 pg-delta 的项目仍然可以使用声明式模式。工作流程的结构相同，但命令有所不同，而且无法使用 db schema declarative 命令。查看 Diff 引擎 了解如何检查你使用的是哪种引擎以及各引擎之间的比较。

🌐 Projects that haven't enabled pg-delta can still use declarative schemas. The workflow has the same shape, but the commands differ, and the db schema declarative commands aren't available. See Diff engines for how to check which engine you're on and how the engines compare.

在旧引擎上生成迁移 #

🌐 Generating migrations on the legacy engine

在旧版引擎上，supabase db diff 会读取你的声明式文件。当 supabase/schemas/ 包含文件时，它会将这些文件与你的迁移进行比较，而不是读取实时数据库。在本地环境停止运行时生成迁移。

🌐 On the legacy engine, supabase db diff reads your declarative files. When supabase/schemas/ contains files, it compares them against your migrations instead of reading the live database. Generate migrations with the local stack stopped.

1. 编辑 supabase/schemas/ 里的文件。

2. 停止本地堆栈并生成迁移：

   1
   supabase stop
   2
   supabase db diff -f add_age

3. 启动堆栈并应用迁移：

   1
   supabase start
   2
   supabase migration up

像在 pg-delta 上一样，直接通过 Studio、SQL 编辑器或 psql 做的更改不会被捕捉到。总是先编辑架构文件，然后再做差异比较。

🌐 As on pg-delta, changes made directly through Studio, the SQL editor, or psql are not picked up. Always edit the schema files, then diff.

在旧引擎上排序模式文件 #

🌐 Ordering schema files on the legacy engine

旧引擎会按照字典顺序应用你的模式文件。当表相互引用时，顺序很重要，因为父表必须先创建。要控制顺序，可以在 config.toml 的 [db.migrations].schema_paths 下列出文件或通配符模式。CLI 会按照你列出的顺序处理这些条目：它会展开每个模式，对该模式匹配的文件按字典顺序排序，并且跳过之前条目已匹配的文件。例如，这个例子总是先运行 employees.sql：

🌐 The legacy engine applies your schema files in lexicographic order. The order matters when tables reference each other, because the parent table must be created first. To control the order, list files or glob patterns under [db.migrations].schema_paths in config.toml. The CLI processes the entries in the order you list them: it expands each pattern, sorts that pattern's matches lexicographically, and skips any file an earlier entry already matched. This example always runs employees.sql first:

1
[db.migrations]
2
schema_paths = [
3
  "./schemas/employees.sql",
4
  "./schemas/*.sql",
5
]

从现有的旧引擎数据库开始 #

🌐 Starting from an existing database on the legacy engine

supabase db schema declarative generate 在旧引擎上不可用。相反，把你的生产模式导出到一个文件中，然后随着时间把它拆分成更小的文件：

1
supabase db dump > supabase/schemas/prod.sql

老旧引擎的局限性 #

🌐 Limitations of the legacy engine [#limitations-of-the-legacy-engine]

除了上述注意事项之外，旧版 migra 引擎还有这些限制：查看所有者和授权、视图的安全调用者（该设置在视图定义中会被静默丢弃）、物化视图上的索引在重新创建视图时不会恢复、修改策略语句、列权限、评论、角色、alter publication ... add table 和 create domain 语句。建议通过版本化迁移来添加这些实体。

🌐 In addition to the caveats above, the legacy migra engine has these limitations: view owner and grants, security invoker on views (the setting is silently dropped from the view definition), indexes on a materialized view aren't restored when the view is recreated, alter policy statements, column privileges, comments, roles, alter publication ... add table, and create domain statements. Add those entities through versioned migrations instead.

切换到 pg-delta#

🌐 Switching to pg-delta [#switching-to-pg-delta]

请参考 将现有项目切换到 pg-delta。对于声明式项目，会改变你日常工作的步骤包括移除 schema_paths，在脚本和 CI 中将 db diff 替换为 db schema declarative sync，以及去掉 supabase stop 步骤。

🌐 Follow Switch an existing project to pg-delta. For a declarative project, the steps that change your day-to-day work are removing schema_paths, replacing db diff with db schema declarative sync in scripts and CI, and dropping the supabase stop step.