#Lecture 004 — The Database Manager: Delete & Query

insert and update change rows; now the manager needs to remove them and, just as importantly, read them back as ModelUser objects a screen can display.

Contents

Prerequisites · Connection · Objectives · Lesson · Code Examples · Common Mistakes · Best Practices · Mini Challenge · Practice Questions · Answers · Takeaways

#Prerequisites

Lecture 003: the DatabaseUser singleton, addRecord and updateRecord.

#Connection With Previous Lectures

The manager can create and change rows. A dashboard also needs to remove a user, load one specific user, load the whole list, and show a count — the four remaining jobs of a CRUD manager.

#Learning Objectives

• Delete one row and all rows with db.delete
• Fetch rows with db.query and map each one through ModelUser.fromMap
• Return null (not a crash) when a single-record lookup finds nothing
• Explain why a count query should not just fetch every row and measure the list

#1. Deleting Rows

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

Future<int> deleteAllRecords() async {
  final db = await database;
  return db.delete(tableUser);
}

• deleteRecord takes an int id — the same type the column actually stores. The original pasted snippet this lecture is built from took a String id and compared it against an INTEGER column; SQLite's loose typing makes that appear to work, but it's a type mismatch waiting to misbehave the moment the comparison logic changes. Keep the Dart type matching the column type.
• db.delete(tableUser) with no where deletes every row — exactly like a DELETE FROM table_user; with no WHERE clause. That's deliberate for deleteAllRecords, and exactly the trap described in Lecture 003 if it happens by accident in deleteRecord.
• Both return the number of rows deleted — 0 from deleteRecord means "no row had that id," which the caller can check instead of assuming success.

#2. Reading One Row

Future<ModelUser?> getSingleRecord(int id) async {
  final db = await database;
  final rows = await db.query(
    tableUser,
    where: '$columnId = ?',
    whereArgs: [id],
    limit: 1,
  );

  if (rows.isEmpty) return null;
  return ModelUser.fromMap(rows.first);
}

• Return type is ModelUser?, not ModelUser. "No user with this id" is a normal, expected outcome — not an error — so it's modeled with null, not with a thrown exception or a force-unwrap.
• limit: 1 stops SQLite as soon as it finds a match instead of scanning the whole table.
• rows is List<Map<String, Object?>> — exactly the shape ModelUser.fromMap expects.

#3. Reading All Rows

Future<List<ModelUser>> getAllRecords() async {
  final db = await database;
  final rows = await db.query(tableUser, orderBy: '$columnCreatedAt DESC');
  return rows.map(ModelUser.fromMap).toList();
}

• orderBy: '$columnCreatedAt DESC' — newest first, done by SQLite itself rather than sorting the list in Dart after loading it.
• rows.map(ModelUser.fromMap).toList() — one line replaces a hand-written loop that builds a list and appends to it one row at a time.
• An empty table naturally returns [] here — there is no special "found nothing" case to handle separately, unlike getSingleRecord.

#4. Counting Rows

Future<int> getCount() async {
  final db = await database;
  final result = await db.rawQuery('SELECT COUNT(*) FROM $tableUser');
  return Sqflite.firstIntValue(result) ?? 0;
}

• SELECT COUNT(*) asks SQLite to compute the count — it never loads the actual rows into memory. Calling getAllRecords() and reading .length would work for a few hundred users and quietly get slower as the table grows, since every column of every row has to be read and mapped just to be thrown away.
• Sqflite.firstIntValue(result) pulls the single integer out of the single-row, single-column result rawQuery returns.

#Code Examples

#Example 1 — Using the Full Manager

final db = DatabaseUser();

final id = await db.addRecord(ModelUser(
  name: 'Zayan',
  email: 'zayan@example.com',
  phoneNumber: '03001234567',
  avatar: '',
  bearer: '',
  createdAt: DateTime.now().toIso8601String(),
  updatedAt: DateTime.now().toIso8601String(),
));

final user = await db.getSingleRecord(id);        // ModelUser
final everyone = await db.getAllRecords();        // List<ModelUser>
final total = await db.getCount();                // 1

await db.deleteRecord(id);
final afterDelete = await db.getSingleRecord(id); // null

#Common Beginner Mistakes

1. Returning null! to satisfy a non-nullable return type — forcing a null into a type that claims it can't be null crashes the instant a "not found" case actually happens. If a lookup can legitimately find nothing, its return type must say so with ?.

2. Computing a count by loading everything ((await getAllRecords()).length) — correct, but it does far more work than SELECT COUNT(*) for the same answer.

3. Deleting with the wrong type in whereArgs — passing a String id against an INTEGER column usually still matches today, because SQLite's type affinity coerces it, but it's relying on a coercion rather than a guarantee. Match the column's declared type.

4. Using query's where for "all rows" by passing an empty string (where: '') instead of simply omitting where — an empty clause is not the same as no clause to every database engine, and sqflite expects omission, not an empty string.

#Best Practices

• Model "might not exist" with a nullable return type, not a sentinel value or a forced unwrap.
• Let SQLite do filtering, sorting and counting (WHERE, ORDER BY, COUNT(*)) instead of loading everything and processing it in Dart.
• Keep the Dart parameter type for a column's id matching the column's declared SQL type.

#Mini Challenge

Add Future<List<ModelUser>> searchByName(String query) async using where: '$columnName LIKE ?' and whereArgs: ['%$query%'] — a case-sensitive "contains" search over the name column.

#Practice Questions

1. Why does getSingleRecord return ModelUser? instead of ModelUser? 2. What does db.delete(tableUser) with no where do? 3. Why use SELECT COUNT(*) instead of getAllRecords().length? 4. What does Sqflite.firstIntValue do?

#Practice Answers

1. "No row with this id" is an expected, normal outcome, not an error — null models that without a crash or a fake value.
2. Deletes every row in the table.
3. SQLite computes the count without reading every row's data into Dart memory.
4. Extracts the single integer from the one-row, one-column result a rawQuery like SELECT COUNT(*) returns.

#Key Takeaways

• delete with no where clears the whole table — exactly like SQL's own DELETE FROM.
• A lookup that might find nothing returns a nullable model, never a forced, fake value.
• Filtering, sorting and counting belong in the SQL statement, not in a Dart loop after loading every row.