Flutter SQLite CRUD Ultimate Tutorial [2024]

December 4, 2023June 9, 2023 by IJ Mobile Lab

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.

Table of Contents

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

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

   flutter create flutter_sqlite_crud

Change your working directory to the project folder:

   cd flutter_sqlite_crud

Open the project in your preferred code editor.

Step 2: Add Dependencies

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

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

   flutter pub get

Step 3: Create Database Helper Class

Create a new file called database_helper.dart inside the lib folder.
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

Create a new file called note.dart inside the lib folder.
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

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

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

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.

5 thoughts on “Flutter SQLite CRUD Ultimate Tutorial [2024]”

Moonga

October 1, 2023 at 2:59 pm

Hi, I followed the steps exactly as described. However, when I run the application the only widgets displaying are the Floating action button and the title in the AppBar. Everything else in the body is not appearing on the screen. Kindly help me trouble shoot the problem that would be super helpful. Thank you in advance.
Reply
Júlio D. C. Conway

October 29, 2023 at 5:37 pm

Hi, I followed the steps exactly as described. However, when I run the application the following error apear:
Performing hot reload…
Syncing files to device Android SDK built for x86…
Reloaded 3 of 1037 libraries in 634ms (compile: 36 ms, reload: 220 ms, reassemble: 243 ms).
I/flutter (14888): Entro em _addNote
E/SQLiteLog(14888): (1) table notes has no column named id
E/flutter (14888): [ERROR:flutter/runtime/dart_vm_initializer.cc(41)] Unhandled Exception: DatabaseException(table notes has no column named id (code 1 SQLITE_ERROR): , while compiling: INSERT INTO notes (id, name, description) VALUES (NULL, ?, ?)) sql ‘INSERT INTO notes (id, name, description) VALUES (NULL, ?, ?)’ args [New Note, Description]
E/flutter (14888): #0 wrapDatabaseException (package:sqflite/src/exception_impl.dart:11:7)
E/flutter (14888):
E/flutter (14888): #1 SqfliteDatabaseMixin.txnRawInsert. (package:sqflite_common/src/database_mixin.dart:549:14)
E/flutter (14888):
E/flutter (14888): #2 BasicLock.synchronized (package:synchronized/src/basic_lock.dart:33:16)
E/flutter (14888):
E/flutter (14888): #3 SqfliteDatabaseMixin.txnSynchronized (package:sqflite_common/src/database_mixin.dart:490:14)
E/flutter (14888):
E/flutter (14888): #4 DatabaseHelper.insert (package:flutter_sqlite_crud/database_helper.dart:55:12)
E/flutter (14888):
E/flutter (14888): #5 _MyHomePageState._addNote (package:flutter_sqlite_crud/main.dart:52:14)
E/flutter (14888):
E/flutter (14888):

can you help me please.
Reply
Asif

November 10, 2023 at 9:38 am

The code does not work. Please upgrade it and make it work.
Reply
- Ali Mobin Memon
  
  November 15, 2023 at 8:39 am
  
  Those who are facing issue here is solution.
  
  He is using id in model but in db table he has written _id with underscore as prefix so resolve it like the below note.dart model code
  
  class Note {
  int? id;
  String name;
  String description;
  
  Note({
  this.id,
  required this.name,
  required this.description,
  });
  
  Map toMap() {
  return {
  ‘_id’: id,
  ‘name’: name,
  ‘description’: description,
  };
  }
  
  factory Note.fromMap(Map map) {
  return Note(
  id: map[‘id’],
  name: map[‘name’],
  description: map[‘description’],
  );
  }
  }
  
  Then after this.
  
  in main.dart file add this UI
  
  import ‘package:flutter/material.dart’;
  import ‘package:untitled3/database_helper.dart’;
  import ‘package:untitled3/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 {
  final dbHelper = DatabaseHelper.instance;
  List _notes = [];
  
  @override
  void initState() {
  super.initState();
  _loadNotes();
  }
  
  void _loadNotes() async {
  List 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(index);
  setState(() {
  _notes.removeAt(index);
  });
  }
  
  @override
  Widget build(BuildContext context) {
  return Scaffold(
  appBar: AppBar(
  title: const 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: const Icon(Icons.add),
  onPressed: () {
  _addNote();
  },
  ),
  );
  }
  }
  
  Furthermore,
  
  the delete function is wrong. as its using index for searching in db so replace the delete method like this
  
  IconButton(
  icon: Icon(Icons.delete),
  onPressed: () {
  _deleteNote(index, _notes[index].id!);
  },
  ),
  
  and the main declaration of function as below.
  
  void _deleteNote(int index, int? id) async {
  await dbHelper.delete(id!);
  setState(() {
  _notes.removeAt(index);
  });
  }
  Reply
Ali Mobin Memon

November 15, 2023 at 8:38 am

Those who are facing issue here is solution. The writer of article is lazy person doesnt even know himself what is right and wrong.

He is using id in model but in db table he has written _id with underscore as prefix so resolve it like the below note.dart model code

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

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

Map toMap() {
return {
‘_id’: id,
‘name’: name,
‘description’: description,
};
}

factory Note.fromMap(Map map) {
return Note(
id: map[‘id’],
name: map[‘name’],
description: map[‘description’],
);
}
}

Then after this.

in main.dart file add this UI

import ‘package:flutter/material.dart’;
import ‘package:untitled3/database_helper.dart’;
import ‘package:untitled3/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 {
final dbHelper = DatabaseHelper.instance;
List _notes = [];

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

void _loadNotes() async {
List 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(index);
setState(() {
_notes.removeAt(index);
});
}

@override
Widget build(BuildContext context) {
return Scaffold(
appBar: AppBar(
title: const 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: const Icon(Icons.add),
onPressed: () {
_addNote();
},
),
);
}
}

Furthermore,

the delete function is wrong. as its using index for searching in db so replace the delete method like this

IconButton(
icon: Icon(Icons.delete),
onPressed: () {
_deleteNote(index, _notes[index].id!);
},
),

and the main declaration of function as below.

void _deleteNote(int index, int? id) async {
await dbHelper.delete(id!);
setState(() {
_notes.removeAt(index);
});
}
Reply