Flutter SQLite CRUD Ultimate Tutorial [2024]

In this article, we will share with you the Flutter SQLite CRUD real example note-taking app using sqflite flutter package. We will build a simple note-taking app where users can add, view, edit, and delete notes. By following this tutorial, you will gain a solid understanding of how to implement SQLite database operations in Flutter.

Prerequisites:

To follow along with this tutorial, you should have a basic understanding of Flutter and have Flutter and Dart installed on your system. Familiarity with basic concepts like widgets and state management in Flutter will be beneficial.

How To Create Flutter SQLite CRUD Application

Step 1: Set Up Flutter Project

1. Create a new Flutter project by running the following command in your terminal:

   flutter create flutter_sqlite_crud

2. Change your working directory to the project folder:

   cd flutter_sqlite_crud

3. Open the project in your preferred code editor.

Step 2: Add Dependencies

1. Open the pubspec.yaml file and add the following dependencies under the dependencies section:

   dependencies:
     flutter:
       sdk: flutter
     sqflite: ^2.0.0
     path: ^2.0.0

2. Save the file and run the following command to fetch the dependencies:

   flutter pub get

Step 3: Create Database Helper Class

1. Create a new file called database_helper.dart inside the lib folder.
2. Add the following code to the database_helper.dart file:

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

   class DatabaseHelper {
     static final _databaseName = "crud.db";
     static final _databaseVersion = 1;

     static final table = 'notes';

     static final columnId = '_id';
     static final columnName = 'name';
     static final columnDescription = 'description';

     // Make this a singleton class
     DatabaseHelper._privateConstructor();
     static final DatabaseHelper instance = DatabaseHelper._privateConstructor();

     // Only have a single app-wide reference to the database
     static Database? _database;
     Future<Database> get database async {
       if (_database != null) return _database!;

       // Lazily instantiate the database if unavailable
       _database = await _initDatabase();
       return _database!;
     }

     // Open the database, creating if it doesn't exist
     _initDatabase() async {
       Directory documentsDirectory = await getApplicationDocumentsDirectory();
       String path = join(documentsDirectory.path, _databaseName);
       return await openDatabase(path,
           version: _databaseVersion, onCreate: _onCreate);
     }

     // SQL code to create the database table
     Future _onCreate(Database db, int version) async {
       await db.execute('''
           CREATE TABLE $table (
             $columnId INTEGER PRIMARY KEY,
             $columnName TEXT NOT NULL,
             $columnDescription TEXT NOT NULL
           )
           ''');
     }
   }

Step 4: Create Model Class

1. Create a new file called note.dart inside the lib folder.
2. Add the following code to the note.dart file:

   class Note {
     int? id;
     String name;
     String description;

     Note({
       this.id,
       required this.name,
       required this.description,
     });

     Map<String, dynamic> toMap() {
       return {
         'id': id,
         'name': name,
         'description': description,
       };
     }

     factory Note.fromMap(Map<String, dynamic> map) {
       return Note(
         id: map['id'],
         name: map['name'],
         description: map['description'],
       );
     }
   }

Step 5: Implement CRUD Operations

1. Open the database_helper.dart file and add the following methods to the DatabaseHelper class:

   // Insert a note into the database
   Future<int> insert(Note note) async {
     Database db = await instance.database;
     return await db.insert(table, note.toMap());
   }

   // Retrieve all notes from the database
   Future<List<Note>> getAllNotes() async {
     Database db = await instance.database;
     List<Map<String, dynamic>> maps = await db.query(table);
     return List.generate(maps.length, (i) {
       return Note.fromMap(maps[i]);
     });
   }

   // Update a note in the database
   Future<int> update(Note note) async {
     Database db = await instance.database;
     return await db.update(table, note.toMap(),
         where: '$columnId = ?', whereArgs: [note.id]);
   }

   // Delete a note from the database
   Future<int> delete(int id) async {
     Database db = await instance.database;
     return await db.delete(table, where: '$columnId = ?', whereArgs: [id]);
   }

Step 6: Implement Flutter UI

1. Open the main.dart file and replace the default code with the following code:

   import 'package:flutter/material.dart';
   import 'package:flutter_sqlite_crud/database_helper.dart';
   import 'package:flutter_sqlite_crud/note.dart';

   void main() {
     runApp(MyApp());
   }

   class MyApp extends StatelessWidget {
     @override
     Widget build(BuildContext context) {
       return MaterialApp(
         title: 'Flutter SQLite CRUD',
         theme: ThemeData(
           primarySwatch: Colors.blue,
         ),
         home: MyHomePage(),
       );
     }
   }

   class MyHomePage extends StatefulWidget {
     @override
     _MyHomePageState createState() => _MyHomePageState();
   }

   class _MyHomePageState extends State<MyHomePage> {
     final dbHelper = DatabaseHelper.instance;
     List<Note> _notes = [];

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

     void _loadNotes() async {
       List<Note> notes = await dbHelper.getAllNotes();
       setState(() {
         _notes = notes;
       });
     }

     void _addNote() async {
       Note newNote = Note(
         name: 'New Note',
         description: 'Description',
       );
       int id = await dbHelper.insert(newNote);
       setState(() {
         newNote.id = id;
         _notes.add(newNote);
       });
     }

     void _updateNote(int index) async {
       Note updatedNote = Note(
         id: _notes[index].id,
         name: 'Updated Note',
         description: 'Updated Description',
       );
       await dbHelper.update(updatedNote);
       setState(() {
         _notes[index] = updatedNote;
       });
     }

     void _deleteNote(int index) async {
       await dbHelper.delete(_notes[index].id!);
       setState(() {
         _notes.removeAt(index);
       });
     }

     @override
     Widget build(BuildContext context) {
       return Scaffold(
         appBar: AppBar(
           title: Text('Flutter SQLite CRUD'),
         ),
         body: ListView.builder(
           itemCount: _notes.length,
           itemBuilder: (context, index) {
             return ListTile(
               title: Text(_notes[index].name),
               subtitle: Text(_notes[index].description),
               trailing: Row(
                 mainAxisSize: MainAxisSize.min,
                 children: [
                   IconButton(
                     icon: Icon(Icons.edit),
                     onPressed: () {
                       _updateNote(index);
                     },
                   ),
                   IconButton

(
                     icon: Icon(Icons.delete),
                     onPressed: () {
                       _deleteNote(index);
                     },
                   ),
                 ],
               ),
             );
           },
         ),
         floatingActionButton: FloatingActionButton(
           child: Icon(Icons.add),
           onPressed: () {
             _addNote();
           },
         ),
       );
     }
   }

Step 7: Run the Application

1. Save all the files and run the application using the following command:

   flutter run

We also write a tutorial on the Quotes Book app

Conclusion:

In this tutorial, you have learned how to create a Flutter SQLite CRUD application. By following the steps outlined, you successfully built a note-taking app that allows users to manage their notes using SQLite for data persistence. This tutorial provides a solid foundation for working with SQLite databases in Flutter and can be extended to build more complex applications with CRUD functionality. Now you can apply this knowledge to develop your own Flutter apps that require local data storage and management.