Why Doctrine cannot see that the migration is already applied when creating a partial index?!

Just happened to me today.

It was a routine database migration, so I created Doctrine annotation, ran bin/console docrtine:schema:update --dump-sql and saw the SQL I need to copy/paste to the migration file.

However, after applying the migration Doctrine suprisingly insisted that the schema is not updated. It suggested to

• drop the created index
• re-create it with the same name and exactly the same settings

I re-run the migrations multiple times with the same effect. Doctrine was stubbornly thinking that the index is not created, and was suggested to create it with exactly the same settings as already applied in the database 🤦‍♂

And then here is an aha moment.

Apparently, something (PostgreSQL?) is messing with the index definition on the way to the db. In my case, I had this in the entity:

#[ORM\Index(fields: ['bankAccountNumber', 'bankRoutingNumber'], options: ["where" => "bank_account_number IS NOT NULL AND bank_routing_number IS NOT NULL"])]

But here is what ended up in the db (notice the parenthesises):

create index idx_9a828526fcadfe7c1518f
    on table (bank_account_number, bank_routing_number)
    where ((bank_account_number IS NOT NULL) AND (bank_routing_number IS NOT NULL));

So, Doctrine was looking at the attribute, then in the database schema and was trying to re-create the index to fix the difference (which is just parenthesises) — that’s why it didn’t like the result in the beginning.

Finally, the fix was just to add the parenthesises to the PHP side, so it equals the database side exactly:

#[ORM\Index(fields: ['bankAccountNumber', 'bankRoutingNumber'], options: ["where" => "((bank_account_number IS NOT NULL) AND (bank_routing_number IS NOT NULL))"])]