multiSchema does not work with SQL Server schemas with dot in name (= databases)

[chagriani]

Bug description

In sqlserver when declaring several schemas, it was not possible to perform a select. There was a table not found error in the database

How to reproduce

(#15077 (comment))

Expected behavior

No response

Prisma information

Its work

datasource db {
    provider = "sqlserver"
    url      = env("DATABASE_URL")
    schemas  = ["database1].[dbo", "database2].[dbo"]
}

Does not work

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

prismaClient.table1.findMany()

Environment & setup

• OS: Windows
• Database: SQL Server
• Node.js version: v16.14.0

Prisma Version

v4.4.0

[Jolg42]

Note from Microsoft's docs about dbo
https://learn.microsoft.com/en-us/sql/relational-databases/security/authentication-access/principals-database-engine?view=sql-server-ver15#dbo-user-and-dbo-schema

The dbo user is a special user principal in each database. All SQL Server administrators, members of the sysadmin fixed server role, sa login, and owners of the database, enter databases as the dbo user. The dbo user has all permissions in the database and cannot be limited or dropped. dbo stands for database owner, but the dbouser account is not the same as the db_owner fixed database role, and the db_owner fixed database role is not the same as the user account that is recorded as the owner of the database.
The dbo user owns the dbo schema. The dbo schema is the default schema for all users, unless some other schema is specified. The dbo schema cannot be dropped.

[janpio]

Note: The schema string that "works" actually includes database names: schemas = ["database1].[dbo", "database2].[dbo"]

@chagriani Is it correct that you want to combine two schemas from different databases? Does SQL Server even allow foreign keys and queries across database boundaries?

[chagriani]

Yes I am combining 2 schemas for different database.

SQL Server does not allow cross-database foreign keys, but allows queries between them.

SELECT *
  FROM database1.dbo.table1 as tb1
  inner join database2.dbo.table2 as tb2 on tb1.id=tb2.id_tb1

[chagriani]

Sorry, now I understand the schema concept and why multiSchema. In my use case I have several databases where they communicate with each other, but they have no relationship.

In Prisma, willingly or not, it was possible to query between databases with this new multiSchema architecture with this declaration schemas = ["database1].[dbo", "database2].[dbo"]

[janpio]

Yeah, that makes sense. You "hacked" our system 👍 😆

I fear we will probably have to limit that first, before we can properly enable this. Right now the foreign keys are a important requirement for a relation in Prisma. We will open that up sooner or later, and then that would also enable doing multiDatabase for SQL Server with databases on the same server.

[chagriani]

You don't know how happy the return of this consultation made me.

But that's ok. I will wait for multiDatabase.

Thank you so much

[Druue]

There was a table not found error in the database

Hey @chagriani, could you please share the specific error that you're running into?

[pimeys]

Naturally this breaks in every engine. Here's what introspection engine gives us:

Incorrect syntax near '.'. code=102
Error: Incorrect syntax near '.'.
   0: sql_migration_connector::apply_migration::migration_step
           with step=CreateSchema(NamespaceId(0))
             at migration-engine/connectors/sql-migration-connector/src/apply_migration.rs:21
   1: sql_migration_connector::apply_migration::apply_migration
             at migration-engine/connectors/sql-migration-connector/src/apply_migration.rs:10
   2: migration_core::state::SchemaPush
             at migration-engine/core/src/state.rs:402

We do not support multi-database schemas with any database. But, you should try out Prisma 4.8.0 tomorrow that does let you to migrate and introspect multi-schema databases with SQL Server.

[eviefp]

multiSchema is not for cross-database schemas. The feature is specifically for being able to use multiple schemas within the same database. There are a few good reasons for this, primarily the fact that a lot of connectors/database engines heavily differentiate between the two concepts (e.g., being able to create foreign keys, relationships, joins, etc.).

What this issue shows is a hacky way to allow tracking models in separate databases using multiSchema. This is not the intended use-case, which is pretty evident by the way you have to write up the schema name (e.g. "db_name].[schema_name").

So while we won't officially support this, we will also not add code to specifically detect and error in this case.

⚠️ BEWARE ⚠️

• this is not officially supported
• it may or may not work on connectors
• it may randomly break anything
• it may break at any time without any sort of prior warning or without any connection to us actually implementing multiple database support

So use at your own risk.