Let’s create a simple todo CRUD application with sqlfite


sqflite on pub.dev↗

Checkout Starter code or final code

Project setup

You can create a new project or just clone the repo.

I am using freezed to generate my classes. You can explore my video on freezed or use normal class and create fromJson/toMap method.

pubspec.yaml

View on GitHub
 9dependencies:
10  flutter:
11    sdk: flutter
12  freezed_annotation: ^3.1.0
13  json_annotation: ^4.12.0
14  uuid: ^4.6.0
15
16  sqflite: ^2.4.2+1 # for android, ios
17  sqflite_common_ffi: ^2.4.0  # for linux run,  check  doc for more
18
19dev_dependencies:
20  flutter_test:
21    sdk: flutter
22  flutter_lints: ^6.0.0
23  build_runner: ^2.15.1
24  freezed: ^3.2.5
25  json_serializable: ^6.14.1

task.dart

View on GitHub
 1import 'package:freezed_annotation/freezed_annotation.dart';
 2import 'package:uuid/uuid.dart';
 3
 4part 'task.freezed.dart';
 5part 'task.g.dart';
 6
 7@freezed
 8abstract class Task with _$Task {
 9  const Task._();
10
11  const factory Task({
12    required String id,
13    required String title,
14    @Default("") String description,
15    @DateDBConverter() required DateTime createdAt,
16  }) = _Task;
17
18  static Task get empty => Task(
19    id: Uuid().v4().toString(),
20    title: "",
21    createdAt: DateTime.now(),
22  );
23
24  factory Task.fromJson(Map<String, Object?> json) => _$TaskFromJson(json);
25}
26
27class DateDBConverter extends JsonConverter<DateTime, String> {
28  const DateDBConverter();
29  @override
30  DateTime fromJson(String json) => DateTime.parse(json);
31
32  @override
33  String toJson(DateTime object) => object.toString();
34}

Now run build_runner dart run build_runner build -d to generate freezed files.

Task repository

We have initialize databse and CRUD responsibility.

We have tasks table which contains id, title,description and createdAt all as text. More on supported datatype for sqlfite.

 1import '../models/task.dart';
 2
 3import 'package:sqflite/sqflite.dart';
 4
 5class TaskRepo {
 6  TaskRepo._(this._db);
 7  final Database _db; // comes from sqflite
 8
 9  static Future<TaskRepo> create() async {
10    final db = await openDatabase(
11      'app.db', //databse name
12      version: 1, //version
13      onCreate: (db, version) async {
14        await db.execute('''
15                      CREATE TABLE tasks(
16                          id TEXT PRIMARY KEY,
17                          title TEXT,
18                          description TEXT,
19                          createdAt TEXT
20                          )
21                      ''');
22      },
23    );
24
25    return TaskRepo._(db);
26  }
27 }

Let’s have our methods

ONE thing to keep in mind is that .fromJson and .toJson should match the key with sqlfite tables we just create.

task_repo.dart

View on GitHub
39
40  Future<Task?> addTask(Task task) async {
41    final result = await _db.insert(
42      'tasks',
43      task.toJson(),
44      conflictAlgorithm: ConflictAlgorithm.replace,
45    );
46
47    return result == 0 ? null : task;
48  }
49
50  Future<List<Task>> getTasks() async {
51    final rows = await _db.query('tasks');
52    return rows.map((row) => Task.fromJson(row)).toList();
53  }

Update sqlfite

You can increase version and alter table as well. Lets’ add progress status and updatedAt

 1@JsonEnum()
 2enum TaskStatus { pending, inProgress, completed }
 3
 4
 5@freezed
 6abstract class Task with _$Task {
 7  const Task._();
 8
 9  const factory Task({
10    required String id,
11    required String title,
12    @Default("") String description,
13    @Default(TaskStatus.pending) TaskStatus status,
14    @DateDBConverter() required DateTime createdAt,
15    @DateDBConverter() required DateTime updatedAt,
16  }) = _Task;
17
18  static Task get empty => Task(
19    id: Uuid().v4().toString(),
20    title: "",
21    createdAt: DateTime.now(),
22    updatedAt: DateTime.now(),
23  );
24
25  factory Task.fromJson(Map<String, Object?> json) => _$TaskFromJson(json);
26}

Update the db(table)

We have updated the version

task_repo.dart

View on GitHub
10  static Future<TaskRepo> create() async {
11    final db = await openDatabase(
12      "tasks_x.db",
13      version: 2,
14      onCreate: (db, version) async {
15        await db.execute('''
16                      CREATE TABLE tasks(
17                          id TEXT PRIMARY KEY,
18                          title TEXT,
19                          description TEXT,
20                          createdAt TEXT
21                          )
22                      ''');
23      },
24      onUpgrade: (db, prev, version) async {
25        if (prev < 2) {
26          await db.execute(
27            "ALTER TABLE tasks ADD COLUMN status TEXT NOT NULL DEFAULT 'pending'",
28          );
29
30          await db.execute(
31            "ALTER TABLE tasks ADD COLUMN updatedAt TEXT DEFAULT '1970-01-01T00:00:00.000Z'",
32          );
33        }
34      },
35    );
36
37    return TaskRepo._(db);
38  }

Task status toggle function

task_repo.dart

View on GitHub
55  Future<int> toggleTaskStatus(String taskId, TaskStatus status) async {
56    final result = await _db.update(
57      'tasks',
58      {'status': status.name, "updatedAt": DateTime.now().toString()},
59      where: 'id = ?',
60      whereArgs: [taskId],
61    );
62
63    return result;
64  }

Using TaskRepo on UI layer

You can use your favorite state-management solution or just pass down the widget tree.

main.dart

View on GitHub
 1import 'dart:io';
 2
 3import 'package:flutter/material.dart';
 4import 'package:sqflite_common_ffi/sqflite_ffi.dart';
 5import '/src/domain/repo/task_repo.dart';
 6
 7import '/src/presentation/task_page.dart';
 8
 9void main() {
10  WidgetsFlutterBinding.ensureInitialized();
11
12  if (Platform.isLinux || Platform.isWindows || Platform.isMacOS) {
13    sqfliteFfiInit();
14    databaseFactory = databaseFactoryFfi;
15  }
16  runApp(const MainApp());
17}
18
19class MainApp extends StatelessWidget {
20  const MainApp({super.key});
21
22  @override
23  Widget build(BuildContext context) {
24    return MaterialApp(
25      debugShowCheckedModeBanner: false,
26      darkTheme: ThemeData.from(
27        useMaterial3: true,
28        colorScheme: ColorScheme.fromSeed(
29          seedColor: Colors.indigo,
30          brightness: Brightness.dark,
31        ),
32      ),
33      themeMode: ThemeMode.dark,
34
35      home: FutureBuilder<TaskRepo>(
36        future: TaskRepo.create(),
37        builder: (context, asyncSnapshot) {
38          if (asyncSnapshot.error != null) {
39            return Material(
40              child: Center(child: Text(asyncSnapshot.error.toString())),
41            );
42          }
43
44          if (asyncSnapshot.data == null) return Material(child: Center(child: CircularProgressIndicator()));
45
46          return TaskPage(taskRepo: asyncSnapshot.data!);
47        },
48      ),
49    );
50  }
51}

task_page.dart

View on GitHub
  1import 'package:flutter/material.dart';
  2import '/src/domain/repo/task_repo.dart';
  3
  4import '/src/presentation/new_task_page.dart';
  5import '../domain/models/task.dart';
  6
  7class TaskPage extends StatefulWidget {
  8  const TaskPage({super.key, required this.taskRepo});
  9
 10  final TaskRepo taskRepo;
 11  @override
 12  State<TaskPage> createState() => _TaskPageState();
 13}
 14
 15class _TaskPageState extends State<TaskPage> {
 16  TaskRepo get repo => widget.taskRepo;
 17
 18  late Future<List<Task>> tasks = repo.getTasks();
 19
 20  Future<void> onNewTaskSave(Task task) async {
 21    final result = await repo.addTask(task);
 22    if (result == null) return;
 23
 24    tasks = repo.getTasks();
 25    setState(() {});
 26  }
 27
 28  void removeTask(Task t) async {
 29    final result = await repo.deleteTask(t.id);
 30    if (result == 0) return;
 31
 32    tasks = repo.getTasks();
 33    setState(() {});
 34  }
 35
 36  void updateTask(Task t, bool? isChecked) async {
 37    final result = await repo.toggleTaskStatus(
 38      t.id,
 39      isChecked == true ? TaskStatus.compelete : TaskStatus.due,
 40    );
 41
 42    if (result == 0) return;
 43
 44    tasks = repo.getTasks();
 45    setState(() {});
 46  }
 47
 48  @override
 49  Widget build(BuildContext context) {
 50    return Scaffold(
 51      appBar: AppBar(title: Text("Task list")),
 52      body: Column(
 53        children: [
 54          Expanded(
 55            child: FutureBuilder<List<Task>>(
 56              future: tasks,
 57              builder: (context, asyncSnapshot) {
 58                if (asyncSnapshot.connectionState == ConnectionState.waiting) {
 59                  return Center(child: CircularProgressIndicator());
 60                }
 61
 62                if (asyncSnapshot.hasError) {
 63                  return Center(child: Text(asyncSnapshot.error.toString()));
 64                }
 65
 66                final items = asyncSnapshot.data ?? [];
 67
 68                return ListView(
 69                  children: [
 70                    if (items.isEmpty) Center(child: Text("No task found")),
 71                    ...items.map(
 72                      (e) => ListTile(
 73                        title: Text(e.title),
 74                        subtitle: Text(e.description),
 75                        trailing: IconButton(
 76                          icon: Icon(Icons.delete),
 77                          onPressed: () => removeTask(e),
 78                        ),
 79
 80                        leading: Checkbox(
 81                          value: e.status == TaskStatus.compelete,
 82                          onChanged: (v) {
 83                            updateTask(e, v);
 84                          },
 85                        ),
 86                      ),
 87                    ),
 88                  ],
 89                );
 90              },
 91            ),
 92          ),
 93          Padding(
 94            padding: const EdgeInsets.all(8.0),
 95            child: Card(
 96              child: NewTaskPage(onSave: onNewTaskSave),
 97            ),
 98          ),
 99        ],
100      ),
101    );
102  }
103}

Export db

if people interested

import db

Next we will see how can we build local-first online sync app with Last write win.