SQLite CRUD Operation In Flutter

In this article, you will learn how to perform CRUD operation in SQlite Database in Flutter.

Introduction

 
In this article, we will learn how to implement SQLite Database in Flutter Application. SQLite is used to store the data in relational tables in the local device. The database is persistent in nature until you do not remove the database from the device. SQLite database is mostly used to store data in offline mode. SQLite plugin is used to implement SQLite database in Flutter application so let’s take the Student example to perform CRUD operation in Flutter.
 
Output
 
 
Plugin Required
 
sqflite: ^1.1.6+4
path_provider: ^1.3.0
 

Steps

 
Step 1
 
The first and most basic step is to create a new application in Flutter. If you are a beginner in Flutter, then you can check my blog Create a first app in Flutter. I have created an app named as “flutter_sqlite_crud”.
 
Step 2
 
Now, we will configure two plugins in pubspec.yaml file sqflite and path_provider.
  1. dependencies:  
  2.  flutter:  
  3.    sdk: flutter  
  4.  cupertino_icons: ^0.1.2  
  5.  sqflite: ^1.1.6+4  
  6.  path_provider: ^1.3.0  
Step 3
 
Now, we will implement modal for the student data structure. For that, we create student_model.dart file and create student class with id and name properties and provide mapping for the SQLite database. Following is the programming implementation of that.
  1. class Student {  
  2.  int id;  
  3.  String name;  
  4.  Student(this.id, this.name);  
  5.    
  6.  Map<String, dynamic> toMap() {  
  7.    var map = <String, dynamic>{  
  8.      'id': id,  
  9.      'name': name,  
  10.    };  
  11.    return map;  
  12.  }  
  13.    
  14.  Student.fromMap(Map<String, dynamic> map) {  
  15.    id = map['id'];  
  16.    name = map['name'];  
  17.  }  
  18. }  
Step 4
 
Now, we will implement SQLite Database CRUD Operation implementation. For that, create a file named as db_helper.dart and initialize the database. Then create insert, delete, update, and select functions for CRUD operation. Following is the programming implementation of that.
  1. import 'package:flutter_sqlite_crud/student_model.dart';    
  2. import 'package:sqflite/sqflite.dart';    
  3. import 'dart:io' as io;    
  4. import 'package:path/path.dart';    
  5. import 'package:path_provider/path_provider.dart';    
  6.      
  7. class DBHelper {    
  8.  static Database _db;    
  9.  Future<Database> get db async {    
  10.    if (_db != null) {    
  11.      return _db;    
  12.    }    
  13.    _db = await initDatabase();    
  14.    return _db;    
  15.  }    
  16.      
  17.  initDatabase() async {    
  18.    io.Directory documentDirectory = await getApplicationDocumentsDirectory();    
  19.    String path = join(documentDirectory.path, 'student.db');    
  20.    var db = await openDatabase(path, version: 1, onCreate: _onCreate);    
  21.    return db;    
  22.  }    
  23.      
  24.  _onCreate(Database db, int version) async {    
  25.    await db    
  26.        .execute('CREATE TABLE student (id INTEGER PRIMARY KEY, name TEXT)');    
  27.  }    
  28.      
  29.  Future<Student> add(Student student) async {    
  30.    var dbClient = await db;    
  31.    student.id = await dbClient.insert('student', student.toMap());    
  32.    return student;    
  33.  }    
  34.      
  35.  Future<List<Student>> getStudents() async {    
  36.    var dbClient = await db;    
  37.    List<Map> maps = await dbClient.query('student', columns: ['id''name']);    
  38.    List<Student> students = [];    
  39.    if (maps.length > 0) {    
  40.      for (int i = 0; i < maps.length; i++) {    
  41.        students.add(Student.fromMap(maps[i]));    
  42.      }    
  43.    }    
  44.    return students;    
  45.  }    
  46.      
  47.  Future<intdelete(int id) async {    
  48.    var dbClient = await db;    
  49.    return await dbClient.delete(    
  50.      'student',    
  51.      where: 'id = ?',    
  52.      whereArgs: [id],    
  53.    );    
  54.  }    
  55.      
  56.  Future<int> update(Student student) async {    
  57.    var dbClient = await db;    
  58.    return await dbClient.update(    
  59.      'student',    
  60.      student.toMap(),    
  61.      where: 'id = ?',    
  62.      whereArgs: [student.id],    
  63.    );    
  64.  }    
  65.      
  66.  Future close() async {    
  67.    var dbClient = await db;    
  68.    dbClient.close();    
  69.  }    
  70. }    
Step 5
 
Now, we will implement UI side by creating form to input student details and data table to show student records. Following is the programming implementation of that.
  1. import 'package:flutter/material.dart';    
  2. import 'package:flutter_sqlite_crud/db_helper.dart';    
  3. import 'package:flutter_sqlite_crud/student_model.dart';    
  4.      
  5. void main() => runApp(MyApp());    
  6.      
  7. class MyApp extends StatelessWidget {    
  8.  @override    
  9.  Widget build(BuildContext context) {    
  10.    return MaterialApp(    
  11.      theme: ThemeData(    
  12.        primarySwatch: Colors.purple,    
  13.      ),    
  14.      home: StudentPage(),    
  15.    );    
  16.  }    
  17. }    
  18.      
  19. class StudentPage extends StatefulWidget {    
  20.  @override    
  21.  _StudentPageState createState() => _StudentPageState();    
  22. }    
  23.      
  24. class _StudentPageState extends State<StudentPage> {    
  25.  final GlobalKey<FormState> _formStateKey = GlobalKey<FormState>();    
  26.  Future<List<Student>> students;    
  27.  String _studentName;    
  28.  bool isUpdate = false;    
  29.  int studentIdForUpdate;    
  30.  DBHelper dbHelper;    
  31.  final _studentNameController = TextEditingController();    
  32.      
  33.  @override    
  34.  void initState() {    
  35.    super.initState();    
  36.    dbHelper = DBHelper();    
  37.    refreshStudentList();    
  38.  }    
  39.      
  40.  refreshStudentList() {    
  41.    setState(() {    
  42.      students = dbHelper.getStudents();    
  43.    });    
  44.  }    
  45.      
  46.  @override    
  47.  Widget build(BuildContext context) {    
  48.    return Scaffold(    
  49.      appBar: AppBar(    
  50.        title: Text('SQLite CRUD in Flutter'),    
  51.      ),    
  52.      body: Column(    
  53.        children: <Widget>[    
  54.          Form(    
  55.            key: _formStateKey,    
  56.            autovalidate: true,    
  57.            child: Column(    
  58.              children: <Widget>[    
  59.                Padding(    
  60.                  padding: EdgeInsets.only(left: 10, right: 10, bottom: 10),    
  61.                  child: TextFormField(    
  62.                    validator: (value) {    
  63.                      if (value.isEmpty) {    
  64.                        return 'Please Enter Student Name';    
  65.                      }    
  66.                      if (value.trim() == "")    
  67.                        return "Only Space is Not Valid!!!";    
  68.                      return null;    
  69.                    },    
  70.                    onSaved: (value) {    
  71.                      _studentName = value;    
  72.                    },    
  73.                    controller: _studentNameController,    
  74.                    decoration: InputDecoration(    
  75.                        focusedBorder: new UnderlineInputBorder(    
  76.                            borderSide: new BorderSide(    
  77.                                color: Colors.purple,    
  78.                                width: 2,    
  79.                                style: BorderStyle.solid)),    
  80.                        // hintText: "Student Name",    
  81.                        labelText: "Student Name",    
  82.                        icon: Icon(    
  83.                          Icons.business_center,    
  84.                          color: Colors.purple,    
  85.                        ),    
  86.                        fillColor: Colors.white,    
  87.                        labelStyle: TextStyle(    
  88.                          color: Colors.purple,    
  89.                        )),    
  90.                  ),    
  91.                ),    
  92.              ],    
  93.            ),    
  94.          ),    
  95.          Row(    
  96.            mainAxisAlignment: MainAxisAlignment.center,    
  97.            children: <Widget>[    
  98.              RaisedButton(    
  99.                color: Colors.purple,    
  100.                child: Text(    
  101.                  (isUpdate ? 'UPDATE' : 'ADD'),    
  102.                  style: TextStyle(color: Colors.white),    
  103.                ),    
  104.                onPressed: () {    
  105.                  if (isUpdate) {    
  106.                    if (_formStateKey.currentState.validate()) {    
  107.                      _formStateKey.currentState.save();    
  108.                      dbHelper    
  109.                          .update(Student(studentIdForUpdate, _studentName))    
  110.                          .then((data) {    
  111.                        setState(() {    
  112.                          isUpdate = false;    
  113.                        });    
  114.                      });    
  115.                    }    
  116.                  } else {    
  117.                    if (_formStateKey.currentState.validate()) {    
  118.                      _formStateKey.currentState.save();    
  119.                      dbHelper.add(Student(null, _studentName));    
  120.                    }    
  121.                  }    
  122.                  _studentNameController.text = '';    
  123.                  refreshStudentList();    
  124.                },    
  125.              ),    
  126.              Padding(    
  127.                padding: EdgeInsets.all(10),    
  128.              ),    
  129.              RaisedButton(    
  130.                color: Colors.red,    
  131.                child: Text(    
  132.                  (isUpdate ? 'CANCEL UPDATE' : 'CLEAR'),    
  133.                  style: TextStyle(color: Colors.white),    
  134.                ),    
  135.                onPressed: () {    
  136.                  _studentNameController.text = '';    
  137.                  setState(() {    
  138.                    isUpdate = false;    
  139.                    studentIdForUpdate = null;    
  140.                  });    
  141.                },    
  142.              ),    
  143.            ],    
  144.          ),    
  145.          const Divider(    
  146.            height: 5.0,    
  147.          ),    
  148.          Expanded(    
  149.            child: FutureBuilder(    
  150.              future: students,    
  151.              builder: (context, snapshot) {    
  152.                if (snapshot.hasData) {    
  153.                  return generateList(snapshot.data);    
  154.                }    
  155.                if (snapshot.data == null || snapshot.data.length == 0) {    
  156.                  return Text('No Data Found');    
  157.                }    
  158.                return CircularProgressIndicator();    
  159.              },    
  160.            ),    
  161.          ),    
  162.        ],    
  163.      ),    
  164.    );    
  165.  }    
  166.      
  167.  SingleChildScrollView generateList(List<Student> students) {    
  168.    return SingleChildScrollView(    
  169.      scrollDirection: Axis.vertical,    
  170.      child: SizedBox(    
  171.        width: MediaQuery.of(context).size.width,    
  172.        child: DataTable(    
  173.          columns: [    
  174.            DataColumn(    
  175.              label: Text('NAME'),    
  176.            ),    
  177.            DataColumn(    
  178.              label: Text('DELETE'),    
  179.            )    
  180.          ],    
  181.          rows: students    
  182.              .map(    
  183.                (student) => DataRow(    
  184.                  cells: [    
  185.                    DataCell(    
  186.                      Text(student.name),    
  187.                      onTap: () {    
  188.                        setState(() {    
  189.                          isUpdate = true;    
  190.                          studentIdForUpdate = student.id;    
  191.                        });    
  192.                        _studentNameController.text = student.name;    
  193.                      },    
  194.                    ),    
  195.                    DataCell(    
  196.                      IconButton(    
  197.                        icon: Icon(Icons.delete),    
  198.                        onPressed: () {    
  199.                          dbHelper.delete(student.id);    
  200.                          refreshStudentList();    
  201.                        },    
  202.                      ),    
  203.                    )    
  204.                  ],    
  205.                ),    
  206.              )    
  207.              .toList(),    
  208.        ),    
  209.      ),    
  210.    );    
  211.  }    
  212. }    
Hurry…. Run the app and test It on emulator/simulator or device :)))
 

Conclusion

 
We have learned how to implement SQLite Database in Flutter and Perform CRUD operation.