ULID incorrectly stored as UUID in database (vanilla symfony application)

Question

Yesterday I tried to do what Symfony shouted out in some blog post (https://symfony.com/blog/new-in-symfony-5-2-doctrine-types-for-uuid-and-ulid) but failed. I want to store ULIDs (in the format "TTTTTTTTTTRRRRRRRRRRRR") in the database because not only are they sortable, but they also contain a timestamp which is perfect for my application. However, when I tell the attribute to be "type=ulid", it is stored in the database as a UUID (format: "xxxxxxxx-xxxx-Mxxx-Nxxx-xxxxxxxxxxxx").

I debugged it for a long time and was so annoyed by it that I started from scratch and the problem still exists.

Where did I go wrong?

(Skip to the ULID header if you wish, the following may seem long but 50% of it is just the basics)

symphony

Things I do over and over again Taken from https://symfony.com/doc/5.4/setup.html:

1. stat shyt # does not exist
2. composer Create project symfony/sculpture:5.4.* shyt
3. cd shyt; composer requires webapp
4. Do you want to include the Docker configuration in the recipe? Yes (default)
5. bin/console about Shows Symfony version 5.4.10 and PHP 7.4

ORM

Taken from https://symfony.com/doc/5.4/doctrine.html:

1. composer requires symfony/orm-pack
2. composer requires --dev symfony/maker-bundle
3. docker-compose up -d

Error: No available, non-overlapping IPv4 address pool found in the default address pool assigned to the network

So I added some lines in the docker-compose.override.yml file:

networks:
  default:
    ipam:
      config:
        - subnet: 10.1.2.1/24

services:
  database:
  # [...] doctrine/doctrine-bundle stuff [...]
  networks:
    default:
      ipv4_address: 10.1.2.3

4. Set DATABASE_URL in ".env" for host "10.1.2.3"
5. bin/console Doctrine: database: create (silly, but as documented)

Cannot create database "app" for the connection named default Exception occurred while executing query: SQLSTATE[42P04]: Duplicate database: 7 Error: Database "app" already exists

um, yes. Docker already does this.

6. make:entity is deferred until we have the ULID functionality.

ULID

We've leaned towards https://symfony.com/doc/5.4/components/uid.html (especially the ULID part):

1. composer requires symfony/uid
2. bin/console make:entity Product

• "someProperty" as "ulid" is not nullable

3. Check Product Entity

Looks almost the same as in the documentation, except it has an additional field (primary key, an integer) and some getters/setters.

4. bin/console make:migration

Testing ULID Entities

In between, we use tests to create database entries programmatically:

1. composer requires phpunit Create database entries programmatically
2. bin/console --env=testism:migrations:migrate
3. bin/console --env=testism:database:create
4. The file "tests/FooTest.php" contains:

<?php

namespace AppTests;

use AppEntityProduct;
use AppRepositoryProductRepository;
use DoctrineORMEntityManager;
use SymfonyBundleFrameworkBundleTestKernelTestCase;
use SymfonyComponentUidUlid;

class FooTest extends KernelTestCase
{
    public function testFoo(): void
    {
        $product = new Product();
        $product->setSomeProperty(new Ulid());
        static::assertNotNull($product->getSomeProperty());

        self::getContainer()->get(ProductRepository::class)
            ->add($product);

        self::getContainer()->get('doctrine.orm.entity_manager')
            ->flush();
    }
}

5. bin/console --env=test ism:query:sql 'TRUNCATE Product' Just to be sure
6. bin/phpunit
7. bin/console --env=testism:query:sql 'SELECT * FROM product'

Show UUID, not ULID.

Display ULID instead of UUID in database

Use ULID as primary key

Clean up first, then execute the example shown at https://symfony.com/doc/5.4/components/uid.html#ulids:

1. rm migrate/* Start over
2. bin/console --env=testism:database:drop --force
3. bin/console --env=testism:database:create
4. bin/console-ism:database:drop --force
5. bin/console --env=testism:database:create
6. Edit "src/Entity/Product.php" to include only the second ULID in the document Example:

<?php

namespace AppEntity;

use DoctrineORMMapping as ORM;
use SymfonyComponentUidUlid;
use AppRepositoryProductRepository;

/**
 * @ORMEntity(repositoryClass=ProductRepository::class)
 */
class Product
{
    /**
     * @ORMId
     * @ORMColumn(type="ulid", unique=true)
     * @ORMGeneratedValue(strategy="CUSTOM")
     * @ORMCustomIdGenerator(class="doctrine.ulid_generator")
     */
    private $id;

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

    // ...

}

(The example in the documentation is missing the repository line)

7. bin/console make:migration
8. bin/console --env=testism:migrations:migrate
9. Testing is now easier:

public function testFoo(): void
    {
        self::getContainer()->get(ProductRepository::class)
            ->add(new Product());

        self::getContainer()->get('doctrine.orm.entity_manager')
            ->flush();
    }

10. bin/phpunit(It’s okay if there is risk)
11. bin/console --env=testism:query:sql 'SELECT * FROM product'

Use UUID again instead of ULID

Database shows UUID instead of ULID

Answer 1

A bit late, but for anyone facing this problem, I also have databases that show UUIDs instead of ULIDs, which seems to be the expected behavior in Doctrine since you can use UUIDs/ULIDs interchangeably, meaning even You store UUID in database but your entities are mapped to ULID, when retrieving object from database you will have ULID, you can also retrieve the same object using ULID or UUID.

For example, I have a user entity whose identifier has a ULID, so the stored object will have a uuid like this:

018477e6-eebc-164c-12e3-22ca8f1a88f3    myemail@mail.com    []

If I retrieve my user using that UUID I will get:

App\Entity\User {#430 ▼
 -id: "01GHVYDVNW2S615RS2SA7HN27K"
 -email: "myemail@mail.com"
 -roles: []
 #createdAt: DateTime @1668458933 {#423 ▶}
 #updatedAt: DateTime @1668458998 {#428 ▶}
 -password: "y$/2i9Ovc2lCQBRfSVgsnmoul1FhF.Kyki3irF6GQvrMrjacQX6ees6"
 -isVerified: true
}

Now if you use that ULID to retrieve your user, it will work too!

If you check the UUID, you will find that the returned object has the uuid converted to base 32:

bash-5.1$ bin/console uuid:inspect 018477e6-eebc-164c-12e3-22ca8f1a88f3
----------------------- --------------------------------------
Label                   Value
----------------------- --------------------------------------
Version                 1                                     
  toRfc4122 (canonical)   018477e6-eebc-164c-12e3-22ca8f1a88f3  
  toBase58                1BsMRvKcgozP4Kw2m4Fb1C                
  toBase32                01GHVYDVNW2S615RS2SA7HN27K
----------------------- --------------------------------------

Finally, you can get the stored uuid by converting it to refc4122 like this:

bin/console ulid:inspect 01GHVYDVNW2S615RS2SA7HN27K
---------------------- --------------------------------------
Label                  Value
---------------------- --------------------------------------
toBase32 (canonical)   01GHVYDVNW2S615RS2SA7HN27K
toBase58               1BsMRvKcgozP4Kw2m4Fb1C
toRfc4122              018477e6-eebc-164c-12e3-22ca8f1a88f3
---------------------- --------------------------------------
Time                   2022-11-14 20:48:53.948 UTC
---------------------- --------------------------------------

I'm not sure why Principle doesn't just store ULIDs, but its current behavior doesn't prevent you from using ULIDs in your project. Hope this helps!