Are you working with @forge/sql and struggling with pagination + total count?

Here's a more efficient and safer way to do it using window functions.

💡 The problem

In typical pagination, we often do two queries:

SELECT * FROM users LIMIT 10 OFFSET 20;
SELECT COUNT(*) FROM users;

But if your SQL engine supports window functions — like TiDB in Atlassian Forge — you can get everything in a single query:

SELECT 
  id,
  name,
  COUNT(*) OVER() AS total_count
FROM users
ORDER BY name
LIMIT 10 OFFSET 20;

You’ll get a total_count in each row — and no second query needed.

✅ Using it with forge-sql-orm 

import { sql } from "drizzle-orm";
import ForgeSQL from "forge-sql-orm";
import { users } from "./schema";

const forgeSQL = new ForgeSQL();

const result = await forgeSQL
  .select({
    id: users.id,
    name: users.name,
    totalCount: sql<number>`COUNT(*) OVER()`,
  })
  .from(users)
  .orderBy(users.name)
  .offset(formatLimitOffset(offset))
  .limit(formatLimitOffset(limit));

And here’s a safe helper for LIMIT/OFFSET:

export function formatLimitOffset(limitOrOffset: number): number {
  if (typeof limitOrOffset !== "number" || isNaN(limitOrOffset)) {
    throw new Error("limitOrOffset must be a valid number");
  }
  return sql.raw(`${limitOrOffset}`) as unknown as number;
}

⚠️ Why this matters — Forge SQL doesn’t support bind params for LIMIT/OFFSET

Libraries like Drizzle normally compile queries like:

SELECT ... FROM users ORDER BY name LIMIT ? OFFSET ?

But Forge SQL (powered by TiDB) doesn’t support parameterized LIMIT or OFFSET.

So you have to inline the values — carefully.

🔐 And yes, you can still get SQL injection in .prepare(...)

Even this Forge-native code is vulnerable, if limit or offset come from user input:

const result = await sql
  .prepare<User>(`SELECT * FROM users LIMIT ${limit} OFFSET ${offset}`)
  .execute();

Just because limit: number in TypeScript doesn’t mean it’s actually a number at runtime.

✅ Use runtime checks:

function safeLimitOffset(value: unknown): number {
  if (typeof value !== "number" || isNaN(value)) {
    throw new Error("Invalid limit or offset");
  }
  return value;
}

✅ One query, safer code, better performance

This pattern works in Atlassian Forge SQL, MySQL, TiDB, PlanetScale, and more.

If you're using @forge/sql, forge-sql-orm, or raw Drizzle — this will help keep things clean and fast.

🧑‍💻 Project: forge-sql-orm

Questions and feedback welcome!