使用 Prisma ORM、JavaScript 和 SQL Server 进行基线

JavaScript

SQL Server

创建初始迁移

¥Create an initial migration

要将 Prisma Migrate 与你在上一节中反思的数据库一起使用，你将需要数据库的基线。

¥To use Prisma Migrate with the database you introspected in the last section, you will need to baseline your database.

基线是指初始化可能已包含数据且无法重置的数据库（例如生产数据库）的迁移历史记录。基线告诉 Prisma Migrate 假设一项或多项迁移已应用到你的数据库。

¥Baselining refers to initializing your migration history for a database that might already contain data and cannot be reset, such as your production database. Baselining tells Prisma Migrate to assume that one or more migrations have already been applied to your database.

要为数据库建立基线，请使用 prisma migrate diff 比较你的架构和数据库，并将输出保存到 SQL 文件中。

¥To baseline your database, use prisma migrate diff to compare your schema and database, and save the output into a SQL file.

首先，创建一个 migrations 目录，并在其中添加一个目录，其中包含你用于迁移的首选名称。在此示例中，我们将使用 0_init 作为迁移名称：

¥First, create a migrations directory and add a directory inside with your preferred name for the migration. In this example, we will use 0_init as the migration name:

mkdir -p prisma/migrations/0_init

info

-p 将在你提供的路径中递归创建任何丢失的文件夹。

¥-p will recursively create any missing folders in the path you provide.

接下来，使用 prisma migrate diff 生成迁移文件。使用以下参数：

¥Next, generate the migration file with prisma migrate diff. Use the following arguments:

--from-empty：假设你要迁移的数据模型为空

¥--from-empty: assumes the data model you're migrating from is empty
--to-schema-datamodel：使用 datasource 块中的 URL 获取当前数据库状态

¥--to-schema-datamodel: the current database state using the URL in the datasource block
--script：输出 SQL 脚本

¥--script: output a SQL script

npx prisma migrate diff --from-empty --to-schema-datamodel prisma/schema.prisma --script > prisma/migrations/0_init/migration.sql

检查迁移

¥Review the migration

该命令将生成类似于以下脚本的迁移：

¥The command will generate a migration that should resemble the following script:

prisma/migrations/0_init/migration.sql

CREATE TABLE [dbo].[Post] (
    [id] INT NOT NULL IDENTITY(1,1),
    [createdAt] DATETIME2 NOT NULL CONSTRAINT [Post_createdAt_df] DEFAULT CURRENT_TIMESTAMP,
    [updatedAt] DATETIME2 NOT NULL,
    [title] VARCHAR(255) NOT NULL,
    [content] NVARCHAR(1000),
    [published] BIT NOT NULL CONSTRAINT [Post_published_df] DEFAULT 0,
    [authorId] INT NOT NULL,
    CONSTRAINT [Post_pkey] PRIMARY KEY ([id])
);

CREATE TABLE [dbo].[Profile] (
    [id] INT NOT NULL IDENTITY(1,1),
    [bio] NVARCHAR(1000),
    [userId] INT NOT NULL,
    CONSTRAINT [Profile_pkey] PRIMARY KEY ([id]),
    CONSTRAINT [Profile_userId_key] UNIQUE ([userId])
);

CREATE TABLE [dbo].[User] (
    [id] INT NOT NULL IDENTITY(1,1),
    [email] NVARCHAR(1000) NOT NULL,
    [name] NVARCHAR(1000),
    CONSTRAINT [User_pkey] PRIMARY KEY ([id]),
    CONSTRAINT [User_email_key] UNIQUE ([email])
);

ALTER TABLE [dbo].[Post] ADD CONSTRAINT [Post_authorId_fkey] FOREIGN KEY ([authorId]) REFERENCES [dbo].[User]([id]) ON DELETE NO ACTION ON UPDATE CASCADE;

ALTER TABLE [dbo].[Profile] ADD CONSTRAINT [Profile_userId_fkey] FOREIGN KEY ([userId]) REFERENCES [dbo].[User]([id]) ON DELETE NO ACTION ON UPDATE CASCADE;

检查 SQL 迁移文件以确保一切正确。

¥Review the SQL migration file to ensure everything is correct.

接下来，使用 prisma migrate resolve 和 --applied 参数将迁移标记为已应用。

¥Next, mark the migration as applied using prisma migrate resolve with the --applied argument.

npx prisma migrate resolve --applied 0_init

该命令将通过将 0_init 添加到 _prisma_migrations 表来将其标记为已应用。

¥The command will mark 0_init as applied by adding it to the _prisma_migrations table.

你现在已经有了当前数据库架构的基线。要对数据库架构进行进一步更改，你可以更新 Prisma 架构并使用 prisma migrate dev 将更改应用到数据库。

¥You now have a baseline for your current database schema. To make further changes to your database schema, you can update your Prisma schema and use prisma migrate dev to apply the changes to your database.

创建初始迁移​

检查迁移​

创建初始迁移

检查迁移