Attempting to alter a table with a column with default value (JSON_ARRAY()) by adding a foreign constraint fails with an error on SQLite

[lorenzolosa]

Laravel Version

11.22.0

PHP Version

8.3.6

Database Driver & Version

SQLite 3.45.1 for Linux on amd64

Description

In Laravel 11.15.0, #51373 introduced the ability to add / drop foreign keys in SQLite, by re-creating the table and copying all data.

However, I run into an error when the table being altered includes a JSON column with JSON_ARRAY() as default value. The error is something like:

SQLSTATE[HY000]: General error: 1 near "(": syntax error (Connection: sqlite, SQL: create table "__temp__items" ("id" integer primary key autoincrement not null, "flags" text not null default JSON_ARRAY(), "item_id" integer, foreign key("item_id") references "items"("id")))

The issue is that the SQL query the column definition should be something like "column_name" text not null default (JSON_ARRAY())), while the query executed has "flags" text not null default JSON_ARRAY()) (without the brackets). This results in a syntax error.

Steps To Reproduce

Create a table with a JSON column with (JSON_ARRAY()) as default value:

<?php

use Illuminate\Database\Migrations\Migration;
use Illuminate\Database\Query\Expression;
use Illuminate\Database\Schema\Blueprint;
use Illuminate\Support\Facades\Schema;

return new class extends Migration
{
    public function up(): void
    {
        Schema::create('items', function (Blueprint $table) {
            $table->id();
            $table->json('flags')->default(new Expression('(JSON_ARRAY())'));
        });
    }
};

Alter the table to add a foreign key constraint:

<?php

use Illuminate\Database\Migrations\Migration;
use Illuminate\Database\Schema\Blueprint;
use Illuminate\Support\Facades\Schema;

return new class extends Migration
{
    /**
     * Run the migrations.
     *
     * @return void
     */
    public function up(): void
    {
        Schema::table('items', function (Blueprint $table) {
            $table->foreignId('item_id')->nullable();

            $table->foreign('item_id')
                ->references('id')
                ->on('items');
    }
}

Running the second migration results in an error like:

SQLSTATE[HY000]: General error: 1 near "(": syntax error (Connection: sqlite, SQL: create table "__temp__items" ("id" integer primary key autoincrement not null, "flags" text not null default JSON_ARRAY(), "item_id" integer, foreign key("item_id") references "items"("id")))

Under the hood, the first migration execute the following SQL statement:

create table "items" ("id" integer primary key autoincrement not null, "flags" text not null default (JSON_ARRAY()))

while the second migration executes:

create table "__temp__items" ("id" integer primary key autoincrement not null, "flags" text not null default JSON_ARRAY(), "item_id" integer, foreign key("item_id") references "items"("id"))"

The issue is that the second statement includes JSON_ARRAY() instead of (JSON_ARRAY()).

As a comparison, manually running the .schema items in SQLite we get:

sqlite> .schema items
CREATE TABLE IF NOT EXISTS "items" ("id" integer primary key autoincrement not null, "flags" text not null default (JSON_ARRAY()), "item_id" integer);

[driesvints]

@hafezdivandari anything we can do here maybe?

[hafezdivandari]

@driesvints I'll look into this asap.

[hafezdivandari]

You may check #52678