Lite your app with SQFLite — Flutter💙
No internet access? Want to store your data in local storage? No worries! SQFLite will be your rescue!
Internet is one of the best things happened to the world! But, your user might not always be connected to the internet. Or you might need to store something locally. That’s where we can use SQFLite which creates an SQL based local database into the user’s device!
Let’s see how we can perform the CRUD functions using SQFLite!
First of all, include sqflite and path_provider packages in pubspec.yaml file! path_provier is required to get the path where our DB file will be stored. You can execute all raw SQL queries too!
Create a database and open it…
Once you have added both the packages, you need to create a database that will hold all your tables. You can do that using the following snippet of code:
static late Database database;
static Future<void> createDB() async {
Directory documentsDirectory = await getApplicationDocumentsDirectory();
String path = documentsDirectory.path + "sqflite.db";
database = await openDatabase(path);
await createTable();
}Here, we created a function in which we first take the path and then create a database in that path! After the database is created and opened, we have called the createTable() function which is a user-defined function.
Create a Table…
You can create a table in the database by using the following code snippet:
static Future<void> createTable() async {
await database.execute('CREATE TABLE SQFLITE (first_name TEXT, last_name TEXT)');
}Here, we created a table called SQFLITE with 2 columns: first_name and last_name with their data type being TEXT!
Insert data into table…
Once the table is created, you can insert data to that table using the following code snippet:
static Future<void> insertData({required User user}) async {
await database
.rawInsert('INSERT INTO SQFLITE(first_name, last_name) VALUES("${user.firstName}", "${user.lastName}")');
}Here, we inserted first name and last name to our SQFLITE table!
Get data from the table…
You can retrieve the data from the table using the following code snippet:
static Future<User> getData({required String firstName}) async {
List<Map> list = await database.rawQuery('SELECT * FROM SQFLITE WHERE first_name = "$firstName"');return User(
firstName: list.length > 0 ? list[0]['first_name'] : "", lastName: list.length > 0 ? list[0]['last_name'] : "");
}
Here, the rawQuery function returns List<Map> which will contain all the entries satisfying the given query. You can then use a loop or return the entire list if you wish to. Or you can just return the 0th index data as done in the above example.
Update data for a particular Row…
You can update the data of a particular row by using our raw SQL query as followed:
static Future<void> updateData({required User user}) async {
await database
.rawUpdate('UPDATE SQFLITE SET last_name = "${user.lastName}" WHERE first_name = "${user.firstName}"');
}Here, we updated the last_name of the particular row where the first_name matches the first name provided by the user.
Delete data from the table…
You can delete a row from the table as follows:
static Future<void> deleteData({required String firstName}) async {
await database.rawDelete('DELETE FROM SQFLITE WHERE first_name = "$firstName"');
}Here, we deleted the row that had first_name same as provided by the user.
Close the database…
Finally, after all the tasks are done, you need to close the database so that there are no memory leaks or unused resources. You can do that simply by the following function:
static Future<void> closeDB() async {
await database.close();
}You can also get a small example for the same from GitHub!
Hope you enjoyed this article!
If you loved it, you can Buy Me A Coffee!
Don’t stop, until you are breathing!💙
- Abhishek Doshi
