Disabling SQL Cache // Stale data from vercel/postgres sql

[max-feldmann]

Hey

So I’ve coded myself into a little bit of a corner here. Please mind: This is my first app project with next.js so please feel free to point out rookie mistakes!

I’ve got a route.ts that is supposed to fetch all entries of a database table, cram them into a CSV and download it to the requesting user’s computer. To get all the data, it uses a findAll method, i’ve written using the versel/postgres SQL client.

However it returns stale data. I’ve tried disabling the cache since vercel sql uses fetch under the hood and all fetch requests are cached by default.

I’ve tried accomplishing this using export const dynamic = 'force-dynamic'; but that didn’t do the trick. Directly exporting this from the file where the findAll lives doesn’t work, since it’s a ‘use server’ component: Either next complains only async constants can be exported here or - if i remove ‘use server’ - it can’t find the DB-credentials from env.

So I’m in a bit of a bind here.

Can someone give me tipps to refactor this?

Cheers,
Max

findAll() Method to get all DB entries

'use server'

import { sql } from "@vercel/postgres";

export const findAll = async () => {
    const data = await sql`
      SELECT * FROM studyruns
    `;
    return data.rows;
}

Route to Download CSV

import { findAll } from "@/_services/studyruns";
import { json2csv } from 'json-2-csv';

export const dynamic = 'force-dynamic';

export async function GET(request: Request) {
  try {
    const studyruns = await findAll(); // Fetch all studyruns from the database
    const csv = json2csv(studyruns); // Use json2csvAsync to convert JSON to CSV

    return new Response(csv, {
      headers: {
        'Content-Type': 'text/csv',
        'Content-Disposition': 'attachment; filename="studyruns.csv"',
        'Cache-Control': 'no-store' // Disable caching
      },
    });
  } catch (error) {
    console.error('Error generating CSV:', error);
    const errorMessage = error instanceof Error ? error.message : 'Unknown error';
    return new Response(JSON.stringify({ error: errorMessage }), {
      status: 500,
      headers: { 'Content-Type': 'application/json' },
    });
  }
}

[pawlean (Pauline P. Narvas)]

Hi, @max-feldmann!

Thank you for providing the context and code snippets.

Here are some recommendations to address the stale data problem:

1. Update the findAll function:

• Import and use the unstable_noStore function from ‘next/cache’ at the beginning of your findAll function. This will help bypass the Next.js Data Cache for this specific function call.
• Simplify the function to directly return the rows from the SQL query.

2. Modify the API route:

• Ensure you’re using the App Router structure (i.e., app/api/download-csv/route.ts).
• Keep export const dynamic = 'force-dynamic'; and add export const revalidate = 0; at the top of your file to prevent caching.
• Use NextRequest and NextResponse from ‘next/server’ for better compatibility with Next.js 13+.
• Expand your cache-control headers in the response to cover more caching scenarios. Include headers like ‘Cache-Control’, ‘Pragma’, and ‘Expires’ with appropriate values to prevent caching at all levels.

3. General improvements:

• Ensure you’re using await with json2csv as it returns a promise.
• Improve error handling and logging in your try-catch block.

4. Client-side considerations:

When fetching from this API on the client-side, use appropriate cache-busting techniques. This might include adding cache-related options to your fetch request.

5. Infrastructure checks:

• Verify that your database is actually updating by querying it directly outside of your Next.js application.
• Check for any caching layers in your infrastructure (e.g., CDN, reverse proxy) that might be holding onto old data.

6. Performance consideration:

While these caching prevention techniques are useful for ensuring fresh data, be mindful of their impact on performance. In a production environment, consider implementing a more nuanced caching strategy based on your specific requirements.

Let us know how you get on!