Cursor-based pagination always returns 0 results starting in 7.4.0 #29176
Description
Bug description
I use SQLite, and hence, I have only seen this with SQLite so far. This happens with both @prisma/adapter-libsql and @prisma/adapter-better-sqlite3.
Cursor-based pagination now always* returns 0 results.
*"Always" meaning every use case I have tried.
Severity
Reproduction
Prisma schema can be found below.
import { PrismaClient } from './generated/prisma/client.js';
import { PrismaLibSql } from '@prisma/adapter-libsql';
import { PrismaBetterSqlite3 } from '@prisma/adapter-better-sqlite3';
import { randomUUID } from 'node:crypto';
import { strictEqual as assertEqual } from 'node:assert';
process.env.DATABASE_URL = 'file:./dev.db';
// const adapter = new PrismaLibSql({
// url: process.env.DATABASE_URL ?? ":memory:",
// }, {
// // Recommended choice here: https://www.prisma.io/docs/orm/overview/databases/sqlite#3-configure-timestamp-format-for-backward-compatibility
// timestampFormat: 'iso8601',
// });
const adapter = new PrismaBetterSqlite3({
url: process.env.DATABASE_URL ?? ":memory:",
}, {
// Recommended choice here: https://www.prisma.io/docs/orm/overview/databases/sqlite#3-configure-timestamp-format-for-backward-compatibility
timestampFormat: 'iso8601',
});
const db = new PrismaClient({
adapter,
// log: ["query"], // Uncomment to log queries
});
async function main() {
const connection_uuid = randomUUID();
const query_ref = 'asdf';
await db.listResult.createMany({
data: [
{
connection_uuid,
query_ref,
result_index: 0,
},
{
connection_uuid,
query_ref,
result_index: 1,
},
{
connection_uuid,
query_ref,
result_index: 2,
},
],
});
const results = await db.listResult.findMany({
take: 5,
skip: 0,
where: {
connection_uuid,
query_ref,
},
select: {
result_index: true,
},
orderBy: {
result_index: "asc",
},
cursor: {
connection_uuid_query_ref_result_index: {
connection_uuid,
query_ref,
result_index: 0,
},
},
});
assertEqual(results.length, 3);
}
main();Expected vs. Actual Behavior
The assertion at the bottom of the example fails on 7.4.0 and passes on 7.3.0 with no changes to the code. I expect it to pass, meaning that there are results returned.
Frequency
Consistently reproducible
Does this occur in development or production?
Both development and production
Is this a regression?
Yes. It works on 7.3.0. I'm not sure if there are patch versions or which of them work.
Workaround
There is no work-around, I think.
Prisma Schema & Queries
generator client {
provider = "prisma-client-js"
output = "../generated/prisma"
}
datasource db {
provider = "sqlite"
}
model ListResult {
id Int @id @default(autoincrement())
connection_uuid String
query_ref String
result_index Int // The 0-based index in which this entry appears in the results.
@@unique([connection_uuid, query_ref, result_index])
} const results = await db.listResult.findMany({
take: 5,
skip: 0,
where: {
connection_uuid,
query_ref,
},
select: {
result_index: true,
},
orderBy: {
result_index: "asc",
},
cursor: {
connection_uuid_query_ref_result_index: {
connection_uuid,
query_ref,
result_index: 0,
},
},
});Prisma Config
(The default config.)
// This file was generated by Prisma, and assumes you have installed the following:
// npm install --save-dev prisma dotenv
import "dotenv/config";
import { defineConfig } from "prisma/config";
export default defineConfig({
schema: "prisma/schema.prisma",
migrations: {
path: "prisma/migrations",
},
datasource: {
url: process.env["DATABASE_URL"],
},
});Logs & Debug Info
prisma:client clientVersion 7.4.0 +171ms
prisma:client:clientEngine Using driver adapter: '{\n' +
' "provider": "sqlite",\n' +
' "adapterName": "@prisma/adapter-better-sqlite3"\n' +
'}' +1ms
prisma:client Prisma Client call: +2ms
prisma:client prisma.listResult.createMany({
data: [
{
connection_uuid: "908735a3-e98e-4608-92e8-ab6bcc7dfa51",
query_ref: "asdf",
result_index: 0
},
{
connection_uuid: "908735a3-e98e-4608-92e8-ab6bcc7dfa51",
query_ref: "asdf",
result_index: 1
},
{
connection_uuid: "908735a3-e98e-4608-92e8-ab6bcc7dfa51",
query_ref: "asdf",
result_index: 2
}
]
}) +0ms
prisma:client Generated request: +0ms
prisma:client {
"modelName": "ListResult",
"action": "createMany",
"query": {
"arguments": {
"data": [
{
"connection_uuid": "908735a3-e98e-4608-92e8-ab6bcc7dfa51",
"query_ref": "asdf",
"result_index": 0
},
{
"connection_uuid": "908735a3-e98e-4608-92e8-ab6bcc7dfa51",
"query_ref": "asdf",
"result_index": 1
},
{
"connection_uuid": "908735a3-e98e-4608-92e8-ab6bcc7dfa51",
"query_ref": "asdf",
"result_index": 2
}
]
},
"selection": {
"$composites": true,
"$scalars": true
}
}
}
+0ms
prisma:client:clientEngine sending request +1ms
prisma:client:clientEngine query plan cache miss +43ms
prisma:client:clientEngine query plan created {
"type": "transaction",
"args": {
"type": "dataMap",
"args": {
"expr": {
"type": "execute",
"args": {
"type": "templateSql",
"fragments": [
{
"type": "stringChunk",
"chunk": "INSERT INTO `main`.`ListResult` (`connection_uuid`, `query_ref`, `result_index`) VALUES ("
},
{
"type": "parameter"
},
{
"type": "stringChunk",
"chunk": ","
},
{
"type": "parameter"
},
{
"type": "stringChunk",
"chunk": ","
},
{
"type": "parameter"
},
{
"type": "stringChunk",
"chunk": "), ("
},
{
"type": "parameter"
},
{
"type": "stringChunk",
"chunk": ","
},
{
"type": "parameter"
},
{
"type": "stringChunk",
"chunk": ","
},
{
"type": "parameter"
},
{
"type": "stringChunk",
"chunk": "), ("
},
{
"type": "parameter"
},
{
"type": "stringChunk",
"chunk": ","
},
{
"type": "parameter"
},
{
"type": "stringChunk",
"chunk": ","
},
{
"type": "parameter"
},
{
"type": "stringChunk",
"chunk": ")"
}
],
"args": [
{
"prisma__type": "param",
"prisma__value": {
"name": "%1",
"type": "String"
}
},
{
"prisma__type": "param",
"prisma__value": {
"name": "%2",
"type": "String"
}
},
{
"prisma__type": "param",
"prisma__value": {
"name": "%3",
"type": "Int"
}
},
{
"prisma__type": "param",
"prisma__value": {
"name": "%1",
"type": "String"
}
},
{
"prisma__type": "param",
"prisma__value": {
"name": "%2",
"type": "String"
}
},
{
"prisma__type": "param",
"prisma__value": {
"name": "%4",
"type": "Int"
}
},
{
"prisma__type": "param",
"prisma__value": {
"name": "%1",
"type": "String"
}
},
{
"prisma__type": "param",
"prisma__value": {
"name": "%2",
"type": "String"
}
},
{
"prisma__type": "param",
"prisma__value": {
"name": "%5",
"type": "Int"
}
}
],
"argTypes": [
{
"arity": "scalar",
"scalarType": "string",
"dbType": null
},
{
"arity": "scalar",
"scalarType": "string",
"dbType": null
},
{
"arity": "scalar",
"scalarType": "int",
"dbType": null
},
{
"arity": "scalar",
"scalarType": "string",
"dbType": null
},
{
"arity": "scalar",
"scalarType": "string",
"dbType": null
},
{
"arity": "scalar",
"scalarType": "int",
"dbType": null
},
{
"arity": "scalar",
"scalarType": "string",
"dbType": null
},
{
"arity": "scalar",
"scalarType": "string",
"dbType": null
},
{
"arity": "scalar",
"scalarType": "int",
"dbType": null
}
],
"placeholderFormat": {
"prefix": "?",
"hasNumbering": false
},
"chunkable": true
}
},
"structure": {
"type": "affectedRows"
},
"enums": {}
}
}
} +16ms
prisma:driver-adapter:better-sqlite3 [js::startTransaction] options: '{\n "usePhantomQuery": false\n}' +67ms
prisma:driver-adapter:better-sqlite3 [js::executeRaw] '{\n' +
' "sql": "INSERT INTO `main`.`ListResult` (`connection_uuid`, `query_ref`, `result_index`) VALUES (?,?,?), (?,?,?), (?,?,?)",\n' +
' "args": [\n' +
' "908735a3-e98e-4608-92e8-ab6bcc7dfa51",\n' +
' "asdf",\n' +
' 0,\n' +
' "908735a3-e98e-4608-92e8-ab6bcc7dfa51",\n' +
' "asdf",\n' +
' 1,\n' +
' "908735a3-e98e-4608-92e8-ab6bcc7dfa51",\n' +
' "asdf",\n' +
' 2\n' +
' ],\n' +
' "argTypes": [\n' +
' {\n' +
' "arity": "scalar",\n' +
' "scalarType": "string",\n' +
' "dbType": null\n' +
' },\n' +
' {\n' +
' "arity": "scalar",\n' +
' "scalarType": "string",\n' +
' "dbType": null\n' +
' },\n' +
' {\n' +
' "arity": "scalar",\n' +
' "scalarType": "int",\n' +
' "dbType": null\n' +
' },\n' +
' {\n' +
' "arity": "scalar",\n' +
' "scalarType": "string",\n' +
' "dbType": null\n' +
' },\n' +
' {\n' +
' "arity": "scalar",\n' +
' "scalarType": "string",\n' +
' "dbType": null\n' +
' },\n' +
' {\n' +
' "arity": "scalar",\n' +
' "scalarType": "int",\n' +
' "dbType": null\n' +
' },\n' +
' {\n' +
' "arity": "scalar",\n' +
' "scalarType": "string",\n' +
' "dbType": null\n' +
' },\n' +
' {\n' +
' "arity": "scalar",\n' +
' "scalarType": "string",\n' +
' "dbType": null\n' +
' },\n' +
' {\n' +
' "arity": "scalar",\n' +
' "scalarType": "int",\n' +
' "dbType": null\n' +
' }\n' +
' ]\n' +
'}' +1ms
prisma:client:transactionManager Closing transaction. {
"transactionId": "d294a50e-3a50-4920-8b43-c23528a268a4",
"status": "committed"
} +4ms
prisma:driver-adapter:better-sqlite3 [js::executeRaw] '{\n "sql": "COMMIT",\n "args": [],\n "argTypes": []\n}' +1ms
prisma:driver-adapter:better-sqlite3 [js::commit] +6ms
prisma:client:clientEngine query plan executed +7ms
prisma:client Prisma Client call: +0ms
prisma:client prisma.listResult.findMany({
take: 5,
skip: 0,
where: {
connection_uuid: "908735a3-e98e-4608-92e8-ab6bcc7dfa51",
query_ref: "asdf"
},
select: {
result_index: true
},
orderBy: {
result_index: "asc"
},
cursor: {
connection_uuid_query_ref_result_index: {
connection_uuid: "908735a3-e98e-4608-92e8-ab6bcc7dfa51",
query_ref: "asdf",
result_index: 0
}
}
}) +0ms
prisma:client Generated request: +0ms
prisma:client {
"modelName": "ListResult",
"action": "findMany",
"query": {
"arguments": {
"take": 5,
"skip": 0,
"where": {
"connection_uuid": "908735a3-e98e-4608-92e8-ab6bcc7dfa51",
"query_ref": "asdf"
},
"orderBy": {
"result_index": "asc"
},
"cursor": {
"connection_uuid_query_ref_result_index": {
"connection_uuid": "908735a3-e98e-4608-92e8-ab6bcc7dfa51",
"query_ref": "asdf",
"result_index": 0
}
}
},
"selection": {
"result_index": true
}
}
}
+0ms
prisma:client:clientEngine sending request +1ms
prisma:client:clientEngine query plan cache miss +0ms
prisma:client:clientEngine query plan created {
"type": "dataMap",
"args": {
"expr": {
"type": "process",
"args": {
"expr": {
"type": "query",
"args": {
"type": "templateSql",
"fragments": [
{
"type": "stringChunk",
"chunk": "SELECT `main`.`ListResult`.`id`, `main`.`ListResult`.`result_index`, `main`.`ListResult`.`connection_uuid`, `main`.`ListResult`.`query_ref` FROM `main`.`ListResult` WHERE (`main`.`ListResult`.`connection_uuid` = "
},
{
"type": "parameter"
},
{
"type": "stringChunk",
"chunk": " AND `main`.`ListResult`.`query_ref` = "
},
{
"type": "parameter"
},
{
"type": "stringChunk",
"chunk": " AND `main`.`ListResult`.`result_index` >= (SELECT `main`.`ListResult`.`result_index` FROM `main`.`ListResult` WHERE (`main`.`ListResult`.`connection_uuid`,`main`.`ListResult`.`query_ref`,`main`.`ListResult`.`result_index`) = ("
},
{
"type": "parameter"
},
{
"type": "stringChunk",
"chunk": ","
},
{
"type": "parameter"
},
{
"type": "stringChunk",
"chunk": ","
},
{
"type": "parameter"
},
{
"type": "stringChunk",
"chunk": "))) ORDER BY `main`.`ListResult`.`result_index` ASC LIMIT "
},
{
"type": "parameter"
},
{
"type": "stringChunk",
"chunk": " OFFSET "
},
{
"type": "parameter"
}
],
"args": [
{
"prisma__type": "param",
"prisma__value": {
"name": "%1",
"type": "String"
}
},
{
"prisma__type": "param",
"prisma__value": {
"name": "%2",
"type": "String"
}
},
{
"prisma__type": "param",
"prisma__value": {
"name": "%1",
"type": "String"
}
},
{
"prisma__type": "param",
"prisma__value": {
"name": "%2",
"type": "String"
}
},
{
"prisma__type": "param",
"prisma__value": {
"name": "%3",
"type": "Int"
}
},
-1,
"0"
],
"argTypes": [
{
"arity": "scalar",
"scalarType": "string",
"dbType": null
},
{
"arity": "scalar",
"scalarType": "string",
"dbType": null
},
{
"arity": "scalar",
"scalarType": "string",
"dbType": null
},
{
"arity": "scalar",
"scalarType": "string",
"dbType": null
},
{
"arity": "scalar",
"scalarType": "int",
"dbType": null
},
{
"arity": "scalar",
"scalarType": "int",
"dbType": null
},
{
"arity": "scalar",
"scalarType": "bigint",
"dbType": null
}
],
"placeholderFormat": {
"prefix": "?",
"hasNumbering": false
},
"chunkable": false
}
},
"operations": {
"pagination": {
"cursor": {
"query_ref": {
"prisma__type": "param",
"prisma__value": {
"name": "%2",
"type": "String"
}
},
"connection_uuid": {
"prisma__type": "param",
"prisma__value": {
"name": "%1",
"type": "String"
}
},
"result_index": {
"prisma__type": "param",
"prisma__value": {
"name": "%3",
"type": "Int"
}
}
},
"take": 5,
"skip": 0
},
"distinct": null,
"reverse": false,
"nested": {},
"linkingFields": null
}
}
},
"structure": {
"type": "object",
"serializedName": null,
"fields": {
"result_index": {
"type": "field",
"dbName": "result_index",
"fieldType": {
"arity": "required",
"type": "int"
}
}
},
"skipNulls": false
},
"enums": {}
}
} +12ms
prisma:driver-adapter:better-sqlite3 [js::queryRaw] '{\n' +
' "sql": "SELECT `main`.`ListResult`.`id`, `main`.`ListResult`.`result_index`, `main`.`ListResult`.`connection_uuid`, `main`.`ListResult`.`query_ref` FROM `main`.`ListResult` WHERE (`main`.`ListResult`.`connection_uuid` = ? AND `main`.`ListResult`.`query_ref` = ? AND `main`.`ListResult`.`result_index` >= (SELECT `main`.`ListResult`.`result_index` FROM `main`.`ListResult` WHERE (`main`.`ListResult`.`connection_uuid`,`main`.`ListResult`.`query_ref`,`main`.`ListResult`.`result_index`) = (?,?,?))) ORDER BY `main`.`ListResult`.`result_index` ASC LIMIT ? OFFSET ?",\n' +
' "args": [\n' +
' "908735a3-e98e-4608-92e8-ab6bcc7dfa51",\n' +
' "asdf",\n' +
' "908735a3-e98e-4608-92e8-ab6bcc7dfa51",\n' +
' "asdf",\n' +
' 0,\n' +
' -1,\n' +
' "0"\n' +
' ],\n' +
' "argTypes": [\n' +
' {\n' +
' "arity": "scalar",\n' +
' "scalarType": "string",\n' +
' "dbType": null\n' +
' },\n' +
' {\n' +
' "arity": "scalar",\n' +
' "scalarType": "string",\n' +
' "dbType": null\n' +
' },\n' +
' {\n' +
' "arity": "scalar",\n' +
' "scalarType": "string",\n' +
' "dbType": null\n' +
' },\n' +
' {\n' +
' "arity": "scalar",\n' +
' "scalarType": "string",\n' +
' "dbType": null\n' +
' },\n' +
' {\n' +
' "arity": "scalar",\n' +
' "scalarType": "int",\n' +
' "dbType": null\n' +
' },\n' +
' {\n' +
' "arity": "scalar",\n' +
' "scalarType": "int",\n' +
' "dbType": null\n' +
' },\n' +
' {\n' +
' "arity": "scalar",\n' +
' "scalarType": "bigint",\n' +
' "dbType": null\n' +
' }\n' +
' ]\n' +
'}' +15ms
prisma:client:clientEngine query plan executed +1ms
Environment & Setup
- OS: Xubuntu 20.04.
- Database: SQLite
- Node.js version: 25.2.1
Prisma Version
Loaded Prisma config from prisma.config.ts.
Prisma schema loaded from prisma/schema.prisma.
prisma : 7.4.0
@prisma/client : 7.4.0
Operating System : linux
Architecture : x64
Node.js : v25.2.1
TypeScript : unknown
Query Compiler : enabled
PSL : @prisma/prisma-schema-wasm 7.4.0-20.ab56fe763f921d033a6c195e7ddeb3e255bdbb57
Schema Engine : schema-engine-cli ab56fe763f921d033a6c195e7ddeb3e255bdbb57 (at node_modules/@prisma/engines/schema-engine-debian-openssl-1.1.x)
Default Engines Hash : ab56fe763f921d033a6c195e7ddeb3e255bdbb57
Studio : 0.13.1