How does one implement primary keys composed of UUIDs, instead of auto-incrementing integers, for Laravel Eloquent models and their relationships?

Question

Auto-incrementing integers cannot be used for primary keys in distributed database topologies in which the potential for conflicts (collisions) exists.

The extant body of literature regarding the subject of UUIDs vs. auto-incrementing integers is vast, and the fundamental precepts are widely understood. Yet, at the same time, no single, comprehensive explanation of how to achieve this in Laravel, with support for Eloquent Models and relationships, seems to exist.

The following article is worthwhile and explains the performance overhead incurred for storing primary keys in VARCHAR(36)/CHAR(36) vs the 4/8-byte integer normally used for auto-incrementing keys. We should heed this advice (especially the post-publication corrections that the author notes throughout):

https://tomharrisonjr.com/uuid-or-guid-as-primary-keys-be-careful-7b2aa3dcb439

Equally valuable is the commentary that stems from the discussion, which is extensive:

https://news.ycombinator.com/item?id=14523523

The following article explains how to implement primary keys using UUIDs in Laravel Eloquent models, but it falls short of explaining how to implement the same for Eloquent relationships, e.g., Many-to-Many with "pivot tables" (per Laravel parlance).

https://medium.com/@steveazz/setting-up-uuids-in-laravel-5-552412db2088

Others have asked similar questions, such as Laravel eloquent UUID in a pivot table , but in that instance, the asker is generating the UUID to be inserted in the pivot table using a MySQL trigger, which I would prefer to avoid in favor of a purely-Eloquent approach.

Another similar question is asked at How To Cast Eloquent Pivot Parameters? , but the crux of the question is how to cast pivot attributes, not how to generate custom values for the ID column upon attaching or synching relationships.

To be clear, we can achieve this easily by passing the optional array argument to the attach() method:

->attach($modelAId, $modelBId, ['id' => Uuid::generate()]);

But it would be necessary to do this every time we call attach() on either of the models, which is cumbersome and violates the DRY principle.

We would be much better served with an event-driven approach that is implemented in the model classes themselves.

What might such an approach look like?

Answer 1

Disclaimer: This is a work-in-progress. As yet, this technique focuses only on Many-to-Many Eloquent relationships, and not the more exotic types, such as Has-Many-Through or Polymorphics.

Current as of Laravel v5.5.*

UUID Generation Packages for Laravel

Before we get started, we need a mechanism by which to generate UUIDs.

The most popular package for UUID generation is as follows:

https://github.com/webpatser/laravel-uuid

Implemeting UUIDs for Eloquent Models

The ability for a model to use a UUID as its primary key may be conferred either by extending Laravel's base Model class, or by implementing a trait. Each approach has its strengths and weaknesses, and because Steve Azzopardi's medium.com article (cited above) already explains the trait method (though, it predates Eloquent's $keyType = 'string'; property), I'll demonstrate the Model extension approach, which, of course, may be adapted to a trait with ease.

Whether we use a model or a trait, the crucial aspects are $incrementing = false; and protected $keyType = 'string';. While extending the base Model class imposes limitations due to PHP's single-inheritance design, it eliminates the need to include these two crucial properties in every model that should utilize a UUID primary key. By contrast, when using a trait, forgetting to include both of these in every model that uses the trait will cause failures.

The base UUID model class:

<?php

namespace Acme\Rocket\Models;

use Illuminate\Database\Eloquent\Model;

use Webpatser\Uuid\Uuid;

class UuidModel extends Model
{
    public $incrementing = false;

    protected $keyType = 'string';

    public function __construct(array $attributes = [])
    {
        parent::__construct($attributes);
    }

    public static function boot()
    {
        parent::boot();

        self::creating(function ($model) {
            $model->{$model->getKeyName()} = Uuid::generate()->string;
        });
    }
}

Next, we'll define the first of two models, User and Role, which are related in a many-to-many capacity.

The User model:

<?php

namespace Acme\Rocket\Models;

use Acme\Rocket\Models\UuidModel;

class User extends UuidModel
{
    public function __construct(array $attributes = [])
    {
        parent::__construct($attributes);
    }
}

This is all that is required for any individual model to use a UUID for its primary key. Whenever a new model is created, the id column will be populated with a freshly-generated UUID automatically.

Implementing Eloquent Relationships for Models with UUID Primary Keys

A requisite for achieving the desired behavior is using a custom pivot model, specifically because we need to disable auto-incrementing for the primary key column (id), and change its type from int to string, just as we did in the UuidModel class, above.

Customizing pivot models has been possible since Laravel 5.0, but the usage has evolved in more recent versions. Interestingly, it is necessary to combine the 5.0 usage with the 5.5+ usage to make this all work.

The custom pivot model is very simple:

<?php

namespace Acme\Rocket\Models;

use Illuminate\Database\Eloquent\Relations\Pivot;

class RoleUser extends Pivot
{
    public $incrementing = false;

    protected $keyType = 'string';
}

Now, we'll add the relationships to the first (User) model:

<?php

namespace Acme\Rocket\Models;

use Webpatser\Uuid\Uuid;

use Illuminate\Database\Eloquent\Model;

use Acme\Rocket\Models\UuidModel;
use Acme\Rocket\Models\Role;
use Acme\Rocket\Models\RoleUser;

class User extends UuidModel
{
    protected $fillable = ['name'];

    public function __construct(array $attributes = [])
    {
        parent::__construct($attributes);
    }

    public function roles()
    {
        return $this->belongsToMany(Role::class)
            ->using(RoleUser::class);
    }

    public function newPivot(Model $parent, array $attributes, $table, $exists, $using = NULL) {
        $attributes[$this->getKeyName()] = Uuid::generate()->string;

        return new RoleUser($attributes, $table, $exists);
    }
}

The key elements to note are the custom pivot model in the roles() method, ->using(RoleUser::class), and the newPivot() method override; both are necessary for a UUID to be inserted into the pivot table's id column whenever the models are attach()ed.

Next, we need to define the Role model, which is essentially identical, but with the many-to-many relationship reversed:

<?php

namespace Acme\Rocket\Models;

use Webpatser\Uuid\Uuid;

use Illuminate\Database\Eloquent\Model;

use Acme\Rocket\Models\UuidModel;
use Acme\Rocket\Models\User;
use Acme\Rocket\Models\RoleUser;

class Role extends UuidModel
{
    protected $fillable = ['name'];

    public function __construct(array $attributes = [])
    {
        parent::__construct($attributes);
    }

    public function users()
    {
        return $this->belongsToMany(User::class)
            ->using(RoleUser::class);
    }

    public function newPivot(Model $parent, array $attributes, $table, $exists, $using = NULL) {
        $attributes[$this->getKeyName()] = Uuid::generate()->string;

        return new RoleUser($attributes, $table, $exists);
    }
}

The best way to demonstrate how this works is with a migration:

<?php

use Illuminate\Support\Facades\Schema;
use Illuminate\Database\Schema\Blueprint;
use Illuminate\Database\Migrations\Migration;

//use Webpatser\Uuid\Uuid;

use Acme\Rocket\Models\User;
use Acme\Rocket\Models\Role;

class UuidTest extends Migration
{
    /**
     * Run the migrations.
     *
     * @return void
     */
    public function up()
    {
        Schema::create('users', function (Blueprint $table) {
            $table->uuid('id');
            $table->primary('id');
            $table->string('name');
            $table->timestamps();
        });

        Schema::create('roles', function (Blueprint $table) {
            $table->uuid('id');
            $table->primary('id');
            $table->string('name');
            $table->timestamps();
        });

        Schema::create('role_user', function (Blueprint $table) {
            $table->uuid('id');
            $table->primary('id');
            $table->unique(['user_id', 'role_id']);
            $table->string('user_id');
            $table->string('role_id');
        });

        $user = User::create([
            'name' => 'Test User',
        ]);

        $role = Role::create([
            'name' => 'Test Role',
        ]);

        // The commented portion demonstrates the inline equivalent of what is
        // happening behind-the-scenes.

        $user->roles()->attach($role->id/*, ['id' => Uuid::generate()->string]*/);
    }

    /**
     * Reverse the migrations.
     *
     * @return void
     */
    public function down()
    {
        Schema::drop('role_users');
        Schema::drop('users');
        Schema::drop('roles');
    }
}

After running the above migration, the role_user table looks like this:

MariaDB [laravel]> SELECT * FROM `role_user`;
+--------------------------------------+--------------------------------------+--------------------------------------+
| id                                   | user_id                              | role_id                              |
+--------------------------------------+--------------------------------------+--------------------------------------+
| 6f7b3820-6b48-11e8-8c2c-1b181bec620c | 6f76bf80-6b48-11e8-ac88-f93cf1c70770 | 6f78e070-6b48-11e8-8b2c-8fc6cc4722fc |
+--------------------------------------+--------------------------------------+--------------------------------------+
1 row in set (0.00 sec)

And to retrieve the models and relationships, we would do the following (using Tinker):

>>> (new \Acme\Rocket\Models\User)->first()->with('roles')->get();
=> Illuminate\Database\Eloquent\Collection {#2709
     all: [
       Acme\Rocket\Models\User {#2707
         id: "1d8bf370-6b1f-11e8-8c9f-8b67b13b054e",
         name: "Test User",
         created_at: "2018-06-08 13:23:21",
         updated_at: "2018-06-08 13:23:21",
         roles: Illuminate\Database\Eloquent\Collection {#2715
           all: [
             Acme\Rocket\Models\Role {#2714
               id: "1d8d4310-6b1f-11e8-9c1b-d33720d21f8c",
               name: "Test Role",
               created_at: "2018-06-08 13:23:21",
               updated_at: "2018-06-08 13:23:21",
               pivot: Acme\Rocket\Models\RoleUser {#2712
                 user_id: "1d8bf370-6b1f-11e8-8c9f-8b67b13b054e",
                 role_id: "1d8d4310-6b1f-11e8-9c1b-d33720d21f8c",
                 id: "89658310-6b1f-11e8-b150-bdb5619fb0a0",
               },
             },
           ],
         },
       },
     ],
   }

As can be seen, we have defined two models and related them through a Many-to-Many relationship, using UUIDs in place of auto-incrementing integers in all instances.

This approach enables us to avoid primary key conflicts in any number of distributed or replicated database scenarios, thereby paving the way for large, complex data structures that will scale well for decades to come.

Final Thoughts

The Many-to-Many syncing methods seem to work, such as sync(), syncWithoutDetaching(), and toggle(), although I have not tested them thoroughly.

This is not the only approach to the larger technique, and it is unlikely to be the "best" approach. And while it works for my limited use-case, I'm sure that others who are better-versed in Laravel and Eloquent than I could offer suggestions for improvement (please do!).

I intend to expand the overall methodology to other relationship types, such as Has-Many-Through and Polymorphics, and will update this Question accordingly.

General Resources for Working With UUIDs in MySQL/MariaDB

http://www.mysqltutorial.org/mysql-uuid/

Status of native UUID support in MySQL

My understanding is that MySQL 8 simply adds new functions that make working with UUIDs easier; it does not add a "native" UUID data type.

And by "easier", it seems that the new functions alleviate some of the challenges around converting between the VARCHAR(36)/CHAR(36) string and a BINARY(16) representation. Apparently, the latter is much faster.

https://mysqlserverteam.com/mysql-8-0-uuid-support/

Status of native UUID support in MariaDB

There's a "Feature Request" open for better UUID support (this ticket explains some of the rationale):

https://mariadb.atlassian.net/browse/MDEV-4958