Database migrations and schema updates made easy
Easy to use PHP package for updating the database schema of your application and migrate data between versions.
composer req aimeos/upscheme
Table of contents
Migrations are like version control for your database. They allow you to get the exact same state in every installation. Using Upscheme, you get:
Here's an example of a table definition that you can adapt whenever your table layout must change. Then, Upscheme will automatically add and modify existing columns and table properties (but don't delete anything for safety reasons):
$this->db()->table( 'test', function( $t ) {
$t->engine = 'InnoDB';
$t->id();
$t->string( 'domain', 32 );
$t->string( 'code', 64 )->opt( 'charset', 'binary', ['mariadb', 'mysql'] );
$t->string( 'label', 255 );
$t->int( 'pos' )->default( 0 );
$t->smallint( 'status' );
$t->default();
$t->unique( ['domain', 'code'] );
$t->index( ['status', 'pos'] );
} );
For upgrading relational database schemas, two packages are currently used most often: Doctrine DBAL and Doctrine migrations. While Doctrine DBAL does a good job in abstracting the differences of several database implementations, it's API requires writing a lot of code. Doctrine migrations on the other site has some drawbacks which make it hard to use in all applications that support 3rd party extensions.
The API of DBAL is very verbose and you need to write lots of code even for simple things. Upscheme uses Doctrine DBAL to offer an easy to use API for upgrading the database schema of your application with minimal code. For the Upscheme example above, these lines of code are the equivalent for DBAL in a migration:
…
Doctrine Migration relies on migration classes that are named by the time they have been created to ensure a certain order. Furthermore, it stores which migrations has been executed in a table of your database. There are three major problems that arise from that:
down()If your application supports 3rd party extensions, these extensions are likely to
add columns to existing tables and migrate data themselves. As there's no way to
define dependencies between migrations, it can get almost impossible to run
migrations in an application with several 3rd party extensions without conflicts.
To avoid that, Upscheme offers easy to use before() and after() methods in
each migration task where the tasks can define its dependencies to other tasks.
Because Doctrine Migrations uses a database table to record which migration already has been executed, these records can get easily out of sync in case of problems. Contrary, Upscheme only relies on the actual schema so it's possible to upgrade from any state, regardless of what has happend before.
Doctrine Migrations also supports the reverse operations in down() methods so
you can roll back migrations which Upscheme does not. Experience has shown that
it's often impossible to roll back migrations, e.g. after adding a new colum,
migrating the data of an existing column and dropping the old column afterwards.
If the migration of the data was lossy, you can't recreate the same state in a
down() method. The same is the case if you've dropped a table. Thus, Upscheme
only offers scheme upgrading but no downgrading to avoid implicit data loss.
Upscheme uses Doctrine DBAL for abstracting from different database server implementations. DBAL supports all major relationsal database management systems (RDBMS) but with a different level of support for the available features:
Good support:
Limited support:
After you've installed the aimeos/upscheme package using composer, you can use
the Up class to execute your migration tasks:
$config = [
'driver' => 'pdo_mysql',
'host' => '127.0.0.1',
'dbname' => '<database>',
'user' => '<dbuser>',
'password' => '<secret>'
];
\Aimeos\Upscheme\Up::use( $config, 'src/migrations' )->up();
The Up::use() method requires two parameters: The database configuration and
the path(s) to the migration tasks. For the config, the array keys and the values
for driver must be supported by Doctrine DBAL. Available drivers are:
Some databases require different parameters, most notable SQLite and Oracle:
SQLite:
$config = [
'driver' => 'pdo_sqlite',
'path' => 'path/to/file.sq3'
];
Oracle:
$config = [
'driver' => 'pdo_oci',
'host' => '<host or IP>',
'dbname' => '<SID or service name (Oracle 18+)>',
'service' => true, // for Oracle 18+ only
'user' => '<dbuser>',
'password' => '<secret>'
];
If you didn't use Doctrine DBAL before, your database configuration may have a different structure and/or use different values for the database type. Upscheme allows you to register a custom method that transforms your configration into valid DBAL settings, e.g.:
\Aimeos\Upscheme\Up::macro( 'connect', function( array $cfg ) {
return \Doctrine\DBAL\DriverManager::getConnection( [
'driver' => $cfg['adapter'],
'host' => $cfg['host'],
'dbname' => $cfg['database'],
'user' => $cfg['username'],
'password' => $cfg['password']
] );
} );
Upscheme also supports several database connections which you can distinguish by their key name:
$config = [
'db' => [
'driver' => 'pdo_mysql',
'host' => '127.0.0.1',
'dbname' => '<database>',
'user' => '<dbuser>',
'password' => '<secret>'
],
'temp' => [
'driver' => 'pdo_sqlite',
'path' => '/tmp/mydb.sqlite'
]
];
\Aimeos\Upscheme\Up::use( $config, 'src/migrations' )->up();
Of course, you can also pass several migration paths to the Up class:
\Aimeos\Upscheme\Up::use( $config, ['src/migrations', 'ext/migrations'] )->up();
To enable (debugging) output, use the verbose() method:
\Aimeos\Upscheme\Up::use( $config, 'src/migrations' )->verbose()->up(); // most important only
\Aimeos\Upscheme\Up::use( $config, 'src/migrations' )->verbose( 'vv' )->up(); // more verbose
\Aimeos\Upscheme\Up::use( $config, 'src/migrations' )->verbose( 'vvv' )->up(); // debugging
A migration task only requires implementing the up() method and must be stored
in one of the directories passed to the Up class:
<?php
namespace Aimeos\Upscheme\Task;
use Aimeos\Upscheme\Schema\Table;
return new class( $this ) extends Base {
public function up()
{
$this->db()->table( 'test', function( Table $t ) {
$t->id();
$t->string( 'label' );
$t->bool( 'status' );
} );
}
};
In your PHP file, always include the namespace statement first. The use
statement is optional and only needed as shortcut for the type hint for the
closure function argument. Your class also has to extend from the "Base" task
class or implement the "Iface" task interface.
Alternatively to anonymous classes, you can use named classes for migration tasks:
<?php
namespace Aimeos\Upscheme\Task;
use Aimeos\Upscheme\Schema\Table;
class TestTable extends Base
{
public function up()
{
$this->db()->table( 'test', function( Table $t ) {
$t->id();
$t->string( 'label' );
$t->bool( 'status' );
} );
}
}
The file your class is stored in must have the same name (case sensitive) as
the class itself and the .php suffix, e.g:
class TestTable -> TestTable.php
There's no strict convention how to name migration task files. You can either name them by what they do (e.g. "CreateTestTable.php"), what they operate on (e.g. "TestTable.php") or even use a timestamp (e.g. "20201231_Test.php").
If the tasks doesn't contain dependencies, they are sorted and executed in alphabethical order according to the file name and the sorting would be:
20201231_Test.php
CreateTestTable.php
TestTable.php
To specify dependencies to other migration tasks, use the after() and before()
methods. Your task is executed after the tasks returned by after() and before
the tasks returned by before():
return new class( $this ) extends Base {
public function after() : array
{
return ['CreateRefTable'];
}
public function before() : array
{
return ['InsertTestData'];
}
}
The task names are the file names of the tasks without the .php suffix. If the
example migration is stored in the file TestTable.php, the order of execution
would be:
CreateRefTable.php -> TestTable.php -> InsertTestData.php
To output messages in your migration task use the info() method:
$this->info( 'some message' );
$this->info( 'more verbose message', 'vv' );
$this->info( 'very verbose debug message', 'vvv' );
Th
No open issues yet, or sync has not completed.