Phinx: Create an Initial Migration from an Existing Database

[ylynfatt]

Is it possible for Phinx to create an initial migration from an existing database? My scenario is that I have an application with a database and it's tables already and I'd like to start using Phinx to handle my migrations (previously i was manually creating SQL scripts). I'd love if Phinx could inspect my database and create an initial migration file with the current table structures as a starting point.

I hope that made sense.

[kmentch]

I would love to see this feature as well.

[robmorgan]

I'll leave this issue open - happy to review a PR, but I won't be working on it personally for now.

[detook]

I've started to work on this today. I see following implementation:

1. phinx schema:dump creates phinx migration at migration_folder/schema/schema.env.php
2. schema.env.php can be migrated using phinx schema:load. In this case old schema will be desrtoyed.
3. phinx schema:dump runs every time after migrate and rollback.

Any comments?

PS. I don't know for now how to deal with missing data type. For example, target table has tinyint field. $table->getColumns() will throw an exception in this case. I think fetching columns and type check shoud be separated. @robmorgan please comment.

Thanks

[detook]

Regarding missing data types - I looked to wrong method. Sorry. Everything is fine here

[ylynfatt]

@detook This sounds good to me for an initial implementation. Can't wait to try it out.

[iwootten]

@detook I've been attempting to use your fork, but can't see the "schema-dump" command available in the list of commands in the CLI on the schema-dump branch. Am I missing something obvious?

I was about to tackle this request myself, but given you're already making headway, I'd like to see if I can use it.

[detook]

Does PhinxApplication's constructor has $this->add(new Command\SchemaDump()); line?

@iwootten, you could help with schema-load command. My fork does basic dump right now, primary and foreign keys will be added during the weekend.

[iwootten]

@detook Yes it's there. I've faked a composer directory vendor/detook/phinx and switched to your branch under there. php vendor/detook/phinx/bin/phinx doesn't seem to give me anything extra.

Happy to help if I can.

[detook]

@iwootten try following:
1) Tell composer to use my branch.

"repositories": [
        {
            "type": "vcs",
            "url": "https://github.com/detook/phinx.git"
        }
],
"require": {
        .....
        "robmorgan/phinx": "dev-schema-dump",
        .....
}

2) Or clone repository as standalone project and develop with TDD. After successful tests try code on real database.

[iwootten]

@detook great, that gets me to use of the command, but it yields a runtime exception when I try to execute it.

"[RuntimeException] The type: "" is not supported."

schema-dump [-c|--configuration="..."] [-p|--parser="..."] [-e|--environment="..."]

[detook]

Great! Looks like Phinx failed to guess your field type. Usually it happens when field has attributes like int(4) unsigned. This is one my TODOs. Let me know.

By the way, I'm working only with MySQL first so other adapters do not work.

[iwootten]

@detook, looks like you're right. I'm using MySQL, so that's no problem. A simpler table gets written out by the dump tool to a migrations directory as expected.

[detook]

@iwootten, latest commit f0f9ebc should dump fields with attributes

[Rican7]

I never do this, but I would LOVE to see this feature. To be honest, without it migrations are only half-useful.
I give this a :+1:

[brianmuse]

:+1: would love to see this as well!

[adamski]

Please can we have @detook's work on this merged in? Its a very useful feature and I'd like to be up to date with the rest of the project if we are using it.. Great work all round !

[detook]

@adamski You can use already use it https://github.com/robmorgan/phinx/issues/109#issuecomment-31066844 This branch does not have latest features from origin repo though.

[adamski]

Great thanks @detook , I already used it to dump schema from an existing database, worked great although I had to add 'longtext' to available types as they are present in our database.
I'm fine with using your branch just would be good to have it merged in to dev-master.

[dimz119]

Thanks @detook it saves a lot of my hours.
BTW, there are missing schema type for MySQL

case static::PHINX_TYPE_VARBINARY:
return array('name' => 'varbinary');
break;
case static::PHINX_TYPE_LONGTEXT:
return array('name' => 'longtext');
break;
case static::PHINX_TYPE_TINYTEXT:
return array('name' => 'tinytext');

Just FYI. :)

[shadowhand]

@dimz119 @detook #249 added support for longtext and tinytext, among others.

[shruthi-ananth]

Thank you @detook. It worked great for me too. But I had to switch over to the dev-master due to newer type support added in the later branches. Can we merge this with current dev-master? It's a great feature to have.

[detook]

Yes, I'm planning to maintain fork in future. I just not sure that I'll have time this January.

[shadowhand]

@detook:

I'm planning to maintain fork in future

Why would you try to maintain a fork, rather than sending a PR?

[detook]

@shadowhand it's already here #192

[rbalik]

Looks like that pull request is closed. Still any plans to merge this in? It would be great to have.

[shadowhand]

@rbalik the pull request was closed because it was made against an outdated branch. Hopefully @detook chooses to recreate it.

[detook]

I don't have time to review changes between 0.3.x and 0.4.x to create good PR at this moment. But I definitely won't leave it. I'll try to find time on weekends in March.

[davila7]

When I run this command "shema: dump" I lose all data. There is a command for the shema and also the data in the tables?

[detook]

@davila7 both dump and load designed to work only with schema.

[davila7]

@detook Ok, but There is a type of command that handles the data?

[lorenzo]

@davila7 mysqldump

[davila7]

mysqldump is to backup, I need something like that:

//schema
public function change(){
$user = $this->table('user');
$user->addColumn('name', 'string', array('limit'=> 40))
->save();

$this->execute("INSERT INTO 'user' ('name') VALUES
('davila7');");
}

or just the insert, since the structure gives me schema:dump

[sabl0r]

Awesome feature!
@detook Are you planing to include the current migration version into your dump? Like ActiveRecord is doing it? This would allow us to skip migrations, which are already covered by the latest dump after doing a schema:load on a fresh database.
I tried the schema-dump branch of your repo and this is missing at the moment.

[mosesliao]

+1

[gwagroves]

I've created a simple standalone php cli script to generate phinx migration commands for an existing MySQL database, if any one is interested: https://github.com/gwa/MySQL2Phinx

(Not all features are currently implemented, but it's better than creating all the commands manually.)

[gmansilla]

Another way of accomplishing this is:

Generate a sql dump with everything (structure + data)
Create an initial migration file and have phinx to execute that sql file (see #69)

Although I am not sure if this is correct approach.

[DanHulton]

That's how we do it right now, but it's not great. If you want to then push your migrations into production, you have to init phinx to make sure the migrations table exists, then manually make entries to skip over your sql dump migration, THEN run the rest of your migration scripts. This can be error-prone, and the error in question (accidentally corrupt your live data) is pretty horrific.

[beporter]

See also #350, which would seemingly solve this problem as a subset of the larger problem of _generating_ migrations from a live DB.

EDIT: Or worded another way: This thread may also be a precursor to the other.

[mathieuimbert]

Is anyone still interested in this feature? I'm working on a 'phinx revert' command that would allow one to create a migration based on an existing database.

[rbalik]

I ended up doing it manually, so don't need it right away, but definitely seems useful.
(And yeah, doing it manually was a huge pain.)

[albertleao]

+1

[J-Fricke]

+1
This would definitely be a useful addition to this tool. I'm going to have to take a look at the implementation @gwagroves listed until this is part of Phinx.

[janreges]

+1

[BassemN]

:+1:

[ludalex]

👍

[angular-tools]

This is definitely a much wanted feature. I see two or three people have attempted this, so I hope something works out :+1:

[basilfx]

I picked up where @warnero left off.

• Rebased onto latest development version
• Bugfixes
• Parsing MySQL enum's
• Working on SQLite interface

So far I can dump a MySQL database (15+ complicated tables) and load it into SQLite or MySQL. However, foreign keys are not (yet) supported, which is something I can live with for now.

Check if out at https://github.com/basilfx/phinx/tree/feature/schema see below.

[jimsandwick]

@basilfx Hi. I'm really interested in the 'Create an Initial Migration from an Existing Database'. Is this possible with the changes you have made? I am unable to see the functionality in the documentation, commands.

How are you dumping and loading into MySQL through Phinx?

Regards,

[basilfx]

@jimsandwick You can use phinx schema:dump to create an initial database schema of the tables in your database, and phinx schema:load to load that schema into your database. This will destroy your existing database, so be careful.

The commands aren't documented (yet). I'll see if I can rebase it onto latest master tomorrow.

[jimsandwick]

@basilfx Thanks very much. I should have read more of the comments above. Thanks for your time and effort. Regards,

[basilfx]

@jimsandwick I have updated the repository:

• 0.5.x-dev: https://github.com/basilfx/phinx/tree/0.5.x-dev
• 0.6.x-dev: https://github.com/basilfx/phinx/tree/0.6.x-dev (latest)

From 0.5.x-dev to 0.6.x-dev was a bit more painful, but it seems to work. I also added some documentation.

[jimsandwick]

@basilfx Thanks very much. Will be having a play around soon. Thanks again.

[adduc]

@basilfx Looks like a great start. Any plans to add support for indexes when dumping the schema?

[celorodovalho]

+1 :+1:

[basilfx]

@adduc Sorry, don't have the time to do active development. I only do bug fixes for the project I use it for. Feel free to add it :-)

[basilfx]

I have updated the repository:

• 0.5.x-dev: https://github.com/basilfx/phinx/tree/0.5.x-dev
• 0.6.x-dev: https://github.com/basilfx/phinx/tree/0.6.x-dev
• 0.7.x-dev: https://github.com/basilfx/phinx/tree/0.7.x-dev (latest)

Initial support for dumping foreign keys and collation is included in 0.7.x-dev (MySQL only).

[andrezap]

Can anyone tell me how can I use the version that @basilfx has created?

[celorodovalho]

@basilfx can you merge my pull request to yours?
https://github.com/robmorgan/phinx/pull/955

[basilfx]

@andrezap Easiest is to add something like this to your composer.json file:

{
    "repositories": [
        {
            "type": "git",
            "url": "https://github.com/basilfx/phinx"
        }
    ],
    "require": {
        "robmorgan/phinx": "dev-0.7.x-dev",
    }
}

@celorodovalho I can, but please rebase onto my 0.7.x-dev and create a PR here. Be sure to select the correct branch when creating a PR.

[bensinclair]

@andrezap Once you do what @basilfx suggested you then run the following in command line:

vendor/bin/phinx schema:dump

[andrezap]

@bensinclair @basilfx Thank you!

[MACscr]

Odd that it still shows me as using 0.6.4

webusr@id-4:/var/www/html$ cat composer.json
{
    "repositories": [
        {
            "type": "git",
            "url": "https://github.com/basilfx/phinx"
        }
    ],
    "require": {
        "mobiledetect/mobiledetectlib": "^2.8",
        "namshi/ab": "^1.1",
        "kassner/log-parser": "^1.1",
        "robmorgan/phinx": "dev-0.7.x-dev"
    }
}
webusr@id-4:/var/www/html$
webusr@id-4:/var/www/html$ composer update
Loading composer repositories with package information
Updating dependencies (including require-dev)
  - Removing robmorgan/phinx (v0.6.5)
  - Installing robmorgan/phinx (dev-0.7.x-dev 2edf860)
    Cloning 2edf86092de87c0478126cfe02c7c406fba8aa96

Writing lock file
Generating autoload files
webusr@id-4:/var/www/html$ vendor/bin/phinx schema:dump
Phinx by Rob Morgan - https://phinx.org. version 0.6.4

using config file ./phinx.yml
using config parser yaml
using migration paths
 - /var/www/html/sql/migrations
using seed paths
PHP Fatal error:  Uncaught Error: Class 'Phinx\Console\Command\ConfirmationQuestion' not found in /var/www/html/vendor/robmorgan/phinx/src/Phinx/Console/Command/SchemaDump.php:45
Stack trace:
#0 /var/www/html/vendor/robmorgan/phinx/src/Phinx/Console/Command/SchemaDump.php(120): Phinx\Console\Command\SchemaDump->getCreateMigrationDirectoryQuestion()
#1 /var/www/html/vendor/symfony/console/Command/Command.php(256): Phinx\Console\Command\SchemaDump->execute(Object(Symfony\Component\Console\Input\ArgvInput), Object(Symfony\Component\Console\Output\ConsoleOutput))
#2 /var/www/html/vendor/symfony/console/Application.php(820): Symfony\Component\Console\Command\Command->run(Object(Symfony\Component\Console\Input\ArgvInput), Object(Symfony\Component\Console\Output\ConsoleOutput))
#3 /var/www/html/vendor/symfony/console/Application.php(187): Symfony\Component\Console\Application->doRunCommand(Object(Phinx\Console\Command\SchemaDump), Object(Symfony\Component\Console\Input\ArgvInput), Object(Symfony\Component\Console\Output\ConsoleOutput in /var/www/html/vendor/robmorgan/phinx/src/Phinx/Console/Command/SchemaDump.php on line 45

[basilfx]

Try to remove the /var/www/html/vendor/robmorgan/phinx/ folder, the run composer install. Version 2edf86092de87c0478126cfe02c7c406fba8aa96 is the right one.

[MACscr]

Tried the following (replacing the bin as well with no diff), but still get the same exact error as I reported earlier:

webusr@id-4:/var/www/html$ rm -rf /var/www/html/vendor/robmorgan/phinx/
webusr@id-4:/var/www/html$ composer install
Loading composer repositories with package information
Installing dependencies (including require-dev) from lock file
  - Installing robmorgan/phinx (dev-0.7.x-dev 2edf860)
    Cloning 2edf86092de87c0478126cfe02c7c406fba8aa96

    Skipped installation of bin bin/phinx for package robmorgan/phinx: name conflicts with an existing file
Generating autoload files
webusr@id-4:/var/www/html$

[MACscr]

No other ideas? Am I somehow missing a dependency that hasn't been listed? I've only installed the required symfony packages, I'm not using symfony in general. Though i dont think thats the problem since the version that is being listed when i run the command is the 0.6.x version and not the 0.7.x version, so obviously need to solve that first.

[casimcdaniels]

I'm trying what @basilfx suggested, and schema:dump doesn't appear to do anything besides load the config and paths.

$ vendor/bin/phinx schema:dump
Phinx by Rob Morgan - https://phinx.org. version 0.6.4

using config file .\phinx.yml
using config parser yaml
using migration paths
 - C:\Users\Casi\Projects\test\db\migrations
using seed paths

Any suggestions?

[futoase]

@mackstar

This may be the cause
https://github.com/basilfx/phinx/pull/1/files#diff-61a9b7867c140a3e0008beba372d8cd4R10

[basilfx]

I have updated the 0.6.x-dev fork and the 0.7.x-dev fork.

The 0.7.x-dev fork contains support for dumping indexes (contributed by @jwh315, MySQL-only).

[HanXHX]

Hi @basilfx,

Your script don't manages "id" well. However, (offer_has_country.country_id) size (TINYINT) is well detected but not country.id.

SQL (MariaDB 10.1) dump part:

CREATE TABLE `country` (
  `id` tinyint(3) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(60) NOT NULL,
  `code` char(2) DEFAULT NULL,
  `code3` char(3) DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `name_UNIQUE` (`name`),
  UNIQUE KEY `code_UNIQUE` (`code`),
  UNIQUE KEY `code3_UNIQUE` (`code3`)
) ENGINE=InnoDB AUTO_INCREMENT=251 DEFAULT CHARSET=latin1;

CREATE TABLE `offer_has_country` (
  `offer_id` smallint(5) unsigned NOT NULL,
  `country_id` tinyint(3) unsigned NOT NULL,
  PRIMARY KEY (`offer_id`,`country_id`),
  KEY `fk_country_has_offer_offer1_idx` (`offer_id`),
  KEY `fk_country_has_offer_country1_idx` (`country_id`),
  CONSTRAINT `fk_country_has_offer_country1` FOREIGN KEY (`country_id`) REFERENCES `country` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION,
  CONSTRAINT `fk_country_has_offer_offer1` FOREIGN KEY (`offer_id`) REFERENCES `offer` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

It generates:

$this->table('country', array())
    ->addColumn('name', 'string', array('length' => 60))
    ->addColumn('code', 'char', array('length' => 2, 'null' => true))
    ->addColumn('code3', 'char', array('length' => 3, 'null' => true))
    ->create();

$this->table('offer_has_country', array('id' => false, 'primary_key' => array('offer_id', 'country_id')))
    ->addColumn('offer_id', 'integer', array('length' => 65535))
    ->addColumn('country_id', 'integer', array('length' => 255))
    ->create();

[jwh315]

@HanXHX I noticed this as well. I am wondering if it would be better to explicitly define the primary key even when the primary key is id

$this->table('country', [ 'id' => false, 'primary_key' => [ 'id' ] ])
             ->addColumn('id', 'integer', [ 'length' => 255 ])
             ->addColumn('name', 'string', [ 'length' => 60, 'collation' => 'latin1_swedish_ci' ])
             ->addColumn('code', 'char', [ 'length' => 2, 'null' => true, 'collation' => 'latin1_swedish_ci' ])
             ->addColumn('code3', 'char', [ 'length' => 3, 'null' => true, 'collation' => 'latin1_swedish_ci' ])
             ->addIndex([ 'name' ], [ 'name' => 'name_UNIQUE', 'unique' => true ])
             ->addIndex([ 'code' ], [ 'name' => 'code_UNIQUE', 'unique' => true ])
             ->addIndex([ 'code3' ], [ 'name' => 'code3_UNIQUE', 'unique' => true ])
             ->create();

This seems to work correctly. @basilfx, I could implement this if you think it is a good idea.

[HanXHX]

Hi @jwh315, @basilfx,

I think more verbose output is less debug ;)

[basilfx]

This seems to work correctly. @basilfx, I could implement this if you think it is a good idea.

@jwh315 That is fine with me as long as Phinx supports that concept. I want to avoid API/syntax changes to Phinx where possible :-)

[micheleangioni]

Hi @robmorgan ,
any chance to see such a feature implemented in (original) Phinx?

[Maelstromeous]

Hey @robmorgan, this is literally all that Phinx is missing for me... any chance to revive this very old feature request? :)

[odan]

I couldn't wait longer and implemented a phinx migrations generator for MySQL (only). It's still beta and I'm looking for feedback.
https://github.com/odan/phinx-migrations-generator

[glensc]

@basilfx basilfx/0.7.x-dev (d70750f)

1. creates invalid php syntax:

->addIndex(array('cam_prj_id', 'cam_customer_id', 'cam_usr_id'), 'unique' => true )
->addIndex(array('ema_username', 'ema_hostname', 'ema_folder'), 'unique' => true , 'limit' => 100)
->addIndex(array('iss_summary', 'iss_description'), 'type' => 'fulltext')

mysqldump line for those lines:

  UNIQUE KEY `cam_manager` (`cam_prj_id`,`cam_customer_id`,`cam_usr_id`),
  UNIQUE KEY `ema_username` (`ema_username`,`ema_hostname`(100),`ema_folder`(100)),
  FULLTEXT KEY `ft_issue` (`iss_summary`,`iss_description`)

1. also, this mysqldump line:

  `iaf_file` longblob,

gets following php code:

            ->addColumn('iaf_file', 'binary', ['length'=>4294967295,'null'=>true])

which gives exception under 0.8.0:

  [PDOException]                                                                                                              
  SQLSTATE[42000]: Syntax error or access violation: 1074 Column length too big for column 'iaf_file' (max = 255); use BLOB   
  or TEXT instead                                                     

[bmudda]

Is there a plan to integrate this to the Phinx project? This would be really helpful when using Phinx in an already existing project with existing schema.

[aat2703]

Any progress on this issue? I think its a major issue for Phinx... Most people using Phinx already have mature applications running... And hand writing several hundreds of table migrations manually is insane... Also a diff generator would be helpful too

[broberts-mrmc]

For what it’s worth, we use Phinx [for migrations], but as you stated, we also found it to lack a “diff” capability, unlike Doctrine, for example, which takes a diffident, ORM-coupled approach that makes “diffing” less problematic for the framework.

While we haven’t adopted the whole of the framework, we’ve found CakePHP includes a diff utility for generating a [Phinx] migration from an existing database. CakePHP and Phinx are integrate via its migrations plugin.

We’ve used Cake for this purpose with success though we’ve found these generated migrations to need some TLC, though it’s still much better that writing them from scratch.

I hope this helps.

On Sep 19, 2018, at 9:35 AM, Andreas Aagaard <[email protected][e-mail]> wrote:

External Email Message

---

Any progress on this issue? I think its a major issue for Phinx... Most people using Phinx already have mature applications running... And hand writing several hundreds of table migrations manually is insane... Also a diff generator would be helpful too

—
You are receiving this because you are subscribed to this thread.
Reply to this email directly, view it on GitHubhttps://github.com/cakephp/phinx/issues/109#issuecomment-422827918, or mute the threadhttps://github.com/notifications/unsubscribe-auth/APvK7IuY1ibc3B7bFJ7G8K88mes_Ewx6ks5uclYtgaJpZM4AzwzC.

[aat2703]

@broberts-mrmc thank you

I'll take a look at it - Seems like it would be the best solution atm.

To help others in same situation: https://book.cakephp.org/3.0/en/migrations.html

[guilhermeaiolfi]

It's been a while since @basilfx and @celorodovalho worked on it. It looked good and all, why it wasn't released in any stable version?

I started working with phinx today and that's a hell of a feature to save time and energy. It would improve a lot the workflow for me, at least. That and a diff command to create migrations for you automatically are the only features missing from phinx.

Thanks.

[othercorey]

Closing due to lack of activity or out of scope. If there is new interest, please open a PR.