Generating migration from existing database in Yii

Question

I'm working on a project that has a fairly complex database (150+ tables). In order to be able to maintain changes, I've decided to add migrations, preferably using Yii or Laravel.

Does anybody know, if it is possible to generate a initial migration from an existing database?

Creating it by hand would:

• take for ever and
• be very error-prone.

If there is no way, does anybody know a good PHP-based framework, that supports such functionality?

Answer 1

Instructions for accomplishing this in Yii:

1. Add your database connection settings to protected/config/console.php.

2. Run yiic migrate create initial to create the stub code for the migration.

3. Copy contents of this gist to protected/commands/InitialDbMigrationCommand.php.

4. Run yiic initialdbmigration 'name_of_your_database' > initial_migration.php to generate up() and down() methods for initial database migration.

5. Copy and paste up() and down() methods from initial_migration.php to the file created in the protected/migrations folder in step 2.

Answer 2

I use both Yii and Laravel and I could not find what you require for either of them. They both create empty files and you need to create the migration script yourself. For a table of 150 tables it will be challenge to create the migrations yourself, but it is not quite as hard as you imagine. Because you already have the information on the fields it should not take so long to create.

Answer 3

Well since migration is about setting up your database structure and make changes to it, not to reflect a current database there is no such way.

And this is also not a step you have to make. You can start from where you are at the moment, which will make you able to rollback up to this point. Which means you can make migrations for your current tables without having to specify their entire structure, but just the changes only.

Let's say you have a table called user and want to add their firstname to it.

php artisan migrate:make add_firstname_to_user

Now go into application/migrations and find the migration file, add this

public function up()
{
    Schema::table('user', function($table)
    {
        $table->string('firstname');
    });
}

public function down() {
    Schema::table('user', function($table)
    {
        $table->drop_column('firstname');
    });
}

Now you can add migrate it

php artisan migrate:install // if you haven't run this, should only be once
php artisan migrate

.. and rollback using

php artisan migrate:rollback

This will add or drop the column firstname, without affecting your table in any other way.