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

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 simple mapping instead.

1 0
Viewed: 877 times
Version: 2.0
Category: How-tos
Written by: grigori
Last updated by: samdark
Created on: Nov 25, 2019
Last updated: 12 days ago
Update Article


View all history

Related Articles