Moor: Delete sqlite database file

[MuTe33]

I'm pretty sure this is not a bug, but I messed up my code and can't find an answer in the internet and within the docs which is the reason I'm asking here. If this is not appropriate, I apologize and please delete this post.

Nevertheless, I managed to create a sqlite database with storing data, etc. Then I wanted to add a column, so I first added the new column, increased the schema version, defined a migration strategy and then run the build_runner. Still, everything worked fine. I did some more coding where I had to update my table, after having some issues I just wanted to start from scratch, so I deleted and reinstalled the app on the simulator hoping to delete the db.sqlite file and start from the beginning.

Unfortunately, I messed up things and now I'm getting the following warning:

flutter: WARNING (moor): It looks like you've created the database MyDatabase multiple times. When these two databases use the same QueryExecutor, race conditions will occur and might corrupt the database.

I also forgot my schema version, therefore I'm a bit stuck.

So my questions are:

• How can I wipe everything and start from the beginning without having to create a new project?
• Where can I find the db.sqlite file in order to delete it? I couldn't find it in my project or in any other directory.

I appreciate any assistance and am sorry if this is not the right place to ask these kinds of questions.

[simolus3]

This is absolutely the right place to ask these kinds of questions! If moor is too hard to use, the documentation and error messages should be improved.

In this case, the error message is actually a bit confusing, and I've changed it so it's clearer in the next moor version. Here, the database doesn't refer to a physical file on the device, but rather the MyDatabase class that you wrote. It means that you're calling the constructor of the MyDatabase class multiple times, which can cause problems. How to fix this can depend on your app, but the faq give some general advice. If you tell me about a particular state management solution you use (e.g. bloc, provider, vanilla Flutter, ...), I can help more. The error message should also contain a stack trace that tells you where the database has been opened a second time, which can help identifying the problem.

If you still want to start over, I would advice you to delete your apps data in the system settings. It's probably not going to help here though, as the problems are unrelated.

[MuTe33]

Thanks for the quick answer!

You absolutely right, I called the constructor of the MyDatabase class twice. I fixed that issue.

How can I delete the app's data in the system settings? I'm running the app on a simulator and can't find the settings.

From what I know, in sqlite you can't just delete databases because it is just a file that you can access. by defining the path such as MyDatabase() : super(FlutterQueryExecutor.inDatabaseFolder(path: 'db1.sqlite'));, where can I find this file?

The reason why I want to start over and delete everything is, I want to make bigger changes to the table and don't want to store the old database still on the phone and take storage unnecessarily.

Many thanks!

[simolus3]

I assume you're using an iOS simulator? If so, see this stackoverflow answer.
Most Android simulators should ship with the settings app, but you can also long-press your app's icon in the drawer to get to a page where you can delete its data.

Where the file is stored also depends on the type of simulator. For iOS simulators you can find the right directory based on this SO answer. Android simulators use their own file system that you can't access from the outside I think.

__EDIT__: In most cases, just uninstalling the app should do the trick.

[MuTe33]

I just did all the steps and it worked absolutely fine! Thank you again for your help.

I'm getting an error when I try to implement an isFavorite functionality.

I defined my class as such:

class Favorites extends Table {
  TextColumn get title => text()();
  IntColumn get index => integer()();
}

My Method:

void addFavorite(String t, int i) {
    var favorite = Favorite(
      title: t,
      index: i,
    );
    into(favorites).insert(favorite);
  }

My Stream which I'm watching:

Stream<bool> isFavorite(String t) {
    return select(favorites).watch().map((favoritesList) =>
        favoritesList.any((favorite) => favorite.title == t));
   }
}

My add event is as follows:
onPressed: () => db.addFavorite(party["name"], index),

My Exception which I'm getting:

[VERBOSE-2:ui_dart_state.cc(157)] Unhandled Exception: DatabaseException(Error Domain=FMDatabase Code=1 "near "index": syntax error" UserInfo={NSLocalizedDescription=near "index": syntax error}) sql 'INSERT INTO favorites (title, index) VALUES (?, ?)' args [Pacha, 1]}
#0      wrapDatabaseException 
package:sqflite/src/exception_impl.dart:12
<asynchronous suspension>
#1      SqfliteDatabaseFactoryImpl.wrapDatabaseException 
package:sqflite/src/factory_impl.dart:25
#2      SqfliteDatabaseMixin.safeInvokeMethod 
package:sqflite/src/database_mixin.dart:188
#3      SqfliteDatabaseMixin.txnRawInsert.<anonymous closure> 
package:sqflite/src/database_mixin.dart:363
#4      SqfliteDatabaseMixin.txnSynchronized.<anonymous closure> 
package:sqflite/src/database_mixin.dart:307
#5      BasicLock.synchronized 
package:synchronized/src/basic_lock.dart:31
#6      SqfliteDatabaseMixin.txnSynchronized 
package:sqflite/src/database_mixin.dart:303
#7      SqfliteDatabaseMixin.txnWriteSynchroniz<…>

I'm not sure what I'm doing wrong or missing. In the last bit of the exception the arguments [Pacha, 1] are the correct values but it won't insert them into the database. I checked the types of values and both are correct. Unfortunately, I can't find anything about this type of exception.

I appreciate any help.

[simolus3]

That's actually a bug in moor! Since index is a keyword in sql, moor should escape it when generating the sql statement. This will be fixed in the next moor release. In the meantime, can you change the column definition and re-run the builder?

class Favorites extends Table {
  TextColumn get title => text()();
  IntColumn get index => integer().named('my_index')();
}

[MuTe33]

Good to know than!

I did it the way you said. It didn't work as intended, but that may be my fault. The error message was as follows:

[VERBOSE-2:ui_dart_state.cc(157)] Unhandled Exception: DatabaseException(Error Domain=FMDatabase Code=1 "table favorites has no column named my_index" UserInfo={NSLocalizedDescription=table favorites has no column named my_index}) sql 'INSERT INTO favorites (title, my_index) VALUES (?, ?)' args [Crux, 2]}
#0      wrapDatabaseException 
package:sqflite/src/exception_impl.dart:12
<asynchronous suspension>
#1      SqfliteDatabaseFactoryImpl.wrapDatabaseException 
package:sqflite/src/factory_impl.dart:25
#2      SqfliteDatabaseMixin.safeInvokeMethod 
package:sqflite/src/database_mixin.dart:188
#3      SqfliteDatabaseMixin.txnRawInsert.<anonymous closure> 
package:sqflite/src/database_mixin.dart:363
#4      SqfliteDatabaseMixin.txnSynchronized.<anonymous closure> 
package:sqflite/src/database_mixin.dart:307
#5      BasicLock.synchronized 
package:synchronized/src/basic_lock.dart:31
#6      SqfliteDatabaseMixin.txnSynchronized 
package:sqflite/src/database_mixin.dart:303
#7      S<…>

Can this happen if I change the existing column, increase the version number and run the builder, but don't define a migration strategy? I didn't know how to define a migration strategy if I only make a change to an existing column without adding or deleting one.
However, the document favourites.g.dart was created with the correct column name, but with this error code.

So I just deleted everything and started again with copy-paste (yes this is not the best solution, but for me, at this moment it is the most efficient). And lo and behold, it worked when I changed the column name. Thanks for that!

There is one question left. When exactly do I always have to define a migration strategy? Only if I add columns and tables or if I also make changes to an existing column, as in this example, when I added the .named() addition to the existing column.

I really appreciate your help! Overall it solved my issue.

[simolus3]

Can this happen if I change the existing column, increase the version number and run the builder, but don't define a migration strategy?

Exactly. The database file on the device still has the old column. The reason we don't have a high-level api to rename columns is that old sqlite versions don't have builtin support for that. Moor could help here (#240), but it's a lot of work to implement.

started again with copy-paste

I imagine you could just delete your app's data again. The problem is only with the database file on the device.

When exactly do I always have to define a migration strategy?

For every change to existing tables. You need to manually increase the schema version after making any changes to your tables, and you need to write a custom migration script with the onUpgrade handler. During development it can be more efficient to just delete your app's data and re-install, but as soon as you shipped a version to users you need to handle migrations.

The only reason you don't have to write a migration when you first write the database is that moor creates all tables by default. That's almost always what you want to do, so writing an onCreate handler isn't necessary in most cases.

[MuTe33]

Thanks for taking the time and helping me with those issues! Much appreciated.

[ErmiCode]

@MuTe33 you can actually do flutter clean and regenerate using moor generator. If you are using a physical device, you can just clear storage on Android. I don't if you know do that on iOS.

[ukor]

@MuTe33 @simolus3 Can you point me to a tutorial on how to call the database class once, I have followed the FAQ suggestion but no luck still.
All the tutorial on kiwi and get_it are outdated (The doc ain't beginners friendly). I separated all my business logics from my UI, provider is not an option.

[simolus3]

If you don't want to use another library (which is fine!), there's nothing wrong with a singleton approach:

@UseMoor(...)
class MyDatabase extends _$MyDatabase {
  // a private constructor helps so that it's not accidentally called multiple times
  MyDatabase._(): super(FlutterQueryExecutor(...))
}

MyDatabase _instance;
MyDatabase get database {
  return _instance ??= MyDatabase._();
}

[blinkn]

Hi, I have a similar question. If I want to physically delete the database file, and instantiate again a new one for MyDatabase to 'recreate' the db from scratch, is there a clean solution for that? I'm doing the following:

1. I use get_it to store a single instance of moor database class across all my app, so first of all I unregister this instance from get_it;
2. Then, I close the database with close() method;
3. Then I delete the db.sqlite file;
4. Then I create a new instance of moor database (at this point all tables are recreated and all migrations are executed);
5. Then finally I register this new instance in get_it and all consumer widgets now, when instantiated, use this new instance;

Everything seems to work fine, but, sometimes randomly I get a SQLException disk I/O error when doing this process, I think it's due to deleting the db.sqlite file physically that is a bad practice.

So, is there any alternative for this? I would like to drop database and recreate it again from scratch.

[simolus3]

If you want to drop the database schema, this should work:

Future deleteAll() async {
    final m = createMigrator();
    // Going through tables in reverse because they are sorted for foreign keys
    for (final table in allTables.toList().reversed) {
      await m.deleteTable(table.actualTableName);
    }
  }

[blinkn]

Thank you sir @simolus3, it works perfectly!
If anyone has the same question, I just added a new method to call m.createAll() to effectively create all the tables again.

Edit: I forgot to mention that doing this way I didn't had to create a new instance of moor database.

[edocabhi]

If you don't want to use another library (which is fine!), there's nothing wrong with a singleton approach:

@UseMoor(...)
class MyDatabase extends _$MyDatabase {
  // a private constructor helps so that it's not accidentally called multiple times
  MyDatabase._(): super(FlutterQueryExecutor(...))
}

MyDatabase _instance;
MyDatabase get database {
  return _instance ??= MyDatabase();
}

@simolus3

In the return statement using MyDatabase() I get "Try using the named constructor as default constructor is absent". If I use the named private constructor MyDatabase._() then accessing it from the inside the bloc like TrapsDao(MyDatabase()) no longer works.

Could you please point me towards doing this the right way

[simolus3]

In the return statement using MyDatabase() I get "Try using the named constructor as default constructor is absent".

Right - that should be ??= MyDatabase._(). I've edited my comment.

then accessing it from the inside the bloc like TrapsDao(MyDatabase()) no longer works

If you used that to construct daos, you might have multiple instances of MyDatabase, which can cause problems. You should be able to just use TapsDao(database) with the database getter from that snippet.

[talamaska]

I'm using Provider, and I saw this message

flutter: WARNING (moor): It looks like you've created the database MyDatabase multiple times.

What I should do? I though the Provider will take care of having only one instance of the class. And I have set dispose to db.close(), I have also set it to lazy: false. Am I missing something?

[edocabhi]

@talamaska Use singleton method as shown above by @simolus3
That should fix your warning.

[davidmartos96]

@talamaska Is your Moor database Provider placed above your MaterialApp widget? If it's placed above you shouldn't need to use a Singleton.

[talamaska]

@davidmartos96 it is placed above, I also though that, until recently I saw this log while hot refreshing my app on the emulator.

MyApp {
.....
return MultiProvider(
      providers: [
         Provider<AppDatabase>(
           create: (_) => AppDatabase(),
           lazy: false,
           dispose: (_, AppDatabase db) => db.close(),
         ),
       ],
       child: MaterialApp(...)
   );
}