声明式数据库模式

Manage your database schemas in one place and generate versioned migrations.

概览 #

🌐 Overview

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

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

因为 schema 文件是权威来源，所以所有修改都应该通过编辑它们来进行——不要通过 Studio 或 SQL 编辑器。supabase db diff 对比的是你的 schema 文件，而不是实时数据库，所以直接在数据库上做的修改不会被捕获。

🌐 Because the schema files are the source of truth, make every change by editing them - not through Studio or the SQL editor. supabase db diff compares your schema files, not the live database, so 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

Create your first schema file

在 supabase/schemas 目录中创建一个 SQL 文件，定义一个 employees 表。

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

Generate a migration file

通过对比你声明的模式生成一个迁移文件。

1
supabase db diff -f create_employees_table

Start the local database and apply migrations

先启动本地数据库。然后，手动应用迁移，以便在本地仪表板中查看你的模式更改。

1
supabase start
2
supabase migration up

正在更新你的模式 #

🌐 Updating your schema

使用声明式模式时，supabase/schemas/ 里的文件是唯一的真实来源。supabase db diff 会把这些文件跟你的迁移进行比较——它不会读取实时数据库。你直接做的修改（Studio、SQL编辑器、psql）对 diff 来说是看不见的：它会显示“未找到模式更改”，而你的修改会被悄悄丢掉。务必先编辑模式文件，然后再运行 db diff。

🌐 With declarative schemas, the files in supabase/schemas/ are the source of truth. supabase db diff compares those files against your migrations - it does not read the live database. Changes you make directly (Studio, the SQL editor, psql) are invisible to the diff: it reports "No schema changes found" and the change is silently dropped. Always edit the schema files, then run db diff.

Add a new column

编辑 supabase/schemas/employees.sql 文件，在 employees 表中添加一个新列。

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

有些实体，比如视图和枚举，期望列按特定顺序声明。为了避免混乱的差异，最好总是把新列添加到表的末尾。

🌐 Some entities like views and enums expect columns to be declared in a specific order. To avoid messy diffs, always append new columns to the end of the table.

Generate a new migration

将现有迁移与你声明的架构进行比较。

1
supabase db diff -f add_age

Review the generated migration

验证生成的迁移是否只包含一个增量更改。

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

Apply the pending migration

在本地启动数据库并应用待处理的迁移。

1
supabase migration up

部署你的模式更改 #

🌐 Deploying your schema changes

通过 Supabase CLI 登录。

1
supabase login

Link your remote project

按照屏幕上的提示来链接你的远程项目。

1
supabase link

Deploy database changes

推送你的更改到远程数据库。

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
$$;

你的模式文件默认会按字典顺序运行。当你有多个表之间的外键时，顺序就很重要，因为父表必须先创建。例如，你的 supabase 目录最终可能会有如下结构。

🌐 Your schema files are run in lexicographic order by default. The order is important when you have foreign keys between multiple tables as the parent table must be created first. For example, your supabase directory may end up with the following structure.

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

对于只有几个表的小项目，默认的 schema 顺序可能已经足够了。不过，随着项目规模的增长，你可能需要更多地控制 schema 的应用顺序。要指定自定义的 schema 应用顺序，你可以在 config.toml 中显式声明它们。所有的通配符模式都会被评估、去重，并按字母顺序排序。例如，下面的模式可以确保 employees.sql 总是最先执行。

🌐 For small projects with only a few tables, the default schema order may be sufficient. However, as your project grows, you might need more control over the order in which schemas are applied. To specify a custom order for applying the schemas, you can declare them explicitly in config.toml. Any glob patterns will evaluated, deduplicated, and sorted in lexicographic order. For example, the following pattern ensures employees.sql is always executed first.

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

拉取你的生产环境架构 #

🌐 Pulling in your production schema

要在现有项目上设置声明式模式，你可以通过运行以下命令来导入你的生产模式：

🌐 To set up declarative schemas on a existing project, you can pull in your production schema by running:

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

从那里，你可以开始将你的模式拆分成更小的文件并生成迁移。你可以一次性完成，也可以在对模式进行更改时逐步进行。

🌐 From there, you can start breaking down your schema into smaller files and generate migrations. You can do this all at once, or incrementally as you make changes to your schema.

回滚模式更改 #

🌐 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.

在向下迁移中生成的 SQL 语句通常具有破坏性。你必须仔细检查它们，以避免意外的数据丢失。

🌐 SQL statements generated in a down migration are usually destructive. You must review them carefully to avoid unintentional data loss.

已知问题 #

🌐 Known caveats

pg-delta 会生成架构差异，这是默认的差异引擎，能够追踪大多数数据库更改。不过，也有一些边缘情况会导致架构差异失败。下面列出的已知情况是针对旧版 migra 引擎记录的（仍可以通过 enabled = false 在 config.toml 的 [experimental.pgdelta] 下使用，或者通过 --use-migra 使用）；其中一些情况，比如来自默认权限的重复授权，也适用于 pg-delta。不管用哪个引擎，都要检查每次生成的迁移。

🌐 Schema diffs are generated by pg-delta, the default diff engine, which tracks most database changes. However, there are edge cases where schema diff can fail. The known cases below were documented against the legacy migra engine (still available via enabled = false under [experimental.pgdelta] in config.toml, or --use-migra); some, such as duplicated grants from default privileges, also apply to pg-delta. Review every generated migration regardless of engine.

如果你需要使用下面的任何实体，记得改用通过版本迁移来添加它们。

🌐 If you need to use any of the entities below, remember to add them through versioned migrations instead.

数据操作语言 #

🌐 Data manipulation language

像 insert、update、delete 这样的 DML 语句不会被 schema diff 捕获

查看所有权 #

🌐 View ownership

查看所有者和权限
视图上的安全调用者
物化视图
在更改列类型时不会重新创建视图

RLS 政策 #

🌐 RLS policies

其他实体 #

🌐 Other entities