Handling Optional Filters and Pagination in Knex.js

The Problem: The 'Everything' Query

Building a search endpoint often starts with a simple SELECT *. But as requirements grow, you end up with a mess of if statements trying to handle optional filters: some users want to filter by category, others by date range, and everyone needs pagination. If you try to build these strings manually, you risk SQL injection; if you write a separate query for every combination of filters, your codebase becomes unmaintainable.

The takeaway is that Knex.js query builders are mutable objects. You can pass a query instance through a series of conditional checks to build the final SQL statement dynamically before it ever hits the database.

Conditional Query Building

Because Knex uses a chainable API, you don't have to call every method in a single line. You can initialize a query and then conditionally append .where() clauses based on the presence of request parameters.

To keep your controllers from becoming bloated, use the .modify() method. This allows you to encapsulate specific filtering logic into reusable functions, separating the "how" of the filter from the "when" of the request.

Implementation Example

In this example, we assume a Node.js environment using Knex v3.x. We are building a product search that handles optional category and minPrice filters, along with standard limit/offset pagination.

const knex = require('knex')(require('./knexfile'));

// A reusable modifier for price filtering
const applyPriceFilter = (queryBuilder, minPrice) => {
  if (minPrice) {
    queryBuilder.where('price', '>=', minPrice);
  }
};

async function searchProducts(filters) {
  const { category, minPrice, page = 1, limit = 10 } = filters;
  const offset = (page - 1) * limit;

  const query = knex('products')
    .select('id', 'name', 'price', 'category')
    // Conditional filter for category
    .modify((qb) => {
      if (category) qb.where('category', category);
    })
    // Using the external modifier for price
    .modify(applyPriceFilter, minPrice)
    // Pagination
    .limit(limit)
    .offset(offset);

  return query;
}

Running and Verifying the Query

To verify the generated SQL without executing it against a live database, use the .toSQL().toNative() method. This is critical for debugging complex dynamic chains.

// Run this in your local environment or a test script
const filters = { category: 'Electronics', minPrice: 100, page: 2, limit: 5 };
const query = await searchProducts(filters);
const { sql, bindings } = query.toSQL().toNative();

console.log('SQL:', sql);
console.log('Bindings:', bindings);

Expected Check: The output should show WHERE "category" = ? AND "price" >= ? LIMIT ? OFFSET ? with the corresponding values in the bindings array. This confirms that Knex is using parameterized queries, which prevents SQL injection.

Performance Trade-offs: Offset vs. Cursor

The .limit() and .offset() approach is the standard for most applications, but it has a significant limitation: performance degradation. As the offset value increases (e.g., page 10,000), the database must still scan through all previous rows before discarding them to reach the starting point.

If your dataset exceeds hundreds of thousands of rows, consider switching to cursor-based pagination (filtering by the last seen ID) instead of .offset().

Closing Checklist

• Avoid knex.raw for dynamic filters to prevent injection.
• Use .modify() to keep your query logic modular.
• Verify with .toSQL() during development to ensure the generated SQL matches your intent.
• Monitor offset performance as your table grows.