Using UUID with Laravel Eloquent model

Using UUID with Laravel.md

Using UUID with Laravel Eloquent model

Inspirations

• Be Careful With MySQL's auto_increment. How We Ended Up Losing Data

Background Info

• UUID's as Primary Keys in MySQL
• USING UUID AS PRIMARY KEY IN MYSQL/MARIADB DATABASES
• Which UUID version to use?

Required Package

• Laravel Uuid

Implementation

In this example, we will use Laravel UUID package to create UUID version 4 and store it as BINARY(16) in MySQL database. The Accessor and Mutator will be used in an Eloquent model named Post to streamline the usage.

Install Laravel UUID package

This can be done via composer

composer require "webpatser/laravel-uuid=1.*"

Laravel Schema do not support BINARY(16)

The binary type is mapped to BLOB type of MySQL, so we will have to use DB::statement() to create a column of type BINARY(16) inside the migration instead.

<?php

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

class AddUuidColumnToPostsTable extends Migration {

	public function up()
	{
		DB::statement('ALTER TABLE posts ADD uuid BINARY(16) NOT NULL AFTER id;');
		DB::statement('CREATE UNIQUE INDEX posts_uuid_unique ON posts (uuid);');
	}

	public function down()
	{
		DB::statement('DROP INDEX posts_uuid_unique ON posts;');
		DB::statement('ALTER TABLE posts DROP uuid;');
	}

}

Note that a migration for table posts should be created normally without uuid column. The above migration will modify the table to add uuid instead.

Create the model

Create the Post model manually or via artisan command, then add the Accessor and Mutator for UUID column.

<?php

use Illuminate\Database\Eloquent\Model;

class Post extends Model
{
    protected $guarded = ['id];
    
    public function setUuidAttribute($value)
    {
        $this->attributes['uuid'] = hex2bin(str_replace('-', '', $value));
    }

    public function getUuidAttribute($value)
    {
        return bin2hex($value);
    }
}

Beware of Using UUID with Query Builder

When querying the table that contains BINARY(16) of UUID, there might be a problem converting BINARY(16) value to string. In that case, use DB::raw('HEX(posts.uuid) as uuid') to first convert the BINARY(16) to string.

$query = DB::table('posts')
	->select('posts.id', DB::raw('HEX(posts.uuid) as uuid'), 'posts.date', 'posts.body')
	->get();

When I used Binary UUID with eager loading relationship, it always return null. Do you have any idea to resolve this issue?

Straightforward! Thanks. You may have followed this post https://www.percona.com/blog/2014/12/19/store-uuid-optimized-way/, and if you haven't then it's pretty recommended.

Thanks for the article, I spotted one error :
protected $guarded = ['id];

You forgot one quote

Hi, thank you for this interesting post.
I followed these steps for the model User and Role but I always get the following error on my pivot table.

SQLSTATE[22001]: String data, right truncated: 1406 Data too long for column 'role_id' at row 1 (SQL: insert into `role_user` (`role_id`, `user_id`) values (1ff5c63fd4704d1687275a7434ec7ed0, 46e8318bf6fa466b98233353ccc5c661))

I tried creating a custom Pivot model but it seems it doesn't trigger/use my mutator function?

RoleUserPivot.php

<?php

use Illuminate\Database\Eloquent\Relations\Pivot;

class RoleUserPivot extends Pivot {

    /**
     * The table associated with the model.
     *
     * @var string
     */
    protected $table = 'role_user';

    public function user() {
        return $this->belongsTo('User');
    }

    public function role() {
        return $this->belongsTo('Role');
    }

    public function setUserIdAttribute($value) {
        $this->attributes['user_id'] = hex2bin(str_replace('-', '', $value));
    }

    public function getUserIdAttribute($value) {
        return bin2hex($this->user_id);
    }

    public function setRoleIdAttribute($value) {
        $this->attributes['user_id'] = hex2bin(str_replace('-', '', $value));
    }

    public function getRoleIdAttribute($value) {
        return bin2hex($this->user_id);
    }
}

How did you solve useing binary uuid's in combination with foreign keys?