Flutter local SQLite

Flutter local SQLite still needs its own decision, but the recommended migration path is now the same reviewed SQL artifact model used elsewhere in the repo.

The three valid cases

1. Disposable local cache.
2. Important offline-first local data.
3. Shared backend database behind the app.

Disposable local cache

If the local database is just a cache, reset is usually better than maintaining migration artifacts forever.

Typical reset strategy:

1. delete the local file or clear the tables
2. recreate the schema from generated runtime metadata
3. reopen the typed runtime client

Important offline-first local data

If the SQLite file contains user data that must survive upgrades, keep the schema changes as reviewed SQL migrations in your repo and bundle those artifacts into Flutter assets.

The startup shape is:

import 'package:comon_orm_sqlite_flutter/comon_orm_sqlite_flutter.dart';

import 'generated/comon_orm_client.dart';

final adapter = await SqliteDatabaseAdapter.openFromGeneratedSchema(
  schema: ComonOrm.runtimeSchema,
  databasePath: 'app.db',
);

await ComonOrm.migrate(
  adapter: adapter,
  migrations: migrationReaderFromAssets('assets/migrations'),
);

final db = ComonOrm(adapter: adapter);

Keep the order explicit:

1. open the adapter from generated metadata
2. apply bundled reviewed migrations
3. create the typed runtime client

Migration artifact layout

Bundle the same artifact layout produced by migrate dev:

assets/migrations/
  manifest.json
  20260315_init/
    before.prisma
    after.prisma
    migration.sql
    metadata.txt

At minimum, before.prisma, after.prisma, and migration.sql must exist for each migration. metadata.txt is strongly recommended because it locks in the exact provider/checksum values recorded in runtime history.

Creating and bundling migrations

Create the next reviewed migration with the normal sqlite CLI flow:

dart run packages/comon_orm/bin/comon_orm.dart migrate dev --name add_user_names

Then copy or sync the resulting migrations/ artifact tree into your Flutter app's assets/migrations/ directory and declare it in pubspec.yaml.

flutter:
  assets:
    - assets/migrations/manifest.json
    - assets/migrations/

manifest.json is updated automatically when the file-backed migration services write a new draft, so Flutter asset discovery stays aligned with the reviewed artifacts.

Practical rules

• prefer reset over migrations when local data is disposable
• keep runtime open and migration application separate
• do not edit old migration directories after they were applied on real devices
• if a migration is risky or rebuild-heavy, review the generated SQL and warnings before bundling it into the app
• keep Flutter using reviewed SQL artifacts rather than maintaining a second Dart-only migration system

Shared backend database behind the app

If the app talks to a shared PostgreSQL or shared SQLite database, the app is not the migration host. Use the reviewed CLI flow outside the app.