DatabaseException(no such column: created_at (code 1)

[figengungor]

I'm testing migrations and I'm getting this exception after adding createdAt column.

class SearchHistory extends Table {
  IntColumn get id => integer().autoIncrement()();

  TextColumn get word => text()();

  DateTimeColumn get createdAt => dateTime().nullable()();
}

 @UseMoor(tables: [SearchHistory])
class TdkDatabase extends _$TdkDatabase {
  // we tell the database where to store the data with this constructor
  TdkDatabase()
      : super(FlutterQueryExecutor.inDatabaseFolder(
            path: 'db.sqlite', logStatements: true));

  // you should bump this number whenever you change or add a table definition. Migrations
  // are covered later in this readme.
  @override
  int get schemaVersion => 2;

  @override
  MigrationStrategy get migration => MigrationStrategy(onCreate: (Migrator m) {
        return m.createAllTables();
      }, onUpgrade: (Migrator m, int from, int to) async {
        if (from == 1) {
          // we added the createdAt property in the change from version 1
          await m.addColumn(searchHistory, searchHistory.createdAt);
        }
      });

...

I also tried to add current date as a default value to createdAt like this but this time I get this error:

DateTimeColumn get createdAt => dateTime().withDefault(currentDateAndTime)();

Cannot add a column with non-constant default

[simolus3]

Thanks for filing an issue. I just tried to reproduce your example by

1. First creating the database without the createdAt column, schemaVersion = 1 and without overriding migration
2. Adding the column, re-running the build, bumping the schema version and copying your migration code

For me, that schema upgrade worked and the migration script ran. Setting the default to currentDateAndTime worked for me as well. On which device did you test the app? It might be an outdated sqlite version, although the sql generated by moor should be supported by older versions as well.

[figengungor]

Thanks for looking into it. I skipped migration part for now but I'll try to create a minimal example soon and share the results with you.

Device: LG G5 Android 8.0.0 moor_flutter: ^2.0.0 moor_generator: ^2.0.1

[pedromassango]

Any update on this? I'm running the same issue on Xiaomi k20 (Android 9)

[simolus3]

I've never been able to reproduce this myself, but if you can reproduce this with a small example I'd be glad to take a look.

[pedromassango]

I don't know how to reproduce it, but I think this is related https://github.com/simolus3/moor/issues/184

I must provide more information soon

[pedromassango]

So I made a update on my app (where I disabled the automated backup made by Android) and my users says that the bug was fixed. Looks like it was caused by the old version of my database (as said on #184 )

[duncwillz]

@pedromassango Did you have to also write any migrations for that before doing this -> (where I disabled the automated backup made by Android) , I am curious

[apoleo88]

I had the same issue. I use the encrypted version.

  drift: ^1.7.0
  sqlcipher_flutter_libs: ^0.5.0

Added to a Table: DateTimeColumn get updated_at => dateTime().nullable().withDefault(currentDateAndTime)();

Migration: await m.addColumn(activityEntries, activityEntries.updated_at);

However, I gott the error:

Drift: Sent ALTER TABLE activity_entries ADD COLUMN updated_at INTEGER NULL DEFAULT (strftime('%s', CURRENT_TIMESTAMP)); with args []
E/flutter (31395): [ERROR:flutter/lib/ui/ui_dart_state.cc(198)] Unhandled Exception: SqliteException(1): Cannot add a column with non-constant default
E/flutter (31395):   Causing statement: ALTER TABLE activity_entries ADD COLUMN updated_at INTEGER NULL DEFAULT (strftime('%s', CURRENT_TIMESTAMP));
E/flutter (31395): #0      DatabaseImpl.execute (package:sqlite3/src/ffi/impl/database.dart:94:9)
E/flutter (31395): #1      Sqlite3Delegate._runWithArgs (package:drift/src/sqlite3/database.dart:108:11)
E/flutter (31395): #2      Sqlite3Delegate.runCustom (package:drift/src/sqlite3/database.dart:122:11)
E/flutter (31395): #3      _BaseExecutor.runCustom.<anonymous closure> (package:drift/src/runtime/executor/helpers/engines.dart:109:19)
E/flutter (31395): #4      _BaseExecutor._synchronized (package:drift/src/runtime/executor/helpers/engines.dart:55:20)
E/flutter (31395): #5      _BaseExecutor.runCustom (package:drift/src/runtime/executor/helpers/engines.dart:105:12)
E/flutter (31395): #6      DatabaseConnectionUser.customStatement.<anonymous closure> (package:drift/src/runtime/api/connection_user.dart:407:23)
E/flutter (31395): #7      DatabaseConnectionUser.doWhenOpened.<anonymous closure> (package:drift/src/runtime/api/connection_user.dart:160:64)
E/flutter (31395): #8      _rootRunUnary (dart:async/zone.dart:1434:47)
E/flutter (31395): #9      _CustomZone.runUnary (dart:async/zone.dart:1335:19)
E/flutter (31395): <asynchronous suspension>
E/flutter (31395): #10     MyDatabase.migration.<anonymous closure> (package:myApp/db/db_classes.dart:3299:10)
E/flutter (31395): <asynchronous suspension>
E/flutter (31395): #11     GeneratedDatabase.beforeOpen.<anonymous closure> (package:drift/src/runtime/api/db_base.dart:116:9)
E/flutter (31395): <asynchronous suspension>
E/flutter (31395): #12     DelegatedDatabase._runMigrations (package:drift/src/runtime/executor/helpers/engines.dart:368:5)
E/flutter (31395): <asynchronous suspension>
E/flutter (31395): #13     DelegatedDatabase.ensureOpen.<anonymous closure> (package:drift/src/runtime/executor/helpers/engines.dart:336:7)
E/flutter (31395): <asynchronous suspension>
E/flutter (31395): #14     NonNullableCancellationExtension.resultOrNullIfCancelled (package:drift/src/runtime/cancellation_zone.dart:62:14)
E/flutter (31395): <asynchronous suspension>
E/flutter (31395): #15     QueryStream.fetchAndEmitData (package:drift/src/runtime/executor/stream_queries.dart:306:20)
E/flutter (31395): <asynchronous suspension>
E/flutter (31395): 

Tested on Android Emulator Google Pixel 3a The project I am working on is using moor for years, not sure if at some point I messed up with the SQL library, but the pubspec doesn't include any more than the dependencies recommended.

[simolus3]

@apoleo88 That's an interesting error. I was afraid it may have have been introduced by a recent sqlite3 version, but that statement works for me with the latest sqlite3 and sqlcipher. I'm not sure how to take a better look at what's going on without more details. Is there more you could share (how you open the database, other dependencies?).

[apoleo88]

Thank you for your fast reply and interest : )

I tried to reproduce it but I have not been successful...

Is there any place where I should check for weird overriding I could have done in the past? I don't use other dependencies that use SQL.

In my main.dart

import 'package:sqlite3/open.dart';
void main() async {
  ..
  open.overrideFor(OperatingSystem.android, () => DynamicLibrary.open('libsqlcipher.so'));
  ..
}

Other dependencies:

dependencies:
  drift: ^1.7.0
  sqlcipher_flutter_libs: ^0.5.0
  path_provider: ^2.0.11
  path: ^1.8.2

dependency_overrides:
  analyzer: ^4.0.0

dev_dependencies:
  build_runner: ^2.1.11
  build_resolvers: ^2.0.9
  source_gen : ^1.2.2

I use these settings:

targets:
  $default:
    builders:
      drift_dev:
        options:
          write_from_json_string_constructor: true
          mutable_classes: true

import 'package:drift/drift.dart';
import 'package:drift/native.dart';

LazyDatabase _openConnection({String databasePath}) {
  return LazyDatabase(() async {
    driftRuntimeOptions.dontWarnAboutMultipleDatabases = true;

    final file = File(databasePath);
    String db_key = await getKey('masterKey');
    return NativeDatabase(file, logStatements: true,
        setup: (rawDb) async {
          assert(_debugCheckHasCipher(rawDb));
          rawDb.execute("PRAGMA key = '"+ db_key +"';");
        });
  });
}
bool _debugCheckHasCipher(database) {
  return database.select('PRAGMA cipher_version;').isNotEmpty;
}