Duplicate `@SQL` declaration during migration

[seed-of-apricot]

Bug description

npx prisma migration produces duplicate @SQL declaration that results in

Database error code: 134

Database error:
The variable name '@SQL' has already been declared. Variable names must be unique within a query batch or stored procedure.

How to reproduce

Change the Prisma schema as shown below.

... // some other models

model Terrain {
  id            String      @id @default(uuid())
  createdAt     DateTime    @default(now())
  deletedAt     DateTime?
  terrainType   TerrainType @relation(fields: [terrainTypeId], references: [id])
  terrainTypeId String      // change this to Int
  Plot          Plot        @relation(fields: [plotId], references: [id])
  plotId        String

  @@index([plotId])
  @@index([terrainTypeId])
}

model TerrainType {
  id      String    @id @default(uuid())    // change String to Int and uuid() to autoincrement()
  name    String
  Terrain Terrain[]
}

model Feature {
  id            String      @id @default(uuid())
  createdAt     DateTime    @default(now())
  deletedAt     DateTime?
  featureType   FeatureType @relation(fields: [featureTypeId], references: [id])
  featureTypeId String      // change this to Int
  Plot          Plot        @relation(fields: [plotId], references: [id])
  plotId        String

  @@index([plotId])
  @@index([featureTypeId])
}

model FeatureType {
  id      String    @id @default(uuid())    // change String to Int and uuid() to autoincrement()
  name    String
  Feature Feature[]
}

Expected behavior

The migration will be applied without errors.

I could apply it by manually removing the second @SQL declaration.

Prisma information

// schema.prisma
generator client {
  provider = "prisma-client-js"
}

datasource db {
  provider     = "sqlserver"
  url          = env("DATABASE_URL")
  relationMode = "prisma"
}

generator pothos {
  provider = "prisma-pothos-types"
}

generator pothosCrud {
  provider            = "prisma-generator-pothos-codegen"
  generatorConfigPath = "./pothos.config.js"
}

model User {
  id        String   @id @default(uuid())
  createdAt DateTime @default(now())
  name      String
}

model Player {
  id        String   @id @default(uuid())
  createdAt DateTime @default(now())
  name      String
  teams     Team[]
}

model Team {
  id        String   @id @default(uuid())
  createdAt DateTime @default(now())
  name      String
  players   Player[]
  games     Game[]
}

model Game {
  id           String      @id @default(uuid())
  createdAt    DateTime    @default(now())
  name         String
  teams        Team[]
  ingameInfo   IngameInfo?
  mapConfig    mapConfig?
  ingameInfoId String?
}

model IngameInfo {
  id        String   @id @default(uuid())
  createdAt DateTime @default(now())
  map       Map?
  game      Game     @relation(fields: [gameId], references: [id])
  gameId    String   @unique

  @@index([gameId])
}

model mapConfig {
  id        String   @id @default(uuid())
  createdAt DateTime @default(now())
  name      String
  width     Int
  height    Int
  Game      Game     @relation(fields: [gameId], references: [id])
  gameId    String   @unique

  @@index([gameId])
}

model Map {
  id           String     @id @default(uuid())
  createdAt    DateTime   @default(now())
  plots        Plot[]
  IngameInfo   IngameInfo @relation(fields: [ingameInfoId], references: [id])
  ingameInfoId String     @unique

  @@index([ingameInfoId])
}

model Plot {
  id        String    @id @default(uuid())
  createdAt DateTime  @default(now())
  name      String
  x         Int
  y         Int
  terrain   Terrain[]
  feature   Feature[]
  Map       Map       @relation(fields: [mapId], references: [id])
  mapId     String

  @@index([mapId])
}

model Terrain {
  id            String      @id @default(uuid())
  createdAt     DateTime    @default(now())
  deletedAt     DateTime?
  terrainType   TerrainType @relation(fields: [terrainTypeId], references: [id])
  terrainTypeId String
  Plot          Plot        @relation(fields: [plotId], references: [id])
  plotId        String

  @@index([plotId])
  @@index([terrainTypeId])
}

model TerrainType {
  id      String    @id @default(uuid())
  name    String
  Terrain Terrain[]
}

model Feature {
  id            String      @id @default(uuid())
  createdAt     DateTime    @default(now())
  deletedAt     DateTime?
  featureType   FeatureType @relation(fields: [featureTypeId], references: [id])
  featureTypeId String
  Plot          Plot        @relation(fields: [plotId], references: [id])
  plotId        String

  @@index([plotId])
  @@index([featureTypeId])
}

model FeatureType {
  id      String    @id @default(uuid())
  name    String
  Feature Feature[]
}

// builder.ts
import SchemaBuilder from "@pothos/core";
import { PrismaClient } from "@prisma/client";
import PrismaPlugin from "@pothos/plugin-prisma";
import PrismaUtils from "@pothos/plugin-prisma-utils";

import type PrismaTypes from "@pothos/plugin-prisma/generated";

export const prisma = new PrismaClient();
export const builder = new SchemaBuilder<{
  PrismaTypes: PrismaTypes;
}>({
  plugins: [PrismaPlugin, PrismaUtils],
  prisma: {
    client: prisma,
  },
});

// index.ts
import { builder } from "./builder";
import {
  generateAllCrud,
  generateAllObjects,
  generateAllQueries,
  generateAllMutations,
} from "./generated/autocrud";

generateAllCrud();

builder.queryType({});
builder.mutationType({});

export const schema = builder.toSchema({});

Environment & setup

• OS: Windows 11 22621.2283
• Database: SQL Server
• Node.js version: v18.7.0

Prisma Version

Environment variables loaded from .env
prisma                  : 5.3.1
@prisma/client          : 5.3.1
Current platform        : windows
Query Engine (Node-API) : libquery-engine 61e140623197a131c2a6189271ffee05a7aa9a59 (at node_modules\@prisma\engines\query_engine-windows.dll.node)
Schema Engine           : schema-engine-cli 61e140623197a131c2a6189271ffee05a7aa9a59 (at node_modules\@prisma\engines\schema-engine-windows.exe)
Schema Wasm             : @prisma/prisma-schema-wasm 5.3.1-2.61e140623197a131c2a6189271ffee05a7aa9a59
Default Engines Hash    : 61e140623197a131c2a6189271ffee05a7aa9a59
Studio                  : 0.494.0

(I always use npx prisma)

[janpio]

I can confirm this:

PS C:\Users\Jan\Documents\throwaway\19963> npx prisma migrate dev
Environment variables loaded from .env
Prisma schema loaded from prisma\schema.prisma
Datasource "db": SQL Server database

√ Enter a name for the new migration: ... change
Applying migration `20240522193038_change`
Error: P3018

A migration failed to apply. New migrations cannot be applied before the error is recovered from. Read more about how to resolve migration issues in a production database: https://pris.ly/d/migrate-resolve

Migration name: 20240522193038_change

Database error code: 134

Database error:
The variable name '@SQL' has already been declared. Variable names must be unique within a query batch or stored procedure.

The original schema I used:

And then I changed it to:

model Terrain {
  id            String      @id @default(uuid())
  createdAt     DateTime    @default(now())
  deletedAt     DateTime?
  terrainType   TerrainType @relation(fields: [terrainTypeId], references: [id])
  terrainTypeId Int // change this to Int
  // Plot          Plot        @relation(fields: [plotId], references: [id])
  // plotId        String

  // @@index([plotId])
  @@index([terrainTypeId])
}

model TerrainType {
  id      Int       @id @default(autoincrement()) // change String to Int and uuid() to autoincrement()
  name    String
  Terrain Terrain[]
}

model Feature {
  id            String      @id @default(uuid())
  createdAt     DateTime    @default(now())
  deletedAt     DateTime?
  featureType   FeatureType @relation(fields: [featureTypeId], references: [id])
  featureTypeId Int // change this to Int
  // Plot          Plot        @relation(fields: [plotId], references: [id])
  // plotId        String

  // @@index([plotId])
  @@index([featureTypeId])
}

model FeatureType {
  id      Int       @id @default(autoincrement()) // change String to Int and uuid() to autoincrement()
  name    String
  Feature Feature[]
}

The generated migration SQL:

/*
  Warnings:

  - You are about to alter the column `featureTypeId` on the `Feature` table. The data in that column could be lost. The data in that column will be cast from `NVarChar(1000)` to `Int`.
  - You are about to alter the column `terrainTypeId` on the `Terrain` table. The data in that column could be lost. The data in that column will be cast from `NVarChar(1000)` to `Int`.
  - The primary key for the `TerrainType` table will be changed. If it partially fails, the table could be left without primary key constraint.
  - You are about to alter the column `id` on the `TerrainType` table. The data in that column could be lost. The data in that column will be cast from `String` to `Int`.
  - The primary key for the `FeatureType` table will be changed. If it partially fails, the table could be left without primary key constraint.
  - You are about to alter the column `id` on the `FeatureType` table. The data in that column could be lost. The data in that column will be cast from `String` to `Int`.

*/
BEGIN TRY

BEGIN TRAN;

-- DropForeignKey
ALTER TABLE [dbo].[Feature] DROP CONSTRAINT [Feature_featureTypeId_fkey];

-- DropForeignKey
ALTER TABLE [dbo].[Terrain] DROP CONSTRAINT [Terrain_terrainTypeId_fkey];

-- DropIndex
DROP INDEX [Feature_featureTypeId_idx] ON [dbo].[Feature];

-- DropIndex
DROP INDEX [Terrain_terrainTypeId_idx] ON [dbo].[Terrain];

-- AlterTable
ALTER TABLE [dbo].[Feature] ALTER COLUMN [featureTypeId] INT NOT NULL;

-- AlterTable
ALTER TABLE [dbo].[Terrain] ALTER COLUMN [terrainTypeId] INT NOT NULL;

-- RedefineTables
BEGIN TRANSACTION;
DECLARE @SQL NVARCHAR(MAX) = N''
SELECT @SQL += N'ALTER TABLE '
    + QUOTENAME(OBJECT_SCHEMA_NAME(PARENT_OBJECT_ID))
    + '.'
    + QUOTENAME(OBJECT_NAME(PARENT_OBJECT_ID))
    + ' DROP CONSTRAINT '
    + OBJECT_NAME(OBJECT_ID) + ';'
FROM SYS.OBJECTS
WHERE TYPE_DESC LIKE '%CONSTRAINT'
    AND OBJECT_NAME(PARENT_OBJECT_ID) = 'TerrainType'
    AND SCHEMA_NAME(SCHEMA_ID) = 'dbo'
EXEC sp_executesql @SQL
;
CREATE TABLE [dbo].[_prisma_new_TerrainType] (
    [id] INT NOT NULL IDENTITY(1,1),
    [name] NVARCHAR(1000) NOT NULL,
    CONSTRAINT [TerrainType_pkey] PRIMARY KEY CLUSTERED ([id])
);
SET IDENTITY_INSERT [dbo].[_prisma_new_TerrainType] ON;
IF EXISTS(SELECT * FROM [dbo].[TerrainType])
    EXEC('INSERT INTO [dbo].[_prisma_new_TerrainType] ([id],[name]) SELECT [id],[name] FROM [dbo].[TerrainType] WITH (holdlock tablockx)');
SET IDENTITY_INSERT [dbo].[_prisma_new_TerrainType] OFF;
DROP TABLE [dbo].[TerrainType];
EXEC SP_RENAME N'dbo._prisma_new_TerrainType', N'TerrainType';
DECLARE @SQL NVARCHAR(MAX) = N''
SELECT @SQL += N'ALTER TABLE '
    + QUOTENAME(OBJECT_SCHEMA_NAME(PARENT_OBJECT_ID))
    + '.'
    + QUOTENAME(OBJECT_NAME(PARENT_OBJECT_ID))
    + ' DROP CONSTRAINT '
    + OBJECT_NAME(OBJECT_ID) + ';'
FROM SYS.OBJECTS
WHERE TYPE_DESC LIKE '%CONSTRAINT'
    AND OBJECT_NAME(PARENT_OBJECT_ID) = 'FeatureType'
    AND SCHEMA_NAME(SCHEMA_ID) = 'dbo'
EXEC sp_executesql @SQL
;
CREATE TABLE [dbo].[_prisma_new_FeatureType] (
    [id] INT NOT NULL IDENTITY(1,1),
    [name] NVARCHAR(1000) NOT NULL,
    CONSTRAINT [FeatureType_pkey] PRIMARY KEY CLUSTERED ([id])
);
SET IDENTITY_INSERT [dbo].[_prisma_new_FeatureType] ON;
IF EXISTS(SELECT * FROM [dbo].[FeatureType])
    EXEC('INSERT INTO [dbo].[_prisma_new_FeatureType] ([id],[name]) SELECT [id],[name] FROM [dbo].[FeatureType] WITH (holdlock tablockx)');
SET IDENTITY_INSERT [dbo].[_prisma_new_FeatureType] OFF;
DROP TABLE [dbo].[FeatureType];
EXEC SP_RENAME N'dbo._prisma_new_FeatureType', N'FeatureType';
COMMIT;

-- CreateIndex
CREATE NONCLUSTERED INDEX [Terrain_terrainTypeId_idx] ON [dbo].[Terrain]([terrainTypeId]);

-- CreateIndex
CREATE NONCLUSTERED INDEX [Feature_featureTypeId_idx] ON [dbo].[Feature]([featureTypeId]);

-- AddForeignKey
ALTER TABLE [dbo].[Terrain] ADD CONSTRAINT [Terrain_terrainTypeId_fkey] FOREIGN KEY ([terrainTypeId]) REFERENCES [dbo].[TerrainType]([id]) ON DELETE NO ACTION ON UPDATE CASCADE;

-- AddForeignKey
ALTER TABLE [dbo].[Feature] ADD CONSTRAINT [Feature_featureTypeId_fkey] FOREIGN KEY ([featureTypeId]) REFERENCES [dbo].[FeatureType]([id]) ON DELETE NO ACTION ON UPDATE CASCADE;

COMMIT TRAN;

END TRY
BEGIN CATCH

IF @@TRANCOUNT > 0
BEGIN
    ROLLBACK TRAN;
END;
THROW

END CATCH

Definitely two DECLARE @SQL NVARCHAR(MAX) = N'' in there.

So changing the type of two primary keys that are part of a relation seems to trigger this.