Structuring and Optimizing Local Databases in Flutter

Using SQLite

SQLite is ideal for relational databases with complex queries. Packages like sqflite or drift are commonly used in Flutter.

Steps to Structure and Optimize SQLite

1. Define a Schema

Design a schema that fits your app's data structure. Use normalization to avoid data duplication.

CREATE TABLE tasks (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    title TEXT NOT NULL,
    description TEXT,
    isCompleted INTEGER NOT NULL,
    dueDate INTEGER
);

2. Use sqflite for Database Management

Set up the database with migrations for schema changes. Use efficient data types like INTEGER for booleans or timestamps.

import 'package:sqflite/sqflite.dart';
import 'package:path/path.dart';

Future initializeDB() async {
  final path = await getDatabasesPath();
  return openDatabase(
    join(path, 'app_database.db'),
    version: 1,
    onCreate: (db, version) {
      return db.execute(
        'CREATE TABLE tasks(id INTEGER PRIMARY KEY, title TEXT, description TEXT, isCompleted INTEGER, dueDate INTEGER)',
      );
    },
  );
}

3. Optimize Query Performance

• Use indices on frequently queried columns.
• Avoid full-table scans by using WHERE clauses.
• Use transactions for batch operations.

4. Handle Large Data

Paginate results using LIMIT and OFFSET. Load only required data for the current UI view.

5. Implement Offline Synchronization

Use a "last sync timestamp" to fetch only changes from the server. Store sync metadata in a separate table.

Using Hive

Hive is a NoSQL database ideal for lightweight, fast storage of structured or semi-structured data.

Steps to Structure and Optimize Hive

1. Define a Model

import 'package:hive/hive.dart';

part 'task_model.g.dart';

@HiveType(typeId: 0)
class Task {
  @HiveField(0)
  final int id;

  @HiveField(1)
  final String title;

  @HiveField(2)
  final String? description;

  @HiveField(3)
  final bool isCompleted;

  @HiveField(4)
  final DateTime? dueDate;

  Task({required this.id, required this.title, this.description, required this.isCompleted, this.dueDate});
}

2. Open and Manage Boxes

void main() async {
  Hive.initFlutter();
  Hive.registerAdapter(TaskAdapter());
  final taskBox = await Hive.openBox('tasks');
  runApp(MyApp());
}

3. Optimize Data Access

• Use lazy boxes for large datasets to load only what you need.
• Avoid frequent writes by batching operations.

4. Query Data

Use Hive’s built-in APIs for data filtering and querying.

5. Encryption for Sensitive Data

var encryptedBox = await Hive.openBox('secureBox', encryptionCipher: HiveAesCipher(mySecureKey));

Best Practices for Offline Support

• Data Caching: Cache frequently accessed data to reduce database hits. Use a timestamp to invalidate stale data.
• Conflict Resolution: Handle data conflicts between offline and online modes.
• Data Compression: Compress large data blobs before saving to the database.
• Error Handling: Implement robust error handling for database read/write operations.
• Backup and Restore: Periodically back up the database file and allow users to restore data when needed.
• Test for Scalability: Ensure your database structure and queries perform well with larger datasets.

Choosing Between SQLite and Hive

Feature	SQLite	Hive
Data Type	Relational (SQL)	NoSQL (Key-Value)
Query Complexity	Advanced (Joins, Relations)	Simple Queries
Performance	Moderate for large datasets	Extremely Fast
Best Use Case	Complex schemas, relations	Lightweight and fast storage
Offline Support	Strong (sync-friendly)	Strong (e.g., caching)