No such column error on existing column.

[muddi900]

I am getting this error on insert

] Unhandled Exception: SqliteException(1): while preparing statement, no such column: message_index, SQL logic error (code 1)
E/flutter ( 7727):   Causing statement: INSERT INTO "chats" ("id", "last_access_time") VALUES (?, ?)

The error is being caused by a trigger:

CREATE TRIGGER IF NOT EXISTS insert_system_message
AFTER INSERT ON chats
BEGIN
    INSERT INTO messages (role,content,messages.message_index,"chat_id")
    VALUES('system' ,'You are a helpful chat bot that limits your responses to 300 characters.' ,0 , NEW.id);
END;

However the messages has the messageIndex table.

db.dart

class Messages extends Table {
  IntColumn get id => integer().autoIncrement()();
  TextColumn get role => text()();
  TextColumn get content => text()();
  IntColumn get messageIndex => integer().nullable()();
  TextColumn get chatId => text().references(Chats, #id)();
  IntColumn get previousMessage =>
      integer().nullable().references(Messages, #id)();
}

db.g.dart

class Message extends DataClass implements Insertable<Message> {
  final int id;
  final String role;
  final String content;
  final int? messageIndex;
  final String chatId;
  final int? previousMessage;
  const Message(
      {required this.id,
      required this.role,
      required this.content,
      this.messageIndex,
      required this.chatId,
      this.previousMessage});
  @override
  Map<String, Expression> toColumns(bool nullToAbsent) {
    final map = <String, Expression>{};
    map['id'] = Variable<int>(id);
    map['role'] = Variable<String>(role);
    map['content'] = Variable<String>(content);
    if (!nullToAbsent || messageIndex != null) {
      map['message_index'] = Variable<int>(messageIndex);
    }
    map['chat_id'] = Variable<String>(chatId);
    if (!nullToAbsent || previousMessage != null) {
      map['previous_message'] = Variable<int>(previousMessage);
    }
    return map;
  }

[simolus3]

Has messageIndex been added in a later version of the database (e.g., could this be a migration issue)? You can check that the actual database schema matches what drift is expecting like this.

[muddi900]

I uninstall the app from the Android Emulator, which should delete the db as well.

CREATE TRIGGER IF NOT EXISTS insert_system_message
AFTER INSERT ON chats
BEGIN
    INSERT INTO messages (role,content,message_index,"chat_id")
    VALUES('system','You are a helpful chat bot that limits your responses to 300 characters.',0,NEW.id);
END;

This is the current def. I deleted the generated file and ran build_runner again.

[INFO] Generating build script completed, took 686ms
[INFO] Reading cached asset graph completed, took 499ms
[INFO] Checking for updates since last build completed, took 3.3s
[WARNING] No actions completed for 17.8s, waiting on:
  - drift_dev on lib/models/db.expr.temp.dart
  - drift_dev on lib/models/db.dart
  - drift_dev on lib/widgets/image_history.dart
  - drift_dev on lib/screens/chat.dart
  - drift_dev on lib/widgets/image_widget.dart
  .. and 8 more

[muddi900]

Is there a way I can access the sqlite file using sqlite shell? Because this is baffling.

[simolus3]

It really is. If you're using a NativeDatabase, you could print the path to the database and pull it via adb. Once you have access to the file, you can use the sqlite3 CLI tools locally.

[muddi900]

I figured it out. The PRAGMA output shows that the field is nullable

cid         name        type        notnull     dflt_value  pk
----------  ----------  ----------  ----------  ----------  ----------
0           id          INTEGER     1                       1
1           role        TEXT        1                       0
2           content     TEXT        1                       0
3           message_in  INTEGER     0                       0
4           chat_id     TEXT        1                       0
5           previous_m  INTEGER     0                       0

Apparently initiating a nullable field in an insert statement throws this error. This true for running a purely sql runtime.

I am updating the field with another trigger.