Prisma•2y ago•

3 replies

Raw SQL generated for cursor using libsql issue

To start with I am using:

"@libsql/client": "0.6.1",
"@prisma/adapter-libsql": "5.14.0",
"@prisma/client": "5.14.0",
"prisma": "5.14.0",

"@libsql/client": "0.6.1",
"@prisma/adapter-libsql": "5.14.0",
"@prisma/client": "5.14.0",
"prisma": "5.14.0",

I am handling a cursor-based pagination. My code looks like this:

const take = constants.pagination.limit + 1;
const cursor = input.after ? { id: decodeCursor(input.after) } : undefined;
const skip = input.after ? 1 : 0;

const data = await this.prismaService.user.findMany({
  take,
  cursor,
  skip,
  orderBy: {
    createdAt: 'desc',
  },
  select: {
    id: true,
  }
})

const take = constants.pagination.limit + 1;
const cursor = input.after ? { id: decodeCursor(input.after) } : undefined;
const skip = input.after ? 1 : 0;

const data = await this.prismaService.user.findMany({
  take,
  cursor,
  skip,
  orderBy: {
    createdAt: 'desc',
  },
  select: {
    id: true,
  }
})

When fetching the first batch of items, with

after

after

argument being

null

null

, I'm successfully getting the data.

The raw generated SQL looks like this:

SELECT `main`.`User`.`id` FROM `main`.`User` ORDER BY `main`.`User`.`createdAt` DESC LIMIT 6 OFFSET 0

SELECT `main`.`User`.`id` FROM `main`.`User` ORDER BY `main`.`User`.`createdAt` DESC LIMIT 6 OFFSET 0

However, when trying to get the next batch based on the cursor, I'm getting an error from libSQL

RESPONSE_TOO_LARGE: Response is too large

RESPONSE_TOO_LARGE: Response is too large

.

When looking at the generated SQL it looks like this:

SELECT `main`.`User`.`id` FROM `main`.`User` AND `main`.`User`.`createdAt` <= (SELECT `main`.`User`.`createdAt` FROM `main`.`User` WHERE (`main`.`User`.`id`) = (6))) ORDER BY `main`.`User`.`createdAt` DESC LIMIT -1 OFFSET 0

SELECT `main`.`User`.`id` FROM `main`.`User` AND `main`.`User`.`createdAt` <= (SELECT `main`.`User`.`createdAt` FROM `main`.`User` WHERE (`main`.`User`.`id`) = (6))) ORDER BY `main`.`User`.`createdAt` DESC LIMIT -1 OFFSET 0

I believe the

-1

-1

is a bug and the proper limit is not being applied correctly here. Does anyone have a pointer on how I can debug this locally and maybe local patch it?

Solution

~~Alright, so I figured out the issue is coming from "You must sort by your cursor, which has to be a unique, sequential column."~~

Jump to solution

Raw SQL generated for cursor using libsql issue

To start with I am using:

"@libsql/client": "0.6.1",
"@prisma/adapter-libsql": "5.14.0",
"@prisma/client": "5.14.0",
"prisma": "5.14.0",

"@libsql/client": "0.6.1",
"@prisma/adapter-libsql": "5.14.0",
"@prisma/client": "5.14.0",
"prisma": "5.14.0",

I am handling a cursor-based pagination. My code looks like this:

const take = constants.pagination.limit + 1;
const cursor = input.after ? { id: decodeCursor(input.after) } : undefined;
const skip = input.after ? 1 : 0;

const data = await this.prismaService.user.findMany({
  take,
  cursor,
  skip,
  orderBy: {
    createdAt: 'desc',
  },
  select: {
    id: true,
  }
})

const take = constants.pagination.limit + 1;
const cursor = input.after ? { id: decodeCursor(input.after) } : undefined;
const skip = input.after ? 1 : 0;

const data = await this.prismaService.user.findMany({
  take,
  cursor,
  skip,
  orderBy: {
    createdAt: 'desc',
  },
  select: {
    id: true,
  }
})

When fetching the first batch of items, with

after

after

argument being

null

null

, I'm successfully getting the data.

The raw generated SQL looks like this:

SELECT `main`.`User`.`id` FROM `main`.`User` ORDER BY `main`.`User`.`createdAt` DESC LIMIT 6 OFFSET 0

SELECT `main`.`User`.`id` FROM `main`.`User` ORDER BY `main`.`User`.`createdAt` DESC LIMIT 6 OFFSET 0

However, when trying to get the next batch based on the cursor, I'm getting an error from libSQL

RESPONSE_TOO_LARGE: Response is too large

RESPONSE_TOO_LARGE: Response is too large

.

When looking at the generated SQL it looks like this:

SELECT `main`.`User`.`id` FROM `main`.`User` AND `main`.`User`.`createdAt` <= (SELECT `main`.`User`.`createdAt` FROM `main`.`User` WHERE (`main`.`User`.`id`) = (6))) ORDER BY `main`.`User`.`createdAt` DESC LIMIT -1 OFFSET 0

SELECT `main`.`User`.`id` FROM `main`.`User` AND `main`.`User`.`createdAt` <= (SELECT `main`.`User`.`createdAt` FROM `main`.`User` WHERE (`main`.`User`.`id`) = (6))) ORDER BY `main`.`User`.`createdAt` DESC LIMIT -1 OFFSET 0

I believe the

-1

-1

is a bug and the proper limit is not being applied correctly here. Does anyone have a pointer on how I can debug this locally and maybe local patch it?

Solution

~~Alright, so I figured out the issue is coming from "You must sort by your cursor, which has to be a unique, sequential column."~~

Jump to solution

Raw SQL generated for cursor using libsql issue

Raw SQL generated for cursor using libsql issue

Similar Threads

Similar Threads

Similar Threads