README

Store UUIDs and ULIDs as efficient binary(16) columns in MySQL instead of char(36) or char(26), saving storage space and improving index performance.

Table of Contents

• Why Use Binary Storage?
• Requirements
• Installation
• Features
• Usage
• Route Model Binding
• API Reference
• Testing
• Performance Considerations
• Compatibility
• Contributing
• Credits

Why Use Binary Storage?

Storing UUIDs and ULIDs as binary data provides significant benefits:

• Storage Efficiency: Binary(16) uses 16 bytes vs char(36)/char(26) which uses 36/26 bytes
• Index Performance: Smaller indexes mean faster lookups and reduced memory usage
• Native Format: UUIDs/ULIDs are stored in their native binary format
• Compatibility: Works seamlessly with Laravel's UUID/ULID objects

Storage Comparison

Type	String Storage	Binary Storage	Savings
UUID	36 bytes (char)	16 bytes (binary)	56% reduction
ULID	26 bytes (char)	16 bytes (binary)	38% reduction

Requirements

• PHP 8.2+ (for Laravel 12) or PHP 8.3+ (for Laravel 13)
• Laravel 12.0 or 13.0
• MySQL 5.7 or higher (MySQL 8.0+ recommended)

Compatibility Matrix

Laravel	PHP 8.2	PHP 8.3	PHP 8.4	PHP 8.5
12.x	✅	✅	✅	✅
13.x	❌	✅	✅	✅

Installation

Install via Composer:

composer require kenzal/mysql-binary-uuids

The service provider will be automatically registered.

Caution

Upgrading from String (char) UUIDs

If you're migrating an existing project from char(36) or varchar(36) UUIDs to binary(16):

1. Install this package
2. Update your models to use the traits or casts
3. Create migrations to convert columns
4. Plan for a breaking schema change — test thoroughly in staging first

Key query change: Binary(16) columns store UUIDs/ULIDs as raw bytes, not human-readable strings. When querying, you must pass UuidInterface or Ulid objects — raw strings will not be automatically converted to binary at the connection level:

use Ramsey\Uuid\Uuid;

// ✅ Query with objects
User::find($uuidObject);
User::find(Uuid::fromString($uuidString));
User::where('id', $uuidObject)->first();

// ❌ Returns null — raw strings won't match binary(16)
User::find($uuidString);
User::where('id', $uuidString)->first();

Route-model binding is handled automatically — string UUIDs/ULIDs from URLs are converted to objects before querying.

Features

✨ Automatic Schema Support

UUIDs are automatically stored as binary(16) when using Laravel's schema builder:

Schema::create('users', function (Blueprint $table) {
    $table->uuid('id')->primary();  // Stored as binary(16)
    $table->uuid('organization_id');
    $table->timestamps();
});

🔄 Eloquent Casts

Cast binary UUID/ULID columns to native Laravel objects:

use Kenzal\MysqlBinaryUuids\Casts\BinaryUuid;
use Kenzal\MysqlBinaryUuids\Casts\BinaryUlid;

class User extends Model
{
    protected function casts(): array
    {
        return [
            'id' => BinaryUuid::class,
            'session_id' => BinaryUlid::class,
        ];
    }
}

🎯 Model Traits

Drop-in replacements for Laravel's HasUuids and HasUlids traits:

use Kenzal\MysqlBinaryUuids\Concerns\HasBinaryUuids;

class User extends Model
{
    use HasBinaryUuids;
    
    // That's it! Automatic UUID v7 generation with binary storage
}

🛠️ Blueprint Macros

Additional Blueprint methods for ULID columns:

Schema::create('sessions', function (Blueprint $table) {
    $table->binaryUlid('id')->primary();
    $table->binaryUlid('user_id');
    $table->foreignBinaryUlid('parent_id')->nullable();
});

Usage

Basic Usage with Casts

use Illuminate\Database\Eloquent\Model;
use Kenzal\MysqlBinaryUuids\Casts\BinaryUuid;

class Post extends Model
{
    protected function casts(): array
    {
        return [
            'id' => BinaryUuid::class,
            'author_id' => BinaryUuid::class,
        ];
    }
}

// Create a post (provide an ID since no trait for auto-generation)
$post = Post::create([
    'id' => '019eb8b2-8b13-7232-a60c-f19b6f0827df',
    'title' => 'My Post',
    'author_id' => '550e8400-e29b-41d4-a716-446655440000',
]);

// Access as UUID objects
echo $post->id->toString(); // "019eb8b2-8b13-7232-a60c-f19b6f0827df"
echo $post->author_id->toString(); // "550e8400-e29b-41d4-a716-446655440000"

// Works with UUID objects too
use Ramsey\Uuid\Uuid;

$post->author_id = Uuid::uuid4();
$post->save();

Using Model Traits

The HasBinaryUuids and HasBinaryUlids traits provide automatic ID generation:

use Illuminate\Database\Eloquent\Model;
use Kenzal\MysqlBinaryUuids\Concerns\HasBinaryUuids;

class User extends Model
{
    use HasBinaryUuids;
    
    protected $fillable = ['name', 'email'];
}

// UUID v7 is automatically generated
$user = User::create([
    'name' => 'John Doe',
    'email' => 'john@example.com',
]);

echo $user->id->toString(); // Auto-generated UUID v7

Using ULIDs

use Kenzal\MysqlBinaryUuids\Concerns\HasBinaryUlids;

class Session extends Model
{
    use HasBinaryUlids;

    protected $fillable = ['user_id'];
}

$session = Session::create([
    'user_id' => $user->id,
]);

// ULID objects are chronologically sortable
echo $session->id; // "01ARZ3NDEKTSV4RRFFQ69G5FAV"

Custom Unique ID Columns

By default, traits apply to the id column. Customize via the uuidColumns() or ulidColumns() methods:

use Kenzal\MysqlBinaryUuids\Concerns\HasBinaryUuids;

class Document extends Model
{
    use HasBinaryUuids;
    
    public function uuidColumns(): array
    {
        return ['id', 'document_number', 'revision_id'];
    }
}

// All three columns get binary UUID casts and auto-generation

Custom UUID/ULID Types

You can specify custom UUID/ULID subclasses for specific columns using string keys. This is useful when you need to extend UUIDs with domain-specific behavior.

First, create your custom UUID class:

use Kenzal\MysqlBinaryUuids\ExtensibleUuid;

class DocumentUuid extends ExtensibleUuid
{
    // ...
}

Then reference it in your model:

use Kenzal\MysqlBinaryUuids\Concerns\HasBinaryUuids;

class Document extends Model
{
    use HasBinaryUuids;
    
    public function uuidColumns(): array
    {
        return [
            'id',  // Uses default BinaryUuid cast
            'document_uuid' => DocumentUuid::class,  // Custom UUID subclass
        ];
    }
}

The same pattern works with ExtensibleUlid and ulidColumns().

Using Directly in casts()

Since ExtensibleUuid and ExtensibleUlid implement Laravel's Castable interface, you can also use them directly in the casts() array without traits:

use Kenzal\MysqlBinaryUuids\Casts\BinaryUuid;
use Kenzal\MysqlBinaryUuids\ExtensibleUuid;

class CustomUuid extends ExtensibleUuid {}

class Document extends Model
{
    protected function casts(): array
    {
        return [
            'id' => BinaryUuid::class,
            'document_uuid' => CustomUuid::class,
        ];
    }
}

Using Both UUID and ULID Columns

A model can have both UUID and ULID columns by using one trait for auto-generation and explicit casts for others:

use Kenzal\MysqlBinaryUuids\Concerns\HasBinaryUuids;
use Kenzal\MysqlBinaryUuids\Casts\BinaryUlid;

class MixedModel extends Model
{
    use HasBinaryUuids;  // For UUID primary key
    
    public function uuidColumns(): array
    {
        return ['id'];  // UUID auto-generated
    }
    
    protected function casts(): array
    {
        return [
            'session_id' => BinaryUlid::class,  // ULID manually specified
        ];
    }
}

Migration Examples

Creating Tables with UUIDs

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

return new class extends Migration
{
    public function up(): void
    {
        Schema::create('organizations', function (Blueprint $table) {
            $table->uuid('id')->primary();
            $table->string('name');
            $table->timestamps();
        });
        
        Schema::create('users', function (Blueprint $table) {
            $table->uuid('id')->primary();
            $table->uuid('organization_id');
            $table->string('name');
            $table->string('email')->unique();
            $table->timestamps();
            
            $table->foreign('organization_id')
                  ->references('id')
                  ->on('organizations')
                  ->onDelete('cascade');
        });
    }
};

Creating Tables with ULIDs

Schema::create('sessions', function (Blueprint $table) {
    $table->binaryUlid('id')->primary();
    $table->uuid('user_id');
    $table->string('ip_address');
    $table->text('user_agent');
    $table->timestamp('last_activity');
    
    $table->index('user_id');
    $table->index('last_activity');
});

Nullable UUID/ULID Columns

Schema::create('posts', function (Blueprint $table) {
    $table->uuid('id')->primary();
    $table->uuid('parent_id')->nullable(); // Optional parent post
    $table->uuid('author_id');
    $table->string('title');
    $table->text('content');
    $table->timestamps();
});

Working with Existing Data

If you're migrating from string-based UUIDs, create a migration to convert:

use Illuminate\Database\Migrations\Migration;
use Illuminate\Support\Facades\DB;

return new class extends Migration
{
    public function up(): void
    {
        // First, create a temporary column
        DB::statement('ALTER TABLE users ADD COLUMN id_binary BINARY(16) AFTER id');
        
        // Convert existing UUIDs to binary
        DB::statement('UPDATE users SET id_binary = UNHEX(REPLACE(id, "-", ""))');
        
        // Drop old column and rename new one
        DB::statement('ALTER TABLE users DROP PRIMARY KEY, DROP COLUMN id');
        DB::statement('ALTER TABLE users CHANGE id_binary id BINARY(16)');
        DB::statement('ALTER TABLE users ADD PRIMARY KEY (id)');
    }
    
    public function down(): void
    {
        // Convert back to char(36) if needed
        DB::statement('ALTER TABLE users ADD COLUMN id_char CHAR(36) AFTER id');
        DB::statement('UPDATE users SET id_char = LOWER(CONCAT(
            HEX(SUBSTRING(id, 1, 4)), "-",
            HEX(SUBSTRING(id, 5, 2)), "-",
            HEX(SUBSTRING(id, 7, 2)), "-",
            HEX(SUBSTRING(id, 9, 2)), "-",
            HEX(SUBSTRING(id, 11, 6))
        ))');
        DB::statement('ALTER TABLE users DROP PRIMARY KEY, DROP COLUMN id');
        DB::statement('ALTER TABLE users CHANGE id_char id CHAR(36)');
        DB::statement('ALTER TABLE users ADD PRIMARY KEY (id)');
    }
};

Route Model Binding

Binary UUID/ULID columns work seamlessly with Laravel's implicit and explicit route-model binding. The traits handle the conversion automatically — string UUIDs/ULIDs from URLs are converted to UuidInterface/Ulid objects before querying, and the connection handles the binary conversion.

Implicit Binding

With traits, no additional configuration is needed:

use Illuminate\Database\Eloquent\Model;
use Kenzal\MysqlBinaryUuids\Concerns\HasBinaryUuids;

class User extends Model
{
    use HasBinaryUuids;
}

// GET /users/550e8400-e29b-41d4-a716-446655440000
Route::get('/users/{user}', function (User $user) {
    return $user; // Resolved correctly from binary(16) column
});

Explicit Binding

Works with casts too — register the binding in AppServiceProvider:

use App\Models\Post;
use Illuminate\Support\Facades\Route;
use Ramsey\Uuid\Uuid;

Route::bind('post', function (string $value) {
    return Post::where('id', Uuid::fromString($value))->firstOrFail();
});

Or use Laravel's getRouteKeyName() on the model.

Custom Route Key Names

Override getRouteKeyName() to bind on a different binary UUID/ULID column:

class Document extends Model
{
    use HasBinaryUuids;

    public function uuidColumns(): array
    {
        return ['id', 'document_uuid'];
    }

    public function getRouteKeyName(): string
    {
        return 'document_uuid';
    }
}

// GET /documents/550e8400-e29b-41d4-a716-446655440000
Route::get('/documents/{document}', function (Document $document) {
    return $document;
});

API Reference

Casts

BinaryUuid

Casts binary(16) columns to Ramsey\Uuid\UuidInterface objects.

protected function casts(): array
{
    return [
        'id' => BinaryUuid::class,
    ];
}

Methods:

• get(): Converts binary data to UUID object
• set(): Accepts UUID strings or objects, converts to binary

BinaryUlid

Casts binary(16) columns to Symfony\Component\Uid\Ulid objects.

protected function casts(): array
{
    return [
        'id' => BinaryUlid::class,
    ];
}

Methods:

• get(): Converts binary data to ULID object
• set(): Accepts ULID strings or objects, converts to binary

Traits

HasBinaryUuids

Provides automatic UUID v7 generation with binary storage.

Features:

• Generates UUID v7 for new models
• Applies BinaryUuid cast to uuidColumns() columns
• Sets $keyType = 'uuid' and $incrementing = false (via HasUniqueStringIds)
• Route-model binding — resolves string UUID URLs against binary(16) columns

Methods:

• newUniqueId(): Generates a new UUID v7
• isValidUniqueId($value): Validates UUID format
• uuidColumns(): Returns array of columns that should have UUIDs (default: ['id'])
  • Supports custom UUID subclasses: ['uuid_id' => DocumentUuid::class]

HasBinaryUlids

Provides automatic ULID generation with binary storage.

Features:

• Generates ULIDs for new models
• Applies BinaryUlid cast to ulidColumns() columns
• Sets $keyType = 'uuid' and $incrementing = false (via HasUniqueStringIds)
• Route-model binding — resolves string ULID URLs against binary(16) columns
• ULIDs are chronologically sortable

Methods:

• newUniqueId(): Generates a new ULID
• isValidUniqueId($value): Validates ULID format
• ulidColumns(): Returns array of columns that should have ULIDs (default: ['id'])
  • Supports custom ULID subclasses: ['ulid_id' => CustomUlid::class]

Blueprint Macros

binaryUlid(string $column = 'ulid')

Create a binary(16) ULID column.

$table->binaryUlid('id')->primary();
$table->binaryUlid('session_id')->nullable();

foreignBinaryUlid(string $column)

Create a foreign key column for referencing a binary ULID.

$table->foreignBinaryUlid('parent_id')
      ->references('id')
      ->on('parents');

Testing

The package includes a comprehensive test suite:

composer test

Run specific test suites:

composer test -- --filter=Unit
composer test -- --filter=Feature
composer test -- --filter=HasBinaryUuids

Performance Considerations

Query Performance

Binary UUIDs maintain excellent query performance. When querying, pass UuidInterface or Ulid objects — the connection automatically converts them to binary:

use Ramsey\Uuid\Uuid;

// Pass objects, not raw strings
$user = User::where('id', Uuid::fromString($uuidString))->first();
$user = User::find($uuidObject);

// Raw strings won't match binary(16) columns
User::find($uuidString); // null — use the object instead

Index Recommendations

For optimal performance:

1. Always index UUID/ULID foreign keys:

   $table->uuid('organization_id');
   $table->index('organization_id');

2. Use UUID v7 or ULIDs for time-ordered data:

   • Both have time-based sorting
   • Reduces index fragmentation
   • Better for clustered indexes

3. Consider composite indexes:

   $table->index(['organization_id', 'created_at']);

Compatibility

UUID Versions

This package works with all UUID versions:

• UUID v4: Random UUIDs
• UUID v7: Time-ordered UUIDs (recommended, used by default)

The HasBinaryUuids trait generates UUID v7 by default for better database performance.

ULID Format

ULIDs are 128-bit identifiers:

• 48-bit timestamp (millisecond precision)
• 80-bit random component
• Lexicographically sortable
• Case-insensitive base32 encoding

Contributing

Contributions are welcome! Please feel free to submit a Pull Request.

Development Setup

git clone https://github.com/kenzal/mysql-binary-uuids.git
cd mysql-binary-uuids
composer install

Local Testing Configuration

Copy phpunit.xml.dist to phpunit.xml and customize for your local environment:

cp phpunit.xml.dist phpunit.xml

Then edit phpunit.xml with your local MySQL credentials:

<env name="DB_HOST" value="127.0.0.1"/>
<env name="DB_PORT" value="3306"/>
<env name="DB_DATABASE" value="laravel_binary_uuids_test"/>
<env name="DB_USERNAME" value="your_username"/>
<env name="DB_PASSWORD" value="your_password"/>

Note: phpunit.xml is gitignored so your local credentials won't be committed.

Running Tests

composer test

Or run specific test suites:

# Run only unit tests
composer test:unit

# Run only feature tests
composer test:feature

Code Style

This package uses Laravel Pint for code formatting. Before submitting a PR:

# Check code style
composer format:test

# Fix code style issues
composer format

Make sure all tests pass and code style checks pass before submitting a PR.

License

This package is open-sourced software licensed under the MIT license.

Credits

• Author: J. Kenzal Hunter, Sr. (PGP Info, PGP Key)
• Laravel: Taylor Otwell and the Laravel community
• Ramsey UUID: Ben Ramsey
• Symfony UID: Symfony community

Support

• Issues: GitHub Issues
• Discussions: GitHub Discussions

Changelog

See CHANGELOG.md for version history.

Made with ❤️ for the Laravel community