Complete SQLite CRUD Operations in Flutter

Introduction

Now a days almost all application have to have some kind of data storage. Application without collaboration with other users will make use of local storage db – SQLite. In this tutorial, we are going to cover all CRUD operations in Flutter with SQLite.

The design part of the project already covered in previous tutorial – Design Flutter Form and ListView Widget for CRUD Operation. It includes form design and list of records inserted from the form, where we saved list of records in widget state object. So in this tutorial we can focus on SQLite CRUD Operations and finally we update existing events with SQLite CRUD Operation. Application from previous tutorial looks like this.

Let’s get Started

First of all, let’s add dependencies for this project, for that update pubspec.yaml file as follows.

dependencies:
  flutter:
    sdk: flutter
  sqflite: any
  path_provider: any

Additionally here we’ve two packages sqflite and path_provider . sqflite helps to work with SQLite database. To find commonly used locations on file system, we use  path_provider package.

Caution: When it comes to yaml files, indentation is very important. Before the package name we need 2 spaces and before version name there should be a space. Instead of exact version we use 'any' , so latest version will be used.

Now let’s update contact model – models/contact.dart .

First of all, you could see few string constance with table name and column names. In this application, we are dealing with contact information including full name and mobile number. Other than from a normal constructor, we have a named constructor fromMap for initializing an instance from a given map object. And with toMap function, we will convert contact object to the corresponding map object.

why map object?

In SQLite database, records are stored as a collection of map objects. In case if you don’t know – basically map is a <key, value> pair. for example –

So before inserting a record into SQLite database file, we’ve to convert the record into corresponding map object. While retrieving records from the db, it returns collection of map objects.

Getting Started with Database

To handle database related operations, we’ve created a new file – util/database_helper.dart.

Here we’ve a class DatabaseHelper with a private named constructor _() . here the name of this constructor is _ , yes it is possible. There is a final static property instance of the type – DatabaseHelper , which is initialized with _() constructor. Because of this final property, it can’t change it’s value after initialization. Hence this is a singleton class (Meaning only one instance of the class is created in through out the app). There is public getter- database , which returns the Database instance. If _database is not initialized _initDatabase function will be called.

Inside _initDatabse() , dataDirectory is initialized with getApplicationDocumentsDirectory function, which is from path_provider package. Basically this function returns the location where database is stored. which is different for android and ios operating system. But it’s handled by the path_provider package. The path is joined with database name as our database file path. To connect and return the database reference openDatabase function is called. version property is just to indicate database version not actual SQLite version. The version is compared within source code with the version in actual physical db. If there is a difference. we can go for upgrade or downgrade process. we are not going to discuss that in this tutorial.

If there is no database with the given db path, we’ve to create the database schema with all the tables. That’s what we’ve done with onCreate property function _onCreateDB() . Inside the function we create contact table.

SQLite CRUD Operations

Now let’s add these function to DatabaseHelper class for CRUD Operations – insert, update, delete and retrieve.

With sqflite package, there are two kind of function one with raw query and a direct function with all required parameters without query. For example, for insert operation we called direct function insert() . instead of that you can also call rawInsert() with raw insert query like ‘insert into contact values …’. Before inserting the record we have to convert the contact object to corresponding map object.

For update and delete operation, where and whereArgs parameters are used to pass additional filter operation. To retrieve all the records from contact table, we called the function query() . The function returns, a list of map objects. Finally we converted the List<Map> into List<Contact> .

Wire up All events

So far we only touched Contact model and DatabaseHelper class files. Now let’s consume these function inside widget events. As I mentioned before, the UI of this app is discussed in previous article here. First of let’s look how to retrieve all contact records from the contact table.

We’ve imported the DatabaseHelper class at the top. Inside MyHomePage widget, we’ve overridden initState method, it will be called once when widget state objects are created. The function can be used to perform initialization of dependent properties/ objects. After calling parent method – super.initState() , _dbHelper instance is initialized with final instance property. After that, to retrieve all of the records from contact table we’ve called the function _refreshContactList() . Inside the function we’ve called the function, fetchContacts() is called to return list of contact records and it is assigned to _contacts state object.

Caution: If you already running your app, stop and re-run the app. because the initState() method won’t be called with Hot-Reload.

Now let’s update form submit event function _onSubmit() . Previously we’ve been adding the contact information to _contacts list directly. Instead of that, we called the insertContact() method to insert the record into SQLite DB.

Edit and Delete Operation

First of all, let’s implement edit/update operation. When user tap on ListTile widget, corresponding contact information should populated to respective text-field. Then user can make required changes and followed by form submission. The event can be implemented with _showForEdit() function as below.

class _MyHomePageState extends State {
  
  final _ctrlName = TextEditingController();
  final _ctrlMobile = TextEditingController();
  ...

  ListTile(
    ...,
    onTap: () {
    _showForEdit(index);
    },
  )
  
  _form => Container(
    TextFormField(
    controller: _ctrlName,
    ...
    ),
    TextFormField(
    controller: _ctrlName,
    ...
    ),
    ...
  )

  _showForEdit(index) {
    setState(() {
      _contact = _contacts[index];
      _ctrlName.text = _contacts[index].name;
      _ctrlMobile.text = _contacts[index].mobile;
    });
  }
}

Initially, you could see two TextEditionController , _ctrlName and _ctrlMobile . These properties are assigned to respective text-field controller property. Then only we can change the field value inside the tap event. _showForEdit() function is wired up to the ListTile – onTap event. Inside the function, we change _contact object and text-field values with selected record details.

Now let’s do the update operation inside the form submit event. where we’ve already implemented the insert operation. since we’ve update _contact object inside _showForEdit() function. here we can check the id of record. based on that we decide whether we’ve an insert or update operation.

_onSubmit() async {
 ...
 if (_contact.id == null)
  await _dbHelper.insertContact(_contact);
 else
  await _dbHelper.updateContact(_contact);
 _resetForm();
 ...
}

Since we change text-field value through TextEditingController , we have to clear the controller separately after form reset operation. so new function _resetForm() is defined for reset operation. along with usual procedure for a form reset operation, we have set the id to null. so the form is cleared for an insert operation.

_resetForm() {
  setState(() {
    _formKey.currentState.reset();
    _ctrlName.clear();
    _ctrlMobile.clear();
    _contact.id = null;
  });
}

Delete Operation

For delete operation, we’ve to add a delete button inside ListTilewidget.

ListTile(
...
trailing: IconButton(
    icon: Icon(Icons.delete_sweep, color: darkBlueColor),
    onPressed: () async {
      await _dbHelper.deleteContact(_contacts[index].id);
      _resetForm();
      _refreshContactList();
    }),
...
)

Inside the onPressed event, we’ve deleted the corresponding record.

That’s it guys. Hope you found this helpful.