multiSchema: migrate reset with sqlserver does not delete second schema

[shmuelsochet]

Bug description

When using multSchema with sqlserver, running migrate reset produces the error Database error: There is already an object named 'schemaName' in the database.

How to reproduce

1. Use multischema
2. Create migration
3. Run npx prisma migrate reset

Expected behavior

The 2nd schema along with its tables should be deleted.

More notes

I created one migration that creates the schema along with a table.
I tried it without the table and the same error occurs.
The first schema I'm using is dbo.

Here's what the migration, with just the schema creation, looks like...

BEGIN TRY

BEGIN TRAN;

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

COMMIT TRAN;

END TRY
BEGIN CATCH

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

END CATCH

I tried wrapping the -- CreateSchema in an if statement. When I do that it doesn't error on the schema but it does on the table. The same type of error...

Database error: There is already an object named 'tableName' in the database.

It appears that the reset is not deleting the 2nd schema's objects.

This issue seems to be similar but it's closed as completed, #16561. Maybe it's only supposed to work for postgres.

Environment & setup

• OS: Docker node:18-alpine
• Database: SQL Server 2019
• Node.js version: 18.8.0

Prisma Version

prisma                  : 4.8.0
@prisma/client          : 4.8.0
Current platform        : linux-musl
Query Engine (Node-API) : libquery-engine d6e67a83f971b175a593ccc12e15c4a757f93ffe (at node_modules/@prisma/engines/libquery_engine-linux-musl.so.node)
Migration Engine        : migration-engine-cli d6e67a83f971b175a593ccc12e15c4a757f93ffe (at node_modules/@prisma/engines/migration-engine-linux-musl)
Introspection Engine    : introspection-core d6e67a83f971b175a593ccc12e15c4a757f93ffe (at node_modules/@prisma/engines/introspection-engine-linux-musl)
Format Binary           : prisma-fmt d6e67a83f971b175a593ccc12e15c4a757f93ffe (at node_modules/@prisma/engines/prisma-fmt-linux-musl)
Format Wasm             : @prisma/prisma-fmt-wasm 4.8.0-61.d6e67a83f971b175a593ccc12e15c4a757f93ffe
Default Engines Hash    : d6e67a83f971b175a593ccc12e15c4a757f93ffe
Studio                  : 0.479.0
Preview Features        : metrics, multiSchema

[Jolg42]

Thanks for this issue! I think this is currently expected as support for SQL Server is currently being worked on for multiSchema.
So you might need to wait for the next release for SQL Server

[YaakovR]

@Jolg42 Any idea when multi-schema support will be fully implemented for SQL Server. Not being able to use migrations, pretty much handicaps this feature.

[Jolg42]

@YaakovR Cannot 100% guarantee it, but very likely support lands in next 4.9.0 release on Tuesday 17th, January 😉
So very soon!

[pimeys]

Yup. Happens with 4.8.0 (which officially does not yet support SQL Server multiSchema).

Repro datamodel:

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

datasource db {
  provider = "sqlserver"
  url      = env("DATABASE_URL")
  schemas  = ["schemaName", "otherName"]
}

model A {
  id Int @id

  @@schema("schemaName")
}

model B {
  id Int @id

  @@schema("otherName")
}

Steps:

• Start a fresh SQL Server 2022 database in docker
• npx prisma migrate dev to create the initial migartion
• npx prisma migrate reset to see the error:

[pimeys@naunau:~/code/prisma-engines/pt]$ npx prisma migrate reset
Warning Precompiled engine files are not available for nixos.
Environment variables loaded from .env
Prisma schema loaded from prisma/schema.prisma
Datasource "db" - SQL Server

✔ Are you sure you want to reset your database? All data will be lost. … yes

Applying migration `20230110161338_init`
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: 20230110161338_init

Database error code: 2714

Database error:
There is already an object named 'otherName' in the database.

Now, luckily when the next version is approaching, we did fix the reset already. The current development versions reset all the schemas cleanly.