PrismaP
Prisma•16mo ago•
1 reply
jermahs

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])
  ...
}


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


Unfortunately, if you run a query in prisma such as 

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.


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.
Case sensitivity (Reference) | Prisma Documentation
Was this page helpful?