How to add multi-tenancy to Symfony with Doctrine SQL Filters
Table of Contents
Introduction
While working on a B2B SaaS, you may run into the need to isolate data per organization - so one tenant never sees another's rows. That's multi-tenancy.
There are several ways to do it: separate databases, separate schemas (yes, I've seen that one with my own eyes), separate servers, or query-level filtering. This post is about the last one.
Say you have an Organization that owns its documents, files, departments, and so on. The usual instinct is to scope every query by hand - one WHERE organization_id = ... per repository method:
public function findRecent(Organization $org): array
{
return $this->createQueryBuilder('d')
->andWhere('d.organization = :org')->setParameter('org', $org)
->orderBy('d.createdAt', 'DESC')
->getQuery()->getResult();
}
It works. Until one day:
- A developer adds a new query method and forgets the
andWhere. - Someone refactors an existing one and drops it.
Either way, you leak data across tenants - silently.
The idea below is simpler: add that andWhere('d.organization = :org') to every context-sensitive query automatically, so you can't forget it. And if not every entity has an organization_id column - don't worry, I'll show you the trick.
Preparation
Full working example on GitHub
I'll use Symfony + Doctrine (no surprises here). We need to write three components: an attribute, a Doctrine filter, and a listener.
First, the attribute class. We use it to mark entities as context-aware:
<?php
namespace Domain\Shared\Attribute;
#[\Attribute(\Attribute::TARGET_CLASS)]
final class ContextAware
{
public function __construct(
// field name to use in the join
public readonly string $fieldName, // <- the trick field
) {
// Marked as context-aware but with no field to scope on = misconfiguration.
// Fail loud and early instead of silently leaking every row later.
if ('' === trim($this->fieldName)) {
throw new \InvalidArgumentException('ContextAware requires a non-empty fieldName.');
}
}
}
Next, the Doctrine SQL filter:
<?php
namespace App\Adapter\Doctrine\Filter;
use App\Entity\Employee;
use Domain\Shared\Attribute\ContextAware;
use Doctrine\ORM\Mapping\ClassMetadata;
use Doctrine\ORM\Query\Filter\SQLFilter;
class AccessContextFilter extends SQLFilter
{
public function addFilterConstraint(ClassMetadata $targetEntity, $targetTableAlias): string
{
$className = $targetEntity->getName();
/**
* @var ContextAware|null $contextAware
*
* If the entity is not marked with the attribute - skip it.
*/
if (!($contextAware = $this->getAttributeFromClass($className, ContextAware::class))) {
return '';
}
$orgId = $this->getParameter('org_id'); // organization id
// fieldName is guaranteed non-empty by the attribute's constructor.
return $this->getQuery($className, $targetTableAlias, $contextAware->fieldName, $orgId);
}
/**
* @param string $className Doctrine entity class name
* @param string $targetTableAlias Alias of the entity table, e.g. d0, t1, or other auto-generated aliases
* @param string $fieldName Field that holds knowledge about the related organization/context
* @param string $orgId Organization id / context id
*/
private function getQuery(
string $className,
string $targetTableAlias,
string $fieldName,
string $orgId
): string {
return match ($className) {
Employee::class, // contains team_id instead of organization_id
=> sprintf('
EXISTS (
SELECT 1
FROM public.team t
WHERE t.id = %s.%s
AND t.organization_id = %s
)', $targetTableAlias, $fieldName, $orgId),
// Any other #[ContextAware] entity (e.g. Document): the marked field maps
// straight to the org/context id -> simple equality.
default => sprintf('%s.%s = %s', $targetTableAlias, $fieldName, $orgId),
};
}
private function getAttributeFromClass(string $className, string $attributeClassName): mixed
{
$reflectionClass = new \ReflectionClass($className);
$attributes = $reflectionClass->getAttributes($attributeClassName);
if (empty($attributes)) {
return null;
}
$firstAttribute = reset($attributes);
return $firstAttribute->newInstance();
}
}
Connect the filter to the Doctrine config
doctrine:
orm:
filters:
context_filter:
class: App\Adapter\Doctrine\Filter\AccessContextFilter
enabled: false
Write the request listener
<?php
namespace App\EventListener;
use App\Entity\User;
use Doctrine\ORM\EntityManagerInterface;
use Symfony\Bundle\SecurityBundle\Security;
use Symfony\Component\EventDispatcher\Attribute\AsEventListener;
use Symfony\Component\HttpFoundation\Request;
use Symfony\Component\HttpKernel\Event\RequestEvent;
final class RequestListener
{
public function __construct(
private readonly EntityManagerInterface $em,
private readonly Security $security,
) {
}
#[AsEventListener]
public function onKernelRequest(RequestEvent $event): void
{
$this->init($event->getRequest());
}
private function init(Request $request): void
{
if ($this->isPrivateArea($request)) {
$this->initializeContext();
}
}
/**
* Note: it's better to move this into a Resolver class,
* but for simplicity I keep it here.
*
* @return void
*/
private function initializeContext(): void
{
$user = $this->security->getUser();
if (!$user instanceof User) { // use App\Entity\User;
return;
}
$filter = $this->em->getFilters()->enable('context_filter');
$filter->setParameter('org_id', $user->getOrganization()->getId());
}
private function isPrivateArea(Request $request): bool
{
$route = $request->attributes->get('_route');
if (is_null($route)) {
return false;
}
return str_starts_with((string) $route, 'private_area_'); // Just an example. Use your own.
}
}
Connect the attribute to your entities
Document:
<?php
namespace App\Entity;
use App\Repository\DocumentRepository;
use Doctrine\ORM\Mapping as ORM;
use Domain\Shared\Attribute\ContextAware;
#[ContextAware(fieldName: 'organization_id')]
#[ORM\Entity(repositoryClass: DocumentRepository::class)]
class Document
{
#[ORM\Id]
#[ORM\GeneratedValue]
#[ORM\Column]
private ?int $id = null;
#[ORM\ManyToOne(inversedBy: 'documents')]
#[ORM\JoinColumn(nullable: false)]
private ?Organization $organization = null;
public function getId(): ?int
{
return $this->id;
}
public function getOrganization(): ?Organization
{
return $this->organization;
}
public function setOrganization(?Organization $organization): static
{
$this->organization = $organization;
return $this;
}
}
Employee:
<?php
namespace App\Entity;
use App\Repository\EmployeeRepository;
use Doctrine\ORM\Mapping as ORM;
use Domain\Shared\Attribute\ContextAware;
#[ContextAware(fieldName: 'team_id')]
#[ORM\Entity(repositoryClass: EmployeeRepository::class)]
class Employee
{
#[ORM\Id]
#[ORM\GeneratedValue]
#[ORM\Column]
private ?int $id = null;
#[ORM\ManyToOne(inversedBy: 'employees')]
private ?Team $team = null;
public function getId(): ?int
{
return $this->id;
}
public function getTeam(): ?Team
{
return $this->team;
}
public function setTeam(?Team $team): static
{
$this->team = $team;
return $this;
}
}
That's all.
How does it work?
The three pieces wire up like this: the #[ContextAware] attribute marks which entities should be scoped and on which column; the filter reads that attribute via reflection and returns the matching SQL fragment; the listener turns the filter on for each in-scope request and feeds it the current org_id. From then on Doctrine appends that fragment to every query it builds for a marked entity - the calling code stays untouched.
When your code calls $documents = $documentRepository->findAll();, the query internally becomes:
SELECT t0.id AS id_1, t0.organization_id AS organization_id_2 FROM document t0 WHERE ((t0.organization_id = 42))
where 42 is $this->security->getUser()->getOrganization()->getId().
The WHERE (t0_.organization_id = 42) part is what Doctrine adds automatically.
Another example, with the Employee entity:
$employees = $employeeRepository->findAll();
Final query:
SELECT t0.id AS id_1, t0.team_id AS team_id_2 FROM employee t0 WHERE ((
EXISTS (
SELECT 1
FROM public.team t
WHERE t.id = t0.team_id
AND t.organization_id = 42
)))
Possible issues
A few traps. The first one is nasty because it fails silently.
The filter is skipped for fetch: EAGER
Issue:
You filter Document, everything looks fine, but in one place it still returns other organizations' rows.
Reason:
If the filtered entity is loaded through an association marked fetch: EAGER, Doctrine loads it eagerly and does not apply SQL filters to that load. So this leaks:
// BAD - eager association bypasses the SQL filter
#[ORM\OneToMany(mappedBy: 'organization', targetEntity: Document::class, fetch: 'EAGER')]
private Collection $documents;
Fix: keep the association lazy (lazy is the default - just drop fetch: EAGER). Load it through a normal query / join instead, and the filter applies again. (This exact behavior has shifted between Doctrine versions, so verify it on yours.)
Lazy to-one + filter = EntityNotFoundException
The flip side of scoping. If a lazy ManyToOne points to a row the filter hides - say a Document whose Organization belongs to another tenant - Doctrine runs the filtered query, gets zero rows for the proxy, and throws EntityNotFoundException instead of returning null.
Usually it means you're touching data that isn't yours in the first place, but it surfaces as a confusing 500 rather than an access error. Keep it in mind when a perfectly valid-looking association suddenly blows up.
Related entities need a JOIN
The filter only constrains entities marked with #[ContextAware]. A query on an unmarked entity like Comment is not scoped unless a marked entity (e.g. Document) is part of it. Join it and the filter kicks in:
// Comments are scoped because the query touches the Document table
$qb->from(Comment::class, 'c')
->join('c.document', 'd'); // <- filter appends d.organization_id = :org_id here
Query Comment without joining Document and you'll get every organization's comments.
Raw SQL and DBAL bypass the filter
The filter only kicks in when the ORM generates the SQL - DQL, repository methods, the query builder, lazy loading. Anything that talks to the database directly skips it entirely:
// NOT scoped - both return every tenant's rows
$this->connection->executeQuery('SELECT * FROM document');
$em->createNativeQuery('SELECT * FROM document', $rsm)->getResult();
No error, no warning - just a silent leak, which makes this one of the easiest to miss. If you drop down to raw SQL or DBAL, the tenant scoping is on you: add the organization_id condition by hand.
The filter is OFF outside HTTP
It's only enabled in the listener, on kernel.request, and even then only for routes matching private_area_*. CLI commands, cron and Messenger handlers - plus any HTTP route outside that prefix - run with it disabled and see all rows. That's usually fine for background jobs, but it means a repository method that relies on the filter is only safe on those private routes. Don't reuse it elsewhere without adding an explicit scope.
Conclusion
One attribute, one filter, one config line, one listener - and reads are scoped to the current organization automatically. Just keep in mind the filter only guards reads: writes, validation and uniqueness checks still need their own tenant scoping, and outside the request (CLI, Messenger) it's off entirely. That's all for today - we added row-level multi-tenancy to Symfony with Doctrine SQL Filters.