Native Query From File Builder Bundle Laravel Package

Getting Started

Know what you're actually installing. This is a Symfony bundle that wraps Doctrine's NativeQuery/ResultSetMapping so that raw SQL lives in YAML files instead of being hard-coded in repositories. That framing matters: if you're a Laravel dev, there is no service provider here — the package's real value to you is the pattern (SQL-as-a-file, parameterized, versioned alongside code), not plug-and-play installation.

Minimal steps (Symfony/Doctrine stack):

composer require micayael/native-query-from-file-builder-bundle

// config/bundles.php
return [
    // ...
    Micayael\NativeQueryFromFileBuilderBundle\NativeQueryFromFileBuilderBundle::class => ['all' => true],
];

Then, before writing a single query, read two files in the vendor directory:

1. DependencyInjection/Configuration.php — tells you the exact config keys (query directory/path, default DBAL connection).
2. Resources/config/services.yaml — tells you the service ID to inject and any constructor arguments.

Configure the directory where your .yaml query files live (typically config/queries/ or %kernel.project_dir%/queries), then define one query:

# config/queries/reporting.yaml
active_users_by_plan:
  sql: "SELECT u.id, u.email, p.name AS plan FROM users u JOIN plans p ON p.id = u.plan_id WHERE u.active = :active"
  # parameter defaults / types, depending on what Configuration.php exposes

First use case: a read-only reporting query. Inject the builder service into a controller or a dedicated ReportingService, call getQuery('reporting.yaml', 'active_users_by_plan') (confirm the method signature — the builder returns a NativeQuery or a SQL string depending on the version), set the parameter, and getResult(). Do not start with anything that writes data.

Implementation Patterns

One file per bounded context, not per query. queries/reporting.yaml, queries/export.yaml, queries/admin.yaml. It makes ownership obvious and keeps code review of SQL changes scoped to the right team. Query names inside the file should be prefixed (reporting.active_users_by_plan) so a name collision in a shared file is impossible.

Always bind, never interpolate. The YAML should hold only the SQL skeleton. Anything that comes from the request (date ranges, IDs, filters) goes through setParameter(). If a section of the YAML lets you build SQL dynamically, treat that as a footgun and keep dynamic SQL in PHP where it's testable.

Wrap the builder in a thin repository/adapter. Don't inject the bundle service directly into a dozen controllers. Give yourself:

final class ReportingQueries
{
    public function __construct(private NativeQueryBuilder $builder) {}

    /** @return array<array{id:int,email:string,plan:string}> */
    public function activeUsersByPlan(bool $active = true): array
    {
        $q = $this->builder->getQuery('reporting.yaml', 'active_users_by_plan');
        $q->setParameter('active', $active);

        return $q->getResult(); // map to DTOs here
    }
}

That adapter is your swap point if the package stops being maintained — the rest of the codebase never knows.

Prefer array/scalar results over entity hydration. For reporting and exports, getArrayResult() (or the raw fetchAllAssociative()) is faster and avoids the surprise of a partially hydrated entity escaping the unit of work. Only reach for ResultSetMapping with entity classes when you genuinely need managed entities — and then remember you've bypassed the EM's change tracking.

Use it for the queries Doctrine can't express. Window functions, CTEs, INSERT ... SELECT, ON DUPLICATE KEY UPDATE, queries against legacy views or a second database. For simple SELECTs, DQL/QueryBuilder is still the better default — don't turn this into a general-purpose query layer.

Treat YAML files as code. Version them, review them in PRs, lint them in CI (yaml_parse at minimum), and add a smoke test that loads every file and asserts every query parses and executes against a test schema with EXPLAIN. A typo in a rarely-hit YAML file is a production 500.

Gotchas and Tips

Framework mismatch is the headline risk. Zero stars, zero dependents, MIT-licensed. On a pure Laravel codebase you'd be pulling Symfony DI, Symfony Config, and Doctrine DBAL in to read a YAML file — almost certainly not worth it. Either (a) keep it isolated to a Symfony service in a mixed stack, or (b) reproduce the pattern in ~80 lines: symfony/yaml (or yaml_parse), a config path, a DBAL connection, and a getQuery(string $file, string $key): string resolver with a per-request array cache.

Validate the query key against a whitelist. If the file name or query name can ever be influenced by user input, you've built a generic SQL execution endpoint. Resolve only keys that exist in a pre-scanned manifest, and never let a request choose the file path.

PDO type coercion. Rows come back with driver-specific types — many setups return integers and booleans as strings. If your API responses look wrong, cast in the mapper, not in the SQL. DBAL's ParameterType::* on setParameter fixes binding, not fetching.

Caching. Verify whether the bundle parses YAML on every call. If it doesn't cache, add a Symfony cache warmer that loads all files once at deploy time into container parameters, or wrap the service in a decorator with a Psr\Cache layer. Parsing YAML per request is quiet but measurable overhead.

Transactions are yours to manage. NativeQuery doesn't participate in the EntityManager's unit of work, so no preFlush/postFlush events, no lifecycle callbacks, no automatic rollback on an exception thrown by other code. Wrap write queries in $connection->transactional() explicitly, and know that a write through native SQL won't invalidate entities already loaded in the same request.

Debugging. Log the resolved SQL and bound parameters at debug level in a decorator — the Symfony profiler's Doctrine panel renders native queries poorly and often hides the bound values. When a query fails, dump $query->getSQL() and $query->getParameters() before execute().

Multi-connection setups. Confirm which DBAL connection the builder defaults to; if you have a read replica or a second database, you likely need to pass the connection explicitly or override the service. Getting this wrong silently hits the primary (or the wrong schema).

Extension points to exploit: override the service definition to inject your own logger/clock; decorate the builder for timing metrics; replace the YAML loader if you outgrow flat files (e.g. per-tenant directories). Because the surface area is small, forking is realistic — keep a note of which files you'd need to touch if you do.

Bus factor. Pin the version in composer.json ("require": "micayael/native-query-from-file-builder-bundle": "1.*"), skim the commit history for activity, and make sure the repository adapter described above is genuinely the only place the package's classes appear. That's your escape hatch.