team_id db migration fails for SQLite

[marvinscham]

Hi! I'm self-hosting Input with SQLite via docker compose. Updating from v1.7.0 to v1.8.1 ran the following migration unsuccessfully which in turn broke most features of the site since the team_id column can't be queried.

input  |   2023_12_01_145224_add_team_id_to_forms ............................ 1ms FAIL
input  | [2024-02-04 17:03:59] production.ERROR: SQLSTATE[HY000]: General error: 1 Cannot add a NOT NULL column with default value NULL (Connection: sqlite, SQL: alter table "forms" add column "team_id" integer not null) {"exception":"[object] (Illuminate\\Database\\QueryException(code: HY000): SQLSTATE[HY000]: General error: 1 Cannot add a NOT NULL column with default value NULL (Connection: sqlite, SQL: alter table \"forms\" add column \"team_id\" integer not null) at /var/www/html/vendor/laravel/framework/src/Illuminate/Database/Connection.php:801)

Looks like either the column needs to be nullable during its creation or a non-null DEFAULT needs to be added for the SQLite version of this call: https://github.com/deck9/input/blob/f2ea24b84a4966f74f205b0f6776e7d66c356566/database/migrations/2023_12_01_145224_add_team_id_to_forms.php#L15-L17

Quick fix that did it for me: re-running the command with a default value specified, 1 being the ID of the default team of my instance:

• docker exec -it input sh
• sqlite3 storage/database.sqlite
• alter table "forms" add column "team_id" integer not null default 1;
• insert into migrations VALUES (39, "2023_12_01_145224_add_team_id_to_forms", 1);

The last step makes sure Input won't try to re-run the migration, which would fail due to duplicate columns.

[PhilReinking]

Hey Marvin, thank you so much for your report and the solution you found. I will take a look into it. I think we would need to add special migrations for the sqlite driver. Not sure right now :)