Database Migrations Management

Asked by J Bruni

Laravel 4 provides a "migrations" mechanism, which is a kind of "database schema versioning": http://laravel.com/docs/migrations

In the other hand, we are using MySQl Workbench to design the database schema.

Are we going to use the Laravel migrations? Are we going to stick with MySQl Workbench? How these could be managed?

Question information

Language:
English Edit question
Status:
Answered
For:
i-opeen Edit question
Assignee:
i-opeen maintainers Edit question
Last query:
Last reply:
Revision history for this message
J Bruni (jbruni) said :
#1

Here is my approach and suggested way to manage the database schema changes:

- Yes, keep using the MySQL Workbench tool to design the schema.
- Yes, make use of Laravel 4 migrations mechanism.

HOW, then?

"php artisan migrate:make schema_creation"

For the fisrt "migration", Laravel's Artisan CLI tool command above created a PHP file in the app/database/migrations, named "2014_01_24_024524_schema_creation.php" - with a skeleton to be fulfilled with the database commands to perform the schema changes. Instead, I changed its contents to the one we currently have in the repository. This very same code can and should be copied to any future migration PHP file - only the automatically generated CLASS name must be preserved.

For each "migration", we have THREE other corresponding files. Using the existent first migration as reference, they are:

- 2014_01_24_024524_schema_creation.mwb
- 2014_01_24_024524_schema_creation.sql
- 2014_01_24_024524_schema_creation_revert.sql

The "mwb" file is the MySQL Workbench corresponding file.
The "sql" file is the corresponding SQL script - generated automatically by MySQL Workbench's SYNCHRONIZE feature.
The third file has also the "sql" extension, with the "_revert" string appended to the base filename. It contains the SQL commands to revert the migration.

The "sql" files are used/called by the PHP migration script. The naming must follow these conventions so everything runs smoothly.

All this has been done for the first "migrations" (schema creation). All these files are expected to remain the same, unchanged. For future migrations, the steps are:

1) Run the "migration:make" Artisan command - "php artisan migrate:make change_description"
2) Copy the latest "mwb" file, and rename it, using the automatically generated PHP script base filename.
3) Copy the previous "php" file contents into the new one (but preserve the generated CLASS name!) - and save the file.
4) Perform the changes in the new "mwb" file, and by using the "Synchronize" tool, generate the "sql" script.
5) By loading the previous "mwb" file in Workbench and using the "Synchronize" tool again, it is possible to generate the "_revert.sql" script.

It may sound a bit cumbersome, but I am used to it... of course, I can perform the process myself while I'm around.

A final issue comes: what if one wants to change the schema but not necessarily create a new migration immediately? As we should not change the "mwb" file already attached to a specific migration, I suggest copying the latest migration "mwb" file named as "ion.mwb", in the same "app/database/migrations" folder, and using it as the working file. This way, the steps outlined above change to:

1) Perform the changes in the "ion.mwb" file.
2) Run the "migration:make" Artisan command - "php artisan migrate:make change_description"
3) Copy the "ion.mwb" file, and rename it, using the automatically generated PHP script base filename.
4) Copy the previous "php" file contents into the new one (but preserve the generated CLASS name!) - and save the file.
5) By using the "Synchronize" tool, generate the "sql" script.
6) By loading the previous "mwb" file in Workbench and using the "Synchronize" tool again, generate the "_revert.sql" script.

Is this ok?

Revision history for this message
UndiFineD (k.dejong) said :
#2

this looks good to me.

there is a reason why i named all tables *_table
i would assume that certain things can be speed up by using views
so when we gather all the data they are placed in the table, but to show relevant data it can be retrieved from (daily) views

Can you help with this problem?

Provide an answer of your own, or ask J Bruni for more information if necessary.

To post a message you must log in.