ISR with Databases — Fetching Database Content with ISR

// lib/db.js — Database connection pool
const { Pool } = require('pg');

const pool = new Pool({
    connectionString: process.env.DATABASE_URL,
    max: 10,                    // Max connections in pool
    idleTimeoutMillis: 30000,   // Close idle connections after 30s
    connectionTimeoutMillis: 5000, // Fail fast if DB is down
});

export async function query(text, params) {
    const client = await pool.connect();
    try {
        const result = await client.query(text, params);
        return result.rows;
    } catch (err) {
        console.error('Database query error:', err);
        throw err;
    } finally {
        client.release();
    }
}

// pages/products/[id].js — ISR with PostgreSQL
export async function getStaticProps({ params }) {
    const start = Date.now();

    try {
        const products = await query(
            'SELECT id, name, price, description, stock, category_id, image_url, updated_at FROM products WHERE id = $1 AND active = true',
            [params.id]
        );

        if (products.length === 0) {
            return { notFound: true };
        }

        const product = products[0];

        // Also fetch category name
        const categories = await query(
            'SELECT name FROM categories WHERE id = $1',
            [product.category_id]
        );

        console.log(`DB query for product ${params.id}: ${Date.now() - start}ms`);

        return {
            props: {
                product: {
                    ...product,
                    category: categories[0]?.name || 'Uncategorized'
                },
                generatedAt: Date.now()
            },
            revalidate: 60
        };
    } catch (err) {
        console.error('Failed to fetch product:', err);
        return { notFound: true };
    }
}

// lib/mongodb.js — MongoDB connection for ISR
const { MongoClient } = require('mongodb');

const uri = process.env.MONGODB_URI;
const client = new MongoClient(uri, {
    maxPoolSize: 10,
    serverSelectionTimeoutMS: 5000,
    socketTimeoutMS: 10000,
});

let cachedClient = null;
let cachedDb = null;

export async function connectToDatabase() {
    if (cachedClient && cachedDb) {
        return { client: cachedClient, db: cachedDb };
    }

    const client = await MongoClient.connect(uri, {
        maxPoolSize: 10,
        serverSelectionTimeoutMS: 5000,
    });

    const db = client.db(process.env.MONGODB_DB || 'myapp');
    cachedClient = client;
    cachedDb = db;

    return { client, db };
}

// pages/blog/[slug].js — ISR with MongoDB
export async function getStaticProps({ params }) {
    const start = Date.now();

    try {
        const { db } = await connectToDatabase();
        const collection = db.collection('posts');

        const post = await collection.findOne({
            slug: params.slug,
            status: 'published'
        });

        if (!post) {
            return { notFound: true };
        }

        console.log(`MongoDB query for ${params.slug}: ${Date.now() - start}ms`);

        return {
            props: {
                post: {
                    id: post._id.toString(),
                    title: post.title,
                    content: post.content,
                    slug: post.slug,
                    author: post.author,
                    publishedAt: post.publishedAt?.toISOString(),
                    tags: post.tags || []
                },
                generatedAt: Date.now()
            },
            revalidate: 300
        };
    } catch (err) {
        console.error('MongoDB query failed:', err);
        return { notFound: true };
    }
}

// lib/sqlite.js — SQLite for ISR (local/edge databases)
const Database = require('better-sqlite3');
const path = require('path');

const DB_PATH = path.join(process.cwd(), 'data', 'content.db');

let db = null;

function getDatabase() {
    if (!db) {
        db = new Database(DB_PATH, {
            readonly: true,  // ISR only reads — writes via separate process
            fileMustExist: true
        });
        db.pragma('journal_mode = WAL');
    }
    return db;
}

// pages/docs/[slug].js — ISR with SQLite
export async function getStaticProps({ params }) {
    try {
        const database = getDatabase();

        const stmt = database.prepare(`
            SELECT d.*, c.name as category_name
            FROM documents d
            LEFT JOIN categories c ON d.category_id = c.id
            WHERE d.slug = ? AND d.status = 'published'
        `);

        const doc = stmt.get(params.slug);

        if (!doc) {
            return { notFound: true };
        }

        return {
            props: {
                doc: {
                    ...doc,
                    created_at: doc.created_at?.toISOString(),
                    updated_at: doc.updated_at?.toISOString()
                },
                generatedAt: Date.now()
            },
            revalidate: 3600
        };
    } catch (err) {
        console.error('SQLite query failed:', err);
        return { notFound: true };
    }
}

// lib/db-best-practices.js — ISR database patterns
const dbBestPractices = {
    connectionPool: {
        why: 'ISR functions may run on multiple serverless instances simultaneously',
        practice: 'Use a connection pool (max 5-10 connections)',
        config: 'max: 10, idleTimeoutMillis: 30000'
    },
    queryTimeout: {
        why: 'ISR revalidation has a 60-second timeout on Vercel',
        practice: 'Set query timeouts (5-10 seconds max)',
        config: 'statement_timeout: 5000'
    },
    readReplicas: {
        why: 'ISR only reads data — use read replicas to avoid production DB load',
        practice: 'Configure a separate read-only database connection',
        config: 'DATABASE_URL_READONLY environment variable'
    },
    caching: {
        why: 'ISR already caches HTML, but frequent revalidations still query DB',
        practice: 'Add an in-memory query cache layer',
        config: 'Use node-cache or Redis for query results'
    },
    errorHandling: {
        why: 'DB failures during revalidation keep stale content',
        practice: 'Return fallback data instead of crashing',
        config: 'try-catch with fallback return'
    },
    monitoring: {
        why: 'Track database performance during revalidation',
        practice: 'Log query duration and error rates',
        config: 'console.time() + structured logging'
    }
};