[DRAFT] RFC Database Migrations (SilverStripe 5.0).

[tractorcow]

Introduction

dev/build is a great system for doing immediately synchronisation of database schema with code. However, due to this being an immediate update, there is little flexibility for developers to introduce their own custom migrations to support changes to schema.

Most modern ORM (E.g. rails, magento) have some form of schema migration toolkit, so this would simply be bringing SilverStripe up to that standard. :)

Example applications

A list of possible examples of schema updates that would require manual migrations:

• Adding versioned to a previously unversioned record (initialise _Live table with current stage records)
• Adding a new column to an existing table (bootstrap with a calculated value)
• Adding a table to a dataobject which did not previously have one (bootstrap with a calculated / default value).
• Migrating from SS3 to SS4
• Arbitrary class renames (or namespaces moves)
• Module splitting
• Creating default database records for new classes (or updated)

Solution requirements

I suggest that any solution should meet the following requirements:

• Schema versions should be able to be versioned, so that multiple versions (e.g. different schemas on live / uat environments) can be tracked. The version id of the schema should be the source of truth to determine which changes any schema should be able to be made. This version could be either date, incrementing id, or some other value, as long as it is sortable and distinct.
• All schema updates should generate metadata that can be reviewed by a developer prior to execution - there is no committment to execute a schema build in order to review what would be changed.
• It should still be possible to do dev/build (detect and generate all schema changes, plus immediately execute them). This should not be the only (or default) path to schema updates.
• Schema generation should be extensible - Modules such as versioned, fluent, etc... should be able to "provide" each schema delta automatically.
• Each schema version should have a record of the full schema at that point in time (in order to facilitate schema diff).
  • This requirement could be compromised as long as it's possible to calculate the final schema for this version (e.g. take the last / initial schema, and calculate all diffs in memory).
• All schema mutations are human readable, and expected to be able to be modified. However, all generated schema should also be able to be immediately executed without needing changes.

Nice to haves:

• Migrations should be two directional - E.g. rollback of a DB to an older version should be possible. Also this would be required to support failed builds (e.g. build error results in automatic rollback).
• Schema generation should be reasonably forgiving - If a delta initiates a create-index (for example), it should not error if the index already exists (at least, in the expected format). I.e. CREATE IF NOT EXISTS over CREATE and so on. :P
• Build this option as a module (can replace dev/build action, other than that no real core changes needed)

Options:

• "stepped" mutations; E.g. odd number mutations = automatic generated (schema updates) , even number mutations = manual updates provided by devs (data migrations).

Example solution

Tasks:

• dev/build/migrate task - Takes current schema, looks at db classes, builds a new migration based on the diff (or no-op if there is no diff).
• dev/build/update - Goes up to the top level (migrates schema to last version generated)
• dev/build/down - goes down one level
• dev/build/up - migrates up one level
• dev/build - Same as dev/build/migrate and dev/build/update

Files:

• new 'schema" top level folder contains all schema migrations.
• Initial "base" schema is in a folder "0"

References:

• ORM Schema generator needs a way of specifying initialising records for a new table silverstripe/silverstripe-framework#8908
• Ability to perform atomic database migrations silverstripe/silverstripe-framework#3868