Flutter SQLite tutorial | SQFLite | Local Database

Flutter SQLite :

Flutter SQLite is used to store data in the app for both android and iOS as well storing of data in apps is a useful scenario for data saving, faster transactions and also offline data management.

When user is offline data can be stored within the app and once online can sync with servers making user friendly app transactions, mostly apps like whatsapp, facebook, gmail store the messages / mail / post you create when there is no internet and publish when they are online.

And also form data like regions, descriptions of the fields, local contacts and much more are easily saved in flutter SQLite database.

The most useful example is cart in many eCommerce apps like Amazon, flipkart, and food delivery apps like swiggy, zomato, uber eats and much more for maintaining user selected items and processing further.

In this tutorial on flutter sqlite we will learn how to insert data, request and delete in flutter app.

Flutter SQLite Video Tutorial:

Project Structure :

This image depicts the usage of flutter sqlite project implementation.

pubspec.yaml :

Add sqflite and path_provider dependencies to pubspec and update the versions accordingly to avoid code level issues.

dependencies:
  flutter:
    sdk: flutter
  sqflite:
  path_provider:

main.dart :

Declare a MyApp class extending StatelessWidget

import 'package:flutter/material.dart';
import 'package:flutter_sqlite/employeePage.dart';

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

class MyApp extends StatelessWidget {
  @override
  Widget build(BuildContext context) {
    return MaterialApp(
      theme: ThemeData(
        primarySwatch: Colors.blue,
      ),
      home: employeePage(),
    );
  }
}

From home page navigate to employeePage()

employee_model.dart :

Add a model class to make transactions in app.

class Employee {
  int id;
  String name;
  String phone;
  Employee(this.id, this.name, this.phone);

  Map<String, dynamic> toMap() {
    var map = <String, dynamic>{
      'id': id,
      'name': name,
      'phone': phone,
    };
    return map;
  }

  Employee.fromMap(Map<String, dynamic> map) {
    id = map['id'];
    name = map['name'];
    phone = map['phone'];
  }
}

db_helper.dart :

Initialize the database

initDatabase() async {
  io.Directory documentDirectory = await getApplicationDocumentsDirectory();
  String path = join(documentDirectory.path, 'employee.db');
  var db = await openDatabase(path, version: 1, onCreate: _onCreate);
  return db;
}

Create a database using a sql query

_onCreate(Database db, int version) async {
  await db
      .execute('CREATE TABLE employee (id INTEGER PRIMARY KEY, name TEXT, phone TEXT)');
}

Add employee details :

Future<Employee> add(Employee employee) async {
  var dbClient = await db;
  employee.id = await dbClient.insert('employee', employee.toMap());
  return employee;
}

Retrieve employee details :

Future<List<Employee>> getEmployee() async {
  var dbClient = await db;
  List<Map> maps = await dbClient.query('employee', columns: ['id', 'name','phone']);
  List<Employee> employees = [];
  if (maps.length > 0) {
    for (int i = 0; i < maps.length; i++) {
      employees.add(Employee.fromMap(maps[i]));
    }
  }
  return employees;
}

Update employee details :

Future<int> update(Employee employee) async {
  var dbClient = await db;
  return await dbClient.update(
    'employee',
    employee.toMap(),
    where: 'id = ?',
    whereArgs: [employee.id],
  );
}

Delete employee details :

Future<int> delete(int id) async {
  var dbClient = await db;
  return await dbClient.delete(
    'employee',
    where: 'id = ?',
    whereArgs: [id],
  );
}

Finally close the database :

Future close() async {
  var dbClient = await db;
  dbClient.close();
}

import 'package:flutter_sqlite/employee_model.dart';
import 'package:path/path.dart' show join;
import 'package:sqflite/sqflite.dart';
import 'dart:io' as io;
import 'package:path_provider/path_provider.dart' show getApplicationDocumentsDirectory;

class DBHelper {
  static Database _db;
  Future<Database> get db async {
    if (_db != null) {
      return _db;
    }
    _db = await initDatabase();
    return _db;
  }

  initDatabase() async {
    io.Directory documentDirectory = await getApplicationDocumentsDirectory();
    String path = join(documentDirectory.path, 'employee.db');
    var db = await openDatabase(path, version: 1, onCreate: _onCreate);
    return db;
  }

  _onCreate(Database db, int version) async {
    await db
        .execute('CREATE TABLE employee (id INTEGER PRIMARY KEY, name TEXT, phone TEXT)');
  }

  Future<Employee> add(Employee employee) async {
    var dbClient = await db;
    employee.id = await dbClient.insert('employee', employee.toMap());
    return employee;
  }

  Future<List<Employee>> getEmployee() async {
    var dbClient = await db;
    List<Map> maps = await dbClient.query('employee', columns: ['id', 'name','phone']);
    List<Employee> employees = [];
    if (maps.length > 0) {
      for (int i = 0; i < maps.length; i++) {
        employees.add(Employee.fromMap(maps[i]));
      }
    }
    return employees;
  }

  Future<int> delete(int id) async {
    var dbClient = await db;
    return await dbClient.delete(
      'employee',
      where: 'id = ?',
      whereArgs: [id],
    );
  }

  Future<int> update(Employee employee) async {
    var dbClient = await db;
    return await dbClient.update(
      'employee',
      employee.toMap(),
      where: 'id = ?',
      whereArgs: [employee.id],
    );
  }

  Future close() async {
    var dbClient = await db;
    dbClient.close();
  }
}

employeePage.dart :

Make the initialization

Future<List<Employee>> employees;

String _employeeName;
String _employeePhone;

bool isUpdate = false;
int employeeIdForUpdate;
DBHelper dbHelper;

final _employeeNameController = TextEditingController();
final _employeePhoneController = TextEditingController();

also the database

@override
void initState() {
  super.initState();
  dbHelper = DBHelper();
  refreshemployeeList();
}

fetch employee list

refreshemployeeList() {
  setState(() {
    employees = dbHelper.getEmployee();
  });
}

AppBar :

Add buttons to the app bar for performing  CRUD operations

 AppBar(
  title: Text('SQLite'),

  actions: <Widget>[
    RaisedButton(
      color: Colors.blue,
      child: Text(
        (isUpdate ? 'UPDATE' : 'ADD'),
        style: TextStyle(color: Colors.white),
      ),
      onPressed: () {
        if (isUpdate) {
          if (_formStateKey.currentState.validate()) {
            _formStateKey.currentState.save();
            dbHelper
                .update(Employee(employeeIdForUpdate, _employeeName,_employeePhone))
                .then((data) {
              setState(() {
                isUpdate = false;
              });
            });
          }
        } else {
          if (_formStateKey.currentState.validate()) {
            _formStateKey.currentState.save();
            dbHelper.add(Employee(null, _employeeName,_employeePhone));
          }
        }
        _employeeNameController.text = '';
        _employeePhoneController.text = '';
        refreshemployeeList();
      },
    ),

    RaisedButton(
      color: Colors.blue,
      child: Text(
        (isUpdate ? 'CANCEL' : 'CLEAR'),
        style: TextStyle(color: Colors.white),
      ),
      onPressed: () {
        _employeeNameController.text = '';
        _employeePhoneController.text = '';
        setState(() {
          isUpdate = false;
          employeeIdForUpdate = null;
        });
      },
    ),

  ],

),

Generate the list view from the data

SingleChildScrollView generateList(List<Employee> employees) {
  return SingleChildScrollView(
    scrollDirection: Axis.vertical,
    child: SizedBox(
      width: MediaQuery.of(context).size.width,
      child: DataTable(
        columns: [
          DataColumn(
            label: Text('Name'),
          ),
          DataColumn(
            label: Text('Phome'),
          ),
          DataColumn(
            label: Text(''),
          )
        ],
        rows: employees
            .map(
              (employee) => DataRow(
            cells: [
              DataCell(
                Text(employee.name),
                onTap: () {
                  setState(() {
                    isUpdate = true;
                    employeeIdForUpdate = employee.id;
                  });
                  _employeeNameController.text = employee.name;
                  _employeePhoneController.text = employee.phone;
                },
              ),
              DataCell(
                Text(employee.phone),
                onTap: () {
                  setState(() {
                    isUpdate = true;
                    employeeIdForUpdate = employee.id;
                  });
                  _employeeNameController.text = employee.name;
                  _employeePhoneController.text = employee.phone;
                },
              ),
              DataCell(
                IconButton(
                  icon: Icon(Icons.delete),
                  onPressed: () {
                    dbHelper.delete(employee.id);
                    refreshemployeeList();
                  },
                ),
              )
            ],
          ),
        )
            .toList(),
      ),
    ),
  );
}

import 'package:flutter/material.dart';
import 'package:flutter_sqlite/db_helper.dart';
import 'package:flutter_sqlite/employee_model.dart';

class employeePage extends StatefulWidget {
  @override
  _employeePageState createState() => _employeePageState();
}

class _employeePageState extends State<employeePage> {
  final GlobalKey<FormState> _formStateKey = GlobalKey<FormState>();

  Future<List<Employee>> employees;

  String _employeeName;
  String _employeePhone;

  bool isUpdate = false;
  int employeeIdForUpdate;
  DBHelper dbHelper;

  final _employeeNameController = TextEditingController();
  final _employeePhoneController = TextEditingController();

  @override
  void initState() {
    super.initState();
    dbHelper = DBHelper();
    refreshemployeeList();
  }

  refreshemployeeList() {
    setState(() {
      employees = dbHelper.getEmployee();
    });
  }

  @override
  Widget build(BuildContext context) {
    return Scaffold(
      appBar: AppBar(
        title: Text('SQLite'),

        actions: <Widget>[
          RaisedButton(
            color: Colors.blue,
            child: Text(
              (isUpdate ? 'UPDATE' : 'ADD'),
              style: TextStyle(color: Colors.white),
            ),
            onPressed: () {
              if (isUpdate) {
                if (_formStateKey.currentState.validate()) {
                  _formStateKey.currentState.save();
                  dbHelper
                      .update(Employee(employeeIdForUpdate, _employeeName,_employeePhone))
                      .then((data) {
                    setState(() {
                      isUpdate = false;
                    });
                  });
                }
              } else {
                if (_formStateKey.currentState.validate()) {
                  _formStateKey.currentState.save();
                  dbHelper.add(Employee(null, _employeeName,_employeePhone));
                }
              }
              _employeeNameController.text = '';
              _employeePhoneController.text = '';
              refreshemployeeList();
            },
          ),

          RaisedButton(
            color: Colors.blue,
            child: Text(
              (isUpdate ? 'CANCEL' : 'CLEAR'),
              style: TextStyle(color: Colors.white),
            ),
            onPressed: () {
              _employeeNameController.text = '';
              _employeePhoneController.text = '';
              setState(() {
                isUpdate = false;
                employeeIdForUpdate = null;
              });
            },
          ),

        ],

      ),
      body: Column(
        children: <Widget>[
          Form(
            key: _formStateKey,
            autovalidate: true,
            child: Column(
              children: <Widget>[
                Padding(
                  padding: EdgeInsets.only(left: 10, right: 10, bottom: 10),
                  child: TextFormField(
                    onSaved: (value) {
                      _employeeName = value;
                    },
                    keyboardType: TextInputType.number,
                    controller: _employeeNameController,
                    decoration: InputDecoration(
                        focusedBorder: new UnderlineInputBorder(
                            borderSide: new BorderSide(
                                color: Colors.blue,
                                width: 2,
                                style: BorderStyle.solid)),
                        // hintText: "employee Name",
                        labelText: "Name",

                        fillColor: Colors.white,
                        labelStyle: TextStyle(
                          color: Colors.blue,
                        )),
                  ),
                ),
                Padding(
                  padding: EdgeInsets.only(left: 10, right: 10, bottom: 10),
                  child: TextFormField(
                    onSaved: (value) {
                      _employeePhone = value;
                    },
                    controller: _employeePhoneController,
                    decoration: InputDecoration(
                        focusedBorder: new UnderlineInputBorder(
                            borderSide: new BorderSide(
                                color: Colors.blue,
                                width: 2,
                                style: BorderStyle.solid)),
                        // hintText: "employee Name",
                        labelText: "Phone",

                        fillColor: Colors.white,
                        labelStyle: TextStyle(
                          color: Colors.blue,
                        )),
                  ),
                ),
              ],
            ),
          ),

          const Divider(
            height: 5.0,
          ),
          Expanded(
            child: FutureBuilder(
              future: employees,
              builder: (context, snapshot) {
                if (snapshot.hasData) {
                  return generateList(snapshot.data);
                }
                if (snapshot.data == null || snapshot.data.length == 0) {
                  return Text('No Employee Found');
                }
                return CircularProgressIndicator();
              },
            ),
          ),

        ],
      ),
    );
  }

  SingleChildScrollView generateList(List<Employee> employees) {
    return SingleChildScrollView(
      scrollDirection: Axis.vertical,
      child: SizedBox(
        width: MediaQuery.of(context).size.width,
        child: DataTable(
          columns: [
            DataColumn(
              label: Text('Name'),
            ),
            DataColumn(
              label: Text('Phome'),
            ),
            DataColumn(
              label: Text(''),
            )
          ],
          rows: employees
              .map(
                (employee) => DataRow(
              cells: [
                DataCell(
                  Text(employee.name),
                  onTap: () {
                    setState(() {
                      isUpdate = true;
                      employeeIdForUpdate = employee.id;
                    });
                    _employeeNameController.text = employee.name;
                    _employeePhoneController.text = employee.phone;
                  },
                ),
                DataCell(
                  Text(employee.phone),
                  onTap: () {
                    setState(() {
                      isUpdate = true;
                      employeeIdForUpdate = employee.id;
                    });
                    _employeeNameController.text = employee.name;
                    _employeePhoneController.text = employee.phone;
                  },
                ),
                DataCell(
                  IconButton(
                    icon: Icon(Icons.delete),
                    onPressed: () {
                      dbHelper.delete(employee.id);
                      refreshemployeeList();
                    },
                  ),
                )
              ],
            ),
          )
              .toList(),
        ),
      ),
    );
  }
}

Flutter SQLite Output :

This screen depicts the flutter SQLite CRUD operations

If you have any query’s on this tutorial on flutter sqlite database do let us know in the comment section below.If you like this tutorial do like and share us for more interesting tutorials.