Flutter - Database Concepts

Flutter - Modern Database Concepts

Flutter offers robust packages for interacting with various types of databases, both local and cloud-based. Key options include:

• sqflite: The standard package for accessing and manipulating local SQLite databases on the device.
• cloud_firestore: A package for interacting with Google’s flexible, scalable NoSQL cloud database, Firebase Firestore.
• Other options: Packages like drift (formerly Moor) provide higher-level abstractions over SQLite/SQL, while hive offers a fast key-value store. Realm also provides an official SDK for Flutter.

This chapter focuses on sqflite for local storage and cloud_firestore for cloud storage, demonstrating common patterns.

Local Storage with SQLite and sqflite

SQLite is a time-tested, embedded SQL database engine ideal for storing structured data locally on a device. The sqflite package provides a low-level API to interact with SQLite databases effectively in Flutter.

Core functionalities provided by sqflite include:

• Opening or Creating a Database: Using openDatabase, you can connect to an existing database file or create a new one.
• Executing SQL Statements: Methods like execute allow running raw SQL commands (CREATE TABLE, INSERT, UPDATE, DELETE). Use parameterized queries (? placeholders) to prevent SQL injection vulnerabilities.
• Querying Data: The query method provides a convenient way to fetch data using structured parameters (columns, where, orderBy, etc.). Raw queries can be performed using rawQuery.
• Transactions: transaction ensures multiple database operations are performed atomically (all succeed or all fail).

Let’s update the product application example to demonstrate storing and fetching product information using sqflite with modern practices.

1. Create a new Flutter application: flutter create product_sqlite_app.
2. Navigate into the project: cd product_sqlite_app.
3. Add dependencies: Open pubspec.yaml and add sqflite and path_provider under dependencies:

dependencies:
  flutter:
    sdk: flutter
  sqflite: ^2.3.0 # Use the latest stable version
  path_provider: ^2.1.1 # Use the latest stable version
  path: ^1.8.3 # Usually included transitively, but good to specify

1. Run flutter pub get in your terminal to install the packages.
2. Define the Data Model: Create a lib/product.dart file with the Product class. Ensure it uses null safety and has methods for converting to/from a Map, crucial for database interaction. Using an auto-incrementing primary key is recommended.

class Product {
  final int? id; // Nullable for new products before insertion
  final String name;
  final String description;
  final int price;
  final String image;

  // Use const constructor for immutable objects
  const Product({
    this.id,
    required this.name,
    required this.description,
    required this.price,
    required this.image,
  });

  // Factory constructor to create a Product from a Map (e.g., from DB)
  factory Product.fromMap(Map<String, dynamic> map) {
    return Product(
      id: map['id'] as int?,
      name: map['name'] as String,
      description: map['description'] as String,
      price: map['price'] as int,
      image: map['image'] as String,
    );
  }

  // Method to convert a Product instance to a Map (e.g., for DB insertion/update)
  Map<String, dynamic> toMap() {
    // Exclude 'id' when null (for auto-increment insertion)
    return {
      if (id != null) 'id': id,
      'name': name,
      'description': description,
      'price': price,
      'image': image,
    };
  }

  // Optional: copyWith method for easier updates
  Product copyWith({
    int? id,
    String? name,
    String? description,
    int? price,
    String? image,
  }) {
    return Product(
      id: id ?? this.id,
      name: name ?? this.name,
      description: description ?? this.description,
      price: price ?? this.price,
      image: image ?? this.image,
    );
  }

  @override
  String toString() {
    return 'Product(id: $id, name: $name, description: $description, price: $price, image: $image)';
  }
}

1. Create a Database Helper Service: Create a lib/database_helper.dart file. This class will encapsulate all database operations using the singleton pattern or dependency injection.

import 'dart:async';
import 'package:path/path.dart';
import 'package:sqflite/sqflite.dart';
import 'package:path_provider/path_provider.dart';
import 'product.dart';

class DatabaseHelper {
  // Singleton pattern
  static final DatabaseHelper _instance = DatabaseHelper._internal();
  factory DatabaseHelper() => _instance;
  DatabaseHelper._internal();

  static Database? _database;
  static const String _dbName = 'ProductDB.db';
  static const String _tableName = 'Product';
  static const int _dbVersion = 1;

  Future<Database> get database async {
    if (_database != null) return _database!;
    _database = await _initDB();
    return _database!;
  }

  Future<Database> _initDB() async {
    final documentsDirectory = await getApplicationDocumentsDirectory();
    final path = join(documentsDirectory.path, _dbName);

    return await openDatabase(
      path,
      version: _dbVersion,
      onCreate: _onCreate,
      // onUpgrade: _onUpgrade, // Optional: Handle schema migrations here
    );
  }

  // Create table and insert initial data
  Future<void> _onCreate(Database db, int version) async {
    await db.execute('''
      CREATE TABLE $_tableName (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        name TEXT NOT NULL,
        description TEXT NOT NULL,
        price INTEGER NOT NULL,
        image TEXT NOT NULL
      )
    ''');

    // Optional: Insert initial data using Batch for efficiency
    final batch = db.batch();
    _getInitialProducts().forEach((product) {
      batch.insert(_tableName, product.toMap());
    });
    await batch.commit(noResult: true);
  }

  List<Product> _getInitialProducts() {
    return [
      Product(name: 'iPhone', description: 'The stylish phone', price: 1000, image: 'iphone.png'),
      Product(name: 'Pixel', description: 'The feature phone', price: 800, image: 'pixel.png'),
      Product(name: 'Laptop', description: 'Productive tool', price: 2000, image: 'laptop.png'),
      // ... add other initial products
    ];
  }

  // Insert a Product
  Future<int> insertProduct(Product product) async {
    final db = await database;
    // Use `insert` which handles auto-increment ID
    return await db.insert(_tableName, product.toMap(), conflictAlgorithm: ConflictAlgorithm.replace);
  }

  // Get all Products
  Future<List<Product>> getAllProducts() async {
    final db = await database;
    final List<Map<String, dynamic>> maps = await db.query(_tableName, orderBy: 'id ASC');

    return List.generate(maps.length, (i) {
      return Product.fromMap(maps[i]);
    });
  }

  // Get a single Product by ID
  Future<Product?> getProductById(int id) async {
    final db = await database;
    final List<Map<String, dynamic>> maps = await db.query(
      _tableName,
      where: 'id = ?',
      whereArgs: [id],
      limit: 1, // Expect only one result
    );

    if (maps.isNotEmpty) {
      return Product.fromMap(maps.first);
    } else {
      return null;
    }
  }

  // Update a Product
  Future<int> updateProduct(Product product) async {
    final db = await database;
    return await db.update(
      _tableName,
      product.toMap(),
      where: 'id = ?',
      whereArgs: [product.id],
    );
  }

  // Delete a Product
  Future<int> deleteProduct(int id) async {
    final db = await database;
    return await db.delete(
      _tableName,
      where: 'id = ?',
      whereArgs: [id],
    );
  }

  // Close the database (optional, useful in specific scenarios)
  Future<void> close() async {
    final db = await database;
    db.close();
    _database = null; // Reset the static variable
  }
}

1. Integrate with UI: Modify your main.dart (or relevant widget files) to use DatabaseHelper.
2. Use FutureBuilder or a state management solution (like Provider or Riverpod) to handle the asynchronous nature of database calls and update the UI accordingly.

// Example usage within a StatefulWidget's State or a StateNotifier/ChangeNotifier

// Get the helper instance
final dbHelper = DatabaseHelper();

// Fetch products
Future<List<Product>> _loadProducts() async {
  return await dbHelper.getAllProducts();
}

// Add a new product
Future<void> _addProduct(Product newProduct) async {
  await dbHelper.insertProduct(newProduct);
  // Refresh UI (e.g., call setState or notifyListeners)
}

// Update an existing product
Future<void> _updateProduct(Product updatedProduct) async {
  await dbHelper.updateProduct(updatedProduct);
  // Refresh UI
}

// Delete a product
Future<void> _deleteProduct(int id) async {
  await dbHelper.deleteProduct(id);
  // Refresh UI
}

This setup provides a clean separation of concerns and uses modern Dart/Flutter practices for local database management.

Cloud Storage with Firebase Firestore

Firebase offers Backend-as-a-Service (BaaS) features, significantly speeding up app development. Firebase Firestore is a popular choice for a cloud-based, real-time NoSQL database that scales automatically and offers offline support.

The cloud_firestore package allows Flutter apps to interact directly with Firestore. Let’s update the example to connect to Firestore.

1. Create a new Flutter application: flutter create product_firestore_app.
2. Navigate into the project: cd product_firestore_app.
3. Add Firebase Core and Firestore dependencies: Open pubspec.yaml:

dependencies:
  flutter:
    sdk: flutter
  firebase_core: ^2.24.2 # Use latest stable version
  cloud_firestore: ^4.13.6 # Use latest stable version

1. Run flutter pub get.
2. Configure Firebase: This is a crucial step and involves platform-specific setup.
3. a. Install the Firebase CLI: npm install -g firebase-tools (or other methods).
4. b. Log in: firebase login.
5. c. Install the FlutterFire CLI: dart pub global activate flutterfire_cli.
6. d. Configure your project: Run flutterfire configure in your Flutter project’s root directory. This command will guide you through selecting or creating a Firebase project and automatically configure your Android, iOS, and potentially web apps, generating necessary files like firebase_options.dart and updating native configuration files.
7. e. (Manual Fallback - Not Recommended): If CLI fails, follow the Firebase console instructions to manually add Android/iOS apps, download google-services.json (Android) / GoogleService-Info.plist (iOS), and configure Gradle/Xcode.
8. Initialize Firebase in your app: Update lib/main.dart to initialize Firebase before running the app.

import 'package:flutter/material.dart';
import 'package:firebase_core/firebase_core.dart';
import 'firebase_options.dart'; // Generated by flutterfire configure

void main() async {
  // Ensure Flutter bindings are initialized
  WidgetsFlutterBinding.ensureInitialized();

  // Initialize Firebase
  await Firebase.initializeApp(
    options: DefaultFirebaseOptions.currentPlatform,
  );

  runApp(MyApp());
}

class MyApp extends StatelessWidget {
  // ... rest of your MyApp widget
  @override
  Widget build(BuildContext context) {
    return MaterialApp(
      // ...
    );
  }
}

1. Update the Product class (if needed): Ensure Product.fromMap can handle Firestore’s data types (e.g., Timestamp for dates). Add a toJson or toMap method suitable for Firestore.

import 'package:cloud_firestore/cloud_firestore.dart';

class Product {
  final String? id; // Firestore document ID
  final String name;
  final String description;
  final int price;
  final String image;
  // Use Timestamp for dates in Firestore
  final Timestamp? createdAt;

  Product({
    this.id,
    required this.name,
    required this.description,
    required this.price,
    required this.image,
    this.createdAt,
  });

  // Convert Firestore DocumentSnapshot to Product object
  factory Product.fromFirestore(DocumentSnapshot<Map<String, dynamic>> snapshot, SnapshotOptions? options) {
    final data = snapshot.data();
    return Product(
      id: snapshot.id,
      name: data?['name'] ?? '',
      description: data?['description'] ?? '',
      price: data?['price'] ?? 0,
      image: data?['image'] ?? '',
      createdAt: data?['createdAt'] as Timestamp?,
    );
  }

  // Convert Product object to Map for Firestore
  Map<String, dynamic> toFirestore() {
    return {
      'name': name,
      'description': description,
      'price': price,
      'image': image,
      // Use FieldValue for server-side timestamp on creation
      'createdAt': createdAt ?? FieldValue.serverTimestamp(),
    };
  }
}

1. Interact with Firestore: Use FirebaseFirestore.instance to get data.
2. a. Get a reference to your collection: FirebaseFirestore.instance.collection('products').
3. b. Use .withConverter for type safety with your Product model.
4. c. Fetch data using get() (for single fetch) or snapshots() (for real-time updates).

// Get a reference to the collection with a type converter
final productsRef = FirebaseFirestore.instance
    .collection('products')
    .withConverter<Product>(
      fromFirestore: Product.fromFirestore,
      toFirestore: (Product product, _) => product.toFirestore(),
    );

// Fetch products once
Future<List<Product>> fetchProductsOnce() async {
  try {
    final querySnapshot = await productsRef.orderBy('createdAt', descending: true).get();
    return querySnapshot.docs.map((doc) => doc.data()).toList();
  } catch (e) {
    print('Error fetching products: $e');
    return [];
  }
}

// Get a real-time stream of products
Stream<QuerySnapshot<Product>> fetchProductsStream() {
  return productsRef.orderBy('createdAt', descending: true).snapshots();
}

// Add a new product
Future<void> addProduct(Product newProduct) async {
  try {
    await productsRef.add(newProduct);
    print('Product added!');
  } catch (e) {
    print('Error adding product: $e');
  }
}

// Update a product (requires document ID)
Future<void> updateProduct(String docId, Product updatedProduct) async {
  try {
    await productsRef.doc(docId).update(updatedProduct.toFirestore());
    print('Product updated!');
  } catch (e) {
    print('Error updating product: $e');
  }
}

// Delete a product (requires document ID)
Future<void> deleteProduct(String docId) async {
  try {
    await productsRef.doc(docId).delete();
    print('Product deleted!');
  } catch (e) {
    print('Error deleting product: $e');
  }
}

1. Use StreamBuilder for Real-time Updates: If using snapshots(), wrap your UI component (like a ListView) in a StreamBuilder to automatically rebuild when data changes in Firestore.

@override
Widget build(BuildContext context) {
  return Scaffold(
    appBar: AppBar(title: Text("Products from Firestore")),
    body: StreamBuilder<QuerySnapshot<Product>>(
      stream: fetchProductsStream(), // Your stream function
      builder: (context, snapshot) {
        if (snapshot.hasError) {
          return Center(child: Text('Error: ${snapshot.error}'));
        }
        if (snapshot.connectionState == ConnectionState.waiting) {
          return Center(child: CircularProgressIndicator());
        }
        if (!snapshot.hasData || snapshot.data!.docs.isEmpty) {
          return Center(child: Text('No products found.'));
        }

        // Data is available
        final products = snapshot.data!.docs.map((doc) => doc.data()).toList();

        return ListView.builder(
          itemCount: products.length,
          itemBuilder: (context, index) {
            final product = products[index];
            // Build your list item widget using product data
            return ListTile(
              title: Text(product.name),
              subtitle: Text(product.description),
              // ... add interactions like delete/edit
            );
          },
        );
      },
    ),
    floatingActionButton: FloatingActionButton(
      onPressed: () {
        // Example: Add a new product
        addProduct(Product(name: 'New Gadget', description: 'Latest tech', price: 500, image: 'gadget.png'));
      },
      child: Icon(Icons.add),
    ),
  );
}

Firestore provides a powerful, scalable backend solution. Remember to configure Firestore security rules in the Firebase console to protect your data.