Unable to introspect a certain database schema when combined with others using multiSchema feature

[danielwarke]

Bug description

When I use prisma db pull to introspect my sql server database using the multiSchema feature, I am unable to combine the Trips database schema with any of my other schemas. When I list the Trips schema by itself, I am able to introspect it correctly, but when I combine it with other schemas and run prisma db pull again, it removes the introspected models from the Trips schema.

How to reproduce

1. Enable multiSchema preview feature as outlined here https://www.prisma.io/docs/guides/other/multi-schema
2. Configure db datasource in schema.prisma file to include multiple schemas including Trips

datasource db {
  provider = "sqlserver"
  url      = env("PRISMA_DATABASE_URL")
  schemas  = ["Appointments", "Trips", "core"]
}

3. Run prisma db pull to introspect the database
4. The created schema will not include any models from Trips database schema
5. Update the db datasource to only include the Trips schema

datasource db {
  provider = "sqlserver"
  url      = env("PRISMA_DATABASE_URL")
  schemas  = ["Trips"]
}

6. Run prisma db pull --force to introspect the database again and clear out the previous models
7. The created schema will include the models from the Trips database schema

Expected behavior

The Trips database schema should be successfully introspected along with any combination of other valid database schemas.

Prisma information

When introspecting other schemas along with Trips:

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

generator prismaClassGenerator {
  provider               = "prisma-class-generator"
  output                 = "../libs/database/src/prisma-class"
  separateRelationFields = "true"
  dryRun                 = "false"
}

datasource db {
  provider = "sqlserver"
  url      = env("PRISMA_DATABASE_URL")
  schemas  = ["Appointments", "Trips", "core"]
}

model Associations {
  AppointmentID           BigInt
  AssociatedAppointmentID BigInt
  AssociationTypeID       Int    @db.SmallInt
  Priority                Int    @db.SmallInt

  @@id([AppointmentID, AssociatedAppointmentID], map: "PK_Associations")
  @@schema("Appointments")
}

model AssociationTypes {
  ID   Int    @id(map: "PK_AssociationTypes") @default(autoincrement()) @db.SmallInt
  Name String @db.NVarChar(50)

  @@schema("Appointments")
}

model billCodes {
  Id             BigInt  @id(map: "PK_AppointmentBillCode") @default(autoincrement())
  HayesId        String? @db.NVarChar(128)
  Article        String? @db.NVarChar(128)
  Description    String? @db.NVarChar(128)
  SP_ProjectType String? @db.NVarChar(20)
  LTLallowed     Boolean @default(false, map: "DF_AppointmentBillCode_LTLallowed")
  FTLallowed     Boolean @default(false, map: "DF_AppointmentBillCode_FTLallowed")
  CustomerID     Int?    @db.SmallInt

  @@schema("Appointments")
}

model ChargeTypes {
  ID              Int                      @id(map: "PK_ChargeTypes") @default(autoincrement()) @db.TinyInt
  Name            String                   @db.NVarChar(50)
  BOL_Description String?                  @db.NVarChar(128)
  rv              Unsupported("timestamp") @map("_rv")

  @@schema("Appointments")
}

model Clusters {
  ID       Int     @id(map: "PK_Clusters") @default(autoincrement())
  Name     String  @db.NVarChar(128)
  TenantID Int
  Sites    Sites[]

  @@schema("core")
}

model Containers {
  ID          Int    @id(map: "PK_Containers") @default(autoincrement()) @db.SmallInt
  TenantID    Int
  Description String @db.NVarChar(100)
  Width       Int
  Length      Int
  Units       String @db.NVarChar(10)
  Area        Int?

  @@schema("core")
}

model Documents {
  ID              BigInt                   @id(map: "PK_AppointmentBOLs") @default(autoincrement())
  DocumentTypeID  Int                      @default(1, map: "DF_AppointmentBOLs_DocumentTypeID") @db.SmallInt
  ApptID          BigInt
  Name            String                   @db.NVarChar(256)
  Path            String?                  @db.NVarChar(Max)
  CreationDate    DateTime                 @db.SmallDateTime
  CreatedByUserID Int
  TemplateVersion Int?
  rv              Unsupported("timestamp") @map("_rv")
  WebPath         String?                  @db.NVarChar(Max)
  SsrsUrl         String?                  @db.NVarChar(Max)
  FilePath        String                   @db.NVarChar(Max)

  @@index([DocumentTypeID, ApptID], map: "<Name of Missing Index, sysname,>")
  @@schema("Appointments")
}

model DocumentTypes {
  ID       Int                      @id(map: "PK_DocumentTypes_1") @default(autoincrement()) @db.SmallInt
  Name     String                   @db.NVarChar(128)
  Report   String?                  @db.NVarChar(128)
  rv       Unsupported("timestamp") @map("_rv")
  Inbound  Boolean?
  Outbound Boolean?
  Limit    Int?

  @@schema("Appointments")
}

model ItineraryTypes {
  ID   Int    @id(map: "PK_ItineraryTypes") @default(autoincrement()) @db.SmallInt
  Name String @db.NVarChar(50)

  @@schema("Appointments")
}

model Queues {
  Id          BigInt                   @id(map: "PK_Queues") @default(autoincrement())
  SiteID      BigInt?
  QueueTypeID Int?                     @db.TinyInt
  Location    String?                  @unique(map: "IX_Locations") @db.NVarChar(128)
  Name        String?                  @db.NVarChar(128)
  Direction   String?                  @db.VarChar(6)
  Active      Boolean?
  rv          Unsupported("timestamp") @map("_rv")
  Export      Boolean?

  @@schema("Appointments")
}

model QueueTypes {
  ID          Int                      @id(map: "PK_QueueTypes") @default(autoincrement()) @db.TinyInt
  Name        String                   @db.NVarChar(50)
  Description String?                  @db.NVarChar(128)
  rv          Unsupported("timestamp") @map("_rv")

  @@schema("Appointments")
}

model Settings {
  ID    Int                      @id(map: "PK_Settings") @default(autoincrement())
  Name  String                   @db.NVarChar(255)
  Value String?                  @db.NVarChar(Max)
  Notes String?                  @db.NVarChar(Max)
  rv    Unsupported("timestamp") @map("_rv")

  @@schema("Appointments")
}

model Settings_Overrides {
  ID                   Int      @id(map: "PK_Settings_Override") @default(autoincrement())
  SettingID            Int
  Active               Boolean
  TenantID             Int?     @db.SmallInt
  SiteID               Int?     @db.SmallInt
  CustomerID           Int?     @db.SmallInt
  QueueID              Int?     @db.SmallInt
  Direction            String?  @db.NVarChar(10)
  LiveLoad             Boolean?
  PreviousApptStatusID Int?     @db.SmallInt
  ApptStatusID         Int?     @db.SmallInt
  Value                String   @db.NVarChar(Max)

  @@schema("Appointments")
}

model Sites {
  ID            BigInt                   @id(map: "PK_Sites") @default(autoincrement())
  Name          String?                  @db.NVarChar(128)
  StreetAddress String?                  @db.NVarChar(128)
  City          String?                  @db.NVarChar(128)
  State         String?                  @db.NVarChar(128)
  PostalCode    String?                  @db.NVarChar(15)
  WMShipPoint   String?                  @db.NVarChar(20)
  LTLout        Boolean                  @default(false, map: "DF_Sites_LTLout")
  rv            Unsupported("timestamp") @map("_rv")
  SiteName      String?                  @db.NVarChar(128)
  TenantID      Int?
  ParentID      Int?
  Code          String?                  @db.NVarChar(10)
  Active        Boolean?
  WhseNumber    String?                  @db.NVarChar(50)
  TimeZone      String?                  @db.NVarChar(50)
  TZ            String?                  @db.NVarChar(10)
  ClusterID     Int?
  Clusters      Clusters?                @relation(fields: [ClusterID], references: [ID], onUpdate: NoAction, map: "FK_Sites_Clusters")

  @@schema("core")
}

model statuses {
  ID               Int     @id(map: "PK_AppointmentStatuses") @default(autoincrement()) @db.SmallInt
  Name             String? @db.NVarChar(50)
  Status           Int?
  Description      String  @db.NVarChar(51)
  DockDoorRequired Boolean @default(false, map: "DF_AppointmentStatuses_DockDoorRequred")
  DockTypeID       Int?    @db.SmallInt

  @@schema("Appointments")
}

When introspecting only Trips schema:

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

generator prismaClassGenerator {
  provider               = "prisma-class-generator"
  output                 = "../libs/database/src/prisma-class"
  separateRelationFields = "true"
  dryRun                 = "false"
}

datasource db {
  provider = "sqlserver"
  url      = env("PRISMA_DATABASE_URL")
  schemas  = ["Trips"]
}

model Transfers {
  ID               BigInt    @id(map: "PK_Transfers") @default(autoincrement())
  LicensePlateID   BigInt
  OriginApptID     BigInt
  DestApptID       BigInt
  LoadDate         DateTime? @db.DateTime
  LoadedByUserID   Int?
  CompletionDate   DateTime? @db.DateTime
  UnloadDate       DateTime? @db.DateTime
  UnloadedByUserID Int?

  @@schema("Trips")
}

model Trips {
  ID         BigInt    @id(map: "PK_Trips") @default(autoincrement())
  TripTypeID Int
  TripName   String    @db.NVarChar(120)
  TripTypes  TripTypes @relation(fields: [TripTypeID], references: [ID], onUpdate: NoAction, map: "FK_Trips_TripTypes")

  @@schema("Trips")
}

model TripTypes {
  ID       Int     @id(map: "PK_TripTypes") @default(autoincrement())
  Name     String  @db.NVarChar(128)
  TenantID Int
  Trips    Trips[]

  @@schema("Trips")
}

Environment & setup

• OS: macOS
• Database: SQL Server
• Node.js version: 16.20.0

Prisma Version

prisma                  : 4.15.0
@prisma/client          : 4.15.0
Current platform        : darwin-arm64
Query Engine (Node-API) : libquery-engine 8fbc245156db7124f997f4cecdd8d1219e360944 (at node_modules/.pnpm/@prisma+engines@4.15.0/node_modules/@prisma/engines/libquery_engine-darwin-arm64.dylib.node)
Migration Engine        : migration-engine-cli 8fbc245156db7124f997f4cecdd8d1219e360944 (at node_modules/.pnpm/@prisma+engines@4.15.0/node_modules/@prisma/engines/migration-engine-darwin-arm64)
Format Wasm             : @prisma/prisma-fmt-wasm 4.15.0-28.8fbc245156db7124f997f4cecdd8d1219e360944
Default Engines Hash    : 8fbc245156db7124f997f4cecdd8d1219e360944
Studio                  : 0.484.0
Preview Features        : multiSchema

[janpio]

Is there anything weird or unexpected about the Trips schema?
If you create these three schemas, but only 1 table for each, on a new database - does the same problem occur?
Can you maybe share the SQL needed to create all these schemas and their tables?

[danielwarke]

Nothing weird about the Trips schema.
Yes the same problem occurs on a new database with only 1 table in each schema.
I could share the SQL needed, but I think I have narrowed down the issue through some trail and error. I believe that (strangely enough) the issue happens anytime you have a database schema which starts with an uppercase letter "T". I was able to create a lowercase trips schema and everything worked perfectly (I also tried tri, rip, and trip, all worked). When using schemas named TripMgmt, Test, or Taco, the issue was present again. I also created another PascalCase schema Guac and things worked perfectly using that one as well (the PascalCase schema Appointments never had issues either).

[janpio]

There is no "what the heck!?" reaction emoji, so I am making it explicit by posting a comment.

[ghost]

+1 Can confirm the exact same scenario. I had key links between schemas and had to delete them all before db pull would work. But even then I had to pull schemas individually and copy and paste because even though they were in the schemas array they wouldn't show up

[galizhan]

have the same error

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

datasource db {
  provider = "sqlserver"
  url      = env("DATABASE_URL")
  schemas  = ["NSI", "SUR", "dbo", "shep", "smart_scripts"]
}

it takes only NSI schema
I have this kind of warning, but never the less this tables are in generated schema

*** WARNING ***

The following models were ignored as they do not have a valid unique identifier or id. This is currently not supported by the Prisma Client:
  - "hAutoaggressionDamageDegrees"
  - "hAutoaggressionDamageTypes"
  - "hAutoaggressionHospitalizedTypes"

[rujorgensen]

I'm experiencing a similar issue with this datasource:

datasource db {
    provider = "sqlserver"
    url      = env("DATABASE_URL")
    schemas  = ["Inventory", "Resources", "snake_case_schema"]
}

Models for all the schemas except snake_case_schema seems to be generated correctly.

[aliighahremanii]

I have exactly the same problem. Not solved yet?

datasource db {
  provider = "sqlserver"
  url      = env("DATABASE_URL")
  schemas  = ["Access", "BOL", "CntrInv", "Crm", "Finance", "Invoice", "Archive", "Shipping", "dbo"]
}

Finance not generated!!
If I add another schema to schemas array, Finance will be generated and dbo will not be generated.

prisma                  : 5.5.2
@prisma/client          : 5.5.2
Current platform        : rhel-openssl-3.0.x
Query Engine (Node-API) : libquery-engine aebc046ce8b88ebbcb45efe31cbe7d06fd6abc0a (at node_modules/@prisma/engines/libquery_engine-rhel-openssl-3.0.x.so.node)
Schema Engine           : schema-engine-cli aebc046ce8b88ebbcb45efe31cbe7d06fd6abc0a (at node_modules/@prisma/engines/schema-engine-rhel-openssl-3.0.x)
Schema Wasm             : @prisma/prisma-schema-wasm 5.5.1-1.aebc046ce8b88ebbcb45efe31cbe7d06fd6abc0a
Default Engines Hash    : aebc046ce8b88ebbcb45efe31cbe7d06fd6abc0a
Studio                  : 0.494.0
Preview Features        : multiSchema

SqlServer: 2022
Nodejs: v21.1.0

[jmarbutt]

I am also having this issue when doing an npx prisma db pull

[CodeMasterZeroOne]

Same issue here, when npx prisma db pull one by one without multiSchema it works but when adding previewFeatures = ["multiSchema"] and an array of databases then the npx prisma db pull fails with Error:
P4001 The introspected database was empty:

[Druue]

@janpio

Is there anything weird or unexpected about the Trips schema?
If you create these three schemas, but only 1 table for each, on a new database - does the same problem occur?
Can you maybe share the SQL needed to create all these schemas and their tables?

You don't even need special SQL in this case, I managed to reproduce this by just:

• prisma db push
  Using the full schema:

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

generator prismaClassGenerator {
    provider               = "prisma-class-generator"
    output                 = "../libs/database/src/prisma-class"
    separateRelationFields = "true"
    dryRun                 = "false"
}

datasource db {
    provider = "sqlserver"
    url      = env("TEST_MSSQL_JDBC_URI")
    schemas  = ["Appointments", "Trips", "core"]
}

model Associations {
    AppointmentID           BigInt
    AssociatedAppointmentID BigInt
    AssociationTypeID       Int    @db.SmallInt
    Priority                Int    @db.SmallInt

    @@id([AppointmentID, AssociatedAppointmentID], map: "PK_Associations")
    @@schema("Appointments")
}

model AssociationTypes {
    ID   Int    @id(map: "PK_AssociationTypes") @default(autoincrement()) @db.SmallInt
    Name String @db.NVarChar(50)

    @@schema("Appointments")
}

model billCodes {
    Id             BigInt  @id(map: "PK_AppointmentBillCode") @default(autoincrement())
    HayesId        String? @db.NVarChar(128)
    Article        String? @db.NVarChar(128)
    Description    String? @db.NVarChar(128)
    SP_ProjectType String? @db.NVarChar(20)
    LTLallowed     Boolean @default(false, map: "DF_AppointmentBillCode_LTLallowed")
    FTLallowed     Boolean @default(false, map: "DF_AppointmentBillCode_FTLallowed")
    CustomerID     Int?    @db.SmallInt

    @@schema("Appointments")
}

model ChargeTypes {
    ID              Int                      @id(map: "PK_ChargeTypes") @default(autoincrement()) @db.TinyInt
    Name            String                   @db.NVarChar(50)
    BOL_Description String?                  @db.NVarChar(128)
    rv              Unsupported("timestamp") @map("_rv")

    @@schema("Appointments")
}

model Clusters {
    ID       Int     @id(map: "PK_Clusters") @default(autoincrement())
    Name     String  @db.NVarChar(128)
    TenantID Int
    Sites    Sites[]

    @@schema("core")
}

model Containers {
    ID          Int    @id(map: "PK_Containers") @default(autoincrement()) @db.SmallInt
    TenantID    Int
    Description String @db.NVarChar(100)
    Width       Int
    Length      Int
    Units       String @db.NVarChar(10)
    Area        Int?

    @@schema("core")
}

model Documents {
    ID              BigInt                   @id(map: "PK_AppointmentBOLs") @default(autoincrement())
    DocumentTypeID  Int                      @default(1, map: "DF_AppointmentBOLs_DocumentTypeID") @db.SmallInt
    ApptID          BigInt
    Name            String                   @db.NVarChar(256)
    Path            String?                  @db.NVarChar(Max)
    CreationDate    DateTime                 @db.SmallDateTime
    CreatedByUserID Int
    TemplateVersion Int?
    rv              Unsupported("timestamp") @map("_rv")
    WebPath         String?                  @db.NVarChar(Max)
    SsrsUrl         String?                  @db.NVarChar(Max)
    FilePath        String                   @db.NVarChar(Max)

    @@index([DocumentTypeID, ApptID], map: "<Name of Missing Index, sysname,>")
    @@schema("Appointments")
}

model DocumentTypes {
    ID       Int                      @id(map: "PK_DocumentTypes_1") @default(autoincrement()) @db.SmallInt
    Name     String                   @db.NVarChar(128)
    Report   String?                  @db.NVarChar(128)
    rv       Unsupported("timestamp") @map("_rv")
    Inbound  Boolean?
    Outbound Boolean?
    Limit    Int?

    @@schema("Appointments")
}

model ItineraryTypes {
    ID   Int    @id(map: "PK_ItineraryTypes") @default(autoincrement()) @db.SmallInt
    Name String @db.NVarChar(50)

    @@schema("Appointments")
}

model Queues {
    Id          BigInt                   @id(map: "PK_Queues") @default(autoincrement())
    SiteID      BigInt?
    QueueTypeID Int?                     @db.TinyInt
    Location    String?                  @unique(map: "IX_Locations") @db.NVarChar(128)
    Name        String?                  @db.NVarChar(128)
    Direction   String?                  @db.VarChar(6)
    Active      Boolean?
    rv          Unsupported("timestamp") @map("_rv")
    Export      Boolean?

    @@schema("Appointments")
}

model QueueTypes {
    ID          Int                      @id(map: "PK_QueueTypes") @default(autoincrement()) @db.TinyInt
    Name        String                   @db.NVarChar(50)
    Description String?                  @db.NVarChar(128)
    rv          Unsupported("timestamp") @map("_rv")

    @@schema("Appointments")
}

model Settings {
    ID    Int                      @id(map: "PK_Settings") @default(autoincrement())
    Name  String                   @db.NVarChar(255)
    Value String?                  @db.NVarChar(Max)
    Notes String?                  @db.NVarChar(Max)
    rv    Unsupported("timestamp") @map("_rv")

    @@schema("Appointments")
}

model Settings_Overrides {
    ID                   Int      @id(map: "PK_Settings_Override") @default(autoincrement())
    SettingID            Int
    Active               Boolean
    TenantID             Int?     @db.SmallInt
    SiteID               Int?     @db.SmallInt
    CustomerID           Int?     @db.SmallInt
    QueueID              Int?     @db.SmallInt
    Direction            String?  @db.NVarChar(10)
    LiveLoad             Boolean?
    PreviousApptStatusID Int?     @db.SmallInt
    ApptStatusID         Int?     @db.SmallInt
    Value                String   @db.NVarChar(Max)

    @@schema("Appointments")
}

model Sites {
    ID            BigInt                   @id(map: "PK_Sites") @default(autoincrement())
    Name          String?                  @db.NVarChar(128)
    StreetAddress String?                  @db.NVarChar(128)
    City          String?                  @db.NVarChar(128)
    State         String?                  @db.NVarChar(128)
    PostalCode    String?                  @db.NVarChar(15)
    WMShipPoint   String?                  @db.NVarChar(20)
    LTLout        Boolean                  @default(false, map: "DF_Sites_LTLout")
    rv            Unsupported("timestamp") @map("_rv")
    SiteName      String?                  @db.NVarChar(128)
    TenantID      Int?
    ParentID      Int?
    Code          String?                  @db.NVarChar(10)
    Active        Boolean?
    WhseNumber    String?                  @db.NVarChar(50)
    TimeZone      String?                  @db.NVarChar(50)
    TZ            String?                  @db.NVarChar(10)
    ClusterID     Int?
    Clusters      Clusters?                @relation(fields: [ClusterID], references: [ID], onUpdate: NoAction, map: "FK_Sites_Clusters")

    @@schema("core")
}

model statuses {
    ID               Int     @id(map: "PK_AppointmentStatuses") @default(autoincrement()) @db.SmallInt
    Name             String? @db.NVarChar(50)
    Status           Int?
    Description      String  @db.NVarChar(51)
    DockDoorRequired Boolean @default(false, map: "DF_AppointmentStatuses_DockDoorRequred")
    DockTypeID       Int?    @db.SmallInt

    @@schema("Appointments")
}

model Trips {
    ID         BigInt    @id(map: "PK_Trips") @default(autoincrement())
    TripTypeID Int
    TripName   String    @db.NVarChar(120)
    TripTypes  TripTypes @relation(fields: [TripTypeID], references: [ID], onUpdate: NoAction, map: "FK_Trips_TripTypes")

    @@schema("Trips")
}

model TripTypes {
    ID       Int     @id(map: "PK_TripTypes") @default(autoincrement())
    Name     String  @db.NVarChar(128)
    TenantID Int
    Trips    Trips[]

    @@schema("Trips")
}

• remove everything from prisma schema except generator and datasource

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

generator prismaClassGenerator {
    provider               = "prisma-class-generator"
    output                 = "../libs/database/src/prisma-class"
    separateRelationFields = "true"
    dryRun                 = "false"
}

datasource db {
    provider = "sqlserver"
    url      = env("TEST_MSSQL_JDBC_URI")
    schemas  = ["Appointments", "Trips", "core"]
}

• prisma db pull

Prisma schema loaded from schema.prisma
Datasource "db": SQL Server database, schemas "Appointments, Trips, core"

✔ Introspected 15 models and wrote them into schema.prisma in 116ms
      
*** WARNING ***

These fields are not supported by Prisma Client, because Prisma currently does not support their types:
  - Model: "ChargeTypes", field: "rv", original data type: "timestamp"
  - Model: "Documents", field: "rv", original data type: "timestamp"
  - Model: "DocumentTypes", field: "rv", original data type: "timestamp"
  - Model: "Queues", field: "rv", original data type: "timestamp"
  - Model: "QueueTypes", field: "rv", original data type: "timestamp"
  - Model: "Settings", field: "rv", original data type: "timestamp"
  - Model: "Sites", field: "rv", original data type: "timestamp"

Run prisma generate to generate Prisma Client.

When there should definitely be 17 models

[janpio]

Which models are missing @Druue? Are those across all schemas, or only specific ones?

[rujorgensen]

@Druue does one of the missing schemas happen to be "Settings_Overrides"? I seem to have issues with snake-cased schemas.

[Druue]

I seem to have forgotten one of the models from the Trips schema mentioned in the OP: Transfers.

Only models from the Trips schema are missing - which, in my case, included the following:

• Trips
• TripTypes

Neither of which are snake-case, nor are any of the schemas

[nickdalts]

Same issue here with SQL Server. I have a couple of cross schema references and can't not include all schemas