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.

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);
}

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;
}

Weird caching/stale results problem, Nextjs

Weird caching/stale results problem, Nextjs

Similar Threads

Similar Threads

Similar Threads