Raw SQL & SqlExpr

SlintORM provides escape hatches for raw SQL when the query builder is not enough. Use SqlExpr to embed SQL expressions in field values, and whereRaw / namedWhere for raw WHERE clauses.

SqlExpr — embed raw SQL in field values

The SqlExpr class lets you pass arbitrary SQL expressions wherever a field value is expected — inserts, updates, and query builder methods like orderBy.

// SqlExpr.raw(sql, params?) — embed arbitrary SQL in field values

import { SqlExpr } from 'slintorm';

// In inserts — pass raw SQL expressions as values
await User.insert({
  name: SqlExpr.raw("'ExprUser'"),
  createdAt: SqlExpr.raw("datetime('now')"),
});

// In updates — increment counters or use SQL functions
await User.update(
  { id: 42 },
  {
    loginCount: SqlExpr.raw('login_count + 1'),
    lastLoginAt: SqlExpr.raw("datetime('now')"),
  }
);

// SqlExpr.raw is not escaped — see security notes below

raw WHERE with whereRaw()

whereRaw(sql, params?) injects a raw SQL fragment into the WHERE clause. Supports positional (?) and Postgres-style ($1) placeholders.

// whereRaw(sql, params?) — raw SQL in WHERE clauses

const users = await User.query()
  .whereRaw('createdAt > datetime("now", "-7 days")')
  .get();

// With positional params (SQLite / MySQL)
const recent = await User.query()
  .whereRaw('createdAt > ?', ['2024-01-01'])
  .get();

// With numbered params (Postgres — auto-detected by driver)
const flagged = await User.query()
  .whereRaw('email LIKE $1 OR name LIKE $2', ['%@example.com', '%test%'])
  .get();

// Combine with regular builder methods
const result = await User.query()
  .where('active', '=', true)
  .whereRaw('LENGTH(bio) > ?', [100])
  .orderBy('createdAt', 'DESC')
  .get();

Named placeholders with namedWhere()

namedWhere(sql, params) works like whereRaw but uses :name placeholders for readability.

// namedWhere(sql, params) — named placeholders for readability

const users = await User.query()
  .namedWhere(
    'createdAt > :since AND role = :role',
    { since: '2024-06-01', role: 'admin' }
  )
  .get();

// Named params can be mixed with regular where() calls
const result = await User.query()
  .where('active', '=', true)
  .namedWhere('updatedAt > :date', { date: '2024-01-01' })
  .get();

Security — SQL injection

Raw expressions bypass SlintORM's parameterisation. Never interpolate user input directly into SQL strings. Always use the params array or stick to builder methods where values are automatically escaped.

// ⚠️ SQL Injection — you are responsible for escaping

// SAFE: SqlExpr.raw with user-provided values
const input = req.query.sortColumn;  // user-controlled
const users = await User.query()
  .orderBy(SqlExpr.raw(input), 'ASC')
  .get();
// ^^ DANGEROUS — input could be "id; DROP TABLE users;"

// SAFER: validate against an allow-list
const allowed = ['name', 'email', 'createdAt'];
if (!allowed.includes(input)) throw new Error('Invalid sort column');

// SAFE: use parameterised queries instead of raw strings
const email = userInput;
const users = await User.query()
  .where('email', '=', email)   // parameterised — safe
  .get();

// SAFE: SqlExpr.raw with parameterised values
const status = userInput;
const users = await User.query()
  .whereRaw('status = ?', [status])  // param — safe
  .get();

// Rule of thumb: never interpolate user input into raw SQL strings.
// Always pass values through the params array.

Driver compatibility

// Driver compatibility

// SqlExpr.raw — all drivers (SQLite, Postgres, MySQL, MongoDB)
//   MongoDB translates SqlExpr.raw to $expr / $where
//   but raw SQL strings passed to whereRaw will NOT work
//   on the MongoDB driver (use $expr or aggregation instead)

// whereRaw — SQLite, Postgres, MySQL
//   Not supported on MongoDB driver

// namedWhere — SQLite, Postgres, MySQL
//   Named placeholders are rewritten to positional/numbered
//   per driver automatically. Not supported on MongoDB.

PreviousSchema Generation NextStreaming