Use SQFLite – the SQLite plugin for Flutter to persist data locally in Flutter apps, enabling offline app usage.
Storing data to the local disk is a popular demand when it comes to offline apps like Google Keep, or apps that need to cache the latest data such that it’s still partially accessible in case the device is disconnected from the internet.
SQFLite is the SQLite plugin for Flutter that allows large and complex data to be stored to the local disk in a structured format, such that it makes it easy for the developers to perform CRUD operations on this data.
This tutorial will teach you how to integrate SQFLite in any existing application to perform CRUD operations on local data.
For the purpose of this tutorial, we will see SQFLite in action with a dream journal app that allows you to:
- record a dream as soon as you wake up,
- view all the dream entries you’ve made as yet,
- edit any particular dream entry later while you’re reviewing it,
- delete a specific dream entry, and
- search dream entries on the basis of keywords.
App Architecture
As seen in the gif above, our Dream Journal app has a total of 3 screens:
Screen 1: a list of all entries along with a floating action button to create a new journal entry, and each entry has a X button to delete that specific entry.
Screen 2: a form for the journal entry used to either record a new dream or to view an existing dream and edit it.
Screen 3: a search screen to look up dream entries containing that specific keyword in their titles.
I’m using BLoC for State Management here, but it’s etirely up to you to use whichever state management technique you prefer, depending on your use case.
This tutorial will only focus on the aspects of SQFLite – to keep things simple.
Getting Started
First off, add the following dependencies in your pubspec.yaml:
dependencies: sqflite: ^1.3.1+1 path: ^1.6.4
sqflite is the Flutter plugin for SQLite and will be used to create/open our locally embedded database and perform CRUD operations on it,
and
path is the plugin responsible for physically embedding the database to the local disk.
If you run into any errors relating to version conflicts while running flutter pub get, upgrade or downgrade the version of the plugin throwing this error to make it consistent with the current version of your flutter sdk.
Creating The Dream Class
Next, create a simple Dream class with the attributes you want to record and store in the database:
class Dream {
int id;
String title;
String details;
Dream({this.id, this.title, this.details});
/// serialise object when writing to database
Map<String, dynamic> toMap() {
var map = <String, dynamic>{
DatabaseProvider.COLUMN_ID: id,
DatabaseProvider.COLUMN_TITLE: title,
DatabaseProvider.COLUMN_DETAILS: details,
};
return map;
}
/// deserialise object when reading from database
Dream.fromMap(Map<String, dynamic> map) {
id = map[DatabaseProvider.COLUMN_ID];
title = map[DatabaseProvider.COLUMN_TITLE];
details = map[DatabaseProvider.COLUMN_DETAILS];
}
}
Each dream entry that we create for our journal will have a unique id, title and dream details that we want to record.
The toMap() method converts our Dream object to a Map object, so it can be stored in our database,
and
the fromMap() method converts the Map object that we retrieve from the database into the actual Dream object which will then be displayed in its respective UI.
Simply put, toMap() will handle the serialisation of our Dream object, and fromMap() will handle the deserialisation.
Also note the use of constants in place of each of the keys in our Map in the toMap() and fromMap() methods.
Let’s set these up next!
Creating The Database Helper Class
Create a new file db_provider.dart. This will contain our DatabaseProvider class – the class responsible for performing all operations related to our database.
import 'models.dart'; // the file that contains our Dream class
import 'package:path/path.dart';
import 'package:sqflite/sqflite.dart';
import 'package:sqflite/sqlite_api.dart';
class DatabaseProvider {
static const String TABLE_DREAM = 'dream';
static const String COLUMN_ID = 'id';
static const String COLUMN_TITLE = 'title';
static const String COLUMN_DETAILS = 'details';
}
Our database will have one table called dream, and this table will have 3 fields; namely id, title and details because these are the 3 fields that we want to record for every dream entry in our journal.
Hence, we created the 4 constants TABLE_DREAM, COLUMN_ID, COLUMN_TITLE and COLUMN_DETAILS to avoid typing errors when we write our database queries or perform serialisation/deserialisation in our Dream class.
Instead of manually typing dream everytime we want to query on our dream table, we can simply use the TABLE_DREAM constant in its place.
Next, add the following lines to your DatabaseProvider class:
// make this a singleton class
DatabaseProvider._();
static final DatabaseProvider db = DatabaseProvider._();
// single reference to the database throughout the app
Database _database;
Future<Database> get database async {
print('database getter called');
if (_database != null) {
return _database;
}
_database = await createDatabase();
return _database;
}
The DatabaseProvider class needs to be a singleton class, so we can create just one object of this class and use its state globally throughout the app whenever we want to use one of its methods.
The database getter will check if there’s any previously created instance of the Database object.
If it exists, that instance will be returned. If it doesn’t exist, it will create it using the createDatabase() method:
Future<Database> createDatabase() async {
String dbPath = await getDatabasesPath();
return await openDatabase(
join(dbPath, 'dreamDB.db'),
version: 1,
onCreate: (Database database, int version) async {
print('Creating dream table');
await database.execute(
"CREATE TABLE $TABLE_DREAM ("
"$COLUMN_ID INTEGER PRIMARY KEY,"
"$COLUMN_TITLE TEXT,"
"$COLUMN_DETAILS TEXT"
")",
);
},
);
}
The getDatabasesPath() is a method provided by the sqflite plugin which returns the default databases location.
On Android, it’s usually data/data//databases
and on iOS, it’s the Documents directory.
The openDatabase() is also a method provided by the sqflite plugin which then uses the dbPath to create our database if it doesn’t already exist and returns a singleton instance to it that we can use in our queries. If the said database already exists, this method simply returns a singleton instance to it.
Let’s look at this method step by step:
- We first pass the full path to our database location by using the
join()method from thepathplugin we initially added as a dependency.
By providingdbPathand the name of our database – dreamDB.db to this method, thepathplugin joins the 2 and returns the final location of our database.
NOTE: name of the database should always have a .db extension.
- The
versionparameter specifies the schema version of the database being opened. This is useful when we make schema changes to our database as this parameter indicates whetheronCreate,onUpgradeoronDowngradeneed to be called. - The
onCreateparameter creates the tables in our database when our database is being created. Note the syntax of creating our dream table. Theidattribute of ourDreamobject is the primary key of this table, whereas the other 2 attributes which were of typeStringare denoted as typeTextin our database.
Now that our setup is done, let’s create methods for each of the CRUD operations we need to perform in our app!
Read Dream Entries From The Database
Add this code to your DatabaseProvider class:
Future<List<Dream>> getDreams() async {
final db = await database; // database getter called
// from the dream table, get all rows (records) in the 1. descending order of their id, 2. columns returned should be id, title, and details
// this returns a List<Map<String, dynamic>>
var dreams = await db.query(
TABLE_DREAM,
columns: [COLUMN_ID, COLUMN_TITLE, COLUMN_DETAILS],
orderBy: '$COLUMN_ID desc',
);
List<Dream> dreamsList = [];
dreams.forEach((currentDream) {
Dream dream = Dream.fromMap(currentDream); // deserialise
dreamsList.add(dream);
});
return dreamsList;
}
Note that the query() method is responsible for performing all read queries to a certain table.
You can specify different parameters to control the records you need to fetch. For instance, we have specified that our records need to be sorted in the descending order of their ids, using the orderBy parameter.
Create A New Dream Entry In The Database
Add this code to your DatabaseProvider class:
Future<Dream> insert(Dream dream) async {
final db = await database; // database getter called
dream.id = await db.insert(TABLE_DREAM, dream.toMap()); // serialise
return dream;
}
Note that the insert() method creates a new entry in the table specified. For this, we need to serialise our Dream object to a Map object using the toMap() method.
Delete A Dream Entry From The Database
Add this code to your DatabaseProvider class:
Future<int> delete(int id) async {
final db = await database; // database getter called
return await db.delete(
TABLE_DREAM,
where: 'id = ?',
whereArgs: [id],
);
}
Note that the delete() method deletes an entry/row from the table specified. For this, we need to define the criteria for our deletion.
In our case, we need to simply delete a Dream Entry by its id, so we specify that using the where and whereArgs parameters.where takes the condition, and whereArgs takes the actual values that need to be used in the defined condition.
So the query above can be mentally read as:
Delete rows from the dream table WHERE the id column has the value id (the dream id that we provided).
Update A Dream Entry In The Database
Add this code to your DatabaseProvider class:
Future<int> update(Dream dream) async {
final db = await database; // database getter called
return await db.update(
TABLE_DREAM,
dream.toMap(),
where: 'id = ?',
whereArgs: [dream.id],
);
}
Note that the update() method updates an entry/row in the table specified. For this, we need to pass the new Dream object after serialising it and also define the criteria for updating.
In our case, we need to simply update a Dream Entry by its id, so we specify that using the where and whereArgs parameters.
So the query above can be mentally read as:
Update rows in the dream table with this new Dream object WHERE the id column has the value id (the dream id that we provided).
This query will look for all rows with the specified id and update its contents using the new Dream object that we provided.
Keyword Based Search Of Dream Entries From The Database
Add this code to your DatabaseProvider class:dart
Future<List<Dream>> getDreamsByKeywordInTitle(String keyword) async {
final db = await database; // database getter called
var dreams = await db.query(
TABLE_DREAM,
columns: [COLUMN_ID, COLUMN_TITLE, COLUMN_DETAILS],
orderBy: '$COLUMN_ID desc',
where: '$COLUMN_TITLE LIKE ?',
whereArgs: ['%$keyword%']
);
List<Dream> dreamsList = [];
dreams.forEach((currentDream) {
Dream dream = Dream.fromMap(currentDream); // serialise
dreamsList.add(dream);
});
return dreamsList;
}
The LIKE operator searches for the specified pattern in the specified column – title, in our case
and
the % signs before and after our keyword variable indicate that the title can begin or end with a keyword, or even have it in any position in the middle.
So for instance, we have 5 entries in our table, and their titles are as follows:
- Cat playing DOTA
- I turned into a cat
- The pizza ate me
- Entering the blackhole
- Got a cat for my birthday
and we pass cat as our search keyword, then our query will return the 1st, 2nd and 5th records because their titles contain the word cat.
This query can be read as follows:
Get rows from the dream table in descending order of their id WHERE the title contains this keyword.
Calling The DatabaseProvider Methods (UI + State Management)
You’re done with the most difficult part! Now all you need to do is call the methods you just created in your DatabaseProvider class to update your UI and handle state management accordingly.
Doing this is no different than making the usual async calls and handling Futures when working with Firestore, Rest APIs etc.
Since I’m using BLoC for state management, this is how I’m calling the getDreams() to fetch all my dream entries and display them in a ListView:dart
class _DreamEntriesState extends State<DreamEntries> {
@override
void initState() {
super.initState();
DatabaseProvider.db.getDreams().then(
(dreamsList) {
BlocProvider.of<DreamBloc>(context).add(SetDreams(dreamsList));
},
);
}
@override
Widget build(BuildContext context) {
return Scaffold(
appBar: AppBar(title: Text('Tell Me Your Dreams 👀')),
floatingActionButton: FloatingActionButton(
child: Icon(Icons.add),
onPressed: () => Navigator.push(
context,
MaterialPageRoute(builder: (BuildContext context) => DreamForm()),
),
),
body: BlocConsumer<DreamBloc, List<Dream>>(
builder: (context, dreamsList) {
return ListView.separated(
padding: const EdgeInsets.symmetric(vertical: 16),
itemCount: dreamsList.length,
shrinkWrap: true,
separatorBuilder: (BuildContext context, int index) =>
Divider(
color: Colors.black,
indent: 10,
endIndent: 10,
),
itemBuilder: (BuildContext context, int index) {
Dream dream = dreamsList[index];
return ListTile(
title: Text(dream.title, style: TextStyle(fontSize: 22)),
subtitle: Text(
dream.details,
maxLines: 3,
style: TextStyle(fontSize: 18),
),
trailing: IconButton(
icon: Icon(Icons.clear),
onPressed: () =>
deleteConfirmationDialog(context, dream, index),
),
onTap: () => Navigator.push(
context,
MaterialPageRoute(
builder: (context) =>
DreamForm(dream: dream, dreamIndex: index),
),
),
);
},
);
},
),
);
}
}
If you’re not using any specific technique for state management, you can also simply wrap your ListView inside a FutureBuilder and make a call to the getDreams() method against the future parameter of your FutureBuilder.
You can make calls to the other methods in a similar fashion.
Check out the full source code to see the complete UI code and all the other method calls used with BLoC for state management.
