In this article we’ll check out how to use SQLite in Flutter.
SQLite is an open-source, stand-alone, relational database engine designed to be embedded into an application.
Why SQLite ?
If you are developing an app that needs to persist and query large amounts of data locally on the device, using a database is preferred instead of a local file or key-value store. Databases provide faster inserts, updates, and queries compared to other local persistence solutions.
So let's begin with the tutorial now.
1. Add the dependencies
To use SQLite, we would require two packages available on pub.dev :
a. sqflite : This package provides classes and functions to interact with a SQLite database.
b. path : This package provides functions to define the location for storing the database on disk.
In pubspec.yaml file, add :
dependencies:
flutter:
sdk: flutter
sqflite:
path:
2. Make database helper class
To make your code more modular and easier to work with, we would build a separate class to handle all the database related operations. This will make things easier to understand and I recommend this approach specially if you have to perform a lot of database operations in your application.
Create a new dart file database_helper.dart and declare a new class DatabaseHelper. We will see the class details soon.
3. Define your data model
This is the most important part for any app. You need to understand your requirements and then carefully decide what kind of data model do you think will best suit your requirements.
For this tutorial I will be using a Student model.
We define a Student class that contains three pieces of data: A unique id, the name, and the age of each student.
Inside the database_helper.dart define the student model as :
class Student {
final int id;
final String name;
final int age;
Student({this.id, this.name, this.age});
}
4. Initialize DatabaseHelper
Now we start with the implementation of DatabseHelper class.
For performing any database operation, anywhere throughout the application, we will be using DatabaseHelper object. We declare this object to be static with private constructor so that database operations remain consistent throughout.
Also Create two fields to define database name and version.
class DatabaseHelper{
static final _databaseName = "students.db";
static final _databaseVersion = 1;
DatabaseHelper._privateConstructor();
static final DatabaseHelper instance = DatabaseHelper._privateConstructor();
}
5. Create and Open the database
As for DatabaseHelper, create a static database and define method to get database.
Open a connection to the database. This involves two steps:
a. Define the path to the database file using getDatabasesPath() from the sqflite package, combined with the join function from the path package.
b. Open the database with the openDatabase() function from sqflite.
_initDatabase() function is used to initialize the database only once.
static Database _database;
Future<Database> get database async {
if (_database != null) return _database;
_database = await _initDatabase(); // only initialize if not created already
return _database;
}
_initDatabase() async {
String path = join(await getDatabasesPath(), _databaseName);
return await openDatabase(path,
version: _databaseVersion, onCreate: _onCreate);
}
6. Creating Student table
A SQLite database is composed of multiple tables which may have relationships among themselves.
For this tutorial, we require a Student table.
_onCreate method is called when our database is created. This is the best time to create our tables.
Future _onCreate(Database db, int version) async {
db.execute(
"CREATE TABLE students(id INTEGER PRIMARY KEY, name TEXT, age INTEGER)",
);
}
Note : If you have multiple tables in the database, it's recommended to use a transaction since it ensures that either all the database queries are executed or none.
Now we have defined the basic structure of our database and also defined the tables. Now we are ready to perform different types of operations on our database.
7. Insert a student in the database
To insert an object in the database, we need to firstly convert it to a Map in dart.
To convert student object to Map, we define a function toMap() inside the student class :
class Student {
final int id;
final String name;
final int age;
Student({this.id, this.name, this.age});
Map<String, dynamic> toMap() {
return {
'id': id,
'name': name,
'age': age,
};
}
}
Now create the following function inside the DatabaseHelper class.
Future<int> insert(Student object) async {
Database db = await instance.database;
var res = await db.insert(contactsTable, object.toMap(),
conflictAlgorithm: ConflictAlgorithm.ignore);
return res;
}
Note : Conflict algorithm is a parameter which tells what to do in case of collision, eg. 2 students with same primary key.
ConflictAlgorithm.ignore tells us that it will ignore new student if one already exists with same key.
To perform any database operation, we just need to create DatabaseHelper instance and call the respective method.
To insert a Student to our table, we can simply do it as :
Student student = Student(id : 5, age : 20, nane : 'Tom'); DatabaseHelper helper = await DatabaseHelper.instance; helper.insert(student);
So simple the code looks right. In this way you can perform any operation on the databse anywhere in your application by just importing the database_helper.dart class and getting the DatabaseHelper instance and calling any then calling any method.
8. Retrieve all students from database
After inserting students, let us query the database for list of all students. This involves two steps:
a. Run a query against the students table. This returns a List<Map>.
b. Convert the List<Map> into a List<Student>.
Future<List<Student>> queryAllRows() async {
Database db = await instance.database;
List<Map> maps = await db.query('students');
return List.generate(maps.length, (i) {
return Student(
id: maps[i]['id'],
name: maps[i]['name'],
age: maps[i]['age'],
);
});
}
We can then get all students anywhere as this :
DatabaseHelper helper = await DatabaseHelper.instance;
print(await helper.queryAllRows());
9. Query for a particular student or some students.
If you have idea about SQLite, you may be knowing that we use where clause to filter results from SQLite table.
We will again use the query() method on the database but the query method also takes some more parameters to provide filtered results.
See the method below.
Let us find names of all the students whose age is 20.
Future<List<String>> getStudentsByAge(String age) async {
List<String> resultStudents;
List<String> columnsToSelect = ['name']; // which columns you want in result
String whereString = 'age = ?'; // the where clause list
List<dynamic> whereArguments = [age]; // paramters for whereString (?)
Database db = await instance.database;
List<Map> result = await db.query('students',
columns: columnsToSelect,
where: whereString,
whereArgs: whereArguments);
if (result == null)
resultStudents = [];
else {
resultStudents =
List.generate(result.length, (index) => result[index]['name']);
return resultStudents;
}
Simply call the method as :
DatabaseHelper helper = await DatabaseHelper.instance; print(await helper.getStudentsByAge(20));
10. Update a Student in the database
After inserting information into the database, you might want to update that information at a later time.
We can use the rawUpdate() method to provide the Update query as we want.
Let us update the name of student to 'Ellen' whose id = '35'.
Future<void> setName(String studentId, String newName) async {
Database db = await instance.database;
await db.rawUpdate(
'UPDATE students SET name = ? WHERE id = ?',
[newName, studentId]);
}
Simply call the method as :
DatabaseHelper helper = await DatabaseHelper.instance; print(await helper.setName(35, 'Ellen'));
11. Delete student from the database
To delete a student from the table, use delete() from sqflite package :
Future<int> delete(String id) async {
Database db = await instance.database;
await db.delete('students', where: 'id = ?', whereArgs: [id]);
return 1;
}
Call the method as :
DatabaseHelper helper = await DatabaseHelper.instance;
print(await helper.setName(35, 'Ellen'));
So we have discussed all the basic operations you require while using SQLite in your flutter application. Full code for database_helper.dart file :
import 'package:path/path.dart';
import 'package:sqflite/sqflite.dart';
class DatabaseHelper{
static final _databaseName = "students.db";
static final _databaseVersion = 1;
DatabaseHelper._privateConstructor();
static final DatabaseHelper instance = DatabaseHelper._privateConstructor();
static Database _database;
Future<Database> get database async {
if (_database != null) return _database;
_database = await _initDatabase(); // only initialize if not created already
return _database;
}
_initDatabase() async {
String path = join(await getDatabasesPath(), _databaseName);
return await openDatabase(path,
version: _databaseVersion, onCreate: _onCreate);
}
Future _onCreate(Database db, int version) async {
db.execute(
"CREATE TABLE students(id INTEGER PRIMARY KEY, name TEXT, age INTEGER)",
);
}
Future<int> insert(Student object) async {
Database db = await instance.database;
var res = await db.insert(contactsTable, object.toMap(),
conflictAlgorithm: ConflictAlgorithm.ignore);
return res;
}
Future<List<Student>> queryAllRows() async {
Database db = await instance.database;
List<Map> maps = await db.query('students');
return List.generate(maps.length, (i) {
return Student(
id: maps[i]['id'],
name: maps[i]['name'],
age: maps[i]['age'],
);
});
}
Future<List<Stringt>> getStudentsByAge(String age) async {
List<String> resultStudents;
List<String> columnsToSelect = ['name']; // which columns you want in result
String whereString = 'age = ?'; // the where clause list
List<dynamic> whereArguments = [age]; // paramters for whereString (?)
Database db = await instance.database;
List<Map> result = await db.query('students',
columns: columnsToSelect,
where: whereString,
whereArgs: whereArguments);
if (result == null)
resultStudents = [];
else {
resultStudents =
List.generate(result.length, (index) => result[index]['name']);
return resultStudents;
}
Future<void> setName(String studentId, String newName) async {
Database db = await instance.database;
await db.rawUpdate(
'UPDATE students SET name = ? WHERE id = ?',
[newName, studentId]);
}
Future<int> delete(String id) async {
Database db = await instance.database;
await db.delete('students', where: 'id = ?', whereArgs: [id]);
return 1;
}
}
class Student {
final int id;
final String name;
final int age;
Student({this.id, this.name, this.age});
Map<String, dynamic> toMap() {
return {
'id': id,
'name': name,
'age': age,
};
}
}
This brings us to the end of this article.
I hope you learn't how to implement basic CRUD operations for creating and manipulating simple records in a SQLite database in Flutter.
If you have any doubts please
mention in the comments section.
Thank you for your patience
reading. If you enjoyed this post, I’d be very grateful if you’d help it
spread by emailing it to a friend, or sharing it on Whatsapp or Facebook.
😇Happy Learning!!
I am a Member Technical at D.E. Shaw India Pvt. Ltd. CodingWithArt is a blog where you find tricks and solutions to challenging coding and development problems. I aim to build a universal tech platform for every enthusiast out there.