Cannot migrate changes on Multi Schema setup for SQL Server

[Chibionos]

Bug description

When trying to migrate a Schema that is mapped to the non default schema in SQL Server.
I am getting an error where the migration tries to find the table on the default schema dbo. It is not honoring the custom schema the table is placed in. 

Change I am trying to migrate is make the id property unique and add the NVarChar(450)

Schema

model Report {
  id                  String  @id @unique @db.NVarChar(450)
  title               String
  description         String
  chartType           String? // "line" | "bar" | "pie"
  certificationStatus String // "certified" | "pending" | "uncertified"
  queryMapping        String

  metric        CertifiedMetricVersion @relation(fields: [metricId], references: [Id])
  metricId      Int
  ReportVersion ReportVersion[]

  @@map("Report")
  @@schema("cm")
}

PS D:\r\report-boi> npx prisma migrate dev
Environment variables loaded from .env
Prisma schema loaded from prisma\schema.prisma
Datasource "db": SQL Server database, schemas "cd, cm, gs, sql, ts"

Error: P1014

The underlying table for model `dbo.Report` does not exist.

How to reproduce

Create a schema with a custom schema in SQL Server and try migrating a change.

Expected behavior

Migration should find the right table and generate the migration scripts needed to make the migration possible.

Prisma information

generator client {
  provider        = "prisma-client-js"
  previewFeatures = ["multiSchema"]
}

datasource db {
  provider          = "sqlserver"
  url               = env("DATABASE_URL")
  shadowDatabaseUrl = env("SHADOW_DATABASE_URL")
  schemas           = ["cd", "cm", "gs", "sql", "ts"]
}

model Report {
  id                  String  @id @unique @db.NVarChar(450)
  title               String
  description         String
  chartType           String? // "line" | "bar" | "pie"
  certificationStatus String // "certified" | "pending" | "uncertified"
  queryMapping        String

  metric        CertifiedMetricVersion @relation(fields: [metricId], references: [Id])
  metricId      Int
  ReportVersion ReportVersion[]

  @@map("Report")
  @@schema("cm")
}

PS D:\r\report-boi> npx prisma migrate dev
Environment variables loaded from .env
Prisma schema loaded from prisma\schema.prisma
Datasource "db": SQL Server database, schemas "cd, cm, gs, sql, ts"

Error: P1014

The underlying table for model `dbo.Report` does not exist.

Environment & setup

• OS: Windows
• Database: SQL Server
• Node.js version: v18.19.0

Prisma Version

PS D:\r\report-boi> prisma -v
Environment variables loaded from .env
prisma                  : 5.13.0
@prisma/client          : 5.13.0
Computed binaryTarget   : windows
Operating System        : win32
Architecture            : x64
Node.js                 : v18.19.0
Query Engine (Node-API) : libquery-engine b9a39a7ee606c28e3455d0fd60e78c3ba82b1a2b (at C:\ProgramData\nvm\v18.19.0\node_modules\prisma\node_modules\@prisma\engines\query_engine-windows.dll.node)
Schema Engine           : schema-engine-cli b9a39a7ee606c28e3455d0fd60e78c3ba82b1a2b (at C:\ProgramData\nvm\v18.19.0\node_modules\prisma\node_modules\@prisma\engines\schema-engine-windows.exe)
Schema Wasm             : @prisma/prisma-schema-wasm 5.13.0-23.b9a39a7ee606c28e3455d0fd60e78c3ba82b1a2b
Default Engines Hash    : b9a39a7ee606c28e3455d0fd60e78c3ba82b1a2b
Studio                  : 0.500.0
Preview Features        : multiSchema

[janpio]

Can you share the generated migration sql of prisma migrate dev --create-only, or does that also already fail?

[janpio]

Can confirm.

Initial schema:

generator client {
  provider        = "prisma-client-js"
  previewFeatures = ["multiSchema"]
}

datasource db {
  provider          = "sqlserver"
  url               = env("DATABASE_URL")
  // shadowDatabaseUrl = env("SHADOW_DATABASE_URL")
  schemas           = ["cd", "cm", "gs", "sql", "ts"]
}

model Report {
  id                  String  @id // @unique @db.NVarChar(450)
  title               String
  description         String
  chartType           String? // "line" | "bar" | "pie"
  certificationStatus String // "certified" | "pending" | "uncertified"
  queryMapping        String

  // metric        CertifiedMetricVersion @relation(fields: [metricId], references: [Id])
  // metricId      Int
  // ReportVersion ReportVersion[]

  @@map("Report")
  @@schema("cm")
}

Creates SQL migration:

BEGIN TRY

BEGIN TRAN;

-- CreateSchema
EXEC sp_executesql N'CREATE SCHEMA [cd];';;

-- CreateSchema
EXEC sp_executesql N'CREATE SCHEMA [cm];';;

-- CreateSchema
EXEC sp_executesql N'CREATE SCHEMA [gs];';;

-- CreateSchema
EXEC sp_executesql N'CREATE SCHEMA [sql];';;

-- CreateSchema
EXEC sp_executesql N'CREATE SCHEMA [ts];';;

-- CreateTable
CREATE TABLE [cm].[Report] (
    [id] NVARCHAR(1000) NOT NULL,
    [title] NVARCHAR(1000) NOT NULL,
    [description] NVARCHAR(1000) NOT NULL,
    [chartType] NVARCHAR(1000),
    [certificationStatus] NVARCHAR(1000) NOT NULL,
    [queryMapping] NVARCHAR(1000) NOT NULL,
    CONSTRAINT [Report_pkey] PRIMARY KEY CLUSTERED ([id])
);

COMMIT TRAN;

END TRY
BEGIN CATCH

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

END CATCH

Then small change to schema, make @id column unique and native type:

generator client {
  provider        = "prisma-client-js"
  previewFeatures = ["multiSchema"]
}

datasource db {
  provider          = "sqlserver"
  url               = env("DATABASE_URL")
  // shadowDatabaseUrl = env("SHADOW_DATABASE_URL")
  schemas           = ["cd", "cm", "gs", "sql", "ts"]
}

model Report {
  id                  String  @id @unique @db.NVarChar(450)
  title               String
  description         String
  chartType           String? // "line" | "bar" | "pie"
  certificationStatus String // "certified" | "pending" | "uncertified"
  queryMapping        String

  // metric        CertifiedMetricVersion @relation(fields: [metricId], references: [Id])
  // metricId      Int
  // ReportVersion ReportVersion[]

  @@map("Report")
  @@schema("cm")
}

leads to:

> npx prisma migrate dev
Environment variables loaded from .env
Prisma schema loaded from prisma\schema.prisma
Datasource "db": SQL Server database, schemas "cd, cm, gs, sql, ts"

Error: P1014

The underlying table for model `dbo.Report` does not exist.

[janpio]

The problem is not in diffing, that works correctly when called standalone:

> npx prisma migrate diff --from-schema-datasource prisma/schema.prisma --to-schema-datamodel prisma/schema.prisma

[*] Changed the `Report` table
  [-] Dropped the primary key on columns (id)
  [*] Altered column `id` (type changed)
  [+] Added primary key on columns (id)
)
  [+] Added unique index on columns (id)

PS C:\Users\Jan\Documents\throwaway\19963> npx prisma migrate diff --from-schema-datasource prisma/schema.prisma --to-schema-datamodel prisma/schema.prisma --script
BEGIN TRY

BEGIN TRAN;

-- AlterTable
ALTER TABLE [cm].[Report] DROP CONSTRAINT [Report_pkey];
ALTER TABLE [cm].[Report] ALTER COLUMN [id] NVARCHAR(450) NOT NULL;
ALTER TABLE [cm].[Report] ADD CONSTRAINT Report_pkey PRIMARY KEY CLUSTERED ([id]);

-- CreateIndex
ALTER TABLE [cm].[Report] ADD CONSTRAINT [Report_id_key] UNIQUE NONCLUSTERED ([id]);

COMMIT TRAN;

END TRY
BEGIN CATCH

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

END CATCH