Basic CRUD Operation with SQLite in Dart

Learn how to implement basic CRUD operations (Create, Read, Update, Delete) using SQLite in Dart. This tutorial guides you through setting up the sqflite plugin, defining a model class, creating a database helper, and performing database operations in a clean and modular way.

SQLite is a lightweight, fast, and reliable local database that’s ideal for mobile app development. Dart, combined with Flutter, makes data handling simple using the sqflite plugin.

External link: Learn more about the sqflite package on pub.dev

1. Setup

Add these dependencies to your pubspec.yaml:

dependencies:
  sqflite: ^2.3.0
  path: ^1.8.0

2. Model Class: Note.dart

class Note {
  final int? id;
  final String title;
  final String content;

  Note({this.id, required this.title, required this.content});

  Map<String, dynamic> toMap() {
    return {
      'id': id,
      'title': title,
      'content': content,
    };
  }

  factory Note.fromMap(Map<String, dynamic> map) {
    return Note(
      id: map['id'],
      title: map['title'],
      content: map['content'],
    );
  }
}

3. Database Helper: NoteDatabase.dart

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

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

  NoteDatabase._init();

  Future<Database> get database async {
    if (_database != null) return _database!;
    _database = await _initDB('notes.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 {
    await db.execute('''
      CREATE TABLE notes (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        title TEXT NOT NULL,
        content TEXT NOT NULL
      )
    ''');
  }

  Future<Note> create(Note note) async {
    final db = await instance.database;
    final id = await db.insert('notes', note.toMap());
    return note.copyWith(id: id);
  }

  Future<List<Note>> readAllNotes() async {
    final db = await instance.database;
    final result = await db.query('notes');
    return result.map((map) => Note.fromMap(map)).toList();
  }

  Future<int> update(Note note) async {
    final db = await instance.database;
    return db.update(
      'notes',
      note.toMap(),
      where: 'id = ?',
      whereArgs: [note.id],
    );
  }

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

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

4. Example Usage: main.dart

import 'Note.dart';
import 'NoteDatabase.dart';

void main() async {
  final db = NoteDatabase.instance;

  // Create
  final note1 = Note(title: 'First Note', content: 'Hello SQLite');
  await db.create(note1);

  // Read
  List<Note> notes = await db.readAllNotes();
  notes.forEach((note) => print('${note.id}: ${note.title}'));

  // Update
  final updatedNote = Note(id: 1, title: 'Updated Note', content: 'Updated content');
  await db.update(updatedNote);

  // Delete
  await db.delete(1);

  // Close
  await db.close();
}

This complete setup provides a solid foundation for integrating SQLite with Dart for local data storage. Use this as a reference when building Flutter apps that require persistent storage.