ResetDatabaseTrait does not work with constraints

[henryfoster]

I have a unique constraint that is deferred on a column which means the constraint is applied only when the transaction is committed. When using it with the ResetDatabase the unique constraint is triggered before the transaction is committed which means for some reason it ignores the "DEFERRABLE INITIALLY DEFERRED" .

SELECT conname, condeferrable, condeferred FROM pg_constraint WHERE conname = 'my_unique_constraint';

A strange thing I noticed is that after creating and running migrations on the test database the constraint is there but as soon as the test with ResetDatabase trait starts it disappears even tho the error unique constraint violation shows exactly the correct constraint name but I can't find it.

My guess is that the constraint is created some where else by ResetDatabase but there is some bug that "DEFERRABLE INITIALLY DEFERRED" is not added correctly.

This makes it impossible to test "swapping" unique values of two rows.

[nikophil]

Hi @henryfoster

I'm not sure to fully grasp the problem 🤔

Do you use migrate or schema strategy to restore your database? How is your index declared? I was thinking Doctrine did not handle such indexes.

Would you mind to create a small reproducer, so that I can understand what's going on?

[henryfoster]

Sure thing I will create a minimal reproducer today or tomorrow. 👍

[henryfoster]

@nikophil Hi, sorry it took so long ):

https://github.com/henryfoster/reproducer

Here is the reproducer. I added at the top of the readme all steps to run it. I hope it helps. Let me know if you have any questions.

[nikophil]

thanks for the reproducer

sorry it took so long

no problem :)

I'll have a look soon

[nikophil]

hello @henryfoster

sorry for the delay of my answer 😅

Doctrine does not understand options: ['DEFERRABLE' => 'INITIALLY DEFERRED']. Thus, I believe that you've added maunally the statement DEFERRABLE INITIALLY DEFERRED to your migration.

In your reproducer, Foundry is not configured to use your migration, so the ResetDatabase trait uses bin/console doctrine:schema:update. See which SQL statements Doctrine generates with your mapping, using schema update command:

$ docker compose exec -T php bin/console doctrine:database:create -e test
Created database "app_test" for connection named default

$ docker compose exec -T php bin/console doctrine:schema:update -e test --dump-sql
CREATE TABLE "order" (id SERIAL NOT NULL, name VARCHAR(255) DEFAULT NULL, position INT NOT NULL, PRIMARY KEY(id));
CREATE UNIQUE INDEX unq_order_position ON "order" (position);

The deferable option is simply ignored.

The solution would be to configure Foundry to use your migrations to generate the schema:

# config/packages/zenstruck_foundry.yaml
zenstruck_foundry:
    orm:
        reset:
            mode: migrate

you'll find more info here https://symfony.com/bundles/ZenstruckFoundryBundle/current/index.html#resetting-using-migrations

I'm closing this issue. Feel free to reopen it if this does not fix your problem.