Query Builder limitations with SQLite in-memory integration testing

Direct Answer

The Query Builder does not automatically translate database-specific JSON or spatial syntax when you switch the connection to SQLite in-memory. Core CRUD methods (select(), where(), orderBy()) generate portable SQL, but any call that maps to MySQL JSON_EXTRACT, PostgreSQL jsonb operators, or spatial functions will emit the original dialect's syntax. SQLite will reject or silently ignore that SQL unless the JSON1 extension is loaded and you execute the query through $db->query() with SQLite-compatible functions like json_extract().

How the Query Builder Behaves

• Portable methods: select(), where(), join(), groupBy(), limit() produce standard SQL that runs on SQLite without changes.
• Dialect-specific features: CodeIgniter 4 does not include a JSON abstraction layer in the Query Builder. Calls such as ->select('json_extract(data, "$.key")') or raw expressions using ->where('JSON_EXTRACT(col, "$.path")', 'value') are passed through to the driver. On MySQL/PostgreSQL they work; on SQLite they fail unless JSON1 is present and the function name matches SQLite's json_extract.
• Silent degradation: If the test SQLite build lacks JSON1, a query referencing a JSON column with LIKE or string comparison may return rows but produce incorrect results (e.g., matching partial strings instead of structured paths).

Compatibility Pattern for JSON Queries

Wrap JSON access behind a driver-aware helper so the same repository method works in production and test environments.

// app/Helpers/JsonQueryHelper.php
if (! function_exists('json_extract_expr')) {
    function json_extract_expr(\CodeIgniter\Database\BaseConnection $db, string $column, string $path): string
    {
        $driver = $db->DBDriver;
        if ($driver === 'MySQLi') {
            return "JSON_EXTRACT({$column}, '{$path}')";
        }
        if ($driver === 'Postgre') {
            return "{$column}->>'{$path}'";
        }
        // SQLite with JSON1
        return "json_extract({$column}, '{$path}')";
    }
}

// Usage in a model or repository
$expr = json_extract_expr($this->db, 'data', '$.user.id');
$builder->select($expr . ' as user_id')->where($expr, $userId);

For tests, ensure the SQLite connection loads JSON1. In phpunit.xml or your bootstrap:

<php>
  <env name="DB_DATABASE" value=":memory:" />
  <env name="DB_DBDriver" value="SQLite3" />
</php>

Then in your test base, after $this->db = \Config\Database::connect():

$this->db->query('PRAGMA compile_options;'); // verify JSON1 appears
// or explicitly load if built as extension
// $this->db->query('SELECT load_extension("json1");');

Verification Steps

1. Inspect the generated SQL after a Query Builder call to confirm no JSON_EXTRACT or ->> appears unless you injected it via the helper.
2. Execute a known JSON query directly: $db->query('SELECT json_extract(col, "$.key") FROM test_table'). If it returns a result, JSON1 is active.
3. Check SQLite version and compile options: SELECT sqlite_version(); and look for ENABLE_JSON1 in PRAGMA compile_options;.

One Diagnostic Detail Needed

What is the exact SQLite version and compile-time configuration in your CI/test runner? Run php -r "$db=new SQLite3(':memory:'); print_r($db->querySingle('PRAGMA compile_options;'));"; and share the output. If ENABLE_JSON1 is missing, the helper must fall back to LIKE pattern matching or you must compile SQLite with JSON1 enabled.