CREATE SCHEMA public is added to all down migrations in Postgres

[speller]

Bug Report

Q	A
Version	3.8.3

Summary

I'm using the bundle with Postrgres 16. The database URL is postgresql://$DB_USER:$DB_PASSWORD@db:5432/$DB_NAME?serverVersion=16.2&charset=utf8. But on every doctrine:migrations:diff it creates migrations like the following:

final class Version20240311103945 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

    }

    public function down(Schema $schema): void
    {
        // this down() migration is auto-generated, please modify it to your needs
        $this->addSql('CREATE SCHEMA public');
    }
}

No matter what are other changes, it ALWAYS adds the $this->addSql('CREATE SCHEMA public'); line to all migrations.

How to fix it?

[maluramichael]

We got the exact same problem. Dont know how to work around it :/

[greg0ire]

Related to doctrine/dbal#5609 and doctrine/dbal#1110 I think.

[ramsey]

I'm having this problem, too. I've been manually removing the line because it causes errors when attempting to roll back to a previous migration.

[PawelSuwinski]

Reading related doctrine/dbal issues it seems that is no easy way to fix it on a lower lib level. Fix on one side causes some issues on the other, ex. filtering out 'public' scheme on PostgreSQLSchemaManager::listSchemaNames() make d:m:diff work well but brakes d:m:create (?). So in my case I decided to do a workaround by decorating one of the DiffGenerator dependency to just skip 'public' scheme during comparison. Using symfony it can be easily done by service configurator pattern.

# services.yaml
services:
    Doctrine\Migrations\Configuration\Migration\ConfigurationLoader: '@doctrine.migrations.configuration_loader'
    doctrine.migrations.dependency_factory:
        class: Doctrine\Migrations\DependencyFactory
        configurator: '@App\Migrations\DependencyFactoryConfigurator'

<?php

namespace App\Migrations;

use Doctrine\DBAL\Platforms\PostgreSQLPlatform;
use Doctrine\DBAL\Schema\Comparator;
use Doctrine\DBAL\Schema\PostgreSQLSchemaManager;
use Doctrine\DBAL\Schema\Schema;
use Doctrine\Migrations\DependencyFactory;
use Doctrine\Migrations\Generator\DiffGenerator;
use Doctrine\Migrations\Provider\EmptySchemaProvider;

class DependencyFactoryConfigurator
{
    public function __invoke(DependencyFactory $dependencyFactory)
    {
        $dependencyFactory->setDefinition(
            DiffGenerator::class,
            function () use ($dependencyFactory): DiffGenerator {
                $connection = $dependencyFactory->getConnection();
                $schemaManager = $connection->createSchemaManager();
                $platform = $connection->getDatabasePlatform();
                if ($platform instanceof PostgreSQLPlatform) {
                    $schemaManager = new class($connection, $platform) extends PostgreSQLSchemaManager {
                        public function createComparator(): Comparator
                        {
                            return new class($this->_platform) extends Comparator {
                                public function compareSchemas(Schema $fromSchema, Schema $toSchema)
                                {
                                    $schemaDiff = parent::compareSchemas($fromSchema, $toSchema);
                                    if (isset($schemaDiff->newNamespaces['public'])) {
                                        unset($schemaDiff->newNamespaces['public']);
                                    }
                                    return $schemaDiff;
                                }
                            };
                        }
                    };
                }
                return new DiffGenerator(
                    $connection->getConfiguration(),
                    $schemaManager,
                    $dependencyFactory->getSchemaProvider(),
                    $platform,
                    $dependencyFactory->getMigrationGenerator(),
                    $dependencyFactory->getMigrationSqlGenerator(),
                    new EmptySchemaProvider($schemaManager)
                );
            }
        );
    }
}

[greg0ire]

I've tried and failed to reproduce the issue with a brand new Symfony app: https://github.com/greg0ire/sample-pg-app

If you know how to do that, please send a PR.

Cc @stof since you mentioned this issue earlier today.

EDIT: issue "reproduced", I was using the wrong command 🤦

[greg0ire]

Fixed as of 3.8.2

Q	A
Version	3.8.3

@speller what did you mean by "3.8.3"? There isn't such a version, is there?

[gndk]

Fixed as of 3.8.2

@greg0ire Unfortunately it is not fixed, at least not for DBAL 3.

I tried to upgrade to ORM 3.x again today and ran into this (and #1406).

Relevant versions in my app:
Postgres 16.4

"doctrine/dbal": "3.9.3",
"doctrine/doctrine-bundle": "2.13.0",
"doctrine/doctrine-migrations-bundle": "3.3.1",
"doctrine/migrations": "3.8.2",
"doctrine/orm": "3.3.0",

I created a PR in your reproducer repo at greg0ire/sample-pg-app#2. Running bin/console doctrine:migrations:diff with DBAL 3 adds the CREATE SCHEMA public line. It does not with DBAL 4.

Please reopen the issue.

[greg0ire]

@gndk the fix only works for DBAL 4, so you'll have to upgrade that as well. As for #1406, I've provided a workaround that I think should be good enough (as in, it's not perfect but shouldn't block your migration to ORM 3)