Moor is a powerful, reactive library for managing local SQLite databases in Flutter applications. It allows you to handle SQL queries seamlessly with Dart syntax and integrates easily into Flutter’s reactive framework. If you need a reliable database solution for tasks like storing user data, saving app settings, or managing offline data, Moor is a great choice. This tutorial will guide you through setting up Moor in Flutter, creating tables, and performing basic CRUD operations (Create, Read, Update, Delete).

What is Moor, and Why Use It?

Moor is a library that offers a modern, type-safe way to interact with SQLite databases in Flutter. Some of its notable features include:

• Dart Syntax for SQL: Moor lets you write SQL queries using Dart, making it simpler to write, understand, and maintain queries.
• Reactive Streams: You can use Moor’s Streams to automatically update your UI whenever the data in your database changes.
• Type Safety: Moor provides type-safe access to database tables, reducing potential runtime errors.

Let’s start by setting up Moor in your Flutter project and using it to manage local data.

Setting Up Moor in Flutter

Step 1: Add Dependencies

Add the required Moor dependencies to your pubspec.yaml file:

dependencies:
  flutter:
    sdk: flutter
  moor: ^4.5.0
  moor_flutter: ^4.5.0
  path_provider: ^2.0.9

dev_dependencies:
  moor_generator: ^4.5.0
  build_runner: ^2.1.7

• moor: The core Moor library. To get the dependency click here.
• moor_flutter: Flutter-specific extensions for Moor.To get the dependency click here.
• path_provider: Provides paths for saving the database files. To get the dependency click here.
• moor_generator and build_runner: Used for generating database code. To get the dependency click here and here.

Run flutter pub get to install the dependencies.

flutter pub get

Step 2: Import Moor

Import Moor in the files where you plan to use it:

import 'package:moor_flutter/moor_flutter.dart';

Creating Tables in Moor

In Moor, tables are defined using classes with fields for each column. Let’s create a simple Task table with columns for the task title and its completion status.

Example: Defining the Task Table

Create a new file named task.dart and define the table as follows:

import 'package:moor_flutter/moor_flutter.dart';

// Define the Task table
class Tasks extends Table {
  IntColumn get id => integer().autoIncrement()();  // Primary key
  TextColumn get title => text().withLength(min: 1, max: 50)();  // Task title
  BoolColumn get isCompleted => boolean().withDefault(Constant(false))();  // Completion status
}

Explanation:

• Table: Defines the schema for the database table.
• IntColumn: Represents an integer column. The autoIncrement function automatically sets this column as the primary key.
• TextColumn: Defines a text column with a length constraint.
• BoolColumn: Boolean column with a default value of false.

Step 3: Create a Database Class

Now that you’ve defined the table, create a Database class that manages the database and includes methods for accessing the Task table.

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

part 'database.g.dart';

@UseMoor(tables: [Tasks])
class AppDatabase extends _$AppDatabase {
  AppDatabase()
      : super(FlutterQueryExecutor.inDatabaseFolder(
          path: 'db.sqlite',
          logStatements: true,
        ));

  @override
  int get schemaVersion => 1;
}

Explanation:

• @UseMoor: This annotation registers the tables (like Tasks) that the database will manage.
• FlutterQueryExecutor: Specifies the path and filename for the database file.
• schemaVersion: Defines the schema version for database migration purposes.

Run the following command to generate the boilerplate code for the database class:

flutter pub run build_runner build

This generates the _AppDatabase class, which provides methods for querying and managing the database.

Performing CRUD Operations with Moor

With the database and table set up, let’s dive into basic CRUD operations.

1. Inserting Data

To insert data into the Tasks table, create a Task entry using the into() function.

import 'database.dart';

Future<void> addTask(AppDatabase db, String title) async {
  await db.into(db.tasks).insert(TasksCompanion(
    title: Value(title),
    isCompleted: Value(false),
  ));
}

Explanation:

• TasksCompanion: Used to insert values into the table. Wraps values with Value objects, indicating they’re user-defined.
• into().insert(): Inserts a new row into the specified table.

2. Reading Data

To retrieve data from the Tasks table, use the select() function to fetch all tasks or filter them based on a condition.

Stream<List<Task>> watchAllTasks(AppDatabase db) {
  return (db.select(db.tasks)).watch();
}

Explanation:

• watch(): Returns a Stream, which automatically updates the UI whenever there is a change in the data.
• select(db.tasks): Selects all rows from the Tasks table.

3. Updating Data

To update data, locate the task by ID and modify the isCompleted field.

Future<void> markTaskAsCompleted(AppDatabase db, int taskId) async {
  await (db.update(db.tasks)
        ..where((task) => task.id.equals(taskId)))
      .write(TasksCompanion(isCompleted: Value(true)));
}

Explanation:

• where(): Locates the specific row to update.
• write(): Modifies the column with the specified values.

4. Deleting Data

To delete a task by ID, use the delete() function:

Future<void> deleteTask(AppDatabase db, int taskId) async {
  await (db.delete(db.tasks)
        ..where((task) => task.id.equals(taskId)))
      .go();
}

This deletes the task with the specified ID from the Tasks table.

Displaying Data in the UI with Streams

One of the biggest advantages of using Moor in Flutter is its reactive Streams, which keep your UI updated whenever the data changes.

Example: Building a List of Tasks

Here’s a basic Flutter widget that displays a list of tasks using a StreamBuilder:

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

class TaskList extends StatelessWidget {
  final AppDatabase db;

  TaskList({required this.db});

  @override
  Widget build(BuildContext context) {
    return Scaffold(
      appBar: AppBar(title: Text('Task List')),
      body: StreamBuilder<List<Task>>(
        stream: db.watchAllTasks(db),
        builder: (context, snapshot) {
          if (!snapshot.hasData) return CircularProgressIndicator();

          final tasks = snapshot.data!;
          return ListView.builder(
            itemCount: tasks.length,
            itemBuilder: (context, index) {
              final task = tasks[index];
              return ListTile(
                title: Text(task.title),
                trailing: Icon(
                  task.isCompleted ? Icons.check_box : Icons.check_box_outline_blank,
                ),
                onTap: () => db.markTaskAsCompleted(db, task.id),
              );
            },
          );
        },
      ),
    );
  }
}

Explanation:

• StreamBuilder: Builds a widget based on the real-time data stream from watchAllTasks.
• ListView.builder: Displays a list of tasks with checkboxes based on their completion status.
• onTap(): Toggles the task’s completion status when tapped.

Advanced Querying in Moor

Moor also allows you to perform complex queries, including joins, ordering, and filtering.

Example: Filtering Tasks by Completion Status

You can filter tasks based on their completion status with a custom query:

Stream<List<Task>> watchCompletedTasks(AppDatabase db) {
  return (db.select(db.tasks)..where((task) => task.isCompleted.equals(true))).watch();
}

This query only retrieves tasks where isCompleted is true, providing a real-time view of completed tasks.

Closing the Database

It’s essential to close the database when it’s no longer needed to release resources. This is typically done in the dispose() method of the StatefulWidget:

@override
void dispose() {
  db.close();  // Close the database
  super.dispose();
}

Conclusion

Moor is a powerful, type-safe library that provides everything you need to manage a local SQLite database in Flutter. With its Dart-based SQL syntax, reactive Streams, and type-safe operations, Moor simplifies the process of handling complex data operations while keeping the code clean and easy to maintain.

This tutorial for Flutter covered the setup, creating tables, CRUD operations, and more advanced querying in Moor. Now, you’re ready to implement Moor in your Flutter project and take full advantage of its capabilities.

Happy Coding…!!!