SymfonyCasts stands united with the people of Ukraine
If you liked what you've learned so far, dive in!
Subscribe to get access to this tutorial plus
video, code and script downloads.
With a Subscription, click any sentence in the script to jump to that part of the video!
Login SubscribeWe 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!
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.
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!
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.
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.
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!
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"
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.
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!
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?
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/current/bundles/DoctrineMigrationsBundle/index.html#manual-tables
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!
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.
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!
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.
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
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!
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
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!
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
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.
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!
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!
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!
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!
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)
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!
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?
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!
// 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
}
}
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?