SQFlite: An Ultimate Guide to Flutter Database

SQFlite package provides an easy interface to interact with SQLite Database in order to store persistent data in Flutter apps.

In this tutorial, We are going to see flutter database example tutorial in detail by developing a very basic To-Do Task Flutter Application.

1. To-Do Task App Overview

Our To-Do App will have two screens, one is for creating and editing tasks and another is for listing our saved tasks.

Our To-Do App will have CRUD (Create, Read, Update, Delete) features so that we can add and delete our tasks easily

At the end of this flutter database tutorial, we will be able to develop the following app as shown in the screenshots below:

flutter database
Screen 1
flutter database
Screen 2

2. App Prerequisite

Before we start developing our app, I would love to list all the required packages used in this app.

Basically, we are using three different packages in our project.

  • SQFlite : This package is used to interact with SQLite database.
  • path_provider : This package is used for getting commonly used locations in the Android and iOS file systems.
  • intl: This package deals with date and time formatting, localization messages etc.

3. Creating Flutter Project

Throughout this tutorial, I will be using VS Code Editor. You are always free to use any editor of your choice!

In order to create new flutter project,

  1. Press Ctrl+Shift+P, Command palette will open.
  2. Select Flutter: New Project option.
  3. Give a suitable name to the project, in my case, its todo_app in all lower cases.
  4. Choose a directory to store your project and click on OK.

Now, you will have fresh flutter project. Make sure that your main.dart file looks like this:

import 'package:flutter/material.dart';
import './screens/task_list.dart';

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

class MyApp extends StatelessWidget {

	@override
  Widget build(BuildContext context) {

    return MaterialApp(
	    title: 'Todo App',
	    debugShowCheckedModeBanner: false,
	    theme: ThemeData(
		    primarySwatch: Colors.deepPurple
	    ),
	    home: TaskList(),
    );
  }
}

Let’s move to the next step.

4. Installing Required Packages

We have already seen the packages that are required for our project.

In this step, we are going to download them in our project so that we can use them.

STEP 1: Copy the following text one by one and paste into your pubspec.yaml file as shown in the screenshot below.

sqflite: any
path_provider: any
intl: 
flutter database
pubspec.yaml

STEP 2: Now Press Ctrl+S or get Package, and now your packages will be downloaded.

5. Building App UI

As I have mentioned earlier that our app will have only two screens. So, Lets build them.

We will be placing our screens in separate folder so, create a new directory inside lib folder named screens.

5.1 Building Task List Screen

flutter database
Tasks List

STEP 1: Create a file called task_list.dart inside screens folder.

STEP 2: Create a Stateful Widget called TaskList as shown in the code below:

class TaskList extends StatefulWidget {

	@override
  State<StatefulWidget> createState() {

    return TaskListState();
  }
}

class TaskListState extends State<TaskList> {

	DatabaseHelper databaseHelper = DatabaseHelper();
	List<Task> taskList;
	int count = 0;

	@override
  Widget build(BuildContext context) {

		if (taskList == null) {
			taskList = List<Task>();
			updateListView();
		}

    return Scaffold(

	    appBar: AppBar(
		    title: Text('Tasks'),
	    ),

	    body: getTaskListView(),

	    floatingActionButton: FloatingActionButton(
		    onPressed: () {
		      debugPrint('FAB clicked');
		      navigateToDetail(Task('', '', 2), 'Add Task');
		    },

		    tooltip: 'Add Task',

		    child: Icon(Icons.add),

	    ),
    );
  }

STEP 3: In order to get all the list view of tasks, write the following code:

ListView getTaskListView() {

		TextStyle titleStyle = Theme.of(context).textTheme.subhead;

		return ListView.builder(
			itemCount: count,
			itemBuilder: (BuildContext context, int position) {
				return Card(
					color: Colors.white,
					elevation: 2.0,
					child: ListTile(

						leading: CircleAvatar(
							backgroundColor: getPriorityColor(this.taskList[position].priority),
							child: getPriorityIcon(this.taskList[position].priority),
						),

						title: Text(this.taskList[position].title, style: titleStyle,),

						subtitle: Text(this.taskList[position].date),

						trailing: GestureDetector(
							child: Icon(Icons.delete, color: Colors.grey,),
							onTap: () {
								_delete(context, taskList[position]);
							},
						),


						onTap: () {
							debugPrint("ListTile Tapped");
							navigateToDetail(this.taskList[position],'Edit Task');
						},

					),
				);
			},
		);
  }

Step 4: Since, we have priorities in our tasks as High and Low, We will set different colors for these priorities. So let’s write helper function for it.

// Returns the priority color
	Color getPriorityColor(int priority) {
		switch (priority) {
			case 1:
				return Colors.red;
				break;
			case 2:
				return Colors.green;
				break;

			default:
				return Colors.green;
		}
	}

Step 5: As we can see above screenshot, we have different icons for different priorities, so lets make helper function for it.

// Returns the priority icon
	Icon getPriorityIcon(int priority) {
		switch (priority) {
			case 1:
				return Icon(Icons.assignment_late);
				break;
			case 2:
				return Icon(Icons.assignment);
				break;

			default:
				return Icon(Icons.assignment);
		}
	}

STEP 6: Now let’s write the code to delete the single task when the user taps the delete button.

void _delete(BuildContext context, Task task) async {

		int result = await databaseHelper.deleteTask(task.id);
		if (result != 0) {
			_showSnackBar(context, 'Task Deleted Successfully');
			updateListView();
		}
	}

If you notice here, we are not actually deleting the task here. We are just calling to the deletetTask() method, which will actually delete it. We will see the actual delete task later in the database section.

STEP 7: Once we have deleted the task, we need to show some messages to the user. For this purpose we are going to write code for the snackbar as follows:

	void _showSnackBar(BuildContext context, String message) {

		final snackBar = SnackBar(content: Text(message));
		Scaffold.of(context).showSnackBar(snackBar);
	}

STEP 8: After the delete operation is performed successfully, there must be a way to refresh/update the list so that the deleted item disappears from the list. For this purpose, we have the updateListView() method.

  void updateListView() {

		final Future<Database> dbFuture = databaseHelper.initializeDatabase();
		dbFuture.then((database) {

			Future<List<Task>> taskListFuture = databaseHelper.getTaskList();
			taskListFuture.then((taskList) {
				setState(() {
				  this.taskList = taskList;
				  this.count = taskList.length;
				});
			});
		});
  }

STEP 9: Now, when we click on any of the task, we must be able to see the details of the task. For this purpose, we will right navigator function to navigate to detail page:

  void navigateToDetail(Task task, String title) async {
	  bool result = await Navigator.push(context, MaterialPageRoute(builder: (context) {
		  return TaskDetail(task, title);
	  }));

	  if (result == true) {
	  	updateListView();
	  }
  }

Now, we have successfully completed all the code for the lib/screens/task_list.dart page.

Full code for this task_list.dart page:

import 'dart:async';
import 'package:flutter/material.dart';
import '../models/task.dart';
import '../utils/db_helper.dart';
import '../screens/task_detail.dart';
import 'package:sqflite/sqflite.dart';
class TaskList extends StatefulWidget {
@override
State<StatefulWidget> createState() {
return TaskListState();
}
}
class TaskListState extends State<TaskList> {
DatabaseHelper databaseHelper = DatabaseHelper();
List<Task> taskList;
int count = 0;
@override
Widget build(BuildContext context) {
if (taskList == null) {
taskList = List<Task>();
updateListView();
}
return Scaffold(
appBar: AppBar(
title: Text('Tasks'),
),
body: getTaskListView(),
floatingActionButton: FloatingActionButton(
onPressed: () {
debugPrint('FAB clicked');
navigateToDetail(Task('', '', 2), 'Add Task');
},
tooltip: 'Add Task',
child: Icon(Icons.add),
),
);
}
ListView getTaskListView() {
TextStyle titleStyle = Theme.of(context).textTheme.subhead;
return ListView.builder(
itemCount: count,
itemBuilder: (BuildContext context, int position) {
return Card(
color: Colors.white,
elevation: 2.0,
child: ListTile(
leading: CircleAvatar(
backgroundColor: getPriorityColor(this.taskList[position].priority),
child: getPriorityIcon(this.taskList[position].priority),
),
title: Text(this.taskList[position].title, style: titleStyle,),
subtitle: Text(this.taskList[position].date),
trailing: GestureDetector(
child: Icon(Icons.delete, color: Colors.grey,),
onTap: () {
_delete(context, taskList[position]);
},
),
onTap: () {
debugPrint("ListTile Tapped");
navigateToDetail(this.taskList[position],'Edit Task');
},
),
);
},
);
}
// Returns the priority color
Color getPriorityColor(int priority) {
switch (priority) {
case 1:
return Colors.red;
break;
case 2:
return Colors.green;
break;
default:
return Colors.green;
}
}
// Returns the priority icon
Icon getPriorityIcon(int priority) {
switch (priority) {
case 1:
return Icon(Icons.assignment_late);
break;
case 2:
return Icon(Icons.assignment);
break;
default:
return Icon(Icons.assignment);
}
}
void _delete(BuildContext context, Task task) async {
int result = await databaseHelper.deleteTask(task.id);
if (result != 0) {
_showSnackBar(context, 'Task Deleted Successfully');
updateListView();
}
}
void _showSnackBar(BuildContext context, String message) {
final snackBar = SnackBar(content: Text(message));
Scaffold.of(context).showSnackBar(snackBar);
}
void navigateToDetail(Task task, String title) async {
bool result = await Navigator.push(context, MaterialPageRoute(builder: (context) {
return TaskDetail(task, title);
}));
if (result == true) {
updateListView();
}
}
void updateListView() {
final Future<Database> dbFuture = databaseHelper.initializeDatabase();
dbFuture.then((database) {
Future<List<Task>> taskListFuture = databaseHelper.getTaskList();
taskListFuture.then((taskList) {
setState(() {
this.taskList = taskList;
this.count = taskList.length;
});
});
});
}
}

5.2 Building Task Detail Screen

flutter database
Create/Edit/Detail Screen

STEP 1: Create a file named task_detail.dart inside screens folder as lib/screens/task_detail.dart.

This screen will be responsible for displaying the task details and also update the task.

STEP 2: Now again lets create a stateful widget named TaskDetail as shown below:

class TaskDetail extends StatefulWidget {
final String appBarTitle;
final Task task;
TaskDetail(this. task, this.appBarTitle);
@override
State<StatefulWidget> createState() {
return TaskDetailState(this.task, this.appBarTitle);
}
}
class TaskDetailState extends State<TaskDetail> {
static var _priorities = ['High', 'Low'];
DatabaseHelper helper = DatabaseHelper();
String appBarTitle;
Task task;
TextEditingController titleController = TextEditingController();
TextEditingController descriptionController = TextEditingController();
TaskDetailState(this.task, this.appBarTitle);
@override
Widget build(BuildContext context) {
TextStyle textStyle = Theme.of(context).textTheme.title;
titleController.text = task.title;
descriptionController.text = task.description;
return WillPopScope(
onWillPop: () {
// Write some code to control things, when user press Back navigation button in device navigationBar
moveToLastScreen();
},
child: Scaffold(
appBar: AppBar(
title: Text(appBarTitle),
leading: IconButton(icon: Icon(
Icons.arrow_back),
onPressed: () {
// Write some code to control things, when user press back button in AppBar
moveToLastScreen();
}
),
),
body: Padding(
padding: EdgeInsets.only(top: 15.0, left: 10.0, right: 10.0),
child: ListView(
children: <Widget>[
// First element
ListTile(
title: DropdownButton(
items: _priorities.map((String dropDownStringItem) {
return DropdownMenuItem<String> (
value: dropDownStringItem,
child: Text(dropDownStringItem),
);
}).toList(),
style: textStyle,
value: getPriorityAsString(task.priority),
onChanged: (valueSelectedByUser) {
setState(() {
debugPrint('User selected $valueSelectedByUser');
updatePriorityAsInt(valueSelectedByUser);
});
}
),
),
// Second Element
Padding(
padding: EdgeInsets.only(top: 15.0, bottom: 15.0),
child: TextField(
controller: titleController,
style: textStyle,
onChanged: (value) {
debugPrint('Something changed in Title Text Field');
updateTitle();
},
decoration: InputDecoration(
labelText: 'Title',
labelStyle: textStyle,
border: OutlineInputBorder(
borderRadius: BorderRadius.circular(5.0)
)
),
),
),
// Third Element
Padding(
padding: EdgeInsets.only(top: 15.0, bottom: 15.0),
child: TextField(
controller: descriptionController,
style: textStyle,
onChanged: (value) {
debugPrint('Something changed in Description Text Field');
updateDescription();
},
decoration: InputDecoration(
labelText: 'Description',
labelStyle: textStyle,
border: OutlineInputBorder(
borderRadius: BorderRadius.circular(5.0)
)
),
),
),
// Fourth Element
Padding(
padding: EdgeInsets.only(top: 15.0, bottom: 15.0),
child: Row(
children: <Widget>[
Expanded(
child: RaisedButton(
color: Theme.of(context).primaryColorDark,
textColor: Theme.of(context).primaryColorLight,
child: Text(
'Save',
textScaleFactor: 1.5,
),
onPressed: () {
setState(() {
debugPrint("Save button clicked");
_save();
});
},
),
),
Container(width: 5.0,),
Expanded(
child: RaisedButton(
color: Theme.of(context).primaryColorDark,
textColor: Theme.of(context).primaryColorLight,
child: Text(
'Delete',
textScaleFactor: 1.5,
),
onPressed: () {
setState(() {
debugPrint("Delete button clicked");
_delete();
});
},
),
),
],
),
),
],
),
),
));
}
void moveToLastScreen() {
Navigator.pop(context, true);
}
// Convert the String priority in the form of integer before saving it to Database
void updatePriorityAsInt(String value) {
switch (value) {
case 'High':
task.priority = 1;
break;
case 'Low':
task.priority = 2;
break;
}
}
// Convert int priority to String priority and display it to user in DropDown
String getPriorityAsString(int value) {
String priority;
switch (value) {
case 1:
priority = _priorities[0];  // 'High'
break;
case 2:
priority = _priorities[1];  // 'Low'
break;
}
return priority;
}
// Update the title of Task object
void updateTitle(){
task.title = titleController.text;
}
// Update the description of Task object
void updateDescription() {
task.description = descriptionController.text;
}
// Save data to database
void _save() async {
moveToLastScreen();
task.date = DateFormat.yMMMd().format(DateTime.now());
int result;
if (task.id != null) {  // Case 1: Update operation
result = await helper.updateTask(task);
} else { // Case 2: Insert Operation
result = await helper.insertTask(task);
}
if (result != 0) {  // Success
_showAlertDialog('Status', 'Task Saved Successfully');
} else {  // Failure
_showAlertDialog('Status', 'Problem Saving Task');
}
}
void _delete() async {
moveToLastScreen();
// Case 1: If user is trying to delete the NEW Task i.e. he has come to
// the detail page by pressing the FAB of TaskList page.
if (task.id == null) {
_showAlertDialog('Status', 'No Task was deleted');
return;
}
// Case 2: User is trying to delete the old task that already has a valid ID.
int result = await helper.deleteTask(task.id);
if (result != 0) {
_showAlertDialog('Status', 'Task Deleted Successfully');
} else {
_showAlertDialog('Status', 'Error Occured while Deleting Task');
}
}
void _showAlertDialog(String title, String message) {
AlertDialog alertDialog = AlertDialog(
title: Text(title),
content: Text(message),
);
showDialog(
context: context,
builder: (_) => alertDialog
);
}
}

Let me explain each method inside the code above:

STEP 3: moveToLastScreen() method help us to navigate to the last screen i.e. Task List Screen by poping the current screen.

STEP 4: updatePriorityAsInt() method helps us to convert our string priorites from Hight => 1 and Low => 2 before saving into the database.

STEP 5: updatePriorityAsString() method does exactly opposite of updatePriorityAsInt() method. This method is used to display priorities to the user.

STEP 6: updateTitle() and updateDescription() methods are used to update the title and description of the particular task.

STEP 7: _save() and _delete() methods are used to save and delete the current opened task.

STEP 8: _showAlertDialog() method is used to show the messages while saving and deleting the task as mentioned in the previous step 7.

So far, we have created all the necessary UI components for our ToDo task app.

Feeling Bored! Still, a long way to go. Now, we are going to see the main part of this flutter database tutorial, i.e. the setting up the database section.

6. Working with Flutter Database

If you have made up this point in this tutorial, then I must congratulate you because now we are going to set up our SQLite database and perform the CRUD operation.

6.1 Creating Flutter Database Model Class

Database Model Class represents the logical structure of our single task in a database. It consists of all the properties of a single task.

For Example, Our single task has a title, a description, date and a priority. It should also contain getter and setter methods for getting and setting the values of the above parameters.

To create a Model class, at first create a new folder named models inside the lib folder as lib/models.

STEP 1: Create a new file named task.dart as a model class inside the models folder.

class Task {
int _id;
String _title;
String _description;
String _date;
int _priority;
Task(this._title, this._date, this._priority, [this._description]);
Task.withId(this._id, this._title, this._date, this._priority, [this._description]);
int get id => _id;
String get title => _title;
String get description => _description;
int get priority => _priority;
String get date => _date;
set title(String newTitle) {
if (newTitle.length <= 255) {
this._title = newTitle;
}
}
set description(String newDescription) {
if (newDescription.length <= 255) {
this._description = newDescription;
}
}
set priority(int newPriority) {
if (newPriority >= 1 && newPriority <= 2) {
this._priority = newPriority;
}
}
set date(String newDate) {
this._date = newDate;
}
// Convert a Task object into a Map object
Map<String, dynamic> toMap() {
var map = Map<String, dynamic>();
if (id != null) {
map['id'] = _id;
}
map['title'] = _title;
map['description'] = _description;
map['priority'] = _priority;
map['date'] = _date;
return map;
}
// Extract a Task object from a Map object
Task.fromMapObject(Map<String, dynamic> map) {
this._id = map['id'];
this._title = map['title'];
this._description = map['description'];
this._priority = map['priority'];
this._date = map['date'];
}
}

6.2 Creating Flutter Database Helper Class

Database Helper class is a class which provides us interface to interact with our SQLite database in flutter.

This is the file where we create, initialize and use the database. This place also used for opening and closing the database.

STEP 1: In order to create the helper class, create a new folder called utils inside the lib folder as lib/utils.

STEP 2: Now create a database helper file as db_helper.dart. You can give any filename as you wish.

STEP 3: Inside this file, we will import the SQFlite and Path Provider plugin as:

import 'package:sqflite/sqflite.dart';
import 'package:path_provider/path_provider.dart';

STEP 4: Create a DatabaseHelper class as follows:

class DatabaseHelper {
static DatabaseHelper _databaseHelper;    // Singleton DatabaseHelper
static Database _database;                // Singleton Database
String taskTable = 'task_table';
String colId = 'id';
String colTitle = 'title';
String colDescription = 'description';
String colPriority = 'priority';
String colDate = 'date';
DatabaseHelper._createInstance(); // Named constructor to create instance of DatabaseHelper
factory DatabaseHelper() {
if (_databaseHelper == null) {
_databaseHelper = DatabaseHelper._createInstance(); // This is executed only once, singleton object
}
return _databaseHelper;
}
Future<Database> get database async {
if (_database == null) {
_database = await initializeDatabase();
}
return _database;
}
Future<Database> initializeDatabase() async {
// Get the directory path for both Android and iOS to store database.
Directory directory = await getApplicationDocumentsDirectory();
String path = directory.path + 'tasks.db';
// Open/create the database at a given path
var tasksDatabase = await openDatabase(path, version: 1, onCreate: _createDb);
return tasksDatabase;
}
void _createDb(Database db, int newVersion) async {
await db.execute('CREATE TABLE $taskTable($colId INTEGER PRIMARY KEY AUTOINCREMENT, $colTitle TEXT, '
'$colDescription TEXT, $colPriority INTEGER, $colDate TEXT)');
}
// Fetch Operation: Get all task objects from database
Future<List<Map<String, dynamic>>> getTaskMapList() async {
Database db = await this.database;
//		var result = await db.rawQuery('SELECT * FROM $taskTable order by $colPriority ASC');
var result = await db.query(taskTable, orderBy: '$colPriority ASC');
return result;
}
// Insert Operation: Insert a Task object to database
Future<int> insertTask(Task task) async {
Database db = await this.database;
var result = await db.insert(taskTable, task.toMap());
return result;
}
// Update Operation: Update a Task object and save it to database
Future<int> updateTask(Task task) async {
var db = await this.database;
var result = await db.update(taskTable, task.toMap(), where: '$colId = ?', whereArgs: [task.id]);
return result;
}
// Delete Operation: Delete a Task object from database
Future<int> deleteTask(int id) async {
var db = await this.database;
int result = await db.rawDelete('DELETE FROM $taskTable WHERE $colId = $id');
return result;
}
// Get number of Task objects in database
Future<int> getCount() async {
Database db = await this.database;
List<Map<String, dynamic>> x = await db.rawQuery('SELECT COUNT (*) from $taskTable');
int result = Sqflite.firstIntValue(x);
return result;
}
// Get the 'Map List' [ List<Map> ] and convert it to 'Task List' [ List<Task> ]
Future<List<Task>> getTaskList() async {
var taskMapList = await getTaskMapList(); // Get 'Map List' from database
int count = taskMapList.length;         // Count the number of map entries in db table
List<Task> taskList = List<Task>();
// For loop to create a 'Task List' from a 'Map List'
for (int i = 0; i < count; i++) {
taskList.add(Task.fromMapObject(taskMapList[i]));
}
return taskList;
}
}

6.3 Performing CRUD Operation

From the above DatabaseHelper class, Now we will write the methods to perform CRUD operations.

6.3.1 CREATE Operation

In this operation, we are going to create a new task as follows:

// Insert Operation: Insert a Task object to database
Future<int> insertTask(Task task) async {
Database db = await this.database;
var result = await db.insert(taskTable, task.toMap());
return result;
}

In the above code, when the user press SAVE button after adding all the details of the task, this insertTask() method is triggered which calls the SQLite insert method. This will insert a new task into the database.

6.3.2 READ Operation

void navigateToDetail(Task task, String title) async {
bool result = await Navigator.push(context, MaterialPageRoute(builder: (context) {
return TaskDetail(task, title);
}));
if (result == true) {
updateListView();
}
}

We have already seen this operation in the Task details page.

6.3.3 UPDATE Operation

// Update Operation: Update a Task object and save it to database
Future<int> updateTask(Task task) async {
var db = await this.database;
var result = await db.update(taskTable, task.toMap(), where: '$colId = ?', whereArgs: [task.id]);
return result;
}

This method is triggered when the user opens the tasks in the detail page and press SAVE button. When save button is pressed, it calls the SQLite update method with the task id.

6.3.4 DELETE Operation

// Delete Operation: Delete a Task object from database
Future<int> deleteTask(int id) async {
var db = await this.database;
int result = await db.rawDelete('DELETE FROM $taskTable WHERE $colId = $id');
return result;
}

Here, when the user presses the Delete button either in the task list page or in the detail page, this method is triggered. This method will call the SQLite delete method.

If you have noticed, All our operation is returning a Future object of type int. It is because, we are using the same form to Insert and Update the tasks. So, If you remember earlier we are checking if there is task id available perform Update operation otherwise perform Insert Operation.

And the Future object ensures that your app doesn’t just freeze while doing some operation i.e. it can perform asynchronous operation and expects a result in Future when the task is completed.

7. Final Words

Congratulation! we have just completed the Flutter Database tutorial which demonstrate the flutter database example tutorial with ToDo Task App by using SQFlite database plugin.

I hope you liked it. If you have anything to ask or say. Let me know in the comment section.

8. What’s Next?

Also Read:

  1. Flutter WebView Tutorial: Convert your website in Mobile App
  2. 10 Widgets Every Flutter Developer Should Know!
  3. Creating Dynamic Long List in Flutter