How to run initial migration that adds FTS5 virtual table?

[mattr12]

Issue

Migration does not seem to run. Even logging statements are not appearing in console.

Background

Been deleting the DB and restarting from scratch. The app (Flutter iOS/Android) is now live and I would like to avoid forcing users to reinstall from scratch. So, I followed the "Getting Started" migrations guide.

1. Ran dart drift_dev make-migrations
2. Made changes to DB (added two new tables, three triggers in a .drift file)
3. Ran build_runner dart run build_runner build to update database
4. Bumped schema number and ran dart drift_dev make-migrations again
5. Added the onUpgrade steps that are pasted below.

  @override
  MigrationStrategy get migration {
    Logger.info("LocalDb: In migration");

    return MigrationStrategy(
      beforeOpen: (details) async {
        await customStatement(
            'PRAGMA foreign_keys=1; PRAGMA trusted_schema=1;');
      },
      onUpgrade: (m, from, to) async {
        Logger.info("LocalDb: In onUpgrade $from to $to");

        await m.runMigrationSteps(
          from: from,
          to: to,
          steps: migrationSteps(
            from1To2: (m, schema) async {
              Logger.info("LocalDb: In steps $from to $to");

              await customStatement(diff1To2);
            },
          ),
        );
      },
    );
  }

// ...

const diff1To2 = """
PRAGMA trusted_schema=1;

CREATE TABLE IF NOT EXISTS notes (
    row_id          INTEGER PRIMARY KEY AUTOINCREMENT,
    note_id         VARCHAR(36) NOT NULL UNIQUE,

    title           VARCHAR(127),
    body            TEXT NOT NULL, 

    logged_at       DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at      DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,

);

CREATE VIRTUAL TABLE IF NOT EXISTS notes_search USING fts5(
    title,
    body,
    content='notes',
    content_rowid='row_id',
);

CREATE TRIGGER IF NOT EXISTS notes_ai AFTER INSERT ON notes BEGIN
  INSERT INTO notes_search(rowid, title, body) VALUES (new.row_id, new.title, new.body);
END;

CREATE TRIGGER IF NOT EXISTS notes_au AFTER UPDATE ON notes BEGIN
  DELETE FROM notes_search WHERE rowid = old.row_id;
  INSERT INTO notes_search(rowid, title, body) VALUES (new.row_id, new.title, new.body);
END;

CREATE TRIGGER IF NOT EXISTS notes_ad AFTER DELETE ON notes BEGIN
  DELETE FROM notes_search WHERE rowid = old.row_id;
END;

""";

Other information

Versions are:

drift: ^2.21.0
drift_dev: ^2.21.2

[simolus3]

Drift uses the user_version pragma to store the latest completed schema migration. When the database is opened, drift compares that pragma to the expected version (the getter in your database class) and runs a migration if the version in the database is older than schemaVersion.

Depending on whether the app was running when you wrote the migration code, there's a chance that (e.g. due to a hot restart) the database was restarted with unfinished migration code which upgraded the user_version pragma to a state where drift no doesn't recognize the need to run a migration.

That said, if the database works it's likely that the migration simply ran already, which is why it wouldn't run again?

[mattr12]

Edit: PRAGMA user_version equals 2. I can see now that the notes table is being created with its triggers. The notes_search fts5 virtual table, however, is not being created.

Oddly, I can create it if I connect to the SQLite DB and manually paste the statement, but the .drift and migration file does not seem to create it (the generated .g.dart database does not create it and only reference "notes_search" in the triggers).

Could the problem be the FTS table? My build.yaml is as follows:

targets:
  $default:
    builders:
      drift_dev:
        options:
          databases:
            my_database: lib/services/drift/local_database.dart
          store_date_time_values_as_text: true
          sql:
            dialects:
              - sqlite
            options:
              modules:
                - fts5

Moreover, the error when attempting to insert is:

ERROR: Error: SqliteException(1): while preparing statement, no such table: main.notes_search, SQL logic error...Causing statement: INSERT INTO "notes".

Finally, when I uninstall the app completely (assumingely deleting the SQLite DB with it), the only log statement that I can see in console is "In migration". The other statements with from and to do not seem to run.

Edit: For some reason, both trusted_schema and foreign_keys are also off, even though I set them to 1 beforeOpen.

[simolus3]

Edit: PRAGMA user_version equals 2. I can see now that the notes table is being created with its triggers. The notes_search fts5 virtual table, however, is not being created.

That's really odd. And this is a Flutter app using drift_flutter or sqlite3_flutter_libs, right? I wonder if you somehow ended up with a sqlite3 without fts5 support.

The other statements with from and to do not seem to run.

That's expected after re-installing the app: Drift will create a fresh instance of the database at the latest schema, so onUpgrade doesn't get called (onCreate would get called, but the default is usually what you need so there's no need to change that).

Edit: For some reason, both trusted_schema and foreign_keys are also off, even though I set them to 1 beforeOpen.

beforeOpen runs after possible migrations, but if you query for them they should be enabled once the database is open. Are you checking the pragmas with a customSelect in drift? They are connection-level settings, so if you're using another tool to inspect the database that wouldn't show them.

[mattr12]

Latest edit: Sorry to bother you, this issue was apparently caused by a trailing comma in the notes_search table.

It is using drift_flutter: ^0.2.0 for a Flutter app on iOS and Android.

I can create the fts5 table when I download the data container using Xcode or connect to the DB on the simulator using something like sqlite3 path/to/local_db.db but that is probably irrelevant here since it uses my laptop's version of SQLite3 as opposed to the one that drift_flutter comes with?

Thanks for the tip on checking pragmas via customSelect--both of them were actually enabled.

Edit: Logged out final q3 = await (localDb.customSelect('PRAGMA compile_options')).get(); and flutter: {compile_options: ENABLE_FTS5} was found. Is that sufficient to rule that FTS5 is enabled?

[simolus3]

Latest edit: Sorry to bother you, this issue was apparently caused by a trailing comma in the notes_search table.

Kind of shocking that SQLite apparently chooses to still run the other statements in that case...

It is using drift_flutter: ^0.2.0 for a Flutter app on iOS and Android.

In that case fts5 is definitely enabled and you don't need to query the compile options (although it certainly doesn't hurt and is a sure indication that the module is available).

[mattr12]

As an aside, it seems that running the create fts5 table in a customStatement works even with the trailing comma but it does not when generating the schema from the .drift file.