Dynamic SQL WHERE Clauses with Knex.js: Safe Filtering Without String Concatenation

The Problem: String Concatenation Is a Trap

You're building a REST API endpoint that returns a list of records. Users can filter by status, category, and a date range. Each filter is optional. The naive approach is to build a SQL string with if statements and concatenation:

let sql = 'SELECT * FROM items WHERE 1=1';
if (status) sql += ` AND status = '${status}'`;
if (category) sql += ` AND category = '${category}'`;

This works until a user passes status = "'; DROP TABLE items; --". You've just handed them your database. Even without malicious input, string building is brittle: quoting rules differ across databases, and the logic gets tangled as filters multiply.

Knex.js solves this with a query builder that lets you add .where() clauses conditionally while keeping all values parameterized. You get safe, portable SQL without giving up flexibility.

Knex's Conditional Chaining: The Safe Way

The core idea is simple: you call .where() only when a filter is present. Because Knex builds a query object, you can chain methods inside if blocks:

const query = db('items');
if (status) query.where('status', status);
if (category) query.where('category', category);

Knex automatically turns status and category into bound parameters (? in SQLite/PostgreSQL, ? or $1 depending on dialect). No string interpolation, no injection risk.

There's an even more compact pattern: pass an object to .where() with undefined values for filters you don't want. Knex ignores keys whose value is exactly undefined:

db('items').where({
  status: status,        // ignored if undefined
  category: category,    // ignored if undefined
});

Careful: null is not ignored—it becomes IS NULL. Use undefined for “no filter”.

Worked Example: A Filterable Endpoint

Let's build a real endpoint. Assume Express, Knex, and a SQLite database. We'll accept status, category, and startDate/endDate as optional query parameters.

app.get('/api/items', async (req, res) => {
  const { status, category, startDate, endDate } = req.query;

  const query = db('items')
    .select('*')
    .where({
      status,      // undefined if not provided
      category,    // undefined if not provided
    });

  if (startDate) query.where('created_at', '>=', startDate);
  if (endDate) query.where('created_at', '<=', endDate);

  const items = await query;
  res.json(items);
});

This handles the common case. But what if you need an OR condition, like “status is either active or pending”? Use a sub-builder to group conditions explicitly:

if (statuses && statuses.length) {
  query.where(builder => {
    statuses.forEach(s => builder.orWhere('status', s));
  });
}

The sub-builder groups the ORs inside parentheses, so they don't interfere with the AND conditions outside.

Trade-Offs and Limitations

The conditional chaining pattern is readable for a handful of filters, but it can get messy when you have many optional parameters. The object shorthand hides the logic, and mixing it with explicit .where() calls can confuse readers. For complex cases, extract condition-building into helper functions or use .modify() to apply reusable query fragments:

function filterByDateRange(query, start, end) {
  if (start) query.where('created_at', '>=', start);
  if (end) query.where('created_at', '<=', end);
}

db('items').modify(filterByDateRange, startDate, endDate);

Operator precedence is another trap. Mixing .orWhere() with .where() without grouping can produce unintended logic. Always wrap OR conditions in a sub-builder, as shown above.

Testing dynamic queries is harder than testing static ones. You should snapshot-test the generated SQL for critical paths. Knex exposes .toSQL() so you can inspect the exact query:

const sql = db('items').where({ status: undefined, category: 'tools' }).toSQL();
console.log(sql.sql);  // SELECT * FROM `items` WHERE `category` = ?

Run this in a small Node script to confirm that undefined values are omitted and that all values appear as placeholders.

Actionable Closing

Start with conditional chaining for your next filter endpoint. Use undefined in the object form for simple equality checks, and reserve sub-builders for OR groups. Verify your generated SQL with .toSQL() and test with a known injection string like '1 OR 1=1'—the parameterized query should return no unexpected rows. This approach keeps your code safe, portable across PostgreSQL, MySQL, and SQLite, and far easier to maintain than string concatenation.