cakephp/phinx

PHP Database Migrations for Everyone

Phinx: Simple PHP Database Migrations

Intro

Phinx makes it ridiculously easy to manage the database migrations for your PHP app. In less than 5 minutes, you can install Phinx and create your first database migration. Phinx is just about migrations without all the bloat of a database ORM system or framework.

Check out book.cakephp.org/phinx (EN, ZH) for the comprehensive documentation.

Features

• Write database migrations using database agnostic PHP code.
• Migrate up and down.
• Migrate on deployment.
• Seed data after database creation.
• Get going in less than 5 minutes.
• Stop worrying about the state of your database.
• Take advantage of SCM features such as branching.
• Integrate with any app.

Supported Adapters

Phinx natively supports the following database adapters:

• MySQL
• PostgreSQL
• SQLite
• Microsoft SQL Server

Install & Run

See version and branch overview for branch and PHP compatibility.

Composer

The fastest way to install Phinx is to add it to your project using Composer (https://getcomposer.org/).

1. Install Composer:

   curl -sS https://getcomposer.org/installer | php
   

2. Require Phinx as a dependency using Composer:

   php composer.phar require robmorgan/phinx
   

3. Install Phinx:

   php composer.phar install
   

4. Execute Phinx:

   php vendor/bin/phinx
   

As a Phar

You can also use the Box application to build Phinx as a Phar archive (https://box-project.github.io/box2/).

1. Clone Phinx from GitHub

   git clone https://github.com/cakephp/phinx.git
   cd phinx
   

2. Install Composer

   curl -s https://getcomposer.org/installer | php
   

3. Install the Phinx dependencies

   php composer.phar install
   

4. Install Box:

   curl -LSs https://box-project.github.io/box2/installer.php | php
   

5. Create a Phar archive

   php box.phar build
   

Documentation

Check out https://book.cakephp.org/phinx for the comprehensive documentation.

Other translations include:

• Chinese (Maintained by @tsy12321)

Contributing

Please read the CONTRIBUTING document.

News & Updates

Follow @CakePHP on Twitter to stay up to date.

Limitations

PostgreSQL

• Not able to set a unique constraint on a table (#1026).

Misc

Version History

Please read the release notes.

License

(The MIT license)

Copyright (c) 2017 Rob Morgan

Permission is hereby granted, free of charge, to any person obtaining a copy of this software and associated documentation files (the "Software"), to deal in the Software without restriction, including without limitation the rights to use, copy, modify, merge, publish, distribute, sublicense, and/or sell copies of the Software, and to permit persons to whom the Software is furnished to do so, subject to the following conditions:

The above copyright notice and this permission notice shall be included in all copies or substantial portions of the Software.

THE SOFTWARE IS PROVIDED "AS IS", WITHOUT WARRANTY OF ANY KIND, EXPRESS OR IMPLIED, INCLUDING BUT NOT LIMITED TO THE WARRANTIES OF MERCHANTABILITY, FITNESS FOR A PARTICULAR PURPOSE AND NONINFRINGEMENT. IN NO EVENT SHALL THE AUTHORS OR COPYRIGHT HOLDERS BE LIABLE FOR ANY CLAIM, DAMAGES OR OTHER LIABILITY, WHETHER IN AN ACTION OF CONTRACT, TORT OR OTHERWISE, ARISING FROM, OUT OF OR IN CONNECTION WITH THE SOFTWARE OR THE USE OR OTHER DEALINGS IN THE SOFTWARE.

phinx's Issues

Wrap migration execution in a transaction

Wrap the migration execution in a transaction if supported by the adapter. This will require a method in the adapter like hasTransactions

Pass direction to change() method

Hi

When running non-reversible functions in the change method, like drop column, you need to be aware of which way the change is going, up or down. For example, a down operation can't reverse a drop column (how would it know what to put in back in?), thus this needs to be conditional based on the direction.

Simple change to Manager/Environment.php: 103:

        if ($direction == MigrationInterface::DOWN) {
            // Create an instance of the ProxyAdapter so we can record all
            // of the migration commands for reverse playback
            $proxyAdapter = new ProxyAdapter($this->getAdapter(), $this->getOutput());
            $migration->setAdapter($proxyAdapter);
            $migration->change($direction);
            $proxyAdapter->executeInvertedCommands();
            $migration->setAdapter($this->getAdapter());
        } else {
            $migration->change($direction);
        }

Oli

A Rails-like 'change' method to describe schema changes

Along-side both up() & down() migration methods, we introduce a change() method. This method can describe a schema change in code. Phinx can automatically migrate up/down based on the description. Rails calls this 'reversible migrations'.

Remove options parameter from AdapterInterface::dropIndex()

It's not used. I think I copied it from Rails.

Phinx cmd doesn't work on all partitions in Windows

I have Phinx installed trough composer in C:\PHPtools, so I have in my path C:\PHPtools\vendor\bin.
I can use Phinx correctly on my C: drive, but as sun as I want to work on drive D: I got problem.

This is cmd output:

The system cannot find the path specified.
Could not open input file: D:\projects\test_app\app\migrations\phinx

I'm interested in using my tools system wide without installing it in projects. Any solution?

Running migrate for the first time without any migrations should not give php warnings with stack trace

When running phinx migrate -e development without any new migrations and no previous migrations, the console outputs a warning together with a strack trace:

Phinx by Rob Morgan. version 0.2.0
[...]
Warning: max(): Array must contain at least one element in
[...]\vendor\robmorgan\phinx\src\Phinx\Migration\Manager.php on line 136
[Stack trace here, i don't think it is needed to fix this issue]

All Done. Took 0.2778s

Please remove the warning.

Issue with running a migration

Hi,

This might be major PEBCAK but I've got an issue with Phinx whenever I try to run a migration or setup. I'm using MAMP Pro and PHP 5.4.3 (with the PHP path set to use MAMP"s version of PHP).

[11-Mar-2013 21:29:46 UTC] PHP Notice: Undefined property: Phinx\Db\Adapter\MysqlAdapter::$output in /Users/anthony/Sites/l.residen.se/fuel/app/vendor/robmorgan/phinx/src/Phinx/Db/Adapter/PdoAdapter.php on line 112
[11-Mar-2013 21:29:46 UTC] PHP Fatal error: Call to a member function writeln() on a non-object in /Users/anthony/Sites/l.residen.se/fuel/app/vendor/robmorgan/phinx/src/Phinx/Db/Adapter/PdoAdapter.php on line 186

If I create a phinxlog table in the database I specify for development I get this error in the console....

PDOException]
SQLSTATE[42S22]: Column not found: 1054 Unknown column 'version' in 'order
clause'

My MySQL connection details appear to be being set properly based on the above. Any idea what might be causing this?

Migration Classes will use PHP 5.4 Traits to Mix in Db Methods

Once PHP 5.4 becomes mainstream, let's use Traits to Mix in the Db Methods.

Note: This would require 5.4 to gain adoption on the server as well, since Phinx runs both locally & remotely.

Phinx should support PHP as a configuration file parser

Phinx should support loading configuration options from a PHP file as well as from yaml.

Primary scenario:
I use PaaS providers such as Pagodabox, Dotcloud, Appfog, etc. I'd like to use Phinx to manage database versioning during deployments. However, most providers provide database credentials in the form of environment variables. These are easily read in native PHP, but cannot be accessed in yaml without writing a shim that outputs credentials to a .yml file (which negates the point of the env variables). Up until recently I could work around this as the Symfony yaml parser actually supported reading native PHP files through Yaml::parse() as long as they returned an array. However, that support is now deprecated and will be removed in future versions.

Secondary scenario:
Most frameworks provide a single settings file to store database credentials and environments. If Phinx can support reading in configuration options through a PHP file, it makes it much easier to create a lightweight, app-specific shim that allows me to maintain database credentials in a single location rather than in both the framework settings file and in phinx.yml.

Add Composer support

Allow Phinx to be installed via composer and add dependencies to the composer.json file.

Index declarations are not separated with comma when creating multiple indexes

When creating table with more the one indexes at once

$this->table('test_table')
            ->addColumn('test_id1', 'integer')
            ->addColumn('test_id2', 'integer')
            ->addIndex('test_id1')
            ->addIndex('test_id2')
            ->save();

KEY definitions are not separated by comma in final SQL statetement:

CREATE TABLE `test_table`
(`id` INT(11) NOT NULL AUTO_INCREMENT,
`test_id1` INT(11) NOT NULL,
`test_id2` INT(11) NOT NULL,
PRIMARY KEY (`id`), 
KEY (test_id1)
KEY (test_id2)
) ENGINE = InnoDB;

I've forked phinx and working on this issue now, so I hope there will be pull request soon :)

The status command should show a nicer message when no migrations are available

The status command should show a nicer message when no migrations are available. At the moment it just lists an empty table, but how about a message like 'Phinx could not find any migrations, try using the create command to make one`.

Phinx fails to migrate when default_migration_table is not set to the default 'phinxlog'

As of 0.2.1, we can't seem to run a migration with a default_migration_table set to something other than 'phinxlog'. If its left as 'phinxlog' the migration runs successfully.

My phinx.yml is set to:

default_migration_table: phinxlog_api_schools

Here's the resulting error trying to migrate to any environment:

Phinx by Rob Morgan. version 0.2.1

using config ./phinx.yml
using migration path /var/www/api/api-schools/migrations
using environment local
using adapter mysql
using database removing_db_name
PHP Notice:  Undefined property: Phinx\Db\Adapter\MysqlAdapter::$output in /var/www/api/api-schools/vendor/robmorgan/phinx/src/Phinx/Db/Adapter/PdoAdapter.php on line 112
PHP Stack trace:
PHP   1. {main}() /var/www/api/api-schools/vendor/robmorgan/phinx/bin/phinx:0
PHP   2. Symfony\Component\Console\Application->run() /var/www/api/api-schools/vendor/robmorgan/phinx/bin/phinx:54
PHP   3. Phinx\Console\PhinxApplication->doRun() /var/www/api/api-schools/vendor/symfony/console/Symfony/Component/Console/Application.php:106
PHP   4. Symfony\Component\Console\Application->doRun() /var/www/api/api-schools/vendor/robmorgan/phinx/src/Phinx/Console/PhinxApplication.php:81
PHP   5. Symfony\Component\Console\Command\Command->run() /var/www/api/api-schools/vendor/symfony/console/Symfony/Component/Console/Application.php:193
PHP   6. Phinx\Console\Command\Migrate->execute() /var/www/api/api-schools/vendor/symfony/console/Symfony/Component/Console/Command/Command.php:240
PHP   7. Phinx\Migration\Manager->migrate() /var/www/api/api-schools/vendor/robmorgan/phinx/src/Phinx/Console/Command/Migrate.php:94
PHP   8. Phinx\Migration\Manager\Environment->getVersions() /var/www/api/api-schools/vendor/robmorgan/phinx/src/Phinx/Migration/Manager.php:137
PHP   9. Phinx\Db\Adapter\PdoAdapter->getVersions() /var/www/api/api-schools/vendor/robmorgan/phinx/src/Phinx/Migration/Manager/Environment.php:227
PHP  10. Phinx\Db\Adapter\PdoAdapter->fetchAll() /var/www/api/api-schools/vendor/robmorgan/phinx/src/Phinx/Db/Adapter/PdoAdapter.php:248
PHP  11. Phinx\Db\Adapter\PdoAdapter->query() /var/www/api/api-schools/vendor/robmorgan/phinx/src/Phinx/Db/Adapter/PdoAdapter.php:234
PHP  12. Phinx\Db\Adapter\PdoAdapter->getConnection() /var/www/api/api-schools/vendor/robmorgan/phinx/src/Phinx/Db/Adapter/PdoAdapter.php:216
PHP  13. Phinx\Db\Adapter\MysqlAdapter->connect() /var/www/api/api-schools/vendor/robmorgan/phinx/src/Phinx/Db/Adapter/PdoAdapter.php:157
PHP  14. Phinx\Db\Adapter\PdoAdapter->createSchemaTable() /var/www/api/api-schools/vendor/robmorgan/phinx/src/Phinx/Db/Adapter/MysqlAdapter.php:79
PHP  15. Phinx\Db\Table->save() /var/www/api/api-schools/vendor/robmorgan/phinx/src/Phinx/Db/Adapter/PdoAdapter.php:333
PHP  16. Phinx\Db\Table->create() /var/www/api/api-schools/vendor/robmorgan/phinx/src/Phinx/Db/Table.php:556
PHP  17. Phinx\Db\Adapter\MysqlAdapter->createTable() /var/www/api/api-schools/vendor/robmorgan/phinx/src/Phinx/Db/Table.php:513
PHP  18. Phinx\Db\Adapter\PdoAdapter->writeCommand() /var/www/api/api-schools/vendor/robmorgan/phinx/src/Phinx/Db/Adapter/MysqlAdapter.php:238
PHP  19. Phinx\Db\Adapter\PdoAdapter->getOutput() /var/www/api/api-schools/vendor/robmorgan/phinx/src/Phinx/Db/Adapter/PdoAdapter.php:186
PHP Fatal error:  Call to a member function writeln() on a non-object in /var/www/api/api-schools/vendor/robmorgan/phinx/src/Phinx/Db/Adapter/PdoAdapter.php on line 186
PHP Stack trace:
PHP   1. {main}() /var/www/api/api-schools/vendor/robmorgan/phinx/bin/phinx:0
PHP   2. Symfony\Component\Console\Application->run() /var/www/api/api-schools/vendor/robmorgan/phinx/bin/phinx:54
PHP   3. Phinx\Console\PhinxApplication->doRun() /var/www/api/api-schools/vendor/symfony/console/Symfony/Component/Console/Application.php:106
PHP   4. Symfony\Component\Console\Application->doRun() /var/www/api/api-schools/vendor/robmorgan/phinx/src/Phinx/Console/PhinxApplication.php:81
PHP   5. Symfony\Component\Console\Command\Command->run() /var/www/api/api-schools/vendor/symfony/console/Symfony/Component/Console/Application.php:193
PHP   6. Phinx\Console\Command\Migrate->execute() /var/www/api/api-schools/vendor/symfony/console/Symfony/Component/Console/Command/Command.php:240
PHP   7. Phinx\Migration\Manager->migrate() /var/www/api/api-schools/vendor/robmorgan/phinx/src/Phinx/Console/Command/Migrate.php:94
PHP   8. Phinx\Migration\Manager\Environment->getVersions() /var/www/api/api-schools/vendor/robmorgan/phinx/src/Phinx/Migration/Manager.php:137
PHP   9. Phinx\Db\Adapter\PdoAdapter->getVersions() /var/www/api/api-schools/vendor/robmorgan/phinx/src/Phinx/Migration/Manager/Environment.php:227
PHP  10. Phinx\Db\Adapter\PdoAdapter->fetchAll() /var/www/api/api-schools/vendor/robmorgan/phinx/src/Phinx/Db/Adapter/PdoAdapter.php:248
PHP  11. Phinx\Db\Adapter\PdoAdapter->query() /var/www/api/api-schools/vendor/robmorgan/phinx/src/Phinx/Db/Adapter/PdoAdapter.php:234
PHP  12. Phinx\Db\Adapter\PdoAdapter->getConnection() /var/www/api/api-schools/vendor/robmorgan/phinx/src/Phinx/Db/Adapter/PdoAdapter.php:216
PHP  13. Phinx\Db\Adapter\MysqlAdapter->connect() /var/www/api/api-schools/vendor/robmorgan/phinx/src/Phinx/Db/Adapter/PdoAdapter.php:157
PHP  14. Phinx\Db\Adapter\PdoAdapter->createSchemaTable() /var/www/api/api-schools/vendor/robmorgan/phinx/src/Phinx/Db/Adapter/MysqlAdapter.php:79
PHP  15. Phinx\Db\Table->save() /var/www/api/api-schools/vendor/robmorgan/phinx/src/Phinx/Db/Adapter/PdoAdapter.php:333
PHP  16. Phinx\Db\Table->create() /var/www/api/api-schools/vendor/robmorgan/phinx/src/Phinx/Db/Table.php:556
PHP  17. Phinx\Db\Adapter\MysqlAdapter->createTable() /var/www/api/api-schools/vendor/robmorgan/phinx/src/Phinx/Db/Table.php:513
PHP  18. Phinx\Db\Adapter\PdoAdapter->writeCommand() /var/www/api/api-schools/vendor/robmorgan/phinx/src/Phinx/Db/Adapter/MysqlAdapter.php:238

Check out 0.2.1, change the default migration table name and try to migrate. You should be able to reproduce the issue.

Add a change method to the migration template

Also add better docs in the php doc blocks and maybe a few links to the official Phinx documentation.

Phinx\Adapter\Select object like Zend_Db to abstract SELECT Queries

We may introduce a Phinx\Adapter\Select class to abstract SELECT queries like Zend_Db does.

e.g.

$select->from('table')->where('id = ?', 1);

Phinx Export Feature

It would be nice to run phinx --export to export a json/xml/SQL representation of your database migrations. What purpose would this serve though?

The unit tests rely on the phinx.yml template

The MigrateTest unit tests rely on the standard phinx.yml config file that ships with Phinx. These tests should use their own phinx.yml file or create an instance of the config object.

To reproduce rename ./phinx.yml then execute the tests:

php vendor/bin/phpunit --configuration tests/phpunit.xml

Setting default_migration_table is ignored and phinxlog table is used anyway.

I expected that setting default_migration_table would change what the database table is called in the database. That did not happen for me. After changing this setting in phinx.yml to version and then running my first migration, the migration info was still stored in a new table phinxlog, apparantly using the old value.

Part of my phinx.yml (anonymized)

paths:
    migrations: %%PHINX_CONFIG_DIR%%/../../../migrations

environments:
    default_migration_table: version
    default_database: development
    development:
        adapter: mysql
        host: localhost
        name: dev
        user: changetoyouruserhere
        pass: 'changetoyourpasswordhere'
        port: 3306

If it matters my setup looks like this: MySQL 5.5.16, PHP 5.4.3 on Windows 7. Not using Symfony for anything else. My migrations runs just fine otherwise.

Implement a site design for phinx.org

Phinx.org needs a design refresh.

Paths should be cleaned up

Phinx should use/print clean paths.

Instead of:

using config ././/phinx.yml
using migration path /home/ubuntu/apps/app1/releases/20120611112413/./scripts/migrations

Phinx should say:

using config ./phinx.yml
using migration path /home/ubuntu/apps/app1/releases/20120611112413/scripts/migrations

web servers may serve phinx.yml as plain text

Would be helpful to add a warning to the docs that the db credentials stored in phinx.yml may be served up in plain text if added to www directory.

Phinx does not record the migration end time in the db

The migration end time is not recorded in the database after a migration has finished executing.

Add Windows Support

Phinx should work on Windows and be installable via Composer.

Can't create tables with an engine other than InnoDB

I was quite surprised to see the engine being hardcoded inside the mysql adapter createTable function.

Phinx should print its version number when installed via Composer

I don't think Composer can inject version numbers like PEAR does. Therefore we might need to manually commit the version number before release.

Create the Phinxlog table using createTable instead of raw SQL

Instead of relying on the hard-coded database constants, modify PdoAdapter to use the createTable method to create the Phinx migrations table.

MysqlAdapter: Add Support for MyISAM storage engine

The MysqlAdapter currently has the InnoDB storage engine hard-coded. Add support for the MyISAM storage engine.

Add foreign key support

Phinx should include support for foreign keys including adding and deleting them.

Methods could be like: addForeignKey() or dropForeignKey().

Specify the database name when migrating

Phinx should specify the database name when migrating. i.e.

using adapter mysql
using database phinx_db

Phinx should not create migrations with invalid class names

Running the command phinx create test will create a migration file with the timestamp and an underscore only. Force the create command to only accept class names in CamelCase. e.g. TestMigration or MyMigration and not foo or bar.

Add a Phinx\Db\Table::addTimestamps() method

This method should automatically create two datetime columns: created_at and updated_at.

Call the following code:

$table->addColumn('created', 'datetime')
$table->addColumn('updated', 'datetime', array('null' => true, 'default' => null))

Add a --verbose parameter to increase console output

Add a -v / --verbose parameter to increase the level of console output.

When executing migrations each command should be printed to the console.

e.g:

 == 20121013151612 CreatePostsTable migrating
 -- createTable('posts')
    -> 1.0121s
 == 20121013151612 CreatePostsTable migrated 1.1352s

This option should be added to both the migrate and the rollback command.

Phinx user/machine global preferences

We need a way to store preferences for the user or globally on their machine.

At the moment it would be nice to open migrations in your favorite text edito, straight after creating them.

Specifying the phinx.yaml path is not working

Say i place my phinx.yml in the following path: /home/ghola/migration/config/phinx.yml
Then i go to my project which is in: /home/www/project, and attempt to run the following command from CLI (assumming phinx is in PATH):
phinx status -c="/home/ghola/migration/config/phinx.yml"

It will error out saying that it can't find the yml file. So i try look inside the code, and i see it's based on the cwd, so i try a path based on the cwd (remember i'm in the project directory):
phinx status -c="../../ghola/migration/config/phinx.yml"

The same error appears. I managed to make it working by changing my cwd to /home/ghola/migration/config/ and issuing the command from there (without any other path specification for the yml file)

Update Phinx Copyright Docblocks to 2013

lots of cleanup needed.

Support for larger BLOB types in MySQL

With the current mapping of the binary type to use the "BLOB" type in MySQL restricts the column to 64KB of data.
Is there a way to use the other blob types "mediumblob" or "longblob" ?

Better Number Support for Mapping Migration Classes to Filenames

e.g. 'LimitResourceNamesTo30Chars' should map to '/^\d{14}_limit_resource_names_to_30_chars.php$/' instead of '/^\d{14}_limit_resource_names_to30_chars.php$/'.

Allow numbers in migration class names

In our system, each task/issue has a numerical ID, and as such it's easier to refer to the ID when naming the migration class (this way you know which task it originated from).

So, class names such as Feature49 should be permitted.

It's a simple regex change inside Util.php from:
return (bool) preg_match('/\b[A-Z][a-zA-Z](?:[a-z][a-zA-Z][A-Z]|[A-Z][a-zA-Z][a-z])[a-zA-Z]\b/', $className);
to
return (bool) preg_match('/\b[A-Z][a-z0-9A-Z](?:[a-z0-9][a-z0-9A-Z][A-Z]|[A-Z][a-z0-9A-Z][a-z0-9])[a-z0-9A-Z]\b/', $className);

Interactive init

Allow to specify the migrations dir and other configurations.
Also should create the migration dir if it does not exist.

Unsigned columns

I see no support for adding unsigned columns. The list of allowed column options is: 'limit', 'length', 'default', 'null', 'precision', 'scale', 'after'. Should i just be using plain SQL for this?

Remove options parameter from AdapterInterface::hasColumn()

It's not used. I think I copied it from Rails.

Add Phinx task to Phing

Phing (http://www.phing.info/) is a great CI tool for PHP, based on Apache Ant, so how about adding Phinx task to Phing as an alternative to DbDeployTask?
http://www.phing.info/docs/guide/trunk/chapters/ExtendingPhing.html

Add a test command

Add a test command to quickly verify the YAML configuration file and then the database connection

Introduce Phinx\Db\Table::getPendingColumns()

Add methods to Phinx\Db\Table

getPendingColumns()
setPendingColumns()

Move the logic from getColumns() & setColumns() to these methods. These old methods should actually return the table's columns!

Manager::migrate() does not support 14 digit integers on all systems

We should change version to be a string instead of an int. Travis CI has problems with integers such as 20121213232502 when calling the migrate / rollback methods.

UTF8 in execute block get corrupted

Hello,

When I use execute with some special characters (accent and such) they get corrupted. Not sure how to fix that.

Thanks,

Support for primary key

Support for defining custom primary key may be useful in some cases.

Currently phinx add ìd` column as auto increment primary key.
It should be possible to override this behavior, so we can create tables w/out primary key, w/out auto increment.

The init command should initialize a project for Phinx

The init command should copy the sample phinx.yml file to initialize a project for Phinx.

Phinx prints its version twice when executed without any parameters

Phinx prints its version twice when executed without any parameters:

$ php bin/phinx
Phinx by Rob Morgan. version dev

Phinx by Rob Morgan. version dev

When trying to set column default value to 0 it is ignored.

This migration

$this->table('table')->changeColumn('field', 'integer', array('default' => 0));

will not have any effect, since 0 considered as no value for default option