How to properly and safely use ORM in Yii2?

Question

Yii

Hello.

background

Кто знает, что такое ORM, и особенно, кто пользовался, знают и понимают, что эта штука очень удобная.
Я искал, есть ли ORM в Yii2 - и нашёл там Doctrine - ( устанавливается вместе с Yii ) и класс Migration
Вы скажете, открой консоль и введи yii migrate - и будет тебе счастье.
Да! Когда ты разработчик и делаешь проект у себя на компе, можно и через консоль. НО! Если это заказчик и его сайт уже в сети интернет, и он не умеет пользоваться консолью???
Получается, что, если я хочу очистить таблицу, то я вызываю функцию, которая выполняет это действие.

For example:

in the model

<?php
use yii\db\Migration;
 
class Action extends Migration
{
    public function up($table, $params = [])
    {
        $this->createTable($table, [
            'id' => $this->primaryKey(),
            $params // передаём массив того, какие столбцы надо добавить
        ]);
    }
 
    public function down($table)
    {
        $this->dropTable($table); // передаём, какую таблицу будем удалять
    }

    public function clear($table)
    {
        $this->truncateTable($table); // передаём, какую таблицу будем очищать
    }

    public function getColumn($table, $column, $type = 'string')
    {
        $this->addColumn($table, $column, $type); // добавление столбца в таблицу
    }
// и так далее ...
}
?>

In controller:

example

<?php
...
class SiteController extends Controller
{
    /**
     * @inheritdoc
     */
    public function behaviors()
    {
        return [
            'verbs' => [
                'class' => VerbFilter::className(),
                'actions' => [
                    'clear' => ['post'], // post ?? - чтобы выполнить ?
                ],
            ],
        ];
    }
...

    public function actionClear() 
    { 
        Action::clear('post');
        return $this->redirect(['index']);
    } 
...
}
?>

In view:

example

...
<?=Html::a('Truncate table', ['site/clear'], ['title' => 'очистить таблицу?', 'class' => 'btn', 'data-method' => 'post', 'data-pjax' => '',]);?>
...

Who has already experienced this? Is it possible to display the functionality by reference in this way, is it safe?
How to reset indexes after clearing the table so that autoincrement starts again from 1. ?

I also want to note that this kind of functionality is available only to one person - the administrator, in the control panel, which is not available to ordinary users. If a post request is attached to the link, then this link will not be able to execute other requests, it will be forbidden, even if someone guesses this link, it will not be executed, because it will not pass request. What will be the recommendations?

Answer 1

Even if you or someone else “mounted” the doctrine to the Yii2 project (this is possible in principle ), this does not mean that you do not need to understand the meaning of migrations. As Maxim Fedorov already said , migrations are needed for database versioning . Using versioning (migrations) of the database as a command is a stupid idea .
Also, migrations don't have to operate on data , they only version the schema. On a working project, you work with data: through scripts, SQL queries, etc.
For example. You have downloaded the project from the repository. Created an empty database. Connected to the project. You apply migrations, and they give an error related to data migrationwhich you don't have. This is clear?
So how do you organize the work of the customer not through the console, but through the web interface? There are two ways to go:

1. With a web script. Create methods in the controller that will delete data, change autoincrement directly, as when working with regular data. You will get some kind of wrapper over the base. Work through a simple SQL query or using Doctrine, but it should not be with the help of migrations!
   
2. With the help of a console script and a queue. Do the same as in the first option, only when you call the method in the controller, you do not delete the data, but throw the task into the queue: "Delete all the data for me from the table." The queue is launched by cron, for example, every 5 minutes. The customer sees the status of the queues by his commands: queued, in progress, completed. You can go without queues, but it will be better and the customer will have feedback.
   

When to use the second option? Then, when you may have errors on the memory limit. As a rule, this is work with big data.
For example, if your table has 10 records, then such a process can be easily launched through a regular Web script. But if you have 10,000,000 million records in the database, then you will have an error on the memory limit. About 10 million wrote exaggerated. The error can also occur with less data. It all depends on your memory limit and the operation itself.
An example of running a script from an application without queues using exec :

class AdminController extends \yii\web\Controller
{
    public function actionIndex()
    {
        return $this->render('index');
    }

    public function actionComposerUpdate()
    {
        return $this->commandExec('composer update');
    }

    public function actionComposerInstall()
    {
        return $this->commandExec('composer install');
    }

    public function actionComposerSelfUpdate()
    {
        return $this->commandExec('composer self-update');
    }

    private function commandExec($command)
    {
        $projectPath = \Yii::getAlias('@app');
        $exec = "cd {$projectPath} && {$command}";

        exec($exec, $resultArray);

        if ($resultArray){
            \Yii::$app->getSession()->setFlash('info', implode("<br>", $resultArray));
        }

        return $this->redirect('index');
    }
}