Declarative Database Schema v26.7+

Maho can build and maintain a module's database tables from a single declarative file, sql/schema.php, instead of a growing chain of imperative install and upgrade scripts. The file describes the target shape of your tables, and Maho reconciles the live database with that target automatically, using Doctrine DBAL's own schema Comparator.

This is the same idea Magento 2 introduced with db_schema.xml, expressed with DBAL's native Schema objects so there is no separate XML or array format to learn or maintain.

Overview

With the traditional setup resources system, every schema change means bumping a module version and writing a new upgrade-X.Y.Z-A.B.C.php with the exact ALTER statements. Over time this becomes a long, hard-to-review history of incremental changes, and the current shape of a table can only be reconstructed by reading every script in order.

The declarative system replaces that with a single file per module that always describes the table as it should be now:

• One source of truth - the file reflects the current target structure, not a diff history.
• Easy to review - a column or index change is a one-line edit, not a new migration script.
• Engine-agnostic - you declare intent (types, indexes, foreign keys); Maho emits the correct DDL for MySQL/MariaDB, PostgreSQL, and SQLite.
• Safe in-place migration - existing installs are reconciled with native ALTERs (or a table rebuild on SQLite), idempotently, landing on a structure byte-identical to a fresh install.
• No reinvention - it is built directly on DBAL's Schema, Comparator, and Table primitives.

Schema (table structure) becomes declarative. Data work stays imperative, see Relationship to setup resources.

The sql/schema.php file

Each module may ship an optional sql/schema.php that returns a closure receiving a shared DBAL Schema. The closure adds (or augments) tables on that schema:

<?php

/**
 * SPDX-FileCopyrightText: 2026 Maho <https://mahocommerce.com>
 * SPDX-License-Identifier: OSL-3.0
 * @package Mage_Cron
 */

declare(strict_types=1);

use Doctrine\DBAL\Schema\DefaultExpression\CurrentTimestamp;
use Doctrine\DBAL\Schema\PrimaryKeyConstraint;
use Doctrine\DBAL\Schema\Schema;
use Doctrine\DBAL\Types\Types;

return function (Schema $schema): void {
    $t = $schema->createTable('cron_schedule');
    $t->addColumn('schedule_id', Types::INTEGER, ['unsigned' => true, 'autoincrement' => true]);
    $t->addColumn('job_code',    Types::STRING,  ['length' => 255, 'default' => '0']);
    $t->addColumn('status',      Types::STRING,  ['length' => 7, 'default' => 'pending']);
    $t->addColumn('messages',    Types::TEXT,    ['notnull' => false]);
    $t->addColumn('created_at',  Types::DATETIME_MUTABLE, ['default' => new CurrentTimestamp()]);
    $t->addColumn('scheduled_at', Types::DATETIME_MUTABLE, ['notnull' => false]);

    $t->addPrimaryKeyConstraint(
        PrimaryKeyConstraint::editor()->setUnquotedColumnNames('schedule_id')->create(),
    );
    $t->addIndex(['job_code']);          // index name autogenerated by DBAL
    $t->setComment('Cron Schedule');
};

The file lives in sql/, alongside any existing imperative sql/<resource>_setup/ scripts. Maho discovers it by convention, no configuration is required.

Conventions

Follow these conventions so your schema matches the core modules and migrates cleanly:

• Unprefixed table names. Author tables with their bare names (cron_schedule, not prefix_cron_schedule). Maho applies the configured table_prefix from app/etc/local.xml centrally.
• notnull defaults to true. DBAL columns are NOT NULL by default, so you only spell out 'notnull' => false for nullable columns. This makes the nullable columns stand out.
• No zero-date defaults. Never use '0000-00-00 00:00:00'. It is not strict-mode safe and cannot be stored on PostgreSQL. Use a nullable date/datetime column ('notnull' => false) and let real NULL represent "no date".
• Use CurrentTimestamp for CURRENT_TIMESTAMP defaults, not the literal string:

  use Doctrine\DBAL\Schema\DefaultExpression\CurrentTimestamp;
  $t->addColumn('created_at', Types::DATETIME_MUTABLE, ['default' => new CurrentTimestamp()]);
  

• Do not name indexes or foreign keys. Call addIndex(), addUniqueIndex(), and addForeignKeyConstraint() without a name argument. DBAL autogenerates a name (a column hash, plus the table name for FKs), which keeps you within the 64-character identifier limit and guarantees uniqueness automatically. For a fulltext index, pass null as the name to keep the flag: addIndex(['data_index'], null, ['fulltext']).
• Charset and collation default to utf8 / utf8_general_ci (matching Maho's legacy adapter). Override per table with $t->addOption('charset', 'utf8mb4') if needed.
• No runtime dependencies. A schema.php must parse with no Mage::app() bootstrap, no database connection, and no engine-aware helpers. Keep it pure structure.

Foreign keys

Declare a foreign key with the referenced table's unprefixed name, the local column(s), and the referenced column(s). Do not pass a constraint name:

$tax->addForeignKeyConstraint(
    'catalog_product_entity',     // referenced table (unprefixed)
    ['entity_id'],                // local column(s)
    ['entity_id'],                // referenced column(s)
    ['onUpdate' => 'CASCADE', 'onDelete' => 'CASCADE'],
);

Make sure the foreign-key column's type matches the referenced column exactly (same integer width, the same unsigned flag), or the database will reject the constraint.

Cross-module tables and foreign keys

The Schema passed to your closure is shared across all modules, and modules are processed in dependency (depends_on) order. That means you can reference, or even extend, a table another module declared, as long as your module declares a dependency on it.

To add a foreign key to another module's table, or to graft columns onto it, fetch it with $schema->getTable():

return function (Schema $schema): void {
    // weee_tax is owned by this module
    $tax = $schema->createTable('weee_tax');
    // ...
    // entity_id references a table owned by Mage_Catalog
    $tax->addForeignKeyConstraint('catalog_product_entity', ['entity_id'], ['entity_id'],
        ['onUpdate' => 'CASCADE', 'onDelete' => 'CASCADE']);

    // Graft WEEE columns onto a flat sales table owned by Mage_Sales
    $item = $schema->getTable('sales_flat_order_item');
    $item->addColumn('weee_tax_applied_amount', Types::DECIMAL,
        ['precision' => 12, 'scale' => 4, 'notnull' => false]);
};

How and when it runs

The declarative schema is applied automatically, you do not call it yourself, in two places:

• ./maho install (during Mage_Install_Model_Installer::installDb())
• ./maho migrate

In both, the order is:

1. Declarative schema   create/alter tables from every sql/schema.php
2. Legacy schema scripts  sql/<resource>_setup/install-*.php, upgrade-*.php
3. Data scripts           data/<resource>_setup/data-*.php
4. Maho scripts           sql/maho_setup/maho-X.Y.Z.php

The declarative phase runs first so that any legacy script which inserts or updates rows finds its tables already in place.

The ./maho migrate command

./maho migrate applies all four phases above. The declarative phase is idempotent: a second run detects no structural difference and applies zero statements.

Previewing changes with --dry-run

./maho migrate --dry-run

This prints the exact SQL the declarative phase would run without touching the database, and lists which setup scripts would run. Destructive statements (dropping an index or foreign key) are highlighted, so you can see them before they execute. Add -v to see the raw SQL.

./maho migrate --dry-run -v

Destructive changes

When a migration would drop an index or foreign key (see What the migration preserves, and what it normalizes), ./maho migrate prints a warning listing those statements, then applies them. The migration always runs to completion, it never stops to ask, so unattended upgrades (deploy scripts, CI, the backend upgrade flow) behave exactly as they always have. Use --dry-run first if you want to review the dropped objects before they are applied.

In-place migration

./maho migrate reconciles an existing (legacy or already-declarative) database with the target schema, in place, on every supported engine:

• MySQL / MariaDB / PostgreSQL are reconciled with native ALTERs (rename, type change, column/index/foreign-key add or drop).
• SQLite cannot ALTER a column type or add a foreign key, so any table that needs a structural change is rebuilt: its rows are copied to a temporary table, the table is recreated straight from the declarative target (primary key, foreign keys, and every index included), and the data is copied back. Tables that already match emit nothing.

Foreign-key enforcement is suspended for the whole batch, and on PostgreSQL and SQLite the batch runs inside a transaction, so a failure mid-migration rolls back cleanly.

The migration is designed so that a legacy install reconciled with ./maho migrate lands on a schema byte-identical to a fresh declarative install, and a second run is a no-op.

What the migration preserves, and what it normalizes

The migration treats data and derivable structure differently:

• Columns are preserved. A column a third-party module or a DBA added to a managed (core) table survives migration with its data untouched. The migration never drops a column it does not manage, because a column holds data and dropping it is irreversible loss.
• Whole undeclared tables are left untouched. The migration only ever considers the tables that some module declares.

But:

• Undeclared indexes and foreign keys are removed. The migration normalizes a managed table to its declared structure, so an index or foreign key that no schema.php declares is dropped. This is intentional: it cleans up the redundant and obsolete indexes/foreign keys that years of legacy upgrade scripts accumulated (for example a unique index the declarative schema turned into the primary key, or an index that duplicated another), and it is what lets a migrated database land byte-identical to a fresh install. Indexes and foreign keys carry no data, so removing one is not destructive the way dropping a column is, a dropped index is a rebuildable performance object, a dropped foreign key is a re-addable constraint.

Keeping a custom index or foreign key

If you have a custom index or foreign key on a managed table that you want to keep, declare it in a module's own sql/schema.php. Once it is part of the declared target it is treated exactly like a core object and is never removed. Anything not declared is treated as legacy structure to normalize away.

Run ./maho migrate --dry-run before migrating to see exactly which indexes and foreign keys would be removed.

Relationship to setup resources

The declarative schema replaces only the structure part of setup resources. Data work stays imperative, split by when it needs to run:

Existing third-party modules keep working unchanged. A module with only the classic imperative scripts and no sql/schema.php is migrated exactly as before. Adopting the declarative schema is opt-in, per module.

Module versions

With structure declarative, a module's <version> no longer tracks schema state, it only gates the remaining data scripts. To change a table, you edit sql/schema.php directly; there is no version bump and no new migration file for a structural change. You still bump the version when you add a new data-*.php script.

Authoring a schema for your own module

1. Create app/code/<...>/<Your_Module>/sql/schema.php returning a closure as shown above.
2. Declare <depends> on any module whose tables you reference or extend.
3. Run ./maho migrate --dry-run to review the SQL, then ./maho migrate to apply it.
4. For application data (seed rows, config, EAV attributes), add a data/<resource>_setup/data-*.php script and bump your module version.

To change the schema later, edit sql/schema.php and run ./maho migrate again. The reconciler figures out the difference.

Known behaviors and limitations

• A new NOT NULL column with no default on a populated table is refused. Existing rows have no value to put in it. Give the column a default, or make it nullable. This is the only migration that any engine refuses outright.
• Legacy zero-date values are converted to NULL. Date/datetime columns that older installs stored as the '0000-00-00 00:00:00' sentinel are made nullable by the declarative schema, and existing sentinel rows are set to real NULL during migration (on MySQL/MariaDB/SQLite; PostgreSQL cannot hold the sentinel, so there is nothing to clean).
• Some report aggregation tables now require order_status NOT NULL. A store that already holds aggregation rows with a NULL order_status should clear the affected aggregation table and reindex its reports before migrating. These are derived tables, no source data is lost.
• PostgreSQL legacy bigint IP columns lose their values. A few nullable IP-tracking columns move from bigint (ip2long) to a binary type with no faithful cast, so existing values are discarded on PostgreSQL. MySQL/MariaDB are unaffected.

See also the Database Layer documentation for the underlying Doctrine DBAL adapter, query builder, and multi-engine support.