sql-flex-query

A lightweight, dialect-aware SQL query builder that enhances base query templates with dynamic WHERE, HAVING, ORDER BY, pagination, and more. Also provides helpers for building INSERT, UPDATE, and DELETE queries with dialect-specific placeholders.

Write once, run on any SQL database — PostgreSQL, MySQL, SQLite, SQL Server, Oracle, CockroachDB, Snowflake. Need another database? Extend BaseDialect to add custom dialect support (see below).

Supported Databases

Database
PostgreSQL
MySQL
SQLite
SQL Server
Oracle
CockroachDB
Snowflake

Why sql-flex-query?

The Problem

Different SQL databases use different placeholder styles, identifier quoting, and pagination syntax:

Database	Placeholders	Identifier Quoting	Pagination
PostgreSQL	`$1, $2`	`"double quotes"`	`LIMIT/OFFSET`
MySQL	`?`	`backticks`	`LIMIT/OFFSET`
SQLite	`?`	`"double quotes"`	`LIMIT/OFFSET`
SQL Server	`@p1, @p2`	`[brackets]`	`OFFSET/FETCH`
Oracle	`:1, :2`	`"double quotes"`	`OFFSET/FETCH`
CockroachDB	`$1, $2`	`"double quotes"`	`LIMIT/OFFSET`
Snowflake	`?`	`"double quotes"`	`LIMIT/OFFSET`

Maintaining separate queries for each dialect is error-prone and hard to maintain.

The Solution

sql-flex-query lets you write one query that automatically adapts to any supported dialect. It handles:

✅ Placeholder conversion ($1 → ? → @p1)
✅ Identifier quoting ("col" → `col` → [col])
✅ Pagination syntax (LIMIT/OFFSET vs OFFSET/FETCH)
✅ Cursor-based (keyset) pagination for large or fast-changing tables
✅ TypeScript type safety
✅ Dynamic WHERE, ORDER BY, text search (OR conditions)
✅ GROUP BY and HAVING support
✅ Column mapping for clean code

Quick Comparison

Feature	sql-flex-query	Knex.js	Raw SQL	ORM (Prisma/TypeORM)
Multi-dialect	✅ Automatic	✅ Yes	❌ Manual	✅ Limited
Use existing SQL	✅ Yes	❌ No	✅ Yes	❌ No
TypeScript support	✅ Full	⚠️ Partial	❌ None	✅ Full
Learning curve	🟢 Low	🟡 Medium	🟢 Low	🔴 High
Migrations	❌ No	✅ Yes	❌ No	✅ Yes
Size	~15KB	~100KB	0KB	~200KB+
Flexibility	🟢 High	🟡 Medium	🟢 High	🔴 Low

Choose sql-flex-query if: You need multi-dialect support, want to use your existing SQL, value TypeScript types, and don't need built-in migrations.

Installation

npm install sql-flex-query

Quick Start

1. Simple Query with Filters

const { buildQueries } = require("sql-flex-query");

const BASE = `
  SELECT /*SELECT_COLUMNS*/
  FROM users u
  /*WHERE_CLAUSE*/
  /*ORDER_BY*/
  /*LIMIT_CLAUSE*/
`;

const result = buildQueries(
  BASE,
  [
    { key: "status", operation: "EQ", value: "ACTIVE" },
    { key: "age", operation: "GTE", value: 18 },
  ],
  [],
  [{ key: "createdAt", direction: "DESC" }],
  1,  // page
  10, // page size
  { createdAt: "u.created_at" }, // columnMapper (optional)
  ["id", "name", "email", "createdAt"], // selectColumns (optional)
  "postgres" // dialect (optional, defaults to postgres)
);

console.log(result.searchQuery);
// SELECT u.id AS "id", u.name AS "name", u.email AS "email", u.created_at AS "createdAt"
// FROM users u
// WHERE "status" = $1 AND "age" >= $2
// ORDER BY u.created_at DESC
// LIMIT 10 OFFSET 0

console.log(result.params);
// ['ACTIVE', 18]

That's it! The same code works for MySQL, SQLite, SQL Server, Oracle, CockroachDB, and Snowflake—just change the dialect parameter.

Basic Examples

1. Simple SELECT with WHERE and Pagination

const { buildQueries } = require("sql-flex-query");

const BASE = `
  SELECT /*SELECT_COLUMNS*/
  FROM users u
  /*WHERE_CLAUSE*/
  /*ORDER_BY*/
  /*LIMIT_CLAUSE*/
`;

const result = buildQueries(
  BASE,
  [
    { key: "status", operation: "EQ", value: "ACTIVE" },
    { key: "age", operation: "GTE", value: 18 },
  ],
  [],
  [{ key: "createdAt", direction: "DESC" }],
  1,
  10,
  { createdAt: "u.created_at" },
  ["id", "name", "email", "createdAt"],
);
// searchQuery: SELECT u.id AS "id", ... WHERE "status" = $1 AND u.created_at >= $2 ORDER BY ... LIMIT 10 OFFSET 0
// params: ['ACTIVE', 18]

2. BETWEEN — Range Filtering

Use BETWEEN to filter a column within a half-open range ([min, max)). Pass a two-element array as the value.

const result = buildQueries({
  baseQueryTemplate: `
    SELECT /*SELECT_COLUMNS*/
    FROM orders o
    /*WHERE_CLAUSE*/
    /*ORDER_BY*/
    /*LIMIT_CLAUSE*/
  `,
  whereParams: [
    // Matches: o.total >= 100 AND o.total < 500
    { key: "total", operation: "BETWEEN", value: [100, 500] },
    // Matches: o.created_at >= '2024-01-01' AND o.created_at < '2025-01-01'
    { key: "createdAt", operation: "BETWEEN", value: ["2024-01-01", "2025-01-01"] },
  ],
  columnMapper: { total: "o.total", createdAt: "o.created_at" },
  dialect: "postgres",
});
// WHERE (o.total >= $1 AND o.total < $2) AND (o.created_at >= $3 AND o.created_at < $4)
// params: [100, 500, '2024-01-01', '2025-01-01']

Note: BETWEEN generates (col >= min AND col < max) — inclusive lower bound, exclusive upper bound. The value must be an array of exactly two elements.

3. Text Search (OR) + Filters (AND)

const result = buildQueries({
  baseQueryTemplate: `
    SELECT /*SELECT_COLUMNS*/
    FROM products p
    /*WHERE_CLAUSE*/
    /*ORDER_BY*/
    /*LIMIT_CLAUSE*/
  `,
  textSearchParams: [
    { key: "name", operation: "LIKE", value: "%laptop%", ignoreCase: true },
    {
      key: "description",
      operation: "LIKE",
      value: "%laptop%",
      ignoreCase: true,
    },
  ],
  whereParams: [
    { key: "status", operation: "EQ", value: "PUBLISHED" },
    { key: "price", operation: "LTE", value: 2000 },
    { key: "deleted_at", operation: "NULL" },
  ],
  sortBy: [{ key: "price", direction: "ASC" }],
  page: 1,
  size: 20,
  dialect: "postgres",
});
// WHERE (LOWER("name") LIKE $1 OR LOWER("description") LIKE $2)
//   AND "status" = $3 AND "price" <= $4 AND "deleted_at" IS NULL
// params: ['%laptop%', '%laptop%', 'PUBLISHED', 2000]

Complex Examples

4. Multi-table JOIN with Column Mapping

const { buildQueries } = require("sql-flex-query");

const BASE = `
  SELECT /*SELECT_COLUMNS*/
  FROM orders o
  JOIN customers c ON c.id = o.customer_id
  JOIN order_items oi ON oi.order_id = o.id
  JOIN products p ON p.id = oi.product_id
  /*WHERE_CLAUSE*/
  /*ORDER_BY*/
  /*LIMIT_CLAUSE*/
`;

const columnMapper = {
  orderId: "o.id",
  orderDate: "o.created_at",
  orderStatus: "o.status",
  customerName: "c.name",
  customerEmail: "c.email",
  productName: "p.name",
  quantity: "oi.quantity",
  unitPrice: "oi.unit_price",
};

const result = buildQueries({
  baseQueryTemplate: BASE,
  columnMapper,
  selectColumns: [
    "orderId",
    "orderDate",
    "orderStatus",
    "customerName",
    "customerEmail",
    "productName",
    "quantity",
    "unitPrice",
  ],
  whereParams: [
    { key: "orderStatus", operation: "IN", value: ["SHIPPED", "DELIVERED"] },
    { key: "orderDate", operation: "GTE", value: "2024-01-01" },
    { key: "orderDate", operation: "LTE", value: "2024-12-31" },
    { key: "unitPrice", operation: "GT", value: 0 },
  ],
  textSearchParams: [
    {
      key: "customerName",
      operation: "LIKE",
      value: "%john%",
      ignoreCase: true,
    },
    {
      key: "customerEmail",
      operation: "LIKE",
      value: "%john%",
      ignoreCase: true,
    },
    {
      key: "productName",
      operation: "LIKE",
      value: "%john%",
      ignoreCase: true,
    },
  ],
  sortBy: [
    { key: "orderDate", direction: "DESC" },
    { key: "customerName", direction: "ASC" },
  ],
  page: 1,
  size: 25,
  dialect: "postgres",
});

// searchQuery:
// SELECT o.id AS "orderId", o.created_at AS "orderDate", o.status AS "orderStatus",
//        c.name AS "customerName", c.email AS "customerEmail",
//        p.name AS "productName", oi.quantity AS "quantity", oi.unit_price AS "unitPrice"
// FROM orders o
// JOIN customers c ON c.id = o.customer_id
// JOIN order_items oi ON oi.order_id = o.id
// JOIN products p ON p.id = oi.product_id
// WHERE (LOWER(c.name) LIKE $1 OR LOWER(c.email) LIKE $2 OR LOWER(p.name) LIKE $3)
//   AND o.status IN ($4, $5)
//   AND o.created_at >= $6 AND o.created_at <= $7
//   AND oi.unit_price > $8
// ORDER BY o.created_at DESC, c.name ASC
// LIMIT 25 OFFSET 0
//
// params: ['%john%', '%john%', '%john%', 'SHIPPED', 'DELIVERED', '2024-01-01', '2024-12-31', 0]

5. GROUP BY + HAVING (Aggregation Reports)

const { buildQueries } = require("sql-flex-query");

// GROUP BY is part of the template.
// The builder handles WHERE (before GROUP BY) and HAVING (after GROUP BY) automatically.
const BASE = `
  SELECT /*SELECT_COLUMNS*/
  FROM orders o
  JOIN customers c ON c.id = o.customer_id
  JOIN order_items oi ON oi.order_id = o.id
  /*WHERE_CLAUSE*/
  GROUP BY c.id, c.name, c.email
  /*HAVING_CLAUSE*/
  /*ORDER_BY*/
  /*LIMIT_CLAUSE*/
`;

const columnMapper = {
  customerName: "c.name",
  customerEmail: "c.email",
  orderCount: "COUNT(DISTINCT o.id)",
  totalSpent: "SUM(oi.quantity * oi.unit_price)",
  avgOrderValue: "AVG(oi.quantity * oi.unit_price)",
  orderDate: "o.created_at",
  orderStatus: "o.status",
};

const result = buildQueries({
  baseQueryTemplate: BASE,
  columnMapper,
  selectColumns: [
    "customerName",
    "customerEmail",
    "orderCount",
    "totalSpent",
    "avgOrderValue",
  ],
  whereParams: [
    // WHERE filters — applied BEFORE GROUP BY
    { key: "orderStatus", operation: "IN", value: ["COMPLETED", "DELIVERED"] },
    { key: "orderDate", operation: "GTE", value: "2024-01-01" },
    { key: "orderDate", operation: "LTE", value: "2024-12-31" },

    // HAVING filters — set having: true — applied AFTER GROUP BY
    { key: "orderCount", operation: "GTE", value: 5, having: true },
    { key: "totalSpent", operation: "GTE", value: 1000, having: true },
  ],
  sortBy: [{ key: "totalSpent", direction: "DESC" }],
  page: 1,
  size: 10,
  dialect: "postgres",
});

// searchQuery:
// SELECT c.name AS "customerName", c.email AS "customerEmail",
//        COUNT(DISTINCT o.id) AS "orderCount",
//        SUM(oi.quantity * oi.unit_price) AS "totalSpent",
//        AVG(oi.quantity * oi.unit_price) AS "avgOrderValue"
// FROM orders o
// JOIN customers c ON c.id = o.customer_id
// JOIN order_items oi ON oi.order_id = o.id
// WHERE o.status IN ($1, $2) AND o.created_at >= $3 AND o.created_at <= $4
// GROUP BY c.id, c.name, c.email
// HAVING COUNT(DISTINCT o.id) >= $5 AND SUM(oi.quantity * oi.unit_price) >= $6
// ORDER BY SUM(oi.quantity * oi.unit_price) DESC
// LIMIT 10 OFFSET 0
//
// params: ['COMPLETED', 'DELIVERED', '2024-01-01', '2024-12-31', 5, 1000]

6. GROUP BY with modifyCountQuery

When using GROUP BY, the default COUNT gives wrong results. Use modifyCountQuery to wrap the count:

const BASE_WITH_GROUP = `
  SELECT /*SELECT_COLUMNS*/
  FROM app_messages msg
  LEFT JOIN app_locations loc ON msg.location_id = loc.id
  JOIN app_tasks task ON task.id = msg.task_id
  JOIN app_task_case_link tcl ON tcl.task_id = task.id
  JOIN (
    SELECT DISTINCT task_id, case_ref, org_code
    FROM app_case_snapshot WHERE is_current = true
  ) cs ON cs.task_id = tcl.task_id AND cs.case_ref = tcl.case_ref
  /*WHERE_CLAUSE*/
  GROUP BY DATE(task.created_at), cs.org_code, msg.is_structured
  /*HAVING_CLAUSE*/
  /*ORDER_BY*/ /*LIMIT_CLAUSE*/
`;

const columnMapper = {
  createdDate: "DATE(task.created_at)",
  organizationCode: "cs.org_code",
  structured: "msg.is_structured",
  messageCount: "COUNT(msg.id)",
  locationName: "loc.display_name",
  state: "msg.state",
};

const result = buildQueries({
  baseQueryTemplate: BASE_WITH_GROUP,
  columnMapper,
  selectColumns: [
    "createdDate",
    "organizationCode",
    "structured",
    "messageCount",
  ],
  whereParams: [
    { key: "state", operation: "EQ", value: "PROCESSED" },
    { key: "locationName", operation: "NOT_NULL" },
    { key: "messageCount", operation: "GT", value: 10, having: true },
  ],
  sortBy: [
    { key: "createdDate", direction: "DESC" },
    { key: "messageCount", direction: "DESC" },
  ],
  page: 1,
  size: 20,
  dialect: "postgres",
  // Wrap count query to count groups, not rows within groups
  modifyCountQuery: (query) =>
    `SELECT COUNT(*) AS count FROM (${query}) AS grouped_count`,
});

// countQuery:
// SELECT COUNT(*) AS count FROM (
//   SELECT 1 FROM app_messages msg
//   LEFT JOIN app_locations loc ON msg.location_id = loc.id
//   JOIN app_tasks task ON task.id = msg.task_id
//   JOIN app_task_case_link tcl ON tcl.task_id = task.id
//   JOIN (
//     SELECT DISTINCT task_id, case_ref, org_code
//     FROM app_case_snapshot WHERE is_current = true
//   ) cs ON cs.task_id = tcl.task_id AND cs.case_ref = tcl.case_ref
//   WHERE msg.state = $1 AND loc.display_name IS NOT NULL
//   GROUP BY DATE(task.created_at), cs.org_code, msg.is_structured
//   HAVING COUNT(msg.id) > $2
// ) AS grouped_count

7. LEFT JOIN + DISTINCT (Fluent API)

const { QueryBuilder } = require("sql-flex-query");

const BASE = `
  SELECT /*SELECT_COLUMNS*/
  FROM employees e
  LEFT JOIN departments d ON d.id = e.department_id
  LEFT JOIN employee_skills es ON es.employee_id = e.id
  LEFT JOIN skills s ON s.id = es.skill_id
  /*WHERE_CLAUSE*/
  /*ORDER_BY*/
  /*LIMIT_CLAUSE*/
`;

const result = new QueryBuilder("postgres")
  .baseQuery(BASE)
  .columnMapper({
    employeeId: "e.id",
    employeeName: "e.name",
    employeeEmail: "e.email",
    departmentName: "d.name",
    hireDate: "e.hire_date",
    salary: "e.salary",
    skillName: "s.name",
  })
  .select(["employeeId", "employeeName", "employeeEmail", "departmentName"])
  .distinct()
  .where([
    {
      key: "departmentName",
      operation: "IN",
      value: ["Engineering", "Product", "Design"],
    },
    { key: "hireDate", operation: "GTE", value: "2023-01-01" },
    { key: "salary", operation: "GTE", value: 50000 },
    { key: "salary", operation: "LTE", value: 150000 },
  ])
  .textSearch([
    {
      key: "employeeName",
      operation: "LIKE",
      value: "%sarah%",
      ignoreCase: true,
    },
    {
      key: "employeeEmail",
      operation: "LIKE",
      value: "%sarah%",
      ignoreCase: true,
    },
    { key: "skillName", operation: "LIKE", value: "%sarah%", ignoreCase: true },
  ])
  .orderBy([
    { key: "departmentName", direction: "ASC" },
    { key: "employeeName", direction: "ASC" },
  ])
  .paginate(2, 15)
  .build();

// searchQuery:
// SELECT DISTINCT e.id AS "employeeId", e.name AS "employeeName",
//        e.email AS "employeeEmail", d.name AS "departmentName"
// FROM employees e
// LEFT JOIN departments d ON d.id = e.department_id
// LEFT JOIN employee_skills es ON es.employee_id = e.id
// LEFT JOIN skills s ON s.id = es.skill_id
// WHERE (LOWER(e.name) LIKE $1 OR LOWER(e.email) LIKE $2 OR LOWER(s.name) LIKE $3)
//   AND d.name IN ($4, $5, $6)
//   AND e.hire_date >= $7 AND e.salary >= $8 AND e.salary <= $9
// ORDER BY d.name ASC, e.name ASC
// LIMIT 15 OFFSET 15
//
// params: ['%sarah%', '%sarah%', '%sarah%', 'Engineering', 'Product', 'Design',
//          '2023-01-01', 50000, 150000]

8. Sales Dashboard (Fluent API + GROUP BY + HAVING + MSSQL)

const { QueryBuilder } = require("sql-flex-query");

const BASE = `
  SELECT /*SELECT_COLUMNS*/
  FROM sales s
  JOIN sales_reps sr ON sr.id = s.rep_id
  JOIN regions r ON r.id = sr.region_id
  JOIN products p ON p.id = s.product_id
  JOIN product_categories pc ON pc.id = p.category_id
  /*WHERE_CLAUSE*/
  GROUP BY r.name, sr.name, pc.name, YEAR(s.sale_date), MONTH(s.sale_date)
  /*HAVING_CLAUSE*/
  /*ORDER_BY*/ /*LIMIT_CLAUSE*/
`;

const result = new QueryBuilder("mssql")
  .baseQuery(BASE)
  .columnMapper({
    regionName: "r.name",
    repName: "sr.name",
    categoryName: "pc.name",
    saleYear: "YEAR(s.sale_date)",
    saleMonth: "MONTH(s.sale_date)",
    totalRevenue: "SUM(s.amount)",
    totalUnits: "SUM(s.quantity)",
    dealCount: "COUNT(s.id)",
    avgDealSize: "AVG(s.amount)",
    saleDate: "s.sale_date",
    saleStatus: "s.status",
  })
  .select([
    "regionName",
    "repName",
    "categoryName",
    "saleYear",
    "saleMonth",
    "totalRevenue",
    "totalUnits",
    "dealCount",
    "avgDealSize",
  ])
  .where([
    { key: "saleDate", operation: "GTE", value: "2024-01-01" },
    { key: "saleDate", operation: "LTE", value: "2024-12-31" },
    { key: "saleStatus", operation: "EQ", value: "CLOSED_WON" },
    {
      key: "regionName",
      operation: "IN",
      value: ["North America", "Europe", "APAC"],
    },
  ])
  .having([
    { key: "totalRevenue", operation: "GTE", value: 50000 },
    { key: "dealCount", operation: "GTE", value: 3 },
  ])
  .orderBy([
    { key: "totalRevenue", direction: "DESC" },
    { key: "regionName", direction: "ASC" },
  ])
  .paginate(1, 20)
  .modifyCountQuery((query) => `SELECT COUNT(*) AS count FROM (${query}) AS t`)
  .build();

// searchQuery (SQL Server):
// SELECT r.name AS [regionName], sr.name AS [repName], pc.name AS [categoryName],
//        YEAR(s.sale_date) AS [saleYear], MONTH(s.sale_date) AS [saleMonth],
//        SUM(s.amount) AS [totalRevenue], SUM(s.quantity) AS [totalUnits],
//        COUNT(s.id) AS [dealCount], AVG(s.amount) AS [avgDealSize]
// FROM sales s
// JOIN sales_reps sr ON sr.id = s.rep_id
// JOIN regions r ON r.id = sr.region_id
// JOIN products p ON p.id = s.product_id
// JOIN product_categories pc ON pc.id = p.category_id
// WHERE s.sale_date >= @p1 AND s.sale_date <= @p2
//   AND s.status = @p3 AND r.name IN (@p4, @p5, @p6)
// GROUP BY r.name, sr.name, pc.name, YEAR(s.sale_date), MONTH(s.sale_date)
// HAVING SUM(s.amount) >= @p7 AND COUNT(s.id) >= @p8
// ORDER BY SUM(s.amount) DESC, r.name ASC OFFSET 0 ROWS FETCH NEXT 20 ROWS ONLY
//
// params: ['2024-01-01', '2024-12-31', 'CLOSED_WON',
//          'North America', 'Europe', 'APAC', 50000, 3]

9. Subquery JOIN with MySQL (Fluent API)

const { QueryBuilder } = require("sql-flex-query");

const BASE = `
  SELECT /*SELECT_COLUMNS*/
  FROM users u
  JOIN (
    SELECT user_id,
           COUNT(*) as login_count,
           MAX(login_at) as last_login
    FROM login_history
    WHERE login_at >= '2024-01-01'
    GROUP BY user_id
  ) lh ON lh.user_id = u.id
  LEFT JOIN user_subscriptions us ON us.user_id = u.id AND us.is_active = true
  LEFT JOIN plans p ON p.id = us.plan_id
  /*WHERE_CLAUSE*/
  /*ORDER_BY*/
  /*LIMIT_CLAUSE*/
`;

const result = new QueryBuilder("mysql")
  .baseQuery(BASE)
  .columnMapper({
    userId: "u.id",
    userName: "u.name",
    userEmail: "u.email",
    loginCount: "lh.login_count",
    lastLogin: "lh.last_login",
    planName: "p.name",
    userStatus: "u.status",
    userRole: "u.role",
  })
  .select([
    "userId",
    "userName",
    "userEmail",
    "loginCount",
    "lastLogin",
    "planName",
  ])
  .where([
    { key: "userStatus", operation: "EQ", value: "ACTIVE" },
    { key: "loginCount", operation: "GTE", value: 10 },
    {
      key: "userRole",
      operation: "IN",
      value: ["ADMIN", "PREMIUM", "ENTERPRISE"],
    },
    { key: "planName", operation: "NOT_NULL" },
  ])
  .textSearch([
    {
      key: "userName",
      operation: "LIKE",
      value: "%search_term%",
      ignoreCase: true,
    },
    {
      key: "userEmail",
      operation: "LIKE",
      value: "%search_term%",
      ignoreCase: true,
    },
  ])
  .orderBy([
    { key: "loginCount", direction: "DESC" },
    { key: "lastLogin", direction: "DESC" },
  ])
  .paginate(1, 50)
  .build();

// searchQuery (MySQL):
// SELECT u.id AS \`userId\`, u.name AS \`userName\`, u.email AS \`userEmail\`,
//        lh.login_count AS \`loginCount\`, lh.last_login AS \`lastLogin\`,
//        p.name AS \`planName\`
// FROM users u
// JOIN (...) lh ON lh.user_id = u.id
// LEFT JOIN user_subscriptions us ON us.user_id = u.id AND us.is_active = true
// LEFT JOIN plans p ON p.id = us.plan_id
// WHERE (\`userName\` LIKE ? OR \`userEmail\` LIKE ?)
//   AND u.status = ? AND lh.login_count >= ?
//   AND u.role IN (?, ?, ?) AND p.name IS NOT NULL
// ORDER BY lh.login_count DESC, lh.last_login DESC
// LIMIT 50 OFFSET 0
//
// params: ['%search_term%', '%search_term%', 'ACTIVE', 10, 'ADMIN', 'PREMIUM', 'ENTERPRISE']

Cursor-Based (Keyset) Pagination

Offset pagination (page/size) gets slower on deep pages, because the database still reads every skipped row. It also skips or repeats rows when data changes between requests. Cursor pagination avoids both problems: it seeks past the last row you saw (WHERE (created_at, id) < (...)), so every page costs the same and stays stable while rows are inserted.

It works with every built-in dialect and with mixed ASC/DESC sort orders.

Fetching pages

const { QueryBuilder, buildCursorPage } = require("sql-flex-query");

const BASE = `
  SELECT /*SELECT_COLUMNS*/
  FROM orders o
  /*WHERE_CLAUSE*/
  /*ORDER_BY*/
  /*LIMIT_CLAUSE*/
`;

async function listOrders(cursor) {
  const result = new QueryBuilder("postgres")
    .baseQuery(BASE)
    .columnMapper({ id: "o.id", createdAt: "o.created_at", total: "o.total", status: "o.status" })
    .select(["id", "createdAt", "total"])
    .where({ key: "status", operation: "EQ", value: "PAID" })
    .orderBy([
      { key: "createdAt", direction: "DESC" },
      { key: "id", direction: "DESC" }, // unique tie-breaker
    ])
    .cursorPaginate(20, cursor) // {} | { after: token } | { before: token }
    .build();

  const { rows } = await pool.query(result.searchQuery, result.params);
  return buildCursorPage(rows, result);
}

const page1 = await listOrders({});
// { items: [...20 rows], hasNextPage: true, hasPreviousPage: false,
//   nextCursor: 'eyJjcmVhdGVkQXQiOnsiJGRhdGUiOi...', prevCursor: null }

const page2 = await listOrders({ after: page1.nextCursor });
const backToPage1 = await listOrders({ before: page2.prevCursor });

The second request generates:

SELECT o.id AS "id", o.created_at AS "createdAt", o.total AS "total"
FROM orders o
WHERE o.status = $1 AND (o.created_at < $2 OR (o.created_at = $3 AND o.id < $4))
ORDER BY o.created_at DESC, o.id DESC
LIMIT 21 OFFSET 0
-- params: ['PAID', 2024-06-01T10:00:00.000Z, 2024-06-01T10:00:00.000Z, 3]

With the options-object API, pass cursor and size:

const result = buildQueries({
  baseQueryTemplate: BASE,
  sortBy: [{ key: "id", direction: "ASC" }],
  size: 20,
  cursor: { after: req.query.cursor }, // or {} for the first page
});

How it works

One extra row: the search query fetches size + 1 rows. buildCursorPage() uses the extra row to set hasNextPage (or hasPreviousPage), then drops it.
Backward pages: a before page is read in reverse order. buildCursorPage() puts the rows back in the requested order.
Count query: countQuery returns the total row count and leaves out the cursor condition. Run it with countParams, not params:
```
const total = await pool.query(result.countQuery, result.countParams);
```
Cursor format: cursors are opaque, URL-safe base64 tokens holding the last row's sort-key values. Date values survive the round trip as Date. You can also pass a plain object such as { after: { id: 42 } }, or build tokens yourself with encodeCursor() / decodeCursor().

Rules and caveats

End sortBy with a unique column (usually id). If two rows share the same sort values, rows at a page boundary can be skipped or repeated.
Sort columns must be NOT NULL. SQL can't compare NULL with < or >, so a cursor containing null is rejected.
Select every sort key under its own name. buildCursorPage() reads each key from the rows. selectColumns together with columnMapper aliases them for you (o.created_at AS "createdAt"). A table-qualified key such as u.id is read from the row's id field.
Sort keys must be plain columns, not aggregates. The cursor condition goes in WHERE, so it can't refer to an aggregate like COUNT(...).
Timestamps: JavaScript Date has millisecond precision. If your column stores microseconds (as PostgreSQL timestamp does), the unique tie-breaker is what keeps pages correct.
Clients can read and edit cursors. Tokens are encoded, not encrypted or signed. This is safe because the library binds cursor values as parameters, never splices them into SQL, and accepts only string, number, boolean, bigint and Date values. Even so, don't use secret columns as sort keys.
page is ignored in cursor mode, and size must be a positive integer.

Dialect Helpers for INSERT / UPDATE / DELETE

The dialectHelpers() factory provides dialect-aware utilities for building INSERT, UPDATE, DELETE, and custom queries — without prescribing a specific pattern. You get the building blocks; you compose the final SQL.

10. INSERT Query (Postgres)

const { dialectHelpers } = require("sql-flex-query");

const h = dialectHelpers("postgres");
const columnMapper = { name: "u.name", email: "u.email", role: "u.role" };

const data = { name: "John Doe", email: "john@example.com", role: "ADMIN" };
const { columns, placeholders, params } = h.buildInsertValues(
  data,
  columnMapper,
);

const query = `INSERT INTO users (${columns.join(", ")}) VALUES (${placeholders.join(", ")}) RETURNING *`;
// query:  INSERT INTO users (u.name, u.email, u.role) VALUES ($1, $2, $3) RETURNING *
// params: ['John Doe', 'john@example.com', 'ADMIN']

Same query with MySQL:

const h = dialectHelpers("mysql");
const { columns, placeholders, params } = h.buildInsertValues(
  data,
  columnMapper,
);

const query = `INSERT INTO users (${columns.join(", ")}) VALUES (${placeholders.join(", ")})`;
// query:  INSERT INTO users (u.name, u.email, u.role) VALUES (?, ?, ?)
// params: ['John Doe', 'john@example.com', 'ADMIN']

11. UPDATE Query with WHERE (Postgres & MSSQL)

Use buildWhereClause with existingParams — the same API you use for SELECT WHERE clauses. When you pass the SET params array, placeholder numbering continues automatically.

const { dialectHelpers } = require("sql-flex-query");

const h = dialectHelpers("postgres");
const columnMapper = { name: "u.name", email: "u.email", id: "u.id" };

// 1. Build SET clause
const data = { name: "Jane Doe", email: "jane@example.com" };
const { setClause, params } = h.buildSetClause(data, columnMapper);
// setClause: 'u.name = $1, u.email = $2'
// params:    ['Jane Doe', 'jane@example.com']

// 2. Build WHERE — pass `params` so placeholders continue from $3
const { clause } = h.buildWhereClause(
  [{ key: "id", operation: "EQ", value: 42 }],
  [],
  columnMapper,
  params, // ← same params array
);

const query = `UPDATE users SET ${setClause}${clause} RETURNING *`;
// query:  UPDATE users SET u.name = $1, u.email = $2 WHERE u.id = $3 RETURNING *
// params: ['Jane Doe', 'jane@example.com', 42]

Same query with MSSQL:

const h = dialectHelpers("mssql");
const { setClause, params } = h.buildSetClause(data, columnMapper);

const { clause } = h.buildWhereClause(
  [{ key: "id", operation: "EQ", value: 42 }],
  [],
  columnMapper,
  params,
);

const query = `UPDATE users SET ${setClause}${clause}`;
// query:  UPDATE users SET u.name = @p1, u.email = @p2 WHERE u.id = @p3
// params: ['Jane Doe', 'jane@example.com', 42]

UPDATE with complex WHERE (multiple criteria, IN, etc.):

const h = dialectHelpers("postgres");
const columnMapper = {
  status: "u.status",
  role: "u.role",
  lastLogin: "u.last_login",
};

const { setClause, params } = h.buildSetClause(
  { status: "INACTIVE" },
  columnMapper,
);

// Same buildWhereClause you'd use for SELECT — just pass params to continue numbering
const { clause } = h.buildWhereClause(
  [
    { key: "role", operation: "IN", value: ["GUEST", "TRIAL"] },
    { key: "lastLogin", operation: "LTE", value: "2023-01-01" },
  ],
  [],
  columnMapper,
  params,
);

const query = `UPDATE users SET ${setClause}${clause}`;
// query:  UPDATE users SET u.status = $1 WHERE u.role IN ($2, $3) AND u.last_login <= $4
// params: ['INACTIVE', 'GUEST', 'TRIAL', '2023-01-01']

12. DELETE Query with WHERE (Postgres)

const { dialectHelpers } = require("sql-flex-query");

const h = dialectHelpers("postgres");
const columnMapper = {
  id: "u.id",
  status: "u.status",
  deletedAt: "u.deleted_at",
};

const { clause, params } = h.buildWhereClause(
  [
    { key: "status", operation: "EQ", value: "DEACTIVATED" },
    { key: "deletedAt", operation: "NOT_NULL" },
  ],
  [],
  columnMapper,
);

const query = `DELETE FROM users${clause}`;
// query:  DELETE FROM users WHERE u.status = $1 AND u.deleted_at IS NOT NULL
// params: ['DEACTIVATED']

Low-Level Helpers

You can also import individual utilities for maximum flexibility:

const {
  createDialect,
  getKey,
  prepareWhereClause,
  prepareClause,
} = require("sql-flex-query");

const dialect = createDialect("postgres");
const mapper = { firstName: "u.first_name" };

// Resolve column names
getKey(mapper, "firstName", dialect); // 'u.first_name'
getKey(mapper, "age", dialect); // '"age"' (auto-quoted)

// Generate placeholder
dialect.placeholder(1); // '$1'
dialect.quoteIdentifier("name"); // '"name"'

// Build a single clause
const params = [];
prepareClause(
  { key: "status", operation: "EQ", value: "ACTIVE" },
  params,
  mapper,
  dialect,
);
// returns: '"status" = $1',  params: ['ACTIVE']

Extending with Custom Dialects

You can add support for custom databases by extending the BaseDialect class and registering your dialect with registerDialect().

Creating a Custom Dialect

import { BaseDialect, DialectName } from 'sql-flex-query';

class MyDialect extends BaseDialect {
  name: DialectName = 'mydialect';

  placeholder(position: number): string {
    return `:${position}`; // e.g., :1, :2
  }

  quoteIdentifier(identifier: string): string {
    return `"${identifier}"`; // double quotes
  }

  // Override if your DB has special pagination syntax
  requiresOrderByForPagination: boolean = false;
  mergesPaginationWithOrderBy: boolean = false;
  // Use default LIMIT/OFFSET or override paginationClause()
}

// Register your dialect
import { registerDialect } from 'sql-flex-query';
registerDialect('mydialect', () => new MyDialect());

// Now you can use it
const result = buildQueries({
  baseQueryTemplate: BASE,
  whereParams: [...],
  dialect: 'mydialect',
});

Using Custom Dialect Directly

You can also instantiate dialects directly without registration:

import { MyDialect } from "./my-dialect";
const dialect = new MyDialect();
const helpers = dialectHelpers(dialect); // or use dialect directly

Template Placeholders Reference

Placeholder	Required	Description
`/SELECT_COLUMNS/`	Yes	Replaced with column list or `*`
`/WHERE_CLAUSE/`	Yes	Replaced with `WHERE ...` or empty string
`/ORDER_BY/`	Yes	Replaced with `ORDER BY ...` or empty string
`/LIMIT_CLAUSE/`	Yes	Replaced with pagination clause or empty string
`/HAVING_CLAUSE/`	Optional	Replaced with `HAVING ...` (use with GROUP BY)

Operations Reference

Operation	SQL	Needs Value	Example
`EQ`	`col = ?`	Yes	`{ key: 'status', operation: 'EQ', value: 'ACTIVE' }`
`NEQ`	`col <> ?`	Yes	`{ key: 'role', operation: 'NEQ', value: 'GUEST' }`
`LIKE`	`col LIKE ?`	Yes	`{ key: 'name', operation: 'LIKE', value: '%john%' }`
`NOT_LIKE`	`col NOT LIKE ?`	Yes	`{ key: 'email', operation: 'NOT_LIKE', value: '%spam%' }`
`GT`	`col > ?`	Yes	`{ key: 'age', operation: 'GT', value: 18 }`
`LT`	`col < ?`	Yes	`{ key: 'price', operation: 'LT', value: 100 }`
`GTE`	`col >= ?`	Yes	`{ key: 'score', operation: 'GTE', value: 90 }`
`LTE`	`col <= ?`	Yes	`{ key: 'weight', operation: 'LTE', value: 80 }`
`IN`	`col IN (?, ?)`	Yes (array)	`{ key: 'status', operation: 'IN', value: ['A', 'B'] }`
`NULL`	`col IS NULL`	No	`{ key: 'deleted_at', operation: 'NULL' }`
`NOT_NULL`	`col IS NOT NULL`	No	`{ key: 'verified_at', operation: 'NOT_NULL' }`
`BETWEEN`	`col >= ? AND col < ?`	Yes (2-item array)	`{ key: 'age', operation: 'BETWEEN', value: [18, 65] }`

Note: IN with an empty array generates 1 = 0, a condition that matches no rows. Most databases reject IN () as a syntax error, so this keeps the query valid on every dialect. Inside a text-search (OR) group the empty IN contributes nothing, and the other conditions still match.

Demo Projects

Check out the demo repository for complete working examples:

https://github.com/ashishlohia70/sql-flex-query-demo

Community & Support

⭐ Star this repo: If you find this library useful, please give it a star on GitHub — it helps others discover it!
🐛 Report issues: Found a bug or have a feature request? Open an issue.
💬 Ask questions: Use GitHub Discussions for Q&A.
🔧 Contribute: Pull requests are welcome! See CONTRIBUTING.md for guidelines.
📖 Follow updates: Watch the repository or follow @ashishlohia70 on GitHub.

License

MIT

Name		Name	Last commit message	Last commit date
.github		.github
examples		examples
src		src
tests		tests
.gitignore		.gitignore
.npmignore		.npmignore
CODE_OF_CONDUCT.md		CODE_OF_CONDUCT.md
CONTRIBUTING.md		CONTRIBUTING.md
LICENSE		LICENSE
README.md		README.md
SECURITY.md		SECURITY.md
package-lock.json		package-lock.json
package.json		package.json
tsconfig.json		tsconfig.json
tsup.config.ts		tsup.config.ts
vitest.config.ts		vitest.config.ts

Latest commit

History

Folders and files

Repository files navigation