SQL Database Storage Using Sqlite In Flutter

Hi Flutter developers, Today we shall learn how to build an app that uses SQLite to store the data locally. SQLite is a relational database management system contained in a C library. SQLite is not a client-server database engine. sqflite the package provides us to implement SQLite into our flutter app. This package provides various methods to insert, update, edit, and fetch queries from the database. Everything in this package is asynchronous.

---

Table of Contents:

About the sqflite

Create Model Class

Create Database Method

Create an ItemCard

Create a Form

Display the data

End Result

GitHub

---

About the sqflite:

sqflite | Flutter Package
SQLite plugin for Flutter. Supports iOS, Android, and macOS. Support transactions and batches Automatic version…pub. dev

1. SQLite package provides us openDatabase a method to open the database at a specified path. the version property helps us to assign a version to the database. onCreate the property takes a Database and version . Inside the onCreate property, we can build our database table using execute method.
2. To insert the data or model inside the database we use the insert method. It takes a table name and JSON value.
3. To fetch the data from the database we use the query method. this method takes the name of the table that is needed to be fetched.
4. To update the table query we use update the method. It takes the table name and id of the query that we want to update. Similarly, we can use the delete method to delete the query.

In this blog, we shall build an app that uses the above method to manage our local database.

Create Model Class:

Make a new file inside your lib folder, create a model inside it with the required properties. To convert the model to JSON we use the toJson method, which maps the Model properties into JSON format. fromJson method id used to convert the JSON data that is fetched from the database to Model class. Both methods are user-defined methods.

class Model {
int id;
String fruitName;
String quantity;

Model({this.id, this.fruitName, this.quantity});

Model fromJson(json) {
return Model(
id: json['id'], fruitName: json['fruitName'], quantity: json['quantity']);
}
Map<String, dynamic> toJson() {
return {'fruitName': fruitName, 'quantity': quantity};
}

}

Create Database Method:

Open the database

To open the database we use openDatabase the method takes the path and returns a Database object.

"CREATE TABLE name_of_table(id INTEGER PRIMARY KEY autoincrement, field_name_1 field_data_type, field_name_2 field_data_type)"

Using the following string we can execute and create a table in the local database by the name specified in the string (eg. model) and properties specified.

Database _database;
Future openDb() async {
_database = await openDatabase(join(await getDatabasesPath(), "ss.db"),
version: 1, onCreate: (Database db, int version) async {
await db.execute(
"CREATE TABLE model(id INTEGER PRIMARY KEY autoincrement, fruitName TEXT, quantity TEXT)",
);
});
return _database;
}

Insert Data in the Database

To perform any operation on the database we need to open the database and then we can use the returned database object to perform the desired operation.

We can insert the data in JSON format using the insert method.

Future insertModel(Model model) async {
await openDb();
return await _database.insert('model', model.toJson());
}

Fetch the data

The query method gets the database data and return it in JSON format. To map the data with the model we can use the map method or we can use List.generate it to create a list of the model with the JSON data.

Future<List<Model>> getModelList() async {
await openDb();
final List<Map<String, dynamic>> maps = await _database.query('model');

return List.generate(maps.length, (i) {
return Model(
id: maps[i]['id'],
fruitName: maps[i]['fruitName'],
quantity: maps[i]['quantity']);
});
// return maps
// .map((e) => Model(
// id: e["id"], fruitName: e["fruitName"], quantity: e["quantity"]))
// .toList();
}

Update the data

Future<int> updateModel(Model model) async {
await openDb();
return await _database.update('model', model.toJson(),
where: "id = ?", whereArgs: [model.id]);
}

Delete database

Future<void> deleteModel(Model model) async {
await openDb();
await _database.delete('model', where: "id = ?", whereArgs: [model.id]);
}

database.dart

import 'dart:async';
import 'package:path/path.dart';
import 'package:sqflite/sqflite.dart';

import 'model.dart';

class DbManager {
Database _database;

Future openDb() async {
_database = await openDatabase(join(await getDatabasesPath(), "ss.db"),
version: 1, onCreate: (Database db, int version) async {
await db.execute(
"CREATE TABLE model(id INTEGER PRIMARY KEY autoincrement, fruitName TEXT, quantity TEXT)",
);
});
return _database;
}

Future insertModel(Model model) async {
await openDb();
return await _database.insert('model', model.toJson());
}

Future<List<Model>> getModelList() async {
await openDb();
final List<Map<String, dynamic>> maps = await _database.query('model');

return List.generate(maps.length, (i) {
return Model(
id: maps[i]['id'],
fruitName: maps[i]['fruitName'],
quantity: maps[i]['quantity']);
});
// return maps
// .map((e) => Model(
// id: e["id"], fruitName: e["fruitName"], quantity: e["quantity"]))
// .toList();
}

Future<int> updateModel(Model model) async {
await openDb();
return await _database.update('model', model.toJson(),
where: "id = ?", whereArgs: [model.id]);
}

Future<void> deleteModel(Model model) async {
await openDb();
await _database.delete('model', where: "id = ?", whereArgs: [model.id]);
}
}

You can use the above method to help you to build our app with a local database now we will see how to implement it will UI. Let’s Create an ItemCard widget that will display the data stored in the database.

Create an ItemCard:

class ItemCard extends StatefulWidget {
Model model;
TextEditingController input1;
TextEditingController input2;
Function onDeletePress;
Function onEditPress;

ItemCard(
{this.model,
this.input1,
this.input2,
this.onDeletePress,
this.onEditPress});

@override
_ItemCardState createState() => _ItemCardState();
}

class _ItemCardState extends State<ItemCard> {
final DbManager dbManager = new DbManager();

@override
Widget build(BuildContext context) {
return Padding(
padding: const EdgeInsets.all(8.0),
child: Card(
child: Padding(
padding: const EdgeInsets.all(8.0),
child: Row(
mainAxisAlignment: MainAxisAlignment.spaceBetween,
children: <Widget>[
Column(
crossAxisAlignment: CrossAxisAlignment.start,
children: <Widget>[
Text(
'Name: ${widget.model.fruitName}',
style: TextStyle(fontSize: 15),
),
Text(
'Quantity: ${widget.model.quantity}',
style: TextStyle(
fontSize: 15,
),
),
],
),
Row(
children: [
CircleAvatar(
backgroundColor: Colors.white,
child: IconButton(
onPressed: widget.onEditPress,
icon: Icon(
Icons.edit,
color: Colors.blueAccent,
),
),
),
SizedBox(
width: 15,
),
CircleAvatar(
backgroundColor: Colors.white,
child: IconButton(
onPressed: widget.onDeletePress,
icon: Icon(
Icons.delete,
color: Colors.red,
),
),
)
],
),
],
),
),
),
);
}
}

This widget will display the fruitName and quantity. It will have an edit and delete a button that will handle deletes and edit functionality.

On pressing the edit button the user form will open with the respective item details and using that from we can update the data. On pressing the delete button the model will be deleted from the database and update the UI accordingly.

Create a Form:

The input form that the user will use to enter the details will be a dialogBox. This dialog box will be displayed when the user clicks the floatingActionButton and ItemCard edit button. It contains two TextFormField that takes the input from the user and two-button Cancel and Submit.

I have also implemented the focus node functionality. It will display the Done button on the keyboard on pressing the Done button the focus will be shifted to the specified TextField

focus.FocusScope.of(context).requestFocus(input2FocusNode);

---

class DialogBox {
Widget dialog(
{BuildContext context,
Function onPressed,
TextEditingController textEditingController1,
TextEditingController textEditingController2,
FocusNode input1FocusNode,
FocusNode input2FocusNode}) {
return AlertDialog(
title: Text("Enter Data"),
content: Container(
height: 100,
child: Column(
children: [
TextFormField(
controller: textEditingController1,
keyboardType: TextInputType.text,
focusNode: input1FocusNode,
decoration: InputDecoration(hintText: "Fruit Name"),
autofocus: true,
onFieldSubmitted: (value) {
input1FocusNode.unfocus();
FocusScope.of(context).requestFocus(input2FocusNode);
},
),
TextFormField(
controller: textEditingController2,
keyboardType: TextInputType.number,
focusNode: input2FocusNode,
decoration: InputDecoration(hintText: "Quantity"),
onFieldSubmitted: (value) {
input2FocusNode.unfocus();
},
),
],
),
),
actions: [
MaterialButton(
onPressed: () {
Navigator.of(context).pop();
},
color: Colors.blueGrey,
child: Text(
"Cancel",
),
),
MaterialButton(
onPressed: onPressed,
child: Text("Submit"),
color: Colors.blue,
)
],
);
}
}

Display the Data:

Everything in sqflite is asynchronous so we need FutureBuilder . The method getModelList returns a future of List of Model .

We will return the ItemCard for each _model . onDeletePress we will execute the delete method and onDeletePress we will perform the form edit.

//initialize the TextEditingController for both the input fields
TextEditingController input1 = TextEditingController();
TextEditingController input2 = TextEditingController();return FutureBuilder(
future: dbManager.getModelList(),
 builder: (context, snapshot) {
if (snapshot.hasData) {
modelList = snapshot.data;
return ListView.builder(
itemCount: modelList.length,
itemBuilder: (context, index) {
Model _model = modelList[index];
 return ItemCard(
model: _model,
input1: input1,
input2: input2,
onDeletePress: () {
dbManager.deleteModel(_model);
setState(() {});
},
onEditPress: () {
input1.text = _model.fruitName;
input2.text = _model.quantity;
showDialog(
context: context,
builder: (context) {
return DialogBox().dialog(
context: context,
onPressed: () {
Model __model = Model(
id: _model.id,
fruitName: input1.text,
quantity: input2.text);
dbManager.updateModel(__model);

setState(() {
input1.text = "";
input2.text = "";
});
Navigator.of(context).pop();
},
textEditingController2: input2,
textEditingController1: input1);
});
},
 );
},
);
}
return Center(
child: CircularProgressIndicator(),
);
},
),

End Result:

When we run the application, we ought to get the screen’s output like the underneath screen video capture.

GitHub:

flutter-devs/sqflite_flutter
A new Flutter application. This project is a starting point for a Flutter application. A few resources to get you…github.com

🌸🌼🌸 Thank you for reading. 🌸🌼🌸

Feel free to connect with us:
And read more articles from FlutterDevs.com.

FlutterDevs team of Flutter developers to build high-quality and functionally-rich apps. Hire a flutter developer for your cross-platform Flutter mobile app project on an hourly or full-time basis as per your requirement! You can connect with us on Facebook, GitHub, Twitter, and LinkedIn for any flutter-related queries.

We welcome feedback and hope that you share what you’re working on using #FlutterDevs. We truly enjoy seeing how you use Flutter to build beautiful, interactive web experiences.