Arslan Yousaf

Posted on Feb 25

SQLite in Flutter: The Complete Guide

The Hero's Journey into Local Database Storage

Hello, brave Flutter developer! 👋 Are you tired of losing your app data every time your user closes the app? Wish there was a way to store data locally without sending desperate prayers to the app state gods?

When you realize you've been using print statements to debug your database code

Fear not! Today we embark on an epic quest to master SQLite in Flutter using the powerful sqflite package. By the end of this guide, you'll be storing, retrieving, updating, and deleting data like a database wizard! 🧙‍♂️

Let's begin our journey!

Setting Up the Project

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

dependencies:
  flutter:
    sdk: flutter
  sqflite: ^2.3.0
  path: ^1.8.3

Run flutter pub get to install the dependencies.

Your packages have arrived! Much faster than most food delivery services

Creating the Database Helper

Now, let's create a database helper class to manage all our SQLite operations. This will be our trusty sidekick throughout the journey!

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

class DatabaseHelper {
  static final DatabaseHelper _instance = DatabaseHelper._internal();
  static DatabaseHelper get instance => _instance;

  // Singleton pattern
  DatabaseHelper._internal();

  static Database? _database;

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

  Future<Database> _initDatabase() async {
    // Get the path to the database file
    String path = join(await getDatabasesPath(), 'my_app_database.db');

    // Open/create the database
    return await openDatabase(
      path,
      version: 1,
      onCreate: _createDb,
    );
  }

  Future<void> _createDb(Database db, int version) async {
    // Create the tables
    await db.execute('''
      CREATE TABLE tasks(
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        title TEXT NOT NULL,
        description TEXT,
        isCompleted INTEGER NOT NULL DEFAULT 0,
        createdAt TEXT NOT NULL
      )
    ''');
  }
}

Phew! That was quite a bit of code. Let's break down what's happening:

We're using the Singleton pattern to ensure we only have one database instance.
_initDatabase() gets the path for our database file and opens/creates it.
_createDb() is called when the database is first created, setting up our table structure.

Defining a Data Model

Now we need a data model to represent our table records. This is like creating a blueprint for our data:

class Task {
  final int? id;
  final String title;
  final String? description;
  final bool isCompleted;
  final DateTime createdAt;

  Task({
    this.id,
    required this.title,
    this.description,
    this.isCompleted = false,
    required this.createdAt,
  });

  // Convert Task to Map for database operations
  Map<String, dynamic> toMap() {
    return {
      'id': id,
      'title': title,
      'description': description,
      'isCompleted': isCompleted ? 1 : 0,
      'createdAt': createdAt.toIso8601String(),
    };
  }

  // Create Task from Map (from database)
  factory Task.fromMap(Map<String, dynamic> map) {
    return Task(
      id: map['id'],
      title: map['title'],
      description: map['description'],
      isCompleted: map['isCompleted'] == 1,
      createdAt: DateTime.parse(map['createdAt']),
    );
  }

  // For debugging
  @override
  String toString() {
    return 'Task(id: $id, title: $title, isCompleted: $isCompleted)';
  }
}

Our Task class has:

Properties that match our table columns
A toMap() method to convert the object to a database-friendly format
A fromMap() factory to create objects from database results

CRUD Operations

Now for the exciting part! Let's add the CRUD (Create, Read, Update, Delete) operations to our DatabaseHelper class:

// Add these methods to your DatabaseHelper class

// CREATE - Insert a new task
Future<int> insertTask(Task task) async {
  Database db = await instance.database;
  return await db.insert('tasks', task.toMap());
}

// READ - Get all tasks
Future<List<Task>> getAllTasks() async {
  Database db = await instance.database;
  List<Map<String, dynamic>> maps = await db.query('tasks');

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

// READ - Get task by ID
Future<Task?> getTask(int id) async {
  Database db = await instance.database;
  List<Map<String, dynamic>> maps = await db.query(
    'tasks',
    where: 'id = ?',
    whereArgs: [id],
  );

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

// UPDATE - Update a task
Future<int> updateTask(Task task) async {
  Database db = await instance.database;
  return await db.update(
    'tasks',
    task.toMap(),
    where: 'id = ?',
    whereArgs: [task.id],
  );
}

// DELETE - Delete a task
Future<int> deleteTask(int id) async {
  Database db = await instance.database;
  return await db.delete(
    'tasks',
    where: 'id = ?',
    whereArgs: [id],
  );
}

// DELETE - Delete all tasks
Future<int> deleteAllTasks() async {
  Database db = await instance.database;
  return await db.delete('tasks');
}

Look at all those magical methods! Each one serves a specific purpose:

insertTask() adds a new task to the database
getAllTasks() retrieves all tasks from the database
getTask() finds a specific task by ID
updateTask() updates an existing task
deleteTask() removes a task by ID
deleteAllTasks() clears all tasks (for those really bad days)

Using the Database in Your App

Now let's see how to use our database helper in a Flutter app:

import 'package:flutter/material.dart';
import 'database_helper.dart';
import 'task.dart';

class TaskListScreen extends StatefulWidget {
  @override
  _TaskListScreenState createState() => _TaskListScreenState();
}

class _TaskListScreenState extends State<TaskListScreen> {
  final dbHelper = DatabaseHelper.instance;
  List<Task> tasks = [];
  final titleController = TextEditingController();
  final descriptionController = TextEditingController();

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

  _refreshTaskList() async {
    List<Task> taskList = await dbHelper.getAllTasks();
    setState(() {
      tasks = taskList;
    });
  }

  _addTask() async {
    if (titleController.text.isEmpty) return;

    await dbHelper.insertTask(Task(
      title: titleController.text,
      description: descriptionController.text,
      createdAt: DateTime.now(),
    ));

    titleController.clear();
    descriptionController.clear();
    _refreshTaskList();
  }

  _toggleTaskStatus(Task task) async {
    Task updatedTask = Task(
      id: task.id,
      title: task.title,
      description: task.description,
      isCompleted: !task.isCompleted,
      createdAt: task.createdAt,
    );

    await dbHelper.updateTask(updatedTask);
    _refreshTaskList();
  }

  _deleteTask(int id) async {
    await dbHelper.deleteTask(id);
    _refreshTaskList();
  }

  @override
  Widget build(BuildContext context) {
    return Scaffold(
      appBar: AppBar(
        title: Text('SQLite Task Manager'),
      ),
      body: Column(
        children: [
          Padding(
            padding: const EdgeInsets.all(8.0),
            child: Column(
              children: [
                TextField(
                  controller: titleController,
                  decoration: InputDecoration(
                    labelText: 'Task Title',
                    border: OutlineInputBorder(),
                  ),
                ),
                SizedBox(height: 8.0),
                TextField(
                  controller: descriptionController,
                  decoration: InputDecoration(
                    labelText: 'Description (Optional)',
                    border: OutlineInputBorder(),
                  ),
                ),
                SizedBox(height: 8.0),
                ElevatedButton(
                  onPressed: _addTask,
                  child: Text('Add Task'),
                ),
              ],
            ),
          ),
          Expanded(
            child: ListView.builder(
              itemCount: tasks.length,
              itemBuilder: (context, index) {
                Task task = tasks[index];
                return ListTile(
                  title: Text(
                    task.title,
                    style: TextStyle(
                      decoration: task.isCompleted
                          ? TextDecoration.lineThrough
                          : null,
                    ),
                  ),
                  subtitle: task.description != null
                      ? Text(task.description!)
                      : null,
                  trailing: Row(
                    mainAxisSize: MainAxisSize.min,
                    children: [
                      IconButton(
                        icon: Icon(
                          task.isCompleted
                              ? Icons.check_box
                              : Icons.check_box_outline_blank,
                        ),
                        onPressed: () => _toggleTaskStatus(task),
                      ),
                      IconButton(
                        icon: Icon(Icons.delete),
                        onPressed: () => _deleteTask(task.id!),
                      ),
                    ],
                  ),
                );
              },
            ),
          ),
        ],
      ),
    );
  }
}

This screen allows users to:

Add new tasks with title and description
View all tasks in a list
Mark tasks as completed/incomplete
Delete tasks

Best Practices & Common Pitfalls

1. Use Transactions for Multiple Operations

If you need to perform multiple database operations that should succeed or fail together, use transactions:

Future<void> bulkInsertTasks(List<Task> tasks) async {
  final db = await instance.database;
  await db.transaction((txn) async {
    for (var task in tasks) {
      await txn.insert('tasks', task.toMap());
    }
  });
}

2. Close the Database When Done

Although SQLite databases in Flutter are automatically closed when the app is terminated, it's good practice to close them explicitly when you're sure you won't need them anymore:

Future<void> closeDatabase() async {
  final db = await instance.database;
  await db.close();
  _database = null;
}

3. Handle Database Migrations

When your app evolves, you'll need to update your database schema. Handle this with database versioning:

Future<Database> _initDatabase() async {
  String path = join(await getDatabasesPath(), 'my_app_database.db');

  return await openDatabase(
    path,
    version: 2, // Increased version number
    onCreate: _createDb,
    onUpgrade: _upgradeDb,
  );
}

Future<void> _upgradeDb(Database db, int oldVersion, int newVersion) async {
  if (oldVersion < 2) {
    // Add a new column to the tasks table
    await db.execute('ALTER TABLE tasks ADD COLUMN priority INTEGER DEFAULT 0');
  }
}

How I feel when migrating a production database

4. Use Parameterized Queries

Always use parameterized queries to prevent SQL injection:

// DON'T DO THIS:
String title = "User Input";
await db.rawQuery("SELECT * FROM tasks WHERE title = '$title'");

// DO THIS INSTEAD:
await db.rawQuery("SELECT * FROM tasks WHERE title = ?", [title]);

5. Handle Errors Gracefully

Wrap your database operations in try-catch blocks to handle exceptions:

Future<void> safeInsertTask(Task task) async {
  try {
    await insertTask(task);
  } catch (e) {
    print('Error inserting task: $e');
    // Handle the error or show a user-friendly message
  }
}

Conclusion

Congratulations, brave developer! You've completed your SQLite adventure in Flutter. You now have the power to create, read, update, and delete data with confidence. Your users will thank you for preserving their precious data even when the app is closed.

Remember, with great database power comes great responsibility. Use transactions, handle migrations properly, and always sanitize your inputs.

Happy coding! 🚀

You after successfully implementing SQLite in your Flutter app

DEV Community