Cannot add a NOT NULL column with default value NULL

[stefanorumi7]

I am running for the first time my project on a real Android device (Android 8.1 bc I need to target old phones). And I am getting this error: Cannot add a NOT NULL column with default value NULL. It is crashing when adding the first ID from the migrations

const List<MigrationCommand> _migration_xxx_up = [
  InsertTable('EntityA'),
  InsertColumn(
    'id',
    Column.varchar,
    onTable: 'EntityA',
    nullable: false,
    unique: true,
  ),
  ...
  CreateIndex(columns: ['id'], onTable: 'EntityA', unique: true),
];

No matter how I change the code and migrations I still get that same error.
Could it be that the project is not compatible with SQLite version 3.19.4?

[stefanorumi7]

Note that this is happening with no records in the table and a fresh installation of the app.

[stefanorumi7]

After manually deleting all nullable: true statements from all migrations the app finally ran. I wonder if there is a way to target Android 8.1 and still get the nullable: true statements to somehow work by using a different command against the DB.

[KiddoV]

Related to my issue: #620

[tshedor]

@stefanorumi7 how were the nullable: true statements generated? Based on your code it seems like the error wouldn't have come from 'id'. Can you provide your full error?

[stefanorumi7]

It includes id but is not restricted to it. I had multiple Column.varchar, Column.boolean and Column.datetime yielding this error when using this version of Sqlite.

If I uncomment the nullable: true from an id column on a random entity, this is the log I get:

I/flutter (10551): │ DatabaseException(no such column: id (code 1): , while compiling: SELECT COUNT() FROM Entity WHERE id = ? LIMIT 1) sql 'SELECT COUNT() FROM Entity WHERE id = ? LIMIT 1' args [some UUID]
I/flutter (10551): │ #0 wrapDatabaseException (package:sqflite_platform_interface/src/platform_exception.dart:12:7)
I/flutter (10551): │ #1
I/flutter (10551): │ #2 SqfliteDatabaseMixin.txnRawQuery. (package:sqflite_common/src/database_mixin.dart:678:30)
I/flutter (10551): │ #3
I/flutter (10551): │ #4 BasicLock.synchronized (package:synchronized/src/basic_lock.dart:38:16)
I/flutter (10551): │ #5
I/flutter (10551): │ #6 SqfliteDatabaseMixin.txnSynchronized (package:sqflite_common/src/database_mixin.dart:589:14)
I/flutter (10551): │ #7

[Skipping app specific logs]

I/flutter (10551): │ ⛔ #1 SqfliteDatabaseMixin.txnRawQuery. (package:sqflite_common/src/database_mixin.dart:678:30)
I/flutter (10551): │ ⛔
I/flutter (10551): │ ⛔ #2 BasicLock.synchronized (package:synchronized/src/basic_lock.dart:38:16)
I/flutter (10551): │ ⛔
I/flutter (10551): │ ⛔ #3 SqfliteDatabaseMixin.txnSynchronized (package:sqflite_common/src/database_mixin.dart:589:14)
I/flutter (10551): │ ⛔
I/flutter (10551): │ ⛔ #4 SqliteProvider.exists (package:brick_sqlite/src/sqlite_provider.dart:104:24)
I/flutter (10551): │ ⛔
I/flutter (10551): │ ⛔ #5 OfflineFirstRepository.exists (package:brick_offline_first/src/offline_first_repository.dart:167:12)
I/flutter (10551): │ ⛔
I/flutter (10551): │ ⛔ #6 OfflineFirstRepository.get (package:brick_offline_first/src/offline_first_repository.dart:206:27)
I/flutter (10551): │ ⛔
I/flutter (10551): │ ⛔ #7 OfflineFirstWithSupabaseRepository.get (package:brick_offline_first_with_supabase/src/offline_first_with_supabase_repository.dart:105:14)
I/flutter (10551): │ ⛔

[Skipping app specific logs]

[tshedor]

@stefanorumi7 I mean, how were your migrations generated? I'm looking for the model definition.

Also if you could please format your code with <backtick><backtick><backtick>dart it would be really helpful to me

[stefanorumi7]

Here is a sample migration from my model in which you can see the lines I had to comment out to make the code work on SQLite version 3.19.4.

// GENERATED CODE EDIT WITH CAUTION
// THIS FILE **WILL NOT** BE REGENERATED
// This file should be version controlled and can be manually edited.
part of 'schema.g.dart';

// While migrations are intelligently created, the difference between some commands, such as
// DropTable vs. RenameTable, cannot be determined. For this reason, please review migrations after
// they are created to ensure the correct inference was made.

// The migration version must **always** mirror the file name

const List<MigrationCommand> _migration_20250606083019_up = [
  InsertTable('Entity'),
  InsertColumn(
    'id',
    Column.varchar,
    onTable: 'Entity',
    // nullable: false,
    unique: true,
  ),
  InsertColumn('prop', Column.varchar, onTable: 'Entity'),
  InsertColumn(
    'creation_date',
    Column.datetime,
    onTable: 'Entity',
    // nullable: false,
  ),
  CreateIndex(columns: ['id'], onTable: 'Entity', unique: true),
];

const List<MigrationCommand> _migration_20250606083019_down = [
  DropTable('Entity'),
  DropColumn('id', onTable: 'Entity'),
  DropColumn('prop', onTable: 'Entity'),
  DropColumn('creation_date', onTable: 'Entity'),
  DropIndex('index_Entity_on_id'),
];

//
// DO NOT EDIT BELOW THIS LINE
//

@Migratable(
  version: '20250606083019',
  up: _migration_20250606083019_up,
  down: _migration_20250606083019_down,
)
class Migration20250606083019 extends Migration {
  const Migration20250606083019()
    : super(
        version: 20250606083019,
        up: _migration_20250606083019_up,
        down: _migration_20250606083019_down,
      );
}

[stefanorumi7]

This issue is still valid, and a bit "too in-depth" for me to get in with a PR.

[KiddoV]

@stefanorumi7 please check out my PR first. Might be related to yours.

#669

[hortigado]

how you have configured the model?

[KiddoV]

@hortigado
I have a case like this

@ConnectOfflineFirstWithSupabase(
  supabaseConfig: SupabaseSerializable(tableName: "users")
)
@MappableClass()
class User extends OfflineFirstWithSupabaseModel with UserMappable {
   [...]
  @Supabase(unique: true) @Sqlite(index: true)
  final String? username;
}

Where username is optional but I have to keep it unique.
Old generator will failed on this.