So you came to a point where you want to use user’s device storage as a database? Well, the SQLite plugin is the answer to that.
In this
tutorial you will not only see how to integrate the plugin but also How to Perform Create, Read, Update and Delete (CRUD) Operation
with SQLite in Flutter.
Recently, I have published two game apps in Goole PlayStore
called Hangman and Jinda – Bhutan Quiz App. In both of the game, I
have made use of the SQLite database to store the score of the user.
You will e required to
note here that if a user uninstalls the application or clears the data of an
app from setting. Then the scores will be deleted permanently. In my above
application as I did not require any online storage, so I used the SQLite
database to store.
Prerequisites
In this guide will be editing a project that we have already
created in our earlier tutorial. You can check the earlier tutorial in the link
below:
GET USER LOCATION IN FLUTTER
WITH GEOLOCATOR PLUGIN
As the project is getting user location, we will now store this
location to the database. So you can refer it in future to see which all place
you have visited. Ain’t that fun??
If you want to try your project or create a new project. I would
recommend you to do that. As that will help you learn more.
Importing SQLite In Flutter
As our project is ready to get into next level. We will start by
importing the required plugin in our project.
Open
your pubsec.yaml file
and add the dependency as follows:
dependencies:
sqflite:
^1.3.2+1
Creating A Data Model
Next, we will create a data model with the data structure that
will be used to manipulate inside the database.
In our application we will have following thing stored:
- id –
Unique ID to identify each record
- locDateTime –
We will store the date and time when the location is recorded.
- userLat –
The latitude of the user.
- userLon –
The longitude of the user.
To
achieve this, we will create a class with the required properties and a method
to map the data before inserting into the database. For our project, we will be
creating models directory
under our lib folder.
So the
file will be lib/models/user_loc.dart
class UserLocation {
final int
id;
final
String locDateTime;
final
double userLat;
final
double userLon;
UserLocation({this.id, this.locDateTime, this.userLat, this.userLon});
Map<String, dynamic> toMap() {
return {
'locDateTime': locDateTime,
'userLat': userLat,
'userLon': userLon,
};
}
@override
String
toString() {
return
'$locDateTime,$userLat,$userLon,$id';
}
}
Creating Helper For Sqflite In Flutter
In this helper class, we will create a method to manipulate the
data. We will start with the import of necessary files, then initialize the
database and method to perform CRUD operations.
Importing Necessary Files
We will mainly be importing sqflite package, a model class which
we have created in the above step and path package used to join the paths.
import 'dart:async';
import
'package:InstaLocation/models/user_loc.dart';
import 'package:path/path.dart';
import 'package:sqflite/sqflite.dart';
Initialize The Database And Create Table
Now we will be using the above imports to open a database connection. The method will create a new database if it does not exists.
We use
the path package to get the path of the directory and create a database in this
path.
We also
create table “locations”
with all necessary parameter while initializing the database.
Future<Database> openDB() async {
final
database = openDatabase(
join(await getDatabasesPath(), 'user_loc_db.db'),
onCreate: (db, version) {
return
db.execute(
"CREATE TABLE locations(id INTEGER PRIMARY KEY AUTOINCREMENT,
locDateTime TEXT, userLat DOUBLE, userLon DOUBLE)",
);
},
version:
1,
);
return
database;
}
ow we will see all the CRUD operations
Create Or Insert
Create a
function to get the data model, which we have created in our model page. Then
convert it into a map and insert data into the database using insert() helper
method.
Future<void> insertLocation(UserLocation
location, final database) async {
final
Database db = await database;
await
db.insert(
'locations',
location.toMap(),
conflictAlgorithm: ConflictAlgorithm.ignore,
);
}
Read
In
sqflite package, we will use query() helper
method to read the data from our Database. The sqflite in Flutter we can query
using various arguments like where,
groupBy, orderBy and having.
Future<List<UserLocation>>
locations(final database) async {
// Get a
reference to the database.
final
Database db = await database;
final
List<Map<String, dynamic>> maps = await db.query('locations');
return
List.generate(maps.length, (i) {
return
UserLocation(
id:
maps[i]['id'],
locDateTime: maps[i]['locDateTime'],
userLat: maps[i]['userLat'],
userLon: maps[i]['userLon'],
);
});
}
Update
Using
the update() helper
we will update the record in the database. While updating the record we cannot
use any record. For that purpose, we use where clause to check the id and
update the specific record.
Future<void> updateLocation(UserLocation
location, final database) async {
// Get a
reference to the database.
final db =
await database;
await
db.update(
'locations',
location.toMap(),
where:
"id = ?",
whereArgs: [location.id],
);
}
Delete
Use
the where argument
in delete() helper to delete specific rows from the table.
Please
note that we use whereArgs to pass arguments to where statement in order to
prevent SQL injection attacks.
Future<void> deleteLocation(int id, final
database) async {
// Get a
reference to the database.
final db =
await database;
await
db.delete(
'locations',
where:
"id = ?",
whereArgs: [id],
);
}
That is all!! We are good to go.
Complete CRUD Operation Code
We will use all method mentioned above and make our project
fully functional with SQLite database and CRUD operations.
Here is
how the final code will be. Remember that for this project we have created a
separate directory and file to store the code. The path for the file would
be lib/utilities/user_loc_db.dart
import 'dart:async';
import
'package:InstaLocation/models/user_loc.dart';
import 'package:path/path.dart';
import 'package:sqflite/sqflite.dart';
Future<Database> openDB() async {
final
database = openDatabase(
join(await getDatabasesPath(), 'user_loc_db.db'),
onCreate: (db, version) {
return
db.execute(
"CREATE TABLE locations(id INTEGER PRIMARY KEY AUTOINCREMENT,
locDateTime TEXT, userLat DOUBLE, userLon DOUBLE)",
);
},
version:
1,
);
return
database;
}
Future<void> insertLocation(UserLocation
location, final database) async {
final
Database db = await database;
await
db.insert(
'locations',
location.toMap(),
conflictAlgorithm: ConflictAlgorithm.ignore,
);
}
Future<List<UserLocation>>
locations(final database) async {
// Get a
reference to the database.
final
Database db = await database;
final
List<Map<String, dynamic>> maps = await db.query('locations');
return List.generate(maps.length,
(i) {
return
UserLocation(
id:
maps[i]['id'],
locDateTime: maps[i]['locDateTime'],
userLat: maps[i]['userLat'],
userLon: maps[i]['userLon'],
);
});
}
Future<void> updateLocation(UserLocation
location, final database) async {
// Get a
reference to the database.
final db =
await database;
await
db.update(
'locations',
location.toMap(),
where:
"id = ?",
whereArgs: [location.id],
);
}
Future<void> deleteLocation(int id, final
database) async {
// Get a
reference to the database.
final db =
await database;
await
db.delete(
'locations',
where:
"id = ?",
whereArgs: [id],
);
}
Conclusion
So we have completed our tutorial. Now we can use this method to
perform CRUD operation using sqflite in Flutter. We can call this method in our
program to perform different operations.