Database and migrations

A module schema is the agreement of two parts: SQL migration files build the schema, and Doctrine entities (#[ORM\Entity]) map that schema to PHP objects. You write the two together, matching each other.

Table naming#

Module tables carry the cp_<module>_* prefix: cp_notes, cp_docs_pages, cp_portal_polls. The prefix prevents collisions with core and other module tables and makes it clear what you will delete in the tables() list.

Migration files#

You build the schema with Resources/migrations/00N_name.sql files:

CREATE TABLE IF NOT EXISTS cp_example_items (
    id INT AUTO_INCREMENT NOT NULL,
    title VARCHAR(255) NOT NULL,
    status VARCHAR(16) NOT NULL DEFAULT 'published',
    created_at DATETIME NOT NULL,
    PRIMARY KEY (id),
    INDEX idx_example_status (status)
) DEFAULT CHARACTER SET utf8mb4 COLLATE `utf8mb4_unicode_ci` ENGINE = InnoDB;

Every table must be utf8mb4 + utf8mb4_unicode_ci + InnoDB; emoji and every language are stored safely.

Operating rules#

Rule	Why
Files run in order by name	The 001_, 002_ prefixes set the order
What has been applied is recorded by file name	There is no content checksum; the name means "done"
An applied file is frozen	Changing it does not reach old sites; write a new file for a fix
Every statement must be idempotent	If the ledger is reset or a backup is restored, the file can run again

Idempotent patterns#

-- Table
CREATE TABLE IF NOT EXISTS cp_example_items ( ... );

-- Initial data: do not overwrite if the administrator changed it
INSERT IGNORE INTO cp_example_settings (name, value) VALUES ('mode', 'default');

-- Adding a column: guarded with information_schema (each statement on its own lines, ending with ";" and a line break)
SET @cp_example_summary := (
    SELECT COUNT(*) FROM information_schema.COLUMNS
    WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'cp_example_items' AND COLUMN_NAME = 'summary'
);
SET @cp_example_summary_sql := IF(
    @cp_example_summary = 0,
    'ALTER TABLE cp_example_items ADD COLUMN summary VARCHAR(500) DEFAULT NULL',
    'DO 0'
);
PREPARE cp_example_summary_stmt FROM @cp_example_summary_sql;
EXECUTE cp_example_summary_stmt;
DEALLOCATE PREPARE cp_example_summary_stmt;

ALTER TABLE ADD COLUMN is not directly idempotent (on the second run it gives a "column already exists" error); that is why the guard above exists. Write guarded versions for ADD INDEX and DROP COLUMN in the same way.

Entities (Doctrine)#

#[ORM\Entity]
#[ORM\Table(name: 'cp_example_items')]
class Item
{
    #[ORM\Id]
    #[ORM\GeneratedValue]
    #[ORM\Column(type: Types::INTEGER)]
    private ?int $id = null;

    #[ORM\Column(type: Types::STRING, length: 255)]
    public string $title = '';

    #[ORM\Column(name: 'created_at', type: Types::DATETIME_IMMUTABLE)]
    public \DateTimeImmutable $createdAt;
}

• The module's Entity/ folder is introduced to Doctrine automatically.
• The SQL and the entity must match exactly: type, length, NULL status, column name. A mismatch shows up as a difference in the Doctrine schema check.
• Use DATETIME_IMMUTABLE, not DateTime.
• Column names are snake_case on the SQL side and camelCase in PHP properties: #[ORM\Column(name: 'created_at')].

Relations and foreign keys#

For relations within the module you can use foreign keys (like ON DELETE CASCADE). Do not attach foreign keys to core tables (user, node); keep the column as a plain integer and check at the application layer. This way integrity is not broken if the core schema changes or the module is disabled. In the tables() list write child tables first (deletion order).

Field definition and initial data#

Adding fields to Node types is also a migration job: a row is added to the cp_field_definitions table with INSERT IGNORE. IGNORE is important, because the administrator may have changed the setting in AACP → Fields and the re-applied file must not overwrite it:

INSERT IGNORE INTO cp_field_definitions
  (bundle, name, type, label, help, required, cardinality, translatable, queryable,
   field_group, weight, settings, view_capability, edit_capability, created_at, updated_at)
VALUES
  ('example', 'summary', 'textarea', 'Summary', 'Shown in lists.', 0, 1, 0, 0, NULL, 0, '{}', NULL, NULL, NOW(), NOW());

Field types: text, textarea, rich_text, boolean, integer, decimal, email, url, date, datetime, select, reference, image, file. Details: Forms and the Field API and Field system.

The same pattern for taxonomy vocabularies: INSERT IGNORE INTO cp_vocabularies (...).

Upgrade: new version, new file#

1. Add the 002_example_summary.sql file (do not touch the old one).
2. Increase the version in module.json.
3. Release. On the site, cp:update sees the module's version difference and applies only the new file.

If a data fix is needed (back-filling a column, rewriting a value) and SQL is not enough, write an UpdateHookInterface: see Update system.

Uninstall#

cp:module:deactivate Example does not delete data. With --purge the uninstall hook runs, the tables in the tables() list (children first) and the module's settings are deleted. Tell the user clearly which data you will delete; this cannot be undone.

Consistency check#

cp:doctor checks whether the tables and columns an applied migration claims to have created really exist (module_sql_integrity). It reports drift but does not repair; repair is done with an explicit update hook that makes the ledger entry forgotten (the idempotent file is retried).

Development tips#

• When trying a new migration, run it twice on the test database; the second run must not fail.
• To see the Doctrine schema difference: php cp-core/bin/console doctrine:schema:validate.
• ALTER can lock a large table; do large table changes in production in maintenance mode.
• When writing queries, avoid N+1 on list screens; see Cache and performance.