Exploring Local Database Solutions (SQLite, Hive, ObjectBox, Realm, Drift) in Flutter

In Flutter development, local databases play a pivotal role in enabling offline data storage and efficient data management within applications. Selecting the right local database solution is crucial for performance, scalability, and ease of integration. This blog post explores several prominent local database solutions available in Flutter: SQLite, Hive, ObjectBox, Realm, and Drift, offering comprehensive insights and code examples to aid developers in making informed decisions.

Why Use Local Databases in Flutter?

• Offline Functionality: Allows the app to function even without an internet connection.
• Data Persistence: Stores data locally on the device.
• Faster Access: Local data retrieval is quicker compared to fetching data from a remote server.
• Reduced Network Usage: Decreases the need for constant network requests.
• Enhanced User Experience: Provides a seamless experience by offering immediate data availability.

1. SQLite in Flutter

SQLite is a widely-used, lightweight, disk-based database that doesn’t require a separate server process and allows accessing the database using a nonstandard variant of the SQL query language. It is well-suited for local data storage due to its simplicity and reliability.

Pros of SQLite

• Simplicity: Easy to set up and use.
• Wide Adoption: Mature and well-documented.
• SQL Support: Familiar query language.

Cons of SQLite

• Performance: Can be slower for complex queries compared to specialized solutions.
• Boilerplate: Requires writing a lot of SQL boilerplate code.

Implementation Example (SQLite with sqflite)

To use SQLite in Flutter, you can use the sqflite package. First, add the dependency to your pubspec.yaml:

dependencies:
  sqflite: ^2.0.0+4
  path_provider: ^2.0.1

Then, create a database helper class to handle database operations:

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

class DatabaseHelper {
  static const _databaseName = "MyDatabase.db";
  static const _databaseVersion = 1;

  static const table = 'my_table';

  static const columnId = '_id';
  static const columnName = 'name';
  static const columnAge = 'age';

  // Make this a singleton class.
  DatabaseHelper._privateConstructor();
  static final DatabaseHelper instance = DatabaseHelper._privateConstructor();

  // Only have a single app-wide reference to the database.
  static Database? _database;
  Future get database async {
    if (_database != null) return _database!;
    // Lazily instantiate the db the first time it is accessed.
    _database = await _initDatabase();
    return _database!;
  }

  // This opens the database (and creates it if it doesn't exist).
  _initDatabase() async {
    final documentsDirectory = await getApplicationDocumentsDirectory();
    final path = join(documentsDirectory.path, _databaseName);
    return await openDatabase(
      path,
      version: _databaseVersion,
      onCreate: _onCreate,
    );
  }

  // SQL code to create the database table.
  Future _onCreate(Database db, int version) async {
    await db.execute('''
      CREATE TABLE $table (
        $columnId INTEGER PRIMARY KEY,
        $columnName TEXT NOT NULL,
        $columnAge INTEGER NOT NULL
      )
      ''');
  }

  // Helper methods

  // Inserts a row in the database where each key in the Map is a column name
  // and the value is the column value. The return value is the id of the
  // inserted row.
  Future insert(Map row) async {
    final db = await instance.database;
    return await db.insert(table, row);
  }

  // All of the rows are returned as a list of maps, where each map is 
  // a key-value list of columns.
  Future>> queryAllRows() async {
    final db = await instance.database;
    return await db.query(table);
  }

  // All of the methods (insert, query, update, delete) can also be done using
  // raw SQL commands. This method uses a raw query to give the row count.
  Future queryRowCount() async {
    final db = await instance.database;
    return Sqflite.firstIntValue(
      await db.rawQuery('SELECT COUNT(*) FROM $table')
    );
  }

  // We are assuming here that the id column in the map is set. The other 
  // column values will be used to update the row.
  Future update(Map row) async {
    final db = await instance.database;
    int id = row[columnId];
    return await db.update(
      table,
      row,
      where: '$columnId = ?',
      whereArgs: [id]
    );
  }

  // Deletes the row specified by the id. The number of affected rows is 
  // returned. This should be 1 as long as the row exists.
  Future delete(int id) async {
    final db = await instance.database;
    return await db.delete(
      table,
      where: '$columnId = ?',
      whereArgs: [id]
    );
  }
}

Usage example in a Flutter widget:

import 'package:flutter/material.dart';
import 'database_helper.dart';

class SQLiteExample extends StatefulWidget {
  @override
  _SQLiteExampleState createState() => _SQLiteExampleState();
}

class _SQLiteExampleState extends State {
  final dbHelper = DatabaseHelper.instance;

  List> _rows = [];

  @override
  void initState() {
    super.initState();
    _queryAll();
  }

  void _insert() async {
    // Row to insert
    Map row = {
      DatabaseHelper.columnName: 'John',
      DatabaseHelper.columnAge: 30
    };
    final id = await dbHelper.insert(row);
    print('Inserted row id: $id');
    _queryAll();
  }

  void _queryAll() async {
    final allRows = await dbHelper.queryAllRows();
    print('Query all rows:');
    allRows.forEach(print);
    setState(() {
      _rows = allRows;
    });
  }

  void _update() async {
    // Row to update
    Map row = {
      DatabaseHelper.columnId: 1,
      DatabaseHelper.columnName: 'Jane',
      DatabaseHelper.columnAge: 35
    };
    final rowsAffected = await dbHelper.update(row);
    print('Updated $rowsAffected row(s)');
    _queryAll();
  }

  void _delete() async {
    // Assuming that the number of rows is the id for the last row.
    final id = _rows.length;
    final rowsDeleted = await dbHelper.delete(id);
    print('Deleted $rowsDeleted row(s): row $id');
    _queryAll();
  }

  @override
  Widget build(BuildContext context) {
    return Scaffold(
      appBar: AppBar(
        title: Text('SQLite Example'),
      ),
      body: Center(
        child: Column(
          mainAxisAlignment: MainAxisAlignment.center,
          children: <Widget>[
            ElevatedButton(
              child: Text('Insert'),
              onPressed: _insert,
            ),
            ElevatedButton(
              child: Text('Query All'),
              onPressed: _queryAll,
            ),
            ElevatedButton(
              child: Text('Update'),
              onPressed: _update,
            ),
            ElevatedButton(
              child: Text('Delete'),
              onPressed: _delete,
            ),
            Expanded(
              child: ListView.builder(
                itemCount: _rows.length,
                itemBuilder: (context, index) {
                  return Card(
                    child: Padding(
                      padding: const EdgeInsets.all(8.0),
                      child: Text(
                          '${_rows[index][DatabaseHelper.columnId]}: ${_rows[index][DatabaseHelper.columnName]} (${_rows[index][DatabaseHelper.columnAge]})'
                      ),
                    ),
                  );
                },
              ),
            ),
          ],
        ),
      ),
    );
  }
}

2. Hive in Flutter

Hive is a lightweight and fast NoSQL database solution written in Dart. It’s perfect for Flutter apps needing a simple key-value store without the complexity of SQL.

Pros of Hive

• Speed: Very fast read and write operations.
• Simple API: Easy to use with a straightforward API.
• No SQL: No SQL knowledge is required.

Cons of Hive

• No SQL: Lacks the power of SQL queries.
• Limited Features: Compared to SQL databases.

Implementation Example (Hive)

First, add the necessary dependencies in your pubspec.yaml:

dependencies:
  hive: ^2.0.4
  hive_flutter: ^1.0.0
dev_dependencies:
  build_runner: ^2.1.2
  hive_generator: ^1.1.0

Initialize Hive in your main.dart:

import 'package:flutter/material.dart';
import 'package:hive_flutter/hive_flutter.dart';

void main() async {
  WidgetsFlutterBinding.ensureInitialized();
  await Hive.initFlutter();
  // Register adapters (explained later)
  runApp(MyApp());
}

Define a model class (e.g., Person) and create a Hive adapter for it. Create a file named person.dart:

import 'package:hive/hive.dart';

part 'person.g.dart';

@HiveType(typeId: 0)
class Person {
  @HiveField(0)
  String name;

  @HiveField(1)
  int age;

  Person({required this.name, required this.age});
}

Run the build runner to generate the adapter file (person.g.dart):

flutter packages pub run build_runner build

Register the adapter in main.dart:

import 'package:flutter/material.dart';
import 'package:hive_flutter/hive_flutter.dart';
import 'package:local_db_solutions/person.dart';

void main() async {
  WidgetsFlutterBinding.ensureInitialized();
  await Hive.initFlutter();
  Hive.registerAdapter(PersonAdapter()); // Register the generated adapter
  runApp(MyApp());
}

class MyApp extends StatelessWidget {
  @override
  Widget build(BuildContext context) {
    return MaterialApp(
      home: HiveExample(),
    );
  }
}

class HiveExample extends StatefulWidget {
  @override
  _HiveExampleState createState() => _HiveExampleState();
}

class _HiveExampleState extends State {
  late Box<Person> _personBox;

  @override
  void initState() {
    super.initState();
    _openBox();
  }

  Future<void> _openBox() async {
    _personBox = await Hive.openBox('peopleBox');
    setState(() {}); // Trigger rebuild to reflect opened box
  }

  @override
  void dispose() {
    Hive.close();
    super.dispose();
  }

  void _addPerson() {
    final person = Person(name: 'Alice', age: 25);
    _personBox.add(person);
    print('Added: ${person.name}');
  }

  void _getPeople() {
    for (int i = 0; i < _personBox.length; i++) {
      final person = _personBox.getAt(i);
      print('Person at $i: ${person?.name}, ${person?.age}');
    }
  }

  @override
  Widget build(BuildContext context) {
    return Scaffold(
      appBar: AppBar(title: Text('Hive Example')),
      body: Center(
        child: Column(
          mainAxisAlignment: MainAxisAlignment.center,
          children: <Widget>[
            ElevatedButton(
              child: Text('Add Person'),
              onPressed: _addPerson,
            ),
            ElevatedButton(
              child: Text('Get People'),
              onPressed: _getPeople,
            ),
            // More UI widgets for display...
          ],
        ),
      ),
    );
  }
}

3. ObjectBox in Flutter

ObjectBox is a high-performance, embedded NoSQL database designed for simplicity and speed, especially suitable for mobile applications. It offers a unique combination of high performance, zero-configuration setup, and an object-oriented API.

Pros of ObjectBox

• High Performance: Designed for speed.
• Zero Configuration: No SQL, no schema migrations by default.
• Object-Oriented: Works directly with objects.

Cons of ObjectBox

• Learning Curve: Unique API may require some learning.
• Limited SQL Support: Primarily object-oriented.

Implementation Example (ObjectBox)

Step 1: Add Dependencies

Add the ObjectBox dependencies to your pubspec.yaml file:

dependencies:
  objectbox: any

dev_dependencies:
  objectbox_generator: any
  build_runner: any

Step 2: Define Your Data Model

Create a class for the data you want to store. Annotate it with @Entity(). You’ll also need to mark a property with @Id().

import 'package:objectbox/objectbox.dart';

@Entity()
class Task {
  @Id()
  int id = 0;

  String description;
  bool isDone;

  Task({required this.description, this.isDone = false});
}

Step 3: Generate ObjectBox Code

Run the build runner to generate the ObjectBox model code:

flutter pub run build_runner build

Step 4: Initialize ObjectBox

Initialize ObjectBox in your application. This typically involves creating a Store.

import 'package:objectbox/objectbox.dart';
import 'objectbox.g.dart'; // created by `flutter pub run build_runner build`

class ObjectBox {
  /// The Store encapsulates the database and manages transactions
  late final Store store;

  static late final ObjectBox _instance;

  static Future getInstance() async {
    // Only create ObjectBox once
    _instance = ObjectBox._create();
    return _instance;
  }
  /// Delete ObjectBox to ensure tests are reproducible.
  static Future deleteInstance() async {
    if (_instance._store.isClosed()) {
      throw AssertionError("ObjectBox already closed.");
    }
    if (!kReleaseMode) {
      _instance._store.close();
      await _instance._store.directory.delete(recursive: true);
    }
  }

  ObjectBox._create() {
    // Future initialization is surprisingly tricky:
    // https://dart.dev/null-safety/understanding-null-safety#initializing-late-variables
    // The actual construction of the store has to happen in sync to ensure its resources get released eventually.
      store = Store(getObjectBoxModel());
  }
}

final objectbox = ObjectBox._instance;

And use objectbox.g.dart model import into build method :

FutureBuilder(
      future: ObjectBox.getInstance(),
      builder: (BuildContext context, AsyncSnapshot snapshot) {
        if (snapshot.hasData) {
          var taskBox = objectbox.store.box();

          final tasksStream = taskBox
              .query()
              .watch(triggerImmediately: true);

          return  StreamBuilder>(
            stream: tasksStream,
            builder: (context, AsyncSnapshot> snapshot) {
              List items;

              if (snapshot.hasData && snapshot.data!.isNotEmpty) {
                items = snapshot.data!;

              } else {
                items = const [];
              }
               // Show Your UI
}
          );
          });
          
          }
       )

To avoid init store into FutureBuilder import from example :

final objectbox = ObjectBox._instance;

To run build into objectbox you need import dependencie for Platform:

import 'dart:io' show Platform;
import 'package:flutter/foundation.dart';

Step 5: Use ObjectBox to Store and Retrieve Data

Create, read, update, and delete tasks as needed.

 //Create the model task instance
 final task = Task(description: 'Sample Text');
 //Access the ObjectBOx with store and then task Model and write in Database;
 objectbox.store.box().put(task);

ObjectBox has a great approach for managing your Local Storage using model oriented storage , keep in mind its complexity but faster storage data, and one more important key in tests: “ensure tests are reproducible”

4. Realm in Flutter

Realm is a mobile database solution designed for ease of use and high performance. It’s an object database that maps data directly to objects, allowing for a more intuitive approach than traditional SQL databases.

Pros of Realm

• Ease of Use:
  Provides an object-oriented API for interacting with the database.
• High Performance:
  Optimized for fast read and write operations.
• Real-time Updates:
  Supports real-time data synchronization and updates.

Cons of Realm

• Licensing:
  May require a commercial license for certain usage scenarios.
• Lock-in:
  Realm’s data format is proprietary, leading to vendor lock-in.

Implementation Example (Realm)

To use Realm in a Flutter project, follow these steps:

Step 1: Add Dependencies

Include Realm dependencies in the pubspec.yaml file:

dependencies:
  realm: ^0.4.0

Run flutter pub get to install the dependencies.

Step 2: Define a Realm Object Model

Create a class that represents your data model. This class should extend RealmObject and include necessary fields:

import 'package:realm/realm.dart';

part 'task.g.dart';

@RealmModel()
class _Task {
  @PrimaryKey()
  late ObjectId id;
  late String description;
  bool isComplete = false;
}

The part 'task.g.dart'; line is essential for generating the necessary code for Realm to manage your objects. Run the code generation command in the next step.

Step 3: Generate the Realm Model

Use the Realm code generator to create the implementation for your Realm object. Run the following command in your terminal:

flutter pub run realm generate

This command generates the task.g.dart file, which contains the implementation of your Task class.

Step 4: Configure and Open a Realm

Configure the Realm and open it in your Flutter app:

import 'package:realm/realm.dart';
import 'task.dart';

class RealmService {
  late Realm realm;

  RealmService() {
    var config = Configuration.local([Task.schema]);
    realm = Realm(config);
  }

  void addTask(String description) {
    realm.write(() {
      realm.add(Task(ObjectId(), description));
    });
  }

  List getTasks() {
    return realm.all().toList();
  }

  void toggleCompletion(Task task) {
    realm.write(() {
      task.isComplete = !task.isComplete;
    });
  }

  void deleteTask(Task task) {
    realm.write(() {
      realm.delete(task);
    });
  }
}

In this example:

• The RealmService class handles Realm initialization and operations.
• The constructor sets up the Realm configuration with the schema for the Task object.
• Methods are provided for adding, retrieving, toggling completion status, and deleting tasks.

Step 5: Use Realm in Your Flutter UI

Now, use the RealmService to interact with the Realm database in your Flutter UI:

import 'package:flutter/material.dart';
import 'package:realm/realm.dart';
import 'realm_service.dart';
import 'task.dart';

class RealmExample extends StatefulWidget {
  @override
  _RealmExampleState createState() => _RealmExampleState();
}

class _RealmExampleState extends State {
  final realmService = RealmService();
  late List<Task> tasks;

  @override
  void initState() {
    super.initState();
    tasks = realmService.getTasks();
  }

  @override
  Widget build(BuildContext context) {
    return Scaffold(
      appBar: AppBar(
        title: Text('Realm Example'),
      ),
      body: ListView.builder(
        itemCount: tasks.length,
        itemBuilder: (context, index) {
          final task = tasks[index];
          return ListTile(
            title: Text(task.description),
            leading: Checkbox(
              value: task.isComplete,
              onChanged: (value) {
                setState(() {
                  realmService.toggleCompletion(task);
                  tasks = realmService.getTasks();
                });
              },
            ),
            trailing: IconButton(
              icon: Icon(Icons.delete),
              onPressed: () {
                setState(() {
                  realmService.deleteTask(task);
                  tasks = realmService.getTasks();
                });
              },
            ),
          );
        },
      ),
      floatingActionButton: FloatingActionButton(
        child: Icon(Icons.add),
        onPressed: () {
          showDialog(
            context: context,
            builder: (BuildContext context) {
              String newTaskDescription = '';
              return AlertDialog(
                title: Text('Add New Task'),
                content: TextField(
                  autofocus: true,
                  decoration: InputDecoration(hintText: 'Task description'),
                  onChanged: (value) {
                    newTaskDescription = value;
                  },
                ),
                actions: [
                  TextButton(
                    child: Text('Cancel'),
                    onPressed: () {
                      Navigator.of(context).pop();
                    },
                  ),
                  TextButton(
                    child: Text('Add'),
                    onPressed: () {
                      setState(() {
                        realmService.addTask(newTaskDescription);
                        tasks = realmService.getTasks();
                        Navigator.of(context).pop();
                      });
                    },
                  ),
                ],
              );
            },
          );
        },
      ),
    );
  }

  @override
  void dispose() {
    realmService.realm.close();
    super.dispose();
  }
}

5. Drift in Flutter

Drift (formerly Moor) is a reactive, type-safe persistence library for Flutter and Dart. It allows you to write SQL queries directly in Dart code and provides compile-time checks for type safety and SQL syntax, making it a robust choice for managing local databases.

Pros of Drift

• Type Safety: Compile-time checks for type correctness.
• SQL-like DSL: Write SQL queries in Dart.
• Reactive: Built-in support for streams and reactive queries.

Cons of Drift

• Complexity: Steeper learning curve compared to simpler solutions.
• Code Generation: Relies on code generation, adding build steps.

Implementation Example (Drift)

Add the required dependencies in your pubspec.yaml:

dependencies:
  drift: ^2.0.0
  sqlite3_flutter_libs: ^0.5.0

dev_dependencies:
  drift_dev: ^2.0.0
  build_runner: ^2.0.0

Define your database schema in a .dart file (e.g., database.dart):

import 'package:drift/drift.dart';
import 'package:drift/native.dart';
import 'dart:io';
import 'package:path_provider/path_provider.dart';
import 'package:path/path.dart' as p;

part 'database.g.dart';

@DataClassName('Task')
class Tasks extends Table {
  IntColumn get id => integer().autoIncrement()();
  TextColumn get description => text().named('content')();
  BoolColumn get isDone => boolean().withDefault(const Constant(false))();
}

LazyDatabase _openConnection() {
  // LazyDatabase truly creates the database the first time it is accessed.
  return LazyDatabase(() async {
    // Put the database file, called db.sqlite into the documents folder of
    // the operating system.
    final dbFolder = await getApplicationDocumentsDirectory();
    final file = File(p.join(dbFolder.path, 'db.sqlite'));
    return NativeDatabase(file);
  });
}

@DriftDatabase(tables: [Tasks])
class AppDatabase extends _$AppDatabase {
  AppDatabase() : super(_openConnection());

  @override
  int get schemaVersion => 1;

  Future<List> getAllTasks() async {
    return await select(tasks).get();
  }

  Future<int> insertTask(TasksCompanion task) async {
    return await into(tasks).insert(task);
  }

  Future updateTask(Task task) async {
    return await update(tasks).replace(task);
  }

  Future deleteTask(int id) async {
    return (delete(tasks)..where((t) => t.id.equals(id))).go();
  }
}

Generate the necessary drift code by running:

flutter pub run build_runner build

Use the generated database class to perform database operations in your Flutter application.

import 'package:flutter/material.dart';
import 'package:local_db_solutions/database.dart';

class DriftExample extends StatefulWidget {
  @override
  _DriftExampleState createState() => _DriftExampleState();
}

class _DriftExampleState extends State<DriftExample> {
  late AppDatabase database;
  late Future<List<Task>> tasksFuture;

  @override
  void initState() {
    super.initState();
    database = AppDatabase();
    tasksFuture = database.getAllTasks();
  }

  void _addTask() async {
    await database.insertTask(
      TasksCompanion(
        description: Value('New Task'),
      ),
    );
    setState(() {
      tasksFuture = database.getAllTasks();
    });
  }

  void _deleteTask(int id) async {
    await database.deleteTask(id);
    setState(() {
      tasksFuture = database.getAllTasks();
    });
  }

  @override
  Widget build(BuildContext context) {
    return Scaffold(
      appBar: AppBar(title: Text('Drift Example')),
      body: FutureBuilder<List<Task>>(
        future: tasksFuture,
        builder: (context, snapshot) {
          if (snapshot.hasData) {
            final tasks = snapshot.data!;
            return ListView.builder(
              itemCount: tasks.length,
              itemBuilder: (context, index) {
                final task = tasks[index];
                return ListTile(
                  title: Text(task.description),
                  trailing: IconButton(
                    icon: Icon(Icons.delete),
                    onPressed: () => _deleteTask(task.id),
                  ),
                );
              },
            );
          } else if (snapshot.hasError) {
            return Center(child: Text('Error: ${snapshot.error}'));
          } else {
            return Center(child: CircularProgressIndicator());
          }
        },
      ),
      floatingActionButton: FloatingActionButton(
        onPressed: _addTask,
        child: Icon(Icons.add),
      ),
    );
  }

  @override
  void dispose() {
    database.close();
    super.dispose();
  }
}

Summary

Selecting the appropriate local database solution depends on your application’s specific requirements:

• SQLite: Good for standard SQL databases with straightforward requirements.
• Hive: Best for simple key-value storage with high-speed operations.
• ObjectBox: Ideal for mobile apps needing high performance with minimal configuration.
• Realm: Suited for applications that require ease of use, real-time updates, and strong object management.
• Drift: Excellent for type-safe, reactive applications requiring compile-time checks and robust SQL support.

Each of these local database solutions offers unique advantages and trade-offs. Evaluating your project’s needs and priorities will lead you to the most suitable choice for effective data management and improved user experience.