Description
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);