The Project | About Us | Contribute | Donations | License

HOME

In this section, we will explore how to use SQLite for local storage in Flutter applications. SQLite is a lightweight, disk-based database that doesn’t require a separate server process and allows access to the database using a nonstandard variant of the SQL query language. It is a popular choice for local storage in mobile applications due to its simplicity and efficiency.

Key Concepts

SQLite Database: A self-contained, serverless, zero-configuration, transactional SQL database engine.
sqflite Package: A Flutter plugin for SQLite, providing a simple API to interact with SQLite databases.
CRUD Operations: The basic operations for interacting with a database - Create, Read, Update, and Delete.

Setting Up SQLite in Flutter

Step 1: Add Dependencies

First, add the sqflite and path packages to your pubspec.yaml file:

dependencies:
  flutter:
    sdk: flutter
  sqflite: ^2.0.0+3
  path: ^1.8.0

Run flutter pub get to install the packages.

Step 2: Import Packages

Import the necessary packages in your Dart file:

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

Step 3: Initialize the Database

Create a function to initialize the database. This function will open the database and create a table if it doesn’t exist:

Future<Database> initializeDatabase() async {
  String path = join(await getDatabasesPath(), 'example.db');
  
  return openDatabase(
    path,
    onCreate: (db, version) {
      return db.execute(
        "CREATE TABLE items(id INTEGER PRIMARY KEY, name TEXT, value INTEGER)",
      );
    },
    version: 1,
  );
}

Step 4: CRUD Operations

Create

To insert data into the database, use the insert method:

Future<void> insertItem(Database db, Map<String, dynamic> item) async {
  await db.insert(
    'items',
    item,
    conflictAlgorithm: ConflictAlgorithm.replace,
  );
}

Read

To retrieve data from the database, use the query method:

Future<List<Map<String, dynamic>>> getItems(Database db) async {
  return await db.query('items');
}

Update

To update existing data, use the update method:

Future<void> updateItem(Database db, Map<String, dynamic> item) async {
  await db.update(
    'items',
    item,
    where: "id = ?",
    whereArgs: [item['id']],
  );
}

Delete

To delete data from the database, use the delete method:

Future<void> deleteItem(Database db, int id) async {
  await db.delete(
    'items',
    where: "id = ?",
    whereArgs: [id],
  );
}

Practical Example

Here is a complete example demonstrating how to use SQLite in a Flutter application:

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

void main() => runApp(MyApp());

class MyApp extends StatelessWidget {
  @override
  Widget build(BuildContext context) {
    return MaterialApp(
      home: HomeScreen(),
    );
  }
}

class HomeScreen extends StatefulWidget {
  @override
  _HomeScreenState createState() => _HomeScreenState();
}

class _HomeScreenState extends State<HomeScreen> {
  Database _database;
  List<Map<String, dynamic>> _items = [];

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

  Future<void> _initializeDatabase() async {
    _database = await initializeDatabase();
    _refreshItems();
  }

  Future<void> _refreshItems() async {
    final data = await getItems(_database);
    setState(() {
      _items = data;
    });
  }

  Future<void> _addItem() async {
    await insertItem(_database, {'name': 'Item ${_items.length + 1}', 'value': _items.length + 1});
    _refreshItems();
  }

  Future<void> _updateItem(int id) async {
    await updateItem(_database, {'id': id, 'name': 'Updated Item', 'value': 999});
    _refreshItems();
  }

  Future<void> _deleteItem(int id) async {
    await deleteItem(_database, id);
    _refreshItems();
  }

  @override
  Widget build(BuildContext context) {
    return Scaffold(
      appBar: AppBar(
        title: Text('SQLite Example'),
      ),
      body: ListView.builder(
        itemCount: _items.length,
        itemBuilder: (context, index) {
          final item = _items[index];
          return ListTile(
            title: Text(item['name']),
            subtitle: Text('Value: ${item['value']}'),
            trailing: Row(
              mainAxisSize: MainAxisSize.min,
              children: [
                IconButton(
                  icon: Icon(Icons.edit),
                  onPressed: () => _updateItem(item['id']),
                ),
                IconButton(
                  icon: Icon(Icons.delete),
                  onPressed: () => _deleteItem(item['id']),
                ),
              ],
            ),
          );
        },
      ),
      floatingActionButton: FloatingActionButton(
        onPressed: _addItem,
        child: Icon(Icons.add),
      ),
    );
  }
}

Exercises

Exercise 1: Create a New Table

Modify the initializeDatabase function to create a new table called users with columns id, username, and email.
Write functions to perform CRUD operations on the users table.

Exercise 2: Implement Search Functionality

Add a search bar to the HomeScreen widget.
Implement a function to search for items in the items table based on the name column.

Exercise 3: Handle Database Versioning

Modify the initializeDatabase function to handle database versioning.
Implement a migration strategy to add a new column description to the items table when upgrading the database version.

Summary

In this section, we learned how to use SQLite for local storage in Flutter applications. We covered the basics of setting up the sqflite package, initializing the database, and performing CRUD operations. We also provided a practical example and exercises to reinforce the concepts. SQLite is a powerful tool for managing local data, and mastering it will significantly enhance your Flutter development skills.

SQLite Database

Key Concepts

Setting Up SQLite in Flutter

Step 1: Add Dependencies

Step 2: Import Packages

Step 3: Initialize the Database

Step 4: CRUD Operations

Create

Read

Update

Delete

Practical Example

Exercises

Exercise 1: Create a New Table

Exercise 2: Implement Search Functionality

Exercise 3: Handle Database Versioning

Summary

Flutter Development Course

Module 1: Introduction to Flutter

Module 2: Dart Programming Basics

Module 3: Flutter Widgets

Module 4: State Management

Module 5: Navigation and Routing

Module 6: Networking and APIs

Module 7: Persistence and Storage

Module 8: Advanced Flutter Concepts

Module 9: Testing and Debugging

Module 10: Deployment and Maintenance

Module 11: Flutter for Web and Desktop