How to use SQLite/Sqflite CRUD on Flutter App [Easiest Guide Example]

In this example, we are going to show you the easiest way to store (CRUD, create, update, read, delete) your data on SQLite/SQflite database. We are going to use an embedded SQLite database engine to store databases in Flutter Android and iOS. See the example below for more details:

Note: This will not work on Linux and Windows due to Path, we will post another guide article to use SQLite on macOS, Linux, and Windows.

First, you need to add path and sqflite flutter packages to your dependency by adding the following lines on pubspec.yaml file.

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

How to Initialize Database and Create Table:

// Get a location using getDatabasesPath
var databasesPath = await getDatabasesPath();
String path = join(databasesPath, 'demo.db');
//join is from path package
print(path); //output /data/user/0/com.testapp.flutter.testapp/databases/demo.db

Database db = await openDatabase(path, version: 1,
    onCreate: (Database db, int version) async {
      // When creating the db, create the table
      await db.execute('''

            CREATE TABLE IF NOT EXISTS students( 
                  id primary key,
                  name varchar(255) not null,
                  roll_no int not null,
                  address varchar(255) not null
              );

              //create more table here
          
          ''');
    //table students will be created if there is no table 'students'
    print("Table Created");
});

How to Add New Data to Table:

db.rawInsert("INSERT INTO students (name, roll_no, address) VALUES (?, ?, ?);", ["Flutter", 12, "Nepal"]); 
//add student from form to database

How to Read Data From Table:

List<Map> slist = await db.rawQuery('SELECT * FROM students');
slist.map((stuone){
    print(stuone["name"]);
    print(stuone["roll_no"]);
    print(stuone["address"]);
});

How to Filter Data on "SELECT" form Table:

List<Map> slist = await db.rawQuery('SELECT * FROM students WHERE roll_no = ?', [12]);
if(slist.length > 0){
   var data = slist.first;
   print(data["name"]);
   print(data["roll_no"]);
   print(data["address"]);
}else{
  print("NO any student with roll no 12");
}

How to Update Data on Table:

db.rawInsert("UPDATE students SET name = ?, roll_no = ?, address = ? WHERE roll_no = ?", ["New Name", 23, "New Address",12]);
//update students table with roll_no = 12

How to Delete Data From Table:

db.rawDelete("DELETE FROM students WHERE roll_no = ?", [12]);
//delete student data with roll no = 12

Futter App Example:

Now, we are going to show you the way to use these codes in the real projects. See the example code below for details:

In our project lib/ folder we have five files:

1. main.dart //main menu
2. db.dart //Class to control database
3. add_student.dart (Add New Student)
4. edit_student.dart (Edit student data with new data from form)
5. list_students.dart (List All Students from table)

Download this Example Project.

1. main.dart

import 'package:flutter/material.dart';
import 'package:testapp/add_student.dart';
import 'package:testapp/list_students.dart';

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

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

class Home extends StatefulWidget {
  @override
  _HomeState createState() => _HomeState();
}

class _HomeState extends State<Home> {
  @override
  Widget build(BuildContext context) {
     return Scaffold(
          appBar: AppBar(
             title: Text("Sqlite and Sqflite"),
          ),
          body: Container( 
            alignment: Alignment.topCenter,
            padding: EdgeInsets.all(20),
            child: Column(
                children:[

                    ElevatedButton(
                      onPressed: (){
                            Navigator.push(context, MaterialPageRoute(builder: (BuildContext context){
                                return AddStudent();
                            }));
                      },
                      child: Text("Add Student"),
                    ),

                    ElevatedButton(
                      onPressed: (){
                            Navigator.push(context, MaterialPageRoute(builder: (BuildContext context){
                                return ListStudents();
                            }));
                      },
                      child: Text("List Student Record"),
                    ),

                ]
            ),
          ),
     );
  }
 
}

Output:

2. db.dart

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

class MyDb{
   late Database db;

   Future open() async {
      // Get a location using getDatabasesPath
      var databasesPath = await getDatabasesPath();
      String path = join(databasesPath, 'demo.db');
      //join is from path package
      print(path); //output /data/user/0/com.testapp.flutter.testapp/databases/demo.db

      Database db = await openDatabase(path, version: 1,
          onCreate: (Database db, int version) async {
            // When creating the db, create the table
            await db.execute('''

                  CREATE TABLE IF NOT EXISTS students( 
                        id primary key,
                        name varchar(255) not null,
                        roll_no int not null,
                        address varchar(255) not null
                    );

                    //create more table here
                
                ''');
          //table students will be created if there is no table 'students'
          print("Table Created");
      });
   }

  Future<Map<dynamic, dynamic>?> getStudent(int rollno) async {
    List<Map> maps = await db.query('students',
        where: 'roll_no = ?',
        whereArgs: [rollno]);
    //getting student data with roll no.
    if (maps.length > 0) {
       return maps.first;
    }
    return null;
  }
}

3. add_student.dart

import 'package:flutter/cupertino.dart';
import 'package:flutter/material.dart';
import 'package:testapp/db.dart';

class AddStudent extends StatefulWidget{
  @override
  State<StatefulWidget> createState() {
      return _AddStudent();
  }
}

class _AddStudent extends State<AddStudent>{

  TextEditingController name = TextEditingController();
  TextEditingController rollno = TextEditingController();
  TextEditingController address = TextEditingController();
  //test editing controllers for form

  MyDb mydb = new MyDb(); //mydb new object from db.dart

  @override
  void initState() {
    mydb.open(); //initilization database
    super.initState();
  }


  @override
  Widget build(BuildContext context) {
       return Scaffold(
            appBar: AppBar(
              title: Text("Add Student"),
            ),
            body:Container( 
               padding: EdgeInsets.all(30),
               child: Column(children: [
                   TextField(
                     controller: name,
                     decoration: InputDecoration(
                        hintText: "Stuent Name",
                     ),
                   ),

                   TextField(
                     controller: rollno,
                     decoration: InputDecoration(
                        hintText: "Roll No.",
                     ),
                   ),

                   TextField(
                     controller: address,
                     decoration: InputDecoration(
                        hintText: "Address:",
                     ),
                   ),

                   ElevatedButton(onPressed: (){

                         mydb.db.rawInsert("INSERT INTO students (name, roll_no, address) VALUES (?, ?, ?);",
                         [name.text, rollno.text, address.text]); //add student from form to database

                         ScaffoldMessenger.of(context).showSnackBar(SnackBar(content: Text("New Student Added")));
                         //show snackbar message after adding student

                         name.text = "";
                         rollno.text = "";
                         address.text = "";
                         //clear form to empty after adding data

                   }, child: Text("Save Student Data")),
               ],),
            )
       );
  }
}

Output:

4. edit_student.dart

import 'package:flutter/cupertino.dart';
import 'package:flutter/material.dart';

import 'db.dart';

class EditStudent extends StatefulWidget{

  int rollno;
  EditStudent({required this.rollno}); //constructor for class

  @override
  State<StatefulWidget> createState() {
    return _EditStudent();
  }
}

class _EditStudent extends State<EditStudent>{
  
  TextEditingController name = TextEditingController();
  TextEditingController rollno = TextEditingController();
  TextEditingController address = TextEditingController();

  MyDb mydb = new MyDb();

  @override
  void initState() {
    mydb.open();

    Future.delayed(Duration(milliseconds: 500), () async {
        var data = await mydb.getStudent(widget.rollno); //widget.rollno is passed paramater to this class
        if(data != null){
            name.text = data["name"];
            rollno.text = data["roll_no"].toString();
            address.text = data["address"];
            setState(() {});
        }else{
           print("No any data with roll no: " + widget.rollno.toString());
        }
    });
    super.initState();
  }


  @override
  Widget build(BuildContext context) {
    return Scaffold(
            appBar: AppBar(
              title: Text("Edit Student"),
            ),
            body:Container( 
               padding: EdgeInsets.all(30),
               child: Column(children: [
                   TextField(
                     controller: name,
                     decoration: InputDecoration(
                        hintText: "Stuent Name",
                     ),
                   ),

                   TextField(
                     controller: rollno,
                     decoration: InputDecoration(
                        hintText: "Roll No.",
                     ),
                   ),

                   TextField(
                     controller: address,
                     decoration: InputDecoration(
                        hintText: "Address:",
                     ),
                   ),

                   ElevatedButton(onPressed: (){
                         mydb.db.rawInsert("UPDATE students SET name = ?, roll_no = ?, address = ? WHERE roll_no = ?",
                         [name.text, rollno.text, address.text, widget.rollno]);
                         //update table with roll no.
                         ScaffoldMessenger.of(context).showSnackBar(SnackBar(content: Text("Student Data Updated")));

                   }, child: Text("Update Student Data")),
               ],),
            )
       );
  }

}

Output:

5. list_students.dart

import 'package:flutter/cupertino.dart';
import 'package:flutter/material.dart';
import 'package:testapp/db.dart';
import 'package:testapp/edit_student.dart';

class ListStudents extends StatefulWidget{
  @override
  State<StatefulWidget> createState() {
     return _ListStudents();
  }

}

class _ListStudents extends State<ListStudents>{

  List<Map> slist = [];
  MyDb mydb = new MyDb();

  @override
  void initState() {
    mydb.open();
    getdata();
    super.initState();
  }

  getdata(){
    Future.delayed(Duration(milliseconds: 500),() async {
      //use delay min 500 ms, because database takes time to initilize.
        slist = await mydb.db.rawQuery('SELECT * FROM students');
        
        setState(() { }); //refresh UI after getting data from table.
    });
  }

  @override
  Widget build(BuildContext context) {
     return Scaffold(
        appBar: AppBar(
           title: Text("List of Students"),
        ),
        body: SingleChildScrollView(
          child: Container(
             child: slist.length == 0?Text("No any students to show."): //show message if there is no any student
             Column(  //or populate list to Column children if there is student data.
                children: slist.map((stuone){
                     return Card(
                       child: ListTile(
                          leading: Icon(Icons.people),
                          title: Text(stuone["name"]),
                          subtitle: Text("Roll No:" + stuone["roll_no"].toString() + ", Add: " + stuone["address"]),
                          trailing: Wrap(children: [

                              IconButton(onPressed: (){
                                Navigator.push(context, MaterialPageRoute(builder: (BuildContext context){
                                    return EditStudent(rollno: stuone["roll_no"]);
                                })); //navigate to edit page, pass student roll no to edit
                              }, icon: Icon(Icons.edit)),


                              IconButton(onPressed: () async {
                                   await mydb.db.rawDelete("DELETE FROM students WHERE roll_no = ?", [stuone["roll_no"]]);
                                   //delete student data with roll no.
                                   print("Data Deleted");
                                   ScaffoldMessenger.of(context).showSnackBar(SnackBar(content: Text("Student Data Deleted")));
                                   getdata();
                              }, icon: Icon(Icons.delete, color:Colors.red))


                          ],),
                       ),
                     );
                }).toList(),
             ), 
          ),
        ),
     );
  }
}

Output:

In this way, you can Create, Update, Read and Delete data on SQLite/SQflite database in Flutter Android/iOS apps. You can download this file from the live provided above.