如何在Laravel迁移中将不可为空的列安全地添加到现有表中？

Question

I would like to add an un-nullable column to an existing table with some rows in Laravel migration. In SQL, I understand such an action should be performed inside a transaction in the order of

1. adding a column
2. initialising the column
3. making it un-nullable

so as to guarantee

• the initialisation to be performed without disrupting the DB integrity, and
• ALTER TABLE not to violate the NOT NULL constraint,

The following is an example PostgreSQL code (assuming users table has a column old_col), referring to an answer:

BEGIN TRANSACTION;
  ALTER TABLE users ADD COLUMN new_col integer;
  UPDATE users SET new_col = old_col + 1;
  ALTER TABLE users ALTER COLUMN new_col SET NOT NULL;
COMMIT;

An ordinary Laravel migration file like this would not work.

public function up()
{
    Schema::table('users', function($table) {
        $table->integer('new_col');  // ->nullable(false) // later?
    });
}

How can one implement a SQL transaction or its equivalent in Laravel migration?

NOTE (edited):
If you want to set up the default value, and if you do not need to (absolutely simultaneously) update the column for the existing rows as a function of some values of each row, then you can simply specify ->default(0) or something like that in the migration file (and avoid all the tricks!). My intention of the question was not to set up the default for the column to add.

Answer 1

The solution with three queries:

DB::transaction(function () {
    Schema::table('users', function (Blueprint $table) {
        $table->integer('id_cloned')->nullable();
    });

    App\Models\User::query()->update([
        'id_cloned' => DB::raw('id + 1'),
        'updated_at' => DB::raw('now()') // if you want to touch the timestamp
    ]);

    Schema::table('users', function (Blueprint $table) {
        $table->integer('id_cloned')->nullable(false)->change();
    });
});

Alternative solution without DB::raw parts, but will generate separate update query for every record:

DB::transaction(function () {
    Schema::table('users', function (Blueprint $table) {
        $table->integer('id_cloned')->nullable();
    });

    foreach (App\Models\User::all() as $row) {
        $row->id_cloned = $row->id + 1;
        $row->save();
    }

    Schema::table('users', function (Blueprint $table) {
        $table->integer('id_cloned')->nullable(false)->change();
    });
});