use uuids as primary keys in laravel #php

0.README.MD

laravel >=9

• on laravel 9 you can use the built in trait

• however if you want to have more control you also can use the custom trait below

  • see 0.Test.php

laravel <=8

• create new trait app/Traits/hasUuid.php

  • see 1.hasUuid.php

• extend all models

  • see 2.Test.php

migrate all existing columns

postgresql

links

• https://blog.hagander.net/automatically-dropping-and-creating-constraints-131/
• https://dataedo.com/kb/query/postgresql/list-all-primary-keys-and-their-columns
• https://stackoverflow.com/a/49429112/2068362

raw queries

• see 3.raw-queries.sql

as a migration

• see 4.xxxx_xx_xx_0001_uuid.php

use in future migrations

• see 5.xxxx_xx_xx_0001_tables.php

clear sessions

rm -f storage/framework/sessions/*

0.Test.php

<?php
/* ... */
use Illuminate\Database\Eloquent\Concerns\HasUuids;

class Test extends Model
{
    use HasUuids;
    /* ... */
}

1.hasUuid.php

<?php
namespace App\Traits;
use Illuminate\Support\Str;

trait hasUuid
{
    protected static function boot()
    {
        parent::boot();
        static::creating(function ($model) {
            if (empty($model->{$model->getKeyName()})) {
                $model->{$model->getKeyName()} = Str::orderedUuid()->toString();
            }
        });
    }

    public function getIncrementing()
    {
        return false;
    }

    public function getKeyType()
    {
        return 'string';
    }
}

2.Test.php

<?php
/* ... */
use App\Traits\hasUuid;

class Test extends Model
{
    use hasUuid;
    /* ... */
}

3.raw_queries.sql

SELECT * FROM (

    /* drop constraints */
    SELECT 'ALTER TABLE "' || nspname || '"."' || relname || '" DROP CONSTRAINT "' || conname || '";'
    FROM pg_constraint 
    INNER JOIN pg_class ON conrelid=pg_class.oid 
    INNER JOIN pg_namespace ON pg_namespace.oid=pg_class.relnamespace 
  	WHERE nspname = 'public' -- addition to copied query
    ORDER BY CASE WHEN contype='f' THEN 0 ELSE 1 END,contype,nspname,relname,conname
     
) as t UNION ALL SELECT * FROM (
         
    /* drop default values */
    SELECT 'ALTER TABLE ' || kcu.table_name || ' ALTER COLUMN ' || kcu.column_name || ' DROP DEFAULT;'
    FROM information_schema.table_constraints tco
    JOIN information_schema.key_column_usage kcu 
    ON kcu.constraint_name = tco.constraint_name
    AND kcu.constraint_schema = tco.constraint_schema
    AND kcu.constraint_name = tco.constraint_name
    AND kcu.table_name NOT IN ('table_1', 'table_2', 'table_3') -- tables to exclude
    WHERE tco.constraint_type = 'PRIMARY KEY'
  	AND kcu.constraint_schema = 'public' -- addition to copied query
  
) as t UNION ALL SELECT * FROM (

    /* convert ids from bigint to uuid */
    SELECT 'ALTER TABLE ' || kcu.table_name || ' ALTER COLUMN ' || kcu.column_name || ' SET DATA TYPE UUID USING LPAD(TO_HEX(' || kcu.column_name || '), 32, ''0'')::UUID;'
    FROM information_schema.table_constraints tco
    JOIN information_schema.key_column_usage kcu 
    ON kcu.constraint_name = tco.constraint_name
    AND kcu.constraint_schema = tco.constraint_schema
    AND kcu.constraint_name = tco.constraint_name
    AND kcu.table_name NOT IN ('table_1', 'table_2', 'table_3') -- tables to exclude
    WHERE (tco.constraint_type = 'PRIMARY KEY' OR tco.constraint_type = 'FOREIGN KEY')
  	AND kcu.constraint_schema = 'public' -- addition to copied query
  
) as t UNION ALL SELECT * FROM (

    /* special rules */
    SELECT 'ALTER TABLE table_4 ALTER COLUMN col_1 SET DATA TYPE UUID USING LPAD(TO_HEX(col_1), 32, ''0'')::UUID;'
) as t UNION ALL SELECT * FROM (
    SELECT 'ALTER TABLE table_5 ALTER COLUMN col_1 SET DATA TYPE UUID USING LPAD(TO_HEX(col_1), 32, ''0'')::UUID;'

) as t UNION ALL SELECT * FROM (

    /* restore constraints */
    SELECT 'ALTER TABLE "' || nspname || '"."' || relname || '" ADD CONSTRAINT "' || conname || '" ' || pg_get_constraintdef(pg_constraint.oid) || ';'
    FROM pg_constraint
    INNER JOIN pg_class ON conrelid=pg_class.oid
    INNER JOIN pg_namespace ON pg_namespace.oid=pg_class.relnamespace
  	WHERE nspname = 'public' -- addition to copied query
    ORDER BY CASE WHEN contype='f' THEN 0 ELSE 1 END DESC,contype DESC,nspname DESC,relname DESC,conname DESC
        
) as t

4.xxxx_xx_xx_0001_uuid.php

<?php
use Illuminate\Support\Facades\DB;
use Illuminate\Database\Migrations\Migration;

class Uuid extends Migration
{
    public function up()
    {
        $query = <<<'EOD'
            SELECT * FROM (
            /* ... */
        EOD;

        $queries = DB::select(DB::raw($query));
        $count = count($queries);
        $count_cur = 0;
        foreach ($queries as $queries__value) {
            DB::statement(current((array) $queries__value));
            echo round((++$count_cur * 100) / $count) . '%' . PHP_EOL;
        }
    }
}

5.xxxx_xx_xx_0001_tables.php

<?php
/* ... */
Schema::create('table1', function (Blueprint $table) {
    // before
    //$table->bigIncrements('id');
    // after
    $table->uuid('id')->primary();
    /* ... */
    // before
    //$table->bigInteger('table2_id');
    // after
    $table->uuid('table2_id');
    /* ... */
});
/* ... */