Complete SQLite CRUD operations in Flutter

Complete SQLite CRUD operations in Flutter

Are you a Flutter developer looking for a reliable way to handle local data storage? Look no further! SQLite is here to simplify your life.

What is SQLite?

• SQLite is a C-language library that serves as a self-contained, high-reliability SQL database engine.
• Most Used: SQLite stands as one of the most widely used database engines worldwide, admired for its efficiency and versatility.
• Prevalence: It’s not just for developers; SQLite is built into countless mobile phones, computers, and everyday applications that we use, making it an integral part of our digital lives.

Why Choose SQLite?

• Performance: Sqflite offers high-performance local data storage, making it suitable for apps of all sizes.
• Simplicity: With a straightforward API, you can quickly create, read, update, and delete records.
• Cross-Platform: Flutter is all about cross-platform development, and Sqflite follows suit, supporting both Android and iOS.

Well-Known Users of SQLite

SQLite boasts a widespread user base, including tech giants like Google and Apple. Its simplicity and efficiency make it a trusted choice for various applications. Explore the list of famous users on SQLite’s website.

What is Sqflite?

Sqflite is a Flutter package that provides a simple and efficient way to work with SQLite databases in your Flutter applications. It’s lightweight, easy to use, and perfect for managing your local data. Everything in this package is asynchronous.

Get Started with Sqflite Today!

Start harnessing the potential of Sqflite today, whether you’re crafting a minimalist to-do list or embarking on a feature-rich mobile app journey. Sqflite is your key to efficient and robust local data storage in Flutter!

Fully functional app built with SQLite integration.

To integrate SQLite functionality in your Flutter project, follow these steps:

1. Add dependency by open thepubspec.yaml file in your project directory.

dependencies:   
 flutter:     
  sdk: flutter   

 # The following adds the Cupertino Icons font to your application.   
 # Use with the CupertinoIcons class for iOS style icons.   
 cupertino_icons: ^0.1.2  

 # Dependencies of SQLITE
 sqflite: ^2.2.0+3 # SQLite Plugin Link https://pub.dev/packages/sqflite
 path: ^1.8.0 # Path Plugin Link https://pub.dev/packages/path
 path_provider: ^2.1.1 # Path Provider Plugin Link https://pub.dev/packages/path_provider

• sqflite: This package provides SQLite database functionality for your Flutter app.
• path_provider: This package allows you to specify the path to the database on your device.

3. Or use command to add the packages as a dependency, run flutter pub add

flutter pub add sqflite path path-provider

2. After adding these dependencies, run flutter pub get and flutter pub upgrade to fetch and install the packages.

Let’s structure a simple Flutter application with Sqflite using an Object-Oriented Programming (OOP) approach. We’ll create a class to encapsulate database operations and model classes for our data.

Create Model Class

• Create a new file inside the ‘lib’ folder.
• In that file, create a model class with the required properties.
• Implement a `toJson` method within the model class to map its properties into JSON format.
• Implement a `fromJson` method to convert JSON data fetched from the database into an instance of the Model class.
• Both the `toJson` and `fromJson` methods are user-defined and customized to handle the specific data conversion requirements.

class NoteModel {
  // Define class properties
  int? id; // User ID
  String? title; // User name
  String? description; // User email

  // Constructor with optional 'id' parameter
  NoteModel(this.title, this.description, {this.id});

  // Convert a Note into a Map. The keys must correspond to the names of the
  // columns in the database.
  NoteModel.fromJson(Map<String, dynamic> map) {
    id = map['id'];
    title = map['title'];
    description = map['description'];
  }

// Method to convert a 'NoteModel' to a map
  Map<String, dynamic> toJson() {
    return {
      'title': title,
      'description': description,
    };
  }
}

Create Database Method

• Adding the imports: add the required imports to the database helper file.

// Import the plugins Path provider and SQLite.
import 'package:sqflite/sqflite.dart';
import 'package:path_provider/path_provider.dart';
import 'package:path/path.dart';

• Open the database: To open the database we use openDatabase the method takes the path and returns a Database object.
• Initialize the database: declare a function which uses the above imports to open a database connection.

_initDatabase() async {
  io.Directory documentsDirectory = await getApplicationDocumentsDirectory();

  // Set the path to the database. Note: Using the `join` function from the
  // `path` package is best practice to ensure the path is correctly
  // constructed for each platform.
  String path = join(documentsDirectory.path, databaseName);
  // When the database is first created, create a table to store Notes.
  var db =
      await openDatabase(path, version: versionNumber, onCreate: _onCreate);
  return db;
}

// Run the CREATE TABLE statement on the database.
_onCreate(Database db, int intVersion) async {
  await db.execute("CREATE TABLE IF NOT EXISTS $tableNotes ("
      " $colId INTEGER PRIMARY KEY AUTOINCREMENT, "
      " $colTitle TEXT NOT NULL, "
      " $colDescription TEXT"
      ")");
}

CRUD Operations

Perform CRUD (Create, Read, Update, Delete) operations on your database.

1. Create

• To perform operations on a database, the database must be opened.
• After the database is open, the returned database object can be utilized for a range of operations.
• When it comes to data insertion, the insert method is employed.
• Data insertion typically involves structuring the data in JSON format.

// Define a function that inserts notes into the database
Future<void> insert(NoteModel note) async {
  // Get a reference to the database.
  final db = await database;

  // Insert the Note into the correct table. You might also specify the
  // `conflictAlgorithm` to use in case the same Note is inserted twice.
  //
  // In this case, replace any previous data.
  await db.insert(tableNotes, note.toJson(),
      conflictAlgorithm: ConflictAlgorithm.replace);
}

2. Read

• With sqflite, data querying offers various options using arguments like where, groupBy, having, orderBy, and columns within the query() helper.

// A method that retrieves all the notes from the Notes table.
Future<List<NoteModel>> getAll() async {
  // Get a reference to the database.
  final db = await database;

  // Query the table for all The Notes. {SELECT * FROM Notes ORDER BY Id ASC}
  final result = await db.query(tableNotes, orderBy: '$colId ASC');

  // Convert the List<Map<String, dynamic> into a List<Note>.
  return result.map((json) => NoteModel.fromJson(json)).toList();
}

3. Update

• Use update() helper to update any record in the database. To update specific records, use the where argument.

// Define a function to update a note
Future<int> update(NoteModel note) async {
  // Get a reference to the database.
  final db = await database;

  // Update the given Note.
  var res = await db.update(tableNotes, note.toJson(),
      // Ensure that the Note has a matching id.
      where: '$colId = ?',
      // Pass the Note's id as a whereArg to prevent SQL injection.
      whereArgs: [note.id]);
  return res;
}

4. Delete

• Use the where argument in delete() helper to delete specific rows from the table.
• Remember to use whereArgs to pass arguments to where statement in order to prevent SQL injection attacks.

// Define a function to delete a note
Future<void> delete(int id) async {
  // Get a reference to the database.
  final db = await database;
  try {
    // Remove the Note from the database.
    await db.delete(tableNotes,
        // Use a `where` clause to delete a specific Note.
        where: "$colId = ?",
        // Pass the Dog's id as a whereArg to prevent SQL injection.
        whereArgs: [id]);
  } catch (err) {
    debugPrint("Something went wrong when deleting an item: $err");
  }
}

Always use whereArgs in Sqflite to prevent SQL injection attacks. This safeguards your Flutter application by ensuring proper sanitation of user inputs and data passed to the where statement.