sqfliteでCRUD

sqfliteでCRUDのコード例です。

https://pub.dev/packages/sqflite

全体を簡潔に把握できるコード例が、あまり無かったので残しておきます。
SqfliteServiceを定義して、riverpod_generatorを利用することでproviderを生成して使っています。

SELECTにrawQueryを利用しているのは特に意味はないです。queryの場合は以下のように書くようです。

await _database?.query('todos', orderBy: "date_modified DESC");
await _database?.query('todos', where: "id = ?", whereArgs: [id.toString()])

コード例

model/todo.dart

import 'package:freezed_annotation/freezed_annotation.dart';

part 'todo.freezed.dart';
part 'todo.g.dart';

@freezed
class Todo with _$Todo {
  const Todo._();

  factory Todo({
    @JsonKey(name: 'id') required int id,
    @JsonKey(name: 'title') required String title,
    @JsonKey(name: 'content') required String content,
    @JsonKey(name: 'is_complete') required int isCompleteInt,
    @JsonKey(name: 'date_modified') required int dateModified,
  }) = _Todo;

  factory Todo.fromJson(
    Map<String, dynamic> json,
  ) =>
      _$TodoFromJson(json);

  bool get isComplete => isCompleteInt == 1;
}

services/sqflite_service.dart

@Riverpod(keepAlive: true)
SqfliteService sqfliteService(SqfliteServiceRef ref) {
  return SqfliteService.getInstance();
}

class SqfliteService {
  SqfliteService._();

  Database? _database;

  Future<void> openDb() async {
    final databasesPath = await getDatabasesPath();
    const dataBaseName = "hogehoge.db";
    final path = join(databasesPath, dataBaseName);
    _database ??=
        await openDatabase(path, version: 1, onCreate: (db, version) async {
      await db.execute(
          'CREATE TABLE todos(id INTEGER PRIMARY KEY AUTOINCREMENT, title TEXT NULL, content TEXT NULL, is_complete INTEGER, date_modified INTEGER)');
    });
  }

  Future<List<Todo>> getAllTodos() async {
    if (_database == null) {
      await openDb();
    }
    List<Map<String, dynamic>> todos = await _database
            ?.rawQuery('SELECT * FROM todos ORDER BY date_modified DESC') ??
        [];
    return todos.map((e) => Todo.fromJson(e)).toList();
  }

  Future<Todo?> getTodoById(int id) async {
    if (_database == null) {
      await openDb();
    }
    List<Map<String, dynamic>> todos = await _database
            ?.rawQuery('SELECT * FROM todos WHERE id = ?', [id.toString()]) ??
        [];
    return todos.map((e) => Todo.fromJson(e)).toList().firstOrNull;
  }

  Future<Todo?> createTodo() async {
    if (_database == null) {
      await openDb();
    }
    final id = await _database?.insert('todos', {
      'title': '',
      'content': '',
      'is_complete': 0,
      'date_modified': DateTime.now().toUtc().millisecondsSinceEpoch
    });
    if (id == null) return null;
    return await getTodoById(id);
  }

  Future<int?> updateTodo(int id, String? title, String? content, bool? isComplete) async {
    if (_database == null) {
      await openDb();
    }
    Map<String, dynamic> updatedTodo = {
      'title': title,
      'content': content,
      'is_fav': isComplete == true
          ? 1
          : isComplete == false
              ? 0
              : null,
      'date_modified': DateTime.now().toUtc().millisecondsSinceEpoch
    };
    updatedTodo.removeWhere((_, value) => value == null);

    return await _database?.update(
      'todos',
      updatedTodo,
      where: "id = ?",
      whereArgs: [id.toString()],
    );
  }

  Future<int?> deleteTodo(int id) async {
    if (_database == null) {
      await openDb();
    }
    return await _database
        ?.delete('todos', where: "id = ?", whereArgs: [id.toString()]);
  }

  static SqfliteService getInstance() => SqfliteService._();
}

使い方例

@riverpod
class HomeViewModel extends _$HomeHomeViewModel {
  @override
  HomeViewModelState build() => const HomeViewModelState(todos: []);

  SqfliteService get _sqfliteService => ref.watch(sqfliteServiceProvider);

  Future<void> fetchAll() async {
    final todos = await _sqfliteService.getAllTodos();
    state = state.copyWith(todos: todos);
  }

  Future<Todo?> add() async {
    final addedTodo = await _sqfliteService.createTodo();
    await fetchAll();
    return addedTodo;
  }

  Future<void> delete(Todo todo) async {
    await _sqfliteService.deleteTodo(todo.id);
    await fetchAll();
  }

  Future<void> putTodoContent(Todo todo, String content) async {
    await _sqfliteService.updateTodo(
      todo.id,
      null,
      content,
      null,
    );
    await fetchAll();
  }
}