Flutter provides a flexible and powerful framework for building cross-platform mobile applications. For applications that require local data storage, SQLite is a robust choice. The sqflite package in Flutter simplifies the process of working with SQLite databases, enabling you to store structured data locally on the device.
What is SQLite and Why Use It?
SQLite is a self-contained, high-reliability, embedded, full-featured, SQL database engine. It is the most used database engine in the world. Many applications, including those on mobile platforms, use SQLite for local data storage due to its lightweight nature, ease of use, and efficiency.
Why Use SQLite in Flutter?
- Lightweight: Suitable for mobile devices with limited resources.
- Reliable: Provides transactional support to ensure data integrity.
- Simple: Easy to set up and use within a Flutter application.
- SQL Standard: Uses standard SQL queries, making it easy to manage data.
How to Work with SQLite Using the sqflite Package in Flutter
To use SQLite in your Flutter application, follow these steps:
Step 1: Add the sqflite Dependency
First, add the sqflite package to your pubspec.yaml file:
dependencies:
flutter:
sdk: flutter
sqflite: ^2.3.0 # Use the latest version
path_provider: ^2.1.0
Run flutter pub get to install the dependencies.
Step 2: Configure Platform-Specific Settings (Android and iOS)
For Android, no additional configuration is required.
For iOS, add the following to your ios/Runner/Info.plist to enable database access:
<key>io.flutter.embedded_views_preview</key>
<string>YES</string>
Step 3: Implement Database Helper Class
Create a class to manage database operations, such as creating the database, inserting data, querying data, updating data, and deleting data.
import 'package:sqflite/sqflite.dart';
import 'package:path/path.dart';
import 'package:path_provider/path_provider.dart';
class DatabaseHelper {
static const _databaseName = "MyDatabase.db";
static const _databaseVersion = 1;
static const table = 'my_table';
static const columnId = '_id';
static const columnName = 'name';
static const columnAge = 'age';
// Make this a singleton class.
DatabaseHelper._privateConstructor();
static final DatabaseHelper instance = DatabaseHelper._privateConstructor();
// Only have a single app-wide reference to the database.
static Database? _database;
Future<Database> get database async {
if (_database != null) return _database!;
// Lazily instantiate the database if it doesn't exist.
_database = await _initDatabase();
return _database!;
}
// This opens the database (and creates it if it doesn't exist).
_initDatabase() async {
Directory documentsDirectory = await getApplicationDocumentsDirectory();
String path = join(documentsDirectory.path, _databaseName);
return await openDatabase(path,
version: _databaseVersion,
onCreate: _onCreate);
}
// SQL code to create the database table.
Future _onCreate(Database db, int version) async {
await db.execute('''
CREATE TABLE $table (
$columnId INTEGER PRIMARY KEY,
$columnName TEXT NOT NULL,
$columnAge INTEGER NOT NULL
)
''');
}
// Helper methods
// Inserts a row in the database where each key in the Map is a column name
// and the value is the column value. The return value is the id of the
// inserted row.
Future<int> insert(Map<String, dynamic> row) async {
Database db = await instance.database;
return await db.insert(table, row);
}
// All of the rows are returned as a list of maps, where each map is
// a key-value list of columns.
Future<List<Map<String, dynamic>>> queryAllRows() async {
Database db = await instance.database;
return await db.query(table);
}
// All of the methods (insert, query, update, delete) can also be done using
// raw SQL commands. Raw methods:
//Raw Insert
Future<int> rawInsert(String name, int age) async {
Database db = await instance.database;
return await db.rawInsert('INSERT INTO $table($columnName, $columnAge) VALUES(?, ?)', [name, age]);
}
// Raw Query
Future<List<Map<String, dynamic>>> rawQuery() async {
Database db = await instance.database;
return await db.rawQuery('SELECT * FROM $table');
}
//Raw Update
Future<int> rawUpdate(int id, String newName, int newAge) async {
Database db = await instance.database;
return await db.rawUpdate('UPDATE $table SET $columnName = ?, $columnAge = ? WHERE $columnId = ?', [newName, newAge, id]);
}
//Raw Delete
Future<int> rawDelete(int id) async {
Database db = await instance.database;
return await db.rawDelete('DELETE FROM $table WHERE $columnId = ?', [id]);
}
// Queries rows based on the argument received.
Future<List<Map<String, dynamic>>> queryRows(String age) async {
Database db = await instance.database;
return await db.query(table, where: '$columnAge = ?', whereArgs: [age]);
}
// Update a row in the database.
Future<int> update(Map<String, dynamic> row) async {
Database db = await instance.database;
int id = row[columnId];
return await db.update(table, row, where: '$columnId = ?', whereArgs: [id]);
}
// Deletes the row specified by the id. The number of affected rows is returned.
Future<int> delete(int id) async {
Database db = await instance.database;
return await db.delete(table, where: '$columnId = ?', whereArgs: [id]);
}
}
Step 4: Use the Database Helper in Your Flutter App
Now, you can use the DatabaseHelper class in your Flutter widgets to perform database operations.
import 'package:flutter/material.dart';
import 'database_helper.dart';
void main() {
runApp(MyApp());
}
class MyApp extends StatelessWidget {
@override
Widget build(BuildContext context) {
return MaterialApp(
title: 'SQLite Demo',
home: MyHomePage(),
);
}
}
class MyHomePage extends StatefulWidget {
@override
_MyHomePageState createState() => _MyHomePageState();
}
class _MyHomePageState extends State<MyHomePage> {
final dbHelper = DatabaseHelper.instance;
final _formKey = GlobalKey<FormState>();
final nameController = TextEditingController();
final ageController = TextEditingController();
List<Map<String, dynamic>> _journals = [];
@override
Widget build(BuildContext context) {
return Scaffold(
appBar: AppBar(
title: Text('SQLite Demo'),
),
body: Padding(
padding: const EdgeInsets.all(16.0),
child: Form(
key: _formKey,
child: Column(
crossAxisAlignment: CrossAxisAlignment.start,
children: <Widget>[
TextFormField(
decoration: InputDecoration(labelText: 'Name'),
controller: nameController,
validator: (value) {
if (value == null || value.isEmpty) {
return 'Please enter your name';
}
return null;
},
),
TextFormField(
decoration: InputDecoration(labelText: 'Age'),
controller: ageController,
keyboardType: TextInputType.number,
validator: (value) {
if (value == null || value.isEmpty) {
return 'Please enter your age';
}
return null;
},
),
Row(
children: [
Padding(
padding: const EdgeInsets.symmetric(vertical: 16.0),
child: ElevatedButton(
onPressed: () async {
if (_formKey.currentState!.validate()) {
_insert();
}
},
child: Text('Insert'),
),
),
Padding(
padding: const EdgeInsets.symmetric(vertical: 16.0, horizontal: 16.0),
child: ElevatedButton(
onPressed: () async {
_queryAll();
},
child: Text('Show Database'),
),
)
],
),
SingleChildScrollView(
scrollDirection: Axis.horizontal,
child: DataTable(
columns: const [
DataColumn(label: Text('ID')),
DataColumn(label: Text('Name')),
DataColumn(label: Text('Age')),
],
rows: _journals.map((journal) => DataRow(
cells: [
DataCell(Text(journal['_id'].toString())),
DataCell(Text(journal['name'])),
DataCell(Text(journal['age'].toString())),
],
)).toList(),
)
)
],
),
),
),
);
}
void _insert() async {
// Row to insert
Map<String, dynamic> row = {
DatabaseHelper.columnName: nameController.text,
DatabaseHelper.columnAge: int.parse(ageController.text)
};
final id = await dbHelper.insert(row);
print('inserted row id: $id');
}
void _queryAll() async {
final allRows = await dbHelper.queryAllRows();
print('query all rows:');
allRows.forEach(print);
setState(() {
_journals = allRows;
});
}
}
In this example, the app provides input fields for a name and age, and when you press the ‘Insert’ button, it adds the provided data into the SQLite database. A DataTable shows the content of the database, and is populated with what is on the table. Each database row gets displayed on the datatable on the application.
Advanced Usage
Using Transactions
Transactions are used to perform multiple operations as a single atomic operation. This ensures that either all operations succeed or none at all, maintaining data integrity.
Future<void> performTransaction() async {
Database db = await instance.database;
try {
await db.transaction((txn) async {
// Perform multiple database operations within the transaction
await txn.rawInsert('INSERT INTO $table($columnName, $columnAge) VALUES(?, ?)', ['Transaction Name 1', 30]);
await txn.rawInsert('INSERT INTO $table($columnName, $columnAge) VALUES(?, ?)', ['Transaction Name 2', 25]);
});
print('Transaction completed successfully');
} catch (e) {
print('Transaction failed: $e');
}
}
Handling Raw SQL Queries
For more complex queries or operations, you can use raw SQL queries.
Future<List<Map<String, dynamic>>> getOldUsers(int age) async {
Database db = await instance.database;
return await db.rawQuery('SELECT * FROM $table WHERE $columnAge > ?', [age]);
}
Conclusion
The sqflite package in Flutter makes it easy to work with SQLite databases, enabling you to store and manage structured data locally on mobile devices. By following the steps outlined in this guide, you can efficiently implement database functionality in your Flutter applications, providing a better user experience with reliable data persistence.