Data Revalidation

[brisk hare]

import { GenericTableV2 } from '@/components/datatables/generic/GenericTableV2';
import eventmgmt_config from '@/configs/eventmgmt_config';
import sql from 'mssql'


export async function eventManagementGetRecentTickets() {
    const sqlPool = await sql.connect(eventmgmt_config);
    const sqlRequest = new sql.Request(sqlPool);

    const sqlQuery = `SELECT TOP (300) *, CAST(timestamp AS DATETIME2) AS adjustedTimestamp FROM [IAMEM].[dbo].[iamevents] WHERE [status] like '%Ticket%' Order by [eId] desc`;

    let queryResult = await sqlRequest.query(sqlQuery);
    await sqlPool.close();

    if (queryResult.recordset.length > 0) {
        return queryResult;
    }

    return null;
}


export default async function Page() {
    let tickets = await eventManagementGetRecentTickets();


    return (
        <div className="m-2 bg-paper rounded-md">
            <div className='p-2'>
                {tickets &&
                    <GenericTableV2 data={tickets} />
                }
            </div>
        </div>
    )
};

How do I make sure 'tickets' is cached but revalidate it every 5 minutes

[simple arrow]

Hi @brisk hare. I'm kapa.ai, your friendly support bot. I will try to answer your question by searching through the knowledge base. :wave:

---

In Next.js, you can use the next.revalidate option of fetch to set the revalidation period (in seconds) of an individual fetch request. This will revalidate the Data Cache, which in turn will revalidate the Full Route Cache. Fresh data will be fetched, and components will be re-rendered on the server.

However, in your case, you are not using fetch to get your data, you are using a direct SQL query. The next.revalidate option is not applicable to direct SQL queries.

If you want to cache and revalidate your data every 5 minutes, you would need to create an API route that executes your SQL query and then use fetch in your page to call this API route.

Here is an example of how you could modify your code to use fetch:

// pages/api/tickets.js
import sql from 'mssql'
import eventmgmt_config from '@/configs/eventmgmt_config';

export default async function handler(req, res) {
  const sqlPool = await sql.connect(eventmgmt_config);
  const sqlRequest = new sql.Request(sqlPool);

  const sqlQuery = `SELECT TOP (300) *, CAST(timestamp AS DATETIME2) AS adjustedTimestamp FROM [IAMEM].[dbo].[iamevents] WHERE [status] like '%Ticket%' Order by [eId] desc`;

  let queryResult = await sqlRequest.query(sqlQuery);
  await sqlPool.close();

  if (queryResult.recordset.length > 0) {
      res.status(200).json(queryResult.recordset);
  } else {
      res.status(404).json({ message: 'No tickets found' });
  }
}

// pages/index.js
import { GenericTableV2 } from '@/components/datatables/generic/GenericTableV2';

export default async function Page() {
  // This request should be cached with a lifetime of 5 minutes.
  const res = await fetch('/api/tickets', {
    next: { revalidate: 300 },
  })
  const tickets = await res.json();

return (
<div className="m-2 bg-paper rounded-md">
<div className='p-2'>
{tickets &&
<GenericTableV2 data={tickets} />
}
</div>
</div>
)
};

In this example, the `fetch` request to `/api/tickets` will be cached and revalidated every 5 minutes (300 seconds).

Please note that this is a simplified example and you may need to adjust it according to your needs. For example, you might want to add error handling for the `fetch` request.

Sources:
- **Next.js fetch API reference** (<https://nextjs.org/docs/app/api-reference/functions/fetch>)
- **Next.js Caching in Next.js** (<https://nextjs.org/docs/app/building-your-application/caching#fetch-optionsnextrevalidate>)
- **Next.js Data Fetching, Caching, and Revalidating** (<https://nextjs.org/docs/app/building-your-application/data-fetching/fetching-caching-and-revalidating#revalidating-data>)
----------------------
Note: you can ask me a follow up question by @ mentioning me again :speech_balloon:
----------------------

@brisk hare kapa.ai is still learning and improving, please let us know how it did by reacting below

[brisk hare]

@simple arrow Can I do this without using fetch? I can access this data directly using the function I provided

@simple arrow Can I do this without using fetch? I can access this data directly using the function I provided

[simple arrow]

Let me check the knowledge base for you. One moment! :books:

---

If you want to cache and revalidate data without using fetch, you might consider using a third-party library such as SWR (https://swr.vercel.app/) or TanStack Query (https://tanstack.com/query/latest). These libraries provide their own APIs for memoizing requests, caching, revalidating, and mutating data.

Here's an example of how you could use SWR in your component:

import useSWR from 'swr'
import { GenericTableV2 } from '@/components/datatables/generic/GenericTableV2';
import { eventManagementGetRecentTickets } from '../lib/load-tickets'

function Page() {
  const { data: tickets, error } = useSWR('tickets', eventManagementGetRecentTickets, { refreshInterval: 300000 })

  if (error) return <div>Failed to load tickets</div>
  if (!tickets) return <div>Loading...</div>

  return (
    <div className="m-2 bg-paper rounded-md">
        <div className='p-2'>
            <GenericTableV2 data={tickets} />
        </div>
    </div>
  )
}

export default Page

In this example, useSWR is used to fetch, cache, and revalidate the data. The refreshInterval option is set to 300000 milliseconds (5 minutes), which means the data will be revalidated every 5 minutes.

Please note that this is a simplified example and you may need to adjust it according to your needs. For example, you might want to add more sophisticated error handling.

Sources:

• Next.js Data Fetching, Caching, and Revalidating (https://nextjs.org/docs/app/building-your-application/data-fetching/fetching-caching-and-revalidating#fetching-data-on-the-client-with-third-party-libraries)
• SWR Documentation (https://swr.vercel.app/)

---

Note: you can ask me a follow up question by @ mentioning me again :speech_balloon: