Raw SQL in a Laravel Package? Ask the Grammar, Don't Assume MySQL

TL;DR — A package that writes raw SQL inherits every database its host application might run on. mailhistory had backticks and DATE_FORMAT() baked into its report query. On PostgreSQL that's a hard syntax error, and the dashboard 500'd on render. The fix is one line of Laravel you probably already know exists; the interesting part is why the test suite said everything was fine.

---

The failure

The mail-history dashboard died on first paint in production:

SQLSTATE[42601]: Syntax error: 7 ERROR: syntax error at or near "`"

Not a slow query. Not a missing column. A syntax error, from a report that had been passing CI for months.

Here's what GetMailHistoryReport was doing:

foreach ($this->statuses() as $status) {
    $query->addSelect(
        DB::raw("SUM(CASE WHEN status = '{$status}' THEN 1 ELSE 0 END) as `".strtolower($status).'`')
    );
}

Backticks. MySQL's identifier quote, hard-coded into a package that ships to whoever composer-requires it. PostgreSQL uses double quotes and treats a backtick as a syntax error full stop — it doesn't "not match", it refuses to parse.

Second offender, same query:

default => match ($interval) {
    'weekly'  => "DATE_FORMAT(created_at, '%x-W%v')",
    'monthly' => "DATE_FORMAT(created_at, '%Y-%m')",
    default   => "DATE_FORMAT(created_at, '%Y-%m-%d')",
},

Note the default =>. The driver match had an arm for sqlite and then everything else was assumed to be MySQL. That's the shape of the bug, really — the code knew databases differ, and then encoded "differ" as a binary.

The fix: the connection already knows

Laravel's query grammar exists precisely for this. Every connection carries one, and it knows how that vendor quotes an identifier:

/**
 * Quote a column alias for the connection in use.
 *
 * Backticks are MySQL's identifier quote and a syntax error everywhere
 * else — on PostgreSQL they raise SQLSTATE[42601]. The grammar knows what
 * this connection wants, so ask it rather than hard-coding one vendor's.
 */
protected function alias(string $alias): string
{
    return DB::connection()->getQueryGrammar()->wrap($alias);
}

wrap() gives you `sending` on MySQL, "sending" on PostgreSQL and SQLite. One method, zero match arms, and it stays correct if someone runs this on SQL Server tomorrow.

The period expression can't be delegated the same way — there's no framework abstraction for "bucket a timestamp into a reporting period" — so it stays a match, but with the third arm it was always missing:

protected function periodExpression(string $interval): string
{
    return match (DB::connection()->getDriverName()) {
        'sqlite' => match ($interval) {
            'weekly'  => "strftime('%Y-W%W', created_at)",
            'monthly' => "strftime('%Y-%m', created_at)",
            default   => "strftime('%Y-%m-%d', created_at)",
        },
        'pgsql' => match ($interval) {
            'weekly'  => 'to_char(created_at, \'IYYY-"W"IW\')',
            'monthly' => "to_char(created_at, 'YYYY-MM')",
            default   => "to_char(created_at, 'YYYY-MM-DD')",
        },
        default => match ($interval) {
            'weekly'  => "DATE_FORMAT(created_at, '%x-W%v')",
            'monthly' => "DATE_FORMAT(created_at, '%Y-%m')",
            default   => "DATE_FORMAT(created_at, '%Y-%m-%d')",
        },
    };
}

Small detail worth stealing: the weekly bucket uses IYYY and IW, PostgreSQL's ISO year and week, to match what MySQL's %x-W%v produces. Use plain YYYY-WW and a week straddling New Year's Day splits into two labels on one database and one label on the other — the two reports quietly disagree and nobody notices until someone reconciles them by hand.

Also: pulling both into named protected methods isn't just tidiness. It's what makes the next section possible.

Why the suite was green

This is the part I'd actually put in a code review.

The package tests on SQLite. SQLite accepts backticks as an identifier quote — deliberately, for MySQL compatibility. So the backtick bug was literally unobservable in the suite. And the DATE_FORMAT() branch? Never reached, because SQLite has its own arm.

The test suite wasn't weak. It was thorough about the one dialect that could never fail.

The obvious remedy is a CI matrix with real MySQL and PostgreSQL services. That's the right long-term answer and it's not free — service containers, migration runs, minutes per build, for a package whose report logic is a few dozen lines.

So the cheaper move first: test the SQL you generate, not the SQL you execute.

function reportOn(string $driver): GetMailHistoryReport
{
    config()->set("database.connections.portability-{$driver}", [
        'driver'   => $driver,
        'host'     => '127.0.0.1',
        'database' => 'unused',
        'username' => 'unused',
        'password' => '',
        'prefix'   => '',
    ]);

    config()->set('database.default', "portability-{$driver}");
    DB::purge("portability-{$driver}");

    return new class extends GetMailHistoryReport
    {
        public function aliasFor(string $alias): string
        {
            return $this->alias($alias);
        }

        public function periodExpressionFor(string $interval): string
        {
            return $this->periodExpression($interval);
        }
    };
}

database is literally 'unused' because nothing ever connects. Laravel resolves the grammar from the driver name in config, and the grammar is a pure object — it'll happily tell you how PostgreSQL quotes an identifier without a PostgreSQL server existing anywhere on the machine.

The assertions then read like a spec:

it('quotes column aliases the way each connection expects', function () {
    expect(reportOn('mysql')->aliasFor('sending'))->toBe('`sending`')
        ->and(reportOn('pgsql')->aliasFor('sending'))->toBe('"sending"')
        ->and(reportOn('sqlite')->aliasFor('sending'))->toBe('"sending"');
});

it('buckets periods with a function the connection actually has', function () {
    $pgsql = reportOn('pgsql');

    // to_char, not DATE_FORMAT — the latter does not exist on PostgreSQL.
    expect($pgsql->periodExpressionFor('daily'))->toBe("to_char(created_at, 'YYYY-MM-DD')")
        ->and($pgsql->periodExpressionFor('weekly'))->toContain('IYYY')
        ->and($pgsql->periodExpressionFor('daily'))->not->toContain('DATE_FORMAT');

    expect(reportOn('mysql')->periodExpressionFor('daily'))->toContain('DATE_FORMAT');
    expect(reportOn('sqlite')->periodExpressionFor('daily'))->toContain('strftime');
});

Plus the blunt one, which is my favourite of the set because it needs no knowledge of the domain at all:

it('never emits a backtick on postgres', function () {
    $report = reportOn('pgsql');

    foreach (['sending', 'delivered', 'bounced', 'complained'] as $alias) {
        expect($report->aliasFor($alias))->not->toContain('`');
    }
});

Runs in milliseconds. No services. Fails on the pre-fix code.

The trade-off, honestly

This is a structural test, not a behavioural one. It proves the generated SQL has the right shape for each dialect. It does not prove to_char(created_at, 'IYYY-"W"IW') returns what I think it returns on a live PostgreSQL 16.

That's a real gap and I'm not going to pretend otherwise. Structural tests catch the class of bug that just bit me — wrong vendor syntax — at roughly zero cost. Semantic tests catch a different class, and they need a real engine.

The honest position: this is the 80% you can have today, and a driver matrix in CI is still on the list. What it buys immediately is that nobody can reintroduce a backtick without a red build.

Takeaway

Three things, in order of how often I see them go wrong:

1. If your package writes raw SQL, default => is not "MySQL". It's "every database I haven't thought about". Name your arms.
2. DB::connection()->getQueryGrammar()->wrap() exists. Reach for it before you type an identifier quote by hand.
3. Know what your suite cannot see. SQLite-in-memory is fast and convenient, and its MySQL-compatibility quirks will hide vendor bugs from you for months. Write at least one test that reads the grammar rather than the result.

The fix shipped as cleaniquecoders/mailhistory 3.1.3 and the app that hit it moved over the same day. Two lines of real change, most of the day in the test file — which is usually the correct ratio for this kind of bug.