N
Neon•15mo ago
rival-black

Weird caching/stale results problem, Nextjs

For context, I am using Neon with Drizzle ORM in a deployed NextJS 14 app (appdir). I've written multiple simple queries that look like this: 
import { lesson1Table, InsertLesson1Row } from "../schema";
const sql = neon(process.env.DATABASE_URL!);
export const db = drizzle(sql);

export async function createLesson1Row(data: InsertLesson1Row) {
  await db.insert(lesson1VerificationTable).values(data);
}

export async function getLesson1Rows() {
  return await db.select().from(lesson1Table);
}
import { lesson1Table, InsertLesson1Row } from "../schema";
const sql = neon(process.env.DATABASE_URL!);
export const db = drizzle(sql);

export async function createLesson1Row(data: InsertLesson1Row) {
  await db.insert(lesson1VerificationTable).values(data);
}

export async function getLesson1Rows() {
  return await db.select().from(lesson1Table);
}
These queries are either used in a single server component that is periodically revalidated, or in "route.ts" files which is the nextjs naming convention for custom request handlers, and in that case it acts as an API endpoint, so that data can be fetched dynamically. The problem I have is with a different, more complex query I wrote: 
export async function getAllCompletedLessons(
  userId: string,
): Promise<boolean[]> {
  const result = await db.execute(sql`
    SELECT
      EXISTS (SELECT user FROM ${lesson1Table} WHERE user = ${userId}) AS lesson1,
      EXISTS (SELECT user FROM ${lesson2Table} WHERE user = ${userId}) AS lesson2,
      EXISTS (SELECT user FROM ${lesson3Table} WHERE user = ${userId}) AS lesson3,
      EXISTS (SELECT user FROM ${lesson4Table} WHERE user = ${userId}) AS lesson4
  `);

  const completionStatus: boolean[] = [
    result.rows[0].lesson1 as boolean,
    result.rows[0].lesson2 as boolean,
    result.rows[0].lesson3 as boolean,
    result.rows[0].lesson4 as boolean
  ];

  return completionStatus;
}
export async function getAllCompletedLessons(
  userId: string,
): Promise<boolean[]> {
  const result = await db.execute(sql`
    SELECT
      EXISTS (SELECT user FROM ${lesson1Table} WHERE user = ${userId}) AS lesson1,
      EXISTS (SELECT user FROM ${lesson2Table} WHERE user = ${userId}) AS lesson2,
      EXISTS (SELECT user FROM ${lesson3Table} WHERE user = ${userId}) AS lesson3,
      EXISTS (SELECT user FROM ${lesson4Table} WHERE user = ${userId}) AS lesson4
  `);

  const completionStatus: boolean[] = [
    result.rows[0].lesson1 as boolean,
    result.rows[0].lesson2 as boolean,
    result.rows[0].lesson3 as boolean,
    result.rows[0].lesson4 as boolean
  ];

  return completionStatus;
}
This query is only used in a route.ts file (query endpoint). The problem with it is that it almost always returns stale data in both production and development. I can mark a lesson as completed as some user, but it keeps returning stale data, and I can't figure out why. After some time, I found a possible solution, that doesn't make much sense to me.
3 Replies
rival-black
rival-blackOP•15mo ago
My solution steps were this: 1) in my route.ts file I added this export: 
export const dynamic = "force-dynamic";
export const dynamic = "force-dynamic";
This didn't fix anything yet, but it makes sense to me to have it, according to nextjs docs. 2) I then updated my file where I create the "neon" object for my database connection: 
const sql = neon(process.env.DATABASE_URL!);
const sqlNoStore = neon(process.env.DATABASE_URL!, {
  fetchOptions: { cache: "no-store" },
});

export const db = drizzle(sql);
export const dbNoStore = drizzle(sqlNoStore);
const sql = neon(process.env.DATABASE_URL!);
const sqlNoStore = neon(process.env.DATABASE_URL!, {
  fetchOptions: { cache: "no-store" },
});

export const db = drizzle(sql);
export const dbNoStore = drizzle(sqlNoStore);
I use the dbNoStore object ONLY for that specific query (getAllCompletedLessons) and I get no caching issues: What I don't understand is why wasn't I getting caching issues with other queries? The only difference I can notice is that getAllCompletedLessons uses db.execute instead of the built in Drizzle syntax. Also, initially I tried to just use a single "db" export, that would have the { fetchOptions: { cache: "no-store" } } option, but that caused an error when I tried to build the project with the page/server component that is being prerendered and periodically revalidated: 
Error occurred prerendering page "/". Read more: https://nextjs.org/docs/messages/prerender-error
NeonDbError: Error connecting to database: Dynamic server usage: no-store fetch https://api.us-east-2.aws.neon.tech/sql /
Error occurred prerendering page "/". Read more: https://nextjs.org/docs/messages/prerender-error
NeonDbError: Error connecting to database: Dynamic server usage: no-store fetch https://api.us-east-2.aws.neon.tech/sql /
flat-fuchsia
flat-fuchsia•15mo ago
Hey @praskOo
flat-fuchsia
flat-fuchsia•15mo ago
To avoid caching endpoint results - add these two lines (https://github.com/neondatabase/examples/blob/main/ai/aws-bedrock/chatbot-nextjs/app/api/chat/route.ts#L1-L3) and proceed without modifying the Neon constructor.
GitHub
examples/ai/aws-bedrock/chatbot-nextjs/app/api/chat/route.ts at mai...
Examples and code snippets demonstrating common ways of integrating Neon with various frameworks and languages. - neondatabase/examples

Did you find this page helpful?