Auto-Increment is the Devil: using UUIDs in Symfony and Doctrine

I migrated my posts to my own blog because Medium is becoming less and less comfortable for readers (paywalls, impossibility to highlight the code, etc.). To read this article in a nicer and privacy friendly context, read it on my personal blog and follow me on Twitter to get notified!

When learning how to create databases, you most likely learned how to use auto-incremented values for identifiers. These automatically generated values are extremely useful as they provide a unique, simple and usually small identifier for a row in the table. They can then be used to join tables and create a relational structure between your entities.

This technique, however, has several drawbacks when badly used. The biggest one being that if you rely on an auto-incremented value in your URLs, anyone can know how many resources you have, which might not be something you would like your competitors to know.

Moreover, by using an auto-incremented value in your URLs, you give the possibility to your users to scrap your whole website easily, by writing a simple script accessing /users/1, /users/2, /users/3, …

While this may not be a huge problem for some entities, it is important to have control over the possibility for users to scrap your website or not.

That’s where UUIDs are useful.

Using UUIDs with Doctrine

UUIDs, or Universally Unique IDentifiers, are a way to generate numbers that are (almost) unique, wherever they are generated, without the need of a central authority to synchronize the unicity (ie. without the need of a database knowing the number of rows before generating a value).

In our context, UUIDs are a great way to avoid exposing auto-incremented numbers in our URLs: instead of /users/1/tgalopin, we could have URLs like /users/c11ed9b0-e060–4aec-b513-e17c24df2c70/tgalopin.

To use UUIDs with Doctrine, I recommend you to use the Ramsey UUID Doctrine package: https://github.com/ramsey/uuid-doctrine. This package will let you configure Doctrine fields as UUIDs, storing them the best way possible in your database. Moreover, if you use Symfony Flex, you won’t even have to configure anything as the recipe will do it for you!

And populate this field with the different versions of UUID, for instance with the version 4 (random UUID):

$this->uuid = Uuid::uuid4();

The problems of UUIDs and how to solve them

While UUIDs are a great way to get unique and difficult-to-scrap identifiers, there are still two main issues when using them:

potential performance loss when using UUID as primary keys

lack of readability of the resulting URLs

Performance of UUID primary keys

If you use UUIDs as primary keys and if your database storage is not able to handle them properly (you should use PostgreSQL ;) ), you will get strings as primary keys. Having strings in WHERE filters, indexes and join queries is a big performance issue due to the size and complexity of the data structure.

This can be improved by a pattern commonly used with UUIDs: having both an auto-incremented integer as primary key and a UUID as a unique field in your entity. This allows you to use the UUID for public display and rely on the auto-incremented integer in joins for performance.

To use this pattern in Doctrine, I create the following trait in most of my applications:

When using UUIDs in URLs, a large part of the URL is not readable by the user anymore. While it’s not a major drawback, having a URL like /user/1/tgalopin is definitely much better than having /user/c11ed9b0-e060–4aec-b513-e17c24df2c70/tgalopin for the users of your application.

To improve this, there are several ways:

we could try to find a smaller data structure than UUIDs (but the support of UUIDs is really great among many programming languages)

we could use only a portion of the UUID (but we would risk to encounter a lot of conflicts)

or we can encode the UUID in a format more suited to URLs

In my opinion, the last option is the best compromise between readability and compatibility, so I looked for different encoding format that would match the readability needed of an URL.

The format you may have thought about when you read the previous paragraph is base64. It’s a great format to express data in a more compact way than hexadecimal, but I didn’t like the possibility to have =, + and / into my identifiers: it didn’t seem to match the need of readability of the URL.

That’s why I looked at base32: base32 has less characters and therefore is slightly longer than base64, but it’s much more suited to an URL as it only has alphanumerical characters.

To use base32 encoded UUIDs, I created several useful tools in my Doctrine application:

A UuidEncoder, which uses the GMP extension to encode and decode UUIDs: