search

zenstackhq / zenstack

Fullstack TypeScript toolkit that enhances Prisma ORM with flexible Authorization layer for RBAC/ABAC/PBAC/ReBAC, offering auto-generated type-safe APIs and frontend hooks.
https://zenstack.dev
MIT License
2.09k stars 88 forks source link

RESTful API handler should avoid using mode: 'insensitive' on SQL Server #841

Closed elsantoalcielo closed 10 months ago

elsantoalcielo commented 11 months ago

I am using ZenStack's Express middleware to serve a REST API with an SQL Server as database. When I try to use a contains filter, as in

http://192.168.1.14:3000/api/gPW_formulations?filter[name$icontains]=sometext

the Prisma client returns the following error:

image

Indeed, the Prisma documentation states that you can't use mode: 'insensitive' on SQL Server:

Microsoft SQL Server uses case-insensitive collation by default. Therefore, filtering with Prisma Client and Microsoft SQL Server is case-insensitive by default.

mode: 'insensitive' property is not required and therefore not available in the generated Prisma Client API.

https://www.prisma.io/docs/concepts/components/prisma-client/case-sensitivity#microsoft-sql-server-provider

What I'm using:

ymc9 commented 11 months ago

Thanks for catching this @elsantoalcielo . For case-senstive filter you can use the "$contains" operator. Currently, the API handlers don't know what database is used so don't have a good way to reject queries that are actually not supported ...

ymc9 commented 10 months ago

Closing for now due to no follow up.