Migration generator

[alex-barylski]

I was looking for a tool to handle the migrations for me (although I am using Doctrine DBAL I am not using Entities or the RM ATM).

Right now I make changes to my local DB and occasionally forget table changes, which of course break code when pushed to staging and worse when they make it to production. Migrations would prevent this. However for speed of work flow (for me personally) it would be nice if an addiitonal command such as "Generator" or "Skeleton" was introduced which allowed Phinx to generate the migrations based on the diff test performed on two databases (either two local DB's or a development VS staging, etc).

Is this completely out of context on what you plan on offering with Phinx? Does your DB API support viewing meta data? Have you considered using the Doctrine DBAL?

Just a thought -- great work BTW -- I will totally use this moving forward until I get everything ported over to ORM.

Alex

[ghost]

I think we can utilize the approach rails has made available on their migrations. You can optionally dump a schema.rb in rails which will snapshot your databases schema.

This approach would allow you to differentiate the file based on the two different environments and be able to compare on contrast changes being introduced.

This also serves will with migraitons and code coordination as you would check in your schema.php, and you could see the differences, allowing one more step of verification to catch errors.

This works with your thoughts? If so, I vision a behavior as

./bin/phinx schema:dump

to generate schema.php

and optionally having your migration configuration file have a flag

%%PHINX_GENERATE_SCHEMA:TRUE%%

this would output a snapshot of your database create statements in a TBD location with schema.php encapsulating the state of your schema.

[cbrunnkvist]

As I see it, one of the things making Phinx so attractive to use is that it is easy to get started with. Particularly so if a project is currently just relying on something akin to "data.mysqldump".

If :floppy_disk:phinx-schema.php (or "reflection of [a] database state", which IMO better explains its purpose) dump&load was implemented I'd vote for it being documented as a totally optional feature which you could enable once you need it.

I suspect that "automatically generating migrations" and "automated schema dumping and loading" are two separate features.

[CWSpear]

I very much like this concept of "automatically generating migrations." It seems to be pretty common in other ecosystems (Node, .NET), but pretty sparse in PHP. I've really only seen it in Doctrine and Propel, which is pretty much the anti-epitome of "Be[ing] PHP framework independent," what with them being pretty large ORMs and all.

I'm actually working on a library to handle "automatically writing phinx migrations" based on a schema definition. My approach plan and reasoning is discussed here: http://cameronspear.com/blog/a-different-and-better-approach-to-database-migrations-in-php/

I hope to have a "alpha" version working with at least MySQL by the end of the year.

[ghost]

keep the thread posted on any updates.

[beporter]

This was a killer feature for CakePHP 2's Migrations plugin. (Alas, abandoned since Cake 3 switched to Phinx.) I found that the safest and quickest way to build a migration was to edit my local development database directly using a powerful tool like Sequel Pro, then use the Migrations plugin to generate the necessary transformations and encode them into a migration.php file for me.

This approach has some substantial advantages in my book:

• It completely precludes the possibility of human error in the writing of the migration file itself-- no syntax errors and no logic errors (you've already modeled the appropriate changes in the DB directly).
• It produces consistent results. Changing schema characteristics can typically done in at least a couple different ways using a tool like Phinx. (Drop a table and re-add a modified version, alter a column directly, or execute a literal SQL command, for example.) But for a given change in a schema, a program will produce the same migration commands every time.
• It's quick and repeatable. While experimenting with changes to the database, a developer can build a migration, test it, and if it doesn't work they can just tweak their schema directly and immediately regenerate the corrected migration file.
• No more having to remember another DSL. I appreciate Phinx's capabilities, but my clients pay me to spend my days writing the PHP that powers their business, not that transforms their DB. If a tool could produce results significantly superior to what I can do by hand, faster and more accurately, then that tool should definitely exist.

All that said, building the infrastructure to be able to compare a live DB to an existing static representation and generate migration commands from the difference is not trivial. It requires not only a way to persist a schema to disk (Cake 2 used a SchemaShell for this,) it also requires a way to encode the entire schema in-memory and compare the differences between two of those objects (ref: Cake 2's CakeSchema class).

It will also be complicated by the fact that Phinx is not exactly feature complete yet--it's not able to fully represent every possible change to a schema. (Try changing the column used as the primary key on an existing table without dropping the table or use raw SQL.) In other words, Phinx needs to grow up more itself before a migration generator is completely feasible. Implementing a generator before that time would result in "lossy" operations where a change you make to your actual schema isn't able to be reflected in the generated migration file, and thus will not persist to other installations when the migration is run by other developers or in production.

The good news is that this is a problem that others have solved before, and there is lots of "prior art" to reference. :+1:

[dereuromark]

:+1: for some diff command to generate a migration from the current changes done to DB (like propel2 does).

[dandoingdev]

I'd love this feature too. Is it on a roadmap?

[BassemN]

:+1:

[hsegnitz]

I actually just had the idea of writing a tool that would take two mysql dumps or other forms (in first stage only with schema definitions, not data) and diff them, with a few options like "ignore changes in auto_increment column" or "don't delete columns/tables" etc. and create another SQL-File with ALTER TABLE statements. Did a quick series of searches - and since I'm already using phinx, I found this thread.

Two questions arise for me:

1. Did anybody start with such a thing yet, and I can join to help? If not:
2. What makes more sense:
   • 2.A) a contribution to phinx to generate phinx migrations or
   • 2.B) a standalone tool (.phar) that has phinx migrations as one possible output, the other being plain .SQL for now. This standalone tool could be wrapped by phinx.

Independently from the above choice, the phinx part could then be also about workflow -- e.g. automatically dumping a schema definition after a successful migration run to use for a comparison run in the future.

Inspiration for this is SQLyog's Schema Migration Assistant which is AFAIK only available in the enterprise edition. My current task at work involves more than 400 MySQL tables with a lot of bad datatypes and indexes for up to 8 joins at once, where I will have to tweak it a lot by hand back and forth to find the correct data types and index definitions. I will not need every step in between, just a diff between "legacy" and "optimized".

[odan]

Currently I'm working an a migration generator for phinx . It's not production ready. https://github.com/odan/migrations

[celorodovalho]

:+1:

[tomasdelvechio]

Currently I'm working an a migration generator for phinx . It's not production ready. https://github.com/odan/migrations

Some tool like this for PostgreSQL

[kumy]

I'm also interested in such tool for Postgresql

[dereuromark]

Someone just has to take the idea and implement it. The leg work has already been done. There is a "snapshot" part available and based on that, the current migration state should be diffable. Then it just needs to generate this diff of fields, indexes and alike.