UUIDs (Universally Unique Id entifiers) are 128-bit values that are used in databases, among other things, to uniquely identify table entries. They are represented as a hexadecimal string divided into five groups separated by hyphens (Example: 09fe49b3-4d2b-471c-ac04-36c9e706b85f). There is numerous Discussions about the advantages and disadvantages of UUIDs in databases - they are indispensable in distributed systems.
In microservices and multi-tenancy applications, it is therefore worth considering introducing UUIDs. Switching from data type BigInteger to UUID (a sortable variant of UUID version 4 from the RFC 4122 specification) in the PHP framework Laravel is done quickly: First, we create a new trait:
2aa7136d977617159be1834eaf40e871
Then we add all our models:
2aa7136d977617159be1834eaf40e871
In Laravel 9⁺ this is even easier: here we already have the HasUuids trait ready to use. Alternatively, the framework also offers support for the related, but still rather unknown data type ULID , which could be interesting due to better readability and sortability.
The difficult part is the migration of existing data. Roughly one could proceed as follows:
- Add new UUID columns with empty values to all tables
(each based on all primary and foreign key columns) - Write UUID values to the new columns
(with ascending primary keys plus updated foreign keys) - Delete original columns
- Rename new columns
There are several challenges here: The migration process takes a long time and new columns are appended to the end of the table (possible solution: resort columns ). A much more direct way is to transform the columns directly.
If you put that PostgreSQL one, you can (after a previous backup, of course) run the following query, for example (before that you can delete all tables that you exclude in Z. 19/31 replace, as well as own special rules in Z. 37/39 add) and copies all queries generated from it:
2aa7136d977617159be1834eaf40e871
If you now run the generated queries together, you have migrated the database within a short time. While the UUIDs thus generated do not conform to the v4 specification, they are lexigraphically in the same order as the previous entries, do not collide with new UUIDs (in the 3rd group in v4 there is always a 4, always one in the migrated variant 0), which also means that new UUIDs are always larger than the migrated UUIDs.
After that it is recommended to delete all Laravel caches (php artisan cache:clear && php artisan route:clear && php artisan config:clear && php artisan view:clear && composer dump-autoload && rm -rf bootstrap/cache/*/*) and running sessions (rm -f storage/framework/sessions/*) to empty. Of course, the whole thing can also be implemented within a Laravel migration:
2aa7136d977617159be1834eaf40e871
After the conversion one uses in future migrations instead bigIncrements or. bigInteger then uuid:
2aa7136d977617159be1834eaf40e871