Orm: Set PostgreSQL function as default value

[darthf1]

Q: How to set native PostgreSQL function as default value?

For all my entities, I have setup both an ID and an UUID. My EntityIdTrait is setup like this:

<?php

namespace App\Entity;

use Doctrine\ORM\Mapping as ORM;
use Ramsey\Uuid\Uuid;
use Ramsey\Uuid\UuidInterface;

trait EntityIdTrait
{
    /**
     * @ORM\Id
     * @ORM\Column(type="integer", options={"unsigned": true})
     * @ORM\GeneratedValue(strategy="IDENTITY")
     */
    protected $id;

    /**
     * @ORM\Column(type="uuid", unique=true)
     */
    protected $uuid;

    public function getId(): ?int
    {
        return $this->id;
    }

    public function getUuid(): UuidInterface
    {
        if (is_string($this->uuid)) {
            $this->uuid = Uuid::fromString($this->uuid);
        }

        return $this->uuid;
    }
}

An ID is always generated because of the @ORM\GeneratedValue annotation. The UUID is created in the entities constructor:

use Ramsey\Uuid\Uuid;

public function __construct()
{
    $this->uuid = Uuid::uuid4()->toString();
}

This works great, if all your database data inserts are going through the ORM.

In my use case, I want to import CSV files to my database. Instead of parsing the file with PHP, I'm using the PostgreSQL Copy command through DBAL.

Unfortunately, when I do this i get the error that the UUID field should not be null. This is logical, because the default value for this column is not set in the database. Because the ORM does not allow for multiple @ORM\GeneratedValue.

For generating the UUID, PostgreSQL has the function gen_random_uuid(). I would like to set this function as a default variable in the Doctrine configuration, so the database schema is updated as well.

Option 1: Set the default variable through the 'options' annotation:

    /**
     * @ORM\Column(
     *     type="uuid",
     *     unique=true,
     *     options={"default"="gen_random_uuid()"}
     * )
     */
    protected $uuid;

Unfortunately this does not work. Because the default value only accepts a string, I cannot pass native PostgreSQL functions here.

Option 2: Define columnDefinition

    /**
     * @ORM\Column(
     *     type="uuid",
     *     columnDefinition="uuid NOT NULL default gen_random_uuid()"
     * )
     */
    protected $uuid;

The following SQL is generated:
CREATE TABLE xxx (id SERIAL NOT NULL, uuid uuid NOT NULL default gen_random_uuid(), PRIMARY KEY(id));

This leads to the expected result, as can be seen in the screenshot:

Right after the schema is updated through the console command, i ran php bin/console doctrine:schema:validate which gives me:

Mapping 
-------                                                                    
 [OK] The mapping files are correct.

Database
--------                                                                                                                        
 [ERROR] The database schema is not in sync with the current mapping file.       

The SQL which is generated is:
ALTER TABLE xxx ALTER uuid DROP DEFAULT;
So right after the schema is updated with the correct DEFAULT settings, Doctrine tells me the schema is not in sync and will immediately drop the default value.

Is there a way to tackle this?

PostgreSQL 11.3
Symfony 4.2.8
Doctrine/ORM 2.6.3
Doctrine/DBAL 2.9.2
PHP 7.3.5-1+ubuntu18.04.1+deb.sury.org+1 (cli) (built: May 3 2019 10:00:24) ( NTS )

[Ocramius]

Multi-column generated values is something that we planned to work on for ORM v3, but isn't there yet, as far as I know /cc @guilhermeblanco

[Ocramius]

@darthf1 as mentioned in https://github.com/doctrine/orm/issues/7215#issuecomment-479972721 (since you changed the title), auto-generated DB values are not supported by the ORM.

Something like options={"default"="gen_random_uuid()"} will only be reflected in the created schema, but one of the operational invariants of the ORM is that the data in the UnitOfWork and the inserted data are in sync. The one exception to this rule is with @GeneratedValue, which is only supported for identifiers (with restrictions based on the strategy).

Therefore, something like default=some_expression() will not be supported by ORM (and the ORM will break in unexpected ways when inserting data for these columns): please use a schema migration tool (doctrine/migrations, sqitch, etc) and apply this sort of customisation manually there.

Closing as won't fix here.

[darthf1]

Understand. Is there no other way to do this? E.g. with the columnDefinition (option 2 above).

[Ocramius]

Not really: we have hacks like CURRENT_TIMESTAMP hardcoded in DBAL, but I'd check DBAL first for the support of expressions in default values, which maybe @morozov can tell you something about (or link a previous decision)

[morozov]

IIRC, we decided to not add any additional support for expressions in DBAL because on the one hand, they are not portable, on the other, by using them you put the responsibility of producing data from the application to the database making the application harder to test (can't store an arbitrary value anymore) and error-prone (think of the time/timezone mismatch, etc). I don't have the link at hand.