Migrating Laravel Nova from PostgreSQL to CockroachDB. Our experience and solution

It often happens that upon closer examination, a certain problem reveals a deeper one, plunging into the solution of which you find a lot of interesting things for yourself.

We will talk about such a situation in one of our projects.

Background, description of the manifestation of the original problem

First of all, how it all started. There is a web project, the backend part of which is written using the Laravel framework (version 8.80.0). As an admin panel – Laravel Nova (version 3.27.0). The DBMS used is PostgreSQL. Deployment to production is done through Kubernetes. One day, for certain reasons, it was decided to architecturally switch from PostgreSQL to a distributed database management system CockroachDB. The migration process was successful.

Note that initially they tried to pick up a third-party driver to connect the ORM to CockroachDB. However, we found out that this is completely unnecessary and the standard pgsql driver works fine.

The original problem was not explicitly related to the DBMS, but manifested itself in this way: one of the Nova resources uses a field with a BelongsToManyField relationship provided by the benjacho/belongs-to-many-field package, but not the essence. In the admin panel, when selecting a related entity in this field, technically, the connection data should be written to the pivot table of the connection, in other words, a pair of primary keys of interconnected tables.

However, the “raw” value of the primary key of the table, obtained from the database, which looked something like this 782610923664375809, was rounded by JavaScript on the side of the front part of the package to 782610923664375800. And then, at the time of writing to the pivot table, we received the error “violates foreign key constraint”

local.ERROR: SQLSTATE[23503]: Foreign key violation: 7 ERROR:  insert on table "******" violates foreign key constraint "********_id_foreign"
DETAIL:  Key (*****_id)=(782610923664375800) is not present in table "******". (SQL: insert into "******" ("***_id", "***_id") values (782611156327727105, 782610923664375800)) 

Which is understandable, since there could not be such a key in it. In the browser it looked like this:

"raw" field value
“raw” field value
rounded field value
rounded field value

Identified root cause

This problem of precision for such large numbers in JavaScript, due to the nature of the IEEE-754 double-precision floating point, occurs on its own. by oneself. The question is how to get around it. Perhaps it would be a good option on the front side to convert it to a string before using the received field value, but we refused this due to the fact that it is impossible to modify all vendor packages.

So, if the dimension of primary key values ​​defined by CockroachDB is so large, is it possible to reduce it initially on the DBMS side when creating a table?

And then the interesting began

In CockroachDB, the numeric data type is represented as INT. However, the usual dimension of Integer, BigInteger is nothing more than an alias for the same INT type, and in essence the same thing. Here is the page official documentation on this.

This fact led us to the fact that an attempt to reduce the dimension of the primary key in the created table by strictly casting its type to unsignedInteger led to the creation of one in the database with the same int8 type. Those. Where did you start, what did you come to.

CockroachDB Internal Behavior

In principle, the CockroachDB developers know all this, obviously they chose the architecture for a reason and the ways to solve this situation are presented here.

It all comes down to two options:

  1. Set the session variable default_int_size to 4, which corresponds to the dimension of the numeric type Integer (int4) and will allow you to create exactly these fields.

  2. Set the default_int_size session variable to 4, which corresponds to the dimension of the Integer (int4) numeric type and will allow you to create exactly these fields

Our DevOps specialists opposed the latter option, since we may not be alone with our database in the cluster.

However, in practice the following has been found: the default_int_size value is taken into account when creating a table for ordinary fields only. If we create a field in the migration code as a primary key (with the inherent auto-increment and unique constraints), then CockroachDB forces it to be of type int8. And we couldn’t do anything about it.

Ways to solve the problem

All of this could have been avoided by using uuid from the start. We rejected the option of switching to it for the following reasons. First of all, 90% of the projects would have to be seriously reworked. Also, along with the advantage, there are also disadvantages of uuid, for example, more memory consumption, slower performance, restrictions on sorting by the primary key.

Here are some opinions on this:
What is better and more correct to use as a primary key: autoincrement or UUID?
About uuids, primary keys and databases

We got the idea of ​​solving the issue again from the official documentation in that part of it, which explains primary key construction.

When creating a table, in the case when the primary key has not been defined, CockroachDB creates it on its own in the form of a hidden “rowid” field (of course, all with the same int8 type).

Then it is quite possible, along with rowid, to create a separate order of the id field of the unsignedInteger type we need (int4), using the default_int_size session variable. And then add the usual primary key constraints to it, such as auto-increment and uniqueness. It is this id field that will later be used by ORM in table relationships. The whole point is precisely in the separate operation of creating a field and then endowing it with the necessary properties.

This solution is working, as a result, we got id table identifiers of an acceptable dimension, which are not rounded by JavaScript on the front side, with minimal costs in the form of migration edits. Migrations ORM Code Examples:

// Создание поля id, разделение операций обязательно. Как уже сказал, без определения первичного ключа при создании таблицы, CockroachDB создаст скрытое rowid поле этого ключа
Schema::create('my_table', function (Blueprint $table) {
                $table->unsignedInteger('id');
                //...
});

Schema::table('my_table', function (Blueprint $table) {
                $table->unsignedInteger('id')->unique()->autoIncrement()->change();
});

// В методе down() отката миграции, после создания таблицы, также необходимо удалить ее sequence структуру. Без этого повторная миграция на создание таблицы выдаст ошибку
public function down()
    {
        Schema::dropIfExists('my_table');

        // Драйвер 'cockroach' - это простое копирование в config/database драйвера 'pgsql', один в один
        if (config('database.default') === 'cockroach') {
            $pdo = DB::getPdo();
            try {
                $pdo->query('drop sequence my_table_id_seq');
            } catch (Exception $e) {
                Log::channel('stderr')->info('sequence my_table_id_seq not exist');
            }
        }
    }


// Создание pivot таблицы для связи многие-ко-многим, используемое полем BelongsToManyField в Nova
Schema::create('my_table1_my_table2', function (Blueprint $table) {
    // Здесь тип идентификатора не важен, т.к. он не участвует непосредственно в связи
                $table->id();

                $table->unsignedInteger('my_table1_id')->nullable();
                $table->foreign('my_table1_id')->references('id')->on('my_table1')->onDelete('cascade');

                $table->unsignedInteger('my_table2_id')->nullable();
                $table->foreign('my_table2_id')->references('id')->on('my_table2')->onDelete('cascade');
});

The question remains of redefining PostgresConnector through an additional service provider in order to set the default_int_size session variable. Recall that we are connecting to CockroachDB through the PostgeSQL driver.

Implemented it like this:

// app/Database/PostgresConnector
<?php

namespace App\Database;

use Illuminate\Database\Connectors\PostgresConnector as BaseConnector;

class PostgresConnector extends BaseConnector
{
    public function connect(array $config)
    {
        $pdo = parent::connect($config);

        if (config('database.default') === 'cockroach') {
            $pdo->query('set default_int_size = 4');
        }
        return $pdo;
    }
}

// app/Providers/DatabaseConnectorProvider
<?php

namespace App\Providers;

use App\Database\PostgresConnector;
use Illuminate\Support\ServiceProvider;

class DatabaseConnectorProvider extends ServiceProvider
{
    public function register()
    {
        $this->app->bind('db.connector.pgsql', PostgresConnector::class);
    }
}

// Регистрируем провайдера в config/app
'providers' => [
    //...
    App\Providers\DatabaseConnectorProvider::class,
],

We shared this experience with the community, we hope it will be useful to someone.

Thank you for your attention! 🙂

Similar Posts

Leave a Reply

Your email address will not be published. Required fields are marked *