Executing Parameterized Raw SQL Safely in Knex.js

When You Need Raw SQL in Knex.js

Knex.js's query builder covers most data access patterns, but certain operations—complex window functions, database-specific syntax, or performance-critical hand-tuned queries—require stepping outside the builder. knex.raw() is the supported, version-stable escape hatch for executing arbitrary SQL with parameterized bindings, preventing SQL injection while giving you full control over the statement.

Prerequisites

• Knex.js installed (npm install knex) and a configured client (pg, mysql2, sqlite3, etc.)
• An initialized Knex instance bound to a connection pool
• Familiarity with the target database's SQL dialect
• Read/write permissions on the tables involved

Basic Parameterized Raw Query

The raw method accepts a SQL string with ? positional placeholders and an array of bindings. Bindings are escaped by the underlying driver, not by string concatenation.

// Example: fetch a single user by primary key
const id = 42;
const rows = await knex.raw('SELECT id, email, created_at FROM users WHERE id = ?', [id]);
// rows[0] contains the result array; rows[1] contains metadata (varies by driver)
console.log(rows[0]); // [{ id: 42, email: '[contact removed]', created_at: '2024-01-15...' }]

Run this in any async context where the Knex instance is in scope (route handler, service function, migration, seed). No elevated permissions beyond the database user's grants are required.

Using Raw Queries Inside Transactions

knex.raw() participates in the connection pool and transaction lifecycle. Wrap multiple raw statements in knex.transaction() to ensure atomicity.

await knex.transaction(async (trx) => {
  await trx.raw('UPDATE accounts SET balance = balance - ? WHERE id = ?', [100, 1]);
  await trx.raw('UPDATE accounts SET balance = balance + ? WHERE id = ?', [100, 2]);
  await trx.raw('INSERT INTO transfers (from_id, to_id, amount) VALUES (?, ?, ?)', [1, 2, 100]);
});
// If any statement throws, the transaction rolls back automatically.

Verify transaction behavior by checking the transfers table after a deliberate failure (e.g., violate a foreign key). The rolled-back inserts should not appear.

Returning Data from Write Operations

PostgreSQL and SQLite support RETURNING; MySQL 8.0+ supports RETURNING for INSERT and REPLACE. Use it to avoid a second round-trip.

const inserted = await knex.raw(
  'INSERT INTO events (name, payload) VALUES (?, ?) RETURNING id, created_at',
  ['signup', JSON.stringify({ source: 'web' })]
);
// inserted[0][0] => { id: 101, created_at: '2024-06-01T12:34:56.789Z' }

For MySQL < 8.0, follow the insert with SELECT LAST_INSERT_ID() inside the same transaction.

Handling Driver-Specific Error Types

Errors thrown by knex.raw() originate from the database driver (e.g., pg's DatabaseError, mysql2's MysqlError). They are not wrapped in a Knex-specific hierarchy. Catch and inspect err.code or err.errno for portable handling.

try {
  await knex.raw('INSERT INTO unique_emails (email) VALUES (?)', [email]);
} catch (err) {
  // PostgreSQL: '23505', MySQL: 1062, SQLite: 'SQLITE_CONSTRAINT_UNIQUE'
  const isUniqueViolation = 
    err.code === '23505' || err.errno === 1062 || err.code === 'SQLITE_CONSTRAINT_UNIQUE';
  if (isUniqueViolation) {
    throw new ConflictError('Email already registered');
  }
  throw err;
}

This approach keeps error handling centralized without depending on Knex's error classes.

Limitations and Risks

• No schema introspection: Column names, types, and constraints are not validated at build time. A migration that renames email to email_address will break the raw query silently until runtime.
• No automatic quoting: Identifiers (table/column names) are not quoted. Use the database's native quoting (\"column\" for Postgres, \`column\` for MySQL) or embed identifiers directly if they are static and safe.
• No where-chaining: You cannot append .where() or .orderBy() to a raw query. Compose the full SQL string instead.
• Binding style: Knex 2.x and 3.x only support positional ? placeholders with an array. Named parameters (:name) and object bindings are not natively supported; use a helper if you need them.

Verification Checklist

1. Parameter binding works: Run await knex.raw('SELECT ? AS val', [123]) and assert the result contains 123.
2. Version compatibility: Check require('knex/package.json').version and confirm the raw signature matches the major version's documentation.
3. Transaction integration: Execute the raw query inside knex.transaction(), force a rollback (throw after the raw call), and verify no partial writes persist.
4. Error shape: Provoke a syntax error (SELECT * FORM users) and log the caught error to confirm driver-specific properties (code, errno, sqlState) are present.

Practical Verification Script

Save the following as verify-raw.js and run with node verify-raw.js (requires a running database and valid knexfile.js).

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

async function main() {
  // 1. Binding sanity check
  const bindingTest = await knex.raw('SELECT ? AS num, ? AS txt', [42, 'hello']);
  console.assert(bindingTest[0][0].num === 42, 'numeric binding failed');
  console.assert(bindingTest[0][0].txt === 'hello', 'string binding failed');
  console.log('✓ Parameter binding works');

  // 2. Transaction rollback
  try {
    await knex.transaction(async (trx) => {
      await trx.raw('INSERT INTO test_raw (val) VALUES (?)', ['should-rollback']);
      throw new Error('forced rollback');
    });
  } catch (e) {
    const count = await knex('test_raw').where('val', 'should-rollback').count('* as c');
    console.assert(parseInt(count[0].c, 10) === 0, 'transaction rollback failed');
    console.log('✓ Transaction rollback works');
  }

  // 3. Error inspection
  try {
    await knex.raw('SELECT * FROM nonexistent_table');
  } catch (err) {
    console.log('Error code:', err.code || err.errno || err.sqlState);
    console.log('✓ Driver error surfaced');
  }

  await knex.destroy();
}

main().catch(console.error);

Expected output: three checkmarks and the driver-specific error code. If any assertion fails, review the Knex version and driver compatibility.

When to Choose Raw Over the Query Builder

Scenario	Use Query Builder	Use knex.raw()
Simple CRUD with dynamic filters	Yes	No
Window functions, CTEs, lateral joins	Limited	Yes
Database-specific features (e.g., ON CONFLICT, MERGE)	Partial	Yes
Bulk operations with RETURNING	Yes (Postgres)	Yes (all dialects)
Schema changes (DDL)	Use migrations	Only in migrations

Summary

knex.raw() is a stable, supported method for executing parameterized SQL across Knex 0.13–3.x. It integrates with the connection pool and transactions, but it bypasses the query builder's schema awareness and error normalization. Use it deliberately for queries the builder cannot express, always bind parameters via the second argument, and handle driver-specific error codes explicitly. The verification script above confirms binding, transaction safety, and error visibility for your specific stack.