# Magic Local Storage documentation > Magic Local Storage is SQLite for Magic apps: DB and Schema facades, migrations, seeders, factories and a sync skeleton. # Installation Add `magic_local_storage` to a Magic app, register its provider and config, and check that the `DB` facade reaches an open SQLite connection. - [Install](#install) - [Requirements](#requirements) - [What the Installer Does](#what-the-installer-does) - [Register by Hand](#register-by-hand) - [Web Setup](#web-setup) - [Verify](#verify) - [Related Documentation](#related-documentation) ## Install ```bash flutter pub add magic_local_storage dart run magic:dowel plugin:install magic_local_storage ``` The barrel, `package:magic_local_storage/magic_local_storage.dart`, is the only runtime import; everything under `src/` is private. The package also ships a Flutter-free `package:magic_local_storage/cli.dart` for the generators, which the dowel dispatcher loads for you. Magic core no longer carries the SQLite layer. A model with `useLocal => true` needs this package: without it the first `find`, `all`, `save`, `delete` or `refresh` throws a `StateError` naming `magic_local_storage`, see [Models](https://anilcancakir.com/magic/docs/local_storage/basics/models.md). ## Requirements | Requirement | Value | |:------------|:------| | Dart | `>=3.13.0` | | Flutter | `>=3.47.0` | | Magic | `^1.0.0`, installed and bootstrapped (`lib/config/app.dart` exists) | | Native | Android, iOS, macOS, Windows and Linux through `sqlite3` FFI, no setup | | Web | `web/sqlite3.wasm`, downloaded by the installer, see [Web Setup](#web-setup) | ## What the Installer Does `plugin:install` reads the package's `install.yaml` and makes two edits: it adds `DatabaseServiceProvider` to the `providers` list of `lib/config/app.dart`, and it adds `() => databaseConfig` to the `configFactories` of `lib/main.dart`. No config file is published, because `databaseConfig` is a getter the package exports. It then chains `local_storage:install`, which downloads `web/sqlite3.wasm` when the project has a `web/` folder. Both steps skip work that is already done, so running the installer twice changes nothing. ## Register by Hand To wire it yourself, register the provider and the config factory in `Magic.init`: ```dart import 'package:magic/magic.dart'; import 'package:magic_local_storage/magic_local_storage.dart'; Future main() async { await Magic.init( configFactories: [ () => databaseConfig, ], configs: [ { 'app': { 'providers': [ (app) => DatabaseServiceProvider(app), ], }, }, ], ); } ``` `DatabaseServiceProvider` is not auto-registered. Its `register()` binds the `DatabaseManager` at `db` and the `QueryModelStore` at `local_model_store`; its `boot()` opens the connection, so the first statement after `Magic.init` already has one. List the provider before any provider whose `boot()` runs a query. ## Web Setup Flutter web loads SQLite from `web/sqlite3.wasm`. The installer downloads the wasm of the `sqlite3` release your project resolved (read from `.dart_tool/package_config.json`, `sqlite3-3.9.1` when it cannot tell) into `web/` when the folder exists, and keeps a file that is already there. Add the web target later with `flutter create --platforms=web .`, then run: ```bash dart run magic:dowel local_storage:install ``` A failed download prints the `curl -L ... -o web/sqlite3.wasm` line to run by hand and does not fail the install. The `sqlite3` package only works with the specially compiled `sqlite3.wasm` from its own GitHub releases (a wasm from sql.js and the like does not load), so take the one from the release that matches the `sqlite3` version in your `pubspec.lock`, and replace it when you upgrade that package. `dart run magic:dowel local_storage:install --dry-run` stages the edits without writing. ## Verify ```dart import 'package:magic/magic.dart'; import 'package:magic_local_storage/magic_local_storage.dart'; void main() { // Magic.init(...) has run with the provider and config above. print(Magic.bound('db')); // true print(Magic.bound('local_model_store')); // true print(DB.select('SELECT sqlite_version() AS version').single['version']); } ``` If `DB` throws `Database not initialized`, `DatabaseServiceProvider` is missing from `providers` or `Magic.init` has not completed. ## Related Documentation - [Configuration](https://anilcancakir.com/magic/docs/local_storage/basics/configuration.md): the `database` config, platforms and the connection. - [Migrations](https://anilcancakir.com/magic/docs/local_storage/basics/migrations.md): create your first table. - [Models](https://anilcancakir.com/magic/docs/local_storage/basics/models.md): persisting a `useLocal` model. - [Generators](https://anilcancakir.com/magic/docs/local_storage/guides/generators.md): `make:migration`, `make:seeder`, `make:factory`. --- # Configuration Choose the database file with the `database` config, and learn how `DatabaseManager` opens SQLite on native platforms and on the web. - [Basic Usage](#basic-usage) - [Quick Reference](#quick-reference) - [Platforms](#platforms) - [DatabaseManager](#databasemanager) - [The Provider](#the-provider) - [Gotchas](#gotchas) - [Related Documentation](#related-documentation) ## Basic Usage `databaseConfig`, exported from the package barrel, is the default config. The installer registers `() => databaseConfig` in `configFactories`; to change the file name, register your own map under the same key instead: ```dart import 'package:magic/magic.dart'; import 'package:magic_local_storage/magic_local_storage.dart'; Future main() async { await Magic.init( configs: [ { 'database': { 'default': 'sqlite', 'connections': { 'sqlite': {'driver': 'sqlite', 'database': 'notes.db', 'prefix': ''}, }, }, 'app': { 'providers': [ (app) => DatabaseServiceProvider(app), ], }, }, ], ); } ``` ## Quick Reference | Key | Type | Default | Meaning | |:----|:-----|:--------|:--------| | `database.default` | `String` | `sqlite` | The connection name `DatabaseManager.init` opens. | | `database.connections..database` | `String` | `magic_app.db` | The database file name (native) or the IndexedDB store name (web). | | `database.connections..driver` | `String` | `sqlite` | Carried for Laravel's shape; the manager always opens SQLite. | | `database.connections..prefix` | `String` | empty | Carried for Laravel's shape; table names are not prefixed. | Without any `database` config the manager still opens `magic_app.db` on the `sqlite` connection, so `databaseConfig` is a convenience rather than a requirement. ## Platforms | Platform | Backend | Where the data lives | |:---------|:--------|:---------------------| | Android, iOS, macOS, Windows, Linux | `sqlite3` over FFI | A file named by `database` in the application documents directory, which survives restarts and updates. | | Web | `sqlite3.wasm` | An IndexedDB-backed file system named by `database`, so rows survive a page reload. | The platform factory is chosen at compile time, so the same code runs on every target. On the web the wasm loads from `/sqlite3.wasm`, which is `web/sqlite3.wasm` in your project; see [Installation](https://anilcancakir.com/magic/docs/local_storage/getting-started/installation.md#web-setup). ## DatabaseManager `DatabaseManager` is a singleton over the active `CommonDatabase`. Resolve it with `Magic.make('db')` or call `DatabaseManager()`; both return the same instance. ```dart import 'package:magic_local_storage/magic_local_storage.dart'; Future main() async { final DatabaseManager manager = DatabaseManager(); print(manager.isInitialized); print(await manager.getColumns('users')); // [id, name, email, created_at, updated_at] print(await manager.hasColumn('users', 'email')); manager.clearSchemaCache('users'); // omit the name to clear every table manager.dispose(); // closes the connection } ``` | Member | Meaning | |:-------|:--------| | `init()` | Opens the configured connection once and dispatches core's `DatabaseConnected` event; a second call does nothing. | | `connection` | The `CommonDatabase`; throws a `StateError` before `init()` completes. | | `getColumns(table)`, `hasColumn(table, column)` | Column names from `PRAGMA table_info`, cached per table. | | `clearSchemaCache([table])` | Forgets the cached columns; `Schema` and `Migrator` call it after every change. | | `dispose()` | Closes the connection and clears the cache. | The query builder and the model store filter every write against `getColumns`, so a stale cache after a manual `ALTER TABLE` drops the new column silently. Use `Schema.table` for column changes, or call `clearSchemaCache` yourself. ## The Provider `DatabaseServiceProvider.register()` binds `db` (the `DatabaseManager`) and `local_model_store` (a `QueryModelStore`) as singletons. `boot()` awaits `DatabaseManager.init()` and logs `Database ready [sqlite]`. A connection failure therefore surfaces during `Magic.init`, before `runApp`. The `DatabaseConnected` event and its `db_events` siblings stay in core, so a listener written against them needs no change. ## Gotchas | Mistake | Fix | |:--------|:----| | Using `:memory:` as the `database` value in tests | The native factory joins the name onto the documents directory. Open `sqlite3.openInMemory()` yourself and pass it to `DatabaseManager().setConnection`, see [Testing](https://anilcancakir.com/magic/docs/local_storage/guides/testing.md). | | Forgetting `web/sqlite3.wasm` on a web target | The first boot fails with a WebAssembly error. Run `local_storage:install` again. | | Expecting `driver` to switch databases | Only SQLite is supported; `driver` is not read. | | Calling `DatabaseManager().connection` before boot | It throws `Database not initialized`; wait for `Magic.init` or call `await DatabaseManager().init()`. | ## Related Documentation - [Installation](https://anilcancakir.com/magic/docs/local_storage/getting-started/installation.md): register the provider and the web wasm. - [Queries](https://anilcancakir.com/magic/docs/local_storage/basics/queries.md): the `DB` facade over this connection. - [Testing](https://anilcancakir.com/magic/docs/local_storage/guides/testing.md): an in-memory connection per test. --- # Models Persist a Magic `Model` to the local SQLite database with `useLocal => true`, and understand the `LocalModelStore` contract this package fulfils. - [Basic Usage](#basic-usage) - [Quick Reference](#quick-reference) - [The Store](#the-store) - [Without the Plugin](#without-the-plugin) - [Direct Queries](#direct-queries) - [Custom Stores](#custom-stores) - [Gotchas](#gotchas) - [Related Documentation](#related-documentation) ## Basic Usage The model lives in core; this package only supplies the storage behind `useLocal`. The `posts` table comes from [Migrations](https://anilcancakir.com/magic/docs/local_storage/basics/migrations.md) and must exist. ```dart import 'package:magic/magic.dart'; class Post extends Model with HasTimestamps, InteractsWithPersistence { @override String get table => 'posts'; @override String get resource => 'posts'; @override List get fillable => ['title', 'body', 'is_published']; @override Map get casts => const {'is_published': 'bool'}; @override bool get useLocal => true; @override bool get useRemote => false; String? get title => getAttribute('title') as String?; static Future find(dynamic id) => InteractsWithPersistence.findById(id, Post.new); static Future> all() => InteractsWithPersistence.allModels(Post.new); } Future main() async { final Post post = Post()..fill({'title': 'Hello', 'is_published': true}); await post.save(); final Post? found = await Post.find(post.id); final List everything = await Post.all(); await found?.delete(); print(everything.length); } ``` ## Quick Reference | `useLocal` | `useRemote` | Behaviour | |:-----------|:------------|:----------| | `true` | `false` | SQLite only; no HTTP call is made. | | `true` | `true` | Both. Reads try SQLite first and fall back to the API; a row pulled from the API is upserted locally; writes go to both. | | `false` | `true` | API only; the store is never resolved. This is the default. | `save`, `delete`, `refresh`, `findById` and `allModels` are core's `InteractsWithPersistence`; see the Eloquent pages of the core documentation for fillable lists, casts, events and the remote leg. ## The Store Core defines the contract, `LocalModelStore`, and resolves it from the container key `local_model_store` before every local leg. `DatabaseServiceProvider` binds a `QueryModelStore` there. | Method | Meaning | |:-------|:--------| | `find(table, primaryKey, id)` | The row map, or `null`. | | `all(table)` | Every row. | | `insert(table, data)` | The new row key. | | `update(table, primaryKey, id, data)` | Overwrites the row. | | `delete(table, primaryKey, id)` | Removes the row. | | `upsert(table, primaryKey, id, data)` | Updates the row when it exists, inserts otherwise; used for rows pulled from the remote side. | `QueryModelStore` runs these through `QueryBuilder`. Writes keep only the attributes the table has a column for, so a model may carry a computed or remote-only attribute without breaking the insert. An `insert` or `update` on a table that does not exist throws a `StateError` naming it, which core's `save()` reports as `false`; a `delete` there fails with SQLite's own `no such table` error instead. Booleans, dates and maps are stored as described in [Queries](https://anilcancakir.com/magic/docs/local_storage/basics/queries.md), and the model's `casts` restore them when a row is hydrated. ## Without the Plugin A `useLocal` model resolves the store before its first local statement. When nothing is bound to `local_model_store`, every leg throws a `StateError` that names `magic_local_storage`: ```text A model with useLocal enabled needs a LocalModelStore bound to "local_model_store". Add magic_local_storage and register its service provider, or set useLocal to false. ``` The check runs before the leg's own `try`, so the error is never swallowed by a local-or-remote fallback. The fix is one of two: add this package and register `DatabaseServiceProvider`, or set `useLocal` to `false`. ## Direct Queries The `ModelQuery` extension adds `query()` to every `Model`: a `QueryBuilder` over the model's table, for conditions the static helpers do not cover. ```dart import 'package:magic/magic.dart'; import 'package:magic_local_storage/magic_local_storage.dart'; class Post extends Model { @override String get table => 'posts'; @override String get resource => 'posts'; } Future main() async { final List> drafts = await Post().query().where('is_published', false).orderBy('id', 'desc').get(); print(drafts.length); } ``` The result is raw row maps, not models. Hydrate them with `InteractsWithPersistence.hydrate(row, Post.new)` when you need the typed accessors. ## Custom Stores Any class that implements `LocalModelStore` can replace the default, which is how a test double or another engine plugs in. Bind it to the same key after the provider has registered: ```dart import 'package:magic/magic.dart'; class MemoryModelStore implements LocalModelStore { final Map>> _tables = {}; Map> _rows(String table) => _tables.putIfAbsent(table, () => {}); @override Future?> find(String table, String primaryKey, dynamic id) async => _rows(table)[id]; @override Future>> all(String table) async => _rows(table).values.toList(); @override Future insert(String table, Map data) async { final int id = _rows(table).length + 1; _rows(table)[id] = {...data, 'id': id}; return id; } @override Future update(String table, String primaryKey, dynamic id, Map data) async { _rows(table)[id] = {...data}; } @override Future delete(String table, String primaryKey, dynamic id) async { _rows(table).remove(id); } @override Future upsert(String table, String primaryKey, dynamic id, Map data) async { _rows(table)[id] = {...data}; } } void main() { MagicApp.instance.singleton('local_model_store', () => MemoryModelStore()); } ``` ## Gotchas | Mistake | Fix | |:--------|:----| | A `StateError` mentioning `local_model_store` | Register `DatabaseServiceProvider`, or bind a `LocalModelStore`, or set `useLocal` to `false`. | | Saving a model before its migration ran | The store throws a `StateError` naming the missing table, so `save()` returns `false` and the model keeps `exists == false` (core's `save` catches the error). Run `Migrator().run` first. | | A typo in `fillable` or an attribute name | A key without a column is dropped silently by the store. Check the table's columns. | | Reading a boolean column through `query()` | Raw rows hold `0` and `1`; hydrate through the model to apply its casts. | | Expecting `Magic.seed` | It is gone; use `Seeding.run`, see [Seeding](https://anilcancakir.com/magic/docs/local_storage/basics/seeding.md). | | Running a `useLocal` test with no store bound | Bind the plugin provider, a `QueryModelStore` or a fake, see [Testing](https://anilcancakir.com/magic/docs/local_storage/guides/testing.md). | ## Related Documentation - [Queries](https://anilcancakir.com/magic/docs/local_storage/basics/queries.md): the `QueryBuilder` behind the store. - [Migrations](https://anilcancakir.com/magic/docs/local_storage/basics/migrations.md): the tables a model persists to. - [Seeding](https://anilcancakir.com/magic/docs/local_storage/basics/seeding.md): factories that create models. - [Sync](https://anilcancakir.com/magic/docs/local_storage/guides/sync.md): keeping the local store consistent with a server. - [Testing](https://anilcancakir.com/magic/docs/local_storage/guides/testing.md): binding a store in a test. --- # Queries Run raw SQL, build queries with the fluent `QueryBuilder` and group writes in a transaction through the `DB` facade. - [Basic Usage](#basic-usage) - [Quick Reference](#quick-reference) - [Raw Queries](#raw-queries) - [Query Builder](#query-builder) - [Transactions](#transactions) - [Schema Awareness](#schema-awareness) - [Gotchas](#gotchas) - [Related Documentation](#related-documentation) ## Basic Usage ```dart import 'package:magic_local_storage/magic_local_storage.dart'; Future main() async { final int id = await DB.table('users').insert({'name': 'Ada', 'email': 'ada@example.com'}); final List> adults = await DB.table('users') .where('age', '>=', 18) .orderBy('name') .limit(10) .get(); print('$id, ${adults.length}'); } ``` ## Quick Reference | Call | Returns | Meaning | |:-----|:--------|:--------| | `DB.table(name)` | `QueryBuilder` | Starts a query on a table. | | `DB.select(sql, [params])` | `List>` | Synchronous raw `SELECT`. | | `DB.insert(sql, [params])` | `int` | Raw insert; the last inserted row id. | | `DB.update(sql, [params])`, `DB.delete(sql, [params])` | `int` | Raw write; the number of changed rows. | | `DB.statement(sql, [params])` | `void` | Raw statement with no result: DDL, `PRAGMA`, savepoints. | | `DB.transaction(callback)` | `Future` | Runs the callback in one transaction. | | `DB.beginTransaction()`, `DB.commit()`, `DB.rollback()` | `void` | Manual control. | ## Raw Queries Raw calls are synchronous: `DatabaseManager().connection` is a local SQLite handle, so nothing waits on a network. Bind values with `?` placeholders and pass them as the second argument; never interpolate user input into the SQL string. ```dart import 'package:magic_local_storage/magic_local_storage.dart'; void main() { final List> adults = DB.select('SELECT * FROM users WHERE age > ?', [18]); final int id = DB.insert('INSERT INTO users (name, email) VALUES (?, ?)', ['Ada', 'ada@example.com']); final int changed = DB.update('UPDATE users SET name = ? WHERE id = ?', ['Grace', id]); final int removed = DB.delete('DELETE FROM users WHERE id = ?', [id]); DB.statement('CREATE INDEX idx_users_email ON users(email)'); print('${adults.length} $changed $removed'); } ``` ## Query Builder `DB.table(name)` returns a `QueryBuilder`. Chain constraints, then end with a method that runs the query. | Constraint | Meaning | |:-----------|:--------| | `select(columns)` | Restricts the columns; the default is `*`. Each entry is a raw SQL fragment, see below. | | `where(column, value)`, `where(column, operator, value)` | An `AND` condition; two arguments mean `=`. | | `whereNull(column)`, `whereNotNull(column)` | `IS NULL` and `IS NOT NULL`. | | `orderBy(column, [direction])` | `asc` by default, `asc` or `desc` in any case; repeat for a second sort key. | | `limit(count)`, `offset(count)` | Paging. | `where` accepts the operators `=`, `==`, `!=`, `<>`, `<`, `<=`, `>`, `>=`, `like`, `not like`, `glob`, `not glob`, `match`, `is` and `is not`, in any case; any other operator, `in` included, throws an `ArgumentError`, and so does an `orderBy` direction other than `asc` or `desc`. A `null` value becomes `IS NULL` with `=`, `==` or `is` (and in the two-argument form) and `IS NOT NULL` with `!=`, `<>` or `is not`; with any other operator it throws an `ArgumentError`. A plain name, letters, digits and underscores with optional dotted parts (`order`, `users.id`, `cache.items`), is quoted as an identifier wherever the builder takes a table or column: `table`, `where`, `whereNull`, `whereNotNull`, `orderBy`, `value`, `pluck`, `insert` and `update`. So a keyword column such as `order` or `group` works, and `value('order')` and `pluck('order')` read it back under its bare name. Anything else is written exactly as given, so an expression keeps working: `orderBy('name COLLATE NOCASE')`, `orderBy('RANDOM()')`, `where('lower(email)', email)`, `where('date(created_at)', '>=', day)` and `json_extract(data, '$.a')`; a hand-quoted `'"order"'` stays as written. `select([...])` entries are always raw SQL, so an expression such as `COUNT(*) AS total` works there and a keyword column has to be quoted by hand: `select(['"order"'])`. | Terminal | Returns | Meaning | |:---------|:--------|:--------| | `get()` | `Future>>` | Every matching row. | | `first()` | `Future?>` | The first row, or `null`. | | `value(column)` | `Future` | One column of the first row. | | `pluck(column)` | `Future>` | One column of every row. | | `count()`, `exists()` | `Future`, `Future` | Aggregate checks. | | `insert(map)` | `Future` | The new row id; `0` when no key matches a column. | | `insertAll(records)` | `Future` | One insert per record. | | `update(map)`, `delete()` | `Future` | The number of changed rows. | | `truncate()` | `Future` | Deletes every row of the table. | ```dart import 'package:magic_local_storage/magic_local_storage.dart'; Future main() async { final Map? user = await DB.table('users').where('email', 'ada@example.com').first(); final List names = await DB.table('users').whereNotNull('email').orderBy('name', 'desc').pluck('name'); final bool taken = await DB.table('users').where('name', 'Ada').exists(); await DB.table('users').where('id', 1).update({'name': 'Grace'}); await DB.table('users').whereNull('email').delete(); print('$user $names $taken'); } ``` Values are converted on the way in: `bool` becomes `1` or `0`, `DateTime` and `Carbon` become ISO 8601 text, and a `Map` or `List` becomes JSON text. Reads return the raw SQLite values, so a boolean column comes back as `0` or `1`; a `Model` cast turns them back, see [Models](https://anilcancakir.com/magic/docs/local_storage/basics/models.md). ## Transactions `DB.transaction` runs the callback between `BEGIN` and `COMMIT` and rolls back when it throws, rethrowing your error. ```dart import 'package:magic_local_storage/magic_local_storage.dart'; Future main() async { await DB.transaction(() async { final int userId = await DB.table('users').insert({'name': 'Ada'}); await DB.table('profiles').insert({'user_id': userId, 'bio': 'Mathematician'}); }); } ``` Do not call `DB.commit()` or `DB.rollback()` inside the callback: the block owns the transaction, and anything written afterwards would run outside it. When the callback fails after closing it, the rollback is skipped so your error is not replaced by `cannot rollback - no transaction is active`; on success the call throws a `StateError` naming the mistake, because returning normally would claim the block was atomic. To interleave other work between statements, use the manual API throughout instead of mixing the two: ```dart import 'package:magic_local_storage/magic_local_storage.dart'; Future main() async { DB.beginTransaction(); try { await DB.table('accounts').where('id', 1).update({'balance': 90}); await DB.table('transfers').insert({'account_id': 1, 'amount': 10}); DB.commit(); } catch (_) { DB.rollback(); rethrow; } } ``` `BEGIN` does not nest. A block that has to run inside someone else's transaction uses a `SAVEPOINT` through `DB.statement`, which is what `Migrator` and `SyncLedger` do. ## Schema Awareness `QueryBuilder.insert` and `update` drop every key that is not a column of the table, using the cached `PRAGMA table_info` of `DatabaseManager`. A map that carries a computed or remote-only attribute therefore never fails the write; the price is that a typo in a column name is dropped without an error. When `insert` returns `0`, none of your keys matched a column. A table with no columns does not exist, so `insert` and `update` on it throw a `StateError` naming the table, usually because its migration has not run yet. The manager caches only tables that exist, so a table created afterwards, even by a raw `DB.statement`, is seen by the next write. ## Gotchas | Mistake | Fix | |:--------|:----| | Passing input as a table, column, operator or direction | Only values are bound. A plain name is quoted but still chooses what is read, and any other text is raw SQL: whitelist names before they reach `table`, `where`, `orderBy` or `select`. | | `where('age', '>', null)` | It throws an `ArgumentError`; a `null` value only pairs with `=`, `==`, `!=`, `<>`, `is` and `is not`. | | `where('id', 'in', [1, 2])` | `in` is not supported and throws an `ArgumentError`; use a raw `DB.select` with one `?` per value. | | Writing to a table before its migration ran | It throws a `StateError` naming the table; run `Migrator().run` first. | | Looking for an `OR` condition | Constraints are always `AND`ed; write the `OR` in a raw `DB.select`. | | `insertAll` for an atomic bulk write | It is one insert per record; wrap it in `DB.transaction`. | | `DB.commit()` inside `DB.transaction` | Remove it; the block commits for you. | | Awaiting `DB.select` | It is synchronous and returns the list directly. | | Expecting booleans back from a raw read | SQLite stores `0` and `1`; cast with a model or compare to `1`. | ## Related Documentation - [Models](https://anilcancakir.com/magic/docs/local_storage/basics/models.md): `useLocal` models over the same connection. - [Migrations](https://anilcancakir.com/magic/docs/local_storage/basics/migrations.md): creating the tables you query. - [Configuration](https://anilcancakir.com/magic/docs/local_storage/basics/configuration.md): the connection behind `DB`. - [Testing](https://anilcancakir.com/magic/docs/local_storage/guides/testing.md): assertions with `DB.table`. --- # Migrations Define and change SQLite tables with `Migration` classes, the `Schema` facade and a `Blueprint`, and apply them with the `Migrator`. - [Basic Usage](#basic-usage) - [Quick Reference](#quick-reference) - [Generating a Migration](#generating-a-migration) - [Running Migrations](#running-migrations) - [The Run Is Atomic](#the-run-is-atomic) - [Synchronous up and down](#synchronous-up-and-down) - [Creating Tables](#creating-tables) - [Modifying and Dropping Tables](#modifying-and-dropping-tables) - [Checking the Schema](#checking-the-schema) - [Gotchas](#gotchas) - [Related Documentation](#related-documentation) ## Basic Usage ```dart import 'package:magic_local_storage/magic_local_storage.dart'; class CreatePostsTable extends Migration { @override String get name => '20261114120000_create_posts_table'; @override void up() { Schema.create('posts', (Blueprint table) { table.id(); table.string('title'); table.text('body').nullable(); table.boolean('is_published').defaultValue(false); table.timestamps(); }); } @override void down() { Schema.dropIfExists('posts'); } } Future main() async { // After Magic.init(...) has booted DatabaseServiceProvider. final List ran = await Migrator().run([CreatePostsTable()]); print('Ran ${ran.length} migration(s)'); } ``` ## Quick Reference | Call | Returns | Meaning | |:-----|:--------|:--------| | `Migrator().run(migrations)` | `Future>` | Applies every pending migration as one batch; returns the names it ran. | | `Migrator().rollback(migrations)` | `Future>` | Reverses the last batch, newest first. | | `Migrator().reset(migrations)` | `Future>` | Reverses every applied migration. | | `Migrator().refresh(migrations)` | `Future` | `reset` then `run`. | | `Migrator().getExecuted()` | `Future>` | Applied names in order. | | `Migrator().getPending(migrations)` | `Future>` | Names not applied yet. | The ledger is the `magic_migrations` table, with one row per migration and its batch number. `name` is the ledger key, so it must be unique and must never change once released. ## Generating a Migration ```bash dart run magic:dowel make:migration create_posts_table --create=posts dart run magic:dowel make:migration add_slug_to_posts --table=posts ``` This writes `lib/database/migrations/m__.dart` holding a PascalCase class with `up` and `down`. `--create=` selects the stub with a `Schema.create` body; without it (or with `--table`) you get a skeleton with an empty `up()` and a `down()` that throws `UnsupportedError` until you write the rollback. The command lives in this package, see [Generators](https://anilcancakir.com/magic/docs/local_storage/guides/generators.md). ## Running Migrations Magic has no migration discovery: list every migration in order. Run them after `Magic.init`, once the provider has opened the connection: ```dart import 'package:flutter/widgets.dart'; import 'package:magic/magic.dart'; import 'package:magic_local_storage/magic_local_storage.dart'; class CreateUsersTable extends Migration { @override String get name => '20261114120000_create_users_table'; @override void up() => Schema.create('users', (Blueprint table) => table.id()); @override void down() => Schema.dropIfExists('users'); } Future main() async { WidgetsFlutterBinding.ensureInitialized(); await Magic.init( configFactories: [() => databaseConfig], configs: [ { 'app': { 'providers': [(app) => DatabaseServiceProvider(app)], }, }, ], ); final List ran = await Migrator().run([ CreateUsersTable(), CreateSyncCursorsTable(), // from this package, only when you use SyncFeed ]); if (ran.isNotEmpty) { Log.info('Ran ${ran.length} migration(s)'); } } ``` `run` skips names already in the ledger, so calling it on every launch is safe. ## The Run Is Atomic Every pending migration of one `run()` is applied inside a single `SAVEPOINT`. If one throws, all of them roll back and the ledger records none, so the next launch retries from a clean schema instead of meeting half-applied work. That matters most when you migrate before `runApp`, where there is no screen to report a failure on. A savepoint rather than `BEGIN` lets it nest inside a transaction you opened yourself: ```dart import 'package:magic_local_storage/magic_local_storage.dart'; Future main() async { final List migrations = []; await Migrator().run(migrations); // the migrator's savepoint alone await DB.transaction(() => Migrator().run(migrations)); // nested in yours } ``` A migration must not call `DB.beginTransaction`, `DB.commit` or `DB.rollback`. The run is already one unit; a commit inside `up()` closes the migrator's savepoint, and `run()` throws a `StateError` naming the migration. That is the one case that cannot be undone, because the earlier statements are committed by the time it is detected. ## Synchronous up and down `up()` and `down()` return `void`. Writing `void up() async` compiles and is a silent defect: `run` cannot await a `void`, so an async body is recorded as complete at its first suspension. Inside a migration use the synchronous API: `Schema.create`, `Schema.table`, `Schema.drop`, `Schema.dropIfExists`, `Schema.rename`, `DB.statement` and `DB.select`. The introspection helpers return futures and do not belong there: `Schema.hasTable`, `Schema.hasColumn`, `Schema.getColumns`, `DatabaseManager.getColumns` and `hasColumn`. To sense a column synchronously, read the pragma: ```dart import 'package:magic_local_storage/magic_local_storage.dart'; class AddAvatarToUsers extends Migration { @override String get name => '20261115090000_add_avatar_to_users'; @override void up() { final bool present = DB.select('PRAGMA table_info(users)').any((Map row) => row['name'] == 'avatar'); if (!present) { DB.statement("ALTER TABLE users ADD COLUMN avatar TEXT NOT NULL DEFAULT ''"); } } @override void down() { throw UnsupportedError('Dropping the avatar column loses data.'); } } ``` A migration with no honest rollback throws `UnsupportedError` from `down()`; doing nothing would let `rollback` delete the ledger row of a migration that is still applied. ## Creating Tables `Schema.create(table, callback)` builds a `Blueprint` and runs `CREATE TABLE IF NOT EXISTS`. | Method | SQLite type | Notes | |:-------|:------------|:------| | `id([name])` | `INTEGER PRIMARY KEY AUTOINCREMENT` | Named `id` by default. | | `string(name)` | `TEXT` | A length argument is accepted and ignored. | | `text(name)` | `TEXT` | Long text. | | `integer(name)`, `bigInteger(name)` | `INTEGER` | Identical in SQLite. | | `boolean(name)` | `INTEGER` | `0` or `1`. | | `real(name)` | `REAL` | Floating point. | | `blob(name)` | `BLOB` | Binary data. | | `timestamps()` | `TEXT` x 2 | Nullable `created_at` and `updated_at`. | Every column is `NOT NULL` unless you chain a modifier: | Modifier | Effect | |:---------|:-------| | `nullable()` | Allows `NULL`. | | `unique()` | Adds `UNIQUE`. | | `defaultValue(value)` | Adds `DEFAULT`; a `bool` becomes `1` or `0`. | ## Modifying and Dropping Tables ```dart import 'package:magic_local_storage/magic_local_storage.dart'; void main() { Schema.table('users', (Blueprint table) { table.string('phone').nullable(); table.renameColumn('name', 'full_name'); table.dropColumn('legacy_field'); }); Schema.rename('posts', 'articles'); Schema.dropIfExists('temporary_data'); Schema.drop('old_table'); // throws when the table is absent } ``` `Schema.table` adds columns first, then renames, then drops. A column added to an existing table must be `nullable()` or carry a `defaultValue`, because SQLite refuses to add a `NOT NULL` column with no default. Renaming a column needs SQLite 3.25 and dropping one needs 3.35. SQLite cannot change a column type: add a new column, copy with `DB.statement('UPDATE users SET name_new = name')`, then drop the old one. ## Checking the Schema Outside migrations, the introspection helpers answer futures: ```dart import 'package:magic_local_storage/magic_local_storage.dart'; Future main() async { if (await Schema.hasTable('users')) { print(await Schema.getColumns('users')); // [id, name, email, created_at, updated_at] print(await Schema.hasColumn('users', 'avatar')); } } ``` ## Gotchas | Mistake | Fix | |:--------|:----| | `void up() async` | Keep `up` and `down` synchronous; use `DB.statement`, not awaited helpers. | | Awaiting `Schema.hasTable` inside `up()` | Read `sqlite_master` or `PRAGMA table_info` with `DB.select`. | | Renaming a released migration's `name` | The ledger key changes and the migration runs again. Keep the name for good. | | Adding a `NOT NULL` column with no default | Chain `nullable()` or `defaultValue(...)`. | | Calling `DB.commit()` in `up()` | Remove it; `run` throws a `StateError` naming the migration. | | Expecting migrations to be found by file name | Pass every migration to `Migrator().run`, in order. | | A `down()` that does nothing | Throw `UnsupportedError`, or `rollback` forgets a migration that is still applied. | ## Related Documentation - [Queries](https://anilcancakir.com/magic/docs/local_storage/basics/queries.md): `DB.statement` and `DB.select`. - [Models](https://anilcancakir.com/magic/docs/local_storage/basics/models.md): the model that persists to the table. - [Generators](https://anilcancakir.com/magic/docs/local_storage/guides/generators.md): `make:migration`. - [Sync](https://anilcancakir.com/magic/docs/local_storage/guides/sync.md): `CreateSyncCursorsTable`. - [Testing](https://anilcancakir.com/magic/docs/local_storage/guides/testing.md): running migrations in a test. --- # Seeding Fill the local database with sample or default rows using `Seeder` classes and model `Factory` classes, and run them with `Seeding.run`. - [Basic Usage](#basic-usage) - [Quick Reference](#quick-reference) - [Running Seeders](#running-seeders) - [Writing Seeders](#writing-seeders) - [Writing Factories](#writing-factories) - [Using Factories](#using-factories) - [Factory States](#factory-states) - [Gotchas](#gotchas) - [Related Documentation](#related-documentation) ## Basic Usage ```dart import 'package:magic/magic.dart'; import 'package:magic_local_storage/magic_local_storage.dart'; class Post extends Model with InteractsWithPersistence { @override String get table => 'posts'; @override String get resource => 'posts'; @override List get fillable => ['title']; @override bool get useLocal => true; @override bool get useRemote => false; } class PostFactory extends Factory { @override Factory newFactory() => PostFactory(); @override Post newInstance() => Post(); @override Map definition() => {'title': faker.lorem.sentence()}; } class PostSeeder extends Seeder { @override Future run() async { await PostFactory().count(10).create(); } } Future main() async { // After Magic.init(...) and Migrator().run(...). await Seeding.run([PostSeeder()]); } ``` ## Quick Reference | Call | Returns | Meaning | |:-----|:--------|:--------| | `Seeding.run(seeders)` | `Future` | Runs each seeder in order and logs the start and the end. | | `Seeder.run()` | `Future` | The body of a seeder; you implement it. | | `Seeder.call(seeders)` | `Future` | Runs other seeders from inside one. | | `Factory.count(n)` | `Factory` | A copy that builds `n` models. | | `Factory.state(map)` | `Factory` | A copy with `map` merged over the definition. | | `Factory.create()` | `Future>` | Builds and saves the models. | | `Factory.make()` | `List` | Builds the models without saving. | | `Factory.raw()` | `List>` | The attribute maps only. | ## Running Seeders `Seeding.run(List)` is the entry point, and it replaces core's removed `Magic.seed`. Call it from `main()` after the migrations, and guard it so a restart does not seed twice: ```dart import 'package:flutter/foundation.dart'; import 'package:magic_local_storage/magic_local_storage.dart'; class DatabaseSeeder extends Seeder { @override Future run() async {} } Future seedOnce() async { if (!kDebugMode) return; if (await DB.table('posts').count() == 0) { await Seeding.run([DatabaseSeeder()]); } } ``` ## Writing Seeders Generate one with `dart run magic:dowel make:seeder PostSeeder`, which writes `lib/database/seeders/post_seeder.dart` and adds the `Seeder` suffix when you leave it off. A seeder has one method, `run()`. A `DatabaseSeeder` that fans out uses `call`: ```dart import 'package:magic_local_storage/magic_local_storage.dart'; class UserSeeder extends Seeder { @override Future run() async {} } class PostSeeder extends Seeder { @override Future run() async {} } class DatabaseSeeder extends Seeder { @override Future run() async { await call([ UserSeeder(), PostSeeder(), ]); } } ``` Order matters: a seeder that needs rows from another runs after it. ## Writing Factories Generate one with `dart run magic:dowel make:factory Post`, which writes `lib/database/factories/post_factory.dart` with the `Factory` suffix added. A factory extends `Factory` and implements three methods: | Method | Returns | Meaning | |:-------|:--------|:--------| | `definition()` | `Map` | The default attributes. | | `newInstance()` | `T` | An empty model. | | `newFactory()` | `Factory` | A fresh factory of the same class; `count` and `state` copy through it. | `faker` is a getter on the base class, so `faker.person.name()`, `faker.internet.email()` and `faker.lorem.sentence()` need no import. The generated stub types the factory as `Factory` with `TODO` markers; replace those with your model. Like Laravel, a factory fills its models unguarded: `id`, timestamps and keys missing from `fillable` are kept, so a factory can build a model exactly as an API returns it. Outside a factory `fill()` still honours `fillable` and `guarded`. The same switch is available directly, and it needs a synchronous callback: ```dart import 'package:magic/magic.dart'; class User extends Model { @override String get table => 'users'; @override String get resource => 'users'; } void main() { final User user = Model.unguarded(() => User()..fill({'id': 1, 'name': 'Taylor'})); print(user.id); } ``` ## Using Factories ```dart import 'package:magic/magic.dart'; import 'package:magic_local_storage/magic_local_storage.dart'; class Post extends Model with InteractsWithPersistence { @override String get table => 'posts'; @override String get resource => 'posts'; @override List get fillable => ['title']; @override bool get useLocal => true; @override bool get useRemote => false; } class PostFactory extends Factory { @override Factory newFactory() => PostFactory(); @override Post newInstance() => Post(); @override Map definition() => {'title': faker.lorem.sentence()}; } Future main() async { final Post one = (await PostFactory().create()).first; // saved final List fifty = await PostFactory().count(50).create(); final List drafts = await PostFactory().state({'title': 'Draft'}).count(5).create(); final List unsaved = PostFactory().count(10).make(); // in memory only final List> payloads = PostFactory().count(3).raw(); // attribute maps print('${one.id} ${fifty.length} ${drafts.length} ${unsaved.length} ${payloads.length}'); } ``` `state()` and `count()` never change the factory they are called on; they return a copy built through `newFactory()`, so a shared base factory can branch without one branch leaking into the other. `make()` leaves each model unsaved with every attribute dirty, and `raw()` returns one map per model. `create()` saves through the model's `save()`, so the model needs `InteractsWithPersistence`. A refused save throws a `StateError` carrying the model's validation errors instead of returning a half-built list. ## Factory States Write reusable states as an extension on `Factory`. `state()` and `count()` return `Factory`, so an extension method chains before or after either of them: ```dart import 'package:magic/magic.dart'; import 'package:magic_local_storage/magic_local_storage.dart'; class Post extends Model { @override String get table => 'posts'; @override String get resource => 'posts'; } extension PostFactoryStates on Factory { Factory published() => state({'is_published': true}); Factory draft() => state({'is_published': false}); } Future seedPosts(Factory posts) async { await posts.published().count(3).create(); await posts.count(10).draft().create(); } ``` A method declared on `PostFactory` itself would be unreachable after the first `state()` or `count()` call, because their result is typed `Factory`. ## Gotchas | Mistake | Fix | |:--------|:----| | Calling `Magic.seed` | Removed from core; call `Seeding.run([...])`. | | Seeding on every launch | Guard with a `count()` check or run it behind `kDebugMode`. | | `Factory.create()` on a model without `InteractsWithPersistence` | The factory calls `save()` dynamically; add the mixin or use `make()`. | | Naming a state method on the factory subclass | Declare it as an extension on `Factory`; the subclass method is lost after `count()`. | | Expecting `create()` to return one model | It returns a list; take `.first` for a single model. | | Seeding before the migrations | Run `Migrator().run` first; a missing table drops every attribute. | ## Related Documentation - [Models](https://anilcancakir.com/magic/docs/local_storage/basics/models.md): the `useLocal` models a factory builds. - [Migrations](https://anilcancakir.com/magic/docs/local_storage/basics/migrations.md): the tables a seeder fills. - [Generators](https://anilcancakir.com/magic/docs/local_storage/guides/generators.md): `make:seeder` and `make:factory`. - [Testing](https://anilcancakir.com/magic/docs/local_storage/guides/testing.md): factories in tests. --- # Generators Scaffold migrations, seeders and factories with `make:migration`, `make:seeder` and `make:factory`, and wire the package into an app with `local_storage:install`. - [Quick Reference](#quick-reference) - [make:migration](#make-migration) - [make:seeder](#make-seeder) - [make:factory](#make-factory) - [local_storage:install](#local-storage-install) - [With make:model and make:resource](#with-make-model-and-make-resource) - [Gotchas](#gotchas) - [Related Documentation](#related-documentation) ## Quick Reference | Command | Writes | Notes | |:--------|:-------|:------| | `make:migration ` | `lib/database/migrations/m__.dart` | `--create=
` or `--table=
`. | | `make:seeder ` | `lib/database/seeders/_seeder.dart` | Adds the `Seeder` suffix. | | `make:factory ` | `lib/database/factories/_factory.dart` | Adds the `Factory` suffix. | | `local_storage:install` | Provider, config factory and `web/sqlite3.wasm` | Chained by `plugin:install`. | Run them with `dart run magic:dowel `. Each generator takes `--force` to overwrite an existing file. They load their stubs from this package, so `flutter pub get` must have run. ## make:migration ```bash dart run magic:dowel make:migration create_posts_table --create=posts dart run magic:dowel make:migration add_slug_to_posts --table=posts dart run magic:dowel make:migration CreateTagsTable --create=tags ``` The file name carries a 14-digit timestamp so files sort in run order. The class is the PascalCase of the name without the timestamp, and the migration's `name` is `_`, which is the ledger key. | Option | Effect | |:-------|:-------| | `--create=
`, `-c` | Uses the stub with a `Schema.create` body, `id()` and `timestamps()`; the table name is the value. | | `--table=
`, `-t` | Writes the skeleton: an empty `up()` and a `down()` that throws `UnsupportedError(' has no rollback yet.')`. The skeleton has no table placeholder, so add the `Schema.table` call yourself and replace the throw with the reverse statements. | | neither | The same skeleton. | A name with a `..` or `.` segment, an empty segment (a leading, trailing or doubled `/`), a backslash or a colon is refused with exit code `1` before anything is written, by all three generators; a nested name such as `admin/create_reports_table` stays inside the default directory. The generator does not register the migration: add it to your `Migrator().run([...])` list, see [Migrations](https://anilcancakir.com/magic/docs/local_storage/basics/migrations.md#running-migrations). ## make:seeder ```bash dart run magic:dowel make:seeder PostSeeder dart run magic:dowel make:seeder Post ``` Both write `lib/database/seeders/post_seeder.dart` with a `PostSeeder` class holding an empty `run()` and a comment showing a factory call. A nested name such as `blog/Post` keeps its directory. ## make:factory ```bash dart run magic:dowel make:factory Post dart run magic:dowel make:factory PostFactory ``` Both write `lib/database/factories/post_factory.dart`. The stub types the factory as `Factory` and throws from `newInstance()` until you import your model and replace the `TODO` lines, so the file does not compile into a working factory before you edit it. A commented extension at the bottom shows where factory states go, see [Seeding](https://anilcancakir.com/magic/docs/local_storage/basics/seeding.md#factory-states). ## local_storage:install ```bash dart run magic:dowel local_storage:install dart run magic:dowel local_storage:install --dry-run ``` It reads the package's `install.yaml`, adds `DatabaseServiceProvider` to `lib/config/app.dart` and `() => databaseConfig` to `lib/main.dart`, then downloads `web/sqlite3.wasm` when the project has a `web/` folder and none exists yet. `plugin:install magic_local_storage` chains it for you. | Flag | Effect | |:-----|:-------| | `--dry-run` | Prints the staged edits, writes nothing and skips the download. | | `--force` | Overwrites files that differ from what the installer last wrote. | | `--non-interactive` | Skips prompts and uses defaults. | | `--no-bootstrap` | Skips auto-running the bootstrap command. | It exits `1` with a hint when `flutter pub get` has not run, and when a file conflicts without `--force`. A failed wasm download is reported with the `curl` line to run by hand and does not fail the install. ## With make:model and make:resource Core's `make:model` and `make:resource` do not ship database generators of their own. They look up `make:migration`, `make:factory` and `make:seeder` in the dowel registry by name, so the plugin's commands appear once it is installed: ```bash dart run magic:dowel make:model Post -mfs dart run magic:dowel make:model Post --all ``` `-m` runs `make:migration create_posts_table --create=posts`, `-f` runs `make:factory Post` and `-s` runs `make:seeder Post`; `--all` runs all three together with the policy, repository and controller. Without this package the model is still written, and each missing generator prints a hint naming `magic_local_storage` and the `plugin:install` command, then the command carries on. ## Gotchas | Mistake | Fix | |:--------|:----| | `make:migration` fails to find a stub | Run `flutter pub get`; the stubs are resolved through `.dart_tool/package_config.json`. | | A new migration that never runs | Add it to the `Migrator().run` list; there is no discovery. | | A generated factory that throws `UnimplementedError` | Import your model and implement `newInstance()` and `newFactory()`. | | Renaming a generated migration file after release | Keep the `name` getter; the ledger keys on it, not on the file name. | | `make:model -m` printing a hint | Add `magic_local_storage` and run `plugin:install`. | ## Related Documentation - [Installation](https://anilcancakir.com/magic/docs/local_storage/getting-started/installation.md): what `plugin:install` changes. - [Migrations](https://anilcancakir.com/magic/docs/local_storage/basics/migrations.md): the migration the generator writes. - [Seeding](https://anilcancakir.com/magic/docs/local_storage/basics/seeding.md): seeders and factories. --- # Sync Keep a local table consistent with a server across many devices with `SyncFeed`, a push-then-pull loop over one resource, and `SyncLedger`, its bookmark store. - [Basic Usage](#basic-usage) - [Quick Reference](#quick-reference) - [The Wire Protocol](#the-wire-protocol) - [Writing a Feed](#writing-a-feed) - [Registering the Ledger Table](#registering-the-ledger-table) - [The Two-Clock Rule](#the-two-clock-rule) - [The Savepoint Note](#the-savepoint-note) - [What Stays App-Side](#what-stays-app-side) - [Gotchas](#gotchas) - [Related Documentation](#related-documentation) ## Basic Usage `SyncFeed.run` pushes every row this device wrote since its last push and, only when that push had no failure, pulls everything the server holds past this device's last pull. It never throws: a failure arrives in the `SyncReport`, and is logged through `Log.error`, because a sync is a convenience on top of a local store that already works without it. ```dart import 'package:magic/magic.dart'; import 'package:magic_local_storage/magic_local_storage.dart'; Future syncItems(SyncFeed feed, String teamId, String userId) async { final SyncReport report = await feed.run(scope: teamId, account: userId); if (!report.complete) { Log.warning('items sync did not finish: ${report.failure}'); } } ``` ## Quick Reference | Member | Meaning | |:-------|:--------| | `SyncFeed.run({scope, account})` | Runs both halves and answers a `SyncReport`. | | `SyncReport` | `pushed` (rows the server accepted), `adopted` (rows taken from another device), `failure` (`String?`) and `complete`. | | `feed`, `resource`, `envelopeKey` | The bookmark name, the API path and the push body key; you override them. | | `pending(...)`, `adoptRow(...)` | Read the rows owed to the server and write a row the server sent; you override them. | | `batchSize` | Rows per push request; `500` by default. | | `maxPages` | Pull pages one run walks; `100` by default. | | `SyncLedger({table})` | Reads and writes the bookmarks; `sync_cursors` by default. | | `CreateSyncCursorsTable` | The migration that creates the ledger table. | ## The Wire Protocol **Push.** `POST '$resource/sync'` with `{envelopeKey: [row.data, ...]}`, in batches of `batchSize`. The response is read the way a pull page is (`{"data": [...]}`), so a write this device lost to a conflicting write elsewhere adopts the winner at once instead of waiting for the next pull. **Pull.** `GET resource` with the query `{scope, cursor}`, `cursor` omitted when there is none yet. The response is `{"data": [...], "cursor": "", "has_more": bool}`. The cursor is taken from the body, never derived from the last row: the pair it encodes is the server's business. A run walks pages until `has_more` is false or `maxPages` is reached. Both halves go through `Http`, so the app needs a network driver (`NetworkServiceProvider`) with the API base URL. A missing leading slash on `resource` is added for you. Push runs first and the pull runs only after a push with no failure. The push makes the server's pull answer already reflect this device's own writes, so one round leaves the device consistent. A failed push still advances the mark over whatever landed before the failure, and leaves the pull cursor untouched. ## Writing a Feed A feed supplies where its bookmarks live (`feed`), which endpoint it talks to (`resource`, `envelopeKey`), and how to read and write its own rows (`pending`, `adoptRow`). ```dart import 'package:magic/magic.dart'; import 'package:magic_local_storage/magic_local_storage.dart'; class ItemsSyncFeed extends SyncFeed { @override String get feed => 'items'; @override String get resource => 'items'; @override String get envelopeKey => 'items'; @override Future> pending({ required String account, required int sinceMillis, required String scope, }) async { final List> rows = DB.select( 'SELECT * FROM items WHERE scope = ? AND updated_at_ms > ? ORDER BY updated_at_ms ASC', [scope, sinceMillis], ); return [ for (final Map row in rows) (data: row, mark: row['updated_at_ms'] as int), ]; } @override Future adoptRow({ required String account, required Map row, }) async { final int? remoteMark = Cast.intOrNull(row['updated_at_ms']); if (remoteMark == null) return false; final List> existing = DB.select( 'SELECT updated_at_ms FROM items WHERE id = ?', [row['id']], ); if (existing.isNotEmpty && (existing.single['updated_at_ms'] as int) >= remoteMark) { return false; } DB.statement( 'INSERT OR REPLACE INTO items (id, scope, updated_at_ms, payload) VALUES (?, ?, ?, ?)', [row['id'], row['scope'], remoteMark, row['payload']], ); return true; } } ``` `pending` answers rows oldest first, with `sinceMillis` as a strict lower bound: the push advances its mark to the last row of each batch, so a batch that did not end on its newest row would move the mark past rows it never sent. Each `SyncPushRow` is a record of the wire payload (`data`) and this device's own epoch millis for the row (`mark`); marks need not be unique. `adoptRow` answers `true` when the row was newer than what the device held and `false` otherwise. A row this client cannot read answers `false` rather than throwing, so one bad row does not stop the rest of a page from landing. Use `Cast.intOrNull`, `doubleOrNull` and `boolOrNull` for wire values whose numeric type is not guaranteed (`int` against `double` on the web). ## Registering the Ledger Table `CreateSyncCursorsTable` creates the `sync_cursors` table the default `SyncLedger` uses. List it explicitly with the app's own migrations: ```dart import 'package:magic_local_storage/magic_local_storage.dart'; Future main() async { await Migrator().run([ CreateSyncCursorsTable(), ]); } ``` The table has five columns (`scope`, `feed`, `account`, `pull_cursor`, `push_mark`), no `id()`, no `timestamps()` and no unique index. `SyncLedger` upserts by delete-then-insert, so an app that already keeps a table of this shape passes `SyncLedger(table: 'my_cursors')` as the `ledger` argument of its feed's `super` constructor and skips the migration. An unknown `(scope, feed)` pair reads as no cursor and a zero mark, which asks for everything. ## The Two-Clock Rule A run tracks two clocks and only one advances locally. The **push mark** is this device's own `updated_at` in epoch millis and decides which rows are worth sending next time. The **pull cursor** is the server's, opaque text handed back unread. Only the push advances the mark. A row adopted from the server carries the originating device's clock, so moving this device's mark to it would step over a local row written earlier and never sent. The cost is small: an adopted row is echoed back once on the next run, where the server's own `>=` rejects it and the mark then covers it. ## The Savepoint Note `SyncLedger.write` issues its delete-then-insert inside a `SAVEPOINT` and `RELEASE`, not `DB.transaction`, because `BEGIN` does not nest and a feed may already run inside a caller's transaction. A savepoint opened inside a transaction joins it and rolls back with it. A caller whose outer transaction fails after the release therefore loses the bookmark write, and the next run resends a tail the server already absorbs: one extra round trip, never a duplicate write. ## What Stays App-Side The skeleton is narrow on purpose. Three things are the app's: - **Scope.** An opaque string passed straight to the ledger and the wire calls; a team id or a workspace id. - **Salt.** Any value mixed into local identifiers or cache keys beyond `scope` and `account`. - **Orchestration.** When a feed runs (a timer, a reconnect, an app resume). `run` answers one call and never schedules itself. ## Gotchas | Mistake | Fix | |:--------|:----| | `pending` returning rows newest first | Order oldest first; the mark advances to the last row of each batch. | | Advancing a mark from an adopted row | Never; only the push advances it. | | Wrapping `run` in `try`/`catch` for failures | It never throws; read `SyncReport.failure`. | | Forgetting `CreateSyncCursorsTable` | The first `run` throws inside the ledger and answers a failed report. List it in `Migrator().run`. | | Running a feed with no network driver | `Http` has no driver; register `NetworkServiceProvider` first. | | Throwing from `adoptRow` on a bad row | Answer `false`; one malformed row must not stop the page. | ## Related Documentation - [Migrations](https://anilcancakir.com/magic/docs/local_storage/basics/migrations.md): registering `CreateSyncCursorsTable`. - [Queries](https://anilcancakir.com/magic/docs/local_storage/basics/queries.md): `DB.select` and `DB.statement` for `pending` and `adoptRow`. - [Testing](https://anilcancakir.com/magic/docs/local_storage/guides/testing.md): driving a feed against an in-memory database. --- # Testing Test code that persists to SQLite against an in-memory database per test, run migrations and factories in it, assert with `DB`, and fake the store when a test needs no database at all. - [In-Memory Database](#in-memory-database) - [Running Migrations](#running-migrations) - [Models and Factories](#models-and-factories) - [Database Assertions](#database-assertions) - [Faking the Store](#faking-the-store) - [Testing a Feed](#testing-a-feed) - [Resetting Static State](#resetting-static-state) - [Related Documentation](#related-documentation) ## In-Memory Database A `useLocal` model resolves a store before every local leg, so each test binds one: the plugin's `QueryModelStore` over an in-memory SQLite connection. Add the `sqlite3` package as a dev dependency to open the connection: ```bash flutter pub add --dev sqlite3 ``` ```dart import 'package:flutter_test/flutter_test.dart'; import 'package:magic/magic.dart'; import 'package:magic_local_storage/magic_local_storage.dart'; import 'package:sqlite3/sqlite3.dart'; void main() { setUp(() { MagicApp.reset(); Magic.flush(); DatabaseManager().setConnection(sqlite3.openInMemory()); MagicApp.instance.singleton('local_model_store', () => QueryModelStore()); }); tearDown(() { DatabaseManager().dispose(); }); test('the connection is open', () { expect(DatabaseManager().isInitialized, isTrue); expect(DB.select('SELECT 1 AS one').single['one'], 1); }); } ``` `DatabaseServiceProvider(MagicApp.instance).register()` binds `db` and `local_model_store` together if you prefer it to the manual line; do not call its `boot()`, which opens the file database. The name `:memory:` does not work as the `database` config value on native platforms, because the factory joins it onto the documents directory. `DatabaseManager` is a singleton that `Magic.flush()` does not touch: set a connection in `setUp` and call `dispose()` in `tearDown`, so no test sees another test's tables. ## Running Migrations A fresh connection has no tables, so run the migrations the test needs. The ledger lives in the same connection, which makes every test start from a clean slate. ```dart import 'package:flutter_test/flutter_test.dart'; import 'package:magic/magic.dart'; import 'package:magic_local_storage/magic_local_storage.dart'; import 'package:sqlite3/sqlite3.dart'; class CreateItemsTable extends Migration { @override String get name => '20261114120000_create_items_table'; @override void up() { Schema.create('items', (Blueprint table) { table.id(); table.string('name'); }); } @override void down() { Schema.dropIfExists('items'); } } void main() { setUp(() async { MagicApp.reset(); Magic.flush(); DatabaseManager().setConnection(sqlite3.openInMemory()); await Migrator().run([CreateItemsTable()]); }); tearDown(() { DatabaseManager().dispose(); }); test('the table exists', () async { expect(await Schema.hasTable('items'), isTrue); expect(await Migrator().getPending([CreateItemsTable()]), isEmpty); }); } ``` ## Models and Factories With the store bound and the tables created, a model and a factory behave as in the app: ```dart import 'package:flutter_test/flutter_test.dart'; import 'package:magic/magic.dart'; import 'package:magic_local_storage/magic_local_storage.dart'; import 'package:sqlite3/sqlite3.dart'; class Item extends Model with InteractsWithPersistence { @override String get table => 'items'; @override String get resource => 'items'; @override List get fillable => ['name']; @override bool get useLocal => true; @override bool get useRemote => false; String? get name => getAttribute('name') as String?; static Future find(dynamic id) => InteractsWithPersistence.findById(id, Item.new); } class ItemFactory extends Factory { @override Factory newFactory() => ItemFactory(); @override Item newInstance() => Item(); @override Map definition() => {'name': faker.lorem.word()}; } void main() { setUp(() { MagicApp.reset(); Magic.flush(); final db = sqlite3.openInMemory()..execute('CREATE TABLE items (id INTEGER PRIMARY KEY AUTOINCREMENT, name TEXT)'); DatabaseManager().setConnection(db); MagicApp.instance.singleton('local_model_store', () => QueryModelStore()); }); tearDown(() { DatabaseManager().dispose(); }); test('a factory creates and a model finds', () async { final Item created = (await ItemFactory().state({'name': 'Anvil'}).create()).first; final Item? found = await Item.find(created.id); expect(found?.name, 'Anvil'); }); test('a model without a bound store fails loudly', () async { MagicApp.reset(); Magic.flush(); await expectLater(Item.find(1), throwsStateError); }); } ``` A seeder run goes through `Log`, so call `Log.fake()` in that test's `setUp`. ## Database Assertions Assert against the table with `DB.table`: ```dart import 'package:flutter_test/flutter_test.dart'; import 'package:magic_local_storage/magic_local_storage.dart'; import 'package:sqlite3/sqlite3.dart'; void main() { setUp(() { DatabaseManager().setConnection(sqlite3.openInMemory()); DB.statement('CREATE TABLE items (id INTEGER PRIMARY KEY AUTOINCREMENT, name TEXT)'); }); tearDown(() { DatabaseManager().dispose(); }); test('rows reach the table', () async { await DB.table('items').insertAll([ {'name': 'Anvil'}, {'name': 'Bolt'}, ]); expect(await DB.table('items').count(), 2); expect(await DB.table('items').where('name', 'Anvil').exists(), isTrue); final Map? row = await DB.table('items').where('id', 1).first(); expect(row?['name'], 'Anvil'); }); } ``` ## Faking the Store A test for code that only needs a `useLocal` model to save, and not a SQL engine, binds a fake `LocalModelStore`. Implement the contract, see the sample under [Custom Stores](https://anilcancakir.com/magic/docs/local_storage/basics/models.md#custom-stores), and bind it: ```dart import 'package:magic/magic.dart'; void bindFake(LocalModelStore store) { MagicApp.instance.singleton('local_model_store', () => store); } ``` There is no mocking library: implement the abstract class, as with every Magic contract. ## Testing a Feed A `SyncFeed` talks to `Http` and to the ledger. Fake the network with `Http.fake`, create the `sync_cursors` table with `CreateSyncCursorsTable`, run the feed and read the bookmarks back: ```dart import 'package:magic/magic.dart'; import 'package:magic_local_storage/magic_local_storage.dart'; Future checkSync(SyncFeed feed) async { Http.fake((MagicRequest request) { return Http.response({'data': [], 'has_more': false}, 200); }); await Migrator().run([CreateSyncCursorsTable()]); final SyncReport report = await feed.run(scope: 'team-1', account: 'user-1'); final SyncBookmarks bookmarks = const SyncLedger().read(scope: 'team-1', feed: 'items'); print('${report.complete} ${bookmarks.pushMark}'); Http.unfake(); } ``` ## Resetting Static State `Magic.flush()` and `MagicApp.reset()` clear the container, not the statics this package keeps. Reset them yourself. | State | Reset | |:------|:------| | The `DatabaseManager` connection and schema cache | `DatabaseManager().dispose()` in `tearDown`; set a new connection in `setUp`. | | A faked network | `Http.unfake()` after `Http.fake`. | | The faked log | `Log.fake()` again in `setUp`; the container reset drops it. | Call `MagicApp.reset()` and `Magic.flush()` in every `setUp`, before you bind a store: the reset also clears the binding. ## Related Documentation - [Models](https://anilcancakir.com/magic/docs/local_storage/basics/models.md): the store contract and the `StateError`. - [Migrations](https://anilcancakir.com/magic/docs/local_storage/basics/migrations.md): the migrations a test runs. - [Seeding](https://anilcancakir.com/magic/docs/local_storage/basics/seeding.md): factories and seeders. - [Sync](https://anilcancakir.com/magic/docs/local_storage/guides/sync.md): the feed under test.