Flag of Ukraine
SymfonyCasts stands united with the people of Ukraine

Migrations

Keep on Learning!

If you liked what you've learned so far, dive in!
Subscribe to get access to this tutorial plus
video, code and script downloads.

Start your All-Access Pass
Buy just this tutorial for $12.00

With a Subscription, click any sentence in the script to jump to that part of the video!

Login Subscribe

We have a beautiful new Question entity class that is supposed to map to a question table in the database. But... that table does not exist yet. How can we create it?

Well, because Doctrine has all of this configuration about the entity, like the fields and field types, it should - in theory - be able to create the table for us. And... it absolutely can!

Hello make:migration

The mechanism we use to make database structure changes is called migrations. At your terminal, run:

php bin/console make:migration

And... that fails:

Access denied for user db_user.

Of course: the command doesn't have access to the Docker environment variables. I meant to run:

symfony console make:migration

This time... cool! It generated a new file inside of a migrations/ directory. Let's go check it out! In migrations/ open the one new file and... awesome! It has an up() method with the exact SQL we need!

... lines 1 - 4
namespace DoctrineMigrations;
use Doctrine\DBAL\Schema\Schema;
use Doctrine\Migrations\AbstractMigration;
/**
* Auto-generated Migration: Please modify to your needs!
*/
final class Version20200707173854 extends AbstractMigration
{
public function getDescription() : string
{
return '';
}
public function up(Schema $schema) : void
{
// this up() migration is auto-generated, please modify it to your needs
$this->addSql('CREATE TABLE question (id INT AUTO_INCREMENT NOT NULL, name VARCHAR(255) NOT NULL, slug VARCHAR(100) NOT NULL, question LONGTEXT NOT NULL, asked_at DATETIME DEFAULT NULL, PRIMARY KEY(id)) DEFAULT CHARACTER SET utf8mb4 COLLATE `utf8mb4_unicode_ci` ENGINE = InnoDB');
}
public function down(Schema $schema) : void
{
// this down() migration is auto-generated, please modify it to your needs
$this->addSql('DROP TABLE question');
}
}

CREATE TABLE question...

and then all of the columns.

The make:migration command is smart: it compares the actual database - which has zero tables at the moment - with all of our entity classes - just one right now - and then generates the SQL needed to make the database match those entities.

It saw the one Question entity... but no question table, and so, it generated the CREATE TABLE statement.

Executing Migrations

But this query has not been executed yet. To do that, run:

php bin/console doctrine:migrations:migrate

Ah, but be careful: we can't use bin/console directly. Instead run:

symfony console doctrine:migrations:migrate

And... congratulations! We have a new question table in the database!

How Executed Migrations are Tracked

The way the migration system works is really cool. Run another command:

symfony console doctrine:migrations:list

This shows all the migrations in your app, which is just one right now. Next to that migration is says "Status Migrated". How does it know that?

Behind the scenes, the migration system created a table in the database called doctrine_migration_versions. Each time it executes a migration file, it adds a new row to that table that records that it was executed.

That means that later, if you run

symfony console doctrine:migrations:migrate

again... it's smart enough to not execute the same migration twice. It looks at the table, sees that it already ran this, and skips it.

When you deploy to production, you'll also run doctrine:migrations:migrate. When you do that, it will check the doctrine_migration_versions table in the production database and execute any new migrations.

Making a Column Unique

Before we keep going, you know what? When we created the Question entity, I forgot to do something. The slug column should really be unique in the database because we will eventually use that part of the URL to query for the one Question that matches.

One of the options you can pass to @ORM\Column() is unique=true.

... lines 1 - 10
class Question
{
... lines 13 - 24
/**
* @ORM\Column(type="string", length=100, unique=true)
*/
private $slug;
... lines 29 - 91
}

That won't change how our PHP code behaves - this doesn't relate to form validation or anything like that. This simply tells Doctrine:

Hey! I want this column to have a unique constraint in the database

Of course... just making this change did not somehow magically add the unique constraint to the database. To do that, we need to generate another migration.

Cool! At your terminal, once again run:

symfony console make:migration

to generate a second migration file. Let's go check it out.

... lines 1 - 4
namespace DoctrineMigrations;
use Doctrine\DBAL\Schema\Schema;
use Doctrine\Migrations\AbstractMigration;
/**
* Auto-generated Migration: Please modify to your needs!
*/
final class Version20200707174149 extends AbstractMigration
{
public function getDescription() : string
{
return '';
}
public function up(Schema $schema) : void
{
// this up() migration is auto-generated, please modify it to your needs
$this->addSql('CREATE UNIQUE INDEX UNIQ_B6F7494E989D9B62 ON question (slug)');
}
public function down(Schema $schema) : void
{
// this down() migration is auto-generated, please modify it to your needs
$this->addSql('DROP INDEX UNIQ_B6F7494E989D9B62 ON question');
}
}

And... woh! It's a CREATE UNIQUE INDEX statement for the slug column! The migrations system compared the question table in the database to the Question entity, determined that the only difference was a missing unique index and then generated the SQL to add it. Honestly, that's amazing.

Let's go run it:

symfony console doctrine:migrations:migrate

This sees both migrations, but only runs the one that hasn't been executed yet. The slug column is now unique in the database.

So this is the workflow: create a new entity or change an existing entity, run make:migration, and then execute it with doctrine:migrations:migrate. This keeps your database in sync with your entity classes and give you a set of migrations that you can run when you deploy to production.

Next: it's time to create some Question objects in PHP and see how we can save those to the question table.

Leave a comment!

27
Login or Register to join the conversation
Ali Z. Avatar

Hi,

After I add `unique=true` to slug column and executed `php bin/console make:migration` I received an error: The metadata storage is not up to date, please run the sync-metadata-storage command to fix this issue. How can I fix that?

Reply

Hey @Ali!

Hmmm - that is super weird. How is your DATABASE_URL configured? Are you using the Symfony binary + Docker setup like we're using in this tutorial? Or are you specifying it in .env (or .env.local)? If you're specifying in .env or .env.local, what does your DATABASE_URL look like? And what does your config/packages/database.yaml look like?

I'm not personally familiar with this error, but it looks like it's usually due to a missing "serverVersion" configuration. This is normally (but not always) configured as a ?serverVersion= on the end of your DATABASE_URL - for example https://github.com/symfony/...

Cheers!

Reply
Sherri Avatar

For anyone who, like me, skipped the Docker bits and is using a mysql database server running locally.... you might have a few errors with the console commands unless your db config is just right.

Worked for me - add to .env.local:

DATABASE_URL="mysql://db1user1:Password123@127.0.0.1/db1_databasename?serverVersion=5.7&charset=utf8"

Reply
Sherri Avatar

A port number after the localhost IP (127.0.0.1) was giving me trouble. Removing it, left it as the default and it worked.

Reply

Hey Sherri

I'm sorry you had to go thorugh all those problems there's where Docker shrines. Anyways, I'm glad to know you could fix the problems and move forward. Thanks for sharing your solution

Cheers!

Reply
Dirk Avatar

Hi Ryan!
We have created a couple of "manual" mariadb tables.
Each time we execute the "make:migration" and "doctrine:migrations:migrate" commands, the "manual created tables" are dropped. So each time we update the app and need to migrate, we have to edit the migration file to avoid losing all of the"manual created tables". Is there a way to avoid this?

Reply

Hey Dirk!

> So each time we update the app and need to migrate, we have to edit the migration file to avoid losing all of the"manual created tables". Is there a way to avoid this?

Yes, there is! Go into your config/packages/doctrine.yaml file and add a doctrine.dbal.schema_filter option:


doctrine:
dbal:
schema_filter: ~^(?!t_)~

This would ignore any tables prefixed with t_ - reference https://symfony.com/doc/cur...

So the only weird part is getting this regular expression correct: you are basically giving it a regular expression of all the tables that it *should* worry about (so that it filters "out" the manual ones). So I think something like ~^(?!(custom_table1|custom_table2))~ where you list your custom tables.

Let me know if that helps!

Cheers!

Reply
Dirk Avatar
Dirk Avatar Dirk | weaverryan | posted 1 year ago | edited

Hey weaverryan !
Yes it worked and I am very thankful for the help and the great tutorials. But this also creates a problem at a different point in the app where I use the schemaManager to list all table names unfortunately are the tables I need, the tables that are ignored by the schema_filter and don't get shown. This is bit problematic and I don't have a plan how I could fix it.

Reply

Hey Dirk!

Hmm. Yea, unfortunately, you can't have it "both ways": all parts of Doctrine use this schema_filter, including the schema manager. Here is what I would do, if you are ok getting a bit fancier ;).

A) Set the array of table names you want to ignore to a parameter:


# config/services.yaml
parameters:
extra_tables: [custom_table1, custom_table2]

B) Remove the schema_filter option. There is another way to do this that can use the parameter above. You would do this by leveraging a class from DoctrineBundle - you need to register it as a service:


# config/services.yaml
services:
# ...

doctrine.ignore_table_filter:
class: Doctrine\Bundle\DoctrineBundle\Dbal\BlacklistSchemaAssetFilter
arguments: ['%extra_tables%']
tags: ['doctrine.dbal.schema_filter']

This accomplishes the same as the schema_filter, just using a lower-level mechanism. I'm using it so that we can read the array extra_tables parameter. This is important because NOW, wherever you're using the SchemaManager in your code to list all the table names, I would inject this parameter and then return the full list of tables as all of the tables from SchemaManager + the tables in the extra_tables parameter.

Let me know if that makes any sense ;). Some code may not be exactly right - I'm just typing it here.

Cheers!

Reply
Dirk Avatar

This solution works perfectly but there is one thing that bothers me. I have to hard code all of the tables which are a lot and they could change or new ones get added. So I was thinking about using a prefix like in the schema_filter but I don't know if this is possible. Nevertheless I got a lot further with my project thanks to your help.

Reply
Boris Z. Avatar
Boris Z. Avatar Boris Z. | posted 1 year ago

Hi!
Could you please help me to understand how to configure migrations to work with multiple databases?
I have use two databases in my app, so I configured two connections, two EntityManagers that work each with its own entities. But I cannot understand how to configure migrations to execute different migrations for each database.
I have read symfony docs https://symfony.com/doc/cur... but still have no idea

Reply

Hey Boris Z. !

This isn't something I've needed to do before but... after some quick googling, this seems like a surprisingly big mess :/. It seems that version 3 of DoctrineMigrationsBundle seems to have dropped the support for using different connections or entity managers - https://github.com/doctrine... - which is a very unfortunate oversight.

And, fixing it in your app, is non-trivial. Fortunately, I found this post - https://jmsche.fr/en/blog/u... - which was exactly the type of thing I was thinking was necessary (but he already did the work!). Let me know if it helps.

Cheers!

1 Reply
Boris Z. Avatar
Boris Z. Avatar Boris Z. | weaverryan | posted 1 year ago | edited

weaverryan, hello again!
You asked me to write if it helps. And yes, it is)
I have made a proxy migrate command as in example and also a diff command in the same way. It was even easier than I expected

Reply

Woohoo! Thanks for following up!

> But I still don't understand why there is no native solution in migrations bundle

I think its removal was basically an accident. And now... it's hard to ask anyone to contribute it that doesn't need it (the maintainers of the bundle clearly don't have this use-case). So, someone just needs to do the work - that proxy command is probably a good guide ;).

Cheers!

Reply
Boris Z. Avatar
Boris Z. Avatar Boris Z. | weaverryan | posted 1 year ago | edited

weaverryan thank you very much! It looks like exactly the solution I was looking for. I'll try it and write here.
But I still don't understand why there is no native solution in migrations bundle. The only needed thing is to make possible to add multiple configurations to the migrations config

Reply
Sean D. Avatar
Sean D. Avatar Sean D. | posted 2 years ago

Are there any best practices for running migrations on a production server? I'm looking at publishing my first Symfony app that uses migrations and I'd appreciate any insights on running migrations as part of a build process. thanks.

Reply

Hey Sean,

Migrations are pretty straightforward... so probably the only rule - write good migrations ;) But serious, the most important part of executing migrations on production - to do not lose any existent data. That's super important... and that's something you should care about. After each generated migration - check it and make sure it's really safe for production data.

Also, if you have automated deploys - you will need to pass "--no-interaction" option to the command to avoid any interactive questions, otherwise it's step will just hang your deployment process.

And usually devs only care about queries in up() method and ignore ones in down(). If something will be incorrect in the migration - you would probably need to rollback manually, i.e. manually execute some queries to sync the DB.

That's probably all I can think about now. Hope it will help :)

Cheers!

Reply
Sean D. Avatar

Thanks for the input. I really appreciate it.

Reply

Hey Sean,

No problem, that's a good question to wonder about as nobody want to lose any real data on production ;) Btw, one more tip, just in case, create a backup of your production DB before deploying if you're not confident in your migrations.

Cheers!

Reply

Hello!

During one of the last doctrine update, my migrations table has been cleaned out. It only contains the last migrations I have made. Was ok until now as the migrations folder has been also changed (was under src/Migrations and now it's under /migrations). But now I need to install a new instance of my website. My new migrations folder miss all migrations files created before the doctrine update. So I copied them to it. But when I execute "make:migration", it told me that I have XX numbers of migrations to execute (all the copied migrations file) and that, because there are no entries in my migrations table about them. Is there a way to fix it? Or should I copy the database from the other website instance?

Thx!

Reply

Hey be_tnt!

Yes, I know this situation - the upgrade from migrations 2 to 3 is kind of annoying. So, first, you are absolutely sure that your migrations table has been cleaned out? I'm asking because, if i remember correctly (and I'm not sure if I am), the default name of the migrations table also changed during the upgrade. So it may be that your migrations table IS still there, but now Doctrine migrations is looking for a table with a different name. Double check that first.

Otherwise, you *can* manually fix it by copying the table from your existing instance or by manually inserting rows in that table for the old migrations. You shouldn't need to do this, because nothing should have cleaned out your migrations table, but in case something funny happened, it's definitely ok to do that :).

Cheers!

Reply

Thx for your quick answer.
I got 3 environments: local, dev and prod and for all of these, the "old" table for migrations has been deleted when upgrading :( Inserting rows manually could take a little but of time as I would need to add 64 records :o) I will check in my backup to see if I can find it.
Any other solutions? :o)

Reply

Hey be_tnt!

> the "old" table for migrations has been deleted when upgrading

That is super weird and unfortunate :/.

> will check in my backup to see if I can find it.

This is the easiest solution :). Fortunately, no data was lost... it's just an annoying thing to need to fix. I can think of 2 work arounds:

1) Locally, clean out your database, then run all of your migrations so that you can get a migrations_versions table (or whatever it's called on your version) that you can then copy up to production. A quick, hacky way to avoid 64 manual inserts.

2) You could delete all of the old migration files and start "from scratch". It would look something like this:

A) Checkout to an older branch that matches production.
B) Delete all the old migration files
C) Empty your database (so, zero tables)
D) Run make:migration

This will create a single migration file that contains all the SQL from the 64. You can then move that into your "main" branch and delete the other 64 (I would probably rename the new migration to a date that is older than any new ones).

So... let me know what you end up doing ;).

Cheers!

Reply

So I found out the old table in my backup, adapted the records with the new schema. Then I copied all my old migrations files (63 in fact) to the new migrations folder and finally execute make:migration to check

I got 2 warnings:
[WARNING] You have 63 available migrations to execute.
[WARNING] You have 63 previously executed migrations in the database that are not registered migrations.

Any idea?

Reply

ok I found it. The '\' between DoctrineMigrations and Version (Version column) was removed during the INSERT. I have put double '\' instead of a single one and now it works ! :)

Ex: INSERT INTO `doctrine_migration_versions` VALUES ('DoctrineMigrations\\Version20190704123309','2020-01-29 15:22:52',0);

Thx for your help!

Reply

Ah, good job be_tnt! Happy you've got things back and working :)

Cheers!

Reply
Cat in space

"Houston: no signs of life"
Start the conversation!

This tutorial also works great for Symfony 6!

What PHP libraries does this tutorial use?

// composer.json
{
    "require": {
        "php": "^7.4.1",
        "ext-ctype": "*",
        "ext-iconv": "*",
        "composer/package-versions-deprecated": "^1.11", // 1.11.99
        "doctrine/doctrine-bundle": "^2.1", // 2.1.1
        "doctrine/doctrine-migrations-bundle": "^3.0", // 3.0.2
        "doctrine/orm": "^2.7", // 2.8.2
        "knplabs/knp-markdown-bundle": "^1.8", // 1.9.0
        "knplabs/knp-time-bundle": "^1.11", // v1.16.0
        "sensio/framework-extra-bundle": "^6.0", // v6.2.1
        "sentry/sentry-symfony": "^4.0", // 4.0.3
        "stof/doctrine-extensions-bundle": "^1.4", // v1.5.0
        "symfony/asset": "5.1.*", // v5.1.2
        "symfony/console": "5.1.*", // v5.1.2
        "symfony/dotenv": "5.1.*", // v5.1.2
        "symfony/flex": "^1.3.1", // v1.17.5
        "symfony/framework-bundle": "5.1.*", // v5.1.2
        "symfony/monolog-bundle": "^3.0", // v3.5.0
        "symfony/stopwatch": "5.1.*", // v5.1.2
        "symfony/twig-bundle": "5.1.*", // v5.1.2
        "symfony/webpack-encore-bundle": "^1.7", // v1.8.0
        "symfony/yaml": "5.1.*", // v5.1.2
        "twig/extra-bundle": "^2.12|^3.0", // v3.0.4
        "twig/twig": "^2.12|^3.0" // v3.0.4
    },
    "require-dev": {
        "doctrine/doctrine-fixtures-bundle": "^3.3", // 3.4.0
        "symfony/debug-bundle": "5.1.*", // v5.1.2
        "symfony/maker-bundle": "^1.15", // v1.23.0
        "symfony/var-dumper": "5.1.*", // v5.1.2
        "symfony/web-profiler-bundle": "5.1.*", // v5.1.2
        "zenstruck/foundry": "^1.1" // v1.5.0
    }
}