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 and 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 PostgreSQL To do this, you can (after a backup, of course) execute the following query, for example (beforehand you can list all the tables you want to exclude in Z). 19/31 replace as well as your own special rules in Z. 37/39 add) and copies all queries generated from it:
2aa7136d977617159be1834eaf40e871
If you now execute the generated queries collectively, you will have migrated the database within a short time. The UUIDs created in this way do not comply with the v4 specification, but they are lexicographically in the same order as the previous entries, do not collide with new UUIDs (there is always one in the 3rd group in v4 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 find 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