Flutter SqfLite database Insert, Update, Delete

Hi Guys, Welcome to Proto Coders Point, In this Flutter Tutorial we will learn how to use SQLITE in Flutter.

What is SQLite ?

SQLite is a Database Library that provides a relational database management system(RDBMS). Here Lite means a very light weight process in term of setup or administration.

Sqlite have very awesome feature : Zero Configuration, Transactional management, Serverless database support and much more.

Flutter SQLite is very small  as small as 400kb in size.

SQLite Source code is open for all the developers any one can use then to develop a data storage.

Now a day’s SQLite are mostly use in storing data locally

For Example : A user plays a games with is completely offline, So all Hs data will be stored in SQLITE DATABASE Locally.

Let’s Start adding the Required Dependencies into our Flutter Project.

Flutter Sqflite library Adding dependencies

Step1: Open Pubspec.yaml file and add dependencies

dependencies:
  sqflite: ^1.3.0 // add this line

NOTE : Latest feature might get added in this flutter plugin so better check out official site for latest version.

Step2: Import Sqlite dart file

Once you have added the dependencies, Now you can easily user the sqlite packages in your flutter project by just importing the dart file.

import 'package:sqflite/sqflite.dart';

How to create a Sqlite Database in Flutter?

Opening a database

Get location of database using getdatabasepath() method.

var databasesPath = await getDatabasesPath();
String path = join(databasesPath, 'student.db');
var db = await openDatabase(path);

here path is a string variable where our database path is been stored.

Closing database which is open.
await db.close();
Opening the database and creating a new TABLE in it.
Database database = await openDatabase(path, version: 1,
    onCreate: (Database db, int version) async {
  await db.execute(
      'CREATE TABLE USER (id INTEGER PRIMARY KEY AUTOINCREMENT, username TEXT, ID INTEGER)');
});

Here in above example i have opened the database and created a new table called as USER.

Ok Now let’s implement SQFLite in our Flutter Application

Flutter Sqlite Tutorial with INSERT, UPDATE and DELETE Option.

Create a new Dart file which is actually an SQLITE_DBHelper

Project file > lib (right click) > New > Dart 

create a file and name it as SQLITE_DBHelper.dart

creating new file sqlite db helper

and copy paste below database helper code.

import 'package:flutter/cupertino.dart';
import 'dart:async';
import 'package:path/path.dart';
import 'package:sqflite/sqflite.dart';

class DBStudentManager {
  Database _datebase;

  Future openDB() async {
    if (_datebase == null) {
      _datebase = await openDatabase(
          join(await getDatabasesPath(), "student.db"),
          version: 1, onCreate: (Database db, int version) async {
        await db.execute(
            "CREATE TABLE student(id INTEGER PRIMARY KEY AUTOINCREMENT,name TEXT,course TEXT)");
      });
    }
  }

  Future<int> insertStudent(Student student) async {
    await openDB();
    return await _datebase.insert('student', student.toMap());
  }

  Future<List<Student>> getStudentList() async {
    await openDB();
    final List<Map<String, dynamic>> maps = await _datebase.query('student');

    return List.generate(maps.length, (index) {
      return Student(
          id: maps[index]['id'],
          name: maps[index]['name'],
          course: maps[index]['course']);
    });
  }

  Future<int> updateStudent(Student student) async {
    await openDB();
    return await _datebase.update('student', student.toMap(),
        where: 'id=?', whereArgs: [student.id]);
  }

  Future<void> deleteStudent(int id) async {
    await openDB();
    await _datebase.delete("student", where: "id = ? ", whereArgs: [id]);
  }
}

class Student {
  int id;
  String name;
  String course;

  Student({ @required this.name, @required this.course,this.id});
  Map<String, dynamic> toMap() {
    return {'name': name, 'course': course};
  }
}

Now you have database helper class that returns all the data you request from main class.

main.dart

Replace all the default code with below code in main.dart file

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

void main() => runApp(MyApp());

class MyApp extends StatelessWidget {
  // This widget is the root of your application.
  @override
  Widget build(BuildContext context) {
    return MaterialApp(
      title: 'Flutter Demo',
      theme: ThemeData(
        primarySwatch: Colors.blue,
      ),
      home: MyHomePage(),
    );
  }
}

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

class _MyHomePageState extends State<MyHomePage> {
  final DBStudentManager dbStudentManager = new DBStudentManager();
  final _nameController = TextEditingController();
  final _courseController = TextEditingController();
  final _formkey = new GlobalKey<FormState>();
  Student student;
  int updateindex;

  List<Student> studlist;

  @override
  Widget build(BuildContext context) {
    double width = MediaQuery.of(context).size.width;
    return Scaffold(
      appBar: AppBar(
        title: Text("Flutter Sqflite Example"),
      ),
      body: ListView(
        children: <Widget>[
          Form(
            key: _formkey,
            child: Padding(
              padding: const EdgeInsets.all(12.0),
              child: Column(
                children: <Widget>[
                  TextFormField(
                    decoration: InputDecoration(labelText: "Name"),
                    controller: _nameController,
                    validator: (val) =>
                        val.isNotEmpty ? null : "Name Should not be Empty",
                  ),
                  TextFormField(
                    decoration: InputDecoration(labelText: "Course"),
                    controller: _courseController,
                    validator: (val) =>
                        val.isNotEmpty ? null : "Course Should not be Empty",
                  ),
                  RaisedButton(
                    textColor: Colors.white,
                    color: Colors.lightBlue,
                    child: Container(
                        width: width * 0.9,
                        child: Text(
                          "Submit",
                          textAlign: TextAlign.center,
                        )),
                    onPressed: () {
                      submitStudent(context);
                    },
                  ),
                  FutureBuilder(
                    future: dbStudentManager.getStudentList(),
                    builder: (context, snapshot) {
                      if (snapshot.hasData) {
                        studlist = snapshot.data;
                        return ListView.builder(
                          shrinkWrap: true,
                          itemCount: studlist == null ? 0 : studlist.length,
                          itemBuilder: (BuildContext context, int index) {
                            Student st = studlist[index];
                            return Card(
                              child: Row(
                                children: <Widget>[
                                  Padding(
                                    padding: const EdgeInsets.all(16.0),
                                    child: Container(
                                      width: width * 0.50,
                                      child: Column(
                                        children: <Widget>[
                                          Text('ID: ${st.id}'),
                                          Text('Name: ${st.name}'),
                                        ],
                                      ),
                                    ),
                                  ),
                                  IconButton(
                                    onPressed: () {
                                      _nameController.text = st.name;
                                      _courseController.text = st.course;
                                      student = st;
                                      updateindex = index;
                                    },
                                    icon: Icon(
                                      Icons.edit,
                                      color: Colors.blue,
                                    ),
                                  ),
                                  IconButton(
                                    onPressed: () {
                                      dbStudentManager.deleteStudent(st.id);
                                      setState(() {
                                        studlist.removeAt(index);
                                      });
                                    },
                                    icon: Icon(
                                      Icons.delete,
                                      color: Colors.red,
                                    ),
                                  ),
                                ],
                              ),
                            );
                          },
                        );
                      }
                      return CircularProgressIndicator();
                    },
                  )
                ],
              ),
            ),
          )
        ],
      ),
    );
  }

  void submitStudent(BuildContext context) {
    if (_formkey.currentState.validate()) {
      if (student == null) {
        Student st = new Student(
            name: _nameController.text, course: _courseController.text);
        dbStudentManager.insertStudent(st).then((value) => {
              _nameController.clear(),
              _courseController.clear(),
              print("Student Data Add to database $value"),
            });
      } else {
        student.name = _nameController.text;
        student.course = _courseController.text;

        dbStudentManager.updateStudent(student).then((value) {
          setState(() {
            studlist[updateindex].name = _nameController.text;
            studlist[updateindex].course = _courseController.text;
          });
          _nameController.clear();
          _courseController.clear();
          student = null;
        });
      }
    }
  }
}

And there your app is ready to open a datebase/ create a new database , create a new table and store all the data in it and even delete or update the present data base using Sqflite flutter library.