PHPackages                             aimeos/upscheme - PHPackages - PHPackages  [Skip to content](#main-content)[PHPackages](/)[Directory](/)[Categories](/categories)[Trending](/trending)[Leaderboard](/leaderboard)[Changelog](/changelog)[Analyze](/analyze)[Collections](/collections)[Log in](/login)[Sign up](/register)

1. [Directory](/)
2. /
3. [Database & ORM](/categories/database)
4. /
5. aimeos/upscheme

ActiveLibrary[Database & ORM](/categories/database)

aimeos/upscheme
===============

Database schema upgrades made easy

1.2.0(1y ago)2.6k135.7k—0.7%4[2 issues](https://github.com/aimeos/upscheme/issues)1LGPL-3.0-or-laterPHPPHP ~7.4||~8.0

Since May 21Pushed 4mo ago6 watchersCompare

[ Source](https://github.com/aimeos/upscheme)[ Packagist](https://packagist.org/packages/aimeos/upscheme)[ RSS](/packages/aimeos-upscheme/feed)WikiDiscussions master Synced 1mo ago

READMEChangelogDependencies (4)Versions (23)Used By (1)

[![Build Status](https://camo.githubusercontent.com/15b8329c3a8875e92f7f412ab1af92290fb51aaeb744dcf8bd93f49626849eef/68747470733a2f2f636972636c6563692e636f6d2f67682f61696d656f732f7570736368656d652e7376673f7374796c653d736869656c64)](https://circleci.com/gh/aimeos/upscheme)[![Coverage Status](https://camo.githubusercontent.com/95172d5e8243b5e995dcca33201db6ad33fbf5709cf5a517ceb0cd3410d872df/68747470733a2f2f636f766572616c6c732e696f2f7265706f732f6769746875622f61696d656f732f7570736368656d652f62616467652e737667)](https://coveralls.io/github/aimeos/upscheme)[![License](https://camo.githubusercontent.com/5f68b67296214bd4cca610b55f93d69ea79aeacec2c6393cf3e9b60c2f98bed1/68747470733a2f2f706f7365722e707567782e6f72672f61696d656f732f7570736368656d652f6c6963656e73652e737667)](https://packagist.org/packages/aimeos/upscheme)

Upscheme: Database schema updates made easy
===========================================

[](#upscheme-database-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**

- [Why Upscheme](#why-upscheme)
- [Database support](#database-support)
- [Integrating Upscheme](#integrating-upscheme)
- [Writing migrations](#writing-migrations)
    - [Naming](#naming-migrations)
    - [Dependencies](#dependencies)
    - [Messages](#messages)
    - [Schemas](#schemas)
    - [Generate from database](#generate-from-database)
- [Database](#database)
    - [Accessing objects](#accessing-objects)
    - [Checking existence](#checking-existence)
    - [Renaming objects](#renaming-objects)
    - [Removing objects](#removing-objects)
    - [Query/modify table rows](#querymodify-table-rows)
    - [Executing custom SQL](#executing-custom-sql)
    - [Database methods](#database-methods)
- [Tables](#tables)
    - [Creating tables](#creating-tables)
    - [Setting table options](#setting-table-options)
    - [Checking table existence](#checking-table-existence)
    - [Changing tables](#changing-tables)
    - [Renaming tables](#renaming-tables)
    - [Dropping tables](#dropping-tables)
    - [Table methods](#table-methods)
- [Columns](#columns)
    - [Adding columns](#adding-columns)
    - [Available column types](#available-column-types)
    - [Column modifiers](#column-modifiers)
    - [Checking column existence](#checking-column-existence)
    - [Changing columns](#changing-columns)
    - [Renaming columns](#renaming-columns)
    - [Dropping columns](#dropping-columns)
    - [Column methods](#column-methods)
- [Foreign keys](#foreign-keys)
    - [Creating foreign keys](#creating-foreign-keys)
    - [Checking foreign key existence](#checking-foreign-key-existence)
    - [Dropping foreign keys](#dropping-foreign-keys)
    - [Foreign key methods](#foreign-key-methods)
- [Sequences](#sequences)
    - [Adding sequences](#adding-sequences)
    - [Checking sequence existence](#checking-sequence-existence)
    - [Dropping sequences](#dropping-sequences)
    - [Sequence methods](#sequence-methods)
- [Indexes](#indexes)
    - [Adding indexes](#adding-indexes)
    - [Checking index existence](#checking-index-existence)
    - [Renaming indexes](#renaming-indexes)
    - [Dropping indexes](#dropping-indexes)
    - [Custom index naming](#custom-index-naming)
- [Customizing Upscheme](#customizing-upscheme)
    - [Adding custom methods](#adding-custom-methods)
    - [Implementing custom columns](#implementing-custom-columns)
- [Upgrade Upscheme](#upgrade-upscheme)

Why Upscheme
------------

[](#why-upscheme)

Migrations are like version control for your database. They allow you to get the exact same state in every installation. Using Upscheme, you get:

- one place for defining tables, columns, indexes, etc. easily
- upgrades from any state in between to the expected schema
- consistent, reliable and hassle-free schema upgrades
- minimal code required for writing migrations
- perfect solution for continuous deployments
- best package for cloud-based PHP applications

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.

### Doctrine DBAL drawbacks

[](#doctrine-dbal-drawbacks)

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:

```
$dbalManager = $conn->createSchemaManager();
$from = $manager->createSchema();
$to = $manager->createSchema();

if( $to->hasTable( 'test' ) ) {
	$table = $to->getTable( 'test' );
} else {
	$table = $to->createTable( 'test' );
}

$table->addOption( 'engine', 'InnoDB' );

$table->addColumn( 'id', 'integer', ['autoincrement' => true] );
$table->addColumn( 'domain', 'string', ['length' => 32] );

$platform = $conn->getDatabasePlatform();
if( $platform instanceof \Doctrine\DBAL\Platform\MySQLPlatform
	|| $platform instanceof \Doctrine\DBAL\Platform\MariaDBPlatform
) {
	$table->addColumn( 'code', 'string', ['length' => 64, 'customSchemaOptions' => ['charset' => 'binary']] );
} else {
	$table->addColumn( 'code', 'string', ['length' => 64]] );
}

$table->addColumn( 'label', 'string', ['length' => 255] );
$table->addColumn( 'pos', 'integer', ['default' => 0] );
$table->addColumn( 'status', 'smallint', [] );
$table->addColumn( 'mtime', 'datetime', [] );
$table->addColumn( 'ctime', 'datetime', [] );
$table->addColumn( 'editor', 'string', ['length' => 255] );

$table->setPrimaryKey( ['id'] );
$table->addUniqueIndex( ['domain', 'code'] );
$table->addIndex( ['status', 'pos'] );

foreach( $from->getMigrateToSql( $to, $conn->getDatabasePlatform() ) as $sql ) {
	$conn->executeStatement( $sql );
}
```

### Doctrine Migration drawbacks

[](#doctrine-migration-drawbacks)

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:

- dependencies between 3rd party extensions
- tracking changes is out of sync
- data loss when using `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.

Database support
----------------

[](#database-support)

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:**

- MySQL
- MariaDB
- PostgreSQL
- SQLite
- SQL server

**Limited support:**

- DB2
- Oracle
- SQL Anywhere

Integrating Upscheme
--------------------

[](#integrating-upscheme)

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' => '',
	'user' => '',
	'password' => ''
];

\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:

- pdo\_mysql
- pdo\_pgsql
- pdo\_sqlite
- pdo\_sqlsrv
- pdo\_oci
- ibm\_db2
- mysqli
- oci8
- sqlanywhere
- sqlsrv

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' => '',
	'dbname' => '',
	'service' => true, // for Oracle 18+ only
	'user' => '',
	'password' => ''
];
```

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' => '',
		'user' => '',
		'password' => ''
	],
	'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
```

Writing migrations
------------------

[](#writing-migrations)

A migration task only requires implementing the `up()` method and must be stored in one of the directories passed to the `Up` class:

```