Raw Queries

Raw Queries

The query builder covers 95% of cases — but every now and then you need a CTE, EXPLAIN, vendor-specific syntax, a stored procedure call, or a complex UPDATE … FROM …. Two entry points handle that:

API	When to use
$db->query($sql, $params)	Execute a complete prepared statement
$db->raw($fragment) / DB::raw(...)	Embed an unescaped SQL fragment inside the builder

Executing raw statements

query() returns the same DataMapperInterface you'd get from read() — so the fetch API is identical:

$result = $db->query(
    'SELECT id, title FROM posts WHERE user_id = :id ORDER BY created_at DESC LIMIT 10',
    [':id' => 5]
);

foreach ($result->asAssoc()->rows() as $row) {
    echo $row['title'] . PHP_EOL;
}

Parameter binding

Both :name-style and ?-style placeholders work; the leading : on parameter keys is optional:

$db->query('SELECT * FROM posts WHERE id = :id', ['id' => 7]);
$db->query('SELECT * FROM posts WHERE id = :id', [':id' => 7]);
// both bind to the :id placeholder

Values bind with type detection (via the DataMapper):

PHP value	PDO type
bool	PDO::PARAM_BOOL
int	PDO::PARAM_INT
null	PDO::PARAM_NULL
anything else	PDO::PARAM_STR

Floats bind as strings — PDO doesn't have a float type, and string binding round-trips fine across MySQL / PostgreSQL / SQLite.

Non-SELECT statements

query() returns a DataMapper even for INSERT / UPDATE / DELETE / DDL. Ignore the return value or read numRows() for affected rows:

$inserted = $db->query(
    'INSERT INTO audit (event, payload) VALUES (:event, :payload)',
    [':event' => 'login', ':payload' => '{}']
)->numRows();

$db->query('CREATE INDEX idx_users_email ON users (email)');

Prepare options

The third argument is a PDO prepare() options array:

$db->query(
    'SELECT * FROM big_table',
    [],
    [PDO::MYSQL_ATTR_USE_BUFFERED_QUERY => false] // stream large result
);

Inlining SQL inside the builder

DB::raw() (or $db->raw()) wraps a string in a RawQuery object. The builder treats it as opaque — no escaping, no parameter binding. It can appear anywhere a column expression or value is expected.

In SELECT

DB::select(
    'id',
    'name',
    DB::raw("CONCAT(first_name, ' ', last_name) AS full_name"),
    DB::raw('NOW() AS server_time')
)->read('users');

In WHERE

DB::where(DB::raw('score > 50 OR is_admin = 1'))
    ->read('users');
// WHERE score > 50 OR is_admin = 1

In SET (computed columns)

DB::update('counters', [
    'value'      => DB::raw('value + 1'),
    'updated_at' => DB::raw('NOW()'),
], ['id' => 1]);

Generated SQL: UPDATE counters SET value = value + 1, updated_at = NOW() WHERE id = :id

In ORDER BY

DB::orderBy(DB::raw('RAND()'))->limit(5)->read('users');  // MySQL
DB::orderBy(DB::raw('RANDOM()'))->limit(5)->read('users'); // SQLite/PgSQL

In conditions shortcut

DB::read('users', null, [DB::raw('score > 50')]);
// WHERE score > 50

As sub-queries

For SELECT sub-queries, prefer the dedicated subQuery() helper — it wraps the inner builder's parameters into the outer query's bag:

DB::whereIn('id', DB::subQuery(function ($qb) {
    $qb->select('user_id')->from('logins')->where('logged_in_at', '>', '2024-01-01');
}))->read('users');

⚠️ Safety

The fundamental rule:

Never embed unsanitized user input into a RawQuery or a query() SQL string.

RawQuery is unescaped by definition — its whole purpose is to bypass quoting and binding. Any value derived from user input must be parameterized:

// ❌ NEVER — direct SQL injection
$db->where(DB::raw("name = '$userInput'"));

// ✅ ALWAYS — parameterized
$db->where('name', '=', $userInput);

Identifier-style values

If you must accept a column or table name from outside, validate against an allow-list before composing the SQL:

$allowed = ['id', 'created_at', 'score'];
if (!in_array($sortColumn, $allowed, true)) {
    throw new \InvalidArgumentException();
}

$db->orderBy($sortColumn, 'DESC');

The builder safely quotes $sortColumn as a column identifier — but it cannot tell whether "id; DROP TABLE users;" was supposed to be a column. Validation is your job.

Raw queries with bound parameters

The safest hybrid: write raw SQL, bind every value:

$db->query(
    'WITH recent AS (
        SELECT id, score FROM posts WHERE created_at > :since
    )
    SELECT id FROM recent WHERE score > :min',
    [
        ':since' => '2025-01-01',
        ':min'   => 50,
    ]
);

Common raw-SQL recipes

Vendor-specific syntax

// PostgreSQL — UPSERT
$db->query('
    INSERT INTO users (email, name)
    VALUES (:email, :name)
    ON CONFLICT (email) DO UPDATE SET name = EXCLUDED.name
', ['email' => 'a@x.com', 'name' => 'A']);

// MySQL — UPSERT
$db->query('
    INSERT INTO users (email, name)
    VALUES (:email, :name)
    ON DUPLICATE KEY UPDATE name = VALUES(name)
', ['email' => 'a@x.com', 'name' => 'A']);

EXPLAIN

$plan = $db->query('EXPLAIN SELECT * FROM users WHERE id = :id', [':id' => 1])
           ->asAssoc()
           ->rows();
print_r($plan);

Stored procedure

$db->query('CALL recompute_user_scores(:user_id)', [':user_id' => 5]);

EXISTS check

$row = $db->query(
    'SELECT EXISTS(SELECT 1 FROM users WHERE email = :email) AS exists',
    [':email' => 'alice@example.com']
)->asAssoc()->row();

if ($row['exists']) {
    // …
}

See also

• CRUD Operations — the high-level helpers
• Query Builder — the fluent API
• Recipes — patterns built from raw + builder