Saving data on the device is a basic need for some apps and it’s straightforward to do it with SQLite. Flutter also has SQLite support so the app can store data in the form of rows and columns on the device. This article covers the Flutter SQLite Crud with Example code and easy steps.
Why do we need SQLite
Sometimes we need to store the data of Users or sometimes we need to retain the login status there we use the SQLite database. In some cases we want the app to work offline and we use the Local database to store data in the device in some online apps we use it to store device/user-specific data and much more
Let’s begin with the implementation first then will cover the basics then will dis flutter SQLite CRUD operations:
Step1: Create the Project and Import the SQLite plugin
First, Make a dummy app or you can use your existing app and import two plugins from pub.dev
SQLite Plugin Link https://pub.dev/packages/sqflite
Path Plugin Link https://pub.dev/packages/path_provider
The path provider will give us a path where we store our database in the mobile device and SQLite will provide us the functions to perform local Storage systems. Past the plugin Import lines in the pubspec.yaml file
dependencies:
flutter:
sdk: flutter
#dependencie of SQLITE
sqflite: ^2.2.0+3
path_provider: ^2.0.11Check the current version of this Plugin and import that one.
Step2: Create a file for handling all Sqlite database-related code
To maintain the code simple and structured we will create a separate file for taking SQLite code. Now let’s create the SqliteDatabaseHelper.dart file and create the dart class with the same name. And Import the file which we added through the plugins Path prover and SQLite.
import 'package:sqflite/sqflite.dart';
import 'package:path/path.dart';
class SqliteDatabaseHelper{
}It’s just a simple Class making which we do in every OOP language.
Step3: Database Provider Function
Now let’s begin creating the database in our device by making the function that will create the database if it does not already exist in the device or will return the database if already created when the app has first launched the app.
Future function with the return type Database and return the openDatabase function which comes with SQLite plugin.
Future<Database> getDataBase(){
}getDataBase() function takes 3 arguments and gives us a database which we again return to our future function.
- String path of the Database
- Version
- onCreate Function
Populate your Future getDataBase() Function with the code below in the code block
final String _tableName = "users";
Future<Database> getDataBase() async {
return openDatabase(
join(await getDatabasesPath(), "usersDatabase.db"),
onCreate: (db, version) async {
await db.execute(
"CREATE TABLE $_tableName (id TEXT PRIMARY KEY, name TEXT, imageUrl TEXT)",
);
},
version: 1,
);
}Created one _tableName String variable and give the required parameters to openDatabase(). join(await getDatabasesPath(), “usersDatabase.db”) this Code provides the String database location.
And in the inverted commas there is a database name given getDatabasesPath() function comes from the PathProvider plugin
in the onCreate call we added the function which provides us the db and version and this function calls when the database needs to be created.
So this function contains the db.execute() function which executes our SQL query of creating a table
Here is the SQL queries tutorial.
Now, this function will create the Local database.
Step4: Creating Modal Class
Whos making and understanding the modal class for the first time may be tricky to understand. It’s nothing that just helps us to store the data of a full Row from the database table in one single variable/Object.
For Example: In the database table there are 6 users and each user has 3 fields and which are id, name, and imageUrl.
To store all three fields for the single user we create the Modal of User so that we can just use the 6 users’ map and their respective values easily.
So let’s create the Modal Class of User and their respective functions as shown below
class User {
final String? id;
final String? name;
final String? imageUrl;
const User({ this.id, this.name, this.imageUrl});
factory User.fromMap(Map<String, dynamic> json) =>
User(id: json["id"], name: json["name"], imageUrl: json["imageUrl"]);
Map<String, dynamic> toMap() {
return {
'id': id,
'name': name,
'imageUrl': imageUrl,
};
}
}
toMap() this function helps to make the User object into JSON and fromMap() converts the JSON Data to the Map of the user Object.
JSON is the data transfer Format. whenever data needs to be passed through the network or to get data from a database. More About JSON: https://www.w3schools.com/whatis/whatis_json.asp
Step5: Flutter SQLite CRUD
All of these operations are super simple, all the functions are already there in SQLite Plugin and all the SQL queries are available.
Insert/Add Method:
First Insert Function Which will take values and add them inside the Database
Future<int> insertUser(User user) async {
int userId = 0;
Database db = await getDataBase();
await db.insert( _tableName, user.toMap()).then((value) {
userId = value;
});
return userId;
}The first line created a variable for getting UserId, then the Get the database Form getDataBase() function in the db variable.
Next, there is an insert function that comes from the Sqlite function which takes the table name and map of user Data. And in then() function, we get userID which we can return in the return statement of our future function
Get All Method:
After adding data then comes the part where we need data and show it into the UI.
Future<List<User>> getAllUsers() async {
Database db = await getDataBase();
List<Map<String, dynamic>> usersMap = await db.query(_tableName);
return List.generate(usersMap.length, (index) {
return User(
id: usersMap[index]["id"],
name: usersMap[index]["name"],
imageUrl: usersMap[index]["imageUrl"]);
});
}Again first there is a need for a database and then db.query method provides all the rows in the provided table inside the query() method.
It returns the list of map data and we are storing it in userMaps function. then return a list with the List.generate() Method which takes a List of maps and class back function which provides an index.
Inside that call back function which is iteration and creating the User Object for every List item means for every row of the table given to us from the database table.
Get method:
This will return a Single user by using Where in the SQL query. For this, we need to pass the userID while calling this function.
Future<User> getUser(String userId)async{
Database db = await getDataBase();
List<Map<String, dynamic>> user = await db.rawQuery("SELECT * FROM $_tableName WHERE id = $userId");
if(user.length == 1){
return User(
id: user[0]["id"],
name: user[0]["name"],
imageUrl: user[0]["imageUrl"]);
} else {
return const User();
}
}Again this function do the same gets the database and return the single user with userID. The return statement returns the empty user when there is no user found in the database.
Update Method:
Aging updating a particular user needs UserId and new values in the function when calling it. Here is the function for that below:
Future<void> updateUser(String userId, String name, String imageUrl) async {
Database db = await getDataBase();
db.rawUpdate("UPDATE $_tableName SET name = '$name', imageUrl = '$imageUrl' WHERE id = '$userId'");
}Need to add an Update query and give it the table and updated values where id = userId.
Delete method:
Future<void> deleteUser(String userId) async {
Database db = await getDataBase();
await db.rawDelete("DELETE FROM $_tableName WHERE id = '$userId'");
}So here is how flutter Sqlite CRUD Operation works with Example code. If you want to use it on your own project just change the modal classes add some fields and model the code according to your need.
Full DatabaseHelper file:
import 'package:sqflite/sqflite.dart';
import 'package:path/path.dart';
import '../modals/Users.dart';
class SqliteDatabaseHelper {
final String _tableName = "users";
Future<Database> getDataBase() async {
return openDatabase(
join(await getDatabasesPath(), "usersDatabase.db"),
onCreate: (db, version) async {
await db.execute(
"CREATE TABLE $_tableName (id TEXT PRIMARY KEY, name TEXT, imageUrl TEXT)",
);
},
version: 1,
);
}
Future<int> insertUser(User user) async {
int userId = 0;
Database db = await getDataBase();
await db.insert( _tableName, user.toMap()).then((value) {
userId = value;
});
return userId;
}
Future<List<User>> getAllUsers() async {
Database db = await getDataBase();
List<Map<String, dynamic>> usersMaps = await db.query(_tableName);
return List.generate(usersMaps.length, (index) {
return User(
id: usersMaps[index]["id"],
name: usersMaps[index]["name"],
imageUrl: usersMaps[index]["imageUrl"]);
});
}
Future<User> getUser(String userId)async{
Database db = await getDataBase();
List<Map<String, dynamic>> user = await db.rawQuery("SELECT * FROM $_tableName WHERE id = $userId");
if(user.length == 1){
return User(
id: user[0]["id"],
name: user[0]["name"],
imageUrl: user[0]["imageUrl"]);
} else {
return const User();
}
}
Future<void> updateUser(String userId, String name, String imageUrl) async {
Database db = await getDataBase();
db.rawUpdate("UPDATE $_tableName SET name = '$name', imageUrl = '$imageUrl' WHERE id = '$userId'");
}
Future<void> deleteUser(String userId) async {
Database db = await getDataBase();
await db.rawDelete("DELETE FROM $_tableName WHERE id = '$userId'");
}
}
Article by: Hemunt Sharma
For more Flutter tutorials, Tips, Tricks, Free code, Questions, and Error Solving.
Remember FlutterDecode.com