数据库迁移

数据库迁移是用于创建、更新或删除现有数据库模式的 SQL 语句。它们是跟踪数据库随时间变化的一种常见方式。

🌐 Database migrations are SQL statements that create, update, or delete your existing database schemas. They are a common way of tracking changes to your database over time.

模式迁移 #

🌐 Schema migrations

在本指南中，我们将创建一个名为 employees 的表，并看看如何对它进行修改。

🌐 For this guide, we'll create a table called employees and see how we can make changes to it.

你需要安装 Supabase CLI 并启动本地开发环境。

🌐 You will need to install the Supabase CLI and start the local development stack.

1
supabase migration new create_employees_table

1
create table if not exists employees (
2
  id bigint primary key generated always as identity,
3
  name text not null,
4
  email text,
5
  created_at timestamptz default now()
6
);

1
supabase migration up

1
supabase migration new add_department_column

1
alter table if exists public.employees
2
add department text default 'Hooli';

1
supabase migration up

最后，你应该会在本地仪表板的 employees 表中看到已添加的 department 列。

🌐 Finally, you should see the department column added to your employees table in the local Dashboard.

种子数据 #

🌐 Seeding data

既然你现在通过迁移来管理数据库，那么每次重置数据库时有一些种子数据可以用会很好。

🌐 Now that you are managing your database with migrations, it would be great have some seed data to use every time you reset the database.

1
insert into public.employees
2
  (name)
3
values
4
  ('Erlich Bachman'),
5
  ('Richard Hendricks'),
6
  ('Monica Hall');

1
supabase db reset

你现在应该可以在仪表板中看到 employees 表和你的种子数据了！你所有的数据库更改都会被记录在代码中，而且你可以随时重置到已知状态，还包含种子数据。

🌐 You should now see the employees table, along with your seed data in the Dashboard! All of your database changes are captured in code, and you can reset to a known state at any time, complete with seed data.

差异比较 #

🌐 Diffing changes

如果你懂 SQL 并且熟悉创建表和列，这个工作流程非常棒。如果不熟悉，你仍然可以使用仪表板来创建表和列，然后用 CLI 对比你的更改并创建迁移。

🌐 This workflow is great if you know SQL and are comfortable creating tables and columns. If not, you can still use the Dashboard to create tables and columns, and then use the CLI to diff your changes and create migrations.

db diff 生成的 SQL 取决于你项目的差异引擎。

🌐 The SQL that db diff generates depends on your project's diff engine.

你的项目使用哪种差异引擎取决于 config.toml。如果它包含一个带有 enabled = true 的 [experimental.pgdelta] 部分，你的项目将使用 pg-delta。如果该部分缺失或 enabled 是 false，你的项目则会使用传统的 migra 引擎。查看 差异引擎 了解两者的区别以及如何切换。

🌐 Which diff engine your project uses depends on config.toml. If it contains an [experimental.pgdelta] section with enabled = true, your project uses pg-delta. If the section is missing or enabled is false, your project uses the legacy migra engine. See Diff engines for how the two differ and how to switch.

1
supabase db diff -f create_cities_table

1
create table "public"."cities" (
2
  "id" bigint primary key generated always as identity,
3
  "name" text,
4
  "population" bigint
5
);

1
supabase db reset

最后一步是把这些更改部署到一个实际的 Supabase 项目中。

🌐 The last step is deploying these changes to a live Supabase project.

部署你的项目 #

🌐 Deploy your project

你一直在本地开发项目，通过迁移修改你的表格。现在是时候将你的项目部署到 Supabase 平台，并开始扩展到数百万用户了！

🌐 You've been developing your project locally, making changes to your tables via migrations. It's time to deploy your project to the Supabase Platform and start scaling up to millions of users!

去 Supabase 创建一个新项目来部署。

🌐 Head over to Supabase and create a new project to deploy to.

1
supabase login

1
supabase link

1
supabase db push

1
supabase db push --include-seed

访问你在 Supabase 上的实时项目，你会看到一个新的 employees 表，其中包含你在上面第二次迁移中添加的 department 列。

🌐 Visiting your live project on Supabase, you'll see a new employees table, complete with the department column you added in the second migration above.

与团队合作 #

🌐 Working with a team

当多个开发者共享一个 Supabase 项目时，有一些规则可以防止迁移不同步。

🌐 When multiple developers share a Supabase project, a few rules keep migrations from getting out of sync.

黄金法则：绝不要直接更改远程数据库。 一旦你开始使用迁移，所有的模式更改——即使是很小的——都应该通过迁移文件进行。在远程数据库上使用仪表板的 SQL 编辑器或表编辑器会绕过迁移历史，db push 就会开始出现同步错误。

团队工作流程：

1
supabase migration new your_change_description

1
supabase db reset
2
git add supabase/migrations
3
git commit -m "add migration: your_change_description"

1
git pull
2
supabase db reset

1
supabase db push

诊断并修复同步错误 #

🌐 Diagnosing and fixing sync errors

如果 db push 出错提示你运行 supabase migration repair，说明你本地的迁移文件和远程数据库的迁移历史不同步。下面是诊断和解决的方法。

🌐 If db push fails with errors suggesting you run supabase migration repair, your local migration files and the remote database's migration history are out of sync. Here's how to diagnose and fix it.

迁徙追踪是如何运作的 #

🌐 How migration tracking works

Supabase 会在一个叫做 supabase_migrations.schema_migrations 的表里跟踪每个数据库已经应用了哪些迁移。当你运行 supabase db push 时，它会把你本地的 supabase/migrations 文件夹和那个表进行比较，只按顺序运行那些还没应用的迁移。

🌐 Supabase tracks which migrations have been applied on each database in a table called supabase_migrations.schema_migrations. When you run supabase db push, it compares your local supabase/migrations folder against that table and runs only the ones not yet applied, in order.

Git 跟踪你的迁移 文件。Supabase 跟踪什么已经 应用到每个数据库。这是两个需要保持同步的独立系统。

🌐 Git tracks your migration files. Supabase tracks what's been applied to each database. These are two separate systems that need to stay in sync.

步骤1：检查哪些不同步 #

🌐 Step 1: Check what's out of sync

先把本地和远程的迁移状态列出来：

🌐 Start by listing the migration status across local and remote:

1
supabase migration list

这显示了哪些迁移在本地应用，哪些在远程应用，以及它们在哪些地方不同。

🌐 This shows which migrations are applied locally, which are applied on the remote, and where they diverge.

步骤 2：如果你直接在远程数据库上进行了更改 #

🌐 Step 2: If you made changes on the remote database directly

把当前的远程状态拉到迁移文件中，以便重新同步：

🌐 Pull the current remote state into a migration file to get back in sync:

1
supabase db pull

这会创建一个新的迁移文件，捕捉当前的远程架构。把它提交到 git，然后以后按标准流程操作就行。

🌐 This creates a new migration file capturing the current remote schema. Commit it to git, then follow the standard workflow going forward.

db pull 在托管模式下捕获的内容取决于你项目的差异引擎。

🌐 What db pull captures in managed schemas depends on your project's diff engine.

你的项目使用哪种差异引擎取决于 config.toml。如果它包含一个带有 enabled = true 的 [experimental.pgdelta] 部分，你的项目将使用 pg-delta。如果该部分缺失或 enabled 是 false，你的项目则会使用传统的 migra 引擎。查看 差异引擎 了解两者的区别以及如何切换。

🌐 Which diff engine your project uses depends on config.toml. If it contains an [experimental.pgdelta] section with enabled = true, your project uses pg-delta. If the section is missing or enabled is false, your project uses the legacy migra engine. See Diff engines for how the two differ and how to switch.

在 pg-delta 上，一个普通的 supabase db pull 会自动捕捉你的自定义，比如在 auth.users 上触发调用 public 中函数的触发器，或者 storage.objects 上的 RLS 策略。即使你创建了，函数位于 auth 或 storage 中的触发器也会被排除，所以需要通过有版本控制的迁移来交付。在旧的 migra 引擎上，初次拉取的 pg_dump 会跳过那些模式，但随后的差异步骤会将你的触发器和 RLS 策略追加到同一个迁移中，之后的拉取也会进行差异处理。旧版 CLI 完全排除了 auth 和 storage，并显示消息说明这一点。如果你看到那条消息，就用显式拉取一次捕捉现有的自定义：

🌐 On pg-delta, a plain supabase db pull captures your customizations automatically, such as a trigger on auth.users that calls a function in public, or RLS policies on storage.objects. A trigger whose function lives inside auth or storage is excluded even if you created it, so deliver it through a versioned migration. On the legacy migra engine, the initial pull's pg_dump skips those schemas, but the diff pass that follows appends your triggers and RLS policies there to the same migration, and later pulls diff them too. Older CLI versions excluded auth and storage entirely and printed a message saying so. If you see that message, capture existing customizations once with explicit pulls:

1
supabase db pull --schema auth
2
supabase db pull --schema storage

查看 Diff 引擎 了解每个引擎在托管模式中捕获的内容。

🌐 See Diff engines for what each engine captures in managed schemas.

步骤3：如果迁移历史表有误 #

🌐 Step 3: If the migration history table is wrong

如果迁移在远程历史表中显示为缺失，但模式更改已经存在（例如，它是手动应用的），你可以在不重新运行它的情况下将其标记为已应用：

🌐 If a migration shows as missing in the remote history table but the schema change is already there (for example, it was applied manually), you can mark it as applied without re-running it:

1
supabase migration repair --status applied <migration-timestamp>

或者如果某个迁移被记录为已应用，但实际上从未运行过：

🌐 Or if a migration is recorded as applied but was never run:

1
supabase migration repair --status reverted <migration-timestamp>