move UUID from PHP to database native

[arhimede]

Feature Request

Q	A
New Feature	yes
RFC	no
BC Break	yes

Summary

What if we move the generating of the UUID from code (PHP) to database ?
that way we can reduce the number of dependencies

in mariadb 11.4 was introduced UUD as generic data type
in mariadb 11.8 there are already , UUID, UUIDv4 and UUIDv7

we may want to allow the developer to choose between UUID version ?

MariaDB [(none)]> SELECT UUID(),UUID_v4(),UUID_v7()\G
*************************** 1. row ***************************
UUID(): f0aecde0-bbc6-11f0-8a5e-00155d3a3010
UUID_v4(): 35cbfd00-876e-4679-a2b1-5d0de3209922
UUID_v7(): 019a5dec-4457-7334-bd3e-6bfeae6b736b

[alexmerlin]

@arhimede

Two ideas that came to my mind during the UUID refactoring, not sure if we want to implement any of them:

Choose entity identifiers

We could have multiple traits / abstract entities implementing different types of identifiers:

• integers
• native UUIDs
• Ramsey UUIDs

Some of them (ramsey UUID) would interfere though with our planned PostgreSQL implementation, so I'm not sure we should proceed with all of them.

Rename entity identifiers

We should rename the identifier from uuid to the default id because if an entity would use an identifier other than UUID, the name uuid would not make sense anymore.

---

Note

Each of these modifications would require adapting DotMaker as well.

[arhimede]

Colective decision after a TSC meeting is:

• rename all over the place uuid into id
• modify the code accordingly obviously
• use traits instead of abstract entities

Note

In a project can be used a mix of id types . Integers, various versions of auto-generated uuid
Also we will publish instruction how to integrated Ramsey's uuid library and use binary type of data
Traits will be implemented for id as integers and uuid as built in data type.

[alexmerlin]

@arhimede

Small hiccup:

I finished the $uuid -> $id refactoring and the ramsey-uuid to native UUID migration in the code.
I can generate migration and they execute properly.
The issue is when I try to execute the fixtures (or do any INSERT across the application):
Depending on the column settings used, Doctrine either doesn't even try to execute an insert or if it tries if fails because the database does not return an identifier so Doctrine considers it a failure.

Testing strategies

I tried the strategies currently available in Doctrine as per their docs.

Using strategy: AUTO

quoting Doctrine:

AUTO means the generator type will depend on what the used platform prefers.
Offers full portability.

    #[ORM\Id]
    #[ORM\Column(name: 'id', type: 'uuid', unique: true, nullable: false)]
    #[ORM\GeneratedValue(strategy: 'AUTO')]

Error:

Entity of type Core\Admin\Entity\AdminRole is missing an assigned ID for field 'id'. The identifier generation strategy for this entity requires the ID field to be populated before EntityManager#persist() is called. If you want automatically generated identifiers instead you need to adjust the metadata mapping accordingly.

Using strategy SEQUENCE

quoting Doctrine:

SEQUENCE means a separate sequence object will be used.
Platforms that do not have native sequence support may emulate it.
Full portability is currently not guaranteed.

    #[ORM\Id]
    #[ORM\Column(name: 'id', type: 'uuid', unique: true, nullable: false)]
    #[ORM\GeneratedValue(strategy: 'SEQUENCE')]

Error:

Operation "Doctrine\DBAL\Platforms\AbstractPlatform::getSequenceNextValSQL" is not supported by platform.

Using strategy: IDENTITY

quoting Doctrine:

IDENTITY means an identity column is used for id generation.
The database will fill in the id column on insertion.
Platforms that do not support native identity columns may emulate them.
Full portability is currently not guaranteed.

    #[ORM\Id]
    #[ORM\Column(name: 'id', type: 'uuid', unique: true, nullable: false)]
    #[ORM\GeneratedValue(strategy: 'IDENTITY')]

Error:

No identity value was generated by the last statement.

Using strategy: NONE

quoting Doctrine:

NONE means the class does not have a generated id.
That means the class must have a natural, manually assigned id.

    #[ORM\Id]
    #[ORM\Column(name: 'id', type: 'uuid', unique: true, nullable: false)]
    #[ORM\GeneratedValue(strategy: 'NONE')]

Error:

Entity of type Core\Admin\Entity\AdminRole is missing an assigned ID for field 'id'. The identifier generation strategy for this entity requires the ID field to be populated before EntityManager#persist() is called. If you want automatically generated identifiers instead you need to adjust the metadata mapping accordingly.

Using strategy: CUSTOM

quoting Doctrine:

CUSTOM means that customer will use own ID generator that supposedly work

With this strategy, we would have to implement our own UUID generator.
Unlike ramsey-uuid, we would not generate the UUIDs in PHP, but make a database query that would return an identifier.
The main disadvantage of this solution is that we would have to make an extra query for each INSERT.
Tested it, so I can confirm that this solution works, the fixtures were executed successfully.

Example implementation:

<?php

namespace Core\App\DBAL;

use Doctrine\DBAL\Exception;
use Doctrine\ORM\EntityManagerInterface;
use Doctrine\ORM\Id\AbstractIdGenerator;

final class UuidV7Generator extends AbstractIdGenerator
{
    /**
     * @throws Exception
     */
    public function generateId(EntityManagerInterface $em, ?object $entity): mixed
    {
        return $em->getConnection()->fetchOne('SELECT UUID_V7()');
    }
}

    #[ORM\Id]
    #[ORM\Column(type: 'uuid', unique: true, nullable: false)]
    #[ORM\GeneratedValue(strategy: 'CUSTOM')]
    #[ORM\CustomIdGenerator(class: UuidV7Generator::class)]

Based on this, we could then also have different generators for UUID_V1 and UUID_V4, allowing each entity to choose which version of UUID to use.

---

I asked a friend, just to confirm the issues with the different strategies:

MariaDB’s DEFAULT (UUID_V7()) does not report a generated value to the client connection.
The database fills it, but the driver never returns it, and Doctrine has no API hook to fetch it back.
Therefore Doctrine refuses to insert when the ID is null.

[alexmerlin]

Actually, I don't think there is an issue (other than the fact that we need an extra query) with using the custom generator.

@arhimede You raised a concern on a possible UUID collision:

RequestA wants to persist an entity, Doctrine executes a query that returns a new UUID and assigns it to the entity as an identifier.
In the meantime, requestB comes in, runs through the same process and accidentally ends up with the same UUID as requestA.
Either of the two requests that persists and flushes second, will end up in a duplicate entity error.

Now, the chance for this is as MariaDB calls it: "astronomically low".
Without using the exact words, they guarantee that they will not generate the same UUID more than once.

A UUID is designed as a number that is globally unique in space and time. Two calls to UUID() are expected to generate two different values, even if these calls are performed on two separate computers that are not connected to each other.

A good read on using UUIDs and how safe they are to use: https://mariadb.org/always-use-the-right-uuid/

---

What are the chances that between the select for the UUID and persisting the entity another request comes in and MariaDB generates the same UUID so the second request "steals" the UUID and my first request ends up trying to create a duplicate?

That risk is effectively zero.

MariaDB’s UUID_V7() (and UUID_V4()) functions are designed to produce globally unique identifiers, not sequential ones per connection. Each call to UUID_V7() generates a value containing:

• A high-resolution timestamp component (to the microsecond), and
• Random or counter bits ensuring uniqueness even for concurrent calls.

MariaDB’s implementation guarantees that:

• No two calls within the same server instance will ever return the same value.
• The probability of collision across different servers is astronomically low—far lower than hardware failure rates.

So even though your application performs:

1. SELECT UUID_V7()
2. INSERT ...

the UUID returned in step 1 will never be duplicated by another connection calling UUID_V7() in the same time window.

In practice, the collision probability is so negligible (≈ 2⁻¹²²) that it’s treated as impossible for all production use cases.

---

In terms of avoiding collisions, is ramsey/uuid's uuid7 safer than MariaDB's UUID_v7()?

Both are equally safe in practice — the risk of collision is astronomically small for either implementation — but they differ slightly in scope of control and environmental guarantees.

Aspect	ramsey/uuid (PHP)	MariaDB UUID_V7()
Specification compliance	Fully compliant with RFC 9562 (UUIDv7 draft → final)	MariaDB’s implementation follows the UUIDv7 draft and aligns with the same layout.
Entropy source	Uses PHP’s random_bytes(), backed by the OS CSPRNG (kernel-level, cryptographically strong).	Uses MariaDB’s internal PRNG combined with timestamp bits.
Uniqueness scope	Guaranteed across all machines and processes using the same proper random source.	Guaranteed across all MariaDB connections on the same or different servers (UUIDs include time + random bits).
Collision probability	~2⁻¹²²	~2⁻¹²²
Failure modes	Only if the PHP runtime’s RNG fails or is compromised.	Only if MariaDB’s RNG fails or two instances are misimplemented.
Operational control	Generated in app code before DB I/O (no latency).	Generated by DB engine (requires DB round-trip).

Bottom line:
Neither is “safer” in a measurable sense; both follow the same 128-bit UUIDv7 structure with strong random components.

• If you prefer database-side consistency and auditing, use MariaDB UUID_V7().

• If you want full application-side control and portability (e.g., testing, other databases), use ramsey/uuid’s Uuid::uuid7().

From a collision-avoidance standpoint, they are equivalent for all practical purposes.

---

Made a small test query to generate 1000 UUID_V7s to observe their progression:

WITH RECURSIVE counter AS (
  SELECT 1 AS n
  UNION ALL
  SELECT n + 1 FROM counter WHERE n < 1000
)
SELECT n, UUID_V7() AS uuid
FROM counter;

[alexmerlin]

Implemented in #456