Flutter Sqlite Database Example

In this article, We are going to integrate local database in flutter application with example. We are using sqflite plugin to setup sqlite database in flutter application. You can also check out flutter storage related articles at HERE.

Flutter does not provide built-in functionality to access sqlite database. So we are using sqflite plugin to access sqlite database on both android and ios. Sqflite plugin provides basic CRUD operations: Create, Read, Update and Delete.

Add below dependencies in pubspec file

sqflite: ^1.2.0
path_provider: ^1.5.1

Initialize Flutter Sqlite Database

static final _databaseName = “EmployeeDatabase.db”;
static final _databaseVersion = 1;

static Database _database;
Future<Database> get database async {
     if (_database != null) return _database;
     _database = await _initDatabase();
     return _database;
}

_initDatabase() async {
     Directory documentsDirectory = await getApplicationDocumentsDirectory();
     String path = join(documentsDirectory.path, _databaseName);
     return await openDatabase(path,
          version: _databaseVersion,
          onCreate: _onCreate);
}

Create Flutter Sqlite Database and Table

Future _onCreate(Database db, int version) async {
     await db.execute(”’
     CREATE TABLE $table (
     $columnId INTEGER PRIMARY KEY,
     $columnName TEXT NOT NULL,
     $columnSalary TEXT NOT NULL,
     $columnAge TEXT NOT NULL
     )
     ”’);
}

Create Model Class

We are creating some custom methods in this model class to perform insert, update, get and delete queries.

class Employee{

int empId;
String empName,empSalary, empAge;

Employee({this.empId, @required this.empName, @required this.empSalary, @required this.empAge});

//to be used when inserting a row in the table
Map<String, dynamic> toMapWithoutId() {
     final map = new Map<String, dynamic>();
     map[“emp_name”] = empName;
     map[“emp_salary”] = empSalary;
     map[“emp_age”] = empAge;
     return map;
}

//to be used when updating a row in the table
Map<String, dynamic> toMap() {
     final map = new Map<String, dynamic>();
     map[“emp_id”] = empId;
     map[“emp_name”] = empName;
     map[“emp_salary”] = empSalary;
     map[“emp_age”] = empAge;
     return map;
}

//to be used when converting the row into object
factory Employee.fromMap(Map<String, dynamic> data) => new Employee(
     empId: data[’emp_id’],
     empName: data[’emp_name’],
     empSalary: data[’emp_salary’],
     empAge: data[’emp_age’]
);
}

Insert Employee Record in Database

Future<int> insert(Map<String, dynamic> row) async {
Database db = await instance.database;
return await db.insert(table, row);
}

Employee addEmployee = new Employee(
     empName: getEmpName,
     empSalary: getEmpSalary,
     empAge: getEmpAge);
DatabaseHelper.instance.insert( addEmployee.toMapWithoutId() );

Get All Employees Records From Database

Future<List<Map<String, dynamic>>> queryAllRows() async {
     Database db = await instance.database;
     var result = await db.query(table);
     return result.toList();
}

Future<List<Map<String, dynamic>>> getEmployees() async {
List<Map<String, dynamic>> listMap = await DatabaseHelper.instance.queryAllRows();
}

Update Employee Record in Database

Future<int> update(Map<String, dynamic> row) async {
     Database db = await instance.database;
     int id = row[columnId];
     return await db.update(table, row, where: ‘$columnId = ?’, whereArgs: [id]);
}

Employee updateEmployee = new Employee(
     empId: widget.selectedEmployee.empId,
     empName: getEmpName,
     empSalary: getEmpSalary,
     empAge: getEmpAge);
DatabaseHelper.instance.update( updateEmployee.toMap() );

Delete Employee Record From Database

Future<int> delete(int id) async {
Database db = await instance.database;
return await db.delete(table, where: ‘$columnId = ?’, whereArgs: [id]);
}

DatabaseHelper.instance.delete( getEmployee.empId );

Final Code For Flutter Sqlite Database

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 = "EmployeeDatabase.db";
  static final _databaseVersion = 1;

  static final table = 'emp_table';

  static final columnId = 'emp_id';
  static final columnName = 'emp_name';
  static final columnSalary = 'emp_salary';
  static final columnAge = 'emp_age';


  DatabaseHelper._privateConstructor();
  static final DatabaseHelper instance = new DatabaseHelper._privateConstructor();

  static Database _database;
  Future<Database> get database async {
    if (_database != null) return _database;
    _database = await _initDatabase();
    return _database;
  }

  _initDatabase() async {
    Directory documentsDirectory = await getApplicationDocumentsDirectory();
    String path = join(documentsDirectory.path, _databaseName);
    return await openDatabase(path,
        version: _databaseVersion,
        onCreate: _onCreate);
  }


  Future _onCreate(Database db, int version) async {
    await db.execute('''
          CREATE TABLE $table (
            $columnId INTEGER PRIMARY KEY,
            $columnName TEXT NOT NULL,
            $columnSalary TEXT NOT NULL,
            $columnAge TEXT NOT NULL
          )
          ''');
  }


  Future<int> insert(Map<String, dynamic> row) async {
    Database db = await instance.database;
    return await db.insert(table, row);
  }


  Future<List<Map<String, dynamic>>> queryAllRows() async {
    Database db = await instance.database;
    var result = await db.query(table);
    return result.toList();
  }


  Future<int> queryRowCount() async {
    Database db = await instance.database;
    return Sqflite.firstIntValue(await db.rawQuery('SELECT COUNT(*) FROM $table'));
  }


  Future<int> update(Map<String, dynamic> row) async {
    Database db = await instance.database;
    int id = row[columnId];
    return await db.update(table, row, where: '$columnId = ?', whereArgs: [id]);
  }


  Future<int> delete(int id) async {
    Database db = await instance.database;
    return await db.delete(table, where: '$columnId = ?', whereArgs: [id]);
  }
}

import 'package:flutter/cupertino.dart';

class Employee{

  int empId;
  String empName,empSalary, empAge;

  Employee({this.empId, @required this.empName, @required this.empSalary, @required this.empAge});

  //to be used when inserting a row in the table
  Map<String, dynamic> toMapWithoutId() {
    final map = new Map<String, dynamic>();
    map["emp_name"] = empName;
    map["emp_salary"] = empSalary;
    map["emp_age"] = empAge;
    return map;
  }

  //to be used when updating a row in the table
  Map<String, dynamic> toMap() {
    final map = new Map<String, dynamic>();
    map["emp_id"] = empId;
    map["emp_name"] = empName;
    map["emp_salary"] = empSalary;
    map["emp_age"] = empAge;
    return map;
  }

  //to be used when converting the row into object
  factory Employee.fromMap(Map<String, dynamic> data) => new Employee(
      empId: data['emp_id'],
      empName: data['emp_name'],
      empSalary: data['emp_salary'],
      empAge: data['emp_age']
  );
}

import 'package:flutter/material.dart';
import 'package:fluttersqflitesample/employees_list.dart';

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

class MyApp extends StatelessWidget {
  // This widget is the root of your application.
  @override
  Widget build(BuildContext context) {
    return MaterialApp(
      debugShowCheckedModeBanner: false,
      title: 'Flutter Sqflite Sample',
      home: EmployeesList(),
    );
  }
}

import 'package:flutter/material.dart';
import 'package:fluttersqflitesample/add_edit_employee.dart';
import 'package:fluttersqflitesample/database_helper.dart';
import 'package:fluttersqflitesample/employee.dart';

class EmployeesList extends StatefulWidget {
  @override
  State<StatefulWidget> createState() {
    // TODO: implement createState
    return EmployeesListState();
  }
}

class EmployeesListState extends State<EmployeesList> {
  List<Employee> listEmployees = [];

  Future<List<Map<String, dynamic>>> getEmployees() async {
    List<Map<String, dynamic>> listMap = await DatabaseHelper.instance.queryAllRows();
    setState(() {
      listMap.forEach((map) => listEmployees.add(Employee.fromMap(map)));
    });
  }

  @override
  void initState() {
    // TODO: implement initState
    getEmployees();
    super.initState();
  }

  @override
  Widget build(BuildContext context) {
    // TODO: implement build

    return SafeArea(
      child: Scaffold(
        appBar: AppBar(
          title: Text("Flutter Sqflite Sample"),
          actions: <Widget>[
            IconButton(
              icon: Icon(Icons.add),
              onPressed: () {
                Navigator.push(context,
                    MaterialPageRoute(builder: (_) => AddEditEmployee(false)));
              },
            )
          ],
        ),
        body: Container(
            padding: EdgeInsets.all(15),
            child: ListView.builder(
                itemCount: listEmployees.length,
                itemBuilder: (context, position) {
                  Employee getEmployee = listEmployees[position];
                  var salary = getEmployee.empSalary;
                  var age = getEmployee.empAge;
                  return Card(
                    elevation: 8,
                    child: Container(
                      height: 80,
                      padding: EdgeInsets.all(15),
                      child: Stack(
                        children: <Widget>[
                          Align(
                              alignment: Alignment.topLeft,
                              child: Text(getEmployee.empName,
                                  style: TextStyle(fontSize: 18))),
                          Align(
                            alignment: Alignment.centerRight,
                            child: Container(
                              margin: EdgeInsets.only(right: 45),
                              child: IconButton(
                                  icon: Icon(Icons.edit),
                                  onPressed: () {
                                    Navigator.push(
                                        context,
                                        MaterialPageRoute(
                                            builder: (_) => AddEditEmployee(
                                                true, getEmployee)));
                                  }),
                            ),
                          ),
                          Align(
                            alignment: Alignment.centerRight,
                            child: IconButton(
                                icon: Icon(Icons.delete),
                                onPressed: (){
                                  DatabaseHelper.instance.delete(getEmployee.empId);
                                  setState(() => {
                                    listEmployees.removeWhere((item) => item.empId == getEmployee.empId)
                                  });
                                }),
                          ),
                          Align(
                              alignment: Alignment.bottomLeft,
                              child: Text("Salary: $salary | Age: $age",
                                  style: TextStyle(fontSize: 18))),
                        ],
                      ),
                    ),
                  );
                })),
      ),
    );
  }
}

import 'package:flutter/cupertino.dart';
import 'package:flutter/material.dart';
import 'package:fluttersqflitesample/database_helper.dart';
import 'package:fluttersqflitesample/employee.dart';
import 'package:fluttersqflitesample/employees_list.dart';

class AddEditEmployee extends StatefulWidget {
  bool isEdit;
  Employee selectedEmployee;

  AddEditEmployee(this.isEdit, [this.selectedEmployee]);

  @override
  State<StatefulWidget> createState() {
    // TODO: implement createState
    return AddEditEmployeeState();
  }
}

class AddEditEmployeeState extends State<AddEditEmployee> {
  TextEditingController controllerName = new TextEditingController();
  TextEditingController controllerSalary = new TextEditingController();
  TextEditingController controllerAge = new TextEditingController();

  @override
  Widget build(BuildContext context) {
    // TODO: implement build

    if (widget.isEdit) {
      controllerName.text = widget.selectedEmployee.empName;
      controllerSalary.text = widget.selectedEmployee.empSalary.toString();
      controllerAge.text = widget.selectedEmployee.empAge.toString();
    }

    return SafeArea(
      child: Scaffold(
        body: SingleChildScrollView(
          child: Container(
            margin: EdgeInsets.all(25),
            child: Column(
              mainAxisAlignment: MainAxisAlignment.center,
              children: <Widget>[
                Row(
                  mainAxisAlignment: MainAxisAlignment.start,
                  children: <Widget>[
                    Text("Employee Name:", style: TextStyle(fontSize: 18)),
                    SizedBox(width: 20),
                    Expanded(
                      child: TextField(controller: controllerName),
                    )
                  ],
                ),
                SizedBox(height: 60),
                Row(
                  mainAxisAlignment: MainAxisAlignment.start,
                  children: <Widget>[
                    Text("Employee Salary:", style: TextStyle(fontSize: 18)),
                    SizedBox(width: 20),
                    Expanded(
                      child: TextField(
                          controller: controllerSalary,
                          keyboardType: TextInputType.number),
                    )
                  ],
                ),
                SizedBox(height: 60),
                Row(
                  mainAxisAlignment: MainAxisAlignment.start,
                  children: <Widget>[
                    Text("Employee Age:", style: TextStyle(fontSize: 18)),
                    SizedBox(width: 20),
                    Expanded(
                      child: TextField(
                          controller: controllerAge,
                          keyboardType: TextInputType.number),
                    )
                  ],
                ),
                SizedBox(height: 100),
                RaisedButton(
                  color: Colors.grey,
                  child: Text("Submit",
                      style: TextStyle(color: Colors.white, fontSize: 18)),
                  onPressed: () {
                    var getEmpName = controllerName.text;
                    var getEmpSalary = controllerSalary.text;
                    var getEmpAge = controllerAge.text;
                    if (getEmpName.isNotEmpty &amp;&amp;
                        getEmpSalary.isNotEmpty &amp;&amp;
                        getEmpAge.isNotEmpty) {
                      if (widget.isEdit) {
                        Employee updateEmployee = new Employee(
                            empId: widget.selectedEmployee.empId,
                            empName: getEmpName,
                            empSalary: getEmpSalary,
                            empAge: getEmpAge);
                        DatabaseHelper.instance.update(updateEmployee.toMap());
                      } else {
                        Employee addEmployee = new Employee(
                            empName: getEmpName,
                            empSalary: getEmpSalary,
                            empAge: getEmpAge);
                        DatabaseHelper.instance
                            .insert(addEmployee.toMapWithoutId());
                      }
                      Navigator.pushAndRemoveUntil(
                          context,
                          MaterialPageRoute(builder: (_) => EmployeesList()),
                              (r) => false);
                    }
                  },
                )
              ],
            ),
          ),
        )
      ),
    );
  }
}