README

A rule-based SQL linter, EXPLAIN-aware analyzer, and query log collector for PHP.

What this is

A static SQL linter — a pluggable set of rules that catch known anti-patterns (SELECT *, unconditional UPDATE/DELETE, leading-wildcard LIKE, unbounded IN (...) lists, and more) without touching a database.
An EXPLAIN-aware analyzer — runs EXPLAIN against MySQL or Postgres and normalizes the output so rules can reason about real access paths (full scans, filesort, temp tables, estimated row counts) instead of guessing.
A query log collector — a framework-agnostic recorder you can wire into any PDO-based app to capture queries per request and detect N+1 patterns by grouping structurally identical queries.
A page/request debugger — a drop-in TrackedPdo that auto-records every query, plus a PageDebugger that runs the linter and N+1 detection across an entire request and renders a self-contained HTML debug panel (or exposes the raw data if you want to build your own view).
Optional Laravel auto-discovery — if Laravel is present, a service provider wires itself up automatically (query recording + the debug panel); everything else works identically in plain PHP with zero Laravel dependency.

What this is not

This is not a query optimizer. It does not have a cost model, does not know your table statistics or index cardinality, and does not rewrite your schema. Real query planners (MySQL's, Postgres's) already do that, with far more information than a static tool ever will. What this package does is surface known anti-patterns and real EXPLAIN output so a human can make an informed decision. Every suggested fix is a suggestion — nothing here mutates your schema or your queries automatically.

Install

composer require tokkerbaz/phpsql-lint --dev

Quick start — static linting

use PhpSqlLint\Analyzer;
use PhpSqlLint\Query;
use PhpSqlLint\Report\CliReporter;

$analyzer = new Analyzer(); // uses the built-in rule set by default

$findings = $analyzer->analyze(
    new Query("SELECT * FROM users WHERE email LIKE '%gmail.com'")
);

echo (new CliReporter())->render($findings);

[WARNING] no-select-star — SELECT * fetches every column, including ones you may not use...
    fix: Replace SELECT * with the explicit list of columns you actually consume.
    query: SELECT * FROM users WHERE email LIKE '%gmail.com'

[WARNING] no-leading-wildcard-like — A leading wildcard (LIKE '%term') cannot use a standard B-tree index...
    fix: If this pattern runs frequently, consider a full-text index, a trigram index...
    query: SELECT * FROM users WHERE email LIKE '%gmail.com'

EXPLAIN-aware analysis

use PhpSqlLint\Explain\ExplainRunner;
use PhpSqlLint\Query;

$runner = new ExplainRunner($pdo); // any PDO connection, mysql or pgsql

$result = $runner->run(new Query('SELECT * FROM orders WHERE customer_id = ?', [42]));

if ($result->isFullScan()) {
    echo "Full scan estimated at {$result->estimatedRows} rows — consider an index.\n";
}

N+1 detection from a live request

use PhpSqlLint\Collector\QueryLogCollector;
use PhpSqlLint\Query;

$collector = new QueryLogCollector();

// wire this into your PDO wrapper / ORM query event
$collector->record(new Query($sql, $bindings, durationMs: $elapsed));

// at the end of the request:
foreach ($collector->detectNPlusOne(threshold: 5) as $shape => $queries) {
    echo "Possible N+1: '{$shape}' ran " . count($queries) . " times\n";
}

Laravel integration (optional, zero-config)

If Laravel is present, package auto-discovery registers PhpSqlLintServiceProvider automatically — you don't need to touch config/app.php. The Laravel-specific code lives entirely under PhpSqlLint\Laravel\* and is never loaded for plain PHP projects; the core package (Analyzer, Rules, Collector, Debugger) has no dependency on Illuminate at all.

composer require tokkerbaz/phpsql-lint --dev
php artisan vendor:publish --tag=phpsql-lint-config   # optional

Then in .env:

PHPSQL_LINT_ENABLED=true

That's it. With enabled=true and APP_ENV in the configured environments list (local by default — see config/phpsql-lint.php), the provider:

Hooks DB::listen() to record every query Laravel runs (this is the idiomatic Laravel integration point — TrackedPdo below is for plain PHP, since Laravel manages its own PDO connections internally).
Pushes RenderDebugPanel middleware onto the web group, which appends the floating debug panel to any HTML response.

Set render_panel to false in the published config if you'd rather query PageDebugger yourself (e.g. to log findings to your APM instead of rendering the HTML panel):

use PhpSqlLint\Collector\QueryLogCollector;
use PhpSqlLint\Debugger\PageDebugger;

$debugger = new PageDebugger(app(QueryLogCollector::class));
Log::info('SQL findings', ['findings' => $debugger->findings()]);

Note on test coverage: the Laravel provider and middleware are exercised manually but don't yet have an automated test suite (that would mean pulling in Orchestra Testbench) — worth flagging honestly rather than implying full coverage. The framework-agnostic core (rules, analyzer, collector, debugger) does have full unit test coverage; see tests/.

Always gate this to non-production environments — the panel and the DB::listen hook expose raw SQL and bindings, which you don't want on production traffic regardless of the enabled flag's default.

Debugging a whole page/request (plain PHP)

TrackedPdo is a drop-in PDO subclass that records every query automatically — no manual ->record() calls. PageDebugger then runs the static rule set and N+1 detection against everything that ran, and can render a floating HTML debug panel, similar in spirit to Symfony's profiler toolbar or Laravel Debugbar, but scoped to what this package checks (findings, N+1, timings).

use PhpSqlLint\Debugger\TrackedPdo;
use PhpSqlLint\Debugger\PageDebugger;

// swap your normal `new PDO(...)` for this — everything else about using
// the connection (prepare, execute, fetch) stays exactly the same
$pdo = new TrackedPdo($dsn, $user, $pass, $options);

// ... your app runs, executing queries against $pdo as normal ...

$debugger = new PageDebugger($pdo->collector());

// in dev/staging only — never enable against production traffic,
// since the panel includes raw SQL and bindings
if ($_ENV['APP_ENV'] !== 'production') {
    echo $debugger->renderHtml(); // echo just before </body>
}

The panel shows, per page load: total query count and time, every static-rule finding, any N+1 groups it caught, and the full query list with per-query timing and call site. It's entirely self-contained (inline CSS/JS, no CDN dependency) so it works even if the page's own asset pipeline is broken.

PageDebugger also exposes the underlying data if you'd rather build your own UI or feed it into logging/APM instead of the HTML panel:

$debugger->summary();          // ['query_count' => ..., 'total_duration_ms' => ..., ...]
$debugger->findings();         // Finding[]
$debugger->nPlusOneGroups();   // array<string, Query[]>
$debugger->slowQueries(100.0); // Query[] slower than 100ms, slowest first

Built-in rules

Rule id	Severity	Detects
`no-select-star`	warning	`SELECT *` usage
`no-unconditional-update-delete`	error	`UPDATE`/`DELETE` with no `WHERE`
`no-leading-wildcard-like`	warning	`LIKE '%term'` (index-defeating)
`no-unbounded-in-list`	warning	`IN (...)` with 100+ literal values

Writing a custom rule

Every rule implements a small interface, so adding your own is straightforward:

use PhpSqlLint\Rules\AbstractRule;
use PhpSqlLint\Query;
use PhpSqlLint\Finding;

final class NoOrderByRandRule extends AbstractRule
{
    public function id(): string { return 'no-order-by-rand'; }

    public function check(Query $query): array
    {
        if (!preg_match('/order by rand\(\)/i', $query->raw())) {
            return [];
        }

        return [Finding::for(
            rule: $this,
            query: $query,
            message: 'ORDER BY RAND() forces a full scan and a temp sort on most engines.',
            suggestedFix: 'Use a pre-computed random key column, or sample IDs separately.',
        )];
    }
}

Development

composer install
composer test    # PHPUnit
composer stan     # PHPStan level 8
composer cs-fix    # php-cs-fixer

License

MIT — see LICENSE.

tokkerbaz / phpsql-lint

Maintainers

Package info

Statistics

Security