SQLite file is modified with select statements about drift

[laugen]

Thanks for the quick reply!

As suggested I tried to use a sqlite3 read-only database connection and passed it to NativeDatabase.opened to use it with Drift.

See branch native_database_opened

DelegatedDatabase connect({required String databaseName}) {
    String path = databasePath(databaseName: databaseName);
    Database database = sqlite3.open(path, mode: OpenMode.readOnly, uri: true);

    ResultSet resultSet = database.select('SELECT * FROM tbl1');
    debugPrint('resultSet: $resultSet');

    return NativeDatabase.opened(database, logStatements: true);
}

Using a native sqlite3 connection it was possible to read from the database, but unfortunately Drift keeps trying to write, which leads to this error:

flutter: database path: ./drift_sqlite3_readonly/lib/database/ex1
flutter: resultSet: [{one: hello!, two: 10}, {one: goodbye, two: 20}]
flutter: database path: ./drift_sqlite3_readonly/lib/database/ex1
The Flutter DevTools debugger and profiler on macOS is available at: http://127.0.0.1:9109?uri=http://127.0.0.1:49418/DntddESAIJ0=/
flutter: MD5 of database file ex1 BEFORE first read operation: 0dfb58a5244634e1b7262fd63c68c85b
[ERROR:flutter/runtime/dart_vm_initializer.cc(41)] Unhandled Exception: SqliteException(8): while executing, attempt to write a readonly database, attempt to write a readonly database (code 8)
  Causing statement: PRAGMA user_version = 1;, parameters: 
#0      throwException (package:sqlite3/src/implementation/exception.dart:75:3)
#1      DatabaseImplementation.execute (package:sqlite3/src/implementation/database.dart:244:9)
#2      DatabaseImplementation.userVersion= (package:sqlite3/src/implementation/database.dart:82:5)
#3      _SqliteVersionDelegate.setSchemaVersion (package:drift/src/sqlite3/database.dart:197:14)
#4      DelegatedDatabase._runMigrations (package:drift/src/runtime/executor/helpers/engines.dart:463:29)
<asynchronous suspension>
#5      DelegatedDatabase.ensureOpen.<anonymous closure> (package:drift/src/runtime/executor/helpers/engines.dart:426:7)
<asynchronous suspension>
#6      _AsyncCompleter.complete (dart:async/future_impl.dart:41:3)
<asynchronous suspension>
#7      DatabaseConnectionUser.doWhenOpened.<anonymous closure> (package:drift/src/runtime/api/connection_user.dart:162:55)
<asynchronous suspension>
#8      SimpleSelectStatement._mapResponse (package:drift/src/runtime/query_builder/statements/select/select.dart:90:3)
<asynchronous suspension>
#9      _HomePageState.afterBuild (package:drift_sqlite3_readonly/home.page.dart:83:28)
<asynchronous suspension>

[simolus3]

Do you have a way to set the user version to 1 in the database when creating it (e.g. by running PRAGMA user_version = 1; in the sqlite3 shell)? Drift is trying to run a migration here and will set the schema version afterwards to avoid doing it again the next time the database is opened. I think there's no way to avoid this, but I can look into APIs that would allow you to disable migrations entirely for these kinds of use cases.

[simolus3]

Added in a683408, you can disable migrations on NativeDatabases in the next drift version.

[simolus3]

You can use an existing database connection from the sqlite3 package and pass it to NativeDatabase.opened to use it with drift - that way you get to use all the opening options.

Thank you for the detailed description and repository to reproduce this though! I'll take a deeper look to see why the database is being modified from reads tomorrow.

[laugen]

I think that gives us the cause. I appreciate your help!
And yes, I think I can get PRAGMA user_version = 1; into the source file.

I would welcome the ability to turn off migration via API. Currently I use drift files to generate the database schema. Therefore I need to implement the onCreate() callback function to prevent the default call of m.createAll();.
https://github.com/laugen/drift_sqlite3_readonly/blob/main/lib/database/ex1_database.dart#L17-L25

@override
MigrationStrategy get migration {
  return MigrationStrategy(
    onCreate: (m) async {
      // By overwriting this callback function the execution of m.createAll(); is avoided and therefore no
      // DDL statements of ex1.drift are performed!
    },
  );
}