Reusing Database Queries in Astro SSG

Optimize Database Calls During SSG in Astro

When building static sites with Astro, a common challenge developers face is managing database calls during Static Site Generation (SSG). If you have multiple pages that need the same data from your database, you might wonder: Does Astro cache the query results, or does it make a separate database call for each page?

High Quality Starter Kits with built-in authentication flow (Auth.js), object uploads (AWS, Clouflare R2, Firebase Storage, Supabase Storage), integrated payments (Stripe, LemonSqueezy), email verification flow (Resend, Postmark, Sendgrid), and much more. Compatible with any database (Redis, Postgres, MongoDB, SQLite, Firestore).

Next.js Starter Kit

View Demo →

SvelteKit Starter Kit

View Demo →

Astro Starter Kit

View Demo →

The short answer: Astro does NOT automatically cache database queries across pages. If three different pages (pages/foo, pages/bar, pages/ipsum) all execute db.select().from(tbl), Astro will make three separate calls to your database during the build process.

In this guide, you will learn how to implement a caching mechanism to ensure your database is queried only once during SSG, regardless of how many pages need that data. This approach can dramatically improve your build times and reduce database load.

Prerequisites

You’ll need the following:

Node.js 18 or later
An existing Astro project
A database connection (MySQL, PostgreSQL, or any database)

Understanding the Problem
The Solution: A Module-Level Cache
Step-by-Step Implementation
Advanced: Cache with TTL for Development
How It Works
Performance Benefits

Understanding the Problem

During Static Site Generation, Astro builds each page independently. Consider this scenario:

---
import { db } from '@/db'
const products = await db.select().from(productsTable)
---
<div>{products.length} products</div>

---
import { db } from '@/db'
const products = await db.select().from(productsTable)
---
<div>Products: {products.map(p => p.name).join(', ')}</div>

---
import { db } from '@/db'
const products = await db.select().from(productsTable)
---
<div>Total: {products.length}</div>

When you run npm run build, Astro will execute the database query three times-once for each page. For large datasets or slow databases, this can significantly increase build times.

The Solution: A Module-Level Cache

The most elegant solution is to create a module-level cache that persists across page builds. Since Astro’s build process runs in a single Node.js process, we can leverage JavaScript’s module caching to share query results.

Step-by-Step Implementation

Step 1: Create a Database Connection Module

First, set up your database connection. For this example, we’ll use Drizzle ORM with MySQL, but the pattern works with any database library.

import { drizzle } from 'drizzle-orm/mysql2'
import mysql from 'mysql2/promise'

// Create a connection pool
const pool = mysql.createPool({
  host: import.meta.env.DATABASE_HOST,
  user: import.meta.env.DATABASE_USER,
  password: import.meta.env.DATABASE_PASSWORD,
  database: import.meta.env.DATABASE_NAME,
})

// Initialize Drizzle
export const db = drizzle(pool)

Step 2: Implement a Query Cache Helper

Now, create a utility function that caches query results at the module level:

type CacheEntry<T> = {
  data: T
  timestamp: number
}

// Module-level cache that persists across page builds
const queryCache = new Map<string, CacheEntry<any>>()

/**
 * Execute a database query with caching
 * @param key - Unique identifier for this query
 * @param queryFn - Function that executes the database query
 * @returns Cached or fresh query results
 */
export async function cachedQuery<T>(
  key: string,
  queryFn: () => Promise<T>
): Promise<T> {
  // Check if we have a cached result
  if (queryCache.has(key)) {
    const cached = queryCache.get(key)!
    console.log(`[Cache HIT] Using cached data for: ${key}`)
    return cached.data as T
  }

  // Execute the query if no cache exists
  console.log(`[Cache MISS] Executing query for: ${key}`)
  const data = await queryFn()

  // Store in cache
  queryCache.set(key, {
    data,
    timestamp: Date.now(),
  })

  return data
}

/**
 * Clear the entire cache (useful for development)
 */
export function clearCache(): void {
  queryCache.clear()
  console.log('[Cache] Cleared all cached queries')
}

/**
 * Clear a specific cache entry
 */
export function clearCacheKey(key: string): void {
  queryCache.delete(key)
  console.log(`[Cache] Cleared cache for: ${key}`)
}

Step 3: Use the Cached Query in Your Pages

Now, update your pages to use the cached query function:

---
import { db } from '@/lib/db'
import { cachedQuery } from '@/lib/db/cache'
import { productsTable } from '@/lib/db/schema'

const products = await cachedQuery('all-products', async () => {
  return await db.select().from(productsTable)
})
---

<!DOCTYPE html>
<html lang="en">
<head>
  <meta charset="UTF-8">
  <meta name="viewport" content="width=device-width, initial-scale=1.0">
  <title>Products - Foo</title>
</head>
<body>
  <h1>All Products</h1>
  <p>{products.length} products available</p>
</body>
</html>

---
import { db } from '@/lib/db'
import { cachedQuery } from '@/lib/db/cache'
import { productsTable } from '@/lib/db/schema'

const products = await cachedQuery('all-products', async () => {
  return await db.select().from(productsTable)
})
---

<!DOCTYPE html>
<html lang="en">
<head>
  <meta charset="UTF-8">
  <meta name="viewport" content="width=device-width, initial-scale=1.0">
  <title>Products - Bar</title>
</head>
<body>
  <h1>Product List</h1>
  <ul>
    {products.map(product => (
      <li>{product.name}</li>
    ))}
  </ul>
</body>
</html>

---
import { db } from '@/lib/db'
import { cachedQuery } from '@/lib/db/cache'
import { productsTable } from '@/lib/db/schema'

const products = await cachedQuery('all-products', async () => {
  return await db.select().from(productsTable)
})
---

<!DOCTYPE html>
<html lang="en">
<head>
  <meta charset="UTF-8">
  <meta name="viewport" content="width=device-width, initial-scale=1.0">
  <title>Products - Ipsum</title>
</head>
<body>
  <h1>Product Summary</h1>
  <p>Total products: {products.length}</p>
  <p>Average price: ${(products.reduce((sum, p) => sum + p.price, 0) / products.length).toFixed(2)}</p>
</body>
</html>

Step 4: Verify the Optimization

When you run npm run build, you’ll see console logs indicating that the query is only executed once:

[Cache MISS] Executing query for: all-products
[Cache HIT] Using cached data for: all-products
[Cache HIT] Using cached data for: all-products

The first page triggers the database query, and subsequent pages reuse the cached result.

Advanced: Cache with TTL for Development

During development, you might want to refresh the cache periodically. Here’s an enhanced version with Time-To-Live (TTL) support:

type CacheEntry<T> = {
  data: T
  timestamp: number
}

const queryCache = new Map<string, CacheEntry<any>>()

// Default TTL: 5 minutes (only used in dev mode)
const DEFAULT_TTL = 5 * 60 * 1000

export async function cachedQuery<T>(
  key: string,
  queryFn: () => Promise<T>,
  options?: {
    ttl?: number
    forceRefresh?: boolean
  }
): Promise<T> {
  const { ttl = DEFAULT_TTL, forceRefresh = false } = options || {}

  // Force refresh if requested
  if (forceRefresh) {
    queryCache.delete(key)
  }

  // Check cache
  if (queryCache.has(key)) {
    const cached = queryCache.get(key)!
    const isExpired = import.meta.env.DEV && Date.now() - cached.timestamp > ttl

    if (!isExpired) {
      console.log(`[Cache HIT] Using cached data for: ${key}`)
      return cached.data as T
    }

    console.log(`[Cache EXPIRED] Refreshing data for: ${key}`)
    queryCache.delete(key)
  }

  // Execute query
  console.log(`[Cache MISS] Executing query for: ${key}`)
  const data = await queryFn()

  queryCache.set(key, {
    data,
    timestamp: Date.now(),
  })

  return data
}

/**
 * Get cache statistics
 */
export function getCacheStats() {
  return {
    size: queryCache.size,
    keys: Array.from(queryCache.keys()),
  }
}

Now you can use it with custom TTL:

---
// Cache for 10 minutes in dev, forever during build
const products = await cachedQuery(
  'all-products',
  async () => db.select().from(productsTable),
  { ttl: 10 * 60 * 1000 }
)
---

How It Works

The caching mechanism works because of how Node.js modules are loaded:

Module Singleton: The queryCache Map is defined at the module level, making it a singleton that persists for the entire build process.
Build Process: During npm run build, Astro runs in a single Node.js process. When multiple pages import the same module, Node.js returns the same module instance.
Cache Key: Using a unique cache key (e.g., 'all-products') ensures different queries don’t collide.
First Query: The first page that needs the data executes the query and stores the result in the cache.
Subsequent Queries: All subsequent pages check the cache first and reuse the existing result.

Performance Benefits

Let’s compare the performance impact:

Without Caching:

100 pages × 200ms per query = 20 seconds of database query time
Increased database load
Higher cloud database costs

With Caching:

1 query × 200ms = 200ms of database query time
Minimal database load
Significant cost reduction

For a real-world example, if you have a product catalog with 1,000 products and 50 pages that need this data:

Without cache: 50 database queries during build
With cache: 1 database query during build
Time saved: ~10-30 seconds per build (depending on database latency)

Best Practices

Use Descriptive Cache Keys: Make cache keys descriptive and unique:

// Good
await cachedQuery('products-active', () => db.select().from(products).where(eq(products.active, true)))
await cachedQuery('products-all', () => db.select().from(products))

// Bad
await cachedQuery('data', () => db.select().from(products))

Cache at the Right Level: Cache data that is truly shared across pages. Don’t cache page-specific queries:

// Good: Shared data
await cachedQuery('site-settings', () => db.select().from(settings))

// Bad: Page-specific data
await cachedQuery(`user-${userId}`, () => db.select().from(users).where(eq(users.id, userId)))

Monitor Cache Usage: Add logging to understand cache effectiveness:

import { getCacheStats } from '@/lib/db/cache'

// After build, log cache statistics
console.log('Cache statistics:', getCacheStats())

Consider Memory Usage: For very large datasets, consider implementing cache size limits:

const MAX_CACHE_SIZE = 100 // Maximum number of cached queries

if (queryCache.size >= MAX_CACHE_SIZE) {
  // Remove oldest entry
  const oldestKey = queryCache.keys().next().value
  queryCache.delete(oldestKey)
}

Conclusion

In this guide, you learned how to optimize database calls during Static Site Generation in Astro by implementing a module-level query cache. This approach ensures that shared data is fetched only once during the build process, dramatically improving build performance and reducing database load. This pattern is particularly valuable when building content-heavy sites with hundreds or thousands of pages that share common data sources.

If you have any questions or comments, feel free to reach out to me on Twitter.