If you are writing an app that requires persistence and queries large amounts of local device data, consider using a database instead of a local folder or key value library. In general, compared to other local persistence solutions, the database can provide faster insert, update, and query functions.
The SQLite database can be used in Flutter applications through the sqflite package. This article will demonstrate inserting, reading, updating, and deleting all kinds of cat data by using sqflite.
If you are not familiar with SQLite and SQL statements, please check the official SQLite tutorial SQLite tutorial. You need to master the basic SQL statements before viewing this article.
Steps
1. Add dependencies
In order to use the SQLite database, you first need to import the two packages sqflite and path. Sqflite provides a wealth of classes and methods so that you can easily use SQLite databases.
Path provides a large number of methods so that you can correctly define the storage location of the database on the disk
dependencies:
flutter:
sdk: flutter
sqflite:
path:Make sure you have imported packages into the file you want to use
import 'dart:async';
import 'package:path/path.dart';
import 'package:sqflite/sqflite.dart';
2. Define the data model of the cat
When you are going to store the cat’s information in the newly created table, you need to define these cat first. For example, when defining a cat category, each cat’s data will contain three fields: a unique id; name; age.
class Cat {
final int id;
final String name;
final int age;
Cat({
required this.id,
required this.name,
required this.age,
});
}
3. Open the database
Before you are ready to read and write data in the database, you must first open the database. There are two steps to open a database:
Use the getDatabasesPath method in the sqflite package and cooperate with the join method in the path package to define the database path.
Open the database with the openDatabase() function from sqflite.
Use the openDatabase method in the sqflite package to open the database
// Avoid errors caused by flutter upgrade.
// Importing 'package:flutter/widgets.dart' is required.
WidgetsFlutterBinding.ensureInitialized();
// Open the database and store the reference.
final database = openDatabase(
// Set the path to the database. Note: Using the `join` function from the
// `path` package is best practice to ensure the path is correctly
// constructed for each platform.
join(await getDatabasesPath(), ‘cat_database.db'),
);
4. Create the cats table
Next, you need to create a table to store various cat information. In this example, create a database table named cats, which defines the data that can be stored. In this way, each cat data contains an id, name and age. Therefore, there will be three columns in the cats database table, namely id, name and age.
id is Dart's int type, and in the data table is SQLite's INTEGER data type. The best practice is to use id as the primary key of the database table to improve query and modification time.
name is Dart's String type, and in the data table is SQLite's TEXT data type.
age is also Dart's int type, and in the data table is SQLite's INTEGER data type.
final database = openDatabase(
// Set the path to the database. Note: Using the `join` function from the
// `path` package is best practice to ensure the path is correctly
// constructed for each platform.
join(await getDatabasesPath(), ‘cat_database.db'),
// When the database is first created, create a table to store dogs.
onCreate: (db, version) {
// Run the CREATE TABLE statement on the database.
return db.execute(
'CREATE TABLE cats(id INTEGER PRIMARY KEY, name TEXT, age INTEGER)',
);
},
// Set the version. This executes the onCreate function and provides a
// path to perform database upgrades and downgrades.
version: 1,
);
Build a complete e-commerce app
5. Insert a cat’s data
Now that you have prepared a database for storing various cat information data, now you can start reading and writing data.
First, insert a piece of Cat data in the cats data table. It is divided into the following two steps:
Convert Cat into a Map data type;
Use the insert() method to save the Map to the cats data table.
class Cat {
final int id;
final String name;
final int age;
Cat({
required this.id,
required this.name,
required this.age,
});
// Convert a Cat into a Map. The keys must correspond to the names of the
// columns in the database.
Map<String, dynamic> toMap() {
return {
'id': id,
'name': name,
'age': age,
};
}
// Implement toString to make it easier to see information about
// each cat when using the print statement.
@override
String toString() {
return 'Cat{id: $id, name: $name, age: $age}';
}
}// Define a function that inserts cats into the database
Future<void> insertCat(Cat cat) async {
// Get a reference to the database.
final db = await database;
// Insert the Cat into the correct table. You might also specify the
// `conflictAlgorithm` to use in case the same cat is inserted twice.
//
// In this case, replace any previous data.
await db.insert(
‘cats’,
cat.toMap(),
conflictAlgorithm: ConflictAlgorithm.replace,
);
}
// Create a Cat and add it to the cats table
var fido = Cat(
id: 0,
name: 'Fido',
age: 35,
);await insertCat(fido);
With the above function when you call toMap(), it converts the cat object to JSON object.
6. Query the list of cats
Now there is a Cat stored in the database. You can retrieve the data of one cat or all the data of cats by querying the database. It is divided into the following two steps:
Call the query method of the cats table object. This will return a List<Map>.
Convert List<Map> to List<Cat> data type.
// A method that retrieves all the dogs from the cats table.
Future<List<Cat>> cats() async {
// Get a reference to the database.
final db = await database;
// Query the table for all The Cats.
final List<Map<String, dynamic>> maps = await db.query(‘cats’);
// Convert the List<Map<String, dynamic> into a List<Cat>.
return List.generate(maps.length, (i) {
return Cat(
id: maps[i]['id'],
name: maps[i]['name'],
age: maps[i]['age'],
);
});
}// Now, use the method above to retrieve all the cats.
print(await cats()); // Prints a list that include Fido.
7. Modify a Cat data
Use the update() method in the sqflite package to modify (update) the data that has been inserted into the database.
The data modification operation consists of the following two steps:
Convert a cat’s data into Map data type;
Use the where statement to locate the specific data to be modified.
Future<void> updateDog(Cat cat) async {
// Get a reference to the database.
final db = await database;
// Update the given Cat.
await db.update(
‘cats’,
cat.toMap(),
// Ensure that the Dog has a matching id.
where: 'id = ?',
// Pass the Cat’s id as a whereArg to prevent SQL injection.
whereArgs: [cat.id],
);
}The items in the bracket in the end are bound to the question mark. So the value for the id is cat.id.
You can also use rawUpdate method to update the field of cats table. Like below
db.rawUpdate('''
UPDATE cats
SET age = ?
WHERE id =?
''', [3, id]
);If you use rawUpdate method then you need to write sql query inside the string. And you need to mention the value and id of the row inside square brackets.
8. Delete a piece of Cat data
In addition to inserting and modifying the data of the cats, you can also delete the data of the cats from the database. To delete data, the delete() method in the sqflite package is used.
In this section, create a new method to receive an id and delete the data that matches the id in the database. In order to achieve this, you must use the where statement to define which one is the deleted data.
Future<void> deleteCat(int id) async {
// Get a reference to the database
final db = await database;
// Remove the Dog from the database.
await db.delete(
‘cats’,
// Use a `where` clause to delete a specific dog.
where: 'id = ?',
// Pass the Cat’s id as a whereArg to prevent SQL injection.
whereArgs: [id],
);
}
Example
The following steps are required to run the example:
Create a new Flutter project;
Add the sqflite and path packages to the pubspec.yaml file;
Paste the following code in the lib/db_test.dart file (create new if there is none, overwrite if there is);
Run flutter run lib/db_test.dart.
Sqlite Example
Travel App
