Server-Side Rendering with Knex: SSR, SSG, ISR

Server-Side Rendering with Knex: SSR, SSG, ISR

Modern web applications demand fast initial page loads, SEO-friendly content, and dynamic data updates. While Knex.js is primarily known as a SQL query builder, it pairs beautifully with rendering strategies like Server-Side Rendering (SSR), Static Site Generation (SSG), and Incremental Static Regeneration (ISR). This tutorial walks you through integrating Knex with each of these rendering patterns, using a Next.js-style mental model that applies to most Node.js frameworks.

What Are SSR, SSG, and ISR?

Before diving into code, let's clarify the three rendering strategies:

• SSR (Server-Side Rendering): The page is rendered on the server on every request. Best for highly dynamic, personalized content.
• SSG (Static Site Generation): The page is rendered once at build time and served as static HTML. Best for content that rarely changes.
• ISR (Incremental Static Regeneration): A hybrid approach where static pages are regenerated in the background at a configurable interval. Best for content that updates periodically but doesn't need real-time freshness.

Knex fits into all three as the data layer that fetches rows from your database before the HTML is generated.

Why Knex for Rendering?

Knex provides a consistent, promise-based query interface across PostgreSQL, MySQL, SQLite, and others. When rendering on the server, you need a reliable way to query data synchronously (relative to the render lifecycle) without leaking connections. Knex's connection pooling, transaction support, and raw query escape hatches make it ideal for SSR pipelines where many requests hit the database concurrently.

Setting Up Knex

First, install Knex and your database driver. We'll use PostgreSQL in this tutorial.

npm install knex pg
npm install -D knex-cli

Create a knexfile.js at your project root:

// knexfile.js
module.exports = {
  development: {
    client: 'pg',
    connection: process.env.DATABASE_URL || {
      host: 'localhost',
      port: 5432,
      user: 'postgres',
      password: 'postgres',
      database: 'tutorial'
    },
    pool: { min: 2, max: 10 },
    migrations: {
      directory: './migrations'
    },
    seeds: {
      directory: './seeds'
    }
  },
  production: {
    client: 'pg',
    connection: process.env.DATABASE_URL,
    pool: { min: 2, max: 20 },
    migrations: {
      directory: './migrations'
    }
  }
};

Create a shared Knex instance that you can import anywhere in your app:

// db/knex.js
const knex = require('knex');
const config = require('../knexfile');

const environment = process.env.NODE_ENV || 'development';
const db = knex(config[environment]);

module.exports = db;

Generate a sample migration for a posts table:

npx knex migrate:make create_posts_table

// migrations/20240101000000_create_posts_table.js
exports.up = function(knex) {
  return knex.schema.createTable('posts', (table) => {
    table.increments('id').primary();
    table.string('title').notNullable();
    table.text('body').notNullable();
    table.string('slug').unique().notNullable();
    table.boolean('published').defaultTo(false);
    table.timestamps(true, true);
  });
};

exports.down = function(knex) {
  return knex.schema.dropTableIfExists('posts');
};

Run the migration and seed some data:

npx knex migrate:latest
npx knex seed:make posts

// seeds/posts.js
exports.seed = async function(knex) {
  await knex('posts').del();
  await knex('posts').insert([
    { title: 'Getting Started with Knex', body: 'Knex is a query builder...', slug: 'getting-started-with-knex', published: true },
    { title: 'SSR Patterns in 2024', body: 'SSR remains relevant...', slug: 'ssr-patterns-2024', published: true },
    { title: 'ISR Explained', body: 'ISR bridges SSG and SSR...', slug: 'isr-explained', published: true }
  ]);
};

SSR: Rendering on Every Request

With SSR, every incoming request triggers a fresh database query and a fresh HTML render. This is the most dynamic strategy and the simplest to reason about with Knex.

Implementing SSR with Knex

Here's an Express server that renders a list of posts server-side using a template engine:

// server/ssr.js
const express = require('express');
const db = require('../db/knex');

const app = express();

app.set('view engine', 'ejs');
app.set('views', './views');

app.get('/', async (req, res, next) => {
  try {
    const posts = await db('posts')
      .select('id', 'title', 'slug', 'created_at')
      .where({ published: true })
      .orderBy('created_at', 'desc')
      .limit(20);

    res.render('index', { posts });
  } catch (err) {
    next(err);
  }
});

app.get('/posts/:slug', async (req, res, next) => {
  try {
    const post = await db('posts')
      .where({ slug: req.params.slug, published: true })
      .first();

    if (!post) {
      return res.status(404).render('404');
    }

    res.render('post', { post });
  } catch (err) {
    next(err);
  }
});

module.exports = app;

The corresponding EJS template for the index page:

<!-- views/index.ejs -->
<!DOCTYPE html>
<html>
<head>
  <title>My Blog</title>
</head>
<body>
  <h1>Recent Posts</h1>
  <ul>
    <% posts.forEach(post => { %>
      <li>
        <a href="/posts/<%= post.slug %>"><%= post.title %></a>
      </li>
    <% }) %>
  </ul>
</body>
</html>

SSR with Next.js

If you're using Next.js, SSR maps to getServerSideProps (App Router uses server components by default). Here's the Pages Router approach:

// pages/posts/[slug].js
import db from '../../db/knex';

export async function getServerSideProps(context) {
  const { slug } = context.params;

  const post = await db('posts')
    .where({ slug, published: true })
    .first();

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

  return {
    props: {
      post: JSON.parse(JSON.stringify(post))
    }
  };
}

export default function PostPage({ post }) {
  return (
    <article>
      <h1>{post.title}</h1>
      <div>{post.body}</div>
    </article>
  );
}

Notice the JSON.parse(JSON.stringify(post)) step. Knex returns RowDataPacket-like objects that may carry non-serializable metadata. Next.js requires props to be plain JSON, so this serialization is essential.

SSG: Rendering at Build Time

SSG renders pages once when you build your application. The database is queried during the build, and the resulting HTML is cached indefinitely until the next build. This is perfect for blogs, documentation, and marketing pages.

Implementing SSG with Knex

In Next.js Pages Router, SSG uses getStaticProps and getStaticPaths:

// pages/posts/[slug].js (SSG version)
import db from '../../db/knex';

export async function getStaticPaths() {
  const posts = await db('posts')
    .select('slug')
    .where({ published: true });

  return {
    paths: posts.map((post) => ({
      params: { slug: post.slug }
    })),
    fallback: false
  };
}

export async function getStaticProps({ params }) {
  const post = await db('posts')
    .where({ slug: params.slug, published: true })
    .first();

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

  return {
    props: {
      post: JSON.parse(JSON.stringify(post))
    }
  };
}

export default function PostPage({ post }) {
  return (
    <article>
      <h1>{post.title}</h1>
      <div>{post.body}</div>
    </article>
  );
}

Building a Static Site with a Custom Script

If you're not using Next.js, you can build a static site yourself by querying Knex and writing HTML files:

// scripts/build-static.js
const fs = require('fs').promises;
const path = require('path');
const db = require('../db/knex');

async function renderTemplate(template, data) {
  // Replace simple placeholders
  let html = template;
  for (const [key, value] of Object.entries(data)) {
    html = html.replaceAll(`{{${key}}}`, value);
  }
  return html;
}

async function build() {
  const posts = await db('posts').where({ published: true });
  const outputDir = path.join(__dirname, '../dist');

  await fs.mkdir(outputDir, { recursive: true });
  await fs.mkdir(path.join(outputDir, 'posts'), { recursive: true });

  const postTemplate = await fs.readFile(
    path.join(__dirname, '../templates/post.html'),
    'utf-8'
  );

  for (const post of posts) {
    const html = await renderTemplate(postTemplate, {
      title: post.title,
      body: post.body
    });

    await fs.writeFile(
      path.join(outputDir, 'posts', `${post.slug}.html`),
      html
    );
    console.log(`Generated: posts/${post.slug}.html`);
  }

  await db.destroy();
  console.log('Build complete.');
}

build().catch((err) => {
  console.error(err);
  process.exit(1);
});

Run this script during your CI/CD build step. The generated HTML files can be deployed to any static host like S3, Netlify, or GitHub Pages.

ISR: Incremental Static Regeneration

ISR gives you the performance of SSG with the freshness of SSR. Pages are served statically, but the server regenerates them in the background at a configurable interval. The first request after the interval triggers regeneration while still serving the stale page; subsequent requests get the fresh version.

Implementing ISR with Knex in Next.js

// pages/posts/[slug].js (ISR version)
import db from '../../db/knex';

export async function getStaticPaths() {
  const posts = await db('posts')
    .select('slug')
    .where({ published: true });

  return {
    paths: posts.map((post) => ({
      params: { slug: post.slug }
    })),
    fallback: 'blocking' // Generate new pages on demand
  };
}

export async function getStaticProps({ params }) {
  const post = await db('posts')
    .where({ slug: params.slug, published: true })
    .first();

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

  return {
    props: {
      post: JSON.parse(JSON.stringify(post))
    },
    revalidate: 60 // Regenerate at most once per 60 seconds
  };
}

export default function PostPage({ post }) {
  return (
    <article>
      <h1>{post.title}</h1>
      <div>{post.body}</div>
      <small>Last updated: {post.updated_at}</small>
    </article>
  );
}

The revalidate: 60 option tells Next.js to regenerate this page in the background if a request comes in more than 60 seconds after the last build. Setting fallback: 'blocking' means new slugs not present at build time will be generated on the first request and then cached.

On-Demand Revalidation

For content that updates unpredictably (like when an editor publishes a new post), you can trigger revalidation on demand via an API route:

// pages/api/revalidate.js
import db from '../../db/knex';

export default async function handler(req, res) {
  const { secret, slug } = req.query;

  if (secret !== process.env.REVALIDATION_SECRET) {
    return res.status(401).json({ message: 'Invalid token' });
  }

  try {
    if (slug) {
      await res.revalidate(`/posts/${slug}`);
    } else {
      // Revalidate all published posts
      const posts = await db('posts')
        .select('slug')
        .where({ published: true });

      for (const post of posts) {
        await res.revalidate(`/posts/${post.slug}`);
      }
    }

    return res.json({ revalidated: true });
  } catch (err) {
    return res.status(500).json({ message: 'Revalidation failed', error: err.message });
  }
}

Trigger this endpoint from your CMS webhook whenever content changes:

curl "https://yourapp.com/api/revalidate?secret=YOUR_SECRET&slug=isr-explained"

Best Practices

Manage Connection Pooling Carefully

SSR means many concurrent requests hitting the database simultaneously. Configure your Knex pool size based on your database's max connections. A common mistake is setting max too high, which exhausts the database connection limit.

// Production pool configuration
pool: {
  min: 2,
  max: 20,
  acquireTimeoutMillis: 30000,
  createTimeoutMillis: 30000,
  idleTimeoutMillis: 30000,
  reapIntervalMillis: 1000,
  createRetryIntervalMillis: 100
}

Always Destroy Connections in Build Scripts

When using Knex in SSG build scripts or getStaticProps, the Node process may hang if you don't close the connection pool. Always call db.destroy() at the end of standalone scripts:

async function buildAll() {
  try {
    const posts = await db('posts').where({ published: true });
    // ... render logic
  } finally {
    await db.destroy();
  }
}

In Next.js, you generally don't need to call destroy() inside getStaticProps or getServerSideProps because the framework manages the process lifecycle. However, if you notice hanging builds, it's worth investigating.

Serialize Data Properly

Knex returns objects with prototype methods and sometimes Date objects. When passing data to React components or JSON-based templates, always serialize:

// Safe serialization helper
function serialize(data) {
  return JSON.parse(JSON.stringify(data));
}

// Usage in getServerSideProps
const post = serialize(await db('posts').where({ slug }).first());

Use Transactions for Multi-Query Renders

If a single render requires multiple related queries, wrap them in a transaction to ensure consistency:

export async function getServerSideProps({ params }) {
  const result = await db.transaction(async (trx) => {
    const post = await trx('posts')
      .where({ slug: params.slug, published: true })
      .first();

    if (!post) return null;

    const comments = await trx('comments')
      .where({ post_id: post.id })
      .orderBy('created_at', 'desc')
      .limit(50);

    return { post, comments };
  });

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

  return {
    props: {
      post: JSON.parse(JSON.stringify(result.post)),
      comments: JSON.parse(JSON.stringify(result.comments))
    }
  };
}

Cache Expensive Queries

Even with SSR, some queries don't need to run on every request. Use an in-memory cache like node-cache or Redis for data that changes infrequently:

const NodeCache = require('node-cache');
const cache = new NodeCache({ stdTTL: 30 });

async function getPublishedPosts() {
  const cached = cache.get('published_posts');
  if (cached) return cached;

  const posts = await db('posts')
    .where({ published: true })
    .orderBy('created_at', 'desc');

  cache.set('published_posts', posts);
  return posts;
}

Choose the Right Strategy Per Route

• Use SSG for landing pages, blog posts, documentation, and any content that changes less than once per day.
• Use ISR for content that updates periodically but doesn't need real-time accuracy, like product catalogs or news articles.
• Use SSR for personalized dashboards, search results, and any page that depends on the authenticated user.

Handle Errors Gracefully

Database queries can fail. Always wrap Knex calls in try/catch and provide fallback content or a proper error page:

export async function getServerSideProps({ params }) {
  try {
    const post = await db('posts')
      .where({ slug: params.slug, published: true })
      .first();

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

    return { props: { post: JSON.parse(JSON.stringify(post)) } };
  } catch (error) {
    console.error('Database query failed:', error);
    return {
      props: {
        error: 'Unable to load post. Please try again later.'
      }
    };
  }
}

Conclusion

Knex is a versatile query builder that works seamlessly across all three major server-side rendering strategies. By understanding when to use SSR for dynamic personalization, SSG for maximum performance on stable content, and ISR for the best of both worlds, you can architect applications that are both fast and fresh. The key is to pair Knex's connection pooling and transaction support with thoughtful caching, proper serialization, and error handling. Start with SSG for your static content, layer in ISR where freshness matters, and reserve SSR for truly dynamic, per-request data. With these patterns in place, your Knex-powered application will deliver excellent performance without sacrificing data integrity or developer experience.