Mariadb Schema Management

MariaDB Schema Management (MSM) — Overview

The MariaDB Schema Management (MSM) plugin manages a database schema across its whole lifecycle — first creation, every versioned release, and in-place upgrades — as a schema project on disk, driven by the msm.* tools of the mariadb-shell MCP server. Prefer MSM over a one-off create script whenever a schema will evolve and be deployed to servers that may hold an older version. (For a single, non-versioned create script, use mariadb-schema-create-script instead.)

Assume MariaDB 11.8 if not told otherwise. This skill is the map; read the focused sub-skill for the step you are on.

The lifecycle

create project + author initial schema   →  mariadb-schema-management-create
        │
        ▼
develop the next version in _next.sql     →  mariadb-schema-management-develop
        │
        ▼
prepare release vX  +  FILL update script  →  mariadb-schema-management-release
   +  generate deployment script
        │
        ▼
deploy vX (create fresh OR upgrade)        →  mariadb-schema-management-deploy
        │
        └──────────────► back to develop (dev version already bumped)

The one rule everyone gets wrong: prepare_release creates an empty update script (releases/updates/<schema>_<prev>_to_<vX>.sql). You MUST fill its migration sections before generating the deployment script — the deployment script embeds them to upgrade existing installs. See mariadb-schema-management-release.

Project layout

msm.create_project scaffolds:

<schema>.msm.project/
├── msm.project.json     # schemaName, license, copyrightHolder, schemaDependencies
├── development/
│   ├── <schema>_next.sql            # the working script; version lives in section 910
│   └── sections/                    # optional SOURCE breakout files (see -develop)
└── releases/
    ├── versions/    <schema>_<x.y.z>.sql              # full CREATE snapshot per release
    ├── updates/     <schema>_<x.y.z>_to_<a.b.c>.sql   # per-release migration (fill by hand)
    └── deployment/  <schema>_deployment_<x.y.z>.sql   # generated; create-or-upgrade; never edit

Versions use semantic versioning (major.minor.patch). The development file carries the _next suffix (no version in the name); the version is stored in the msm_schema_version view defined in section 910.

The MSM section model (shared reference)

MSM scripts are plain SQL split into numbered sections marked by -- ##### ... MSM Section NNN: Title banners. The section a statement lives in determines how the deployment script treats it. Always edit sections with msm.get_sql_content_from_section / msm.set_section_sql_content (by file_path + section_id) so the banners are never corrupted.

Create / version script (author with -create, evolve with -develop):

Update script (fill during -release):

Idempotent vs. non-idempotent — the core distinction:

• Non-idempotent (140 create / 240 update): TABLE structure and data — state that cannot be trivially re-run, so it is version-guarded.
• Idempotent (150 create / 250 update): everything else — always written with CREATE OR REPLACE / DROP ... IF EXISTS so re-running is safe.

Delimiter/placement rule (why the split exists): in the generated deployment script, sections 140, 240, 170, 270 become the body of a stored procedure — write them as plain ;-terminated statements, no DELIMITER, use dynamic SQL for conditional DDL. Sections 130, 150, 230, 250, 190, 290 are emitted at top level and use DELIMITER %% for routine bodies.

The msm.* MCP tools

• Project: msm.create_project, msm.get_project_information.
• Sections: msm.get_sql_content_from_section, msm.set_section_sql_content.
• Versions: msm.set_development_version, msm.get_released_versions, msm.get_last_released_version, msm.get_last_deployment_version, msm.get_deployment_script_versions.
• Release / deploy: msm.prepare_release, msm.generate_deployment_script, msm.deploy_schema (the last needs an open db.connect connection).

See Also

• mariadb-schema-management-create — scaffold the project and author the initial schema.
• mariadb-schema-management-develop — evolve _next.sql for the next version, incl. SOURCE breakout files.
• mariadb-schema-management-release — prepare a release, fill the update script, generate the deployment script.
• mariadb-schema-management-deploy — create or upgrade a schema on a live server.
• mariadb-schema-create-script — the standalone (non-MSM) single create script.