CodeIgniter 4: When to Use Query Builder vs. Raw SQL

The Dilemma of the Fluent Interface

When building database-driven applications in CodeIgniter 4, you are often caught between two worlds: the elegant, chainable methods of the Query Builder and the absolute control of raw SQL. The goal is usually to write clean code that is secure against SQL injection, but as reporting requirements grow more complex, the "fluent" way of doing things can become a hindrance rather than a help.

The key takeaway is that the Query Builder is your primary tool for CRUD (Create, Read, Update, Delete) and dynamic filtering, while raw SQL is your specialized tool for performance tuning, complex reporting, and database-specific features. Using them interchangeably within the same transaction is supported and often necessary.

Where Query Builder Wins: Dynamic CRUD

The CodeIgniter\Database\BaseBuilder is designed for scenarios where the query structure changes based on user input. Because it uses parameter binding automatically, you don't have to manually escape every variable to prevent SQL injection.

One of the most significant advantages in CI4 is the ability to handle bulk data. Using insertBatch() or updateBatch() is drastically more efficient than looping through a dataset and calling insert(). These methods compile a single multi-row statement (e.g., INSERT INTO table (cols) VALUES (...), (...), (...)), reducing the number of round-trips to the database server.

Where Raw SQL is Mandatory

The Query Builder is a generalist. It targets the common denominator of supported databases, which means it lacks support for advanced SQL features. You should switch to $db->query() when you need:

• Window Functions: Using RANK(), LEAD(), or LAG() for analytical reporting.
• Recursive CTEs: Handling hierarchical data (like organizational charts) using WITH RECURSIVE.
• Row-Level Locking: The builder does not support FOR UPDATE or FOR SHARE clauses, which are critical for preventing race conditions during financial transactions.
• Database Hints: Using MySQL-specific hints like STRAIGHT_JOIN to force a specific join order for performance.

Worked Example: Hybrid Approach for a Financial Ledger

Imagine a scenario where you need to lock a user's balance for an update (Raw SQL) and then log the transaction in a batch (Query Builder). Both can exist within the same transaction block.

// Run this in a Controller or Service method
// Required permissions: Database user must have SELECT, UPDATE, and INSERT privileges
$db = \Config\Database::connect();

$db->transStart();

// 1. Raw SQL for Row-Level Locking
// We use $db->prefixTable() because raw queries ignore the DBPrefix config
$sql = "SELECT balance FROM " . $db->prefixTable('accounts') . " WHERE id = ? FOR UPDATE";
$account = $db->query($sql, [123])->getRow();

if ($account && $account->balance >= 100) {
    // 2. Raw SQL for the update
    $db->query("UPDATE " . $db->prefixTable('accounts') . " SET balance = balance - 100 WHERE id = ?", [123]);

    // 3. Query Builder for batch logging
    $logBuilder = $db->table('transaction_logs');
    $data = [
        ['account_id' => 123, 'amount' => -100, 'type' => 'withdrawal'],
        ['account_id' => 123, 'amount' => 0, 'type' => 'audit_trail']
    ];
    $logBuilder->insertBatch($data);
}

$db->transComplete();

// Verification: Check if transaction succeeded
if ($db->transStatus() === false) {
    // Handle failure
}

Critical Limitations and Risks

Choosing the wrong tool or using them incorrectly can lead to subtle bugs. Keep these three constraints in mind:

• The whereIn Limit: When using $builder->whereIn('id', $array), be cautious with very large arrays (typically > 1,000 elements). Most databases have a limit on the number of bound parameters. If your array is huge, chunk the data or load the IDs into a temporary table and use a join.
• The State Leak: If you reuse a builder instance for multiple queries, you must call $builder->resetQuery(). If you don't, the WHERE and JOIN clauses from the first query will persist into the second, leading to incorrect results.
• The Prefix Gap: The Query Builder automatically adds the DBPrefix defined in your configuration. Raw queries do not. Always wrap your table names in $db->prefixTable('table_name') to ensure your code remains portable across environments.

Diagnostic Tip: Analyzing the Output

If a query is performing poorly, you don't have to guess what the Query Builder is producing. In CI4.4+, you can use getCompiledSelect() to retrieve the SQL string without executing it. This is invaluable for running an EXPLAIN plan in your database management tool:

$builder = $db->table('orders');
$builder->select('customer_id, SUM(total) as revenue')
        ->where('status', 'completed')
        ->groupBy('customer_id');

// This returns the SQL with '?' placeholders
$sql = $builder->getCompiledSelect(); 
log_message('debug', 'Executing SQL: ' . $sql);

Final Decision Matrix

Scenario	Recommended Tool	Reason
Simple CRUD / Dynamic Filters	Query Builder	Security, readability, and speed of development.
Bulk Inserts/Updates	insertBatch()	Reduced network overhead and faster execution.
Complex Analytics/Reports	Raw SQL	Access to Window Functions and CTEs.
Concurrency Control	Raw SQL	Required for FOR UPDATE locking.