Migrations

Schema changes in newmeta always roll out via SQL migrations. Never through the update() method in bootstrap.php, never through a direct INSERT in plugin code. This page covers where migrations live, how they're named, and how the MigrationRunner executes them.

Three scopes

The MigrationRunner scans three directories:

Scope	Path	Contents
Core	_migrations/core/	System migrations (baseline, core tables like _migrations, sessions, api_keys, …)
Plugins central	_migrations/plugins/{name}/	Migrations shipped with the core bundle
Plugin-local	_public/extensions/core/backend/{plugin}/migrations/	Migrations that live with the plugin itself

When files exist for the same plugin name in both sources, they merge: different file names run additively; identical file names (e.g. 001_create_tables.sql in both central and plugin-local) follow a "later source wins" rule and the plugin-local variant overrides. So plugins can patch central migrations locally without replacing the core history.

Naming convention

_migrations/core/
├── 001_baseline.sql
├── 002_add_2fa_columns.sql
├── 003_create_api_keys.sql
├── 004_create_allowed_origins.sql
├── ...
└── 018_widen_consent_text_columns.sql

• Number: three digits, leading zeros (001, 002, …). Files within a scope are sorted alphabetically — three digits preserves the correct order up to 999 migrations.
• Underscores instead of spaces or dashes.
• Descriptive names: 002_add_2fa_columns.sql instead of 002_update.sql.
• One logical change per file — prefer 008_add_blog_visible.sql + 009_add_spacing_and_flex.sql over a single monster.

Example from the real repo (_migrations/core/): 18 migrations, each thematically focused.

The _migrations table

Every successfully executed migration is logged in the _migrations table:

CREATE TABLE IF NOT EXISTS `_migrations` (
  `id`          INT AUTO_INCREMENT PRIMARY KEY,
  `scope`       VARCHAR(100) NOT NULL,      -- 'core' or plugin name
  `migration`   VARCHAR(255) NOT NULL,      -- File name
  `batch`       INT NOT NULL DEFAULT 1,     -- Batch number (one run = one batch)
  `executed_at` DATETIME DEFAULT CURRENT_TIMESTAMP,
  `checksum`    VARCHAR(64) DEFAULT NULL,   -- SHA-256 of the file
  UNIQUE KEY `scope_migration` (`scope`, `migration`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

The UNIQUE KEY on (scope, migration) prevents double execution. The checksum detects cases where someone edits an already-executed migration after the fact — the runner reports that as an error.

Idempotency: IF NOT EXISTS everywhere

Migrations must be idempotent. The runner never executes a migration twice, but the DB can still be in intermediate states (e.g. partially migrated after a crash).

Correct idempotency patterns:

-- Create a table
CREATE TABLE IF NOT EXISTS my_articles (
  id         INT AUTO_INCREMENT PRIMARY KEY,
  title      VARCHAR(255) NOT NULL,
  body       TEXT,
  created_at DATETIME DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Add a column
ALTER TABLE my_articles ADD COLUMN IF NOT EXISTS language_short VARCHAR(5) DEFAULT 'de';

-- Create an index
CREATE INDEX IF NOT EXISTS idx_lang ON my_articles (language_short);

-- Drop a column (carefully!)
ALTER TABLE my_articles DROP COLUMN IF EXISTS old_column;

IF NOT EXISTS isn't universally supported

CREATE TABLE IF NOT EXISTS works everywhere. But ALTER TABLE ... ADD COLUMN IF NOT EXISTS only landed in MySQL 8.0.29 / MariaDB 10.3. For older instances: the MigrationRunner ignores MySQL error codes 1050 (Table already exists), 1060 (Duplicate column name), and 1061 (Duplicate key) — so the migration still runs through without IF NOT EXISTS. IF NOT EXISTS remains the cleaner choice whenever the MySQL version supports it.

Plugin migrations

A plugin places its own migrations under _public/extensions/core/backend/{plugin}/migrations/:

_public/extensions/core/backend/menueditor/
├── bootstrap.php
├── layout/
└── migrations/
    └── 001_create_tables.sql

The first migration typically contains every table the plugin needs:

-- 001_create_tables.sql — menueditor plugin

CREATE TABLE IF NOT EXISTS menueditor_menus (
  id          INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  web_id      INT UNSIGNED NOT NULL,
  name        VARCHAR(100) NOT NULL DEFAULT 'Main Menu',
  slug        VARCHAR(100) NOT NULL DEFAULT 'main',
  settings    JSON NULL,
  created_at  DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at  DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  UNIQUE KEY idx_slug (web_id, slug)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS menueditor_items (
  id              INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  menu_id         INT UNSIGNED NOT NULL,
  base_id         INT UNSIGNED NULL,
  language_short  VARCHAR(5) NOT NULL DEFAULT 'de',
  parent_id       INT UNSIGNED NULL DEFAULT NULL,
  sort_order      INT UNSIGNED NOT NULL DEFAULT 0,
  item_type       ENUM('link','page','anchor','label','megamenu') NOT NULL DEFAULT 'link',
  label           VARCHAR(500) NOT NULL DEFAULT '',
  -- ... more columns
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

Real source: _public/extensions/core/backend/menueditor/migrations/001_create_tables.sql.

Later changes ship as new files (002_add_icon_field.sql, 003_add_unique_slug_constraint.sql, …) — never edit a migration that has already run.

Multi-language: base_id + language_short

If a table should be multi-language (recognizable by "multilanguage":"1" on the plugin buttons, see Buttons), it needs two required columns:

CREATE TABLE my_articles (
  id             INT AUTO_INCREMENT PRIMARY KEY,
  base_id        INT NULL,               -- NULL or = id for the base record
  language_short VARCHAR(5) NOT NULL DEFAULT 'de',
  title          VARCHAR(255) NOT NULL,
  body           TEXT,
  -- ...
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

• Base record: base_id IS NULL or base_id = id (the main language)
• Language variant: base_id = {base id}, a different language_short

The core API (backend/item) handles the pattern automatically — an INSERT in the base language sets base_id = new id, an INSERT in another language creates a variant record with base_id set.

More details in the php-backend-check skill in the repo or in the implementation of backend/item.

The MigrationRunner — how it executes

The runner has two entry points:

1. Update Manager UI: /admin/update → "Run migrations" button
2. CLI (ideally inside the deploy script):

php console/bin scheduled-tasks --time-limit=60

Both paths go through MigrationRunner::runAll(). Core steps:

1. acquireLock()           # var/migration.lock with flock(LOCK_EX | LOCK_NB)
2. ensureTable()           # CREATE TABLE IF NOT EXISTS _migrations
3. getNextBatch()          # MAX(batch) + 1
4. For every scope (core, plugins-central, plugin-local):
   - Scan the directory, sort files alphabetically
   - Skip files already executed (per _migrations)
   - Compute SHA-256 of the new file
   - Run the file; on success, register it in _migrations
5. releaseLock()

Lock mechanism: parallel runner calls (e.g. an admin double-clicks) are blocked via flock() on var/migration.lock — only one runner runs at a time.

Checksum protection: if someone edits an already-executed migration, the SHA-256 no longer matches the stored one — the runner reports the error and aborts. Never edit existing migrations; always write a new one.

Rollback

The MigrationRunner has no automatic rollback. Practice: write a new "compensation migration" that reverses the change.

-- 003_add_feature_flag.sql (wrong)
ALTER TABLE users ADD COLUMN feature_x_enabled TINYINT(1) DEFAULT 0;

-- 004_remove_feature_flag.sql (compensation)
ALTER TABLE users DROP COLUMN IF EXISTS feature_x_enabled;

Both files stay in the repo — that keeps the history auditable and lets the runner build new instances correctly.

Plugin activation vs. migration execution

The MigrationRunner runs separately from the plugin install. installPlugin() does call installDatabase(), but that's a legacy hook that only runs the $this->mysqlInstall property (inline SQL in the plugin) — the plugin's migrations/ folder is not touched:

// install_controller.php::installDatabase()
public function installDatabase()
{
    if (!empty($this->mysqlInstall)) {
        multiQuery($this->mysqlInstall);
    }
}

The actual plugin migrations only run on the next global MigrationRunner pass:

1. An admin triggers /admin/update → "Run migrations"
2. Or the CLI: php console/bin scheduled-tasks --time-limit=60

A freshly activated plugin has no tables yet

If you've just activated a plugin with its own migrations/001_create_tables.sql, you still need to trigger the Update Manager or run the CLI runner afterwards — otherwise the DB tables don't exist yet, and API endpoints throw Table doesn't exist. A good deploy workflow runs the MigrationRunner right after plugin activation.

Common issues

Editing a migration after execution

Do that, and the checksum in _migrations no longer matches. The next runner call raises an error and aborts. Fix: UPDATE _migrations SET checksum = '...' WHERE scope = '…' AND migration = '…' or — cleaner — ship the change as a new file.

Reusing a number

Two files named 003_* within the same scope cause undefined ordering (alphabetical by the remainder of the name). On teams, always check which number is free — ideally ls _migrations/core | tail -3 before creating the file.

DROP without a check

DROP TABLE xy aborts if the table is already gone. Prefer DROP TABLE IF EXISTS xy. Same for DROP COLUMN IF EXISTS, DROP INDEX IF EXISTS.

DB snapshot before a large migration

An ALTER TABLE on a production table with many rows (>1M) can run for minutes and block writes. For big changes: deploy outside peak hours, take a DB dump beforehand, test long migrations on staging.

Foreign keys in migrations

The MigrationRunner ignores 1050/1060/1061, but not FK-constraint errors. When a FK points at a table that doesn't exist yet, the migration aborts. Watch the order: parent table in an earlier migration or higher up in the same file.

See also

• Plugin Anatomy — where migrations/ lives inside a plugin folder
• Plugins overview › lifecycle — when migrations run during plugin activation
• Content Constructs — when base_id / language_short are needed
• Example: Hello World — plugin with its own migration from scratch