Prisma•4d ago

MSSQL Include (Joins) Seem Case Sensitive

Observing the documentation, https://www.prisma.io/docs/orm/prisma-client/queries/case-sensitivity#microsoft-sql-server-provider and my knowledge of MSSQL I know that MSSQL is case insensitive. However, it has become apparent that prisma is some how forcing case sensitivity when using an include on a relationship. We observed this by having some legacy data that was lower case in one spot and a different case in another despite it being a FK. When doing a raw query this works as expected, but through prisma it fails with a null not allowed error message.

model Locations
  ...
  relatedUser String @db.Nvarchar(256)
  ...
  owner: UserInfo @relation('Locations_RelateduserToUserInfo', fields: [relatedUser], references: [Username])
  ...
}

model Locations
  ...
  relatedUser String @db.Nvarchar(256)
  ...
  owner: UserInfo @relation('Locations_RelateduserToUserInfo', fields: [relatedUser], references: [Username])
  ...
}

In our legacy production db we have a value in UserInfo.Username as "someuser", but the Locations.relatedUser is "SomeUser". Don't ask me why, I'm not happy with the casing issue within my database to begin with : / If you jsut run a raw query from SSMS or other query editor, then the following works

SELECT * FROM Locations L
INNER JOIN UserInfo UI ON UI.Username = L.RelatedUser

SELECT * FROM Locations L
INNER JOIN UserInfo UI ON UI.Username = L.RelatedUser

Unfortunately, if you run a query in prisma such as

await prisma.locations.findMany({
  where: {
    id: someLocationId,
  },
  include: {
    owner: true
  }
}

await prisma.locations.findMany({
  where: {
    id: someLocationId,
  },
  include: {
    owner: true
  }
}

then you'll get the following error: "PrismaClientUnknownRequestError"

Inconsistent query result: Field owner is required to return data, got `null` instead.

Inconsistent query result: Field owner is required to return data, got `null` instead.

Please help! We can obviously modify our data in our database, but we have some weird legacy requirements that I'd rather not contend with tat this time. Any help would be greatly appreciated! Thanks in advance!

Case sensitivity (Reference) | Prisma Documentation

How Prisma Client handles case sensitivity when filtering and sorting.

0 Replies

No replies yetBe the first to reply to this messageJoin

Gaming

Programming

MSSQL Include (Joins) Seem Case Sensitive