Cached Queries With Nodejs

TL;DR:

• Find the project here: github.com/andyj/cached-queries-with-nodejs

Replicating CFML Query Caching with Node.js and Redis

I'm still a CFML coder at heart, so this is a personal project to replicate something similar to CFML Query Caching. It demonstrates how to set up a Node.js application with a RDBMS (SQLite3 for now) and Redis for caching query results. Each user’s query results are cached uniquely based on their user ID and query parameters. This setup aims to replicate caching of queries, similar to what you might find in CFML.

What I’m Trying to Achieve

In this project, I aim to replicate the query caching mechanism found in CFML, where query results are cached based on user-specific parameters. Using Node.js, SQLite3, and Redis, I want to implement a system where each user's queries are cached independently, ensuring that repeated queries return fast, cached results rather than querying the database again. Here’s how I’m going about it:

The Code

Caching Function

The core functionality revolves around caching queries. Here’s the function responsible for fetching cached data or querying the database if the data is not cached:

// Function to get cached data or fetch from DB
async function getCachedData(query, params, cacheDuration, userId) {
  const paramString = JSON.stringify(params);
  const hash = crypto.createHash('md5').update(query + paramString + userId + cacheDuration).digest('hex');
  const cacheKey = `sql_cache_${userId}_${hash}`;

  try {
    const result = await client.get(cacheKey);
    if (result) {
      return JSON.parse(result);
    } else {
      const data = await getDataFromDB(query, params);
      await client.setex(cacheKey, cacheDuration, JSON.stringify(data));
      return data;
    }
  } catch (err) {
    throw err;
  }
}

This function generates a unique cache key using the query, parameters, user ID, and cache duration, ensuring that each user's data is isolated. If the data is found in the cache, it’s returned directly. Otherwise, the function fetches the data from the database and stores it in Redis.

Setting Up the Route

To demonstrate this functionality, here’s the code for an Express route that uses the getCachedData function:

// Example route
app.get('/data', async (req, res) => {
  const userId = req.query.userId || 'default_user'; // Replace with actual user ID/session ID retrieval logic
  const query = 'SELECT time()';
  const params = [];
  const cacheDuration = 5; // 5 seconds

  try {
    const data = await getCachedData(query, params, cacheDuration, userId);
    res.json(data);
  } catch (error) {
    console.log(error);
    res.status(500).send('Error fetching data');
  }
});

This route extracts the user ID from the query parameters, sets a default user if none is provided, and specifies the query and cache duration. It then calls getCachedData to fetch the data and sends the response back to the client.

As a rule you don't want to use default_user, unless you have a specific use-case, otherwise everyone will see the results of the cached query. Please beware!

Hopefully, this setup is a simple yet effective way to cache query results in a Node.js application, inspired by CFML’s query caching feature. By leveraging Redis for caching and SQLite3 as the database, this implementation provides a solution for improving the performance of database queries.