Sqlite CRUD Operation for Flutter
In this article, I will show you how to use the complete Create, Read, Update and Delete (CRUD) operation of sqflite plugin for flutter.
I believe most of you already have basic flutter knowledge, if not then you can go through some of the basic at https://flutter.dev/docs/get-started/install
First create your flutter project
cmd
flutter create sqlite_tutorial
Enable null safety to use latest Flutter version 2.0
dart migrate --apply-changes
What Is SQLite
SQLite is an open source relational database, it is used to create a database, perform different operation like add, delete,and remove data.
SQLite does not require a server or backend code, all the data is saved to a text file in the device. You can learn more about it here.
Adding Sqflite Plugin to Flutter
To be able to use SQLite in Flutter, you need to add the plugin sqflite.
The path plugin is to specify the location of the file that will be created which will contain the database.
So to add it you need to navigate to the pubspec.yaml file, and write the following:
dependencies: flutter: sdk: flutter cupertino_icons: ^1.0.2 sqflite: ^2.0.0+3 path: ^1.8.0
Now you can start using SQLite in the Flutter project! In the following sections we will create a list of users and add those users to the database.
Creating the Model Class
Create new file under lib/services/DatabaseHandler.dart
Since we need to add multiple users to the database, then we have to create a class called User which will contain different fields related to a user, for example:
class User { final int? id; final String name; final int age; final String country; final String? email; User( { this.id, required this.name, required this.age, required this.country, this.email}); User.fromMap(Map<String, dynamic> res) : id = res["id"], name = res["name"], age = res["age"], country = res["country"], email = res["email"]; Map<String, Object?> toMap() { return {'id':id,'name': name, 'age': age, 'country': country, 'email': email}; } }
Here we create a named constructor called User.fromMap() and a method called toMap() since to save data to the SQLite database we need to convert it to a map. Now, we will create the DatabaseHandler class.
Create a table in SQLite
Create a class called DatabaseHandler. This class will take care of all the operations regarding the SQLite database.
import 'package:sqflite/sqflite.dart';
import 'package:path/path.dart';
class DatabaseHandler {
Future<Database> initializeDB() async {
String path = await getDatabasesPath();
return openDatabase(
join(path, 'example.db'),
onCreate: (database, version) async {
await database.execute(
"CREATE TABLE users(id INTEGER PRIMARY KEY AUTOINCREMENT, name TEXT NOT NULL,age INTEGER NOT NULL, country TEXT NOT NULL, email TEXT)",
);
},
version: 1,
);
}
}So here the method getDatabasePath() is inside the sqflite package and it will get the default database location. The openDatabase() method is also inside the package sqflite and it accepts a mandatory String as an argument which will be the path of the database.
As you can see above, we use the method join() which is inside the package path, it will join the given path into a single path, so for example we would get databasepath/example.db.
The onCreate() callback will be called after the database was created, and it will execute the above sql query that will create the table users.
Saving Data in SQLite
Now inside the class DatabaseHandler, we can create another method to insert users to the database, for example:
Future<int> insertUser(User user) async { final Database db = await initializeDB(); await db.insert('users', user.toMap()); }
Here insertUser() will take a user. The insert() method takes the following parameters String table, Map<String, Object?> values, and that’s why we create a toMap() method in the model class.
Retrieve Data From SQLite
Then to retrieve data, we can create another method in the DatabaseHandler class, for example:
Future<List<User>> retrieveUsers() async { final Database db = await initializeDB(); final List<Map<String, Object?>> queryResult = await db.query('users'); return queryResult.map((e) => User.fromMap(e)).toList(); }
So here we use the query() method and give it the string users which is the table name. So this will select all columns from the table users. Then since queryResult returns a List, therefore we use the map() method to transform the List<Map<String, Object?>> into a List<User>.
Delete Data From SQLite
To delete data, we create the following method:
Future<void> deleteUser(int id) async { final db = await initializeDB(); await db.delete( 'users', where: "id = ?", whereArgs: [id], ); }
Here using the delete() method we pass the table name and then specify according to which column we need to delete the row in the database table.
Update Data In SQLite
To update data, we create the following method:
Future<void> updateUsers(User user) async {
final Database db = await initializeDB();
await db.update(
'users',
user.toMap(),
where: 'id = ?',
whereArgs: [user.id],
);
}Here using the update() method we pass the table name and then specify according to which column we need to update the row in the database table.
Display Data From The Database
Navigate to the main.dart file, and remove all the code related to the counter Flutter application, and inside the _MyHomePageState add the following:
class _MyHomePageState extends State<MyHomePage> { late DatabaseHandler handler; @override void initState() { super.initState(); this.handler = DatabaseHandler(); this.handler.initializeDB(); }
So here we create an instance of the class DatabaseHandler() and then call initalizeDb() to create the database which will contain the users table.
Then we add several textfield for adding and updating data. Update inside the _MyHomePageState as following:
class _MyHomePageState extends State<MyHomePage> {
final name = TextEditingController();
final age = TextEditingController();
final country = TextEditingController();
final email = TextEditingController();
late DatabaseHandler handler;
late dynamic idSelected; //for select the id of data displayed to be updated
@override
void dispose() {
// Clean up the controller when the widget is disposed.
name.dispose();
age.dispose();
country.dispose();
email.dispose();
super.dispose();
}
@override
void initState() {
super.initState();
this.handler = DatabaseHandler();
this.handler.initializeDB();
}Then inside the build() method, we will use a column to add TextField widget and FutureBuilder widget to call retrieveUsers() method with the delete function using deleteUser() by using Dismissible widget. Also we added two button for add new user and update existing user.
body: Column( children: <Widget>[ Container( padding: const EdgeInsets.all(10), alignment: Alignment.centerLeft, child: Text( 'Fill in the form and then you can add new data or update existing data'), ), Padding( padding: const EdgeInsets.only(bottom: 10, left: 10, right: 10), child: TextField( decoration: new InputDecoration( focusedBorder: OutlineInputBorder( borderSide: BorderSide(color: Colors.greenAccent, width: 1.0), ), enabledBorder: OutlineInputBorder( borderSide: BorderSide(color: Colors.red, width: 1.0), ), hintText: 'Name', ), controller: name, ), ), Padding( padding: const EdgeInsets.only(bottom: 10, left: 10, right: 10), child: TextField( decoration: new InputDecoration( focusedBorder: OutlineInputBorder( borderSide: BorderSide(color: Colors.greenAccent, width: 1.0), ), enabledBorder: OutlineInputBorder( borderSide: BorderSide(color: Colors.red, width: 1.0), ), hintText: 'Age', ), controller: age, ), ), Padding( padding: const EdgeInsets.only(bottom: 10, left: 10, right: 10), child: TextField( decoration: new InputDecoration( focusedBorder: OutlineInputBorder( borderSide: BorderSide(color: Colors.greenAccent, width: 1.0), ), enabledBorder: OutlineInputBorder( borderSide: BorderSide(color: Colors.red, width: 1.0), ), hintText: 'Country', ), controller: country, ), ), Padding( padding: const EdgeInsets.only(bottom: 10, left: 10, right: 10), child: TextField( decoration: new InputDecoration( focusedBorder: OutlineInputBorder( borderSide: BorderSide(color: Colors.greenAccent, width: 1.0), ), enabledBorder: OutlineInputBorder( borderSide: BorderSide(color: Colors.red, width: 1.0), ), hintText: 'Email', ), controller: email, ), ), ElevatedButton( onPressed: () async { await this.addUsers(); setState(() {}); }, child: const Text('Add User'), ), ElevatedButton( onPressed: () async { await this.updateUsers(); setState(() {}); }, child: const Text('Update selected entry'), ), Expanded( child: FutureBuilder( future: this.handler.retrieveUsers(), builder: (BuildContext context, AsyncSnapshot<List<User>> result) { if (result.hasData) { return ListView.builder( itemCount: result.data?.length, itemBuilder: (BuildContext context, int index) { return Dismissible( direction: DismissDirection.endToStart, background: Container( color: Colors.red, alignment: Alignment.centerRight, padding: EdgeInsets.symmetric(horizontal: 10.0), child: Icon(Icons.delete_forever), ), key: ValueKey<int>(result.data![index].id!), onDismissed: (DismissDirection direction) async { await this .handler .deleteUser(result.data![index].id!); setState(() { result.data!.remove(result.data![index]); }); }, child: Card( child: ListTile( onTap: () { setState(() { idSelected = result.data![index].id; }); }, contentPadding: EdgeInsets.all(8.0), title: Text("ID = " + result.data![index].id.toString()), subtitle: Column(children: <Widget>[ Text("Name = " + result.data![index].name.toString()), Text("Age = " + result.data![index].age.toString()), Text("Country = " + result.data![index].country.toString()), Text("Email = " + result.data![index].email.toString()), ]))), ); }, ); } else { return Center(child: CircularProgressIndicator()); } }, ), // This trailing comma makes auto-formatting nicer for build methods. ), ], ),
After that done, add the two function we declared on each button.
class _MyHomePageState extends State<MyHomePage> { ... Future<void> addUsers() async { User firstUser = User( name: name.text, age: int.parse(age.text), country: country.text, email: email.text); await this.handler.insertUser(firstUser); } Future<void> updateUsers() async { User firstUser = User( id: idSelected, name: name.text, age: int.parse(age.text), country: country.text, email: email.text); await this.handler.updateUsers(firstUser); } ... }
All done. Now to test it call flutter run on cmd
The output will be like this:
Then fill in the form and add user. Do this action twice in order to have 2 data saved on sqlite.
Then click on any data displayed to update it, after clicked, fill in the form with new info and click update selected entry. Then you will see the data will be updated.
To delete a user, use the Dismissible widget to swipe to the right, which will remove the item from the list and delete the user from the database by calling the method deleteUser().
Comments
Post a Comment