Database

AppKit integrates Doctrine ORM for entity mapping and persistence, a fluent QueryBuilder for raw DBAL queries, and a DoctrineOrmPagination helper for paginated results.

Configuring Doctrine

config/doctrine.php returns a closure that receives an OrmConfigurator instance.

// config/doctrine.php
use Modufolio\Appkit\Doctrine\OrmConfigurator;

return function (OrmConfigurator $orm) use ($projectDir): void {
    $orm->connection([
        'driver' => 'pdo_sqlite',
        'path'   => $projectDir . '/database/data.db',
    ])->entities(
        $projectDir . '/src/Entity'
    );
};

entities() is variadic — pass multiple paths to scan entity classes from more than one directory. Paths are additive; calling entities() again appends rather than replaces.

$orm->connection([...])->entities(
    $projectDir . '/src/Entity',
    $projectDir . '/packages/billing/src/Entity',
    $projectDir . '/packages/cms/src/Entity',
);

Switching to MySQL or PostgreSQL

$orm->connection([
    'driver'   => 'pdo_mysql',
    'host'     => getenv('DB_HOST'),
    'dbname'   => getenv('DB_NAME'),
    'user'     => getenv('DB_USER'),
    'password' => getenv('DB_PASSWORD'),
    'charset'  => 'utf8mb4',
])->entities($projectDir . '/src/Entity');

$orm->connection([
    'driver'   => 'pdo_pgsql',
    'host'     => getenv('DB_HOST'),
    'dbname'   => getenv('DB_NAME'),
    'user'     => getenv('DB_USER'),
    'password' => getenv('DB_PASSWORD'),
])->entities($projectDir . '/src/Entity');

`OrmConfigurator` methods

Method	Description
`connection(array $params)`	Set DBAL connection parameters
`entities(string ...$paths)`	Scan one or more directories for entity classes (variadic — pass as many paths as you need)
`addFilter(string $name, string $class)`	Register a Doctrine filter (e.g. soft delete)
`middlewares(array $middlewares)`	Set the DBAL middleware stack — replaces any earlier list, so pass the full stack
`addSubscriber(EventSubscriber $subscriber)`	Register a Doctrine event subscriber
`cache(?CacheItemPoolInterface $metadata, ?CacheItemPoolInterface $query, ?CacheItemPoolInterface $result)`	Configure caches for metadata, queries, and results

Custom Doctrine types

OrmConfigurator has no API for custom column types, and AppKit never registers any itself. Register them by hand inside the closure, before configuring the connection:

// config/doctrine.php
use Doctrine\DBAL\Types\Type;
use Ramsey\Uuid\Doctrine\UuidType;

return function (OrmConfigurator $orm): void {
    if (!Type::hasType(UuidType::NAME)) {
        Type::addType(UuidType::NAME, UuidType::class);
    }

    $orm->connection([...])->entities(...);
};

The hasType() guard matters: the closure runs once per environment, but in a long-running worker or a test suite that boots the app repeatedly, a bare addType() throws on the second call.

Per-environment config replaces the base file

config/<env>/doctrine.php does not extend config/doctrine.php — the kernel requires exactly one file:

$closure = require $this->fileMap['doctrine'];
$closure($configurator);

There is no merge and no fallback. Anything the base config does — custom types, filters, middleware — must be repeated in each environment file that replaces it.

This is the most common way to break a test suite: entities declare #[ORM\Column(type: 'uuid')], the base config registers UuidType, the test config does not, and every test dies in setUp() with Unknown column type "uuid" requested — an error that points at Doctrine rather than at the config file that caused it.

The way out is to make the environment file delegate to the base file and override only what differs:

// config/test/doctrine.php
use Modufolio\Appkit\Doctrine\OrmConfigurator;

return function (OrmConfigurator $orm) use ($projectDir): void {
    // Run the base config first — types, filters, middleware, entity paths.
    (require dirname(__DIR__) . '/doctrine.php')($orm);

    // Then override only the connection.
    $orm->connection([
        'driver' => 'pdo_sqlite',
        'memory' => true,
    ]);
};

This works because connection() replaces its parameters wholesale, custom types are guarded by hasType(), and addFilter() overwrites by name. middlewares() replaces too — calling it in the environment file drops the base file's list, so pass the complete stack. Only entities() appends — so do not call it again in the environment file unless you are adding an extra path.

Defining an entity

Entity classes live in src/Entity/. Use Doctrine PHP attributes for mapping.

// src/Entity/Post.php
namespace App\Entity;

use App\Repository\PostRepository;
use Doctrine\ORM\Mapping as ORM;
use Symfony\Component\Validator\Constraints as Assert;

#[ORM\Entity(repositoryClass: PostRepository::class)]
#[ORM\Table(name: 'posts')]
class Post
{
    #[ORM\Id]
    #[ORM\GeneratedValue]
    #[ORM\Column(type: 'integer')]
    private ?int $id = null;

    #[ORM\Column(type: 'string', length: 255)]
    #[Assert\NotBlank]
    #[Assert\Length(max: 255)]
    private string $title = '';

    #[ORM\Column(type: 'text')]
    private string $body = '';

    public function getId(): ?int { return $this->id; }
    public function getTitle(): string { return $this->title; }
    public function setTitle(string $title): void { $this->title = $title; }
    // ...
}

Defining a repository

Repositories extend Doctrine\ORM\EntityRepository and are registered in config/repositories.php.

// src/Repository/PostRepository.php
namespace App\Repository;

use App\Entity\Post;
use Doctrine\ORM\EntityRepository;

/**
 * @extends EntityRepository<Post>
 */
class PostRepository extends EntityRepository
{
    public function findPublished(): array
    {
        return $this->createQueryBuilder('p')
            ->where('p.published = true')
            ->orderBy('p.createdAt', 'DESC')
            ->getQuery()
            ->getResult();
    }
}

// config/repositories.php
return [
    PostRepository::class => Post::class,
];

Accessing the entity manager

Inject EntityManagerInterface via config/controllers.php:

// config/controllers.php
use Doctrine\ORM\EntityManagerInterface;

return [
    PostController::class => [
        EntityManagerInterface::class,
    ],
];

In the controller:

class PostController extends AbstractController
{
    public function __construct(
        private readonly EntityManagerInterface $entityManager,
    ) {}

    public function create(#[MapRequestPayload] CreatePostDto $dto): ResponseInterface
    {
        $post = new Post();
        $post->setTitle($dto->title);
        $post->setBody($dto->body);

        $this->entityManager->persist($post);
        $this->entityManager->flush();

        return Response::redirect($this->urlGenerator->generate('post.show', ['id' => $post->getId()]));
    }
}

Schema management

Two approaches — choose based on your situation.

New projects (development):

php bin/console orm:schema-tool:create

Creates tables directly from entity definitions. Fast for greenfield setup but does not create migration files.

Existing databases (production and teams):

# Generate a migration from the diff between entities and current schema
php bin/console migrations:diff

# Review the generated file in database/migrations/
# Then apply it
php bin/console migrations:migrate

Never run orm:schema-tool:create against an existing database. Use migrations:migrate for any database that already has data.

Migrations

php bin/console migrations:diff       # Generate migration from entity changes
php bin/console migrations:migrate    # Run all pending migrations
php bin/console migrations:status     # Show current version and pending count
php bin/console migrations:list       # List all migrations and their status
php bin/console migrations:execute    # Run a specific migration

Migrations are stored in database/migrations/. Each class extends AbstractMigration and implements up() and down().

// database/migrations/Version20260507194151.php
public function up(Schema $schema): void
{
    $this->addSql('CREATE TABLE posts (id INTEGER NOT NULL, title VARCHAR(255) NOT NULL, PRIMARY KEY(id))');
}

public function down(Schema $schema): void
{
    $this->addSql('DROP TABLE posts');
}

The `QueryBuilder`

Modufolio\Appkit\Doctrine\QueryBuilder is a fluent wrapper around DBAL's query builder for raw SQL queries without ORM overhead. The API is inspired by the Laravel Query Builder.

use Modufolio\Appkit\Doctrine\QueryBuilder;

$qb = new QueryBuilder($this->entityManager->getConnection());

$posts = $qb->from('posts')
    ->select('id', 'title', 'created_at')
    ->where('published', '=', true)
    ->orderBy('created_at', 'DESC')
    ->limit(10)
    ->get();

Identifiers are validated, values are bound

Values always travel as bound parameters. Identifiers — table, column and alias names given to from(), select(), where*(), join*(), orderBy(), groupBy(), insert() and update() — are checked against a strict grammar (name, alias.name, schema.table.name, *, alias.*) and rejected with an InvalidArgumentException otherwise; comparison operators are allowlisted (=, <>, !=, <, <=, >, >=, LIKE, NOT LIKE, ILIKE, NOT ILIKE). A request value that reaches orderBy() therefore cannot inject SQL — but it can still name any column. For user-selectable sorting use orderByAllowed(), which restricts the column to a list you define and maps public keys to real columns:

$qb->from('users', 'u')
    ->orderByAllowed($query['sort'] ?? 'name', $query['dir'] ?? 'asc', [
        'name' => 'u.name',
        'date' => 'u.created_at',
    ]);

An unknown key or direction throws an InvalidArgumentException, which the exception handler turns into a 400 — the right answer to a tampered ?sort=.

Expressions (COUNT(*), CASE ..., arithmetic) do not pass the identifier check; use selectRaw(), whereRaw() or whereExpression() for them. Those are raw by design and must never receive request input.

Method reference

Method	Description
`from(string $table, ?string $alias)`	Set the table
`select(string ...$columns)`	Select columns (`id`, `u.name`, `u.*`, or `['col' => 'alias']`); expressions go through `selectRaw()`
`selectRaw(string $expr, array $bindings)`	Select a raw expression; `?` placeholders bind in order
`where(string $col, string $op, mixed $val)`	Add a WHERE condition
`orWhere(string $col, string $op, mixed $val)`	Add an OR WHERE condition
`whereIn(string $col, array $values)`	WHERE IN clause
`whereNotIn(string $col, array $values)`	WHERE NOT IN clause
`whereNull(string $col)`	WHERE IS NULL
`whereNotNull(string $col)`	WHERE IS NOT NULL
`whereRaw(string $expr, array $bindings)`	Raw WHERE expression; `?` placeholders bind in order, e.g. `whereRaw('age > ?', [25])`
`join(...)`	INNER JOIN
`leftJoin(...)`	LEFT JOIN
`rightJoin(...)`	RIGHT JOIN
`orderBy(string $col, string $dir)`	ORDER BY — `$col` must be a plain column reference
`orderByAllowed(string $col, string $dir, array $allowed)`	ORDER BY restricted to an allowlist; safe for request-controlled sorting
`groupBy(string ...$cols)`	GROUP BY
`limit(int $n)`	LIMIT
`offset(int $n)`	OFFSET
`insert(array $values)`	INSERT — returns affected rows
`update(array $values)`	UPDATE — returns affected rows
`delete()`	DELETE — returns affected rows
`get()`	Fetch all rows as array
`first()`	Fetch first row or null
`count()`	Fetch count
`fetchColumn(string $col)`	Fetch a single column as array
`toSql()`	Return the SQL string

Pagination

DoctrineOrmPagination paginates a Doctrine Query object.

use Modufolio\Appkit\Doctrine\DoctrineOrmPagination;

$query = $this->entityManager
    ->createQuery('SELECT p FROM App\Entity\Post p ORDER BY p.createdAt DESC');

$pagination = (new DoctrineOrmPagination())->paginate($query, page: $page, limit: 20);

$items   = $pagination->getResults();
$total   = $pagination->total();
$pages   = $pagination->pages();
$hasPrev = $pagination->hasPrevPage();
$hasNext = $pagination->hasNextPage();
$range   = $pagination->range(5); // array of page numbers around current page

`DoctrineOrmPagination` methods

Method	Returns	Description
`paginate(Query, int $page = 1, int $limit = 10)`	`self`	Instance method; populates and returns `$this`
`getResults()`	`array`	Current page items
`total()`	`int`	Total item count
`limit()`	`int`	Items per page
`page()`	`int`	Current page number
`pages()`	`int`	Total page count
`offset()`	`int`	Current page offset
`firstPage()`	`int`	Always `1`
`lastPage()`	`int`	Same as `pages()`
`isFirstPage()`	`bool`
`isLastPage()`	`bool`
`hasPrevPage()`	`bool`
`prevPage()`	`?int`
`hasNextPage()`	`bool`
`nextPage()`	`?int`
`range(int $range = 5)`	`array`	Page numbers centred on current page
`hasPages()`	`bool`	`true` if more than one page
`start()`	`int`	Index of first item on current page
`end()`	`int`	Index of last item on current page

Soft delete

SoftDeleteFilter excludes soft-deleted records from queries. Registering it only makes it known to Doctrine — OrmConfigurator::addFilter() calls Configuration::addFilter() and nothing more — so nothing is filtered until you enable it on the entity manager:

// config/doctrine.php
$orm->addFilter('soft_delete', \Modufolio\Appkit\Doctrine\Filter\SoftDeleteFilter::class);

// After the entity manager exists — in a service, a controller, or once
// after boot. The setting belongs to the entity manager instance, so a
// worker runtime that reset() the app has to enable it again.
$this->entityManager->getFilters()->enable('soft_delete');

The filter appends deleted_at IS NULL to every entity that maps a deletedAt field and leaves other entities alone. Both names are fixed. AppKit configures no naming strategy, so Doctrine's default would name the column deletedAt; give the column its name explicitly:

#[ORM\Column(name: 'deleted_at', nullable: true)]
private ?\DateTimeImmutable $deletedAt = null;

Disable the filter for queries that must see soft-deleted rows:

$this->entityManager->getFilters()->disable('soft_delete');

DQL queries

// Direct DQL
$users = $this->entityManager
    ->createQuery('SELECT u FROM App\Entity\User u WHERE u.enabled = true')
    ->getResult();

// With parameters
$user = $this->entityManager
    ->createQuery('SELECT u FROM App\Entity\User u WHERE u.email = :email')
    ->setParameter('email', $email)
    ->getOneOrNullResult();

For quick SQL inspection during development:

php bin/console dbal:run-sql "SELECT COUNT(*) FROM users"

Navigation Menu

Repository navigation

Database

Configuring Doctrine

Switching to MySQL or PostgreSQL

`OrmConfigurator` methods

Custom Doctrine types

Per-environment config replaces the base file

Defining an entity

Defining a repository

Accessing the entity manager

Schema management

Migrations

The `QueryBuilder`

Identifiers are validated, values are bound

Method reference

Pagination

`DoctrineOrmPagination` methods

Soft delete

DQL queries

FilesExpand file tree

database.md

Latest commit

History

database.md

File metadata and controls

Database

Configuring Doctrine

Switching to MySQL or PostgreSQL

OrmConfigurator methods

Custom Doctrine types

Per-environment config replaces the base file

Defining an entity

Defining a repository

Accessing the entity manager

Schema management

Migrations

The QueryBuilder

Identifiers are validated, values are bound

Method reference

Pagination

DoctrineOrmPagination methods

Soft delete

DQL queries

`OrmConfigurator` methods

The `QueryBuilder`

`DoctrineOrmPagination` methods