Hari 25: SQLite & Local Database

60 min Last updated 09 Apr 2026

sqflite — SQLite untuk Flutter

// pubspec.yaml: sqflite: ^2.3.0, path: ^1.8.0
import "package:sqflite/sqflite.dart";
import "package:path/path.dart";

class DatabaseHelper {
  static Database? _db;
  static const _dbName = "todo.db";
  static const _tableTodo = "todos";

  static Future get database async {
    _db ??= await _initDatabase();
    return _db!;
  }

  static Future _initDatabase() async {
    String path = join(await getDatabasesPath(), _dbName);
    return openDatabase(path, version: 1, onCreate: _onCreate);
  }

  static Future _onCreate(Database db, int version) async {
    await db.execute("""
      CREATE TABLE $_tableTodo (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        judul TEXT NOT NULL,
        selesai INTEGER DEFAULT 0,
        dibuat TEXT DEFAULT CURRENT_TIMESTAMP
      )
    """);
  }

  // CRUD
  static Future tambah(String judul) async {
    final db = await database;
    return db.insert(_tableTodo, {"judul": judul, "selesai": 0});
  }

  static Future>> semua() async {
    final db = await database;
    return db.query(_tableTodo, orderBy: "dibuat DESC");
  }

  static Future selesaikan(int id) async {
    final db = await database;
    return db.update(_tableTodo, {"selesai": 1}, where: "id = ?", whereArgs: [id]);
  }

  static Future hapus(int id) async {
    final db = await database;
    return db.delete(_tableTodo, where: "id = ?", whereArgs: [id]);
  }
}

💡 Notice: Pattern ini merepresentasikan sqflite API. Selalu gunakan parameterized queries untuk mencegah SQL injection.

Assignment

Buat simulasi SQLite CRUD dengan insert, update, delete, dan query berdasarkan filter.

Expected output:

=== Database (3 rows) ===

{judul: Belajar Dart, selesai: 0, id: 1}

{judul: Buat Flutter app, selesai: 0, id: 2}

{judul: Push ke GitHub, selesai: 0, id: 3}

Setelah update & delete:

=== Database (2 rows) ===

{judul: Belajar Dart, selesai: 0, id: 1}

{judul: Buat Flutter app, selesai: 1, id: 2}

Belum selesai:

{judul: Belajar Dart, selesai: 0, id: 1}

Dart main.dart

// Simulasi SQLite CRUD
class Database {
  List<Map<String, dynamic>> _rows = [];
  int _autoId = 1;

// INSERT
  int insert(Map<String, dynamic> row) {
    Map<String, dynamic> newRow = Map.from(row);
    newRow["id"] = _autoId++;
    _rows.add(newRow);
    return newRow["id"];
  }

// SELECT
  List<Map<String, dynamic>> query({String? where, dynamic whereArg}) {
    if (where == null) return List.from(_rows);
    return _rows.where((r) => r[where] == whereArg).toList();
  }

// UPDATE
  int update(int id, Map<String, dynamic> values) {
    int idx = _rows.indexWhere((r) => r["id"] == id);
    if (idx == -1) return 0;
    _rows[idx].addAll(values);
    return 1;
  }

// DELETE
  int delete(int id) {
    int before = _rows.length;
    _rows.removeWhere((r) => r["id"] == id);
    return before - _rows.length;
  }

void printAll() {
    print("=== Database (${_rows.length} rows) ===");
    for (var r in _rows) print(r);
  }
}

void main() {
  Database db = Database();

// Insert 3 todo
  db.insert({"judul": "Belajar Dart", "selesai": 0});
  db.insert({"judul": "Buat Flutter app", "selesai": 0});
  db.insert({"judul": "Push ke GitHub", "selesai": 0});
  db.printAll();

// Update id 2 jadi selesai
  db.update(2, {"selesai": 1});

// Delete id 3
  db.delete(3);

print("\nSetelah update & delete:");
  db.printAll();

// Query yang belum selesai
  print("\nBelum selesai:");
  db.query(where: "selesai", whereArg: 0).forEach(print);
}

Solution

class Database {
  List<Map<String, dynamic>> _rows = [];
  int _autoId = 1;

int insert(Map<String, dynamic> row) {
    Map<String, dynamic> newRow = Map.from(row);
    newRow["id"] = _autoId++;
    _rows.add(newRow);
    return newRow["id"];
  }

List<Map<String, dynamic>> query({String? where, dynamic whereArg}) {
    if (where == null) return List.from(_rows);
    return _rows.where((r) => r[where] == whereArg).toList();
  }

int update(int id, Map<String, dynamic> values) {
    int idx = _rows.indexWhere((r) => r["id"] == id);
    if (idx == -1) return 0;
    _rows[idx].addAll(values);
    return 1;
  }

int delete(int id) {
    int before = _rows.length;
    _rows.removeWhere((r) => r["id"] == id);
    return before - _rows.length;
  }

void printAll() {
    print("=== Database (${_rows.length} rows) ===");
    for (var r in _rows) print(r);
  }
}

void main() {
  Database db = Database();

db.insert({"judul": "Belajar Dart", "selesai": 0});
  db.insert({"judul": "Buat Flutter app", "selesai": 0});
  db.insert({"judul": "Push ke GitHub", "selesai": 0});
  db.printAll();

db.update(2, {"selesai": 1});
  db.delete(3);

print("\nSetelah update & delete:");
  db.printAll();

print("\nBelum selesai:");
  db.query(where: "selesai", whereArg: 0).forEach(print);
}

Output