doctrine migrations bundle and postgres schema: diff does not work properly

Question

I'm using doctrine in my Symfony project, by connecting to an already existent postgres database.

The DB has several schemas, but the symfony app will use nothing but its own schema. The first Entity class I created is the following one:

namespace Belka\TestBundle\Entity;

use Doctrine\ORM\Mapping as ORM;

/**
 * @ORM\Entity
 * @ORM\Table(name="app_auth.User", schema="app_auth")
 */
class User {
    /**
     * @ORM\Column(type="string")
     * @ORM\Id
     * @ORM\GeneratedValue(strategy="NONE")
     */
    private $username;

    /**
     * @ORM\Column(type="string")
     */
    private $email;

    /**
     * @ORM\Column(type="string")
     */
    private $password;
}

as you can see, the Entity is specifying its own schema app_auth. Next, I tried to use the migrations bundle. So, I installed and configured it, in order not to consider anything but my schema:

Extract of config.yml:

doctrine:
    dbal:
        driver:   "%database_driver%"
        host:     "%database_host%"
        port:     "%database_port%"
        dbname:   "%database_name%"
        user:     "%database_user%"
        password: "%database_password%"
        charset:  UTF8
        schema_filter: ~^app_auth\..*~

Extract of config_dev.yml:

doctrine_migrations:
    dir_name: "%kernel.root_dir%/../.container/update/DoctrineMigrations"
    namespace: Application\Migrations
    table_name: "app_auth.migration_versions"
    name: Application Migrations

And I run the diff:

php app/console doctrine:migrations:diff

Unfortunately, the migration class generated is the following one:

namespace Application\Migrations;

use Doctrine\DBAL\Migrations\AbstractMigration;
use Doctrine\DBAL\Schema\Schema;

/**
 * Auto-generated Migration: Please modify to your needs!
 */
class Version20160422171409 extends AbstractMigration
{
    /**
     * @param Schema $schema
     */
    public function up(Schema $schema)
    {
        // this up() migration is auto-generated, please modify it to your needs

    }

    /**
     * @param Schema $schema
     */
    public function down(Schema $schema)
    {
        // this down() migration is auto-generated, please modify it to your needs
        $this->abortIf($this->connection->getDatabasePlatform()->getName() != 'postgresql', 'Migration can only be executed safely on \'postgresql\'.');

        $this->addSql('CREATE SCHEMA app_auth');
    }
}

what's wrong with my configuration then?

Grimonia

Which error message are you seeing ? – Comeaux

I don't get any error. It actually seems to ignore the entity. I created the table by myself and run the migrations:diff again: what I get is an empty up and a down with an DROP table in it – Grimonia

@Grimonia I am facing the same issue. I know your post is old, but did you find a solution ? – Cloudless

Nope, if I remember well I did it by hand. What about writing them on github? – Grimonia

I am still getting this happening. Symfony 5.1. – Knockknee

Maybe this thread can help in another way: How migrate to specific schema... – Willowwillowy

Answer 1

Dirty but working solution:

Tested with doctrine/migrations: 3.0.1

We can patch out the schema removing functionality of the DiffGenerator. I personally used this https://github.com/cweagans/composer-patches

Install the package, and then in composer.json "extra" section add:

"patches": {
        "doctrine/migrations": {
            "Change doctrine behavior to correctly pass PostgreSQL schema to schema_filter while diffing": "patches/doctrine-diff-generator.patch"
        }
    }

The patch file in patches/doctrine-diff-generator.patch:

--- lib/Doctrine/Migrations/Generator/DiffGenerator.php      2020-06-21 10:55:42.000000000 +0200
+++ lib/Doctrine/Migrations/Generator/DiffGenerator.patched.php      2020-12-23 12:33:02.689405221 +0100
@@ -142,8 +142,6 @@
  */
 private function resolveTableName(string $name) : string
     {
-        $pos = strpos($name, '.');
-
-        return $pos === false ? $name : substr($name, $pos + 1);
+        return $name;
     }
 }

Of course you have to bare in mind, that updates to doctrine could break the patch. And you are changing this functionality globally for your doctrine, so beware of breaking changes.

Personally, for my use case, which is adding doctrine entities into legacy database, without breaking it, this works nicely, and saves me from adding every new table managed by doctrine into the schema_filter.

Explanation: When we look into DiffGenerator implementation it actually decodes the table names with schema correctly, but only when collecting the current database schema. This happens in PostgreSqlSchemaManager.php:

    /**
 * {@inheritdoc}
 */
protected function _getPortableTableDefinition($table)
{
    $schemas     = $this->getExistingSchemaSearchPaths();
    $firstSchema = array_shift($schemas);

    if ($table['schema_name'] === $firstSchema) {
        return $table['table_name'];
    }

    return $table['schema_name'] . '.' . $table['table_name'];
}

But then then when calculating the target schema, this information is lost here in DiffGenerator.php:

    /**
 * Resolve a table name from its fully qualified name. The `$name` argument
 * comes from Doctrine\DBAL\Schema\Table#getName which can sometimes return
 * a namespaced name with the form `{namespace}.{tableName}`. This extracts
 * the table name from that.
 */
private function resolveTableName(string $name) : string
{
    $pos = strpos($name, '.');

    return $pos === false ? $name : substr($name, $pos + 1);
}

And only afterwards the return value of this function is passed into the schema_filter. Unfortunate, but i guess there was a reason for this :)

Spatial