Flutter Sqflite(2.0.0+3) database CRUD operation implementation.

databaseHandlerTodos.dart

import 'package:path/path.dart';
import 'package:sqflite/sqflite.dart';
// Modify the todoDataModle.dart's location according to your project.
import 'package:<your-package-directory>/dataModle/todoDataModle.dart';

class DatabaseHandlerTodos {
  static final DatabaseHandlerTodos instance = DatabaseHandlerTodos._init();
  static Database? _database;
  DatabaseHandlerTodos._init();

  Future<Database> get database async {
    if (_database != null) {
      return _database!;
    }
    _database = await _initDB('todos.db');
    return _database!;
  }

  Future<Database> _initDB(String filePath) async {
    final dbPath = await getDatabasesPath();
    final path = join(dbPath, filePath);

    return await openDatabase(path, version: 1, onCreate: _createDB);
  }

  Future _createDB(Database db, int version) async {
    final idType = 'INTEGER PRIMARY KEY AUTOINCREMENT';
    final textType = 'TEXT NOT NULL';
//     You can customise your table attributes here
    await db.execute('''
      CREATE TABLE $tableTodos ( 
        ${TodoFields.id} $idType,
        ${TodoFields.title} $textType,
        ${TodoFields.subtitle} $textType,
        ${TodoFields.priority} $textType,
        ${TodoFields.dueDate} $textType,
        ${TodoFields.date} $textType
        )
      ''');
  }

  Future<Todo> create(Todo todo) async {
    final db = await instance.database;
    final id = await db.insert(tableTodos, todo.toJson());
    return todo.copy(id: id);
  }

  Future<Todo> readTodo(int id) async {
    final db = await instance.database;
    final maps = await db.query(
      tableTodos,
      columns: TodoFields.values,
      where: '${TodoFields.id} = ?',
      whereArgs: [id],
    );
    if (maps.isNotEmpty) {
      return Todo.fromJson(maps.first);
    } else {
      throw Exception('ID $id not found');
    }
  }

  Future<List<Todo>> readAllTodos() async {
    final db = await instance.database;
    final orderBy = '${TodoFields.id} DESC';
    // final result =
    //     await db.rawQuery('SELECT * FROM $tableTodos ORDER BY $orderBy');

    final result = await db.query(tableTodos, orderBy: orderBy);
    return result.map((json) => Todo.fromJson(json)).toList();
  }

  Future<int> update(Todo Todo) async {
    final db = await instance.database;

    return db.update(
      tableTodos,
      Todo.toJson(),
      where: '${TodoFields.id} = ?',
      whereArgs: [Todo.id],
    );
  }

  Future<int> delete(int id) async {
    final db = await instance.database;
    return await db.delete(
      tableTodos,
      where: '${TodoFields.id} = ?',
      whereArgs: [id],
    );
  }

  Future close() async {
    final db = await instance.database;
    db.close();
  }
}

todoDataModle.dart

final String tableTodos = 'todos';

class TodoFields {
  static final List<String> values = [
    id,
    title,
    subtitle,
    priority,
    dueDate,
    date
  ];
  static final String id = '_id';
  static final String title = 'title';
  static final String subtitle = 'subtitle';
  static final String priority = 'priority';
  static final String dueDate = 'dueDate';
  static final String date = 'date';
}

class Todo {
  final int? id;
  final String title;
  final String subtitle;
  final String priority;
  final String dueDate;
  final String date;

  const Todo({
    this.id,
    required this.title,
    required this.subtitle,
    required this.priority,
    required this.dueDate,
    required this.date,
  });

  Todo copy({
    int? id,
    String? title,
    String? subtitle,
    String? priority,
    String? dueDate,
    String? date,
  }) {
    return Todo(
      id: id ?? this.id,
      title: title ?? this.title,
      subtitle: subtitle ?? this.subtitle,
      priority: priority ?? this.priority,
      dueDate: dueDate ?? this.dueDate,
      date: date ?? this.date,
    );
  }

  static Todo fromJson(Map<String, Object?> json) {
    return Todo(
      id: json[TodoFields.id] as int?,
      title: json[TodoFields.title] as String,
      subtitle: json[TodoFields.subtitle] as String,
      priority: json[TodoFields.priority] as String,
      dueDate: json[TodoFields.dueDate] as String,
      date: json[TodoFields.date] as String,
    );
  }

  Map<String, Object?> toJson() {
    return {
      TodoFields.id: id,
      TodoFields.title: title,
      TodoFields.subtitle: subtitle,
      TodoFields.priority: priority,
      TodoFields.dueDate: dueDate,
      TodoFields.date: date,
    };
  }
}