Unhandled Exception: SqliteException(1): no such table: main.checkLists, SQL logic error (code 1) [ In Moor]

Question

I have found most people solved this issue simply by cleaning and re building their flutter databses by handling migrations in Moor. However, I have done the same but to no avail.

Current error:

[ERROR:flutter/lib/ui/ui_dart_state.cc(209)] Unhandled Exception: SqliteException(1): no such table: main.checkLists, SQL logic error (code 1)

Which occurs when I insert a new task into tasks

I have two Tables in my database currently.

1. CheckLists
2. Tasks

Tasks was added later and I created a MigrationStrategy as such:

MigrationStrategy get migration => MigrationStrategy(
    onCreate: (m) async {
      await m.createAll(); // create all tables
      final TasksCompanion task = TasksCompanion.insert(checkListId: 1, toDo:'First'); 
      await into(tasks).insert(task); // insert on first run.
    },

    onUpgrade: (migrator, from, to) async {
      if(from == 1){
        await migrator.createTable(tasks);
      }
    },
    beforeOpen: (details) async {
      await customStatement('PRAGMA foreign_keys = ON');
    },
  );

Insertion on first run is successful. Meaning Tasks table is created. I don't know what else to do since there are no other compiler errors.

Note that the error states no such table: main.checkLists yet the error occurs on insertion of task to tasks table

Here is the Insertion Algorithm I use

in moorDatabase.dart

// inside Task Dao    
Future insertTask(Insertable<Task> task){
    return into(tasks).insert(task);
}

in tasksScreen.dart

addTask(BuildContext context, String toDo, DateTime createdAt){
    final database = Provider.of<AppDatabase>(context, listen: false);
    final tasksDao = database.tasksDao;
    final TasksCompanion task = TasksCompanion.insert(checkListId: widget.checkListId, toDo: toDo, createdAt: Value.ofNullable(createdAt));
    tasksDao.insertTask(task);
}

Answer 1

Okay so I figured it out. Simply had to add this line to the onUpgrade method inside my migration strategy because the column checkListId was added later.

await migrator.addColumn(tasks, tasks.checkListId);

Also bumped my schema version before cleaning an running build_runner

Answer 2

I had the same problem when I changed the table name in the code. I fixed it by deleting the application from the emulator or from the physical phone and re-running the compilation of the application

Answer 3

Just delete the database. Mine was in Documents. The problem is usually due to the project name. Delete the project and then run it again.