Schema Management

Manage a MariaDB database schema across its lifecycle with the msm global object of MariaDB Shell, from the first version through releases to deployments that create or upgrade the schema.

MariaDB Schema Management (MSM) manages one database schema as a project on disk. You write the full definition of the schema in a single development script, release it under version numbers, and deploy each release with a generated script that creates the schema on a new server or upgrades it from any earlier release on an existing one.

MSM is included with MariaDB Shell as the built-in msm plugin. It works with plain SQL files that you keep in version control, and it needs a database server only for the deployment.

warning

MSM is a preview feature. msm.info() reports it as PREVIEW and for testing purposes only.

When to Use MSM

Use MSM for a schema that changes over time and is installed on more than one server, for example an application schema that exists on developer machines, in test environments, and in production, each at a different version. A single create script covers a new installation but not the upgrade of an existing one. MSM generates one deployment script per release that handles both cases.

The Lifecycle

 create project ──► develop ──► prepare release ──► write update script ──► generate deployment script ──► deploy
                       ▲                                                                                     │
                       └────────────────────────── next development version ◄──────────────────────────────┘

Create a project. msm.create_new_project_folder() creates the project folder with a development script for version 0.0.1. See Schema Projects.
Develop. You write the complete, current definition of the schema into the sections of the development script development/<schema>_next.sql: tables, views, routines, roles, and REST endpoints. See Developing a Schema.
Prepare a release. msm.prepare_release() takes a snapshot of the development script under a version number. From the second release on, it also creates an empty update script from the previous release to the new one, and it raises the development version for the next round of development.
Write the update script. You add the statements that change existing tables from the previous release to the new one.
Generate the deployment script. msm.generate_deployment_script() combines the release and all update scripts into one script that creates the schema or upgrades it from any earlier release. See Preparing Releases.
Deploy. msm.deploy_schema() runs the deployment script on a server, optionally after taking a backup of the existing schema. See Deploying Schemas.

The scripts are divided into numbered MSM sections. The section that a statement is in decides how the deployment script uses it, for example whether it runs once when a table is created or on every deployment. See MSM Sections.

Requirements

A MariaDB Shell release package, installed as described in Installation. The release packages include the msm plugin; a MariaDB Shell built from source doesn't.
For deployments, a MariaDB server and an account that can create the schema and its objects. See Deploying Schemas.

Quick Start

The following example creates a project for a schema named shop, releases a first version with one table, and deploys it. Start MariaDB Shell connected to a server and switch to Python mode:

mariadb-shell mariadb://root@localhost:3306 --py

Turn off the interactive prompts, so that the functions return their results instead of printing them:

shell.options.useWizards = False

Create the project in the existing directory ~/projects:

project = msm.create_new_project_folder("shop", "~/projects", "Example Corp", license="MIT")

Add a table to section 140 of the development script, and set the version to release:

dev_script = project + "/development/shop_next.sql"
msm.set.section_sql_content(dev_script, "140", """
CREATE TABLE `shop`.`product`(
    `id` INT AUTO_INCREMENT PRIMARY KEY,
    `name` VARCHAR(100) NOT NULL
);""")
msm.set.development_version(schema_project_path=project, version="1.0.0")

Release version 1.0.0, generate its deployment script, and deploy it:

msm.prepare_release(schema_project_path=project, version="1.0.0", next_version="1.1.0")
msm.generate_deployment_script(schema_project_path=project, version="1.0.0")
msm.deploy_schema(schema_project_path=project)

Deployment of `shop` version 1.0.0 completed successfully.

The schema shop now exists on the server with the table product and the view msm_schema_version, which reports the deployed version.

The msm Global Object

The plugin adds the msm global object to Python mode, with the sub-objects msm.get and msm.set. All functions are also available through command line integration, for example mariadb-shell -- msm prepare-release. See Function Reference.

To show the built-in help, run \? msm in Python mode, or mariadb-shell -- msm --help on the command line.

Interactive and Non-Interactive Use

The functions behave differently depending on the useWizards shell option:

Interactive (useWizards is on, the default in an interactive session, and on the command line). A function prompts for missing values, such as a version number, and prints its result instead of returning it. For example, msm.get.released_versions() prints one version per line.
Non-interactive (useWizards is off). A function doesn't prompt, returns its result, and raises an error when a required value is missing. Use this mode in scripts.

Set the option for the current session with shell.options.useWizards = False, or start MariaDB Shell with --no-wizard.