Press "Enter" to skip to content

Flutter: Create Database, Insert, Select, Update and Delete Data with SQFlite

Tutorial Abstraction: In Flutter you can simply Create Database and Insert, Select, Update and Delete Data with SQFlite plugin for persisting data on Android and iOS devices.

Persisting data on every application is important for later uses. You can keep your data on your own mobile phone or on server (internet).

Here let’s discuss how to keep data on our own mobile device, and not on server. For persisting data on a device, we must need a database at first.

SQLite is one of the famous free database, commonly used for mobile app development. In Flutter, we use its variant version of name SQFlite.

Using SQFlite, you can perform all database operations like create database and data base manipulations like Insert, Select, Update, Delete, Count, Join, Drop, Truncate, etc on Flutter.

How to Add SQFlite on Flutter

To add sqflite: ^1.1.0 on your pubspec.yaml file and run ‘pub get’.

Flutter SQFlite plugin for database operations

Main File with Design

On main.dart file, we create a design to insert new items and to list items from database.

main.dart

import 'package:flutter/material.dart';
import 'package:myapp/db_helper/model.dart';
import 'package:myapp/db_helper/database_helper.dart';
import 'package:myapp/DetailsPage.dart';
import 'dart:async';

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

class MyApp extends StatelessWidget {

  @override
  Widget build(BuildContext context) {
    final appTitle = 'Data List';
    return MaterialApp(
      title: appTitle,
      home: Scaffold(
        appBar: AppBar(
          title: Text(appTitle),
        ),
        body: BuildBody(),
      ),
    );
  }
}

class BuildBody extends StatefulWidget {
  @override
  _BuildBodyState createState() => _BuildBodyState();
}

class _BuildBodyState extends State<BuildBody> {
  DatabaseHelper databaseHelper = DatabaseHelper();

  Future<List<ListModel>> listItems;
  List<ListModel> noteListmain = List<ListModel>();

  TextEditingController titleController = TextEditingController();

  @override
  void initState() {
    super.initState();
    refreshDataList();
  }

  refreshDataList() {
    setState(() {
      getAllData();
    });
  }
  @override
  Widget build(BuildContext context) {

    return Container(
           padding: EdgeInsets.all(20),
              child: Column(
                 mainAxisAlignment: MainAxisAlignment.spaceBetween,
                children: <Widget>[
                   Flexible(
                      flex:4,
                      child: Container(
                        child: Column(
                          children: <Widget>[
                            Text("Title :"),
                            Flexible(
                              child: TextField(
                                controller: titleController,
                              ),
                            ),
                            RaisedButton(
                              child: Text("Add"),
                              onPressed : () {
                                setState(() {                                 
                                  insertData();
                                  FocusScope.of(context).unfocus();
                                });
                              },
                            )
                          ],
                        ),
                      )
                   ),
                   Flexible(
                      flex: 9,
                     child: ListView.builder(
                         itemCount: noteListmain.length,

                         itemBuilder: (BuildContext context, int position){
                           return InkWell(
                             child: Card(
                               color: Colors.white,
                               elevation: 2.0,
                               child: ListTile(
                                 leading: CircleAvatar(
                                   backgroundColor: Colors.black,
                                   child: Icon(Icons.assessment),
                                 ),

                                 title: Text(this.noteListmain[position].title, ),

                                 trailing: GestureDetector(
                                   child: Icon(Icons.delete, color: Colors.grey,),
                                   onTap: () {

                                     deleteData(this.noteListmain[position].id);
                                   },
                                 ),

                               ),
                             ),
                             onTap: ()
                             {
                               updateData(this.noteListmain[position].id, this.noteListmain[position].title);
                              },
                           );
                         }
                     )
                   )
                 ]
              )
       );
    }

  void insertData() async {
    int result;
    String title = titleController.text;
    result = await databaseHelper.insertData(ListModel(title));
    print('inserted row id: $result');
    titleController.text = '';
    refreshDataList();
  }

  void getAllData() async  {
    final noteMapList = await databaseHelper.getDbData();
    setState(() {
      noteListmain = noteMapList;
    });
  }

  void updateData(int id, String title) async{
    bool result = await Navigator.push(context, MaterialPageRoute(builder: (context) {
      return DetailsPage(id, title);
    }));
    if(result == true){
      refreshDataList();
    }
  }

  void deleteData(int itemId) async{
    int result = await databaseHelper.deleteData(itemId);
     refreshDataList();
  }
}

Editing a Data

For editing current title on list item, just tap on the item that you want to edit. Then another page named ‘DetialsPage‘ will open, where editing page codes are included.

DetailsPage.dart

import 'package:flutter/material.dart';
import 'package:myapp/db_helper/model.dart';
import 'package:myapp/db_helper/database_helper.dart';

class DetailsPage extends StatefulWidget {
  final int itemId;
  final String itemTitle;
  DetailsPage(this.itemId,this.itemTitle);
  @override
  _DetailsPageState createState() => _DetailsPageState();
}

class _DetailsPageState extends State<DetailsPage> {

  TextEditingController newTextController = new TextEditingController();
  DatabaseHelper databaseHelper = DatabaseHelper();

  @override
  void initState() {
    super.initState();
    setState(() {
      newTextController.text = widget.itemTitle;
    });
  }

  @override
  Widget build(BuildContext context) {
    return Scaffold(
        appBar: AppBar(
          title: Text("Details Page"),
        ),
        body: Container(
          padding: EdgeInsets.all(20),
          child: Column(
            children: <Widget>[
              Text("Edit title"),
              TextFormField(
                controller: newTextController,
              ),
              Row(
                mainAxisAlignment: MainAxisAlignment.spaceAround,
                children: <Widget>[
                  RaisedButton(
                    color: Theme
                        .of(context)
                        .primaryColorDark,
                    textColor: Theme
                        .of(context)
                        .primaryColorLight,
                    child: Text(
                      'Cancel',
                      textScaleFactor: 1.5,
                    ),
                    onPressed: () {
                      setState(() {
                        Navigator.pop(context, true);
                        //insertData();
                      });
                    },
                  ),
                  RaisedButton(
                    color: Theme
                        .of(context)
                        .primaryColorDark,
                    textColor: Theme
                        .of(context)
                        .primaryColorLight,
                    child: Text(
                      'Save',
                      textScaleFactor: 1.5,
                    ),
                    onPressed: () {
                      setState(() {
                        updateItem(context, widget.itemId, newTextController.text);
                      });
                    },
                  ),
                ],
              )
            ],
          ),
        )
    );
  }

  void updateItem(BuildContext context, int itemId, String newItem) async {
    int result = await databaseHelper.updateData(ListModel.withId(itemId,newItem));
    Navigator.pop(context, true);
  }
}

Database Operations : Create, Select, Insert, Update and Delete

There is a database helper class which manages all database operations such as create database, create table, insert, select, update, delete data, etc.

database_helper.dart

import 'package:sqflite/sqflite.dart';
import 'dart:async';
import 'dart:io';
import 'package:path_provider/path_provider.dart';
import 'package:myapp/db_helper/model.dart';

class DatabaseHelper {

  static DatabaseHelper _databaseHelper;  
  static Database _database;              

  String dbTable = 'data_table';
  String colId = 'id';
  String colTitle = 'title';

  DatabaseHelper._createInstance();

  factory DatabaseHelper() {

    if (_databaseHelper == null) {
      _databaseHelper = DatabaseHelper._createInstance(); // This is executed only once
    }
    return _databaseHelper;
  }
  Future<Database> get database async {

    if (_database == null) {
      _database = await initializeDatabase();
    }
    return _database;
  }

  Future<Database> initializeDatabase() async {
    Directory directory = await getApplicationDocumentsDirectory();
    String path = directory.path + 'data_db.db';
    var notesDatabase = await openDatabase(path, version: 1, onCreate: _createDb);
    return notesDatabase;
  }

  void _createDb(Database db, int newVersion) async {

    await db.execute('CREATE TABLE $dbTable($colId INTEGER PRIMARY KEY AUTOINCREMENT, $colTitle TEXT)');
  }


  Future<List<ListModel>> getDbData() async {

    Database db = await this.database;
    List<Map> maps = await db.query(dbTable, orderBy: '$colId ASC');
    List<ListModel> noteList = List<ListModel>();
    if (maps.length > 0) {
      for (int i = 0; i < maps.length; i++) {

       noteList.add(ListModel.fromMapObject(maps[i]));
      }
    }
    return noteList;
  }

  Future<List<Map<String, dynamic>>> getNoteMapList() async {
    Database db = await this.database;
    var result = await db.query(dbTable, orderBy: '$colId ASC');
    return result;
  }
 
  Future<int> insertData(ListModel data) async {
    Database db = await this.database;
    var result = await db.insert(dbTable, data.toMap());
    return result;
  }

  Future<int> updateData(ListModel data) async {
    var db = await this.database;
    var result = await db.update(dbTable, data.toMap(), where: '$colId = ?', whereArgs: [data.id]);
    return result;
  }

  Future<int> deleteData(int id) async {
    var db = await this.database;
    int result = await db.rawDelete('DELETE FROM $dbTable WHERE $colId = $id');
    return result;
  }

}

Model Class

There is a model class which keeps the data format and convert data normal data to Map object and vice versa.

While inserting data to database you need convert to Map object. As well as while retrieving data from database you need to convert from Map object to Data List.

model.dart

class ListModel {

  int id;
  String title;

  ListModel(this.title);
  ListModel.withId(this.id, this.title,);


  // Converting a data list object into a Map object
  Map<String, dynamic> toMap() {
    var map = Map<String, dynamic>();
    map['title'] = title;
    return map;
  }

  // Extract a Data List object from a Map object
  ListModel.fromMapObject(Map<String, dynamic> map) {
    this.id = map['id'];
    this.title = map['title'];
  }
}

Project File Structure

In Flutter project view section, under ‘lib’ we have created main.dart, Detailspage.dart and a directory named ‘db_helper’. Which contains database_helper and model class.

Sample Output

Here under title, we can add title by typing text inside the textField. Under this section, you can view all the items that you have added already. Delete button will delete selected item from the list. If you tap on an item, you will directed to details page, here you can edit the current item title.

Be First to Comment

Leave a Reply