修补和热修复

修补或热修复数据库通常涉及直接在生产中进行时间关键的更改。例如，你可以将索引直接添加到生产数据库以解决查询运行缓慢的问题。

¥Patching or hotfixing a database involves making an often time critical change directly in production. For example, you might add an index directly to a production database to resolve an issue with a slow-running query.

修补生产数据库直接导致模式漂移：你的数据库架构具有来自真实来源的 '渐行渐远'，并且与你的迁移历史记录不同步。你可以使用 prisma migrate resolve 命令来协调迁移历史记录，而无需使用 prisma migrate deploy 删除并重新应用修补程序。

¥Patching the production database directly results in schema drift: your database schema has 'drifted away' from the source of truth, and is out of sync with your migration history. You can use the prisma migrate resolve command to reconcile your migration history without having to remove and re-apply the hotfix with prisma migrate deploy.

warning

本指南不适用于 MongoDB。
使用 db push，而不是 migrate dev，用于 MongoDB。

¥This guide does not apply for MongoDB.
Instead of migrate dev, db push is used for MongoDB.

使用补丁或修补程序协调你的迁移历史记录

¥Reconciling your migration history with a patch or hotfix

以下场景假设你在生产中进行了手动更改，并希望将该更改传播到迁移历史记录和其他数据库。

¥The following scenario assumes that you made a manual change in production and want to propagate that change to your migration history and other databases.

要协调生产中的迁移历史记录和数据库架构：

¥To reconcile your migration history and database schema in production:

1. 在架构中复制你在生产中所做的更改 - 例如，将 @@index 添加到特定型号。

   ¥Replicate the change you made in production in the schema - for example, add an @@index to a particular model.

2. 生成新的迁移并记下完整的迁移名称，包括写入 CLI:(20210316150542_retroactively_add_index) 的时间戳：

   ¥Generate a new migration and take note of the full migration name, including a timestamp, which is written to the CLI:(20210316150542_retroactively_add_index):

   npx prisma migrate dev --name retroactively-add-index

   Show CLI results

   migrations/
   └─ 20210316150542_retroactively_add_index/
   └─ migration.sql
   
   Your database is now in sync with your schema.
   
   ✔ Generated Prisma Client (2.19.0-dev.29) to .\node_modules\@prisma\client in 190ms

3. 在不运行 migrate deploy 的情况下将迁移推送到生产环境。相反，将上一步中创建的迁移标记为 '已经申请了'，以便 Prisma Migrate 不会尝试再次应用修补程序：

   ¥Push the migration to production without running migrate deploy. Instead, mark the migration created in the previous step as 'already applied' so that Prisma Migrate does not attempt to apply your hotfix a second time:

   prisma migrate resolve --applied "20201127134938-retroactively-add-index"

   此命令将迁移添加到迁移历史表中，而不运行实际的 SQL。

   ¥This command adds the migration to the migration history table without running the actual SQL.

4. 对已修补的其他数据库重复上一步 - 例如，如果你将补丁应用到临时数据库。

   ¥Repeat the previous step for other databases that were patched - for example, if you applied the patch to a staging database.

5. 将迁移传播到未修补的其他数据库 - 例如，通过将迁移提交到源代码管理并允许 CI/CD 管道将其应用到所有数据库。

   ¥Propagate the migration to other databases that were not patched - for example, by committing the migration to source control and allowing your CI/CD pipeline to apply it to all databases.

注意：迁移不会应用于已被 prisma migrate resolve 命令标记为已应用的数据库。

¥Note: The migration will not be applied to databases where it has been marked as already applied by the prisma migrate resolve command.

迁移失败

¥Failed migration

如果出现以下情况，迁移可能会失败：

¥A migration might fail if:

• 你 在运行之前修改迁移 并引入了语法错误

  ¥You modify a migration before running it and introduce a syntax error

• 你向已有数据的表添加强制 (NOT NULL) 列

  ¥You add a mandatory (NOT NULL) column to a table that already has data

• 迁移过程意外停止

  ¥The migration process stopped unexpectedly

• 数据库在迁移过程中关闭

  ¥The database shut down in the middle of the migration process

_prisma_migrations 表中的每个迁移都有一个 logs 列用于存储错误。

¥Each migration in the _prisma_migrations table has a logs column that stores the error.

在生产环境中处理失败的迁移有两种方法：

¥There are two ways to deal with failed migrations in a production environment:

• 回滚、选择性修复问题并重新部署

  ¥Roll back, optionally fix issues, and re-deploy

• 手动完成迁移步骤并解决迁移

  ¥Manually complete the migration steps and resolve the migration

选项 1：将迁移标记为回滚并重新部署

¥Option 1: Mark the migration as rolled back and re-deploy

以下示例演示了如何回滚迁移、选择性地进行更改以修复问题以及重新部署：

¥The following example demonstrates how to roll back a migration, optionally make changes to fix the issue, and re-deploy:

1. 将迁移标记为已回滚 - 这会更新 _prisma_migrations 表中的迁移记录以将其注册为回滚，从而允许再次应用它：

   ¥Mark the migration as rolled back - this updates the migration record in the _prisma_migrations table to register it as rolled back, allowing it to be applied again:

   prisma migrate resolve --rolled-back "20201127134938_added_bio_index"

2. 如果迁移已部分运行，你可以：

   ¥If the migration was partially run, you can either:

   • 修改迁移以检查步骤是否已完成（例如：CREATE TABLE ... IF NOT EXISTS）或

     ¥Modify the migration to check if a step was already completed (for example: CREATE TABLE ... IF NOT EXISTS) OR

   • 手动恢复已完成的步骤（例如删除创建的表）

     ¥Manually revert the steps that were completed (for example, delete created tables)

   如果你修改迁移，请确保将其复制回源代码控制，以确保生产数据库的状态在开发中准确反映。

   ¥If you modify the migration, make sure you copy it back to source control to ensure that state of your production database is reflected exactly in development.

3. 修复迁移失败的根本原因（如果相关） - 例如，如果迁移由于 SQL 脚本本身的问题而失败。确保将所有更改的迁移复制回源代码管理。

   ¥Fix the root cause of the failed migration, if relevant - for example, if the migration failed due to an issue with the SQL script itself. Make sure that you copy any changed migrations back to source control.

4. 重新部署迁移：

   ¥Re-deploy the migration:

   prisma migrate deploy

选项 2：手动完成迁移并根据应用进行解决

¥Option 2: Manually complete migration and resolve as applied

以下示例演示如何手动完成迁移步骤并将该迁移标记为已应用。

¥The following example demonstrates how to manually complete the steps of a migration and mark that migration as applied.

1. 手动完成生产数据库上的迁移步骤。确保所有手动步骤与迁移文件中的步骤完全匹配，并将所有更改复制回源代码管理。

   ¥Manually complete the migration steps on the production database. Make sure that any manual steps exactly match the steps in the migration file, and copy any changes back to source control.

2. 解决所应用的迁移 - 这告诉 Prisma Migrate 认为迁移已成功应用：

   ¥Resolve the migration as applied - this tells Prisma Migrate to consider the migration successfully applied:

   prisma migrate resolve --applied "20201127134938_my_migration"

使用 migrate diff 和 db execute 修复失败的迁移

¥Fixing failed migrations with migrate diff and db execute

为了帮助修复失败的迁移，Prisma ORM 提供了以下命令来创建和执行迁移文件：

¥To help with fixing a failed migration, Prisma ORM provides the following commands for creating and executing a migration file:

• prisma migrate diff 比较两个数据库模式源以创建迁移，将一个数据库模式源迁移到第二个数据库模式源的状态。你可以输出差异摘要或 SQL 脚本。该脚本可以通过 > file_name.sql 输出到文件中或通过管道传输到 db execute --stdin 命令。

  ¥prisma migrate diff which diffs two database schema sources to create a migration taking one to the state of the second. You can output either a summary of the difference or a sql script. The script can be output into a file via > file_name.sql or be piped to the db execute --stdin command.

• prisma db execute 将 SQL 脚本应用于数据库，而不与 Prisma 迁移表交互。

  ¥prisma db execute which applies a SQL script to the database without interacting with the Prisma migrations table.

这些命令在 3.9.0 及更高版本（带有 --preview-feature CLI 标志）的预览版中可用，并且在 3.13.0 及更高版本中普遍可用。

¥These commands are available in Preview in versions 3.9.0 and later (with the --preview-feature CLI flag), and generally available in versions 3.13.0 and later.

本节提供迁移失败的示例场景，并说明如何使用 migrate diff 和 db execute 来修复它。

¥This section gives an example scenario of a failed migration, and explains how to use migrate diff and db execute to fix it.

迁移失败的示例

¥Example of a failed migration

想象一下，你的本地开发环境和生产环境中的架构中有以下 User 模型：

¥Imagine that you have the following User model in your schema, in both your local development environment and your production environment:

schema.prisma

model User {
  id   Int    @id
  name String
}

此时，你的架构已同步，但两个环境中的数据不同。

¥At this point, your schemas are in sync, but the data in the two environments is different.

然后，你决定更改数据模型，添加另一个 Post 模型并使 User 上的 name 字段唯一：

¥You then decide to make changes to your data model, adding another Post model and making the name field on User unique:

schema.prisma

model User {
  id    Int     @id
  name  String  @unique
  email String?
}

model Post {
  id    Int    @id
  title String
}

你使用命令 prisma migrate dev -n Unique 创建名为 '独特的' 的迁移，该迁移保存在本地迁移历史记录中。在你的开发环境中成功应用迁移，现在是时候发布到生产环境了。

¥You create a migration called 'Unique' with the command prisma migrate dev -n Unique which is saved in your local migrations history. Applying the migration succeeds in your dev environment and now it is time to release to production.

不幸的是，这种迁移只能部分执行。创建 Post 模型并添加 email 列成功，但使 name 字段唯一失败并出现以下错误：

¥Unfortunately this migration can only be partially executed. Creating the Post model and adding the email column succeeds, but making the name field unique fails with the following error:

ERROR 1062 (23000): Duplicate entry 'paul' for key 'User_name_key'

这是因为你的生产数据库中存在非唯一数据（例如，两个用户具有相同的名称）。

¥This is because there is non-unique data in your production database (e.g. two users with the same name).

你现在需要从部分执行的迁移中手动恢复。在从失败状态恢复之前，不可能使用 prisma migrate deploy 进行进一步迁移。

¥You now need to recover manually from the partially executed migration. Until you recover from the failed state, further migrations using prisma migrate deploy are impossible.

此时有两个选项，具体取决于你决定如何处理非唯一数据：

¥At this point there are two options, depending on what you decide to do with the non-unique data:

• 你意识到非唯一数据是有效的，并且你无法继续当前的开发工作。你想要回滚整个迁移。为此，请参阅 向后移动并恢复所有更改

  ¥You realize that non-unique data is valid and you cannot move forward with your current development work. You want to roll back the complete migration. To do this, see Moving backwards and reverting all changes

• 数据库中存在非唯一数据是无意的，你希望修复该问题。修复后，你想要继续进行其余的迁移。为此，请参阅 继续前进并应用缺失的更改

  ¥The existence of non-unique data in your database is unintentional and you want to fix that. After fixing, you want to go ahead with the rest of the migration. To do this, see Moving forwards and applying missing changes

向后移动并恢复所有更改

¥Moving backwards and reverting all changes

在这种情况下，你需要创建一个迁移，将生产数据库恢复到上次迁移之前的数据模型状态。

¥In this case, you need to create a migration that takes your production database to the state of your data model before the last migration.

• 首先，你需要迁移失败之前的迁移历史记录。你可以从 git 历史记录中获取此信息，也可以在本地删除迁移历史记录中上次失败迁移的文件夹。

  ¥First you need your migration history at the time before the failed migration. You can either get this from your git history, or locally delete the folder of the last failed migration in your migration history.

• 现在，你希望将生产环境从当前的失败状态恢复到本地迁移历史记录中指定的状态：

  ¥You now want to take your production environment from its current failed state back to the state specified in your local migrations history:

  • 运行以下 prisma migrate diff 命令：

    ¥Run the following prisma migrate diff command:

     npx prisma migrate diff \
      --from-url "$DATABASE_URL_PROD" \
      --to-migrations ./prisma/migrations \
      --shadow-database-url $SHADOW_DATABASE_URL \
      --script > backward.sql

    这将创建一个 SQL 脚本文件，其中包含将生产环境从当前失败状态转变为迁移历史记录定义的目标状态所需的所有更改。请注意，因为我们使用的是 --to-migrations，所以该命令需要 影子数据库。

    ¥This will create a SQL script file containing all changes necessary to take your production environment from its current failed state to the target state defined by your migrations history. Note that because we're using --to-migrations, the command requires a shadow database.

  • 运行以下 prisma db execute 命令：

    ¥Run the following prisma db execute command:

     npx prisma db execute --url "$DATABASE_URL_PROD" --file backward.sql

    这会将 SQL 脚本中的更改应用于目标数据库，而无需与迁移表交互。

    ¥This applies the changes in the SQL script against the target database without interacting with the migrations table.

  • 运行以下 prisma migrate resolve 命令：

    ¥Run the following prisma migrate resolve command:

     npx prisma migrate resolve --rolled-back Unique

    这会将生产环境中迁移表中名为 '独特的' 的失败迁移标记为已回滚。

    ¥This will mark the failed migration called 'Unique' in the migrations table on your production environment as rolled back.

你的本地迁移历史记录现在会产生与生产数据库所处状态相同的结果。现在，你可以再次修改数据模型，以创建适合你对正在处理的功能的新理解的迁移（使用非唯一名称）。

¥Your local migration history now yields the same result as the state your production database is in. You can now modify the datamodel again to create a migration that suits your new understanding of the feature you're working on (with non-unique names).

继续前进并应用缺失的更改

¥Moving forwards and applying missing changes

在这种情况下，你需要修复非唯一数据，然后按计划继续进行其余的迁移：

¥In this case, you need to fix the non-unique data and then go ahead with the rest of the migration as planned:

• 尝试将迁移部署到生产环境时出现的错误消息已经告诉你 name 列中存在重复数据。你需要更改或删除有问题的行。

  ¥The error message from trying to deploy the migration to production already told you there was duplicate data in the column name. You need to either alter or delete the offending rows.

• 继续应用失败的迁移的其余部分以获取 schema.prisma 文件中定义的数据模型：

  ¥Continue applying the rest of the failed migration to get to the data model defined in your schema.prisma file:

  • 运行以下 prisma migrate diff 命令：

    ¥Run the following prisma migrate diff command:

    
    npx prisma migrate diff --from-url "$DATABASE_URL_PROD" --to-schema-datamodel schema.prisma --script > forward.sql
    

    这将创建一个 SQL 脚本文件，其中包含将生产环境从当前失败状态转变为 schema.prisma 文件中定义的目标状态所需的所有更改。

    ¥This will create a SQL script file containing all changes necessary to take your production environment from its current failed state to the target state defined in your schema.prisma file.

  • 运行以下 prisma db execute 命令：

    ¥Run the following prisma db execute command:

    npx prisma db execute --url "$DATABASE_URL_PROD" --file forward.sql

    这会将 SQL 脚本中的更改应用于目标数据库，而无需与迁移表交互。

    ¥This applies the changes in the SQL script against the target database without interacting with the migrations table.

  • 运行以下 prisma migrate resolve 命令：

    ¥Run the following prisma migrate resolve command:

    npx prisma migrate resolve --applied Unique

    这会将生产环境的迁移表中名为 '独特的' 的失败迁移标记为已应用。

    ¥This will mark the failed migration called 'Unique' in the migrations table on your production environment as applied.

现在，你的本地迁移历史记录会产生与生产环境所处状态相同的结果。你现在可以继续使用已知的 migrate dev /migrate deploy 工作流程。

¥Your local migration history now yields the same result as the state your production environment is in. You can now continue using the already known migrate dev /migrate deploy workflow.

迁移历史冲突

¥Migration history conflicts

info

这不适用于 3.12.0 及以上版本。

¥This does not apply from version 3.12.0 upwards.

如果已应用的迁移已被编辑，prisma migrate deploy 会触发警告 - 但是，它不会停止迁移过程。要删除警告，请从源代码管理恢复原始迁移。

¥prisma migrate deploy issues a warning if an already applied migration has been edited - however, it does not stop the migration process. To remove the warnings, restore the original migration from source control.

Prisma Migrate 和 PgBouncer

¥Prisma Migrate and PgBouncer

如果你尝试在使用 PgBouncer 进行连接池的环境中运行 Prisma Migrate 命令，你可能会看到以下错误：

¥You might see the following error if you attempt to run Prisma Migrate commands in an environment that uses PgBouncer for connection pooling:

Error: undefined: Database error
Error querying the database: db error: ERROR: prepared statement "s0" already exists

请参阅 Prisma Migrate 和 PgBouncer 解决方法 了解更多信息和解决方法。关注 GitHub 问题 #6485 获取更新。

¥See Prisma Migrate and PgBouncer workaround for further information and a workaround. Follow GitHub issue #6485 for updates.