No such table error : sqlite (moor) tables created only after clearing app data in Flutter

user_12266950 0
Viewed 1222

I am creating a database using Moor for my flutter app. It has 5 tables and corresponding DAOs (Data Access Objects).

@UseMoor(
    tables: [Cart, CartTotal, Books, Subjects, Images],
    daos: [CartDao, CartTotalDao, BooksDao, SubjectsDao, SearchDao, ImagesDao])
class AppDatabase extends _$AppDatabase {
  AppDatabase()
      : super(FlutterQueryExecutor.inDatabaseFolder(
            path: 'db.sqlite', logStatements: true));

  @override
  int get schemaVersion => 1;
}

When I run the app on an emulator, everything works fine. All tables are created properly. The error occurs when I run the app on an actual device. 3 tables are created fine and populated with data from the cloud as expected. The other 2 tables for cart and total are not created and throw a no such table error when accessing the cart page.

I/flutter ( 5321): Moor: Sent SELECT * FROM cart WHERE name != ? with args [Total]
E/SQLiteLog( 5321): (1) no such table: cart

Also no logging from my database update service that runs at app start is shown. Once I clear the app data from the app info section on my device and start the app again, everything works fine again.

I/flutter ( 5717): Moor: Sent CREATE TABLE IF NOT EXISTS books (amazon_link VARCHAR NULL, author VARCHAR NOT NULL, cost INTEGER NOT NULL, cover_url VARCHAR NULL, description VARCHAR NULL, discounted_cost INTEGER NULL, flipkart_link VARCHAR NULL, isbn INTEGER NOT NULL, language VARCHAR NULL, name VARCHAR NOT NULL, pages INTEGER NULL, publication_number INTEGER NOT NULL, publication_status VARCHAR NOT NULL, youtube_link VARCHAR NULL, PRIMARY KEY (publication_number)); with args []
I/flutter ( 5717): Moor: Sent CREATE TABLE IF NOT EXISTS subjects (publication_number INTEGER NOT NULL, sub_name VARCHAR NOT NULL, PRIMARY KEY (publication_number, sub_name)); with args []
I/flutter ( 5717): Moor: Sent CREATE TABLE IF NOT EXISTS images (url VARCHAR NOT NULL, publication_number INTEGER NOT NULL); with args []
I/flutter ( 5717): Moor: Sent CREATE TABLE IF NOT EXISTS cart (name VARCHAR NOT NULL, quantity INTEGER NOT NULL, cost INTEGER NOT NULL, discounted_cost INTEGER NOT NULL, PRIMARY KEY (name)); with args []
I/flutter ( 5717): Moor: Sent CREATE TABLE IF NOT EXISTS cart_total (name VARCHAR NOT NULL, quantity INTEGER NOT NULL, cost INTEGER NOT NULL, discounted_cost INTEGER NOT NULL, PRIMARY KEY (name)); with args []

Opening the cart page works as expected.

I/flutter ( 5717): Moor: Sent SELECT * FROM cart WHERE name != ? with args [Total]
I/flutter ( 5717): Moor: Sent SELECT * FROM cart_total WHERE name = ?; with args [Total]

Am I missing something here? I also searched if there are any limits to creating tables but could not find any. Since moor works by generating code using build_runner, I also tried clearing the old build and rebuilding again.

Since the app runs fine on an emulator and on device after clearing data, I am assuming that my code is correct. What could be the problem?

flutter-moor flutter sqlite android database
1 Answers

So the issue was not with moor or my code. As the app is in not released yet and I am still making changes to the database, I did not want make changes to schema version and provide a migration strategy.

My understanding was that the database will get deleted on app uninstall and whatever changes made to the database will be the new schema for the app next time I install it. That is not the case. Atleast not always.

Turns out Google Drive backs up your app's database file on some devices and restores it on app reinstall. That is what was causing weird behaviour in my app.

Here's a link to the github issue for detailed explanation.

user_122669500
Related