数据库迁移

数据库迁移是用于创建、更新或删除现有数据库模式的 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.

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.

步骤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>