From PHP Attributes to SQL: Inside Semitexa Schema Sync

Start with an article that needs a summary

Suppose the database already has an articles table with id and title. You want an optional summary. In a discovered application module, the target resource definition can express that change directly:

use Semitexa\Orm\Adapter\MySqlType;
use Semitexa\Orm\Attribute\{Column, FromTable, PrimaryKey};

#[FromTable(name: 'articles')]
final readonly class ArticleResource
{
    public function __construct(
        #[PrimaryKey(strategy: 'uuid')]
        #[Column(type: MySqlType::Varchar, length: 36)]
        public string $id,

        #[Column(type: MySqlType::Varchar, length: 255)]
        public string $title,

        #[Column(type: MySqlType::Text, nullable: true)]
        public ?string $summary = null,
    ) {}
}

The new declaration is the last property. MySqlType::Text supplies the database type, and nullable: true permits existing rows to have no summary. The PHP type ?string and constructor default express the corresponding application value. The SQL nullability is declared explicitly in the attribute.

The UUID primary-key strategy belongs to the resource metadata. This example supplies existing identifiers; it does not rely on a MySQL auto-increment column. The table and column declarations give the ORM enough information to build an intended schema.

Our isolated example produced this MySQL statement for the change:

ALTER TABLE `articles` ADD COLUMN `summary` TEXT NULL DEFAULT NULL;

Understanding how that statement appears takes us through three separate responsibilities: collection, comparison, and planning.

SchemaCollector turns attributes into table definitions

SchemaCollector starts with classes discovered through #[FromTable]. It groups those classes by physical table name, reads their properties and attributes, and constructs a shared TableDefinition for each table. A ColumnDefinition carries details such as the SQL type, length, nullability, default, primary-key strategy, and PHP property name.

The collector also processes class-level indexes and relation metadata. It gathers the structure before resolving foreign keys, so referenced resources can participate in the same collected schema. A configured connection filter selects the resources that belong to the requested connection.

This stage validates the description. For example, a nullable primary key becomes a collection error. An index with an empty column name also produces an error, rather than silently becoming a different index. The orm:sync command checks collection errors before continuing to comparison.

Several modules can contribute to one physical table

Now suppose the Content module owns the article resource above, and an SEO module needs a search-specific title. Putting that field in Content’s class couples the base module to the extension’s requirements. A separate table introduces another storage relationship for a value that belongs to the same article row.

Semitexa supports a direct declaration: the SEO module defines its own resource targeting articles. Content’s resource stays unchanged, and the two classes need no inheritance relationship:

use Semitexa\Orm\Adapter\MySqlType;
use Semitexa\Orm\Attribute\{Column, FromTable, Index, PrimaryKey};

// A separate resource in the SEO module.
#[FromTable(name: 'articles')]
#[Index(columns: ['seoTitle'], name: 'idx_articles_seo_title')]
final readonly class ArticleSeoResource
{
    public function __construct(
        #[PrimaryKey(strategy: 'uuid')]
        #[Column(type: MySqlType::Varchar, length: 36)]
        public string $id,

        #[Column(type: MySqlType::Varchar, length: 160, nullable: true)]
        public ?string $seoTitle = null,
    ) {}
}

Both resources enter the same connection’s discovery and collection scope. The collector finds an existing TableDefinition under articles and contributes the new column and index to it. The matching id is present once. The merged target contains id, title, summary, and seoTitle.

That merge happens before comparison. Content’s partial view does not make the SEO field a removal candidate while the SEO resource is also discovered. The comparator sees the combined description of the table. Against the already synchronized Content schema, our verified MySQL plan adds only the extension’s field and index:

ALTER TABLE `articles` ADD COLUMN `seoTitle` VARCHAR(160) NULL DEFAULT NULL;
ALTER TABLE `articles` ADD INDEX `idx_articles_seo_title` (`seoTitle`);

The useful unit of ownership is now the module’s contribution. A module can introduce its own typed fields and indexes through its own resource declaration, while Schema Sync reconciles the assembled table. Adding the extension requires no edit to the base resource and no hand-written merge listener. The shared-table documentation describes this model.

One row, separate typed views

The merged schema does not add PHP properties to Content’s class. The base and SEO resources remain separate views with a shared identity. In our in-memory SQLite check, both hydrated from the same row: Content saw its title and summary, and SEO saw its search title. Dehydrating the SEO view produced only id and seoTitle. Creating a new row still has to satisfy required columns contributed by the other resources; a partial view alone does not supply those values.

Agree on shared definitions

Shared columns need compatible declarations. The current collector skips a column when its database name is already present. It keeps the first definition and does not check whether a later definition disagrees about its type or length. We verified that conflicting title lengths of 255 and 120 produced different targets when collection order was reversed, without a collection error. Modules should repeat the identity consistently and use distinct names for fields they own.

Named indexes have a different rule: an identical definition is deduplicated, while the same effective index name with different columns or uniqueness raises a conflict. Removing a module from discovery also changes the assembled target. Its remaining database columns can become removal candidates, so module removal must be considered together with the schema policy described below.

The distinction is the declaration and composition path

Other ORM stacks expose extension mechanisms too. Doctrine documents metadata events that can add field mappings; SQLAlchemy documents extending an existing Table in shared metadata. Semitexa makes contributions from independently declared module resources part of ordinary schema collection. That is the concrete architectural advantage illustrated here.

The comparison is against the current database state

The MySQL SchemaComparator reads database metadata through INFORMATION_SCHEMA. The SQLite comparator reads SQLite’s schema catalog and PRAGMA metadata. Both turn that information into a representation that can be compared with the collected resource definitions.

A SchemaDiff records the differences: tables to create, columns to add or alter, indexes to change, foreign keys to change, and objects that appear only in the database. For our existing articles table, the desired schema contains one additional column. The diff therefore records an addition for summary.

Comparison includes more than checking names. Column type, nullability, default, and auto-increment state can produce alterations. The MySQL comparator normalizes certain type representations and converts code defaults into the form reported by the database. This helps an unchanged declaration avoid becoming a repeated alteration merely because its representation differs.

Once the database matches the collected description, the diff is empty. The command exits with “Database is up to date. No changes needed.” That is the basis of repeated reconciliation: each run asks what differs now.

The collection scope matters. Objects present in the database but absent from the discovered resource set can become removal candidates, subject to configured exclusions and the removal policy. Review the selected connection and the discovered schema when a plan contains unexpected changes.

A diff becomes a plan you can inspect before execution

SyncEngine::buildPlan() converts the diff into an ExecutionPlan. Each DdlOperation carries its SQL, operation type, table name, description, and an isDestructive marker. Planning and execution are separate steps, so producing SQL does not require applying it.

The installed CLI exposes the following inspection commands:

bin/semitexa orm:diff --connection=default
bin/semitexa orm:sync --connection=default --dry-run -v
bin/semitexa orm:sync --connection=default --dry-run \
  --output=var/schema-plan.sql

orm:diff shows the differences. The sync dry run builds the plan; verbose output shows the SQL alongside its descriptions. The output option saves SQL to a file. The command can display both safe and destructive groups, while the exported statements exclude destructive operations unless --allow-destructive is supplied.

For this nullable-field addition, the plan contains one operation classified as safe. The existing-row example needs no value transformation: a missing summary is a valid value.

The classification has a specific implementation. It recognizes destructive removals and potentially destructive type changes, with widening rules for supported type families. For example, widening a VARCHAR can be treated differently from narrowing it. The marker does not prove that an operation is inexpensive, compatible with every deployed application version, or valid for every existing row. Index creation, locking, and new constraints still have database consequences.

Removing a MySQL column takes two distinct decisions

Now remove summary from the resource definition while it still exists in MySQL. The comparator reports a column present only in the database. The planner examines the column’s comment to decide which phase of removal applies.

If the column has not been marked, the generated operation adds the SEMITEXA_DEPRECATED comment. For our nullable TEXT column, the verified first-phase SQL is:

ALTER TABLE `articles` MODIFY COLUMN `summary` text NULL DEFAULT NULL COMMENT 'SEMITEXA_DEPRECATED';

The column and its values remain present. On a subsequent comparison, the stored comment identifies it as already deprecated. The planner can then generate the second phase:

ALTER TABLE `articles` DROP COLUMN `summary`;

That operation is marked destructive. The default statement selection excludes it, and execution also filters destructive operations unless explicitly allowed. To review the full second-phase plan without applying it, use:

bin/semitexa orm:sync --connection=default \
  --dry-run --allow-destructive -v

The flag applies to the plan’s destructive group, so inspect every included operation. In the first phase, even that flag does not turn an unmarked column directly into a drop: the planner still generates the deprecation operation. This gives the MySQL workflow an observable intermediate state before removal.

This mechanism relies on database comments and the ORM’s generated SQL. Its behavior must be checked for the configured dialect; SQLite does not support the same comment-based phase.

A schema description cannot infer what existing values mean

Imagine renaming the PHP property title to headline while keeping the database column unchanged. The column attribute supports an explicit database name:

#[Column(type: MySqlType::Varchar, length: 255, name: 'title')]
public string $headline,

The PHP property can change while the schema continues to describe the same title column. Without that mapping, a new database name expresses a different target column. A structural diff cannot determine that the old values should move into it.

Changing data requires its own procedure. Splitting one field into two, backfilling a new column, converting historical values, and introducing a required field all need decisions about existing records. A practical rollout may add a nullable field, deploy code that can handle both states, backfill and verify values, then tighten the constraint in a later step.

Schema Sync handles the comparison between declared structure and database structure. Data transformations and application rollout sequencing remain explicit engineering work. Keeping that boundary clear makes the generated plan easier to reason about.

Execution respects the connection and the database dialect

On the pooled MySQL execution path, the sync engine borrows one dedicated connection for the selected operations and returns it in a finally block. Keeping the plan on one connection avoids sending related work through separate pool acquisitions. The reviewed tests exercise this connection lifecycle and cleanup after a failing operation.

MySQL DDL has implicit-commit behavior, as described in the MySQL manual. The engine consequently avoids wrapping its MySQL plan in a transaction that would promise a rollback of the entire sequence. If a later statement fails, earlier schema changes may already be applied. Inspect the resulting database state before planning the next action.

SQLite follows a different path. The engine uses a transaction wrapper where its adapter supports transactional DDL. The verified in-memory example added summary, preserved the existing title, gave the existing row a null summary, and produced an empty diff on the next comparison.

Support for a database also depends on the particular operation. SQLite’s ALTER TABLE documentation describes its available alterations and table-reconstruction procedure. In the reviewed Semitexa implementation, some alterations produce explicit unsupported-operation placeholders. Execution rejects those placeholders with a table-recreation error. Comment-based deprecation is one such unsupported operation; a flag does not make that SQL executable.

When an audit logger is configured, a successfully completed execution writes the executed operations in JSON and SQL form. The logger is called after the operation loop. Those files are useful execution records, but a failed MySQL sequence can leave partial changes before that logging point. The current database schema remains essential evidence during recovery.

The useful abstraction is an inspectable reconciliation plan

The path is now concrete: module resources contribute to shared table definitions; the comparator identifies differences; the planner generates SQL and policy markers; execution applies the selected operations. Each representation answers a different question, and the CLI exposes the transition before a write.

We verified this walkthrough with the installed ORM on September 30, 2026. The MySQL example used an explicit schema-state fixture to exercise collection, the comparator’s table logic, and plan generation; its SQL was inspected without execution against MySQL. The SQLite example used a real in-memory database for introspection, execution, preservation of an existing row, and the empty post-sync diff.

An additional controlled discovery fixture represented Content and SEO contributions, checked their merged plan and conflict behavior, and exercised both typed views against the same SQLite row. Thirteen existing ORM tests passed, with 35 assertions, covering collection, comparison, and dedicated-connection execution. The command examples were checked against the installed CLI help. These checks establish the illustrated behavior within that scope.

For a resource change, start by reading the desired definition and the current diff. Inspect the generated SQL, identify any data work, and check the actual dialect’s execution behavior. That sequence makes a small PHP edit traceable all the way to its database effect.

Once the storage structure is defined, follow the next boundary in From Database Rows to Business Rules: how a persistence resource passes through an explicit mapper into a business model, and how that model’s behavior returns through the repository to storage.