local_first_sqlite_storage #

Full tests workflow

A powerful, schema-based storage adapter for the local_first ecosystem. Built on SQLite with support for typed schemas, column indexes, rich queries, and reactive updates.

Note: This is a companion package to local_first. You need to install the core package first.

Why local_first_sqlite_storage? #

Schema-based: Define typed columns with automatic table creation
Rich Queries: SQL-powered filtering, sorting, limit/offset, and null checks
Column Indexes: Fast lookups on indexed fields
Battle-tested: Built on SQLite, the world's most deployed database
Reactive: Real-time UI updates with watchQuery
Namespace Support: Multi-user isolation with useNamespace
JSON Fallback: Schema columns for performance, JSON for flexibility

Features #

✅ Typed Schemas: Define columns with LocalFieldType (text, int, double, datetime, bool)
✅ Automatic Indexes: Create indexes on schema columns for fast queries
✅ Rich Query Builder: SQL-based filtering with comparisons, IN/NOT IN, null checks
✅ Sorting: Order by any column (ascending/descending)
✅ Pagination: Limit and offset support for large datasets
✅ Reactive Queries: watchQuery with real-time updates
✅ Metadata Storage: Store app configuration and sync state
✅ Namespaces: Isolate data per user or tenant
✅ Full CRUD: Complete create, read, update, delete operations
✅ Local Drops: Remove rows without queueing a delete to sync, and remove the database files of every namespace
✅ JSON Storage: Full object stored in data column + schema columns
✅ Encryption: Optional AES-256 at-rest encryption via SQLCipher

Installation #

Add the core package and SQLite adapter to your pubspec.yaml:

dependencies:
  local_first: ^0.9.0
  local_first_sqlite_storage: ^0.5.0

Then install it with:

flutter pub get

Quick Start #

import 'package:local_first/local_first.dart';
import 'package:local_first_sqlite_storage/local_first_sqlite_storage.dart';

// 1) Define your model (keep it immutable)
class Todo {
  const Todo({
    required this.id,
    required this.title,
    required this.completed,
    required this.updatedAt,
  });

  final String id;
  final String title;
  final bool completed;
  final DateTime updatedAt;

  JsonMap toJson() => {
        'id': id,
        'title': title,
        'completed': completed,
        'updated_at': updatedAt.toUtc().toIso8601String(),
      };

  factory Todo.fromJson(JsonMap json) => Todo(
        id: json['id'] as String,
        title: json['title'] as String,
        completed: json['completed'] as bool,
        updatedAt: DateTime.parse(json['updated_at']).toUtc(),
      );

  static Todo resolveConflict(Todo local, Todo remote) =>
      local.updatedAt.isAfter(remote.updatedAt) ? local : remote;
}

// 2) Create repository with schema
final todoRepository = LocalFirstRepository<Todo>.create(
  name: 'todo',
  getId: (todo) => todo.id,
  toJson: (todo) => todo.toJson(),
  fromJson: Todo.fromJson,
  onConflict: Todo.resolveConflict,
  schema: const {
    'title': LocalFieldType.text,        // Indexed text column
    'completed': LocalFieldType.bool,    // Indexed boolean column
    'updated_at': LocalFieldType.datetime, // Indexed datetime column
  },
);

// 3) Initialize client with SQLite storage
Future<void> main() async {
  final client = LocalFirstClient(
    repositories: [todoRepository],
    localStorage: SqliteLocalFirstStorage(),
    syncStrategies: [
      // Add your sync strategy here
    ],
  );

  await client.initialize();

  // 4) Use the repository
  await todoRepository.upsert(
    Todo(
      id: '1',
      title: 'Buy milk',
      completed: false,
      updatedAt: DateTime.now().toUtc(),
    ),
    needSync: true,
  );

  // 5) Query with filters (uses indexed columns!)
  final incompleteTodos = await todoRepository.query(
    where: (fields) => fields['completed'].equals(false),
    orderBy: [OrderBy('updated_at', OrderDirection.desc)],
    limit: 10,
  );
}

Architecture #

Storage Structure #

┌────────────────────────────────────────────┐
│      SqliteLocalFirstStorage               │
│  ┌──────────────────────────────────────┐  │
│  │  Metadata Table (__config__)         │  │
│  │  key (TEXT) | value (TEXT)           │  │
│  │  - Sync sequences                    │  │
│  │  - App configuration                 │  │
│  └──────────────────────────────────────┘  │
│                                            │
│  ┌──────────────────────────────────────┐  │
│  │  Repository Tables                   │  │
│  │  ┌────────────────────────────────┐  │  │
│  │  │  todo (table)                  │  │  │
│  │  │  eventId (TEXT) PRIMARY KEY    │  │  │
│  │  │  data (TEXT) -- Full JSON      │  │  │
│  │  │  title (TEXT) INDEXED          │  │  │
│  │  │  completed (INTEGER) INDEXED   │  │  │
│  │  │  updated_at (INTEGER) INDEXED  │  │  │
│  │  └────────────────────────────────┘  │  │
│  │  ┌────────────────────────────────┐  │  │
│  │  │  user (table)                  │  │  │
│  │  │  eventId (TEXT) PRIMARY KEY    │  │  │
│  │  │  data (TEXT)                   │  │  │
│  │  │  name (TEXT) INDEXED           │  │  │
│  │  │  email (TEXT) INDEXED          │  │  │
│  │  └────────────────────────────────┘  │  │
│  └──────────────────────────────────────┘  │
└────────────────────────────────────────────┘

How Schema Works #

Full JSON Storage: Complete object stored in data column
Schema Columns: Extracted fields stored in typed columns with indexes
Query Optimization: Filters use indexed columns, then reconstruct full object from data

┌─────────────────────────────────────────┐
│  Application Code                       │
│  todoRepository.query(                  │
│    where: (f) => f['completed'] == false│
│  )                                       │
└────────────────┬────────────────────────┘
                 │
┌────────────────▼────────────────────────┐
│  LocalFirstRepository                   │
│  - Builds query from where clause       │
└────────────────┬────────────────────────┘
                 │
┌────────────────▼────────────────────────┐
│  SqliteLocalFirstStorage                │
│  - Converts to SQL:                     │
│    SELECT * FROM todo                   │
│    WHERE completed = 0  -- Uses index!  │
│  - Executes query                       │
│  - Deserializes from data column        │
└────────────────┬────────────────────────┘
                 │
┌────────────────▼────────────────────────┐
│  SQLite Database                        │
│  - Uses index for fast lookup           │
│  - Returns matching rows                │
└─────────────────────────────────────────┘

Supported Field Types #

Define schema with these types:

LocalFieldType	SQL Type	Dart Type	Use Case
`text`	TEXT	String	Names, descriptions, IDs
`int`	INTEGER	int	Counters, quantities
`double`	REAL	double	Prices, ratings
`datetime`	INTEGER	DateTime	Timestamps (stored as milliseconds)
`bool`	INTEGER	bool	Flags, completion status

Example Schema #

final schema = const {
  'title': LocalFieldType.text,        // String field
  'priority': LocalFieldType.int,      // Integer field
  'price': LocalFieldType.double,      // Double field
  'due_date': LocalFieldType.datetime, // DateTime field
  'completed': LocalFieldType.bool,    // Boolean field
};

Supported Config Types #

Metadata storage supports these types via setConfigValue/getConfigValue:

Type	Example	Use Case
`bool`	`true`	Feature flags, preferences
`int`	`42`	Counters, sync sequences
`double`	`3.14`	Ratings, calculations
`String`	`'hello'`	User names, tokens
`List<String>`	`['a', 'b']`	Tags, categories

Rich Query Builder #

Comparisons #

// Equals
await todoRepository.query(
  where: (fields) => fields['completed'].equals(true),
);

// Greater than
await todoRepository.query(
  where: (fields) => fields['priority'].greaterThan(5),
);

// Less than
await todoRepository.query(
  where: (fields) => fields['due_date'].lessThan(DateTime.now()),
);

// Greater than or equal
await todoRepository.query(
  where: (fields) => fields['price'].greaterThanOrEqual(10.0),
);

// Less than or equal
await todoRepository.query(
  where: (fields) => fields['rating'].lessThanOrEqual(3.5),
);

// Not equals
await todoRepository.query(
  where: (fields) => fields['status'].notEquals('deleted'),
);

IN and NOT IN #

// IN - matches any value in list
await todoRepository.query(
  where: (fields) => fields['category'].whereIn(['work', 'personal', 'urgent']),
);

// NOT IN - excludes values in list
await todoRepository.query(
  where: (fields) => fields['status'].notIn(['deleted', 'archived']),
);

Null Checks #

// Is null
await todoRepository.query(
  where: (fields) => fields['deleted_at'].isNull(),
);

// Is not null
await todoRepository.query(
  where: (fields) => fields['assigned_to'].isNotNull(),
);

Sorting #

// Single column ascending
await todoRepository.query(
  orderBy: [OrderBy('title', OrderDirection.asc)],
);

// Single column descending
await todoRepository.query(
  orderBy: [OrderBy('updated_at', OrderDirection.desc)],
);

// Multiple columns
await todoRepository.query(
  orderBy: [
    OrderBy('priority', OrderDirection.desc),  // First by priority
    OrderBy('due_date', OrderDirection.asc),   // Then by due date
  ],
);

Pagination #

// First 20 items
await todoRepository.query(limit: 20);

// Skip first 20, get next 20
await todoRepository.query(limit: 20, offset: 20);

// Page 3 (items 40-59)
final page = 3;
final pageSize = 20;
await todoRepository.query(
  limit: pageSize,
  offset: (page - 1) * pageSize,
);

Combined Queries #

// Complex query combining multiple features
final urgentIncompleteTodos = await todoRepository.query(
  where: (fields) => fields['completed'].equals(false)
      .and(fields['priority'].greaterThan(7))
      .and(fields['category'].whereIn(['work', 'urgent'])),
  orderBy: [
    OrderBy('priority', OrderDirection.desc),
    OrderBy('due_date', OrderDirection.asc),
  ],
  limit: 10,
);

Reactive Queries #

Watch for real-time updates:

// Watch all todos
final stream = todoRepository.watchQuery();

stream.listen((todos) {
  print('Todos updated: ${todos.length}');
});

// Watch with filters
final incompleteStream = todoRepository.watchQuery(
  where: (fields) => fields['completed'].equals(false),
);

// In Flutter
StreamBuilder<List<LocalFirstEvent<Todo>>>(
  stream: todoRepository.watchQuery(
    where: (fields) => fields['completed'].equals(false),
    orderBy: [OrderBy('updated_at', OrderDirection.desc)],
  ),
  builder: (context, snapshot) {
    if (!snapshot.hasData) return CircularProgressIndicator();

    final events = snapshot.data!;
    final todos = events.map((e) => e.data).toList();

    return ListView.builder(
      itemCount: todos.length,
      itemBuilder: (context, index) => TodoTile(todos[index]),
    );
  },
)

What a watcher promises: the newest state, not every state.

One read at a time. Writes that land while a read is in progress collapse into a single further read, so the last value emitted is the current one — two writes side by side can no longer leave the stream on the older result.
A row deleted while the watcher is reading does not break the read.
Watchers belong to the storage, not to a namespace: one registered before useNamespace keeps following whichever namespace is current and is re-emitted right after the switch. InMemoryLocalFirstStorage behaves the same way, so a test written over it proves what this adapter will do.

Comparison with Hive Storage #

Feature	SqliteLocalFirstStorage	HiveLocalFirstStorage
Performance	Fast (native SQLite)	Faster (pure Dart)
Schema	Schema-based	Schema-less
Query Capabilities	Rich SQL queries	In-memory filtering
Indexes	Column indexes	No indexes
Storage Size	Larger (with indexes)	Smaller
Setup Complexity	Define schemas	Zero config
Best For	Complex queries, filtering	Simple models, speed
Platform Support	All platforms	All platforms
Memory Usage	Very low	Low (with lazy)
Encryption	SQLCipher (AES-256)	HiveAesCipher (AES-256)
`deleteWhere`	Indexed column, or `json_extract` scan	Reads the box through
Watchers across `useNamespace`	Kept and re-emitted	Dropped (boxes re-open)

Choose SQLite when:

✅ You need complex SQL queries
✅ You want indexed filtering and sorting
✅ Your data has many fields you'll filter by
✅ You need advanced query capabilities
✅ Performance with large datasets is critical

Choose Hive when:

✅ You want the fastest performance
✅ Your models are simple and don't need complex filtering
✅ You prefer zero configuration
✅ You're building for mobile/web and want pure Dart

Namespace Support #

Isolate data per user or tenant:

final storage = SqliteLocalFirstStorage();
await storage.initialize();

// Switch to user Alice's namespace
await storage.useNamespace('user_alice');

// All operations now affect Alice's data
await todoRepository.upsert(todo);

// Switch to user Bob's namespace
await storage.useNamespace('user_bob');

// Now operating on Bob's data
final bobTodos = await todoRepository.getAll();

Use cases:

👤 Multi-user applications
🏢 Multi-tenant apps
📱 Multiple accounts support
🔐 Data isolation requirements

CRUD Operations #

Create/Update (Upsert) #

await todoRepository.upsert(
  Todo(
    id: '1',
    title: 'Buy milk',
    completed: false,
    updatedAt: DateTime.now(),
  ),
  needSync: true, // Mark for synchronization
);

Read Single Item #

final event = await todoRepository.getById('1');
if (event != null) {
  print('Todo: ${event.data.title}');
}

Read All Items #

final events = await todoRepository.getAll();
final todos = events.map((e) => e.data).toList();

Delete #

await todoRepository.delete('1', needSync: true);

Query with Filter #

// Uses indexes for fast filtering
final events = await todoRepository.query(
  where: (fields) => fields['completed'].equals(false),
  orderBy: [OrderBy('updated_at', OrderDirection.desc)],
  limit: 20,
);

Drop Rows Without Syncing #

todoRepository.delete(id, needSync: true) is a write: it is logged and sent. These two are the opposite — the row leaves the device and the server is never told, because it already knows. Both run their deletes in one transaction (joining the one in progress when there is one), notify watchQuery listeners only when something was removed, and return how many state rows went:

final storage = client.localStorage;

// A full re-sync of one repository: forget what came from the server, keep
// what this device wrote and has not sent yet.
final dropped = await storage.deleteAllSynced('todo');

// Data that stopped being ours to hold: the todos of a list the account left.
// 'list_id' is declared in the schema, so it is matched on its own column.
await storage.deleteWhere('todo', field: 'list_id', value: listId);

// null matches the rows that do not have the field at all.
await storage.deleteWhere('todo', field: 'list_id', value: null);

A row with an event still waiting to be sent survives deleteAllSynced, with its events, so an offline write is never lost to a re-sync.

deleteWhere matches a field of the repository's schema on its own indexed column. A field that is not in the schema is read out of the data column with json_extract, which scans the table — declare the fields you drop by.

Delete the Database Files #

// The app's data model moved on: open the new model under a new database name
// and take the old files off the device.
final removed = await SqliteLocalFirstStorage.deleteDatabases('app_v1.db');

A static call: it removes app_v1.db (the default namespace) and <namespace>__app_v1.db for every namespace that ever existed, with the journal files beside them, and returns how many databases were removed. Never call it with the name of a database that is open. Takes an optional directory (the platform's databases folder by default).

Best Practices #

1. Define Schemas for Fields You'll Query #

// Good: Schema includes fields you'll filter/sort by
final schema = const {
  'title': LocalFieldType.text,      // Will search by title
  'completed': LocalFieldType.bool,  // Will filter by status
  'priority': LocalFieldType.int,    // Will sort by priority
  'updated_at': LocalFieldType.datetime, // Will sort by date
};

// Less efficient: Missing fields you'll query
final schema = const {
  'title': LocalFieldType.text, // Only title indexed
  // completed, priority not in schema - will be slower to query
};

2. Use Proper Field Types #

// Good: Use datetime for timestamps
final schema = const {
  'created_at': LocalFieldType.datetime, // Stored as int, can compare dates
};

// Bad: String timestamps are hard to compare
final schema = const {
  'created_at': LocalFieldType.text, // "2025-01-26" - string comparison issues
};

3. Keep Models Immutable #

class Todo {
  const Todo({required this.id, required this.title}); // Immutable

  final String id;
  final String title;

  // Use copyWith for updates
  Todo copyWith({String? title}) => Todo(
    id: id,
    title: title ?? this.title,
  );
}

4. Handle Conflicts Properly #

static Todo resolveConflict(Todo local, Todo remote) {
  // Last-write-wins based on timestamp
  return local.updatedAt.isAfter(remote.updatedAt) ? local : remote;

  // Or merge specific fields
  // return Todo(
  //   id: local.id,
  //   title: remote.title,          // Take remote title
  //   completed: local.completed,    // Keep local status
  //   updatedAt: remote.updatedAt,   // Use latest timestamp
  // );
}

5. Use Pagination for Large Lists #

// Load first page
final page1 = await todoRepository.query(limit: 20, offset: 0);

// Load more on scroll
final page2 = await todoRepository.query(limit: 20, offset: 20);

// Or implement infinite scroll
Future<List<Todo>> loadMoreTodos(int currentCount) {
  return todoRepository.query(limit: 20, offset: currentCount);
}

6. Store Metadata for Sync State #

// Save last sync sequence
await client.setConfigValue('__last_seq__$repositoryName', sequence);

// Load on next sync
final lastSeq = await client.getConfigValue<int>('__last_seq__$repositoryName');

Encryption (At-Rest) #

Enable transparent AES-256 encryption by passing a password to the storage constructor:

final storage = SqliteLocalFirstStorage(
  password: 'my-secret-key',
);

When a password is provided, the entire database file is encrypted using SQLCipher. All data — tables, indexes, journal — is protected at rest. Without the correct password, the file is unreadable.

When no password is provided, the database operates normally without encryption (fully backward compatible).

Android ProGuard / R8 Configuration #

SQLCipher uses native libraries via JNI. When building a release APK with code shrinking enabled (minifyEnabled = true), you must add the following ProGuard rule to prevent R8 from removing required classes.

Create or update android/app/proguard-rules.pro:

-keep class net.sqlcipher.** { *; }
-dontwarn net.sqlcipher.**

Then reference it in your android/app/build.gradle (or build.gradle.kts):

Groovy (build.gradle):

android {
    buildTypes {
        release {
            minifyEnabled true
            proguardFiles getDefaultProguardFile('proguard-android-optimize.txt'), 'proguard-rules.pro'
        }
    }
}

Kotlin DSL (build.gradle.kts):

android {
    buildTypes {
        release {
            isMinifyEnabled = true
            proguardFiles(
                getDefaultProguardFile("proguard-android-optimize.txt"),
                "proguard-rules.pro"
            )
        }
    }
}

Note: This rule is only needed on Android when using encryption with code shrinking. iOS, macOS, Windows, and Linux do not require additional configuration.

Troubleshooting #

Slow Queries #

Symptoms: Queries taking a long time with large datasets

Solutions:

Ensure queried fields are in schema:

// Add fields you filter/sort by to schema
schema: const {
  'completed': LocalFieldType.bool, // Now indexed!
}

Use pagination to limit results:

query(limit: 20) // Don't load everything

Check that indexes were created (check SQLite logs)

Schema Changes Not Applying #

Symptoms: Added field to schema but queries fail

Solutions:

Schema changes require database migration or reset
For development, delete app data and reinstall

For production, implement proper migrations:

// Future: Migration support will be added

Data Not Persisting #

Symptoms: Data disappears after app restart

Solutions:

Ensure you await client.initialize():

await client.initialize(); // Don't forget await!

Check SQLite file location is correct
Verify no errors during writes

Type Mismatch Errors #

Symptoms: TypeError when querying

Solutions:

Ensure JSON field types match schema types:

// Schema says int
'priority': LocalFieldType.int,

// JSON must have int, not string
toJson() => {'priority': 5, not: '5'}

Handle null values properly:

toJson() => {
  'priority': priority ?? 0, // Provide default
}

Namespace Data Isolation Issues #

Symptoms: Data from different users mixing

Solutions:

Always call useNamespace before operations:

await storage.useNamespace('user_$userId');
await todoRepository.getAll(); // Now isolated

Verify namespace is set:

print('Current namespace: ${storage.currentNamespace}');

Testing #

Unit Tests #

Run tests from this package root:

flutter test

Integration Tests #

flutter test integration_test/

Mock Storage for Tests #

// Use in-memory storage for tests
final testClient = LocalFirstClient(
  repositories: [todoRepository],
  localStorage: InMemoryLocalFirstStorage(), // No disk I/O
  syncStrategies: [],
);

Example App #

This package includes a complete example demonstrating:

SQLite storage with schemas
Rich query capabilities
Multi-repository support
Namespace isolation
Reactive UI updates

To run the example:

cd local_first_sqlite_storage/example
flutter pub get
flutter run

Contributing #

Contributions are welcome. See CONTRIBUTING.md for guidelines.

Support the Project 💰 #

Your contributions help us enhance and maintain our plugins. Donations are used to procure devices and equipment for testing compatibility across platforms and versions.

License #

This project is available under the MIT License. See LICENSE for details.

local_first_sqlite_storage 0.5.0 local_first_sqlite_storage: ^0.5.0 copied to clipboard

Metadata

local_first_sqlite_storage #

Why local_first_sqlite_storage? #

Features #

Installation #

Quick Start #

Architecture #

Storage Structure #

How Schema Works #

Supported Field Types #

Example Schema #

Supported Config Types #

Rich Query Builder #

Comparisons #

IN and NOT IN #

Null Checks #

Sorting #

Pagination #

Combined Queries #

Reactive Queries #

Comparison with Hive Storage #

Namespace Support #

CRUD Operations #

Create/Update (Upsert) #

Read Single Item #

Read All Items #

Delete #

Query with Filter #

Drop Rows Without Syncing #

Delete the Database Files #

Best Practices #

1. Define Schemas for Fields You'll Query #

2. Use Proper Field Types #

3. Keep Models Immutable #

4. Handle Conflicts Properly #

5. Use Pagination for Large Lists #

6. Store Metadata for Sync State #

Encryption (At-Rest) #

Android ProGuard / R8 Configuration #

Troubleshooting #

Slow Queries #

Schema Changes Not Applying #

Data Not Persisting #

Type Mismatch Errors #

Namespace Data Isolation Issues #

Testing #

Unit Tests #

Integration Tests #

Mock Storage for Tests #

Example App #

Contributing #

Support the Project 💰 #

License #

← Metadata

Documentation

Publisher

Weekly Downloads

Metadata

Topics

License

Dependencies

More

local_first_sqlite_storage 0.5.0
local_first_sqlite_storage: ^0.5.0 copied to clipboard