UUID instead of an auto-increment integer for ID with Active Record

You are viewing revision #15 of this wiki article.
This version may not be up to date with the latest version.
You may want to view the differences to the latest version or see the changes made in this revision.

« previous (#14)next (#16) »

I have a dream ... I am happy to join with you today in what will go down in history as the greatest demonstration of

bad design of Active Record.

I have an API. It's built with a RESTful extension over Active Record, and some endpoints provide PUT methods to upload files. By a REST design we create an entity with POST /video first, and then upload a video file with PUT /video/{id}/data.

How do we get the {id}? The essential solutuion is UUID generated by a client. It allows API application to be stateless and scale it, use master-master replication for databases and feel yourself a modern guy. If you have Postgres - lucky you, feel free to use the built-in UUID data type and close this article. With MySQL the essential solution is insert into users values(unhex(replace(uuid(),'-',''))... MySQL team recommends updating our INSERT queries. With Active Record it is not really possible. For fetching UUIDs it recommends adding a virtual column - this can be used.

If you design the application from ground up, you can use defferent fields for a binary and text representation of UUID, and reference them in different parts of an application, but I am bound to the legacy code.

Adding getId()/setId() won't help - data comes from a client in JSON and fills the model object with a setAttributes() call avoiding generic magic methods.

Here's the hack:

Step 1. Add a private $idText; property

use yii\db\ActiveRecord;
class Video extends ActiveRecord
    private $idText;

Step 2. Add two validators and a filter

['id','match', 'pattern'=>'/^[0-9a-f]{8}-[0-9a-f]{4}-[1-5][0-9a-f]{3}-[89ab][0-9a-f]{3}-[0-9a-f]{12}$/i'],
// convert UUID from text value to binary and store the text value in a private variable
// this is a workaround for lack of mapping in active record
['id','filter','skipOnError'=>true, 'filter'=>function($uuid){
         $this->idText = $uuid;
         return pack("H*", str_replace('-', '', $uuid));
//now let's check if ID is taken
['id','unique','filter'=>function(\yii\db\Query $q){$q->where(['id'=>$this->getAttribute('id')]);}],

First rule is a validator for an input. Second rule is a filter preparing UUID to be written in a binary format and keeping the text form for output. Third one is a validator running a query over the binary value generated by a filter. Note: I wrote $this->getAttribute('id'), $this->id returns a text form. We can write a query to validate data, not to save it.

Step 3. Add getters

public function __get($name)
    return ($name === 'id') ? $this->getId() : parent::__get($name);

 * Return UUID in a textual representation
public function getId(): string
    if ($this->idText === NULL && $this->getIsNewRecord()){
        //the filter did not convert ID to binary yet, return the data from input
        return $this->getAttribute('id');
    //ID is converted
    return $this->idText ?? $this->getAttribute('id_text');

Active Record does not call the getter method if attributes contain the property. It should not be this way, so I return the default component behavior and make ID returned the right way. From the other hand, the first valiator calls $model->id triggering the getter before the UUID is saved to the private property so I need to serve the value from user input.

It is strange to mutate data in a validator, but I found this is the only way. I belive I shouldn't use beforeSave() callback to set the binary value for generating SQL, and return the text value back in afterSave() - supporting this code would be a classic hell like #define true false;.

Step 4. Define the mapping for output

public function fields()
    $fields = parent::fields();
    $fields['id'] =function(){return $this->getId();};
    return $fields;

This method is used by RESTful serializers to format data when you access your API with GET /video requests.

So, now you can go the generic MySQL way

Step 5. add a virtual column

ALTER TABLE t1 ADD id_text varchar(36) generated always as
 ) virtual;

Step 5. Use Object Relation Mapping in Yii 3 when it's available and write siple mapping instead.

5 0
Viewed: 45 463 times
Version: 2.0
Category: How-tos
Written by: grigori
Last updated by: samdark
Created on: Nov 25, 2019
Last updated: 2 years ago
Update Article


View all history

Related Articles