SQLite with sqflite: CRUD Operations and Querying

SQLite with sqflite: CRUD Operations and Querying

The sqflite package is the standard SQLite plugin for Flutter. It gives you a full relational database on device — persistent, structured, and query-capable. In this lesson you will master every CRUD operation (Create, Read, Update, Delete), write raw SQL when you need full control, map database rows back to typed Dart model objects, and group multiple writes inside a transaction so they either all succeed or all roll back together.

The Model Class

Before writing any database code, define a Dart class that mirrors your table. A clean model includes a toMap() method for inserting/updating and a fromMap() factory constructor for reading rows back out.

class Task {
  final int? id;        // nullable — null before first insert
  final String title;
  final bool isDone;
  final DateTime createdAt;

  const Task({
    this.id,
    required this.title,
    this.isDone = false,
    required this.createdAt,
  });

  // Dart object  ➜  DB row
  Map<String, dynamic> toMap() => {
    if (id != null) 'id': id,
    'title': title,
    'is_done': isDone ? 1 : 0,          // SQLite has no BOOLEAN
    'created_at': createdAt.toIso8601String(),
  };

  // DB row  ➜  Dart object
  factory Task.fromMap(Map<String, dynamic> map) => Task(
    id: map['id'] as int?,
    title: map['title'] as String,
    isDone: (map['is_done'] as int) == 1,
    createdAt: DateTime.parse(map['created_at'] as String),
  );

  Task copyWith({int? id, String? title, bool? isDone}) => Task(
    id: id ?? this.id,
    title: title ?? this.title,
    isDone: isDone ?? this.isDone,
    createdAt: createdAt,
  );
}

Insert (Create)

Use db.insert() to add a row. Pass the table name, the map from toMap(), and an optional ConflictAlgorithm that controls what happens when a unique constraint is violated.

import 'package:sqflite/sqflite.dart';

Future<int> insertTask(Database db, Task task) async {
  // Returns the new row's id (auto-increment integer)
  return await db.insert(
    'tasks',
    task.toMap(),
    conflictAlgorithm: ConflictAlgorithm.replace,
    // replace  → overwrite existing row with same PRIMARY KEY
    // ignore   → silently discard the duplicate
    // rollback → throw an exception (default)
  );
}

// Usage
final newId = await insertTask(db, Task(
  title: 'Buy groceries',
  createdAt: DateTime.now(),
));
print('Inserted task with id $newId');

Query (Read)

Use db.query() for a structured SELECT, or db.rawQuery() when you need JOINs, subqueries, or complex WHERE clauses. Both return List<Map<String, dynamic>> which you map to your model with fromMap().

// --- Structured query ---
Future<List<Task>> getPendingTasks(Database db) async {
  final rows = await db.query(
    'tasks',
    columns: ['id', 'title', 'is_done', 'created_at'],
    where: 'is_done = ?',
    whereArgs: [0],                      // always use ? placeholders
    orderBy: 'created_at DESC',
    limit: 50,
  );
  return rows.map(Task.fromMap).toList();
}

// --- Raw SQL query ---
Future<List<Task>> searchTasks(Database db, String keyword) async {
  final rows = await db.rawQuery(
    'SELECT * FROM tasks WHERE title LIKE ? ORDER BY created_at DESC',
    ['%$keyword%'],
  );
  return rows.map(Task.fromMap).toList();
}

// --- Fetch single row by id ---
Future<Task?> getTaskById(Database db, int id) async {
  final rows = await db.query(
    'tasks',
    where: 'id = ?',
    whereArgs: [id],
    limit: 1,
  );
  if (rows.isEmpty) return null;
  return Task.fromMap(rows.first);
}

Update

Use db.update() to modify existing rows. Provide a where clause and whereArgs to target specific rows; omitting them updates every row in the table.

Future<int> markTaskDone(Database db, int id) async {
  // Returns the number of rows affected
  return await db.update(
    'tasks',
    {'is_done': 1},
    where: 'id = ?',
    whereArgs: [id],
  );
}

// Full object update using the model's toMap()
Future<int> updateTask(Database db, Task task) async {
  return await db.update(
    'tasks',
    task.toMap(),
    where: 'id = ?',
    whereArgs: [task.id],
  );
}

Delete

Use db.delete() to remove rows. Like update(), omitting where deletes all rows.

// Delete one row
Future<int> deleteTask(Database db, int id) async {
  return await db.delete('tasks', where: 'id = ?', whereArgs: [id]);
}

// Delete all completed tasks inside a transaction so the
// operation is atomic — either all rows are removed or none.
Future<void> purgeCompleted(Database db) async {
  await db.transaction((txn) async {
    // txn is a Transaction object that behaves like Database
    final deleted = await txn.delete(
      'tasks',
      where: 'is_done = ?',
      whereArgs: [1],
    );
    await txn.rawInsert(
      'INSERT INTO audit_log (action, count, ts) VALUES (?, ?, ?)',
      ['purge_completed', deleted, DateTime.now().toIso8601String()],
    );
  });
  // If any statement inside throws, the whole transaction rolls back.
}

Transactions for Grouped Writes

A transaction wraps multiple SQL statements in a single atomic unit. Either every statement succeeds and the changes are committed, or an exception causes a full rollback. Use transactions when:

• Inserting related rows across multiple tables (e.g., an order + its line items)
• Updating a counter and inserting a log entry together
• Importing a batch of records — if one fails, none should persist

Summary

The four CRUD helpers — insert(), query(), update(), delete() — cover the majority of database work. For complex queries, fall back to rawQuery() and rawInsert()/rawUpdate(). Define a model class with toMap() and fromMap() to keep serialization logic in one place, and always use ? placeholders to prevent SQL injection. Group related writes inside db.transaction() to guarantee atomicity and to boost batch-insert throughput.