I’m running into an issue with Prisma + SQL Server when using a custom schema (not dbo)
Here’s what I’m doing:
• I’m using a custom schema called custom_schema and mapped my models with @@map or @map("custom_schema.TableName").
• I manually created the schema custom_schema in the database before running the initial migration.
• Running the initial migration with prisma migrate dev on a clean database worked fine. It created the custom_schema tables and the _prisma_migrations table, and the migration completed successfully.
• Later, when I tried to create a second migration using pnpm prisma migrate dev --name test_migration, I ran into this error:
I understand that Prisma drops and recreates the shadow database during migrate dev, and since Prisma doesn’t auto-create custom schemas in SQL Server, the custom_schema schema doesn’t exist when it tries to apply the migration to the shadow DB—hence the error.
I also tried specifying a shadowDatabaseUrl manually and pre-creating the schema there, but since the shadow DB gets wiped and recreated by Prisma anyway, that didn’t help either.
Is there an official or recommended way to use custom schemas with SQL Server in Prisma and still be able to use migrate dev reliably?
Thanks in advance for any insights
• I’m using a custom schema called custom_schema and mapped my models with @@map or @map("custom_schema.TableName").
• I manually created the schema custom_schema in the database before running the initial migration.
• Running the initial migration with prisma migrate dev on a clean database worked fine. It created the custom_schema tables and the _prisma_migrations table, and the migration completed successfully.
• Later, when I tried to create a second migration using pnpm prisma migrate dev --name test_migration, I ran into this error:
I understand that Prisma drops and recreates the shadow database during migrate dev, and since Prisma doesn’t auto-create custom schemas in SQL Server, the custom_schema schema doesn’t exist when it tries to apply the migration to the shadow DB—hence the error.
I also tried specifying a shadowDatabaseUrl manually and pre-creating the schema there, but since the shadow DB gets wiped and recreated by Prisma anyway, that didn’t help either.
Is there an official or recommended way to use custom schemas with SQL Server in Prisma and still be able to use migrate dev reliably?
Thanks in advance for any insights
