[Proposal] Database migration declarative approach per table, run migrate depending on the difference

[ajcastro]

Database migration using declarative approach per table. Table migration modifications will run migrate depending on the difference between the source code migration and the current database.

Imagine we only have a migration file per database table. I think it is easier to maintain and remember the current table/database structure.

By diffing the source code migration and the current database, the migrate command should know what changes it should make to the database.

There will be less usage of up and down when creating migrations.
I think having the current up and down methods in migration should stay so that we can still use it and existing migrations wont be affected. This will be an additional feature.

Maybe we can add a new command for it let's say php artisan db:structure.

I think this also have a better workflow, whenever you db table structure changes, just change the table migration and run php artisan db:structure. Or you want to checkout to a different branch and use the current db structure in that branch, just git checkout another-branch && php artisan db:structure.

We can also add a watch option that will watch changes in source code structure and automatically update it in your database, php artisan db:structure --watch.

Then migrations will just be used for procedural instructions that needs to be run, whether seeding an initial database records. Setting up other things, things which can be rollback using down method the usual way.

[mfn]

Just my 2c:

• this topic is inherently complex
• CakePHP2 has such a feature. Don't know about their version 3 though
  It worked most of the time but broke sometimes
• I think Doctrine has a feature like this to "reverse engineer" the schema and much more stuff to work with that. E.g. https://github.com/barryvdh/laravel-ide-helper uses this to introspect the database and add meta phpdoc to the models, etc.
• You also have to think about supporting all currently supported database => huge thing.

[drbyte]

I understand the "why" behind this proposal. Good points made.

As pointed out, it is inherently complex.

Also my 2c:
I suspect it's something that ought to be made into a package, and proven in-the-wild, before it would be considered for inclusion in core. This will help demonstrate demand for it.
It would also give opportunity to address database-engine-specific issues until they're solid.

[sisve]

Users of laravel-doctrine/orm has a php artisan doctrine:schema:update --show-sql that shows the needed sql statements to make the existing match the models. You can remove the --show-sql to execute it automatically too. There's a php artisan doctrine:schema:validate that not only verifies that the models match the database, but that the models themselves have proper references to each other. (Things like how bidirectional relationships are setup.)

I believe that a requirement for this feature is the mappings of how what properties a model has, and how models reference each other. This is currently not available in Eloquent.

[mfn]

Maybe related (didn't use it):
https://github.com/Xethron/migrations-generator

Laravel Migrations Generator: Automatically generate your migrations from an existing database schema.

[sisve]

@mfn That package would be unrelated. It does database-to-code, what we're looking at in this issue is having existing models, and some way to generate the sql queries necessary to update the database (or create a migration) so that the database matches the models.

We're looking for a code-first tool that can not only create the database schema, but also updating it when the code changes.

[erfanimani]

In case it's useful — Magento 2.3 comes with this feature now. Documentation can be found here https://devdocs.magento.com/guides/v2.3/extension-dev-guide/declarative-schema/db-schema.html

There's indeed some complicated bits, like renaming tables and columns.

[pstephan1187]

I would love a feature like this. Our production app at work has 168 migrations, which I think is ridiculous. For other projects I have developed a small personal implementation of Doctrine to do this (https://github.com/pstephan1187/schema-manager). It's not perfect and I only update it as I use it from time-to-time, so having something like this in the core would be huge for me. I would be more than happy to help work on this for the core if it's something that would be approved.