SQLite Database in Flutter

# CHAPTER 21

SQLite Database in Flutter #

1. Introduction #

2. Learning Objectives #

• Install the sqflite and path packages.

• Initialize an embedded SQLite database.

• Create SQL tables with primary keys and data types.

• Execute raw SQL queries or use built-in helper methods.

• Perform CRUD operations (Insert, Query, Update, Delete).

3. Setup and Installation #

dependencies:
  sqflite: ^2.3.0
  path: ^1.8.3 # Required to find the correct hard drive directory on iOS/Android

4. Creating the Database Helper #

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

class DatabaseHelper {
  // 1. Setup the Singleton
  static final DatabaseHelper instance = DatabaseHelper._init();
  static Database? _database;
  DatabaseHelper._init();

  // 2. Open the Database
  Future<Database> get database async {
    if (_database != null) return _database!;
    _database = await _initDB(&#039;notes.db');
    return _database!;
  }

  Future<Database> _initDB(String filePath) async {
    final dbPath = await getDatabasesPath();
    final path = join(dbPath, filePath);

    // 3. Create the Database and Tables
    return await openDatabase(path, version: 1, onCreate: _createDB);
  }

  Future _createDB(Database db, int version) async {
    // Write RAW SQL to create a table!
    await db.execute(&#039;''
      CREATE TABLE notes (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        title TEXT NOT NULL,
        content TEXT NOT NULL
      )
    &#039;'');
  }
}

5. Performing CRUD Operations #

CREATE (Insert):

Future<int> insertNote(Map<String, dynamic> row) async {
  Database db = await instance.database;
  // Automatically writes the INSERT INTO SQL command!
  return await db.insert(&#039;notes', row); 
}

READ (Query):

Future<List<Map<String, dynamic>>> getAllNotes() async {
  Database db = await instance.database;
  // Automatically writes SELECT * FROM notes!
  return await db.query(&#039;notes'); 
}

UPDATE:

Future<int> updateNote(Map<String, dynamic> row) async {
  Database db = await instance.database;
  int id = row[&#039;id'];
  return await db.update(&#039;notes', row, where: 'id = ?', whereArgs: [id]);
}

DELETE:

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

6. Integrating with the UI #

List<Map<String, dynamic>> myNotes = [];

@override
void initState() {
  super.initState();
  refreshNotes();
}

Future<void> refreshNotes() async {
  final data = await DatabaseHelper.instance.getAllNotes();
  setState(() {
    myNotes = data; // UI redraws with the database contents!
  });
}

// Inside the build method:
ElevatedButton(
  onPressed: () async {
    // 1. Insert data into DB
    await DatabaseHelper.instance.insertNote({
      &#039;title': 'Buy Groceries',
      &#039;content': 'Milk, Eggs, Bread'
    });
    // 2. Refresh the UI
    refreshNotes();
  },
  child: Text("Add Note"),
)

7. Visual Learning: Local vs Cloud #

[ SQFLITE (Offline App) ]               [ FIREBASE/API (Online App) ]

User Device:                            User Device:
+-------------+                         +-------------+
| UI Widget   |                         | UI Widget   |
|     v       |                         |     v       |
| SQFLite DB  |                         | Internet    |
| (100% Free) |                         |     v       |
+-------------+                         | Server DB   |
                                        +-------------+
Pros: Lightning fast, no internet.      Pros: Synced across multiple devices.
Cons: Data lost if phone is destroyed.  Cons: Slower, requires Wi-Fi.

8. Common Mistakes #

• SQL Injection Vulnerabilities: Notice in the Update/Delete methods we used where: 'id = ?', whereArgs: [id]. Beginners often try to manually concatenate strings: where: 'id = $id'. NEVER DO THIS. If a user types malicious SQL code into a text box, string concatenation will execute it and delete your entire database. ALWAYS use the ? parameters (whereArgs), which securely sanitizes the input!

9. Best Practices #

• Use Model Classes: Just like we did with JSON parsing in Chapter 19, you should never pass raw Maps to your UI. Create a Note class with fromMap() and toMap() methods. Your getAllNotes method should return a List<Note>, ensuring your UI is strictly typed and protected from typo crashes.

10. Practice Exercises #

1. 1. What official package allows you to embed a relational SQL database directly into a Flutter application?

1. 2. Write the raw SQL statement required to create a table named users with an id (integer) and name (text).

11. MCQs with Answers #

12. Interview Questions #

• Q: Explain the necessity of the path package when configuring sqflite. Why can't a developer simply hardcode a file path like C:/app/database.db?

• Q: Describe the security risks of manually concatenating variables into SQL strings. How does sqflite mitigate this risk through parameterized queries?

• Q: Compare the architectural use cases of shared_preferences versus sqflite. Provide a specific app feature that mandates the use of sqflite.

13. FAQs #

14. Summary #

15. Next Chapter Recommendation #