Buy

Database Migrations

Google for DoctrineMigrationsBundle. To install it, copy the composer require line. But again, we don't need to have the version - Composer will find the best version for us:

composer require doctrine/doctrine-migrations-bundle

While Jordi is preparing that for us, let's keep busy. Copy the new statement from the docs and paste that into the AppKernel class:

56 lines app/AppKernel.php
... lines 1 - 5
class AppKernel extends Kernel
{
public function registerBundles()
{
$bundles = array(
... lines 11 - 20
new Doctrine\Bundle\MigrationsBundle\DoctrineMigrationsBundle(),
... lines 22 - 23
);
... lines 25 - 33
}
... lines 35 - 54
}

Beautiful!

We already know that the main job of a bundle is to give us new services. But this bundle primarily gives us something different: a new set of console commands. Run bin/console with no arguments:

./bin/console

Hiding in the middle is a whole group starting with doctrine:migrations. These are our new best friend.

The Migrations Workflow

Our goal is to find a way to safely update our database schema both locally and on production.

To do this right, drop the database entirely to remove all the tables: like we have a new project.

./bin/console doctrine:database:drop --force

This is the only time you'll need to do this. Now, re-create the database:

./bin/console doctrine:database:create

Now, instead of running doctrine:schema:update, run:

./bin/console doctrine:migrations:diff

This created a new file in app/DoctrineMigrations. Go open that up:

29 lines app/DoctrineMigrations/Version20160207083131.php
... lines 1 - 10
class Version20160207083131 extends AbstractMigration
{
public function up(Schema $schema)
{
// this up() migration is autogenerated, please modify it to your needs
$this->abortIf($this->connection->getDatabasePlatform()->getName() != "mysql");
$this->addSql("CREATE TABLE genus (id INT AUTO_INCREMENT NOT NULL, name VARCHAR(255) NOT NULL, sub_family VARCHAR(255) NOT NULL, species_count INT NOT NULL, fun_fact VARCHAR(255) NOT NULL, PRIMARY KEY(id)) DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci ENGINE = InnoDB");
}
public function down(Schema $schema)
{
// this down() migration is autogenerated, please modify it to your needs
$this->abortIf($this->connection->getDatabasePlatform()->getName() != "mysql");
$this->addSql("DROP TABLE genus");
}
}

Check this out: the up() method executes the exact SQL that we would have gotten from the doctrine:schema:update command. But instead of running it, it saves it into this file. This is our chance to look at it and make sure it's perfect.

When you're ready, run the migration with:

./bin/console doctrine:migrations:migrate

Done! Obviously, when you deploy, you'll also run this command. But here's the really cool part: this command will only run the migration files that have not been executed before. Behind the scenes, this bundle creates a migrations_versions table that keep strack of which migration files it has already executed. This means you can safely run doctrine:migrations:migrate on every deploy: the bundle will take care of only running the new files.

Tip

You can run migration in reverse in case something fails. Personally, I never do this and I never worry about down() being correct. If you have a migration failure, it's a bad thing and it's better to diagnose and fix it manually.

Making Columns nullable

In newAction(), I'll add some code that sets fake data on the subFamily and speciesCount properties. But, I'll keep funFact blank: maybe some genuses just aren't very fun:

76 lines src/AppBundle/Controller/GenusController.php
... lines 1 - 11
class GenusController extends Controller
{
... lines 14 - 16
public function newAction()
{
$genus = new Genus();
$genus->setName('Octopus'.rand(1, 100));
$genus->setSubFamily('Octopodinae');
$genus->setSpeciesCount(rand(100, 99999));
... lines 23 - 28
}
... lines 30 - 74
}

Ok, head over to /genus/new to try it out! Woh, a huge explosion!

Integrity constraint violation: 1048 Column fun_fact cannot be null

Here's the deal: Doctrine configures all columns to be required in the database by default. If you do want a column to be "nullable", find the column and add nullable=true:

81 lines src/AppBundle/Entity/Genus.php
... lines 1 - 10
class Genus
{
... lines 13 - 34
/**
* @ORM\Column(type="string", nullable=true)
*/
private $funFact;
... lines 39 - 79
}

Creating Another Migration

Of course, just because we made this change doesn't mean that our table was automatically updated behind the scenes. Nope: we need another migration. No problem! Go back to the terminal and run:

./bin/console doctrine:migrations:diff

Open up the new migration file: ALTER TABLE genus CHANGE fun_fact to have a default of null. This look perfect. Run it with:

./bin/console doctrine:migrations:migrate

So easy! Refresh the page again: no errors. Migrations are awesome.

Leave a comment!

  • 2016-10-30 Max

    Well, that sounds reasonable :) Thank you very much!

  • 2016-10-30 weaverryan

    Yo Max!

    Yea, amazingly, under many (but not all) situations, Doctrine DOES turn notice the renaming and turn it into a CHANGE query. That's pretty cool :). So, you're 100% right about doctrine:migrations:diff and doctrine:schema:update being equally perfect or equally flawed in the SQL they generate: if one somehow generates some SQL that isn't quite right, then they both will generate that (they generate the SQL using the same engine).

    The big advantage of migrations is when you deploy your app to production *and* when you have those not-so-common cases where the generated SQL isn't perfect. Let me give you an example: suppose I'm adding a new column to Event, e.g. slug (URL-safe, unique string), and I give it unique=true. The generated SQL for that will look totally fine. But when I run it, it'll likely fail: if there are any Event objects already in the database, then they will all be given a blank slug... which of course isn't unique :). The fix, would be no:

    A) NOT make the field unique, and generate a migration for just the new (non-unique field)
    B) Add a second migration by hand (or update the first) that does some sort of a *data* migration - calculating and setting unique slug values on each row
    C) Make the slug field unique again, and generate a migration for *just* the unique change

    Now, if you deploy to production and run doctrine:migrations:migrate, that entire, validated & safe history is replayed. Obviously, that can't be done with doctrine:schema:update.

    I know people that deployed and used doctrine:schema:update on production for a long time. It will work 99% of the time, but eventually it won't :). The big difference with migrations is that it actually allows you to *change* the SQL to fix whatever problem it has, and doctrine:schema:update doesn't give us that chance.

    Cheers!

  • 2016-10-28 Max

    Hey Victor!

    Thank you very much for your reply. How exactly are migrations solving that problem? When I run doctrine:migrations:diff the SQL says (after adding a new property and changing an old one to a new name):

    $this->addSql('ALTER TABLE yoda_event ADD test VARCHAR(255) NOT NULL, CHANGE details dummy LONGTEXT NOT NULL');

    doctrine:schema:update --dump-sql creates the same statement:

    ALTER TABLE yoda_event ADD test VARCHAR(255) NOT NULL, CHANGE details dummy LONGTEXT NOT NULL;

    So besides of creating a history of my database changes, where is the difference of running a checked (via --dump-sql ) doctrine:schema:update and a migration checked via looking at the php?? A doctrine:migrations statement could be equally flawed as a doctrine:schema:update statement, so both could possibly "destroy" parts of my production database.

  • 2016-10-28 Victor Bocharsky

    Hey Max,

    Migrations allows you to automate updating DB in production safely (of course, you should double check all the new migrations to ensure that they contain safe instructions). The most common example I know is that you can rename entity's property, i.e. rename column in DB. But if you blindly run doctrine:schema:update - Doctrine will just add a new column and drop existent one, i.e. you will lose your data! Probably, It's not important for development - you can just reload your fixtures again, but it's very critical for production. And migrations solve this problem for you. I mean, doctrine:schema:update command isn't perfect, and sometimes you need to do some extra work. So if you write a good migration - you can safely execute it on production server without losing your data.

    What about downgrading - it's a very rare cases. Btw, we don't care much about it on KnpU. If something went wrong - we manually execute some SQL commands to return DB in the previous state based on upgrading commands and using "doctrine:schema:update --dump-sql" for help. But probably in some projects using downgrading makes sense, especially if project under the active development and has a big team.

    I hope it clearer for you now.

    Cheers!

  • 2016-10-26 Max

    Hey!
    Actually I don' really got the point why using migrations is so useful... Just for seeing the SQL code before you run it could be solved differently without a external file.
    Furthermore you say that it just runs the newly generated migration files. But wouldn't do doctrine:schema:update do basically the same - applying only my NEW changes (as the old ones are already mirrored in the database) using the various ORM annotations to the database by looking at it, substracting the status quo from the entity-annotation-status and showing my the necessary changes in SQL language?

    I got the feeling that the migrations create something like an version control system for the database schema as there's also the down function in the migration file. But is there a real-life scenario where you would use that - downgrading your database to a previous state?

    As always: thanks in advance!

  • 2016-10-19 Shairyar Baig

    Many thanks Ryan, I tried this and it seems to work pretty well.

  • 2016-10-16 weaverryan

    Hey Shairyar!

    Yep, that's exactly right! The Doctrine migrations library keeps a migration_versions table in your database (both locally and on production - basically inside any database where the app is run) that keeps track of what migrations were and were not run. So, the flow looks like this:

    1) [Local] Make some entity changes
    2) [Local] Run doctrine:migrations:diff to create the new file in app/DoctrineMigrations/
    3) [Local] Run doctrine:migrations:migrate to execute that file locally and record that this migration file has been executed in the migration_versions table (of your local database).

    ... then eventually you copy ALL files, including the migration files to production. At this point, suppose that you have actually created *3* new migration files since you last deployed your files.

    4) [Production] Run doctrine:migrations:migrate. This will look at the migration_versions table on your *production* database and notice that there are *3* new files in your app/DoctrineMigrations directory that have not been executed against that database yet. Then, it will execute those 3 migrations and record 3 new rose in the migration_versions table.

    So, really, running doctrine:migrations:migrate should just be part of your deploy process - I usually do it right after copying my files and clearing cache.

    Cheers!

  • 2016-10-16 Shairyar Baig

    Hi Ryan, I am a bit confused.

    I have two copies of the code, one sitting locally on my laptop and another a production copy which is uploaded via ftp sitting on a server. So if I run the command /bin/console doctrine:migrations:diff on my laptop this will create a migration file locally, do I then upload that file on the server and run the command /bin/console doctrine:migrations:migrate there?

  • 2016-09-25 weaverryan

    Hey Pete!

    Over the weekend, I finally discovered the problem: a backwards-compatibility break in PHP 7.1. Some of the guys in charge of these very libraries that are involved are working to try to revert it before 7.1 is fully released.

    Here's more info: https://github.com/php/php-src.... And yes, there literally is a ? being added somewhere internally, and it's (at least in theory) by design.

    Cheers!

  • 2016-09-24 Peter Stephens

    "composer upgrade" did upgrade a few packages, but did not fix the problem. I downgraded to PHP 7.0 and it works fine. I will stick with that as I have no real reason to use PHP 7.1. Thanks for the tips. I learned a lot.

    - Pete

  • 2016-09-21 Victor Bocharsky

    Hm, probably I was wrong about package, Could you try to update all your dependencies to the latest versions with: $ composer update?

  • 2016-09-21 Peter Stephens

    Looks like I am already at 3.0.4

    $ composer show -i | grep -i zend
    You are using the deprecated option "installed". Only installed packages are shown by default now. The --all option can be used to show all packages.
    zendframework/zend-code 3.0.4 provides facilities to generate arbitrary code using an object oriented interface
    zendframework/zend-eventmanager 3.0.1 Trigger and listen to events within a PHP application

  • 2016-09-21 Peter Stephens

    Oh cool! I will try that. Thanks Victor.

  • 2016-09-20 Victor Bocharsky

    Hey Peter,

    IIRC, it was a bug which fixed in the newest version of this library. What version of zendframework/zend-code do you use? Please, try to update zendframework/zend-code to the latest available version. I see the latest version is 3.0.4 now. You could try to update only this package with next command in console:


    $ composer update zendframework/zend-code

    Cheers!

  • 2016-09-20 Peter Stephens

    Thanks for tip, but no dice. I have PHP7.1 on Ubuntu. It probably has to do with that version as the other post states.

    I did find the regex expression in the file, "vendor/zendframework/zend-code/src/Generator/TypeGenerator.php" and can see that it is from the "fromTypeString" function. I can also see that "fromTypeString" is getting called from "setType" in "vendor/zendframework/zend-code/src/Generator/ParameterGenerator.php". I kinda lose it from there.

    Do you think the "question mark" at the beginning of the dump is an indicator or what it is actually complaining about? I went to "http://www.phpliveregex.com/" and used the "preg_match" to look at the regex and it matches "Doctrine\DBAL\Schema\SchemaConfig", but not "?Doctrine\DBAL\Schema\SchemaConfig".

    - Pete

  • 2016-09-19 weaverryan

    Hey Peter!

    Wow, I don't understand that either! And when I googled for it, I could *also* only find that same *one* like on the Internet about this :/. I'm not sure if it matters, but what version of PHP are you running?

    I would try this one thing: delete your entire vendor/ directory and use composer to re-install. You should *not* need to do this... but this is a really strange error :).


    rm -rf vendor/
    composer install

    Let me know if that works, or if there's anything else that may be non-traditional about your setup.

    Cheers!

  • 2016-09-18 Peter Stephens

    Hey guys, When I try to run "php bin/console doctrine:migrations:migrate" I get,

    Migrating up to 20160918154810 from 0
    Migration 20160918154810 failed during Pre-Checks. Error Provided type "?Doctrine\DBAL\Schema\SchemaConfig" is invalid: must conform "/^[a-zA-Z_\x7f-\xff][a-zA-Z0-9_\x7f-\xff]*(\\[a-zA-Z_\x7f-\xff][a-zA-Z0-9_\x7f-\xff]*)*$/"

    [Zend\Code\Generator\Exception\InvalidArgumentException]
    Provided type "?Doctrine\DBAL\Schema\SchemaConfig" is invalid: must conform "/^[a-zA-Z_\x7f-\xff][a-zA-Z0-9_\x7f-\xff]*(\\[a-zA-Z_\x7f-\xff][a-zA-Z0-9_\x7f-\xff]*)*$/"

    doctrine:migrations:migrate [--write-sql] [--dry-run] [--query-time] [--allow-no-migration] [--configuration [CONFIGURATION]] [--db-configuration [DB-CONFIGURATION]] [--db DB] [--em EM] [--shard SHARD] [-h|--help] [-q|--quiet] [-v|vv|vvv|--verbose] [-V|--version] [--ansi] [--no-ansi] [-n|--no-interaction] [-e|--env ENV] [--no-debug] [--] <command> [<version>]

    I did find this, http://stackoverflow.com/quest...

    However I don't really understand what the writer is implying.

  • 2016-08-12 claire

    Thankyou!!
    It worked and thank you again for explaining what was happening when you run composer. Really interesting to find out about this feature 'incenteev parameter handler'.

    Claire

  • 2016-08-11 weaverryan

    Hi Claire!

    Ok, let's figure this out :). First, the error isn't coming exactly from running composer require. But instead, after compose require finishes running, Symfony does some final clean up (e.g. it clears its own cache). When *this* is happening, we're getting this error.

    And at first, the reason is really simple: somewhere (probably config.yml) you are referencing %database_path%. But, this is *missing* from your parameters.yml file. It's very likely that you *did* have database_path in parameters.yml, until you ran composer require. You see, there is an extra process that runs whenever you run composer require/update/install. This process - called the "incenteev parameter handler" - compares your parameters.yml.dist file and your parameters.yml files. If you are missing something from your parameters.yml file (as compared to the .dist file) it will ask you for a value and fill it in for you. BUT, if you have some *extra* keys in parameters.yml (like database_path) that you forgot to add to your parameters.yml.dist file, it will actually remove them from parameters.yml! I've always disliked that feature, but I bet this is what's happening.

    The fix:

    - re-add database_path to your parameters.yml file
    - also add database_path to parameters.yml.dist (it can be set to any value - it just needs to be there).

    The next time you use Composer, it won't remove your database_host parameter. Btw, you can see where this "magic" parameters handler thing is registered - if you look at your composer.json file, under post-install-cmd/post-update-cmd section: https://github.com/symfony/sym....

    Let me know if that fixes it! Cheers!

  • 2016-08-11 claire

    Hi Ryan,

    I am trying to install the require doctrine/doctrine-migrations-bundle. However I keep getting this back : [Symfony\Component\DependencyInjection\Exception\ParameterNotFoundException]

    You have requested a non-existent parameter "database_path". Did you mean one of these: "database_host", "database_port", "database_name", "database_user"?

    I've check my parameters.yml.dist and parameters.yml and neither have the key of database_path. Not really sure where the error is coming from.

    Thanks

  • 2016-07-04 Dan Costinel

    Still some months left for my current deal with the hosting company. And yeah, I'm taking into consideration to change the hosting provider. I thought there might be other solutions to my problem... Anyways, thanks for the tip.

  • 2016-07-04 Victor Bocharsky

    Hey, Dan!

    If you don't have access to the console command - change your hosting provider ;)

    If seriously, you don't have many options here. You should run migration commands manually. You probably should have access to the PhpMyAdmin or credentials to connect to the database with MySQL Workbench. But in this case it's difficult to control it due to the human factor, you may miss something, which will cause an error. So the better option is to change your hosting provider.

    Cheers!

  • 2016-07-03 Dan Costinel

    Hi Ryan,
    Little question: did you ever had a situation when you wanted to deploy a Symfony app into a server which doesn't provide a way to run console commands? What was your approach in that case? Because you say that whenever we need to deploy we need to run doctrine:migrations:migrate too... And I need to deploy a Symfony app without being able to run console commands

  • 2016-03-21 weaverryan

    Hi Hikaru!

    As far as I know, that's not possible: but it's by design. Since there is only *one* database, it's better to install a bundle then just run 'doctrine:migrations:diff' to generate whatever migrations you need from that bundle. I think the authors of DoctrineMigrationsBundle were just worried about installing a bundle, running doctrine:migrations:migrate and suddenly something has altered your production database - maybe without you realizing :).

    Cheers!

  • 2016-03-18 Hikaru Shindo

    Is it possible to save migrations to the bundles they belong to in case you may have more then one bundle with entities and do not want to recreate the migrations on each project?