#Lecture 003 — The Database Manager: Create & Update

Screens should not know how sqflite works. We put every database call in one class — DatabaseUser — so a screen only ever says "save this user" or "update that one."

Contents

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

#Prerequisites

Lectures 001–002: the table_user schema, ModelUser, and toMap()/fromMap().

#Connection With Previous Lectures

We can open a database and turn a user into a map. Someone must actually open the connection once, keep it open, and offer addRecord / updateRecord to the rest of the app. That someone is the manager — the same role GeneralBox plays in the Hive course, just talking SQL instead of boxes.

#Learning Objectives

• Write a singleton manager with a lazily-opened Database
• Insert a row with db.insert and read back the generated id
• Update a row with db.update, matched by where / whereArgs
• Explain why the manager — not the screen — owns every SQL statement

#1. The Manager Skeleton

// lib/data_sqflite/database_user.dart
import 'package:path/path.dart' as p;
import 'package:sqflite/sqflite.dart';

import 'model_user.dart';

class DatabaseUser {
  DatabaseUser._internal();
  static final DatabaseUser instance = DatabaseUser._internal();
  factory DatabaseUser() => instance;

  Database? _database;

  Future<Database> get database async {
    _database ??= await _initializeDatabase();
    return _database!;
  }

  Future<Database> _initializeDatabase() async {
    final databasesPath = await getDatabasesPath();
    final path = p.join(databasesPath, 'database_user.db');

    return openDatabase(
      path,
      version: 1,
      onCreate: (db, version) async {
        await db.execute('''
          CREATE TABLE $tableUser (
            $columnId INTEGER PRIMARY KEY,
            $columnName TEXT NOT NULL,
            $columnEmail TEXT NOT NULL,
            $columnPhoneNumber TEXT NOT NULL,
            $columnAvatar TEXT NOT NULL,
            $columnBearer TEXT NOT NULL,
            $columnCreatedAt TEXT NOT NULL,
            $columnUpdatedAt TEXT NOT NULL
          )
        ''');
      },
    );
  }
}

• DatabaseUser._internal() + static final instance + factory DatabaseUser() — the standard Dart singleton: every DatabaseUser() call anywhere in the app returns the same object, so there is only ever one open connection.
• _database ??= await _initializeDatabase(); — open the file the first time it's asked for, then reuse it. ??= only runs the right-hand side when _database is still null.
• get database is async — opening a file is I/O, so every caller must await DatabaseUser().database.

#2. Insert a Row

Future<int> addRecord(ModelUser user) async {
  final db = await database;
  return db.insert(tableUser, user.toMap());
}

• db.insert returns the row id SQLite assigned — exactly the value the model's id was null for. Hand that id back to the caller so it can build the saved ModelUser with a real id:

final id = await DatabaseUser().addRecord(newUser);
final saved = ModelUser(
  id: id,
  name: newUser.name,
  email: newUser.email,
  phoneNumber: newUser.phoneNumber,
  avatar: newUser.avatar,
  bearer: newUser.bearer,
  createdAt: newUser.createdAt,
  updatedAt: newUser.updatedAt,
);

#3. Update a Row

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

• where: '$columnId = ?' + whereArgs: [user.id] — the ? is a placeholder sqflite fills in safely; never interpolate the id straight into the string ('id = ${user.id}'). That opens the door to SQL injection the same way raw string SQL does in any language.
• update (like insert) returns an int — here, the number of rows that matched and changed. 0 means the id wasn't found; the caller can check for that.
• Because updateRecord takes a full ModelUser, update a copy of the loaded user (existingUser.copyWith(name: 'New Name')) rather than building a fresh one by hand — that way fields you didn't mean to touch keep their saved values.

#Code Examples

#Example 1 — Insert Then Update

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 rowsChanged = await db.updateRecord(ModelUser(
  id: id,
  name: 'Zayan Ali',
  email: 'zayan@example.com',
  phoneNumber: '03001234567',
  avatar: '',
  bearer: '',
  createdAt: DateTime.now().toIso8601String(),
  updatedAt: DateTime.now().toIso8601String(),
));

print(rowsChanged); // 1

#Common Beginner Mistakes

1. Interpolating a value straight into the where string (where: 'id = ${user.id}') — works for a well-behaved int, but trains you into a habit that is a SQL injection hole the moment the value is user-typed text. Always use ? + whereArgs.

2. Catching every exception and returning a fallback value — a tempting pattern is:

Future<bool> addRecord(ModelUser user) async {
  try {
    final db = await database;
    await db.insert(tableUser, user.toMap());
    return true;
  } catch (e) {
    return false;
  }
}

This hides why the insert failed — a locked database, a constraint violation, and a genuine bug all come back as the same false. Let the exception propagate (or log it) so the caller, or you during development, can see the real error.

3. Opening a new Database on every call instead of reusing the singleton's cached _database — every openDatabase call is a real file open; doing it per-call is slow and can exhaust file handles under heavy use.

4. Forgetting whereArgs — passing where: '$columnId = ?' with no whereArgs updates (or deletes) every row, because the unmatched ? makes the clause invalid and sqflite treats a malformed where as "match nothing filtered out."

#Best Practices

• One manager, one singleton, one cached Database — never scatter openDatabase calls through screens.
• Always parameterize where clauses with ? and whereArgs; never build SQL with string interpolation.
• Return the real result (int rows affected, or the thrown exception) instead of collapsing every outcome into true/false.

#Mini Challenge

Add a Future<int> addOrUpdate(ModelUser user) that calls addRecord when user.id == null and updateRecord otherwise — one method a screen can call for both "new" and "edit" forms.

#Practice Questions

1. Why does the manager use a singleton? 2. What does db.insert return, and why is that useful? 3. Why is where: '$columnId = ?' safer than string interpolation? 4. What happens if whereArgs is left out of an update call that has a where placeholder?

#Practice Answers

1. So the whole app shares one open database connection instead of opening the file repeatedly.
2. The row id SQLite assigned — needed to build the saved model, including on a fresh insert.
3. The value is bound safely instead of being spliced into the SQL text, which rules out SQL injection.
4. The where clause is invalid, so sqflite matches no rows as filtered — in practice this means the statement silently applies to every row instead of the one intended.

#Key Takeaways

• The manager is a singleton that lazily opens one Database and reuses it.
• insert returns the new row id; update returns the number of rows changed.
• where clauses are always parameterized with ? and whereArgs — never built with string interpolation.