SqliteException: Cannot add a NOT NULL column with default value NULL

[uocnb]

Hello, I'm got this error when trying to add new not null column with clientDefault()

example column

  TextColumn get uuid => text().clientDefault(() => UUID.v4())();

add via migration:

if (from == 1 && to == 2) {
        await m.addColumn(someTable, someTable.uuid);
}

error detail:

SqliteException: Cannot add a NOT NULL column with default value NULL
flutter: #0      Database.execute 
package:moor_ffi/…/impl/database.dart:114
#1      _VmDelegate._runWithArgs 
package:moor_ffi/src/vm_database.dart:61
#2      _VmDelegate.runCustom 
package:moor_ffi/src/vm_database.dart:71
#3      _ExecutorWithQueryDelegate.runCustom.<anonymous closure> 
package:moor/…/helpers/engines.dart:79
#4      BasicLock.synchronized 
package:synchronized/src/basic_lock.dart:32
#5      _ExecutorWithQueryDelegate._synchronized 
package:moor/…/helpers/engines.dart:22
#6      _ExecutorWithQueryDelegate.runCustom 
package:moor/…/helpers/engines.dart:76
#7      Migrator.issueCustomQuery 
package:moor/…/query_builder/migration.dart:198
#8      Migrator.addColumn 
package:moor/…/query_builder/migration.dart:193
#9      MyDatabase.migration.<anonymous closure> 
package:dich_ly_so/db/database.dart:162
#10     GeneratedDatabase.handleDatabaseVersionChange 
package:moor/…/api/db_base.dart:113
#11     DelegatedDatabase._runMigrations 
package:moor/…/helpers/engines.dart:284
<asynchronous suspension>
#12     DelegatedDatabase.ensureOpen.<anonymous closure> 
#12     DelegatedDatabase.ensureOpen.<anonymous closure> 
package:moor/…/helpers/engines.dart:247
<asynchronous suspension>
#13     DelegatedDatabase.ensureOpen.<anonymous closure> (package:moor/src/runtime/executor/helpers/engines.dart)
#14     BasicLock.synchronized 
package:synchronized/src/basic_lock.dart:32
#15     DelegatedDatabase.ensureOpen 
package:moor/…/helpers/engines.dart:238
#16     LazyDatabase.ensureOpen.<anonymous closure> 
package:moor/…/utils/lazy_database.dart:50
#17     _rootRunUnary  (dart:async/zone.dart:1155:38)
...

Would be nice to have it handle by moor instead of write issueCustomQuery() for each table.

[simolus3]

Hm, good point. Unfortunately it's not easy for moor to pull off. We'd have to create a temporary table with a nullable uuid column, update each row with the client default function and finally copy that back to the main table.

[uocnb]

Another way is modify table with nullable column, update each row with client default than alter the column to remove nullable.

[simolus3]

alter the column to remove nullable

Sadly it's not that easy in sqlite: https://stackoverflow.com/a/4007086/3260197

[uocnb]

Ah yeah, that's true. So I think it can be implement with part of backup / restore or could be better if we have generated DAO as helper methods to make it easier.