[MongoDB] All queries use $expr + $$REMOVE, bypassing indexes even for _id, @unique, and required fields #29000
Description
Bug description
Prisma MongoDB generates $expr queries with $$REMOVE checks for every query, including:
_idprimary key lookups (impossible to be missing)@uniquefield lookups (have indexes that should be used)- Required fields (Prisma enforces on create, cannot be missing)
The $$REMOVE check forces $expr usage, which bypasses all MongoDB indexes, causing COLLSCAN on every query regardless of available indexes.
Expected behavior
// Query by _id
{ _id: ObjectId("...") } // Uses primary key index, O(1)
// Query by @unique field
{ uid: "..." } // Uses unique index, O(1)Actual behavior
// Query by _id - WHY check if _id is missing? It cannot be.
{ $expr: { $and: [{ $eq: ["$_id", ...] }, { $ne: ["$_id", "$$REMOVE"] }] } }
// Query by @unique field
{ $expr: { $and: [{ $eq: ["$uid", ...] }, { $ne: ["$uid", "$$REMOVE"] }] } }Both force COLLSCAN, O(n) on entire collection.
Impact
- Every Prisma MongoDB query does a COLLSCAN
- Primary key lookups are O(n) instead of O(1)
- Unique indexes are completely ignored
- Performance degrades linearly with collection size
Suggested fix
Only add $$REMOVE check for optional (?) fields. Required fields and _id cannot be missing by definition.
Related issues
- Prisma is using
$exprand$andin Mongo, resulting in the query by-passing indexes #22878 - MongoDB Connector generates queries which do not take advantage of indices. #17396
- findFirstAndThrow on the primary key sometimes leads to a COLLSCAN when using mongodb #23775
- MongoDB
findUnique()orfindUniqueOrThrow()leads to COLLSCAN if multiple queries happen at the same time #22812 - MongoDB Query with 'in' condition will cause COLLSCAN, without leveraging indexes #19955
- Simple MongoDB queries are much slower compared to Mongoose #16916
- Slow queries on MongoDB using
includefor relations #15156 - Performance issues with MongoDB #14871
Severity
🚨 Critical: Data loss, app crash, security issue
Reproduction
Please copy and paste:
mkdir prisma-expr-bug && cd prisma-expr-bug
cat > package.json << 'EOF'
{
"type": "module",
"dependencies": {
"@prisma/client": "^6.19.1",
"mongodb-memory-server": "^10.1.4",
"prisma": "^6.19.1",
"tsx": "^4.19.4"
}
}
EOF
cat > schema.prisma << 'EOF'
datasource db {
provider = "mongodb"
url = env("DATABASE_URL")
}
generator client {
provider = "prisma-client-js"
}
model Test {
id String @id @default(auto()) @map("_id") @db.ObjectId
uid String @unique // REQUIRED + UNIQUE
name String // REQUIRED
country String? // OPTIONAL
}
EOF
cat > test.ts << 'EOF'
import { MongoMemoryReplSet } from "mongodb-memory-server";
const mongo = await MongoMemoryReplSet.create({ replSet: { count: 1 } });
process.env.DATABASE_URL = mongo.getUri("test");
const { PrismaClient } = await import("@prisma/client");
const prisma = new PrismaClient({ log: [{ level: "query", emit: "event" }] });
prisma.$on("query", (e) => {
console.log("\n=== QUERY ===");
console.log(e.query);
console.log("Uses $expr:", e.query.includes("$expr"));
});
const record = await prisma.test.create({
data: { uid: "test-1", name: "Test", country: null },
});
console.log("\n\n=== TEST 1: _id (PRIMARY KEY) ===");
await prisma.test.findUnique({ where: { id: record.id } });
console.log("\n\n=== TEST 2: @unique field ===");
await prisma.test.findUnique({ where: { uid: "test-1" } });
console.log("\n\n=== TEST 3: Required field ===");
await prisma.test.findFirst({ where: { name: "Test" } });
await prisma.$disconnect();
await mongo.stop();
EOF
npm install
DATABASE_URL="mongodb://localhost:27017/fake" npx prisma generate
npx tsx test.ts
Expected vs. Actual Behavior
All three queries show Uses $expr: true:
=== TEST 1: _id (PRIMARY KEY) ===
db.Test.aggregate([ { $match: { $expr: { $and: [{ $eq: ["$_id", ...] }, { $ne: ["$_id", "$$REMOVE"] }] } } } ...])
Uses $expr: true=== TEST 2: @unique field ===
db.Test.aggregate([ { $match: { $expr: { $and: [{ $eq: ["$uid", ...] }, { $ne: ["$uid", "$$REMOVE"] }] } } } ...])
Uses $expr: true=== TEST 3: Required field ===
db.Test.aggregate([ { $match: { $expr: { $and: [{ $eq: ["$name", ...] }, { $ne: ["$name", "$$REMOVE"] }] } } } ...])
Uses $expr: true
Frequency
Consistently reproducible
Does this occur in development or production?
Both development and production
Is this a regression?
Complex. I don't think it has ever been like that, but the whole null vs unset context is complex and really need an afterthought.
Workaround
No workaround.
Prisma Schema & Queries
datasource db {
provider = "mongodb"
url = env("DATABASE_URL")
}
generator client {
provider = "prisma-client-js"
}
model Test {
id String @id @default(auto()) @map("_id") @db.ObjectId
uid String @unique // REQUIRED + UNIQUE
name String // REQUIRED
country String? // OPTIONAL
}Test.findUniqueOrThrow({ where: { id: 'XXX' } })Environment & Setup
- Prisma: 6.19.1
- Database: MongoDB
- OS: macOS / Linux
- Node: v24.8.0
Prisma Version
Prisma config detected, skipping environment variable loading.
Prisma schema loaded from src/service/prisma/schema.prisma
prisma : 6.19.1
@prisma/client : 6.19.1
Computed binaryTarget : darwin-arm64
Operating System : darwin
Architecture : arm64
Node.js : v24.8.0
TypeScript : 5.9.3
Query Engine (Node-API) : libquery-engine c2990dca591cba766e3b7ef5d9e8a84796e47ab7 (at node_modules/@prisma/engines/libquery_engine-darwin-arm64.dylib.node)
PSL : @prisma/prisma-schema-wasm 7.1.1-3.c2990dca591cba766e3b7ef5d9e8a84796e47ab7
Schema Engine : schema-engine-cli c2990dca591cba766e3b7ef5d9e8a84796e47ab7 (at node_modules/@prisma/engines/schema-engine-darwin-arm64)
Default Engines Hash : c2990dca591cba766e3b7ef5d9e8a84796e47ab7
Studio : 0.511.0